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 program going in infinite loop

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

New User


Joined: 04 Jun 2007
Posts: 6
Location: India

PostPosted: Fri Jul 17, 2009 12:32 pm    Post subject: DB2 program going in infinite loop
Reply with quote

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
Back to top
View user's profile Send private message

swatikamtikar

New User


Joined: 04 Jun 2007
Posts: 6
Location: India

PostPosted: Fri Jul 17, 2009 12:42 pm    Post subject: Reply to: DB2 program going in infinite loop
Reply with quote

Please respond. This is slightly urgent as we need to complete the testing today.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Fri Jul 17, 2009 12:52 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
swatikamtikar

New User


Joined: 04 Jun 2007
Posts: 6
Location: India

PostPosted: Fri Jul 17, 2009 12:54 pm    Post subject:
Reply with quote

yes its taking infinitely long. So ultimately we are cancelling the job.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Fri Jul 17, 2009 12:57 pm    Post subject:
Reply with quote

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?
Back to top
View user's profile Send private message
swatikamtikar

New User


Joined: 04 Jun 2007
Posts: 6
Location: India

PostPosted: Fri Jul 17, 2009 1:12 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Fri Jul 17, 2009 1:37 pm    Post subject:
Reply with quote

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...
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Fri Jul 17, 2009 1:38 pm    Post subject:
Reply with quote

Quote:
Only this error occurs when we are running against Qual region.


what error are you talking about - long time to execute?
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10202
Location: italy

PostPosted: Fri Jul 17, 2009 2:18 pm    Post subject: Reply to: DB2 program going in infinite loop
Reply with quote

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


if You are in a hurry You should NOT rely on forums replies ...
get a payed consultant who will adapt his schedules to Your needs
Back to top
View user's profile Send private message
expat

Global Moderator


Joined: 14 Mar 2007
Posts: 8593
Location: Back in jolly old England

PostPosted: Fri Jul 17, 2009 2:19 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
swatikamtikar

New User


Joined: 04 Jun 2007
Posts: 6
Location: India

PostPosted: Fri Jul 17, 2009 2:36 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Fri Jul 17, 2009 3:00 pm    Post subject:
Reply with quote

Quote:
is the job going into infinite loop


I don't see any loop, sorry, can't help.
Back to top
View user's profile Send private message
Bharath Bhat

Active User


Joined: 20 Mar 2008
Posts: 283
Location: chennai

PostPosted: Fri Jul 17, 2009 3:34 pm    Post subject:
Reply with quote

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


Explain the query in all the regions and compare. Then consider contacting the DBAs for reorg/runstats.
Back to top
View user's profile Send private message
Robert Sample

Global Moderator


Joined: 06 Jun 2008
Posts: 7913
Location: Bellevue, IA

PostPosted: Fri Jul 17, 2009 3:43 pm    Post subject:
Reply with quote

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.
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: Fri Jul 17, 2009 4:30 pm    Post subject:
Reply with quote

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.
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 I can not compile my program PL1 V3.R... Miguel Fernandez PL/I & Assembler 13 Tue Dec 06, 2016 8:30 pm
No new posts IMS BMP program causes 878 system abend Artemk IMS DB/DC 7 Tue Nov 22, 2016 8:26 pm
No new posts A sample of exit program for exit XMN... lind sh CICS 2 Mon Oct 03, 2016 5:07 pm
No new posts Need points to tune IMS Checkpoint Pr... satish.ms10 IMS DB/DC 2 Fri Sep 30, 2016 4:12 pm
No new posts ISPF program to set SCRNAME prino TSO/ISPF 5 Fri Sep 30, 2016 2:18 pm


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