Hello, I'm looking for a method to check the utilization of index by
any SQL (Static and Dynamic) at the database level instead of
generating the access plan for each SQL.
I have inherited a database where index occupies more space than the
data and most of the indexes are looking redundant to me hence I would
like to check each index utilization and drop the same if it is not
used.
Env: DB2 UDB 8.2 /AIX 5.3
Thanks,
Sam 7 2151
Sam,
I know it is not what you are looking for, but may want to start with
catalog tables syscat.packaged ep and syscat.statemen ts.They will help
you to identify embedded sql stmts and indexes in the packages.
Regards,
Mehmet Baserdem re***********@g mail.com wrote: Hello, I'm looking for a method to check the utilization of index by any SQL (Static and Dynamic) at the database level instead of generating the access plan for each SQL.
I have inherited a database where index occupies more space than the data and most of the indexes are looking redundant to me hence I would like to check each index utilization and drop the same if it is not used.
Env: DB2 UDB 8.2 /AIX 5.3
Thanks, Sam
You should take a look at DB2 Query Patroller: http://www-306.ibm.com/software/data...uerypatroller/
Larry Edelstein
hi Sam,
You can use the index advisor to find out the unused indexes and which
will give the necessary index you should have in your database.
1.) First set up the following tables
EXPLAIN_ARGUMEN T
EXPLAIN_INSTANC E
EXPLAIN_OBJECT
EXPLAIN_OPERATO R
EXPLAIN_PREDICA TE
EXPLAIN_STATEME NT
EXPLAIN_STREAM
There is a script which will automatically create these tables for
you..I forgot the location..will let you know ASAP.
2.) You should store all your sql in a file(query.in for example).
3.) run command
db2advis -d database_name -i query.in -t 5 -n schema_name
which will show some thing like below ::
total disk space needed for initial set [ 0.405] MB
total disk space constrained to [ 9.683] MB
Trying variations of the solution set.
Optimization finished.
37 indexes in current solution
[406.4958] timerons (without recommendations )
[ 36.6679] timerons (with current solution)
[90.98%] improvement
-- LIST OF RECOMMENDED INDEXES
-- =============== ============
-- index[1], 0.009MB
CREATE UNIQUE INDEX "schemaname "."IDX603090642 270000" ON
"schemaname "."T_CNTR " ("CNTR_CAT_C DE" ASC, "CNTR_SUB_CAT_C DE" ASC)
INCLUDE ("PROD_TYP_CDE" ) ALLOW REVERSE SCANS ;
COMMIT WORK ;
RUNSTATS ON TABLE "schemaname "."T_CNTR" FOR INDEX "DB2GRA
"."IDX603090642 270000" ;
COMMIT WORK
-- RECOMMENDED EXISTING INDEXES
-- =============== =============
-- RUNSTATS ON TABLE "schemaname "."tablenam e" FOR INDEX "schemaname
"."PK_CNTRY " ;
-- COMMIT WORK ;
-- UNUSED EXISTING INDEXES
-- =============== =============
-- DROP INDEX "WDI "."IO1";
-- DROP INDEX "WDI "."IO2";
rAinDeEr,
Did you mean to say "db2 -tf EXPLAIN.DDL"
The "query.in" file will not cover all statements/scenarios. Isn't Sam
still need historical usage data for indexes?
Is there any way to log/archieve all sql stmts ever issued to database
server?
regards,
Mehmet Baserdem
Yes Mehmet,
I was talking about EXPLAIN.ddl which will create the tables. When we
run the db2advis it will recommend the new indexes and the utilisation
of the indexes and think that was exactly what Sam needed.
Also the redundant indexes are found by the advisor and is recommended
to DROP.
You can have the all the SQL's stored the file. I am not sure of a way
to capture dynamic SQL's.
You need db2audit to log all SQL submitted - The audit facility acts at
an instance level, recording all instance level activities and database
level activities.
thanks,
rAinDeEr
rAinDeEr wrote: You can have the all the SQL's stored the file. I am not sure of a way to capture dynamic SQL's.
If you have a new enough version of DB2 (I think 8.2), then you can use
the -g option to pull the queries from a dynamic SQL snapshot.
Alternatively, you can also use the -qp option to pull queries from
Query Patroller (or, apparently, use the GUI to gather a more
interesting range of data form the Query Patroller). These options are
documented in the infocenter. Unfortunately, I'm not currently running
a new enough version of DB2 to try either of these options out myself.
-ChrisC
Thanks Chris and Reindeer. I would look at the options suggested by
you.
Sam.. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Anks |
last post by:
hi
I am testing my applet to servlet communication program using
appletviewer.
as soon as i start the applet the cpu utilization reaches to 100%.I am
using apache tomcat 4.1 application server.the os is windows 2000
professional.
the same problem occurs online as more people open there applet
(ultimately it hangs).
|
by: Anon |
last post by:
Hello all,
I'm hoping for some guidance here... I am a c/c++ "expert", but a
complete python virgin. I'm trying to create a program that loads in the
entire FreeDB database (excluding the CDDBID itself) and uses this
"database" for other subsequent processing. The problem is, I'm running
out of memory on a linux RH8 box with 512MB. The FreeDB database that I'm
trying to load is presently consisting of two "CSV" files. The first file...
|
by: David Schwartz |
last post by:
We are having quite a time since moving a large database to a new
server (actually built new server, renamed as old to make seamless for
users, etc.)
Import 104 million row database (5 column) into table (CD_Assets_bad2)
from existing (CD_Assets):
Account(varchar(8))
TransactionDate(datetime(8)
Flow(varchar(1))
Category(varchar(7))
|
by: Dathon |
last post by:
I have a Windows service that's built with .NET. The process is meant to run
in the background and not suck up too much CPU time. I set the thread
priority for the various threads in the service to Below Normal, but I'm
still getting reports that the service is making a noticable impact on
overall machine performance.
I stuck some Thread.Sleep calls in a various places in the code, but that
doesn't help much, since the service makes...
|
by: Sean C. |
last post by:
Helpful folks,
Most of my previous experience with DB2 was on s390 mainframe systems
and the optimizer on this platform always seemed very predictable and
consistent. Since moving to a WinNT/UDB 7.2 environment, the choices
the optimizer makes often seem flaky. But this last example really
floored me. I was hoping someone could explain why I get worse
response time when the optimizer uses two indexes, than when it uses
one. Some context:
| |
by: matts.gustavsson |
last post by:
I am running a standalone PC (DELL 1,73MHz with 504 MB of RAM) using
Windows XPP Service Pack 2 and BD2 version 8.1 (the freeware
version!).
Right after start of the PC the process db2dasrrm.exe takes all CPU
time. This makes the system extremely slow; to start the Control
Center takes several minutes. Exploring the database, e.g. to open the
table view gives the error SQL 1032N, "No start command of the
database has been given"....
|
by: Sam Durai |
last post by:
How can we find out CPU utilization at query level (which query is
using how much % of CPU ) when multiple queries are running in
parallel.. against DB2 database on AIX. Is there any utility, tool,
method in DB2 itself which can be used for this OR is there any third
party tool, utility which can be used ?
- Sam
|
by: Kaheru |
last post by:
memory utilization increase?
This is because when i try to keep track of the CPU utilization and memory utilization of my FTP server process (ftpserver.exe), the CPU utilization increase, but the memory utilization decrease. Why is this happening?
PS:
CPU Utilization is taken from Process\% Processor Time
Memory Utilization is taken from Process\Working Set (I want to measure the physical memory it uses)
Any help is appreciated....
|
by: Lennart |
last post by:
What I'm looking for is a scenario where I can start some kind of
monitoring and in a month or two inspect how many rows have been read
via each index in a database. Preferably the monitoring should have a
low impact on performance for the system.
Any thoughts anyone?
Kind regards
/Lennart
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |