CURRENTDATA and The Australian solution again...

Thomas Berg

CURRENTDATA and The Australian solution again...
I send this one time again because i didn't get so many answers earlier and we are in the process of report the error to IBM:
I wonder if ANY of You had a experience resembling this ? TIA

We have a problem with -803 in an INSERT with a value originating from incrementing a numeric counter in this way:

UPDATE DB2B.AKTTPARM
SET NUMSEN = NUMSEN + 1
WHERE PARMID = :DCLAKTTPARM.PARMID

and immediatly after:

SELECT NUMSEN
INTO :DCLAKTTPARM.NUMSEN
FROM DB2B.AKTTPARM
WHERE PARMID = :DCLAKTTPARM.PARMID

Whereafter an INSERT with NUMSEN (in another table) and some other values give -803.
(NUMSEN should always be unique)
The interesting with the problem is that several programs (IMS-online) got the same error
AND THE SAME WRONG NUMSEN in a couple (2-3) of minutes.
(The value seems to be from before any UPDATE.)

The ISOLATION was CS and CURRENTDATA was NO.

My question is: Does CURRENTDATA make any difference regardig the result of the SELECT
in the UPDATE-SELECT-sequence above ("The Australian solution") ?
(This have happens at least 2 times the same week (Monday evening and Wednesday morning). After we changed CURRENTDATA to YES this have not happenned again.)

Regards,
Thomas Berg
Föreningssparbanken AB
SWEDEN

Rick Davis

Re: CURRENTDATA and The Australian solution again...
(in response to Thomas Berg)
Thomas,
See DB2 V5 Admin. Guide, bookmanager ref 5.7.5.4.4 Effects of Isolation
Values:
"CURRENTDATA
This option has two effects:
For local access, it tells whether the data upon which your cursor is positioned
must remain identical to (or "current with") the data in the local base table.
For cursors positioned on data in a work file, the CURRENTDATA option has no
effect. This effect only applies to read-only or ambiguous
| cursors in plans or packages bound with CS isolation. For
| SELECT statements in which no cursor is used, such as those
| that return a single row, a lock is not held on the row unless
| you specify WITH RS or WITH RR on the statement."

You might try setting up a small loop...
startloop
lock table in mode exclusive
if good sqlcode
add 1 to number
unlock table
commit
else
try to lock the table a few more times until times > some number of trys
endloop
HTH, Rick


---------------------------------------- Message History
----------------------------------------


From: [login to unmask email] on 10/05/99 12:31 PM GMT

Please respond to [login to unmask email]

To: [login to unmask email]
cc:
Subject: CURRENTDATA and The Australian solution again...


Tim Lowe

Re: CURRENTDATA and The Australian solution again...
(in response to Rick Davis)
Thomas,
I am out of ideas, and I hope that IBM can resolve it. I am very curious and
would like to hear the response from IBM.

I remember that you said that you were using data sharing, and I think that this
would be the key.
I know that with data sharing it is possible to get "old data" from the disk
using isolation=CS and currentdata=NO.
However, since you are doing an UPDATE first, then DB2 would have to lock this
page (or row depending on locksize), and store it in the "local" DB2's
bufferpool.
Since the X-lock from the UPDATE cannot be released by DB2 until commit (and you
already said that there was no commit between the select and update) then you
should still hold the X-lock during the select and therefore noone else could
update the same row. So, the data in the bufferpool should still be valid.
Right?

Are you caching this data in the coupling facility using GBPCACHE?

Thanks,
Tim Lowe
Lead DBA
St. Paul Companies







Thomas Berg <[login to unmask email]> on 10/05/99 07:31:28 AM

Please respond to DB2 Data Base Discussion List <[login to unmask email]>








To: [login to unmask email]

cc: (bcc: Tim Lowe/sfm/spc)



Subject: CURRENTDATA and The Australian solution again...








I send this one time again because i didn't get so many answers earlier and we
are in the process of report the error to IBM:
I wonder if ANY of You had a experience resembling this ? TIA

We have a problem with -803 in an INSERT with a value originating from
incrementing a numeric counter in this way:

UPDATE DB2B.AKTTPARM
SET NUMSEN = NUMSEN + 1
WHERE PARMID = :DCLAKTTPARM.PARMID

and immediatly after:

SELECT NUMSEN
INTO :DCLAKTTPARM.NUMSEN
FROM DB2B.AKTTPARM
WHERE PARMID = :DCLAKTTPARM.PARMID

Whereafter an INSERT with NUMSEN (in another table) and some other values give
-803.
(NUMSEN should always be unique)
The interesting with the problem is that several programs (IMS-online) got the
same error
AND THE SAME WRONG NUMSEN in a couple (2-3) of minutes.
(The value seems to be from before any UPDATE.)

The ISOLATION was CS and CURRENTDATA was NO.

My question is: Does CURRENTDATA make any difference regardig the result of the
SELECT
in the UPDATE-SELECT-sequence above ("The Australian solution") ?
(This have happens at least 2 times the same week (Monday evening and Wednesday
morning). After we changed CURRENTDATA to YES this have not happenned again.)

Regards,
Thomas Berg
F

Rick Davis

Re: CURRENTDATA and The Australian solution again...
(in response to Tim Lowe)
Thomas, Tim,
I tried to send this earlier but the server said it had a problem. I don't
know if you got it or not. Why not just set up a little loop...
startloop
lock table in exclusive mode
if locked
add one to number
select number into variable
commit (I believe this commit releases the exclusive lock)
if not locked
try some number of times
endloop.
HTH, Rick


---------------------------------------- Message History
----------------------------------------


From: [login to unmask email] on 10/06/99 05:13 PM GMT

Please respond to [login to unmask email]

To: [login to unmask email]
cc:
Subject: Re: CURRENTDATA and The Australian solution again...




Thomas,
I am out of ideas, and I hope that IBM can resolve it. I am very curious and
would like to hear the response from IBM.

I remember that you said that you were using data sharing, and I think that this
would be the key.
I know that with data sharing it is possible to get "old data" from the disk
using isolation=CS and currentdata=NO.
However, since you are doing an UPDATE first, then DB2 would have to lock this
page (or row depending on locksize), and store it in the "local" DB2's
bufferpool.
Since the X-lock from the UPDATE cannot be released by DB2 until commit (and you
already said that there was no commit between the select and update) then you
should still hold the X-lock during the select and therefore noone else could
update the same row. So, the data in the bufferpool should still be valid.
Right?

Are you caching this data in the coupling facility using GBPCACHE?

Thanks,
Tim Lowe
Lead DBA
St. Paul Companies







Thomas Berg <[login to unmask email]> on 10/05/99 07:31:28 AM

Please respond to DB2 Data Base Discussion List <[login to unmask email]>








To: [login to unmask email]

cc: (bcc: Tim Lowe/sfm/spc)



Subject: CURRENTDATA and The Australian solution again...








I send this one time again because i didn't get so many answers earlier and we
are in the process of report the error to IBM:
I wonder if ANY of You had a experience resembling this ? TIA

We have a problem with -803 in an INSERT with a value originating from
incrementing a numeric counter in this way:

UPDATE DB2B.AKTTPARM
SET NUMSEN = NUMSEN + 1
WHERE PARMID = :DCLAKTTPARM.PARMID

and immediatly after:

SELECT NUMSEN
INTO :DCLAKTTPARM.NUMSEN
FROM DB2B.AKTTPARM
WHERE PARMID = :DCLAKTTPARM.PARMID

Whereafter an INSERT with NUMSEN (in another table) and some other values give
-803.
(NUMSEN should always be unique)
The interesting with the problem is that several programs (IMS-online) got the
same error
AND THE SAME WRONG NUMSEN in a couple (2-3) of minutes.
(The value seems to be from before any UPDATE.)

The ISOLATION was CS and CURRENTDATA was NO.

My question is: Does CURRENTDATA make any difference regardig the result of the
SELECT
in the UPDATE-SELECT-sequence above ("The Australian solution") ?
(This have happens at least 2 times the same week (Monday evening and Wednesday
morning). After we changed CURRENTDATA to YES this have not happenned again.)

Regards,
Thomas Berg
F