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.
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.