IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Cursor WITH HOLD option, gets closed within same UOW.


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
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
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

Global Moderator


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

PostPosted: Fri Jul 06, 2012 6:29 pm
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

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy

PostPosted: Fri Jul 06, 2012 6:46 pm
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
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: 792
Location: Chennai, India

PostPosted: Fri Jul 06, 2012 7:02 pm
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
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

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy

PostPosted: Fri Jul 06, 2012 7:11 pm
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
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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts SCOPE PENDING option -check data DB2 2
No new posts OUTFIL with SAVE option DFSORT/ICETOOL 7
No new posts CICS vs LE: STORAGE option CICS 0
No new posts INSYNC option with same function as I... JCL & VSAM 0
No new posts Option DYNALLOC second parameter. DFSORT/ICETOOL 11
Search our Forums:

Back to Top