DB2 program going in infinite loop

swatikamtikar · New User Joined: 04 Jun 2007 Posts: 6 Location: India

Hello,

We have an existing code which we are running against Qual region for testing purpose. All DB2 qualifers have been modified to point to Qual.
But the job gets stuck at the Open cursor statement. We intercepted the program using Tracemaster. There is an Open cursor command below which the statement
CALL 'DSNHLI' USING SQL-PLIST5 was the cause of infinite loop.
Program fails to advance beyond this point. Please help if anyone knows how to resolve this issue

swatikamtikar · New User Joined: 04 Jun 2007 Posts: 6 Location: India

Please respond. This is slightly urgent as we need to complete the testing today.

dbzTHEdinosauer · Posted: Fri Jul 17, 2009 12:52 pm

what do you mean 'infinite loop'?

do you mean the open cursor was/is taking a long time?

and existing does not mean anything. either the code works or it does not. due to upgrades in cobol version, application of db2 patches, upgrades of db2, modifications of tables existing code may nolonger justifiy its existance.

swatikamtikar · New User Joined: 04 Jun 2007 Posts: 6 Location: India

yes its taking infinitely long. So ultimately we are cancelling the job.

dbzTHEdinosauer · Posted: Fri Jul 17, 2009 12:57 pm

so what is your question?

you have a bad/poor CURSOR declaration which you cancel before completion.

could you possibly have mult-joins?

what is your question other than you have a problem, have no idea what or how, and you are in a hurry?

swatikamtikar · New User Joined: 04 Jun 2007 Posts: 6 Location: India

This is the cursor declaration:
DECLARE CSR-PURGE CURSOR WITH HOLD FOR
SELECT E040_PARTITION_NBR,
E040_PARTN_ADD_TS,
E040_INTRL_WS_ID,
E040_INTRL_NT_ID,
E040_NT_ADD_TS,
E040_NT_TYPE,
E040_ADD_TS,
E040_ADD_USERID,
E040_UPD_TS,
E040_UPD_USERID,
E040_IN_USE,
E040_IN_USE_USERID,
E040_TEXT
FROM
VCMS.VCMSE040_OD_NOTE
WHERE
E040_INTRL_WS_ID IN
(SELECT E010_INTRL_WS_ID FROM VCMS.VCMSE010_OD_WRKSH
WHERE "E010_WRKSHT_STAT" > '39'
AND (DAYS(CURRENT TIMESTAMP)-DAYS(E010_CLSD_TS)>=4*365)
AND E010_INTRL_WS_ID NOT IN
(SELECT E030_INTRL_WS_ID FROM VCMS.VCMSE030_THEFT))
WITH UR
FOR FETCH ONLY
END-EXEC.

Code at which it gets stuck:
A1000-OPEN-CURSOR.
EXEC SQL
OPEN CSR-PURGE
END-EXEC.
DISPLAY 'CURSOR OPENED WITH SQLCODE ' SQLCODE.

The program executes this 'open cursor' fine in the Dev region. Also the batch cycle which runs on the prod region runs fine. Only this error occurs when we are running against Qual region.

dbzTHEdinosauer · Posted: Fri Jul 17, 2009 1:37 pm

I would start with the obvious:

1. are the 3 tables defined with the same indexes in all 3 enviornments?

2. what are the EXPLAIN results for all 3 environments?

3. what are the row volumes for all 3 tables in all 3 environments?

4. when was each table in each environment last reorg'd, runstats, etc...

dbzTHEdinosauer · Posted: Fri Jul 17, 2009 1:38 pm

enrico-sorichetti · Posted: Fri Jul 17, 2009 2:18 pm

expat · Posted: Fri Jul 17, 2009 2:19 pm

Have you also considered what other processes are in progress against your job. How many are there. What these other processes are doing.

Is it a different LPAR, if so, how is it defined in relationship to any other LPAR to receive service, especially the prod LPAR. For example - if the prod LPAR needs additional resource, will it take it from dev, from qual, from somewher else, a combination of all of the above.

I have seen similar situations where the Prod and OA regions were heavily used and stole resource from the Dev and Test regions which then ran like a three legged dog.

swatikamtikar · New User Joined: 04 Jun 2007 Posts: 6 Location: India

I verified that the indexes of all tables in all 3 regions are same.The other 3 questions I am unable to answer.
Yes the error that I am talking abt is the job going into infinite loop for which I am seeking help.

dbzTHEdinosauer · Posted: Fri Jul 17, 2009 3:00 pm

Bharath Bhat · Posted: Fri Jul 17, 2009 3:34 pm

Robert Sample · Posted: Fri Jul 17, 2009 3:43 pm

swatikamtikar, you've got a preconceived notion that you've got an infinite loop. You don't. For some reason the SQL code is taking longer in your test environment than in your production environment. This does not mean there is an infinite loop. As long as you're convinced that is the problem, you are not going to be looking for the real issue.

dick scherrer · Posted: Fri Jul 17, 2009 4:30 pm

Hello,

Many, many queries that run quite acceptably with tens or even hundreds of rows fall flat on their face when the data consists of millions of rows. If no one did "full volume" testing before promoting this to a more-like-real environment, it is quite possible that it will take some time to change the table(s) or the process to run acceptably.

It is very short-sighted to hold off "real" volume testing until so late. All testing does not need high volume, but as soon as the majority of the code is working, there should be some higher volume testing done.

Suggest you try that query in SPUFI from the cursor declare and see what happens.