|
View previous topic :: View next topic
|
| Author |
Message |
Rijit
Active User
Joined: 15 Apr 2010
Posts: 168
Location: Pune
|
|
|
|
| If in a program has many embedded SQL statements and the program takes long CPU time to run, then how to identify which SQL statements are consuming more CPU time? I have DB2 detector and Insight in my project. Experts pls advise. |
|
| Back to top |
|
 |
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
Rijit,
You can do this couple of ways,
1. For detector, first submit the job and after the job finishes. Go into detector and go into the current interval(1) --> View type has to be (A)ctivity --> (S)elect the plan in the list --> After going into it --> (S)elect the package --> You can see the SQL Call's made from the package and how much each have utilized. For more details on the tools available at you place, you can ask DB2 Support people/DBA they will be happy to help.
2. Give EXPLAIN(YES) while binding the package, ofcourse you should have PLAN_TABLE and DSN_STATEMENT_TABLE before doing it. Identify the query using queryno and check in statement_table for that package which is taking more PROCMS when compared to other queries in the same package.
Thanks,
Sushanth |
|
| Back to top |
|
|
Rijit
Active User
Joined: 15 Apr 2010
Posts: 168
Location: Pune
|
|
|
|
| Thanks Sushanth for a detailed explanation! Will try on this and will share the results:) |
|
| Back to top |
|
|
|
|
