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
 

 

Subquery inside a Updatable cursor in DB2

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

New User


Joined: 04 Mar 2013
Posts: 13
Location: Hyderabad

PostPosted: Wed Jun 12, 2013 2:02 pm    Post subject: Subquery inside a Updatable cursor in DB2
Reply with quote

Hi All ,
I am trying to use a subquery while declaring a updatable cursor to fetch for update a required column on a table .

I see the cursor is behaving as read only and is not updating what i am expecting . Just wanted to understand the logic behind this .

Thanks.
Back to top
View user's profile Send private message

Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2162
Location: @my desk

PostPosted: Wed Jun 12, 2013 2:24 pm    Post subject:
Reply with quote

easebourne_ironfist,

Are you getting any error? Can you post here your DECLARE CURSOR and UPDATE statements?
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Wed Jun 12, 2013 2:33 pm    Post subject:
Reply with quote

Could you please show us what is that being tried?

Please paste the code within code tags
Back to top
View user's profile Send private message
easebourne_ironfist

New User


Joined: 04 Mar 2013
Posts: 13
Location: Hyderabad

PostPosted: Thu Jun 13, 2013 4:41 pm    Post subject:
Reply with quote

I am running a easytrive program . The code is running fine .It does not gives any error . The thing is it wont update the db as we expect . To make things simple i gave time stamp value so that the cursor selects only one row . Code is as given below .

Code:
PARM ABEXIT SNAP DB2SSID(DB2T)                                          00010000
******************************                                          00280000
FILE DUMMY                                                              00290000
****************************                                            00330000
* MM CURSOR HOST VARIABLES                                              00340000
****************************                                            00350000
CLAIM-NUM  W  7  P 0                                                    00370000
***********************                                                 00500000
***** DB2 CURSORS *****                                                 00510000
***********************                                                 00520000
*                                                                       00530000
**************************                                              00560000
 SQL DECLARE TEST CURSOR FOR                              +             00570000
  SELECT A.CLM_NUM           +                                          00580000
    FROM GHTR.CLAIM AS A                                      +         00600000
    WHERE A.CLM_NUM = (SELECT B.CLM_NUM                         +       00610000
                     FROM GHTR.CLAIM_AUDIT AS B  +                      00620000
                    WHERE B.LAST_UPD_TP = '2012-07-07-22.59.17.955608' +00621000
                    )    +                                              00622000
    FOR UPDATE OF A.CLM_NUM                                   +         00630000
    QUERYNO 050                                                         00640000
*                                                                       00650000
*                                                                       00820000
JOB INPUT NULL FINISH END-OF-JOB                                        00830000
*                                                                       00840000
   DISPLAY ' '                                                          00850000
   DISPLAY '************************************************'           00860000
   DISPLAY '*** TEST RUN FOR CURSOR TESTING              ***'           00870000
   DISPLAY '************************************************'           00880000
   DISPLAY ' '                                                          00890000
*                                                                       00900000
   SQL OPEN TEST                                                        01180000
*                                                                       01190000
   IF SQLCODE NE 0                                                      01200000
     DISPLAY '-----------------'                                        01201000
     DISPLAY '  ABEND IN OPEN'                                          01202000
     DISPLAY '-----------------'                                        01210000
     STOP EXECUTE                                                       01270000
   END-IF                                                               01280000
*                                                                       01290000
*  FETCH THE FIRST ROW                                                  01300000
*                                                                       01310000
   SQL FETCH TEST INTO  +                                               01320000
       :CLAIM-NUM                                                       01330000
*                                                                       01350000
   IF SQLCODE = 0                                                       01360000
     DISPLAY 'GOING FOR UPDATE'                                         01370000
     SQL UPDATE GHTR.CLAIM                    +                         01371000
         SET CLM_NUM = 111111111111 +                                   01372000
         WHERE CURRENT OF TEST                                          01373000
     DISPLAY SQLCODE                                                    01374000
     IF SQLCODE = 0                                                     01375000
         DISPLAY ' FINE '                                               01376000
     ELSE                                                               01377000
         DISPLAY 'IN UPDATE WITH ' SQLCODE                              01378000
     END-IF                                                             01379000
    ELSE                                                                01380000
     IF SQLCODE = 100                                                   01390000
       DISPLAY 'INFORMATORY: ROW HAS NOT BEEN FOUND'                    01420000
      ELSE                                                              01430000
        DISPLAY '-----------------'                                     01431000
        DISPLAY '  ABEND IN FETCH'                                      01432000
        DISPLAY '-----------------'                                     01440000
        DISPLAY '-----------------'                                     01480000
        RETURN-CODE = 9                                                 01490000
        STOP EXECUTE                                                    01500000
     END-IF                                                             01510000
   END-IF                                                               01520000
*                                                                       01530000
   SQL CLOSE TEST                                                       01540000
*                                                                       01550000
*                                                                       01560000
   STOP EXECUTE                                                         01570000
*                                                                       01580000
********************                                                    02660000
END-OF-JOB. PROC                                                        02670000
********************                                                    02680000
*                                                                       02690000
    DISPLAY '**********************************************'            02700000
    DISPLAY '***     EXECUTION NORMALLY TERMINATED      ***'            02710000
    DISPLAY '**********************************************'            02720000
    DISPLAY ' '                                                         02730000
END-PROC                                                                02740000
*                                                                       02750000

Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Thu Jun 13, 2013 5:01 pm    Post subject:
Reply with quote

And can you also show us the output of displays you got?

If you cursor fetches only one row and then you check for SQLCODE

Yourr logic should do something like this I will give the pseudocode implement in Eazytrieve

Code:

MOVE 'N' to flag
Open cursor
Fetch cursor at end set flag as J
Do until flag='J'
      Update table
      Fetch cursor at end set flag as J
End
Close cursor
Back to top
View user's profile Send private message
Ranjithkumar

New User


Joined: 10 Sep 2008
Posts: 94
Location: India

PostPosted: Thu Jun 13, 2013 5:10 pm    Post subject: Reply to: Subquery inside a Updatable cursor in DB2
Reply with quote

If CLM_NUM is the primary key of table GHTR.CLAIM then UPDATE operation with WHERE CURRENT OF will not work.
Also your code is not designed to abend when the update statement SQLCODE is other than ZERO.If you could post the SYSOUT display, it will be easy to interpret.
Back to top
View user's profile Send private message
easebourne_ironfist

New User


Joined: 04 Mar 2013
Posts: 13
Location: Hyderabad

PostPosted: Fri Jun 14, 2013 5:03 pm    Post subject:
Reply with quote

Hi All ,
Thanks for your efforts , but i got the answer to my question .
I guess the question was not properly understood . The job
that i am running wont return any error . Its will give max cc 0
and sql query also return normal return code only .

I mentioned that everything seems fine but the result is not coming as expected .The rows are not getting updated .

This happens becuase the cursor becomes ready only . It reads the
subquery and make the rest of the cursor read only making it
incapable of updating anythinng in the table .
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Fri Jun 14, 2013 5:14 pm    Post subject:
Reply with quote

Quote:
Thanks for your efforts , but i got the answer to my question .


The Answer is??
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 Jun 14, 2013 7:08 pm    Post subject:
Reply with quote

Hello,

Quote:
I mentioned that everything seems fine but the result is not coming as expected .The rows are not getting updated .

This happens becuase the cursor becomes ready only . It reads the
subquery and make the rest of the cursor read only making it
incapable of updating anythinng in the table

I believe this is the answer.
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 Updating Cursor row withour using FOR... chandan.inst DB2 15 Tue Nov 08, 2016 11:17 am
No new posts Access SQLCA fields inside a stored p... gangapd DB2 4 Thu Oct 27, 2016 10:20 am
No new posts Add system date inside of a PS file a... pramitdas DFSORT/ICETOOL 5 Sun Aug 28, 2016 12:06 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


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