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
 

 

Can we have correlated subquery in Declare Cursor ?

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> COBOL Programming
View previous topic :: :: View next topic  
Author Message
saurabh_24jul

New User


Joined: 31 Aug 2006
Posts: 5
Location: India

PostPosted: Fri Oct 28, 2011 10:59 pm    Post subject: Can we have correlated subquery in Declare Cursor ?
Reply with quote

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

Akatsukami

Global Moderator


Joined: 03 Oct 2009
Posts: 1738
Location: Bloomington, IL

PostPosted: Fri Oct 28, 2011 11:26 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Albrecht Alcibiades

New User


Joined: 18 Oct 2011
Posts: 5
Location: Germany

PostPosted: Fri Oct 28, 2011 11:37 pm    Post subject: Reply to: Can we have correlated subquery in Declare Cursor
Reply with quote

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
View user's profile Send private message
Albrecht Alcibiades

New User


Joined: 18 Oct 2011
Posts: 5
Location: Germany

PostPosted: Sat Oct 29, 2011 1:12 am    Post subject: Reply to: Can we have correlated subquery in Declare Cursor
Reply with quote

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

New User


Joined: 31 Aug 2006
Posts: 5
Location: India

PostPosted: Sat Oct 29, 2011 8:58 am    Post subject:
Reply with quote

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
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7230

PostPosted: Sat Oct 29, 2011 12:15 pm    Post subject: Reply to: Can we have correlated subquery in Declare Cursor
Reply with quote

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

REXX Moderator


Joined: 13 Mar 2006
Posts: 1219
Location: Israel

PostPosted: Sun Oct 30, 2011 12:19 pm    Post subject:
Reply with quote

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

New User


Joined: 18 Oct 2011
Posts: 5
Location: Germany

PostPosted: Mon Oct 31, 2011 1:47 pm    Post subject: Reply to: Can we have correlated subquery in Declare Cursor
Reply with quote

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

New User


Joined: 31 Aug 2006
Posts: 5
Location: India

PostPosted: Mon Oct 31, 2011 4:02 pm    Post subject: Re: Reply to: Can we have correlated subquery in Declare Cur
Reply with quote

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
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7230

PostPosted: Mon Oct 31, 2011 4:38 pm    Post subject: Reply to: Can we have correlated subquery in Declare Cursor
Reply with quote

What is IRXXXX? Is it one of your application programs?
Back to top
View user's profile Send private message
saurabh_24jul

New User


Joined: 31 Aug 2006
Posts: 5
Location: India

PostPosted: Mon Oct 31, 2011 4:51 pm    Post subject: Re: Reply to: Can we have correlated subquery in Declare Cur
Reply with quote

Bill Woodger wrote:
What is IRXXXX? Is it one of your application programs?


Yes ..This is COBOL-DB2 Program Name
Back to top
View user's profile Send private message
Albrecht Alcibiades

New User


Joined: 18 Oct 2011
Posts: 5
Location: Germany

PostPosted: Mon Oct 31, 2011 4:55 pm    Post subject: Reply to: Can we have correlated subquery in Declare Cursor
Reply with quote

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
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7230

PostPosted: Mon Oct 31, 2011 5:09 pm    Post subject: Reply to: Can we have correlated subquery in Declare Cursor
Reply with quote

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

New User


Joined: 31 Aug 2006
Posts: 5
Location: India

PostPosted: Mon Oct 31, 2011 6:03 pm    Post subject:
Reply with quote

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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> COBOL Programming All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Updating Cursor row withour using FOR... chandan.inst DB2 15 Tue Nov 08, 2016 11:17 am
No new posts Variable for cursor names ravikumar15 DB2 5 Sun Jul 17, 2016 7:08 am
No new posts DB2 Stored Procedure - Dynamic SQL - ... GuyC DB2 2 Wed Feb 17, 2016 5:10 pm
No new posts ISREDIT - Position Cursor on the Comm... Tarique Anwer TSO/ISPF 5 Wed Feb 03, 2016 12:44 pm
No new posts Need a cursor to achieve the below re... sandeep kumar302 DB2 10 Tue Mar 31, 2015 10:52 am


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