Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Identify long running SQL statements in a program

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Rijit

Active User


Joined: 15 Apr 2010
Posts: 168
Location: Pune

PostPosted: Thu Jul 28, 2011 10:00 pm    Post subject: Identify long running SQL statements in a program
Reply with quote

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
View user's profile Send private message

sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Fri Jul 29, 2011 12:37 am    Post subject:
Reply with quote

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
View user's profile Send private message
Rijit

Active User


Joined: 15 Apr 2010
Posts: 168
Location: Pune

PostPosted: Fri Jul 29, 2011 1:47 am    Post subject:
Reply with quote

Thanks Sushanth for a detailed explanation! Will try on this and will share the results:)
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Determine Region with max bandwidth f... seahawk789 COBOL Programming 3 Mon Feb 27, 2017 8:26 pm
No new posts SQL query not working in Cobol program. CuriousMainframer COBOL Programming 14 Wed Feb 22, 2017 5:56 pm
No new posts Executing OO COBOL program invoking J... Virendra Shambharkar COBOL Programming 2 Tue Jan 10, 2017 6:37 pm
No new posts Need Suggestion on COBOL program vickey_dw COBOL Programming 5 Thu Jan 05, 2017 10:55 pm
No new posts I can not compile my program PL1 V3.R... Miguel Fernandez PL/I & Assembler 13 Tue Dec 06, 2016 8:30 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us