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

Difference of COMMIT in main program and in subprogram


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
dejunzhu

Active User


Joined: 08 May 2008
Posts: 390
Location: China

PostPosted: Thu Nov 07, 2013 3:11 pm
Reply with quote

I have below operation, in which I code 'COMMIT' in the main program, and prgram run successfully:

MAIN program:
1. declare C1 SENSITIVE SCROLL CURSOR WITH HOLD FOR
SELECT ...
FROM..
FOR UPDATE

2. OPEN C1
3. FETCH ABSOLUTE 3 FROM C1
4. UPDATE ... WHERE CURRENT OF C1
5. COMMIT WORK
6. FETCH NEXT RECORD FROM C1
7. UPDATE ... WHERE CURRENT OF C1
8. COMMIT WORK.



while when I code 'COMMIT' in a subprogram, and the main program calls the subprogram, SQLCODE -508 error occurred when perform POSITIONED-UPDATE against the second fetched row.

MAIN program:
1. declare C1 SENSITIVE SCROLL CURSOR WITH HOLD FOR
SELECT ...
FROM..
FOR UPDATE

2. OPEN C1
3. FETCH ABSOLUTE 3 FROM C1
4. UPDATE ... WHERE CURRENT OF C1
5. call subprogram to commit
6. FETCH NEXT RECORD FROM C1
7. UPDATE ... WHERE CURRENT OF C1 ==>SQLCODE -508 error here!!!
8. call subprogram to commit

subprogram:
COMMIT WORK


I examed the description of SQLCODE -508, but quite don't understand why.

Can anybody please help on this issue?
Is there limitation for commit in a subprogram?
Back to top
View user's profile Send private message
dejunzhu

Active User


Joined: 08 May 2008
Posts: 390
Location: China

PostPosted: Thu Nov 07, 2013 3:24 pm
Reply with quote

sorry, I forgot to state that I have other SQL STATEMENT which performs UPDATE against some table other than the table that the main program is using.
Back to top
View user's profile Send private message
Pandora-Box

Global Moderator


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

PostPosted: Thu Nov 07, 2013 6:37 pm
Reply with quote

After calling SUBPROGRAM did you check if the cursor was active?
Back to top
View user's profile Send private message
dejunzhu

Active User


Joined: 08 May 2008
Posts: 390
Location: China

PostPosted: Thu Nov 07, 2013 7:11 pm
Reply with quote

how to check if the cursor is active or not?
Back to top
View user's profile Send private message
dejunzhu

Active User


Joined: 08 May 2008
Posts: 390
Location: China

PostPosted: Thu Nov 07, 2013 7:19 pm
Reply with quote

I found if I I code like below, SQLCODE -508 error also occurrs.

MAIN program:
1. declare C1 SENSITIVE SCROLL CURSOR WITH HOLD FOR
SELECT ...
FROM..
FOR UPDATE

2. OPEN C1
3. FETCH ABSOLUTE 3 FROM C1
4. UPDATE ... WHERE CURRENT OF C1
5. FETCH NEXT RECORD FROM C1
6. COMMIT WORK
7. UPDATE ... WHERE CURRENT OF C1 ==>SQLCODE -508 error here!!!


why cursor does not position currectly after FETCH operation?
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Thu Nov 07, 2013 7:23 pm
Reply with quote

Did you test sqlcode after
6. FETCH NEXT RECORD FROM C1
?
Back to top
View user's profile Send private message
dejunzhu

Active User


Joined: 08 May 2008
Posts: 390
Location: China

PostPosted: Thu Nov 07, 2013 8:25 pm
Reply with quote

GuyC wrote:
Did you test sqlcode after
6. FETCH NEXT RECORD FROM C1
?

I evaluated SQLCODE immediately after each SQL statement, even COMMIT.
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Fri Nov 08, 2013 1:04 am
Reply with quote

Hello,

If this code issues COMMITs at verious places, i expect there will be ugly surprises for a Long time.

I believe if this is implemented as production, it will cause lots of problems because of "timing". I would expect that sometimes data will be inconsistent.
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 Nov 08, 2013 11:53 am
Reply with quote

Also Thumb rule to do is never commit in sub programs.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Fri Nov 08, 2013 1:29 pm
Reply with quote

publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=%2Fcom.ibm.db2z10.doc.apsg%2Fsrc%2Ftpc%2Fdb2z_heldnonheldcursor.htm

publib.boulder.ibm.com/infocenter/dzichelp/v2r2/topic/com.ibm.db2z10.doc.apsg/src/tpc/db2z_typecursor.htm

Looks like you have a "Dynamic scrollable cursor".
make it a static scrollable cursor:
declare C1 SENSITIVE STATIC SCROLL CURSOR WITH HOLD

Read the stuff above to see the implications
Back to top
View user's profile Send private message
dejunzhu

Active User


Joined: 08 May 2008
Posts: 390
Location: China

PostPosted: Sat Nov 09, 2013 10:44 am
Reply with quote

Thanks GuyC,
I tried with STATIC option, it works.
But something I quite don't understand:
Quote:
Declaring a cursor as SENSITIVE DYNAMIC has the following effects:
•When the application executes positioned UPDATE and DELETE statements with the cursor, those changes are visible. In addition, when the application executes insert, update, or delete operations (within the application but outside the cursor), those changes are visible.


According to the description, SENSITIVE DYNAMIC should also work? Am I right?
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Tue Nov 12, 2013 1:37 pm
Reply with quote

No, Dynamic means that any changes to the DB2 table are visible in the cursor. it says nothing about where the cursor is positioned after a commit. the next paragraph does :

Quote:
After a commit operation, the position of a held cursor depends on its type:
•A non-scrollable cursor that is held is positioned after the last retrieved row and before the next logical row. The next row can be returned from the result table with a FETCH NEXT statement.
•A static scrollable cursor that is held is positioned on the last retrieved row. The last retrieved row can be returned from the result table with a FETCH CURRENT statement.
•A dynamic scrollable cursor that is held is positioned after the last retrieved row and before the next logical row. The next row can be returned from the result table with a FETCH NEXT statement. DB2® returns SQLCODE +231 for a FETCH CURRENT statement.

if it isn't ON a row you would get -508 when attempting an update current.
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 Batch call online program, EXCI task ... CICS 3
No new posts step by step trace 4 ISPF dialog call... TSO/ISPF 17
No new posts Capturing COBOL job and program names... All Other Mainframe Topics 2
No new posts Receiving RC 5 when executing REXX pr... CLIST & REXX 3
No new posts COBOL program logic for comparisipn o... COBOL Programming 2
Search our Forums:

Back to Top