Find the slowest query of DB2 V 8.1 using Toad for DB2

Dear all,

as we are experience slow process in DB2 V 8.1, how to find the slowest query, in terms of CPU, disk IO and user response time by using Toad for DB2 5.1 ?

any library of script for DB2 V 8.1 to finding bottleneck ?
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kent OlsenData Warehouse Architect / DBACommented:
When the slowdown is occurring, you can run one of the built-in tools, like "LIST APPLICATIONS" from the command line prompt to see a snapshot of what's running.

What's the host O/S?  Is this Z/OS or LUW?


Kent
0
marrowyungSenior Technical architecture (Data)Author Commented:
that one is AIX,

after I do list applications, what it shows and what else I should look for ?
0
marrowyungSenior Technical architecture (Data)Author Commented:
LIST APPLICATIONS seems not a DB2 command ?
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Kent OlsenData Warehouse Architect / DBACommented:
LIST APPLICATIONS runs from the command line tool.

db2 =>  connect to DW

   Database Connection Information

 Database server        = DB2/NT 9.7.4
 SQL authorization ID   = DB2ADMIN
 Local database alias   = DW

db2 => list applications

Auth Id  Application    Appl.      Application Id                                                 DB       # of
         Name           Handle                                                                    Name    Agents
-------- -------------- ---------- -------------------------------------------------------------- -------- -----
DB2ADMIN db2bp.exe      3606       *LOCAL.DB2.131009125559                                        DW       1

db2 =>

Open in new window

0
marrowyungSenior Technical architecture (Data)Author Commented:
ok, it seems working now from Toad, can't see why it can't before.

Once I can list that out, what should I look at .. I just see what is running but not seeing what is the slowest process here.

I don't show me what CPU and RAM usage of that process. From MS SQL it is very easy too see,
0
Kent OlsenData Warehouse Architect / DBACommented:
You should be able to see the tasks running on the AIX server with the *ps* command.
0
marrowyungSenior Technical architecture (Data)Author Commented:
this is the one I saw by running from Toad

list applications
you can see that I can't see what is the loading of each of them.. interesting. I am using the latest version of Toad for DB2.
0
marrowyungSenior Technical architecture (Data)Author Commented:
once I got the handler from the screenshot I gave you, I can kill that process by:

kill <handle>

?
0
Kent OlsenData Warehouse Architect / DBACommented:
The *list applications* command has a couple of options that you might want to use

  list applications [global] [list detail]

I suggest that you use both.  It will include extra detail like the calling program and pid/thread.

  list applications global list detail



Kent
0
marrowyungSenior Technical architecture (Data)Author Commented:
here is the result of running ps:

bash-2.05$ ps
   PID TTY      TIME CMD
12598 pts/2    0:00 bash
12678 pts/2    0:00 ps

Open in new window


However I ran ps - ef and the result below:

bash-2.05$ ps -ef
     UID   PID  PPID  C    STIME TTY      TIME CMD
    root     0     0  0   Nov 07 ?        0:01 sched
    root     1     0  0   Nov 07 ?       10:29 /etc/init -
    root     2     0  0   Nov 07 ?        0:00 pageout
    root     3     0  1   Nov 07 ?       6557:44 fsflush
    root   593     1  0   Nov 07 ?        0:01 /usr/lib/saf/sac -t 300
    root   597   593  0   Nov 07 ?        0:00 /usr/lib/saf/ttymon
    root 17656     1  0   Nov 07 ?       65:00 /usr/sbin/nscd
    root    50     1  0   Nov 07 ?        0:00 /usr/lib/sysevent/syseventd
    root    59     1  0   Nov 07 ?       14:32 /usr/lib/picl/picld
    root    91     1  0   Nov 07 ?       45:14 /usr/lib/sparcv9/cpudiagd -i
    root   141     1  0   Nov 07 ?        0:02 /usr/sbin/inetd -s
    root 19249     1  0   Oct 05 ?        0:04 /usr/sbin/syslogd
    root  1444     1  0   Nov 07 console  0:00 -sh
    root   342   341  0   Nov 07 ?        0:00 /usr/lib/locale/ja/wnn/jserver_m
    root   165     1  0   Nov 07 ?        0:00 /usr/lib/nfs/lockd
    root   350   348  0   Nov 07 ?        0:00 htt_server -port 9010 -syslog -message_locale C
   smmsp   237     1  0   Nov 07 ?        0:01 /usr/lib/sendmail -Ac -q15m
    root   513   508  0   Nov 07 ?        1:06 mibiisa -r -p 32773
    root   596     1  0   Nov 07 ?       171:21 /opt/IBM/db2/V8.1/bin/db2fmcd
db2inst6  9964 25049  0   Oct 08 ?       19:27 db2sysc
    root   218     1  0   Nov 07 ?        0:00 /usr/lib/power/powerd
    root   335     1  0   Nov 07 ?        0:00 /usr/lib/locale/ja/wnn/dpkeyserv
    root   235     1  0   Nov 07 ?        2:00 /usr/lib/sendmail -bd -q15m
    root   230     1  0   Nov 07 ?        0:11 /usr/lib/utmpd
    root   341     1  0   Nov 07 ?        0:00 /usr/lib/locale/ja/wnn/jserver
    root   348     1  0   Nov 07 ?        0:00 /usr/lib/im/htt -port 9010 -syslog -message_locale C
    root   386     1  0   Nov 07 ?        0:00 /usr/lib/locale/ja/atokserver/atokmngdaemon
    root   508     1  0   Nov 07 ?        0:01 /usr/lib/snmp/snmpdx -y -c /etc/snmp/conf
    root   407     1  0   Nov 07 ?        0:00 /usr/dt/bin/dtlogin -daemon
    root 10920 25334  0   Oct 05 ?       135:35 /usr/ar/developmentremedy/bin/arserverd -s developmentremedy -i /usr/ar/develop
dasusr1   845     1  0   Nov 07 ?        0:16 /space/home/dasusr1/das/bin/db2fmd -i dasusr1 -m /space/home/dasusr1/das/lib/li
dasusr1   656     1  0   Nov 07 ?       86:15 /space/home/dasusr1/das/adm/db2dasrrm
    root 25045 25044  0   Oct 05 ?        0:01 db2sysc
db2inst6 25429 25049  0   Oct 05 ?       63:09 db2sysc
db2fenc6  2774 25044  0   Oct 05 ?        0:00 db2fmp 1,1,0,0,0,0,0,0,1e014,2,0,1,29fe0,11000000,11000000,15fc000,2a02,2,2e3f0
    root 25450 25334  0   Oct 05 ?       27:32 /usr/java/bin/java -Djava.library.path=/usr/ar/developmentremedy/AREmail -cp /u
    root  6821 27398  0 17:39:06 ?        0:00 /usr/local/sbin/sshd -R
db2inst6 25456 25049  0   Oct 05 ?       28:02 db2sysc
    root 25047 25044  0   Oct 05 ?        0:01 db2sysc
db2fenc6  1512 25044  0   Oct 06 ?        0:00 db2fmp 1,0,0,0,0,0,0,0,1e014,2,0,1,c9fe0,11000000,11000000,15fc000,2a02,2,5d200
    root 25046 25044  0   Oct 05 ?        0:01 db2sysc
db2fenc6 25885 25044  0   Oct 10 ?        0:00 db2fmp 1,0,0,0,0,0,0,0,1e014,2,0,1,e9fe0,11000000,11000000,15fc000,2a02,2,1a900
    root 25043     1  0   Oct 05 ?        0:00 db2sysc
db2inst6 25049 25044  0   Oct 05 ?        0:26 db2sysc
db2inst6 16014 25049  0 04:02:55 ?        0:01 db2sysc
db2inst6 25054 25044  0   Oct 05 ?        5:04 db2fmp 1,0,0,0,1,0,0,0,1e014,2,0,1,9fe0,11000000,11000000,15fc000,2a02,2,70009
    root 25459 25334  0   Oct 05 ?        0:00 /usr/ar/developmentremedy/recon_engine/bin/arrecond -s developmentremedy -i /us
db2inst6 25050 25044  0   Oct 05 ?        0:02 db2sysc
db2inst6   878 25049  0 16:02:55 ?        0:25 db2sysc
db2inst6 25469 25049  0   Oct 05 ?        1:23 db2sysc
db2inst6 10937 25048  0   Oct 05 ?        0:00 db2sysc
  nagios 14139     1  0   Nov 08 ?       14:43 /usr/local/nagios/bin/nrpe -c /usr/local/nagios/etc/nrpe.cfg -d
db2inst6 15709 25049  0   Oct 05 ?       28:21 db2sysc
db2inst6 12598  6836  0 19:12:44 pts/2    0:00 bash
db2inst6 16019 25049  0 04:02:55 ?        0:04 db2sysc
db2inst6 25424 25049  0   Oct 05 ?       780:58 db2sysc
    root 25443 25334  0   Oct 05 ?        0:00 /usr/ar/developmentremedy/bin/arsvcdsp -s developmentremedy -d /usr/ar/developm
    root 25447 25334  0   Oct 05 ?        0:00 /usr/ar/developmentremedy/bin/arplugin -s developmentremedy -i /usr/ar/developm
db2inst6 25536 25050  0   Oct 05 ?       18:18 db2sysc
db2inst6 10933 25048  0   Oct 05 ?       10:20 db2sysc
db2inst6 10930 25048  0   Oct 05 ?        1:32 db2sysc
db2inst6 25470 25049  0   Oct 05 ?        0:39 db2sysc
db2inst6 12222 25049  0   Oct 07 ?       46:42 db2sysc
    root 18004     1  0   Jun 13 ?       235:58 /usr/java/bin/java -Dviper.fifo.path=/var/run/smc898/boot.fifo -Xmx128m -Djava.
db2inst6 10932 25048  0   Oct 05 ?        0:01 db2sysc
    root 27398     1  0   Nov 27 ?        3:34 /usr/local/sbin/sshd
    root 25048 25044  0   Oct 05 ?        0:02 db2sysc
    root  6836  6831  0 17:39:15 pts/2    0:00 -bash
db2inst6  1767 25049  0   Oct 15 ?        1:10 db2sysc
    vmet  6829  6821  0 17:39:11 ?        0:00 /usr/local/sbin/sshd -R
db2inst6 10931 25048  0   Oct 05 ?        0:01 db2sysc
    root 20904     1  0   Apr 05 ?        0:01 /usr/sbin/vold
db2inst6  8710 25049  0   Oct 11 ?        5:16 db2sysc
    root 11047 10920  0   Oct 05 ?        0:05 /usr/ar/developmentremedy/bin/arforkd -s developmentremedy -l /usr/ar/developme
db2inst6 25452 25049  0   Oct 05 ?        3:56 db2sysc
db2inst6   725 25049  0 08:02:55 ?        1:41 db2sysc
    root 13564     1  0   Jan 03 ?        4:41 /usr/sbin/cron
db2fenc6  2775 25044  0   Oct 05 ?        0:01 db2fmp 1,1,0,0,0,0,0,0,1e014,2,0,1,49fe0,11000000,11000000,15fc000,2a02,2,3e5e0
db2inst6 25044 25043  0   Oct 05 ?        0:01 db2sysc
    vmet  6831  6829  0 17:39:11 pts/2    0:00 -sh
db2inst6 25085 25050  0   Oct 05 ?        1:58 db2sysc
db2inst6 10935 25048  0   Oct 05 ?        1:05 db2sysc
db2inst6  1155 25049  2 16:07:55 ?        0:09 db2sysc
db2inst6 25468 25049  0   Oct 05 ?       27:15 db2sysc
db2inst6 10929 25048  0   Oct 05 ?        3:21 db2sysc
    root 19182     1  0   Oct 05 ?        0:00 /usr/sbin/rpcbind
    root 25453 25334  0   Oct 05 ?        0:01 /usr/ar/developmentremedy/cmdb/bin/arcmdbd -i /usr/ar/developmentremedy -m
db2inst6 25051 25044  0   Oct 05 ?        0:00 db2sysc
db2inst6 10934 25048  0   Oct 05 ?        8:21 db2sysc
db2inst6 25467 25049  0   Oct 05 ?        1:37 db2sysc
db2inst6 10928 25048  0   Oct 05 ?        0:22 db2sysc
db2inst6 10927 25048  0   Oct 05 ?        0:08 db2sysc
db2inst6 10936 25048  0   Oct 05 ?        1:02 db2sysc
db2inst6 25052 25048  0   Oct 05 ?        0:00 db2sysc
    root 25334     1  0   Oct 05 ?        0:00 /usr/ar/developmentremedy/bin/armonitor -s developmentremedy -c /etc/arsystem/d
    root 25462 25334  0   Oct 05 ?        0:01 /usr/ar/developmentremedy/bin/arservapd -s developmentremedy -i /usr/ar/develop
db2inst6 25461 25049  0   Oct 05 ?       13:52 db2sysc
db2inst6 18399 25049  0 20:32:55 ?        1:08 db2sysc
db2inst6 12757 12598  0 19:14:23 pts/2    0:00 ps -ef
db2inst6  1768 25049  0   Oct 15 ?        1:41 db2sysc

Open in new window


any idea?
0
marrowyungSenior Technical architecture (Data)Author Commented:
The "  list applications global list detail" show this:


Lookup Error - SQL0104N  An unexpected token "list" was found following "GLOBAL".  Expected 
tokens may include:  "END-OF-STATEMENT".  SQLSTATE=42601

Open in new window

0
marrowyungSenior Technical architecture (Data)Author Commented:
This one works:

list applications show detail

Open in new window


but it don't show which query use more CPU and RAM than other do.


it only should pid/thread, application id.

from the change time column I see this kind of thing:

 *LOCAL.db2inst6.131017072435

Open in new window



what is this means ?
0
marrowyungSenior Technical architecture (Data)Author Commented:
I found out this from the web:

list applications for database {dbName} show detail | grep -i "executing"

list applications for database {dbName} show detail | grep -i "lock"

it seems it can't be execute from within UI.

The first statement return :

Lookup Error - SQL0104N  An unexpected token "|" was found following "DETAIL".  Expected 
tokens may include:  "END-OF-STATEMENT".  SQLSTATE=42601

Open in new window

0
Kent OlsenData Warehouse Architect / DBACommented:
Really, really, really bad typing on my part.  Sorry...

  list application [global] [show detail]

  list application global show detail
0
marrowyungSenior Technical architecture (Data)Author Commented:
sorry, that's what I am referring to, this:

  list application global show detail

doesn't work for V 8,1 except without the global, very hard to see why.
0
Kent OlsenData Warehouse Architect / DBACommented:
Ok.  Run it without the "global" option then.

I don't have a version 8 system at my disposal so I'm struggling, too.  I do have version 9, but there are some differences.  Apparently, that's one of them.


Kent
0
Kent OlsenData Warehouse Architect / DBACommented:
The V8 documentation says that it should work.  Try the singular and plural forms.


  list applications global show detail
  list application global show detail


If *global* doesn't work, you're probably running from an account that doesn't have sufficient permissions for that option.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
marrowyungSenior Technical architecture (Data)Author Commented:
"If *global* doesn't work, you're probably running from an account that doesn't have sufficient permissions for that option. "

I am already running using the ROOT account.

any more persmission need? go from this direction I think.
0
marrowyungSenior Technical architecture (Data)Author Commented:
"list applications global show detail"

this one works now, thanks.

but I don't see any CPU loading and RAM loading information by the result, which column is about the cPU and RAM usag?E
0
Kent OlsenData Warehouse Architect / DBACommented:
You'll need to take the returned values and feed them into other tools to get those values.

You can also use a third party tool.  Quest Spotlight on DB2 is good, though it's not free.  DB2MON is a pretty good free tool.


  http://www.db2mon.com


Kent
0
marrowyungSenior Technical architecture (Data)Author Commented:
woow, then this kind of DB2 vesrion is not good! not much can be DIG from that.

then  I am not sure how OTHER DB2 expertist fix DB2 slow perfomance problem.

I can see that there are only 2 x file after extraction, just run that .exe ?
0
marrowyungSenior Technical architecture (Data)Author Commented:
I am sorry that when I run that it say some DDL is not found, can you try in it in your platform ?

it said DB2APP.dll is missing from your computer.
0
Kent OlsenData Warehouse Architect / DBACommented:
Hi Marrowyung,

Is it possible for you to upgrade your version of DB2?  Newer versions are much more robust and have a lot better diagnostic support.  The newer versions do not install on 32-bit servers.  There is an incompatibility in at least one of the Microsoft interfaces, so if you're running 32-bit Windows server, don't try the new DB2.  But if you've got a 64 bit server and you're running DB2 Express-C, the new version may have exactly what you're looking for.

At your version, you should be able to get the executed SQL from the SYSCAT.STATEMENTS table/view.  You'll probably need DBA access to read that table.

Some time around DB2 V9, another table/view was added that probably solves 90% of your issues.  SYSCAT.TOP_DYNAMIC_SQL.  But it doesn't exist on version 8.

I've already got DB2MON installed on my desktop.  As soon as I find a clean desktop that I can play with, I'll give the installer a try.


Kent
0
marrowyungSenior Technical architecture (Data)Author Commented:
"I've already got DB2MON installed on my desktop.  As soon as I find a clean desktop that I can play with, I'll give the installer a try."

I run that on antoher computer it still the same.

"Some time around DB2 V9, another table/view was added that probably solves 90% of your issues.  SYSCAT.TOP_DYNAMIC_SQL.  But it doesn't exist on version 8."

yeah, upgrade is needed. We will consider it on the first quanter of next year if we have budget. to something else like MS SQL 2014 or Oracle 12c

is "select * from
SYSCAT.STATEMENTS " keep returning what is running ?

I can't do this
"select * from SYSCAT.TOP_DYNAMIC_SQL"
and it said
"Lookup Error - DB2 Database Error: ERROR [42704] [IBM][DB2/SUN] SQL0204N  "SYSCAT.TOP_DYNAMIC_SQL" is an undefined name."
0
Kent OlsenData Warehouse Architect / DBACommented:
>> I run that on another computer it still the same.

It installs fine on 32-bit Windows XP.  What desktop are you running?


>> yeah, upgrade is needed. We will consider it on the first quarter of next year if we have budget. to something else like MS SQL 2014 or Oracle 12c

Are you running a licensed version of DB2 or DB2 Express-C?  Since the database seems to be running as the back end for a web application, you can probably run DB2 Express-C.  It's free, and allows unlimited database size.  It will throttle itself to use no more than 4G memory and 4 processors.  It doesn't support federation, but it sounds like you're not using that feature anyway.

For the cost of a 64-bit server you could upgrade to the latest version of DB2.  A couple of grand for a linux server and you're done spending money.

>> is "select * from
   SYSCAT.STATEMENTS " keep returning what is running ?

That shows the SQL statement log.  What is running now and what was run recently.


>> I can't do this
  "select * from SYSCAT.TOP_DYNAMIC_SQL"
and it said
"Lookup Error - DB2 Database Error: ERROR [42704] [IBM][DB2/SUN] SQL0204N  "SYSCAT.TOP_DYNAMIC_SQL" is an undefined name."


That table doesn't exist in version 8.  It came in around 9.0 or 9.1.
0
marrowyungSenior Technical architecture (Data)Author Commented:
">> I run that on another computer it still the same.

It installs fine on 32-bit Windows XP.  What desktop are you running?"

Windows 7.. oh my god.

"Are you running a licensed version of DB2 or DB2 Express-C? "

yes !

"Since the database seems to be running as the back end for a web application"

why you say this ?

so if we have "select * from
   SYSCAT.STATEMENTS " 

why we still need this ? " list applications global list detail"
0
Kent OlsenData Warehouse Architect / DBACommented:
>>"Since the database seems to be running as the back end for a web application"
>>  why you say this ?

Educated guess.  :)

SELECT * FROM syscat.statements -- returns all active and recent queries.

LIST APPLICATIONS GLOBAL LIST DETAIL -- returns all connections
0
marrowyungSenior Technical architecture (Data)Author Commented:
"Educated guess.  :)"

good thinking..

"


"SELECT * FROM syscat.statements "
but I aan't kill anything by that, seems not much use !

"LIST APPLICATIONS GLOBAL LIST DETAIL "

this one can kill but in our case it don't show up the system usage of each of them.
0
Kent OlsenData Warehouse Architect / DBACommented:
Yeah.  On that old a version of DB2 you'll have to match the SQL to the task and then take the desired action.

Upgrading to DB2 V9.1 or higher gets you more tools that will help here.
0
marrowyungSenior Technical architecture (Data)Author Commented:
"On that old a version of DB2 you'll have to match the SQL to the task and then take the desired action.
"

thanks but what to match and use the application id to match?

please show me some method, or basically I can't do much by that.
0
Kent OlsenData Warehouse Architect / DBACommented:
There isn't a direct link between the information from those two sources.  :(  You'll have to investigate and try to make offline matches.

Sorry....

As noted above, the best solution is to upgrade your version of DB2.  That's a very old version, long out of the IBM support window.


Kent
0
marrowyungSenior Technical architecture (Data)Author Commented:
"As noted above, the best solution is to upgrade your version of DB2.  That's a very old version, long out of the IBM support window."

yes!! thanks.

I am planning to buy SQL optimizer for DB2 from Toad.
0
marrowyungSenior Technical architecture (Data)Author Commented:
finally, this can close now.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
DB2

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.