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

DB2 program going in infinite loop


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
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
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
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
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
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
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
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
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
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

Superior Member


Joined: 14 Mar 2007
Posts: 10888
Location: italy

PostPosted: Fri Jul 17, 2009 2:18 pm
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: 8796
Location: Welsh Wales

PostPosted: Fri Jul 17, 2009 2:19 pm
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
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
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
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: 8700
Location: Dubuque, Iowa, USA

PostPosted: Fri Jul 17, 2009 3:43 pm
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

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix

PostPosted: Fri Jul 17, 2009 4:30 pm
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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Error to invoke MPP program through B... IMS DB/DC 3
No new posts Using API Gateway from CICS program CICS 0
This topic is locked: you cannot edit posts or make replies. REXX - Do - Not able to LOOP CLIST & REXX 10
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
Search our Forums:

Back to Top