|
View previous topic :: View next topic
|
| Author |
Message |
genesis786
Active User
Joined: 28 Sep 2005
Posts: 210
Location: St Katherine's Dock London
|
|
|
|
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 |
|
 |
Pandora-Box
Global Moderator
.jpg)
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
| Rule 1 for Best coding practice : "Never commit in called/Sub modules" - Atleast I feel its important |
|
| Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10888
Location: italy
|
|
|
|
a LUW is what happens <between> two commits
after a commit a new LUW is started amen |
|
| Back to top |
|
|
genesis786
Active User
Joined: 28 Sep 2005
Posts: 210
Location: St Katherine's Dock London
|
|
|
|
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 |
|
|
Gnanas N
Active Member
Joined: 06 Sep 2007
Posts: 792
Location: Chennai, India
|
|
|
|
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 |
|
|
genesis786
Active User
Joined: 28 Sep 2005
Posts: 210
Location: St Katherine's Dock London
|
|
|
|
Ah! Thanks Gnana -802 does ring some bells.. I have something to pursue now.. thanks a lot!!  |
|
| Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10888
Location: italy
|
|
|
|
| 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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
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 |
|
|
|
|
