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 Could we identify the called modules ... elixir1986 COBOL Programming 5 Fri Jul 14, 2017 1:53 am
No new posts Writing a file using online program grvtomar PL/I & Assembler 3 Fri Jun 30, 2017 1:06 pm
No new posts pre-compiler for program in an HFS Pedro DB2 0 Fri Jun 23, 2017 11:13 pm
No new posts Identify top 30 big tables or indexes... ashek15 DB2 0 Fri Jun 16, 2017 10:01 am
No new posts Compiling program in endeavor for tra... RALAKKAL Compuware & Other Tools 4 Mon Jun 05, 2017 8:27 pm


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