|
View previous topic :: View next topic
|
| Author |
Message |
saurabh_24jul
New User
Joined: 31 Aug 2006
Posts: 5
Location: India
|
|
|
|
Hi All,
I have the requirement, where when I run the given below UPDATE query it should update 3 rows for CDS_EFF_CYMD >= 20081217 as per given below in snapshots. In SPUFI it is working fine but when I run the same query in COBOL-DB2 BATCH program it will update only for CDS_EFF_CYMD = 20081217 and will not go to the rest of the 2 rows for CDS_EFF_CYMD =20091217 and 20101217.
To resolve this issue I have used the SELECT statement as given below in DECLARE CURSOR to get 3 rows but it is giving me RECURSIVE ERROR in COBOL-DB2 program. Now the only option left with me to break the subquery and use in COBOL-DB2 program to get values on the fly to fullfill this requirement.
It would be very helpful , if anybody is having other effective solution for this issue. Thanks in advance !!!!
NOTE : Hard code values are HOST VARIABLES in COBOL-DB2 Programs.
| Code: |
File-AID for DB2 Browse DXXXXXXX.CDS_DATA 13 rows fetched
COMMAND ===> SCROLL ===> CSR
CDS_SITE_ID CDS_SEQ_NBR CDS_CARRIER_NBR CDS_EFF_CYMD
CHAR(3) SMALLINT INTEGER INTEGER
PRIMARY---- ----PRIMARY --------PRIMARY -----PRIMARY
****** **************************** TOP OF DATA ****************************
000001 ILL 3 3189 20101217
000002 ILL 0 3189 20101217
000003 ILL 1 3189 20091217
000004 ILL 0 3189 20091217
000005 ILL 5 3189 20081217
000006 ILL 3 3189 20081217
000007 ILL 0 3190 20000301
000008 ILL 0 3191 20000301
000009 ILL 0 3195 20000301
000010 ILL 0 3196 20000301
000011 ILL 0 3197 20000301
000012 ILL 0 3199 20000301
000013 ILL 0 3203 20000301
****** ************************** BOTTOM OF DATA *************************** |
| Code: |
UPDATE DXXXXXXX.CDS_DATA X
SET X.CDS_SITE_ID = 'ILL'
WHERE X.CDS_SITE_ID = 'ILL'
AND X.CDS_CARRIER_NBR = 3189
AND X.CDS_EFF_CYMD >= 20081217
AND X.CDS_SEQ_NBR = (
SELECT MAX(Y.CDS_SEQ_NBR) AS MAXSEQNBR
FROM DXXXXXXX.CDS_DATA Y
WHERE X.CDS_SITE_ID = Y.CDS_SITE_ID
AND X.CDS_CARRIER_NBR = Y.CDS_CARRIER_NBR
AND X.CDS_EFF_CYMD = Y.CDS_EFF_CYMD)
---------+---------+---------+---------+---------+---------+--
DSNE615I NUMBER OF ROWS AFFECTED IS 3 |
| Code: |
SELECT X.CDS_SITE_ID,
X.CDS_CARRIER_NBR,
X.CDS_EFF_CYMD,
X.CDS_SEQ_NBR
FROM DXXXXXXX.CDS_DATA X
WHERE X.CDS_SITE_ID = 'ILL'
AND X.CDS_CARRIER_NBR = 3189
AND X.CDS_EFF_CYMD >= 20081217
AND X.CDS_SEQ_NBR = (
SELECT MAX(Y.CDS_SEQ_NBR) AS MAXSEQNBR
FROM DXXXXXXX.CDS_DATA Y
WHERE X.CDS_SITE_ID = Y.CDS_SITE_ID
AND X.CDS_CARRIER_NBR = Y.CDS_CARRIER_NBR
AND X.CDS_EFF_CYMD = Y.CDS_EFF_CYMD)
---------+---------+---------+---------+---------+--------
CDS_SITE_ID CDS_CARRIER_NBR CDS_EFF_CYMD CDS_SEQ_NBR
---------+---------+---------+---------+---------+--------
ILL 3189 20101217 3
ILL 3189 20091217 1
ILL 3189 20081217 5
DSNE610I NUMBER OF ROWS DISPLAYED IS 3 |
|
|
| Back to top |
|
 |
Akatsukami
Global Moderator
Joined: 03 Oct 2009
Posts: 1788
Location: Bloomington, IL
|
|
|
|
Is not a query that begins
| Code: |
UPDATE DXXXXXXX.CDS_DATA X
SET X.CDS_SITE_ID = 'ILL'
WHERE X.CDS_SITE_ID = 'ILL' |
going to be inefficacious regardless of other considerations? |
|
| Back to top |
|
|
Albrecht Alcibiades
New User
Joined: 18 Oct 2011
Posts: 5
Location: Germany
|
|
|
|
Hello,
What confuses me with the last select is:
How can you get different values for CDS_SEQ_NBR if you ask for equality? |
|
| Back to top |
|
|
Albrecht Alcibiades
New User
Joined: 18 Oct 2011
Posts: 5
Location: Germany
|
|
|
|
Hello,
i have to apologize, it is a correlated subquery (i did not realize that X.CDS_EFF_CYMD is changing).
As Mr Akatsukami said: i have sat to long... |
|
| Back to top |
|
|
saurabh_24jul
New User
Joined: 31 Aug 2006
Posts: 5
Location: India
|
|
|
|
| Akatsukami wrote: |
Is not a query that begins
| Code: |
UPDATE DXXXXXXX.CDS_DATA X
SET X.CDS_SITE_ID = 'ILL'
WHERE X.CDS_SITE_ID = 'ILL' |
going to be inefficacious regardless of other considerations? |
Hi..Thanks for looking into this question...
Basically I want to touch those 3 rows so that when Batch Process run it will take those 3 rows for extract. I have run the UPDATE query to check if that kind of criteria is fullfil to include all rows with different CDS_EFF_CYMD and MAX CDS_SEQ_NBR.
I have done following steps for breaking the subquery.
1. Get MAX - CDS_SEQ_NBR for this example I'll get CDS_SEQ_NBR = 5
2. Then when I use this MAX-CDS_SEQ_NBR in upper SELECT it will give me 1 row only.
Please let me know if there is any other solution since I'm not able to get it by breaking the subquery. |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
I personally think that what Mr Akatsukami is alluding to is the apparent "setting" of a value, SET X.CDS_SITE_ID = 'ILL', in the circumstance that the item in question already has that value, WHERE X.CDS_SITE_ID = 'ILL'.
Presumably this was not the intent, but even if it does not resolve your immediate problem it should itself be resolved. |
|
| Back to top |
|
|
Marso
REXX Moderator
Joined: 13 Mar 2006
Posts: 1353
Location: Israel
|
|
|
|
| Sherlock Holmes wrote: |
| When you have eliminated the impossible, whatever remains, however improbable, must be the truth |
Your update looks OK, it works with SPUFI, it should work in your program.
One possibility left: the update in your program is slightly different...
| saurabh_24jul wrote: |
| Basically I want to touch those 3 rows so that when Batch Process run it will take those 3 rows for extract |
I'm afraid "touching" as you showed it won't be enough...  |
|
| Back to top |
|
|
Albrecht Alcibiades
New User
Joined: 18 Oct 2011
Posts: 5
Location: Germany
|
|
|
|
When do you get the recursive-error?
At run-time or during DB2 precompile/bind (if you use static sql)?
When our shop migrated from DB2 v7 to v8 and from v8 to v9 we used at the beginning a precompiler-version which did not support all new features.
This resultet in a situation, where you could do things with Spufi and the same query didn't work with embedded sql.
So i think it would be helpful to state the DB2 and DB2-precompiler-version. |
|
| Back to top |
|
|
saurabh_24jul
New User
Joined: 31 Aug 2006
Posts: 5
Location: India
|
|
|
|
| Albrecht Alcibiades wrote: |
When do you get the recursive-error?
At run-time or during DB2 precompile/bind (if you use static sql)?
When our shop migrated from DB2 v7 to v8 and from v8 to v9 we used at the beginning a precompiler-version which did not support all new features.
This resultet in a situation, where you could do things with Spufi and the same query didn't work with embedded sql.
So i think it would be helpful to state the DB2 and DB2-precompiler-version. |
Hi Albrecht ,
I'm getting recursive error while run-time. We are using DB2 Version: 9.1.5 and I'm compiling the code through ENDEVOR. I'm not sure which DB2 and DB2 Precompiler version , and where to setup in Endevor compilation.
Could you please help me to figure it out. Many Many Thanks to you !!!
| Code: |
IGZ0064S A recursive call to active program IRXXXX in compilation unit IRXXXX
was attempted.
CEE3250C The system or user abend U1064 R=00000000 was issued.
From compile unit CEEWUCHA at entry point CEEWUCHA at compile unit
offset +0000016C at entry offset +0000016C at address 1996F7F4.
|
|
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
| What is IRXXXX? Is it one of your application programs? |
|
| Back to top |
|
|
saurabh_24jul
New User
Joined: 31 Aug 2006
Posts: 5
Location: India
|
|
|
|
| Bill Woodger wrote: |
| What is IRXXXX? Is it one of your application programs? |
Yes ..This is COBOL-DB2 Program Name |
|
| Back to top |
|
|
Albrecht Alcibiades
New User
Joined: 18 Oct 2011
Posts: 5
Location: Germany
|
|
|
|
It looks like a recursive call in your COBOL-programs.
CEEWUCHA is an LE-abend-handler to imitate VS COBOL II behavior (you can find information in "Enterprise COBOL for z/OS Compiler and Runtime Migration Guide").
You should extract the programm call-stack from the dump and look at the last programms for recursive calls.
There is a good chance that you introduced the error in your program-changes, which you made to solve your initial problem, or in some error-handling code (if you get a new sqlcode/sqlstate from your query). |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
I'm thinking the same as Albrecht.
Because you had put in all the complicated DB2 code, you assumed with no evidence that the problem was there. You've now lost a couple of days of your time, and the time of people looking at your problem, because you did not give all the information you had available.
There doesn't need to be a single line of SQL in you program to get this message. If you call a program, and by some route, for some reason, control comes back to the entry-point of your program, bingo! You get that message. |
|
| Back to top |
|
|
saurabh_24jul
New User
Joined: 31 Aug 2006
Posts: 5
Location: India
|
|
|
|
Thank you so much Guys !!!!!
There is a flaw in my code .... in my previous organisation , we have XPEDITOR and I was able to find resolution soon , but here I need to put lots of DISPLAY statement which takes huge amount of time and I overlooked the flow of the program.
Due to this I read the topic of DECLARE CURSOR in Roger Sander's DB2 Book and there it is mentioned that we need to be very careful while using correlated subquery in CURSOR.
Thanks much !!! |
|
| Back to top |
|
|
|
|
