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
 

 

DB2 Performance issue

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

Active User


Joined: 18 Oct 2005
Posts: 182
Location: Luton UK

PostPosted: Tue Aug 12, 2008 12:14 pm    Post subject: DB2 Performance issue
Reply with quote

Hi,

I am having a job in the production that is running for 6 to 7 hours at the same time i expect the job to run not more than 1 hour.

An i tried to replicate the problem in uat and find out what exactly is the problem like is there any loop going around in the code...etc but couldn't replicate the problem as the job completes in uat in few minutes ofcourse the volume of data is different but when i compare to prd it's very less time it takes.

I requested dba to monitor the job with performance monitor and he came back saying he cannot makeout what exactly is the problem.

I requested him to get the stats of sql using explain tool.

I got Performance monitor stats but i am unable to interupt it any one has got idea where exactly to look in to it and find what exactly is the problem.

Thanks in advance,
jai
Back to top
View user's profile Send private message

ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Tue Aug 12, 2008 8:19 pm    Post subject:
Reply with quote

Incase of an indefinite loop stopping the resource limit wont help as it will run with NOLIMIT ... First of all check whether both the codes in UA and PROD is the same or not .. normally in production time consumed will be more ..it could of various reasons ...maybe resource is not available as multiple jobs would be using the same resource ...in UA as you are testing only the problematic SP this wont happen ....

If you have OMEGAMON chk at which SQL the SP is getting stuck ... try to tune the particular SQL .... if you have the MIPS stats with you check which sql is taking the most TCB_SEC or time ....
Back to top
View user's profile Send private message
Ezhil Murugan

New User


Joined: 10 Jul 2008
Posts: 14
Location: Chennai

PostPosted: Wed Aug 13, 2008 2:22 pm    Post subject:
Reply with quote

Running STROBE would provide us a complete summary CPU usage by the job ( It provides details of CPU usage details at module , I/O files, IMS/DB2 level). So we could exactly identify the exact area of high CPU consumption
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: Wed Aug 13, 2008 9:55 pm    Post subject:
Reply with quote

Hello,

Quote:
i expect the job to run not more than 1 hour
On what was this expectation based?

It sounds like the code may use a cartesian product that causes no measurable problem when the data volume is tiny, but when the tables have large amounts of data, the run-time exponentially increases.
Back to top
View user's profile Send private message
rajandhla

Active User


Joined: 18 Oct 2005
Posts: 182
Location: Luton UK

PostPosted: Mon Aug 18, 2008 2:14 pm    Post subject: Reply to: DB2 Performance issue
Reply with quote

Thanks guys for your replies...

I have performance monitor stats and i am new to this area of work...

could you please have a look at attachment having PM stats and
please let me know your observations and suggestions that how can i improve the performance of the job...

whether the tunning of sql is required or db2 related issue....

please help with your suggestions...

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

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Wed Aug 27, 2008 11:12 am    Post subject:
Reply with quote

FRom your stats, the elapsed time is almost double of the actual CPU time ...try to reduce this first ...it could be waiting for resources for a longer period ... concentrate on this part ...and there are around 102 locks being made during this time ... checking whether you really require locks .... if you see the total DML there are around 3k open and close cursor ... so you need to fine tune the cursors first ....
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 BC, BCR, BRC, BRCL performance steve-myers PL/I & Assembler 0 Fri Dec 23, 2016 7:44 am
No new posts Execessive parameter issue Sumeendar JCL & VSAM 5 Mon Dec 19, 2016 4:35 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 DFHRESPONSE returns issue divated CICS 3 Wed Nov 02, 2016 6:32 pm
No new posts What are the way we can improve CPU p... Gunapala CN DB2 10 Mon Oct 24, 2016 2:16 pm


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