-911 Contention problem with DB2 batch jobs on trying to get the next 'Sale ref number'

Dave Nance

-911 Contention problem with DB2 batch jobs on trying to get the next 'Sale ref number'
Richard,
What Walter was trying to get across to you was that the commit frequency should be 1 since you have multiple processes updating this same row. That's why you keep having the -911 one process is holding on to the resource until it has updated the row 10 times.
The identity column would get you around this, as you would no longer need this single row in a separate table. I, believe, he might have been suggesting you change the key in your partitioned table to an identity column. That way you no longer have the locking contention, you just insert a record to your table and it gets the next available indentity value.

Dave Nance
First Health Services, Corp.
(804)527-6841


>>> [login to unmask email] 12/15/05 3:59:14 AM >>>

Hello Walter,

Thanks for replying.The commit frequency is 10 records. We commit after every 10 record and we are on DB2 Ver 7.x.

The table has a row that stores a max. value (e.g 45678234) that has to be read by each partition job and updated to by adding + 1 (e.g. 45678235).

This row has Row level locking defined on table. I am not sure about IDENTITY column
How can an IDENTITY column resolve -911

Cheers
Richard

Walter Janißen <[login to unmask email]> wrote:
What do you mean with 'low'? I think in your case, the commit frequency
must be 1 to avoid deadlocks or timeouts.

Which DB2-version are you running? Since V6 there is the option of IDENTITY-
columns, where -911 does not occur or if you are at V8 then you could use
SEQUENCE. In this case, you need not alter your table definition, but you
have to change your program logic (same holds for IDENTITY).

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm



Yahoo! Shopping
Find Great Deals on Holiday Gifts at Yahoo! Shopping --------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

"MMS <firsthealth.com>" made the following annotations.
------------------------------------------------------------------------------
This message, including any attachments, is intended solely for the use of the named recipient(s) and may contain confidential and/or privileged information. Any unauthorized review, use, disclosure or distribution of this communication(s) is expressly prohibited. If you are not the intended recipient, please contact the sender by
reply e-mail and destroy any and all copies of the original message.
======

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm