IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Performance issue in the DB2 program that doing select


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Pradst57

New User


Joined: 03 Oct 2015
Posts: 11
Location: india

PostPosted: Mon Oct 12, 2015 8:31 pm
Reply with quote

Hi Team,
I am having a performance issue in DB2 program(OB2b440) used in the weekly job to select & generate a report. It will usually complete in less than 5 minutes now it is suddenly going in to loop withou any result for 20+ mins. This issue started after one of the table associated with this program OB2B440 was reorg-rebalanced & it made package associated with that as invalid. Weirdly the package OB2B440 was found freed in the production. (which shouldn't happened for reorg, Except this program all other programs associated the rebalnce table were available as invalid & running as usual after the rebind). So the program associated with this package was compiled/link edited & moved to production again & bind was done against the generated DBRM.

Job was restarted after this but still it did not completed & was cancelled by operations as it was using more CPU. After doing Explain on the the program 3 table's access path were found changed & it was fixed after doing reorg,runstats(with freqvalues as per IBM Data studio query tuner suggestion) & rebind. Now all of the table are accessed by index with 1 or more matchcols. But still this job not running, whenever it is submitted it simply there in db2 by consuming cpu without result. Same program running as usual even after replicating its structure & Data as per current
production(Access path also same as per production).

Query Involved:

Code:
DECLARE RDIT_CUR CURSOR FOR SELECT RDIT.RDC_NBR, RDIT.ITEM_NBR, RDIT.ON_HAND_INVTY_QTY,
ITMT.RELAY_ONLY_IND, ITMT.SHORT_DESCRIPTION, ITMT.MRCH_DPT_ID, ITMT.VENDOR_ID, VALUE ( RBLT.LOC_ID, ' ' ) ,   
RBLT.BIN_LOC_QTY, RBLT.LOC_ID_TYP_CD  FROM ITEM ITMT, ( RDC_ITEM RDIT                               
LEFT OUTER JOIN RDC_BIN_LOC RBLT ON RBLT.RDC_NBR = RDIT.RDC_NBR AND     
RBLT.ITEM_NBR = RDIT . ITEM_NBR AND BIN_LOC_QTY > 0 )                   
WHERE RDIT.ON_HAND_INVTY_QTY > 0 AND ITMT.RELAY_ONLY_IND = 'Y' AND
ITMT.ITEM_NBR = RDIT.ITEM_NBR AND NOT EXISTS ( SELECT 1 FROM ORDER_HEADER ORHT , ORDER_DETAIL ORDT                                 
WHERE ORHT.STATUS = 'O' AND ORDT.STATUS = 'O' AND ORHT.RLSE_DT <= DATE ( CURRENT DATE ) - 14 DAYS
AND ORHT.RDC_NBR = RDIT.RDC_NBR AND ORDT.RDC_NBR = RDIT.RDC_NBR AND ORDT.ITEM_NBR = RDIT.ITEM_NBR )   
ORDER BY RDIT.RDC_NBR, RDIT.ITEM_NBR ;



Any help on this issue will be much appreciated.

Code'd, for what it was worth
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Mon Oct 12, 2015 9:21 pm
Reply with quote

Did you talk to DBA yet? have you run the same query in QA region with same set up? what is the data volume in this week compared to last weeks? Any DB2 Upgrade happened in the week?what are RUNSTAT results?
Somehow, the Tablescan is poosibly be the cause for this issue.
Back to top
View user's profile Send private message
Pradst57

New User


Joined: 03 Oct 2015
Posts: 11
Location: india

PostPosted: Mon Oct 12, 2015 10:39 pm
Reply with quote

Hi Rohit, Thanks for the reply. I am the DBA, Unfortunately all the troubleshooting I did haven't worked for me. Same query running as usual with production data & structure in QA. There is not much of change in the data & there was no upgrade happened recently.

Runstat went fine & all the table's are accessed by the index(Same access path in test also). I forgot to mention after runstats, I was able to run the same query dynamically using SPUFI/DSNTEP2 & able to get the result within 3 minutes in production also. Issue is only when running this as program in job(Package was rebinded after runstats too).
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Tue Oct 13, 2015 2:49 am
Reply with quote

compare the BIND card in prod Vs QA, are they same? check if any partion is causing the issue ? SPUFI results may vary as BIND wouldn't influce it.

Check if OPTHINT is causing it to use the access path that you don't want.

Are you sure above query is the reason for the failure? what error are you getting or the job is cancelled?

Also, did you check the runstats before with the runstasts after the "RE-ORG/RUNSTAT" activity to understand what changed?

NLEVELS ( I am not sure if this is the name) on for the index exceeds 3 or 4. then may be an issue.
Back to top
View user's profile Send private message
Pradst57

New User


Joined: 03 Oct 2015
Posts: 11
Location: india

PostPosted: Tue Oct 13, 2015 10:11 am
Reply with quote

I am sorry I over looked the below two TS scan

Access path when this Job ran fine:
TNAME ACCESSTYPE MATCHCOLS ACCESSNAME
RBLT_RDC_BIN_LOC I 2 RBLXUC01
ITMT_ITEM I 0 ITMXPR07

Now above two table's are accessed through TS scan.

Bind card is same between QA & prod, OPTHINT is not enabled in both the sub system & program is binded with reopt(none) so there is no chance of access path change at the time of execution, Yes above query is the reason & job dont show any error & we have to cancel it to make other jobs run as it is consuming all the CPU available.
Back to top
View user's profile Send private message
Pradst57

New User


Joined: 03 Oct 2015
Posts: 11
Location: india

PostPosted: Tue Oct 13, 2015 11:59 am
Reply with quote

Both the tables RBLT & ITMT used the index before doing run-stats for them.
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 2455
Location: Hampshire, UK

PostPosted: Tue Oct 13, 2015 5:07 pm
Reply with quote

As the query seems to run fine without the program code wrapped around it perhaps the program code is wrong! Have you checked that? Put diagnostics in it?
Back to top
View user's profile Send private message
Pradst57

New User


Joined: 03 Oct 2015
Posts: 11
Location: india

PostPosted: Tue Oct 13, 2015 5:17 pm
Reply with quote

Hi Nic, Same code was working fine till this issue started. I suspect there is something might got changed in the recent production move(Program was compiled & moved to production, since the package got freed last week after reorg-re-balance). I will ask my team to run diagnostic on the program & share you the result. Thanks
Back to top
View user's profile Send private message
Pradst57

New User


Joined: 03 Oct 2015
Posts: 11
Location: india

PostPosted: Tue Oct 13, 2015 7:32 pm
Reply with quote

Additional Details:
First time this job abended when there was reorg/rebalance was in progress for one of the the table this program access.

That time got below error along with -911 on that table.

Fault Analyzer V10R1M0 (UK54542 2010/02/23) invoked by IDIXCEE using SYS1.PARMLIB(IDICNF00)
Subsystem IDISDB2P RC=4, Rsn=7FFFFFFF SQLConnect SqlError sqlrc=-805.
DB2 DSNP Call Level Interface error: SQLConnect SqlError sqlrc=-805.
Module OB2B440, program OB2B440, source line # 1748: Abend U0911

But after the restart there was no -805, But simply this dont complete & run for long.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Tue Oct 13, 2015 8:01 pm
Reply with quote

Quote:
I suspect there is something might got changed in the recent production move(Program was compiled & moved to production, since the package got freed last week after reorg-re-balance). I will ask my team to run diagnostic on the program & share you the result. Thanks


Then how did it run in QA at first place? Where are the results of QA execution?

Quote:
I am sorry I over looked the below two TS scan

Access path when this Job ran fine:
TNAME ACCESSTYPE MATCHCOLS ACCESSNAME
RBLT_RDC_BIN_LOC I 2 RBLXUC01
ITMT_ITEM I 0 ITMXPR07


Does it coming out of loop after SQLCODE = 100? is it properly set and working? if not then this will be looped forever which is then most likely the reason if evrything else you claimed to be accurate.

What is the solution for this? Have you done indexting yet?
Back to top
View user's profile Send private message
Pradst57

New User


Joined: 03 Oct 2015
Posts: 11
Location: india

PostPosted: Tue Oct 13, 2015 8:20 pm
Reply with quote

Rohit, There was no change in the program in QA region. It is running as usual and returning 2000+ rows. Since package was found missing in prod, Application team did recompile for production version. So am thinking there must some thing wrong in the way they recompiled it.

Sorry, I don't understand what you referring as "Does it coming out of loop after SQLCODE = 100 is it properly set and working?". When this program started executing in job, Its simply keep on running without returning anything(No error/SQL code am getting). But the same query running in DSNTEP2 with same access-path completing in 3 minutes.
Back to top
View user's profile Send private message
RahulG31

Active User


Joined: 20 Dec 2014
Posts: 446
Location: USA

PostPosted: Tue Oct 13, 2015 9:06 pm
Reply with quote

This seems wrong and may be the cause of the issue:
Quote:
Weirdly the package OB2B440 was found freed in the production.

How did you know that the package was freed? I assume you would have checked SYSPACKAGE. But, it should have been there with VALID='N'.

So, if it is not there then you need to check with IBM. My first doubt is that the package should have been there in the first place.

.
Back to top
View user's profile Send private message
Pradst57

New User


Joined: 03 Oct 2015
Posts: 11
Location: india

PostPosted: Wed Oct 14, 2015 6:23 pm
Reply with quote

Rahul, Packages for other two jobs failed had package with valid=N, But this package OB2B440 was not there at all. I checked the package in BMC Catalogue manager, package OB2B440 was found missing.
Back to top
View user's profile Send private message
RahulG31

Active User


Joined: 20 Dec 2014
Posts: 446
Location: USA

PostPosted: Wed Oct 14, 2015 7:43 pm
Reply with quote

Ok. That would mean that the problem is with BMC catalogue manager. It's not showing the invalidated package. So, if you go ahead and check SYSPACKAGE then you should be able to see the package with VALID='N'. That would mean that the package was not freed and nor it should.

Talk to BMC support.

.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Thu Oct 15, 2015 1:54 am
Reply with quote

Rahul is Right.


This is what I too learned and found.

Go to Google..
Type 'BMC REORG using REBALANCE issues free package' and open the first link and go till last to see the solution.

and if you open 2nd link then that may explains why performance issue took birth.
Back to top
View user's profile Send private message
Pradst57

New User


Joined: 03 Oct 2015
Posts: 11
Location: india

PostPosted: Thu Oct 15, 2015 3:57 pm
Reply with quote

Rahul, We recompiled the program & did the bind package already. Now the package is present with VALID='Y'.

Rohit, Rebalance was done by using IBM utilty, We didnt used BMC Reorg Rebalance.
Back to top
View user's profile Send private message
RahulG31

Active User


Joined: 20 Dec 2014
Posts: 446
Location: USA

PostPosted: Thu Oct 15, 2015 8:18 pm
Reply with quote

Ok. I am not sure if this can show you something relevant but can you go to SYSPACKAGE table and try to

1. Compare the columns related to your program which is giving problem with other program which is running fine. There are lots of columns but I think it should be worth comparing to find out what is the difference.

2. See if there are 2 rows for the same program (NAME) which is giving problem and check the columns OPERATIVE and LASTUSED.

And I said about VALID='N' because that is from where things went wrong. I can only guess but I believe the reason should be present somewhere in the SYSPACKAGE table.

Since, you also noticed a change of access paths, it is worth pointing (I found out this in other posts):

Quote:
When an invalid package is executed, DB2 does an automatic rebind so that DB2 can determine a new access path. If the rebind fails the package is marked as inoperative.


Good Luck.

.
Back to top
View user's profile Send private message
Pradst57

New User


Joined: 03 Oct 2015
Posts: 11
Location: india

PostPosted: Fri Oct 16, 2015 12:12 pm
Reply with quote

Thanks Rahul, For your reply & time. There is no difference between the program running fine & this one(Except for bind time & Timestamp). And also there is only one entry for theis problematic program OB2B440 in SYSPACKAGE.
Going to open a PMR with IBM on this.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Fri Oct 16, 2015 8:36 pm
Reply with quote

Quote:
Going to open a PMR with IBM on this.


Please post back the resolution so that we could be benefited.

However, last question. Can you try renaming this program and give a brand new name and execute it in QA and see the performance? This is just to see how it behaves with new package and program. This way you can conclude the issue is purely with package and not with the sql or program.
Back to top
View user's profile Send private message
Pradst57

New User


Joined: 03 Oct 2015
Posts: 11
Location: india

PostPosted: Tue Oct 20, 2015 11:22 am
Reply with quote

Hi Rohit/Team. Issue did fixed after column level stats itself(After which query also started running faster), But its really shame on us, That we haven't noticed the major difference between prod & QA setup.

Program the job uses in QA is bounded against package, But in the production it is just bounded against plan(For some weird reason, Though we are in DB2 V9), So there was no package existed in production at any time(It was my wrong assumption to thought package got freed, As I don't have the access to see prod bind card & application folks are also very new), So after the stats, only package got rebinded, not the plan. So job executed the plan with older access path until we rebinded the plan yesterday. Now after rebind plan the job completed in a minute.

Thank you all who helped me here.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Using API Gateway from CICS program CICS 0
No new posts SFTP Issue - destination file record ... All Other Mainframe Topics 2
No new posts DB2 Event passed to the Application P... DB2 1
No new posts How to pass the PARM value to my targ... COBOL Programming 8
No new posts Dynamically pass table name to a sele... DB2 2
Search our Forums:

Back to Top