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
 

 

Performance issue in a cobol db2 prog

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

New User


Joined: 07 Apr 2008
Posts: 10
Location: Mumbai

PostPosted: Thu Nov 04, 2010 3:24 am    Post subject: Performance issue in a cobol db2 prog
Reply with quote

Hi,
I am facing performance issue in a cobol db2 prog.The table is a partioned table built with surrogate keys and holds data of 18 million records per month and the prg access the data for each month .

My job runs for almost 6-8 hours to fetch those records.
when looked at the check point stats it shows approx less than 20000 record fetched per 3 minutes
I am using a simple sql query selecting all the rows and in where clause I am using the surrogate keys coming from input file as host variables.

all the keys are indexed keys.

Note: The predecessor job does heavy updates and insertions over the table ,due this is there a chance of lowering performance?


Please let me know your suggestions on how to reduce the run time and CPU time of the job

Regards
Jayreddy
Back to top
View user's profile Send private message

dbzTHEdinosauer

Global Moderator


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

PostPosted: Thu Nov 04, 2010 4:00 am    Post subject:
Reply with quote

gee, with all the information you have given,
i would start with - flip a coin.

what are the results of explain?
have you tried reorg before you job and after the predecessor job?
try unload and then process the qsam file and see the difference in performance.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1280
Location: Belgium

PostPosted: Thu Nov 04, 2010 3:07 pm    Post subject:
Reply with quote

What's the partitinoning key ? month ?
What's the clustering key ? (how well clustered is it after update/insert job, you probably need a runstats to check this)
Are the input records in some order ?
Back to top
View user's profile Send private message
jayreddy

New User


Joined: 07 Apr 2008
Posts: 10
Location: Mumbai

PostPosted: Tue Nov 09, 2010 1:42 am    Post subject: Reply to: Performance issue
Reply with quote

Thanks for replies i need some time to come back to your quesitons.
mean while could you please suggest me some better ways of reducing CPU time of the Job .

Is it ok to run the REORG job ,before i start running my job does it give me any advanatage or REORG JOB too run f or longer time on the huge table
Please let me know
Regards
JAYREDDY
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: Tue Nov 09, 2010 3:07 am    Post subject:
Reply with quote

Hello,

How much cpu does the problem process require?

How many rows in the table match the most restrictive key value? It may be faster to unload the rows using the key that most limits the "found set" and then filter out the other data that is not needed from this extract using the sort. . .

How long does it take to run a reorg?
Back to top
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 269
Location: Mumbai

PostPosted: Tue Nov 09, 2010 7:46 am    Post subject:
Reply with quote

Hi,

Are you using single fetch or multi fetch?

If you are using Single fetch try to run your program using ROWSET positioning for 500 or 1000 rows.
Somewhere you need to tweak your program logic but I hope it will help you to reduce CPU time

regards,
Chandan
Back to top
View user's profile Send private message
mlp

New User


Joined: 23 Sep 2005
Posts: 91

PostPosted: Tue Nov 09, 2010 9:14 am    Post subject:
Reply with quote

I presume that you are fetching the rows in your job using cursor with hold option and as you said the columns used in the where clause are part of index.

Now as the result-set of the cursor is huge i "suspect" while opening the cursor it is taking too much of time. One way to reduce the this time is by limiting the result-set of cursor by using some hardcoded values of partitioning keys. Once the work for one set of combination is over take checkpoint and then re-open the cursor with another set of partitioning keys. This solution is applicable if you know the possible values of partitioning keys.
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 SQL query not working in Cobol program. CuriousMainframer COBOL Programming 14 Wed Feb 22, 2017 5:56 pm
No new posts Execute TSO ACF commands from COBOL-I... supreethi.srid TSO/ISPF 0 Tue Feb 21, 2017 5:19 pm
No new posts Mainframe Interview Questions CICS,CO... akshathan Mainframe Interview Questions 6 Thu Feb 09, 2017 6:38 pm
No new posts Submit job from Cobol with input data abdulrafi COBOL Programming 4 Wed Feb 01, 2017 12:41 pm
This topic is locked: you cannot edit posts or make replies. COBOL Software Development Lead at Fi... shally Mainframe Jobs 0 Fri Jan 20, 2017 5:21 am


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