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
 

 

Difference of COMMIT in main program and in subprogram

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: Difference of COMMIT in main program and in subprogram
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    Post subject:
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

Moderator


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

PostPosted: Thu Nov 07, 2013 6:37 pm    Post subject:
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    Post subject:
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    Post subject:
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: 1278
Location: Belgium

PostPosted: Thu Nov 07, 2013 7:23 pm    Post subject:
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    Post subject:
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

Site Director


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

PostPosted: Fri Nov 08, 2013 1:04 am    Post subject:
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

Moderator


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

PostPosted: Fri Nov 08, 2013 11:53 am    Post subject:
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: 1278
Location: Belgium

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

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

http://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    Post subject:
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: 1278
Location: Belgium

PostPosted: Tue Nov 12, 2013 1:37 pm    Post subject:
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    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 I can not compile my program PL1 V3.R... Miguel Fernandez PL/I & Assembler 13 Tue Dec 06, 2016 8:30 pm
No new posts IMS BMP program causes 878 system abend Artemk IMS DB/DC 7 Tue Nov 22, 2016 8:26 pm
No new posts Sticky: difference between ... DUMMY ... and ... enrico-sorichetti JCL & VSAM 0 Mon Oct 17, 2016 4:31 pm
No new posts A sample of exit program for exit XMN... lind sh CICS 2 Mon Oct 03, 2016 5:07 pm
No new posts Need points to tune IMS Checkpoint Pr... satish.ms10 IMS DB/DC 2 Fri Sep 30, 2016 4:12 pm


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