How to analyse poor performance of the database

[sac]

I am using DB2 v8.1 on UNIX.
At times the database shows extremely poor performance.
I do not have dba/admin rights nor do I have the web based client for
db2 v8.1.
I have only command line access.
Also DBA support starts after our jobs have completed.
(1) Is there any command that I could use from command line to find
out the process taking up maximum of the database resources ?
(2) Can the database snapshot be used for this purpose ?
(3) Is there any log created by DB2 which could be used for finding
the process consuming database resources ?

I would appreciate any help in this direction.
Thanks in advance !

[Mark A]

"sac" <sc******@gmail.com> wrote in message
news:15**************************@posting.google.c om...

I am using DB2 v8.1 on UNIX.
At times the database shows extremely poor performance.
I do not have dba/admin rights nor do I have the web based client for
db2 v8.1.
I have only command line access.
Also DBA support starts after our jobs have completed.
(1) Is there any command that I could use from command line to find
out the process taking up maximum of the database resources ?
(2) Can the database snapshot be used for this purpose ?
(3) Is there any log created by DB2 which could be used for finding
the process consuming database resources ?

I would appreciate any help in this direction.
Thanks in advance !

You could turn on the statement monitor and do a snapshot for dynamic
statements. It will tell you the number of times each SQL statement was
executed during the snapshot window, the total execution time, total CPU
time, etc. Then you could take the ones with high average times and do
explains on them.

But you need sysadmin authority to do the snapshots.

[Thiru]

Hi,
If you have enough privilege(Sysadmin Authority), monitoring
switches has to be turned on. This can be done by using

update dbm cfg using <Monitoring Switch Name> on IMMEDIATE;

Then snapshot can be taken by using

get snapshot for dynamic sql on <database alias>;

Obtained output can be used for analysis purpose.

Cheers,
Thiru
WantedToBeDBA.

[Civilian_Target]

sac wrote:

I am using DB2 v8.1 on UNIX.
At times the database shows extremely poor performance.
I do not have dba/admin rights nor do I have the web based client for
db2 v8.1.
I have only command line access.
Also DBA support starts after our jobs have completed.
(1) Is there any command that I could use from command line to find
out the process taking up maximum of the database resources ?
Well, yes. See 2.
(2) Can the database snapshot be used for this purpose ?
Yep. You want to take a snapshot and read it. Any blaring performance
problems will generally be evident in the summary, the top 4 screenfuls
of info.

Classic performance problems would be evident from "High water mark for
connections", "Time database waited on locks" and "Deadlocks detected".
If you're getting high numbers for either of the first two, or any of
the third, your web app is probably causing DB2 performance issues.

Sometimes of course, a misconfigured DB2 server is the issue, such as
lack of indexing. For this, you should find the statement in the
"Dynamic SQL Snapshot Result" with the highest Total Execution Times.
Copy the statement text and load it into the DB2 control centre on any
server with a complete copy of the DB you're testing, and run a "Query
Plan" on it. If you can see any TSCANs, or any blaring loss of execution
time on a certain part of the statement, you should be able to either
index it, fix your code so that it never occurs, or if worst comes to
worst, set the table to volatile cardinality.
(3) Is there any log created by DB2 which could be used for finding
the process consuming database resources ?

I don't follow you here, (2) may cover it.

Civilian_Target