DB2 Tips n Tricks Part 30 - How to Find Which DB2 Thread is consuming High CPU

How to Find Which DB2 Thread is consuming High CPU
Solution:
db2pd -edus
Top -Hp db2syscpid
Ps -Lp db2syscpid -o pid,ppid,pcpu,pmem,lwp
Happy Learning & Sharing

[@thiagorossetto7223]

Those simple commands can save your life ! Thanks for showing us your technique, Harish !

Very versy usefull. Thank you for the tutorial :). Your insides from the DB2 to Operating System really helped me! :) Thank you

[@DB2LUWAcademy]

Video Tutorial-How to Find Which DB2 Thread is consuming High CPU

[@OtavioNunesHC]

Excellent explanation. You did not beat around the bush.

[@srikanth1910]

Nice tip & easy narration.

[@swathisuggu]

very useful.

[@pvsraghuram]

You the man..!

[@umavallimurugan5094]

Good

[@DB2LUWAcademy]

Please subscribe to my channel

[@ananthanarayanan8985]

hi .. it was an excellent video.. i just wanted to know if we have the equivalent of top -Hp in Aix ..

[@DB2LUWAcademy]

topas command in AIX is similar

[@santoshkumar-yg9yc]

***** hi..can u tell me what is equivalent command in aix for top -Hp..I have tried it in aix , and it didnt worked.

[@fayazkhanmohammed9738]

wanted to learn automatic storage database issues as advanced level . is it possible make it please.... Thanq

[@sudheeshek]

What is the commands in AIX ?top -hp wont work in AIx environment

[@sivudu4u]

Hi Harish.. I am big fan of your videos. I have question here.. Kindly address this. You said in this video, At 5:39, "From the name of the thread, we can know the functionality of the thread". But in our live prod environments, let say we have multiple db2agent threads(in the above example we have only one that too idle) and one of them consuming more resources,. In that case how can we find exact application handle from the Name of the Thread. Appreciate your response. Thanks.

[@DB2LUWAcademy]

sivudu4u Hi Application Snapshot command will have "Agent process/thread ID" correlate it with db2pd -edus will give you the information you are looking for. Welcome.

[@ynkishoresarda]

Hello Harish if any application gets fired from OS level, it would have different PIDs at OS level and db level. Is there a way we can correlate these PIDs ? I mean that we can figure out application XXX is having process id YYY at OS level and ZZZ at db level. Please suggest. Thanks

[@DB2LUWAcademy]

hi, you have to elaborate. let me reply as far as i understand. an application connected from remote client machine will have an Agent serving that application. Application Snapshot will even reveal the client process id in the remote machine as well. if that is what you are looking for. All agents thread based in DB2 only one process db2sysc at OS level from V9.5. thanks.

[@ynkishoresarda]

Hello Let me rephrase my question Application Server :- server A Application Name :- Batch A OS process id at Server A :- 1908 Database Server :- Server B Here is a simple architecture where Server A is application server and Server B is a db server. Server A is having more then one batch process running in parallel with different process id and eventually there will be multiple db connections with multiple process id. Now any batch is running with OS process id 1908 at server A. now i want to know what is the process id at db server which can be correlate with process id 1908. Once i have the process id at db server then i can do Application snapshot etc ? And please suggest what should be pin point in out put of snapshot. if i do a "List Application" then it will list all connection coming from server A. Thanks

[@DB2LUWAcademy]

Naval Kishore Sarda Hi, If you are using DB2 Client software(IBM runtime client or data server client) and establishing a connection the application snapshot shows the client process id as well. If you are using JDBC Drivers, you will have to pass some string to database to uniquely identify a connection. you can set unique application name, set connection attributes for example.Please look at the video(33) how to set application name. there are also other connection attributes you can set. I am not aware of directly mapping process id in Application Server to DB Server, not having any environment also to verify. Hope my reply was helpful.

[@sureshnaani8773]

How can I solve DB2sysc process using more memory still continue

[@DB2LUWAcademy]

not very sure on memory consumption. sorry for not being to help.

[@sunilkumarshetty9148]

high I am trying in UNIX, below command does not work ps -Lp db2syscpid -o pid,ppid,pcpu,pmem,lwp can any one tell me which option

[@DB2LUWAcademy]

consult UNIX documentation for specific command options. every body has their own UNIX.

[@rohith2786]

what is the equivalent command for Ps -Lp db2syscpid -o pid,ppid,pcpu,pmem,lwp in AIX. It doesn't seem to recognize -p option and -o lwp option

[@sudheeshek]

+Rohith Kumar Thagilipally i think this tutorial is only for DB2 on linux.This dosent seems to be working on AIx

[@DB2LUWAcademy]

sudheesh ek feel free to add the appropriate commands for AIX. Unix tool sets varies across different platforms. Solaris/HP-Unix may also have their implementation slightly different. Care should be taken when using OS tools.

[@sudheeshek]

thank you for the quick response. btw DO you know the equivalent of top -Hp in AIX please ?

[@DB2LUWAcademy]

Please consult IBM AIX Knowledge Center

[@shayon_gangopadhyay]

For Ps -Lp db2syscpid -o pid,ppid,pcpu,pmem,lwp in Linux the equivalent command in AIX is : ps aux