[z/OS v7] DRAIN request for Update Statements?

Philip Sevetson

[z/OS v7] DRAIN request for Update Statements?
Does anyone know why DB2 would issue a Drain Lock Request for an Update
statement? We're receiving a 00C900BA reason code on a -904 SQLCODE when
attempting to UPDATE rows in a table while another process is reading from
the table. I thought only utilities and DDL issued Drain Locks!

--Phil Sevetson
Database Administration
Wakefern Food Corporation CISD
mailto:[login to unmask email]


---------------------------------------------------------------------------------
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

[login to unmask email]

Re: [z/OS v7] DRAIN request for Update Statements?
(in response to Jim Harrison)
Phil,

I think you've hit one of the exceptions handled in Claim processing.

Your Update statement issues a Claim, and then issues a Drain in one of
these cases (from Admin Guide):

1. A drain on the object is in process for the claim class needed. In
this case, the claimer waits for the drain lock.

2. The claim is the first claim on an object before its data set has been
physically opened. Here, acquiring the drain lock ensures
that no exception states prohibit allocating the data set.

When the claimer gets the drain lock, it makes its claim and releases the
lock before beginning its processing.

Lock Lyon
Compuware Corp



Does anyone know why DB2 would issue a Drain Lock Request for an Update
statement? We're receiving a 00C900BA reason code on a -904 SQLCODE when
attempting to UPDATE rows in a table while another process is reading from
the table. I thought only utilities and DDL issued Drain Locks!

--Phil Sevetson
Database Administration
Wakefern Food Corporation CISD
mailto:[login to unmask email]



---------------------------------------------------------------------------------
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

Jim Harrison

Re: [z/OS v7] DRAIN request for Update Statements?
(in response to Philip Sevetson)
Are you updating partition keys? From what I recall (it happened to me once)
DB2 will try to drain all the partitions between the old key value and the new
value. Get the programmer to change from an update to select, change, insert &
delete instead - if you can.

http://www.idugdb2-l.org/adminscripts/wa.exe?A2=ind0212C&L=DB2-L&P=R10513&I=-3

Quoting [login to unmask email]:

> Does anyone know why DB2 would issue a Drain Lock Request for an Update
> statement? We're receiving a 00C900BA reason code on a -904 SQLCODE when
> attempting to UPDATE rows in a table while another process is reading from
> the table. I thought only utilities and DDL issued Drain Locks!
>
> --Phil Sevetson

---------------------------------------------------------------------------------
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

Philip Sevetson

Re: [z/OS v7] DRAIN request for Update Statements?
(in response to LL581@DAIMLERCHRYSLER.COM)
A-HA! Yes, we're updating partition keys. We (the DBAs) didn't notice
that the updated column was in the partitioning key. One mystery,
definitely solved. Now we get to explain it to the analyst, who
fortunately is pretty sharp.

--Phil Sevetson
Database Administration
Wakefern Food Corporation CISD
mailto:[login to unmask email]






Jim Harrison <[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
12/22/2004 02:19 PM
Please respond to DB2 Database Discussion list at IDUG


To: [login to unmask email]
cc:
Subject: Re: [DB2-L] [z/OS v7] DRAIN request for Update Statements?


Are you updating partition keys? From what I recall (it happened to me
once)
DB2 will try to drain all the partitions between the old key value and the
new
value. Get the programmer to change from an update to select, change,
insert &
delete instead - if you can.

http://www.idugdb2-l.org/adminscripts/wa.exe?A2=ind0212C&L=DB2-L&P=R10513&I=-3

Quoting [login to unmask email]:

> Does anyone know why DB2 would issue a Drain Lock Request for an Update
> statement? We're receiving a 00C900BA reason code on a -904 SQLCODE
when
> attempting to UPDATE rows in a table while another process is reading
from
> the table. I thought only utilities and DDL issued Drain Locks!
>
> --Phil Sevetson

---------------------------------------------------------------------------------
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



---------------------------------------------------------------------------------
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