DB2/I Series Commitment Control Question.

[KrugerHenning]

If I switch on commitment control on a file (table) and do an insert, will I be able to select that entry later on in the process even though the data have not been commited yet? (I know this work in outer environments but don't know about DB2/I Series.)

I have looked at the DB2/I Series documentation but can't find it there.

Thank you.

[Willie Huitema]

I'm pretty sure the asnwer is yes, although I haven't used commitment control for some time and can't absolutely remember. The asnwer should be somewhere in the link below, but it's not obvoius.

http://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp?topic=/sqlp/rbafyccsqlstmt.htm

Willie Huitema

[KrugerHenning]

Hi Willie,

Thank you for the reply. I did look at that link and some other connected to that. I also looked at the IBM document "DB2 Universal Database for iSeries SQL Programming Concepts Version 5" and "IBM Systems - iSeriesDB2 Query Manager and SQL Development Kit for iSeries Commands Version 5 Release 4" both good documents explaining the ins and outs of Commitment control but not answering my question. (I may have missed it some where ;-)

We will have to test then to find the answer it seems.

Thank you.
Kruger.

[George Jeffcock]

Hi All,

http://as400questions.blogspot.com/2009/07/faq-how-to-use-commitment-control.html

Commitment Control Locks

On the STRCMTCTL command, you specify a level of locking, either LCKLVL (*ALL) or LCKLVL (*CHG). When your program is operating under commitment control and has processed an input or output operation on a record in a file under commitment control, the record is locked by commitment control as follows:
• Your program can access the record.
• Another program in your routing step, with this file under commitment control, can read the record. If the file is a shared file, the second program can also update the record.
• Another program in your routing step that does not have this file under commitment control cannot read or update the record.
• Another program in a separate routing step, with this file under commitment control, can read the record if you specified LCKLVL (*CHG), but it cannot read the record if you specified LCKLVL (*ALL). With either lock level, the next program cannot update the record.
• Another program that does not have this file under commitment control and that is not in your routing step can read but not update the record.
• Commitment control locks are different than normal locks, depend on the LCKLVL specified, and can only be released by the COMMIT and ROLLBACK operations.
• The COMMIT and ROLLBACK operations release the locks on the records. The UNLCK operation will not release records locked using commitment control. See the CL Reference for details on lock levels.
• The number of entries that can be locked under commitment control before the COMMIT or ROLLBACK operations are required may be limited.

[KrugerHenning]

Thank you for the help.

[Anon Anon]

DB2 for i supports five isolation levels defined by SQL standards:
[list]
[*]NC - No commitment control
[*]UR - Uncommitted read
[*]CS - Cursor stability
[*]RS - Read stability
[*]RR - Repeatable read
[list]
When using Plex to provide commitment control, the FNC Commit SYS Parent triple causes STRCMTCTL to be called with *CHG which is the same as UR.  If you are familiar with the uncommitted read isolation level of other databases, you will get the same behavior in DB2 for i.  Basically this is:
[list]
[*]Read only operations do not hold a lock
[*]Inserts and Updates hold a lock until the next commit or rollback, but the records can still be read in a read only manner until the commit or rollback operation occurs
[*]Deleted records will not be found, but the key will be reserved until the next commit or rollback.
[list]
If you need an isolation level other than UR, you should remove the FNC Commit SYS Parent triple and manually call the STRCMTCTL in a CL before the program call. If you have a Commit or Rollback action diagram statement in the function you will need to either replace the FNC Commit SYS Parent triple with FNC Commit SYS Child, or create a Commit function and a Rollback function each with FNC Commit SYS Child triples. These can be called from any function, and the commit or rollback will be generated. If all you do is remove the FNC Commit SYS Parent triple, any commit or rollback action diagram statements in the function will not be generated.

[George Jeffcock]

some other older thoughts from people including Mark:

http://edge.plexinfo.net/?action=edge&forum=479&thread=30366#MessageId93643

you may also be interested in the following for general commitment control and plex (http://edge.plexinfo.net/EdgeForum.php?action=edge&forum=479&thread=29497#MessageId89668).. I swear by DeBeers implmentation method.

[KrugerHenning]

Thank you to all. This is very help full.