Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist 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: 1542
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: 10358
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: 788
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: 10358
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: 6970
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 -502 Error - Cursor already open anand jeyapaul DB2 7 Sun Oct 29, 2017 4:22 am
No new posts Update table without cursor Ron Klop DB2 3 Wed Oct 11, 2017 1:06 pm
No new posts Where is output from PARM ABEXIT (SNA... packerm CA Products 3 Thu Sep 28, 2017 4:06 pm
No new posts MQ command in option 6 of ISPF! Vignesh Sid All Other Mainframe Topics 3 Tue Sep 26, 2017 6:01 pm
No new posts Testing rerad cursor for status with ... John F Dutcher DB2 8 Fri May 19, 2017 9:35 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us