R: [DB2-L] SQLCODE -811 - Not expected return code

Mauro Moschelli

R: [DB2-L] SQLCODE -811 - Not expected return code
When you update a column present in an index, DB2 will delete and insert the
index entry in the new position. If the new key is greater than the previous
one, I think a query could possibly read the same row twice, once with the
old values and again with the new values. Depending on how fast the query
is, the event could be very rare.

HTH

Mauro Moschelli
Intesa Sanpaolo S.p.A.

IBM Certified Database Administrator - DB2 UDB V8.1 for z/OS




_____

Da: DB2 Data Base Discussion List [mailto:[login to unmask email] Per conto di
Barat Robert
Inviato: venerdì 5 dicembre 2008 4.48
A: [login to unmask email]
Oggetto: Re: [DB2-L] SQLCODE -811 - Not expected return code


Hello Faz,
Thank you for the reply.
I have only recently started in the DBA role so I will do some research on
the CHECK INDEX command and try that.
The table has had an online reorg recently (before the problem occured
however) as a new index was added recently for performance reasons.

The index that the 'select' query is using (index only = 'Y') is different
to the index (index only = 'Y') 'update' the other transaction is doing.
However, the index used by the select query is updated, as the column being
updated exists on this index. (i have viewed the log records and can see the
exact update performed at the time the condition was returned)

I have noticed now the developers have included extra output in the code
where this return code occurs, which should give us a little more
information to work with. If the problem persists, I will discuss with the
developers about adding extra logic using cursors to handle the -811 and
perhaps capture more information. This should show if there is an
un-initialized storage variable which i could not determine when stepping
through the logic in the code.

Cheers
-rob

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of Fazio, Richard
Sent: Thursday, 4 December 2008 12:49 PM
To: [login to unmask email]
Subject: Re: [DB2-L] SQLCODE -811 - Not expected return code



Lock avoidance is a wonderful thing…except in cases like this. I’m
wondering if you are going in with differing access paths using data only
access in one case and index only access in another. I assume you ran
CHECK INDEX on all the tables involved just to make sure that you do not
have a broken index…yes?



As far as extra doc, I had a very illogical condition like this. When I hit
the condition I snapped a dump. I discovered my error with ease
(un-initialized storage).



However, in this case, I thing it would be more helpful to see the data.
Can you change the statement to an Open cursor/ Fetch loop? If you get a
second row back from the cursor start gathering all the data from the cursor
and abend the transaction manually. It may cause a bit of overhead
(depending upon the frequency of the transaction), but at least you would be
sure of the data being observed by the application.



Best of luck,

faz


_____


From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of Barat Robert
Sent: Wednesday, December 03, 2008 4:55 PM
To: [login to unmask email]
Subject: [DB2-L] SQLCODE -811 - Not expected return code



Good day to all,



We have recently experienced on rare occasions instances of an 81J (-811)
error on a query where this return code is not expected.

We are using DB2 V8 CM on z/os.



The query is similar to below :



EXEC SQL

SELECT column3

INTO :variable

FROM table1 A

,table2 B

WHERE A.column1 = :dclgen.variable1

AND A.column2 = :dclgen.variable2

AND A.column3 = B.column1

END-EXEC



Table 1 has column1, column2 as the unique index

Table 2 has column1 as the unique index (Primary key)



From these table definitions, I would expect 0 or 1 row from table1 to be
selected by specifying both columns on the unique index,

and then by joining on the unique index to Table 2, would expect either 0 or
1 row returned depending on existance.



A couple of observations from the analysis we've performed.

* if we run query in spufi using the keys from the query in error, we
receive 1 row as expected

* the row in table1 is updated by another transaction around the same time
this select is performed (the transactions appear to be running at the same
time in different CICS regions)

* I have examined the code and cannot see anything unusual or unexpected.



My questions to the group are

* Is this at all possible due to the concurrent nature of the 2
transactions? (note the query does not specify 'WITH UR' )

* Is there a way I can gather additional diagnostics at run time when this
condition occurs? (we output the key values from working storage to the cics
log only at the moment)





Robert Barat

DB2 Database Administrator

Woolworths IT - NES Data Services

Woolworths Limited



***********************************************************

CAUTION: This email and files included in its transmission

are solely intended for the use of the addressee(s) and may

contain information that is confidential and privileged.

If you receive this email in error, please advise us

immediately and delete it without copying the contents

contained within. Woolworths Limited (including its group

of companies) do not accept liability for the views

expressed within or the consequences of any computer

viruses that may be transmitted with this email. The

contents are also subject to copyright. No part of it

should be reproduced, adapted or transmitted without the

written consent of the copyright owner.

***********************************************************


_____

< http://idug.org/lsNA > IDUG 2009 - North America * May 11-15, 2009 *
Denver, CO, USA

The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L
list archives, FAQ, and delivery preferences are at IDUG.ORG
< http://www.idug.org/lsidug > under the Listserv tab. While at the site, you
can also access the IDUG Online Learning Center, Tech Library and Code
Place, see the latest IDUG conference information
< http://www.idug.org/lsconf > , and much more. If you have not yet signed up
for Basic Membership in IDUG, available at no cost, click on Member
< http://www.idug.org/lsms > Services


_____

< http://idug.org/lsNA > IDUG 2009 - North America * May 11-15, 2009 *
Denver, CO, USA

The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L
list archives, FAQ, and delivery preferences are at IDUG.ORG
< http://www.idug.org/lsidug > under the Listserv tab. While at the site, you
can also access the IDUG Online Learning Center, Tech Library and Code
Place, see the latest IDUG conference information
< http://www.idug.org/lsconf > , and much more. If you have not yet signed up
for Basic Membership in IDUG, available at no cost, click on Member
< http://www.idug.org/lsms > Services

***********************************************************

CAUTION: This email and files included in its transmission

are solely intended for the use of the addressee(s) and may

contain information that is confidential and privileged.

If you receive this email in error, please advise us

immediately and delete it without copying the contents

contained within. Woolworths Limited (including its group

of companies) do not accept liability for the views

expressed within or the consequences of any computer

viruses that may be transmitted with this email. The

contents are also subject to copyright. No part of it

should be reproduced, adapted or transmitted without the

written consent of the copyright owner.

***********************************************************


_____

< http://idug.org/lsNA > IDUG 2009 - North America * May 11-15, 2009 *
Denver, CO, USA

The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L
list archives, FAQ, and delivery preferences are at IDUG.ORG
< http://www.idug.org/lsidug > under the Listserv tab. While at the site, you
can also access the IDUG Online Learning Center, Tech Library and Code
Place, see the latest IDUG conference information
< http://www.idug.org/lsconf > , and much more. If you have not yet signed up
for Basic Membership in IDUG, available at no cost, click on Member
< http://www.idug.org/lsms > Services


______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Phil Grainger

Re: R: [DB2-L] SQLCODE -811 - Not expected return code
(in response to Mauro Moschelli)
Outside guess

are either of your indexes created with "WHERE NOT NULL"?

If so, this means you can have MORE THAN ONE null value in the key - multiple nulls for the same column do NOT violate uniqueness when "WHERE NOT NULL" is specified

SO, you could have two rows in table 1 with "A" for column1 both with the same value for column2 but null for column1

Now when you join to table2 you could get two result rows back (especially if the application code is not handling nulls properly!)

Just a thought

Phil Grainger
CA

________________________________

From: DB2 Data Base Discussion List on behalf of Moschelli Mauro
Sent: Fri 05/12/2008 08:31
To: [login to unmask email]
Subject: [DB2-L] R: [DB2-L] SQLCODE -811 - Not expected return code


When you update a column present in an index, DB2 will delete and insert the index entry in the new position. If the new key is greater than the previous one, I think a query could possibly read the same row twice, once with the old values and again with the new values. Depending on how fast the query is, the event could be very rare.

HTH

Mauro Moschelli
Intesa Sanpaolo S.p.A.

IBM Certified Database Administrator - DB2 UDB V8.1 for z/OS




________________________________

Da: DB2 Data Base Discussion List [mailto:[login to unmask email] Per conto di Barat Robert
Inviato: venerdì 5 dicembre 2008 4.48
A: [login to unmask email]
Oggetto: Re: [DB2-L] SQLCODE -811 - Not expected return code


Hello Faz,
Thank you for the reply.
I have only recently started in the DBA role so I will do some research on the CHECK INDEX command and try that.
The table has had an online reorg recently (before the problem occured however) as a new index was added recently for performance reasons.

The index that the 'select' query is using (index only = 'Y') is different to the index (index only = 'Y') 'update' the other transaction is doing. However, the index used by the select query is updated, as the column being updated exists on this index. (i have viewed the log records and can see the exact update performed at the time the condition was returned)

I have noticed now the developers have included extra output in the code where this return code occurs, which should give us a little more information to work with. If the problem persists, I will discuss with the developers about adding extra logic using cursors to handle the -811 and perhaps capture more information. This should show if there is an un-initialized storage variable which i could not determine when stepping through the logic in the code.

Cheers
-rob

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Fazio, Richard
Sent: Thursday, 4 December 2008 12:49 PM
To: [login to unmask email]
Subject: Re: [DB2-L] SQLCODE -811 - Not expected return code



Lock avoidance is a wonderful thing...except in cases like this. I'm wondering if you are going in with differing access paths using data only access in one case and index only access in another. I assume you ran CHECK INDEX on all the tables involved just to make sure that you do not have a broken index...yes?



As far as extra doc, I had a very illogical condition like this. When I hit the condition I snapped a dump. I discovered my error with ease (un-initialized storage).



However, in this case, I thing it would be more helpful to see the data. Can you change the statement to an Open cursor/ Fetch loop? If you get a second row back from the cursor start gathering all the data from the cursor and abend the transaction manually. It may cause a bit of overhead (depending upon the frequency of the transaction), but at least you would be sure of the data being observed by the application.



Best of luck,

faz


________________________________


From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Barat Robert
Sent: Wednesday, December 03, 2008 4:55 PM
To: [login to unmask email]
Subject: [DB2-L] SQLCODE -811 - Not expected return code



Good day to all,



We have recently experienced on rare occasions instances of an 81J (-811) error on a query where this return code is not expected.

We are using DB2 V8 CM on z/os.



The query is similar to below :



EXEC SQL

SELECT column3

INTO :variable

FROM table1 A

,table2 B

WHERE A.column1 = :dclgen.variable1

AND A.column2 = :dclgen.variable2

AND A.column3 = B.column1

END-EXEC



Table 1 has column1, column2 as the unique index

Table 2 has column1 as the unique index (Primary key)



From these table definitions, I would expect 0 or 1 row from table1 to be selected by specifying both columns on the unique index,

and then by joining on the unique index to Table 2, would expect either 0 or 1 row returned depending on existance.



A couple of observations from the analysis we've performed.

* if we run query in spufi using the keys from the query in error, we receive 1 row as expected

* the row in table1 is updated by another transaction around the same time this select is performed (the transactions appear to be running at the same time in different CICS regions)

* I have examined the code and cannot see anything unusual or unexpected.



My questions to the group are

* Is this at all possible due to the concurrent nature of the 2 transactions? (note the query does not specify 'WITH UR' )

* Is there a way I can gather additional diagnostics at run time when this condition occurs? (we output the key values from working storage to the cics log only at the moment)





Robert Barat

DB2 Database Administrator

Woolworths IT - NES Data Services

Woolworths Limited



***********************************************************

CAUTION: This email and files included in its transmission

are solely intended for the use of the addressee(s) and may

contain information that is confidential and privileged.

If you receive this email in error, please advise us

immediately and delete it without copying the contents

contained within. Woolworths Limited (including its group

of companies) do not accept liability for the views

expressed within or the consequences of any computer

viruses that may be transmitted with this email. The

contents are also subject to copyright. No part of it

should be reproduced, adapted or transmitted without the

written consent of the copyright owner.

***********************************************************


________________________________

IDUG 2009 - North America * May 11-15, 2009 * Denver, CO, USA < http://idug.org/lsNA >

The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at IDUG.ORG <http://www.idug.org/lsidug> under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information <http://www.idug.org/lsconf> , and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services < http://www.idug.org/lsms >


________________________________

IDUG 2009 - North America * May 11-15, 2009 * Denver, CO, USA < http://idug.org/lsNA >

The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at IDUG.ORG <http://www.idug.org/lsidug> under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information <http://www.idug.org/lsconf> , and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services < http://www.idug.org/lsms >

***********************************************************

CAUTION: This email and files included in its transmission

are solely intended for the use of the addressee(s) and may

contain information that is confidential and privileged.

If you receive this email in error, please advise us

immediately and delete it without copying the contents

contained within. Woolworths Limited (including its group

of companies) do not accept liability for the views

expressed within or the consequences of any computer

viruses that may be transmitted with this email. The

contents are also subject to copyright. No part of it

should be reproduced, adapted or transmitted without the

written consent of the copyright owner.

***********************************************************


________________________________

IDUG 2009 - North America * May 11-15, 2009 * Denver, CO, USA < http://idug.org/lsNA >

The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at IDUG.ORG <http://www.idug.org/lsidug> under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information <http://www.idug.org/lsconf> , and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services < http://www.idug.org/lsms >


________________________________

IDUG 2009 - North America * May 11-15, 2009 * Denver, CO, USA < http://idug.org/lsNA >

The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at IDUG.ORG <http://www.idug.org/lsidug> under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information <http://www.idug.org/lsconf> , and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services < http://www.idug.org/lsms >


______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms