View previous topic :: View next topic
|
Author |
Message |
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 |
|
Back to top |
|
|
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. |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
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 |
|
|
swatikamtikar
New User
Joined: 04 Jun 2007 Posts: 6 Location: India
|
|
|
|
yes its taking infinitely long. So ultimately we are cancelling the job. |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
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 |
|
|
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. |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
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 |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007 Posts: 10886 Location: italy
|
|
|
|
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 |
|
|
expat
Global Moderator
Joined: 14 Mar 2007 Posts: 8796 Location: Welsh Wales
|
|
|
|
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 |
|
|
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. |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
Quote: |
is the job going into infinite loop |
I don't see any loop, sorry, can't help. |
|
Back to top |
|
|
Bharath Bhat
Active User
Joined: 20 Mar 2008 Posts: 283 Location: chennai
|
|
|
|
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 |
|
|
Robert Sample
Global Moderator
Joined: 06 Jun 2008 Posts: 8700 Location: Dubuque, Iowa, USA
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19243 Location: Inside the Matrix
|
|
|
|
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 |
|
|
|