Application program issuing a drain??

Jim Harrison

Application program issuing a drain??
I've been perusing the archives but not having much luck. Under what
circumstances would an application program issue a DRAIN
request? Everything I've read says that only utilities use DRAINs to gain
exclusive control of an object. Nowhere do I see that application programs
use it - but I've got one that does. It's a COBOL insert/update/delete
program which is abending with a -904 reason code 00C200EA. My
understanding is that this shouldn't be happening - that it should fail
trying to get an X lock at some point if the other applications don't let go.



David S Waugh

Re: Application program issuing a drain??
(in response to Jim Harrison)
Hi Jim:

Try looking at the following entries in the Archives:

Item # Date Time Recs Subject
013919 00/08/10 11:05 16 Re: DRAIN LOCK on changing a partitioning key
013921 00/08/10 12:46 10 Re: DRAIN LOCK on changing a partitioning key
039442 02/01/13 06:02 103 Drain lock waits during application processing
049854 02/07/31 11:23 19 drain failure 00c200ea
049862 02/07/31 12:13 52 Re: drain failure 00c200ea
049891 02/08/01 09:29 138 Re: drain failure 00c200ea

I think they have the answer you're looking for. The usual reason is: Updating a partitioning key, which causes the row to move from one partition to another, will cause DB2 to issue drains for the old & new partition, plus all partitions in between.

HTH

David S. Waugh, NCW
DSW Consulting & Services


---------- Jim Harrison <[login to unmask email]> writes:

From: Jim Harrison <[login to unmask email]>
To: [login to unmask email]
Subject: Application program issuing a drain??
Date: Thu, 19 Dec 2002 13:49:58 -0500

I've been perusing the archives but not having much luck. Under what
circumstances would an application program issue a DRAIN
request? Everything I've read says that only utilities use DRAINs to gain
exclusive control of an object. Nowhere do I see that application programs
use it - but I've got one that does. It's a COBOL insert/update/delete
program which is abending with a -904 reason code 00C200EA. My
understanding is that this shouldn't be happening - that it should fail
trying to get an X lock at some point if the other applications don't let go.






Jim Harrison

Re: Application program issuing a drain??
(in response to David S Waugh)
Thanks. It's a good bet the threads you pointed me to are describing the
problem. Now comes the fight with the application people to get them to
change their code!

ps - I did search on that reason code but didn't get any hits. I guess the
search engine must be case sensitive as I probably typed it in all caps

At 09:43 PM 12/19/2002 +0000, David S Waugh said:
>Hi Jim:
>
>Try looking at the following entries in the Archives:
>
>Item # Date Time Recs Subject
>013919 00/08/10 11:05 16 Re: DRAIN LOCK on changing a partitioning key
>013921 00/08/10 12:46 10 Re: DRAIN LOCK on changing a partitioning key
>039442 02/01/13 06:02 103 Drain lock waits during application processing
>049854 02/07/31 11:23 19 drain failure 00c200ea
>049862 02/07/31 12:13 52 Re: drain failure 00c200ea
>049891 02/08/01 09:29 138 Re: drain failure 00c200ea
>
>I think they have the answer you're looking for. The usual reason
>is: Updating a partitioning key, which causes the row to move from one
>partition to another, will cause DB2 to issue drains for the old & new
>partition, plus all partitions in between.
>
>HTH
>
>David S. Waugh, NCW
>DSW Consulting & Services



Mike Turner

Re: Application program issuing a drain??
(in response to Jim Harrison)
Jim

There are two cases when SQL will try to get a Drain Lock (not quite the
same thing as issuing a Drain request). One is a long claim and will happen
for the first SQL to process a partition after a DB2 Utility has run or
first access to partition after physical dataset open. The second case
occurs when updating a partitioning key which results in the row moving
partitions.

Regards
Mike Turner
Email: [login to unmask email]
Web: www.michael-turner.ltd.uk

Message text written by DB2 Data Base Discussion List
>I've been perusing the archives but not having much luck. Under what
circumstances would an application program issue a DRAIN
request? Everything I've read says that only utilities use DRAINs to gain
exclusive control of an object. Nowhere do I see that application programs
use it - but I've got one that does. It's a COBOL insert/update/delete
program which is abending with a -904 reason code 00C200EA. My
understanding is that this shouldn't be happening - that it should fail
trying to get an X lock at some point if the other applications don't let
go.
<



Pearl

Re: Application program issuing a drain??
(in response to Mike Turner)
Check Your tablespace, Is it Partition Tablespace? If yes, check your
program, Do it used SQL update statement on partition key?
I found this problem when application program updated partition key
(wherter it's change or not), they cann't selected data (wherter add 'with
UR' in select statement or not).
When I asked IBM ,They explained it's internal process of DB2 because
when you updated Partition key, it's mean you may change data location from
partition A to partition B and then DB2 will do drain lock on all partitions
between partition A to partition B. So, you cann't access data that being
drain lock and you will get sql error code -904.
I think it's unacceptable. IBM's document (I cann't remember title name
but I think it was what's new in V6) talk about new feature in DB2 OS/390 V6
that you can update partition key column. But they didn't write limitation
or effect when you used this feature in any books. My Programmers complained
me why I introduced this feature that is deficient to them. So, I recommend
them to modify program use delete/insert instead of update statement.
I heard IBM will not improve this drain lock in DB2 OS/390 V7.


Ju

----- Original Message -----
From: "Jim Harrison" <[login to unmask email]>
Newsgroups: bit.listserv.db2-l
To: <[login to unmask email]>
Sent: Friday, December 20, 2002 1:49 AM
Subject: Application program issuing a drain??


> I've been perusing the archives but not having much luck. Under what
> circumstances would an application program issue a DRAIN
> request? Everything I've read says that only utilities use DRAINs to
gain
> exclusive control of an object. Nowhere do I see that application
programs
> use it - but I've got one that does. It's a COBOL insert/update/delete
> program which is abending with a -904 reason code 00C200EA. My
> understanding is that this shouldn't be happening - that it should fail
> trying to get an X lock at some point if the other applications don't let
go.
>
>
>


>



Tina Hilton

Re: Application program issuing a drain??
(in response to Pearl)
You can always disable this feature in the zparms (PARTKEYU). Of course,
you'd have to wait until after they changed their code.

-----Original Message-----
From: Jim Harrison [mailto:[login to unmask email]
Sent: December 19, 2002 5:14 PM
To: [login to unmask email]
Subject: Re: Application program issuing a drain??


Thanks. It's a good bet the threads you pointed me to are describing the
problem. Now comes the fight with the application people to get them to
change their code!

ps - I did search on that reason code but didn't get any hits. I guess the
search engine must be case sensitive as I probably typed it in all caps

At 09:43 PM 12/19/2002 +0000, David S Waugh said:
>Hi Jim:
>
>Try looking at the following entries in the Archives:
>
>Item # Date Time Recs Subject
>013919 00/08/10 11:05 16 Re: DRAIN LOCK on changing a partitioning key
>013921 00/08/10 12:46 10 Re: DRAIN LOCK on changing a partitioning key
>039442 02/01/13 06:02 103 Drain lock waits during application processing
>049854 02/07/31 11:23 19 drain failure 00c200ea
>049862 02/07/31 12:13 52 Re: drain failure 00c200ea
>049891 02/08/01 09:29 138 Re: drain failure 00c200ea
>
>I think they have the answer you're looking for. The usual reason
>is: Updating a partitioning key, which causes the row to move from one
>partition to another, will cause DB2 to issue drains for the old & new
>partition, plus all partitions in between.
>
>HTH
>
>David S. Waugh, NCW
>DSW Consulting & Services








Phil Grainger

Re: Application program issuing a drain??
(in response to Tina Hilton)
I was under the impression that the default was NOT to allow updates that
cause rows to move partitions. If so, then someone at Davids site has
already made an informed(?) decision to enable this feature.

Lesson for everyone else, make sure it's DISABLED so people can't sneak this
into production without you knowing about it

Phil Grainger
Computer Associates
Product Manager, DB2
Tel: +44 (0)161 928 9334
Fax: +44 (0)161 941 3775
Mobile: +44 (0)7970 125 752
[login to unmask email]


-----Original Message-----
From: Tina Hilton [mailto:[login to unmask email]
Sent: 20 December 2002 12:17
To: [login to unmask email]
Subject: Re: [DB2-L] Application program issuing a drain??


You can always disable this feature in the zparms (PARTKEYU). Of course,
you'd have to wait until after they changed their code.

-----Original Message-----
From: Jim Harrison [mailto:[login to unmask email]
Sent: December 19, 2002 5:14 PM
To: [login to unmask email]
Subject: Re: Application program issuing a drain??


Thanks. It's a good bet the threads you pointed me to are describing the
problem. Now comes the fight with the application people to get them to
change their code!

ps - I did search on that reason code but didn't get any hits. I guess the
search engine must be case sensitive as I probably typed it in all caps

Christopher Tee

Re: Application program issuing a drain??
(in response to Phil Grainger)
The default is YES (unfortunately!) I think IBM made it the default because
of the way the change was originally implemented via a PTF.

Chris

-----Original Message-----
From: Grainger, Phil [mailto:[login to unmask email]
Sent: 20 December 2002 14:14
To: [login to unmask email]
Subject: Re: Application program issuing a drain??



I was under the impression that the default was NOT to allow updates that
cause rows to move partitions. If so, then someone at Davids site has
already made an informed(?) decision to enable this feature.

Lesson for everyone else, make sure it's DISABLED so people can't sneak this
into production without you knowing about it

Phil Grainger
Computer Associates
Product Manager, DB2
Tel: +44 (0)161 928 9334
Fax: +44 (0)161 941 3775
Mobile: +44 (0)7970 125 752
[login to unmask email]


-----Original Message-----
From: Tina Hilton [ mailto:[login to unmask email]
<mailto:[login to unmask email]> ]
Sent: 20 December 2002 12:17
To: [login to unmask email]
Subject: Re: [DB2-L] Application program issuing a drain??


You can always disable this feature in the zparms (PARTKEYU). Of course,
you'd have to wait until after they changed their code.

-----Original Message-----
From: Jim Harrison [ mailto:[login to unmask email] <mailto:[login to unmask email]> ]
Sent: December 19, 2002 5:14 PM
To: [login to unmask email]
Subject: Re: Application program issuing a drain??


Thanks. It's a good bet the threads you pointed me to are describing the
problem. Now comes the fight with the application people to get them to
change their code!

ps - I did search on that reason code but didn't get any hits. I guess the
search engine must be case sensitive as I probably typed it in all caps



-----------------------------------------------------------------------------------------------------------------------
This e-mail is intended only for the above addressee. It may contain
privileged information. If you are not the addressee you must not copy,
distribute, disclose or use any of the information in it. If you have
received it in error please delete it and immediately notify the sender.

evolvebank.com is a division of Lloyds TSB Bank plc.
Lloyds TSB Bank plc, 71 Lombard Street, London EC3P 3BS. Registered in
England, number 2065. Telephone No: 020 7626 1500
Lloyds TSB Scotland plc, Henry Duncan House, 120 George Street,
Edinburgh EH2 4LH. Registered in Scotland, number 95237. Telephone
No: 0131 225 4555

Lloyds TSB Bank plc and Lloyds TSB Scotland plc are regulated by the
Financial Services Authority and represent only the Scottish Widows
and Lloyds TSB Marketing Group for life assurance, pensions and
investment business.

Signatories to the Banking Codes.
-----------------------------------------------------------------------------------------------------------------------