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
 

 

Cursor WITH HOLD option, gets closed within same UOW.

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

Active User


Joined: 28 Sep 2005
Posts: 210
Location: St Katherine's Dock London

PostPosted: Fri Jul 06, 2012 6:22 pm    Post subject: Cursor WITH HOLD option, gets closed within same UOW.
Reply with quote

Hi - we have a cursor declared in one of the modules like below with "WITH HOLD" option..
Code:

EXEC SQL                                               
    DECLARE   MY_CSR_A   CURSOR WITH HOLD FOR
    SELECT   A, B, C ... etc..
    FROM      MYTAB_VIEW                       
    WHERE     AA_TS < :WW-TS                     
      AND     (xxx_STA = ' '                           
       OR      xxx_STA = 'D'                           
       OR      xxx_STA = 'R'                           
       OR      xxx_STA = 'T'                           
       OR      xxx_STA = 'W')                         
      AND     (yyy_TYP = :yyyy-TYP-1               
       OR      yyy_TYP = :yyyy-TYP-2)             
    ORDER BY  zzzz ASC,
              AA_TS ASC         
 END                 
    OPTIMIZE FOR 1 ROW           
END-EXEC               


Now because of SOA type of framework, this module calls some other modules, where a COMMIT happens and when the control comes back to this module and next FETCH is done, the CURSOR is found to be closed [SQLCODE = -501].

Could anyone share their thoughts what could be causing the cursor to get closed even when it is defined with WITH HOLD and runs under same UOW.

TRAN1 --> calls this PROG1 (with WITH HOLD cursor)
PROG1 --> does some START TRAN, and CALL's other progs.
Back to top
View user's profile Send private message

Pandora-Box

Moderator


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

PostPosted: Fri Jul 06, 2012 6:29 pm    Post subject:
Reply with quote

Rule 1 for Best coding practice : "Never commit in called/Sub modules" - Atleast I feel its important
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10201
Location: italy

PostPosted: Fri Jul 06, 2012 6:46 pm    Post subject: Reply to: Cursor WITH HOLD option, gets closed within same U
Reply with quote

Quote:
same UOW.
icon_eek.gif

a LUW is what happens <between> two commits
after a commit a new LUW is started amen
Back to top
View user's profile Send private message
genesis786

Active User


Joined: 28 Sep 2005
Posts: 210
Location: St Katherine's Dock London

PostPosted: Fri Jul 06, 2012 7:01 pm    Post subject:
Reply with quote

Thanks Enrico for responding. Sorry, i am not very good at these concepts, so might have misquoted.

So if I am understanding correctly, after a COMMIT, if a new LUW starts, WITH HOLD will no longer be able to keep the cursor open?

I read this link but couldn't completely understand why the cursor is getting closed in our case.

Basically, I am trying to arrive at an understanding

i) If the cursor cannot be kept open (because of the way it works), I will place an explicit OPEN after the call returns from sub programs.

ii) If WITH HOLD does promise to keep it open, is there something I need to check/change to ensure it works such.
Back to top
View user's profile Send private message
Gnanas N

Active Member


Joined: 06 Sep 2007
Posts: 785
Location: Chennai, India

PostPosted: Fri Jul 06, 2012 7:02 pm    Post subject:
Reply with quote

Please consider these too.
Quote:
Rollback operation, SQLCODES -404, -652, -679, -802, -901, -904, -909, -910, -911, -913, and -952 may force the cursor to close.
Back to top
View user's profile Send private message
genesis786

Active User


Joined: 28 Sep 2005
Posts: 210
Location: St Katherine's Dock London

PostPosted: Fri Jul 06, 2012 7:09 pm    Post subject:
Reply with quote

Ah! Thanks Gnana -802 does ring some bells.. I have something to pursue now.. thanks a lot!! icon_smile.gif
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10201
Location: italy

PostPosted: Fri Jul 06, 2012 7:11 pm    Post subject: Reply to: Cursor WITH HOLD option, gets closed within same U
Reply with quote

Quote:
PROG1 --> does some START TRAN, and CALL's other progs.


lately the CICS concepts and understandings have become more confusing

do You realize that a START tran will/might start a completely new environment
most probably unrelated and asynchronous in respect to the <starting> environment

wiser to review thoroughly the START concepts and Your program logic
to see where You ( Your program) stand
as far as resources available/in use
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 06, 2012 7:17 pm    Post subject:
Reply with quote

you reference LUW db2, are you processing on a server at the same time as mainframe???

As Gnana indicated there are circumstances where a CURSOR will be closed.
In addition, You may have encountered a disconnect
(read: a disconnect was issued prior to a connect,
both occuring after the cursor open.

last but not least: I have found in about 90 percent of CURSOR WITH HOLD being CLOSED was due to CLOSE CURSOR being isssued.
read: poor code control.
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 BWO option in VSAM blayek CICS 3 Sat Nov 05, 2016 10:47 am
No new posts Regarding COBOL Stored Procedure opti... selvamsrinivasan85 DB2 4 Fri Nov 04, 2016 8:57 pm
No new posts Variable for cursor names ravikumar15 DB2 5 Sun Jul 17, 2016 7:08 am
No new posts Compiler option for override the exte... muralikrishnan_new COBOL Programming 9 Fri Jul 08, 2016 12:09 pm


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