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
 

 

Tuning Db2 StoredProcedure

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

New User


Joined: 07 Dec 2008
Posts: 6
Location: INDIA

PostPosted: Sun Nov 27, 2011 8:11 pm    Post subject: Tuning Db2 StoredProcedure
Reply with quote

Hi, we have DB2 SP being called from Batch Program for 400000 times. SP has nearly 40 SQL Queries to execute for each call from Batch Program. Nearly 30 SQL Queries are using SUM function.

Currently this process is taking 24 Min CPU time and 4-5Hrs of Elapsed Time.

Now we are in the process of fine tune this process.
I would like to know in general how many maximum SQL queries can be coded inside SP?
How many max input calls allowed for SP for better performance?

Thanks
Back to top
View user's profile Send private message

dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6968
Location: porcelain throne

PostPosted: Sun Nov 27, 2011 9:25 pm    Post subject:
Reply with quote

why are you looking at the SP to fine tune,
when it sounds as if the whole process needs to be revamped?

is this a daily, weekly, monthly, quarterly run?

you mention SUM; have you thought about MQT - Materialized Query Tables or simply building and storing aggregate values.

Tuning any SQL means eventually you reach a point where the time involved means
data has to be crunched and it takes time.

you need to reduce the 400000 CALLs and the 40 queries.
that is 16 million sql invokations (CALLs to db2)
which in itself (just forget about what the query has to do) takes time.

and these questions:
Quote:
I would like to know in general how many maximum SQL queries can be coded inside SP?
How many max input calls allowed for SP for better performance?

sounds as if you are one of Mac's mechanics -- making the horn blow louder since the breaks can not be fixed.
Back to top
View user's profile Send private message
prasanthiv

New User


Joined: 07 Dec 2008
Posts: 6
Location: INDIA

PostPosted: Sun Nov 27, 2011 9:40 pm    Post subject: Reply to: Tuning Db2 StoredProcedure
Reply with quote

Thanks for your reply. This is a weekly Job. Yes Exactly we need to change the whole process. I am thinking of shifting to Parallel processing to handle 400000 CALLs. Is there any better way apart from running SP parallel y ?
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6968
Location: porcelain throne

PostPosted: Mon Nov 28, 2011 12:15 am    Post subject:
Reply with quote

you are not thinking about changing the process,
you consistanly want the existing process to run faster.

without knowing something of the application, tables, etc...
and knowing the goal of this batch run and how is works within the complete system,
what you are asking for is free consultant work.

suggest you look into other methods to accomplish the goal of this process,
(i.e. have the aggregation of information done with/on a different way)

just to answer your question about parallel SP tasks,
anytime you can split up the work into multiple jobs and
they do not conflict
you have the potential of speeding-up your processing,
but you would have to test in your environment to determine is there is any gain to be found.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Mon Nov 28, 2011 11:21 am    Post subject:
Reply with quote

Hello,

Quote:
Hi, we have DB2 SP being called from Batch Program for 400000 times. SP has nearly 40 SQL Queries to execute for each call from Batch Program.
Make sure you know who decided was a proper approach and make sure thry are no longer to provide this kind of specification.

Had anyone done anything approaching volume testing of this process, it wold have been seen that the approach was self-defeating. What happens here quite often is someone has this "great idea" implements the design/code and runs a few iterations and decide this is good. Then when the normal volume is run, it all comes crashing done.

If you discard this now and implement a better solutiin, you will save considerable lost time & effort.
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 Batch job tuning sgandhla Testing & Performance analysis 5 Fri Mar 24, 2017 9:41 pm
No new posts PL/I code tuning/Performance improvement Virendra Shambharkar PL/I & Assembler 4 Mon Dec 05, 2016 11:57 am
No new posts Performance tuning of Online system bipinpeter All Other Mainframe Topics 2 Thu Nov 26, 2015 2:29 pm
No new posts Fine tuning for VSAM dataset Vasanthr JCL & VSAM 3 Sat Jan 17, 2015 2:24 am
No new posts IMS Pre-load Validation - Performance... yogi.47eie IMS DB/DC 4 Fri Oct 24, 2014 1:18 pm


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