-811 and returned host variable values on Z/OS

Eric Long

-811 and returned host variable values on Z/OS
In the past, there has been code written that assumed that data was
returned to host variables when a -811 SQLCODE was received. Of course
this is contrary to the IBM manual (from pre-V9) which says that population of
host variables is not guaranteed for a -811! But still the code exists and at
least some times data is returned.

I heard in a presentation at a DB2 User Group that in DB2 V9 data will never
be returned during a -811 SQLCODE due to the FETCH FIRST n ROWS
performance improvements.

I would like to verify this since this could affect some of our current coding.

Thanks,
Eric Long

______________________________________________________________________

* IDUG 2009 Melbourne, Australia * 18-20 March * http://IDUG.ORG/Events *
______________________________________________________________________




IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Suresh Sane

Re: -811 and returned host variable values on Z/OS
(in response to Eric Long)

Eric,

I am finding the values are still returned on a -811. They happen to be the second row. No warranties expressed or implied....

Thx
Suresh> Date: Thu, 8 Jan 2009 16:01:13 +0000> From: [login to unmask email]> Subject: [DB2-L] -811 and returned host variable values on Z/OS> To: [login to unmask email]> > In the past, there has been code written that assumed that data was > returned to host variables when a -811 SQLCODE was received. Of course > this is contrary to the IBM manual (from pre-V9) which says that population of > host variables is not guaranteed for a -811! But still the code exists and at > least some times data is returned. > > I heard in a presentation at a DB2 User Group that in DB2 V9 data will never > be returned during a -811 SQLCODE due to the FETCH FIRST n ROWS > performance improvements.> > I would like to verify this since this could affect some of our current coding.> > Thanks, > Eric Long > > ______________________________________________________________________> > * IDUG 2009 Melbourne, Australia * 18-20 March * http://IDUG.ORG/Events *> ______________________________________________________________________> > > > > IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html
_________________________________________________________________
Windows Live™: Keep your life in sync.
http://windowslive.com/explore?ocid=TXT_TAGLM_WL_t1_allup_explore_012009
______________________________________________________________________

* IDUG 2009 Melbourne, Australia * 18-20 March * http://IDUG.ORG/Events *
______________________________________________________________________




IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

M. Khalid Khan

Re: -811 and returned host variable values on Z/OS
(in response to Suresh Sane)
As the manual says data population is not guaranteed, the data your
applications is using is already unreliable. Add "FETCH FIRST 1 ROW ONLY"
if you need the data or use the sqlcode ONLY if the question really is -
are there more than one rows meeting this condition.
HTH
Khalid





"Eric Long" <[login to unmask email]>
Sent by: "DB2 Data Base Discussion List" <[login to unmask email]>
01/08/2009 10:01 AM
Please respond to
"DB2 Database Discussion list at IDUG" <[login to unmask email]>


To
[login to unmask email]
cc

Subject
[DB2-L] -811 and returned host variable values on Z/OS






In the past, there has been code written that assumed that data was
returned to host variables when a -811 SQLCODE was received. Of course
this is contrary to the IBM manual (from pre-V9) which says that
population of
host variables is not guaranteed for a -811! But still the code exists
and at
least some times data is returned.

I heard in a presentation at a DB2 User Group that in DB2 V9 data will
never
be returned during a -811 SQLCODE due to the FETCH FIRST n ROWS
performance improvements.

I would like to verify this since this could affect some of our current
coding.

Thanks,
Eric Long

______________________________________________________________________

* IDUG 2009 Melbourne, Australia * 18-20 March * http://IDUG.ORG/Events *
______________________________________________________________________




IDUG.org was recently updated requiring members to use a new password. You
should have gotten an e-mail with the temporary password assigned to your
account. Please log in and update your member profile. If you are not
already an IDUG.org member, please register at
http://www.idug.org/component/juser/register.html




**********

The information contained in this communication is confidential, private, proprietary, or otherwise privileged and is intended only for the use of the addressee. Unauthorized use, disclosure, distribution or copying is strictly prohibited and may be unlawful. If you have received this communication in error, please notify the sender immediately at (312)653-6000 in Illinois; (800)835-8699 in New Mexico; (918)560-3500 in Oklahoma; or (972)766-6900 in Texas.

**********

______________________________________________________________________

* IDUG 2009 Melbourne, Australia * 18-20 March * http://IDUG.ORG/Events *
______________________________________________________________________




IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Agus Kwee

Re: -811 and returned host variable values on Z/OS
(in response to M. Khalid Khan)

Eric,
I have just tested this in our DB2 z/OS V9 system.
The result was still consistent with the result that I always get on previous DB2 versions:
When SQLCODE -811 is returned, the variables are populated with the values of the second result row.
Regards,
Agus Kwee
Themis Training

----- Original Message -----
From: Eric Long
Date: Thursday, January 8, 2009 12:25 pm
Subject: [DB2-L] -811 and returned host variable values on Z/OS
To: [login to unmask email]

> In the past, there has been code written that assumed that data
> was
> returned to host variables when a -811 SQLCODE was received. Of
> course
> this is contrary to the IBM manual (from pre-V9) which says that
> population of
> host variables is not guaranteed for a -811! But still the code
> exists and at
> least some times data is returned.
>
> I heard in a presentation at a DB2 User Group that in DB2 V9
> data will never
> be returned during a -811 SQLCODE due to the FETCH FIRST n ROWS
> performance improvements.
>
> I would like to verify this since this could affect some of our
> current coding.
>
> Thanks,
> Eric Long
>
> ______________________________________________________________________
>
> * IDUG 2009 Melbourne, Australia * 18-20 March *
> http://IDUG.ORG/Events *
> ______________________________________________________________________
>
>
>
>
> IDUG.org was recently updated requiring members to use a new
> password. You should have gotten an e-mail with the temporary
> password assigned to your account. Please log in and update your
> member profile. If you are not already an IDUG.org member,
> please register at http://www.idug.org/component/juser/register.html
>

______________________________________________________________________

* IDUG 2009 Melbourne, Australia * 18-20 March * http://IDUG.ORG/Events *
______________________________________________________________________




IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Richard Fazio

Re: -811 and returned host variable values on Z/OS
(in response to Agus Kwee)
Data received from SQLCODE811 has always been considered GARBAGE.

Any code that depends upon data values presented back from SQLCODE811 is in error.

If IBM Cleaned up processing to ensure that the "garbage" is officially suppressed. This just closes a hole that's been confusing the programming community for years.

So, whether IBM does indeed suppress this data in V9 or not is really not the point. The data presented back from the data engine is unpredictable so, under any release of DB2 I would say if an SQLCODE811 occurs there are only three valid options:

1) You only care about presence of data based upon a where clause. This is true and move forward in the program, but data in host variables is ignored.

2) One or more rows from the SQL statement is desired. Data returned in the host variables is still ignored. So, open a cursor and fetch 1 - n rows.

3) This is an illogical solution...generate appropriate diagnostics and fail the process.

This is always a tough sell to programmers because the empirical evidence suggests that this is the right thing to do (it looks like good data in the program).

They should fix their code.
My 2¢.

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Eric Long
Sent: Thursday, January 08, 2009 10:01 AM
To: [login to unmask email]
Subject: [DB2-L] -811 and returned host variable values on Z/OS

In the past, there has been code written that assumed that data was
returned to host variables when a -811 SQLCODE was received. Of course
this is contrary to the IBM manual (from pre-V9) which says that population of
host variables is not guaranteed for a -811! But still the code exists and at
least some times data is returned.

I heard in a presentation at a DB2 User Group that in DB2 V9 data will never
be returned during a -811 SQLCODE due to the FETCH FIRST n ROWS
performance improvements.

I would like to verify this since this could affect some of our current coding.

Thanks,
Eric Long

______________________________________________________________________

* IDUG 2009 Melbourne, Australia * 18-20 March * http://IDUG.ORG/Events *
______________________________________________________________________




IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

______________________________________________________________________

* IDUG 2009 Melbourne, Australia * 18-20 March * http://IDUG.ORG/Events *
______________________________________________________________________




IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

David Seibert

Re: -811 and returned host variable values on Z/OS
(in response to Richard Fazio)
Here, here!!!

This is what the DBA community I associate with have been telling programming staff and others for 19 years!

Well-stated, Rich. (as usual)


Dave


The contents of this e-mail are intended for the named addressee only. It contains information that may be confidential. Unless you are the named addressee or an authorized designee, you may not copy or use it, or disclose it to anyone else. If you received it in error please notify us immediately and then destroy it.

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Fazio, Richard
Sent: Thursday, January 08, 2009 2:27 PM
To: [login to unmask email]
Subject: Re: [DB2-L] -811 and returned host variable values on Z/OS

Data received from SQLCODE811 has always been considered GARBAGE.

Any code that depends upon data values presented back from SQLCODE811 is in error.

If IBM Cleaned up processing to ensure that the "garbage" is officially suppressed. This just closes a hole that's been confusing the programming community for years.

So, whether IBM does indeed suppress this data in V9 or not is really not the point. The data presented back from the data engine is unpredictable so, under any release of DB2 I would say if an SQLCODE811 occurs there are only three valid options:

1) You only care about presence of data based upon a where clause. This is true and move forward in the program, but data in host variables is ignored.

2) One or more rows from the SQL statement is desired. Data returned in the host variables is still ignored. So, open a cursor and fetch 1 - n rows.

3) This is an illogical solution...generate appropriate diagnostics and fail the process.

This is always a tough sell to programmers because the empirical evidence suggests that this is the right thing to do (it looks like good data in the program).

They should fix their code.
My 2¢.

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Eric Long
Sent: Thursday, January 08, 2009 10:01 AM
To: [login to unmask email]
Subject: [DB2-L] -811 and returned host variable values on Z/OS

In the past, there has been code written that assumed that data was returned to host variables when a -811 SQLCODE was received. Of course this is contrary to the IBM manual (from pre-V9) which says that population of host variables is not guaranteed for a -811! But still the code exists and at
least some times data is returned.

I heard in a presentation at a DB2 User Group that in DB2 V9 data will never be returned during a -811 SQLCODE due to the FETCH FIRST n ROWS performance improvements.

I would like to verify this since this could affect some of our current coding.

Thanks,
Eric Long

______________________________________________________________________

* IDUG 2009 Melbourne, Australia * 18-20 March * http://IDUG.ORG/Events * ______________________________________________________________________




IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

______________________________________________________________________

* IDUG 2009 Melbourne, Australia * 18-20 March * http://IDUG.ORG/Events * ______________________________________________________________________




IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

______________________________________________________________________

* IDUG 2009 Melbourne, Australia * 18-20 March * http://IDUG.ORG/Events *
______________________________________________________________________




IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Shery hepp

Re: -811 and returned host variable values on Z/OS
(in response to David Seibert)
Hi Eric- I would be very interested in hearing what you find out on this question.

Thanks- Shery Hepp

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Eric Long
Sent: Thursday, January 08, 2009 9:01 AM
To: [login to unmask email]
Subject: [DB2-L] -811 and returned host variable values on Z/OS

In the past, there has been code written that assumed that data was
returned to host variables when a -811 SQLCODE was received. Of course
this is contrary to the IBM manual (from pre-V9) which says that population of
host variables is not guaranteed for a -811! But still the code exists and at
least some times data is returned.

I heard in a presentation at a DB2 User Group that in DB2 V9 data will never
be returned during a -811 SQLCODE due to the FETCH FIRST n ROWS
performance improvements.

I would like to verify this since this could affect some of our current coding.

Thanks,
Eric Long

______________________________________________________________________

* IDUG 2009 Melbourne, Australia * 18-20 March * http://IDUG.ORG/Events *
______________________________________________________________________




IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Roger Miller

Re: -811 and returned host variable values on Z/OS
(in response to Shery hepp)
When DB2 works as it says, it's not a bug. As I remember this one, some of
the distributed protocols have been enforcing the rule back to V2.3.
The books documented that data could not be trusted for negative SQLCODEs
back about that far. If your host variables have valid values, then you might
be doing something over again, as there is no way to tell. In short, trusting
the untrustable is a bad idea, with about 15 years of warning. We don't have
lab sources for how something works other than documented, so I'd guess
that you won't get much.

When we don't design it that way, don't code it that way, don't test it that
way, and don't document it that way, maybe it won't work.

Roger Miller, DB2 for z/OS

On Thu, 8 Jan 2009 17:55:31 -0700, Hepp Shery C
<[login to unmask email]> wrote:

>Hi Eric- I would be very interested in hearing what you find out on this
question.
>
>Thanks- Shery Hepp
>
>-----Original Message-----
>From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Eric Long
>Sent: Thursday, January 08, 2009 9:01 AM
>To: [login to unmask email]
>Subject: [DB2-L] -811 and returned host variable values on Z/OS
>
>In the past, there has been code written that assumed that data was
>returned to host variables when a -811 SQLCODE was received. Of course
>this is contrary to the IBM manual (from pre-V9) which says that population
of
>host variables is not guaranteed for a -811! But still the code exists and at
>least some times data is returned.
>
>I heard in a presentation at a DB2 User Group that in DB2 V9 data will never
>be returned during a -811 SQLCODE due to the FETCH FIRST n ROWS
>performance improvements.
>
>I would like to verify this since this could affect some of our current coding.
>
>Thanks,
>Eric Long
>


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Adam Baldwin

Re: -811 and returned host variable values on Z/OS
(in response to Roger Miller)
Just to add to what Roger says in his posting, we did extensive testing of this
when we had V2.3 in Beta, and although host variables were populated, the
content varied. The only "sure thing" was that that the -811 meant that there
were/are more than one qualifying row.

Cheers, Adam


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Don Leahy

Re: -811 and returned host variable values on Z/OS
(in response to Adam Baldwin)
Amen to that.

Some programmers have been relying on this apparent behavior for
years. I tell them that their programs are "working by coincidence"
and could break at any time.

On Thu, Jan 8, 2009 at 2:27 PM, Fazio, Richard <[login to unmask email]> wrote:
> Data received from SQLCODE811 has always been considered GARBAGE.
>
> Any code that depends upon data values presented back from SQLCODE811 is in error.
>
> If IBM Cleaned up processing to ensure that the "garbage" is officially suppressed. This just closes a hole that's been confusing the programming community for years.
>
> So, whether IBM does indeed suppress this data in V9 or not is really not the point. The data presented back from the data engine is unpredictable so, under any release of DB2 I would say if an SQLCODE811 occurs there are only three valid options:
>
> 1) You only care about presence of data based upon a where clause. This is true and move forward in the program, but data in host variables is ignored.
>
> 2) One or more rows from the SQL statement is desired. Data returned in the host variables is still ignored. So, open a cursor and fetch 1 - n rows.
>
> 3) This is an illogical solution...generate appropriate diagnostics and fail the process.
>
> This is always a tough sell to programmers because the empirical evidence suggests that this is the right thing to do (it looks like good data in the program).
>
> They should fix their code.
> My 2¢.
>
> -----Original Message-----
> From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Eric Long
> Sent: Thursday, January 08, 2009 10:01 AM
> To: [login to unmask email]
> Subject: [DB2-L] -811 and returned host variable values on Z/OS
>
> In the past, there has been code written that assumed that data was
> returned to host variables when a -811 SQLCODE was received. Of course
> this is contrary to the IBM manual (from pre-V9) which says that population of
> host variables is not guaranteed for a -811! But still the code exists and at
> least some times data is returned.
>
> I heard in a presentation at a DB2 User Group that in DB2 V9 data will never
> be returned during a -811 SQLCODE due to the FETCH FIRST n ROWS
> performance improvements.
>
> I would like to verify this since this could affect some of our current coding.
>
> Thanks,
> Eric Long
>
> ______________________________________________________________________
>
> * IDUG 2009 Melbourne, Australia * 18-20 March * http://IDUG.ORG/Events *
> ______________________________________________________________________
>
>
>
>
> IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html
>
> ______________________________________________________________________
>
> * IDUG 2009 Melbourne, Australia * 18-20 March * http://IDUG.ORG/Events *
> ______________________________________________________________________
>
>
>
>
> IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html
>


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Troy Coleman

Re: -811 and returned host variable values on Z/OS
(in response to Don Leahy)
I agree with you Rich.
I've been telling people this for years. But I'm guessing that since no
one has listened to the DBA's, IBM came out with the "FETCH FIRST 1 ROW
ONLY".

Troy

Fazio, Richard wrote:
> Data received from SQLCODE811 has always been considered GARBAGE.
>
> Any code that depends upon data values presented back from SQLCODE811 is in error.
>
> If IBM Cleaned up processing to ensure that the "garbage" is officially suppressed. This just closes a hole that's been confusing the programming community for years.
>
> So, whether IBM does indeed suppress this data in V9 or not is really not the point. The data presented back from the data engine is unpredictable so, under any release of DB2 I would say if an SQLCODE811 occurs there are only three valid options:
>
> 1) You only care about presence of data based upon a where clause. This is true and move forward in the program, but data in host variables is ignored.
>
> 2) One or more rows from the SQL statement is desired. Data returned in the host variables is still ignored. So, open a cursor and fetch 1 - n rows.
>
> 3) This is an illogical solution...generate appropriate diagnostics and fail the process.
>
> This is always a tough sell to programmers because the empirical evidence suggests that this is the right thing to do (it looks like good data in the program).
>
> They should fix their code.
> My 2¢.
>
> -----Original Message-----
> From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Eric Long
> Sent: Thursday, January 08, 2009 10:01 AM
> To: [login to unmask email]
> Subject: [DB2-L] -811 and returned host variable values on Z/OS
>
> In the past, there has been code written that assumed that data was
> returned to host variables when a -811 SQLCODE was received. Of course
> this is contrary to the IBM manual (from pre-V9) which says that population of
> host variables is not guaranteed for a -811! But still the code exists and at
> least some times data is returned.
>
> I heard in a presentation at a DB2 User Group that in DB2 V9 data will never
> be returned during a -811 SQLCODE due to the FETCH FIRST n ROWS
> performance improvements.
>
> I would like to verify this since this could affect some of our current coding.
>
> Thanks,
> Eric Long
>
> ______________________________________________________________________
>
> * IDUG 2009 Melbourne, Australia * 18-20 March * http://IDUG.ORG/Events *
> ______________________________________________________________________
>
>
>
>
> IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html
>
> ______________________________________________________________________
>
> * IDUG 2009 Melbourne, Australia * 18-20 March * http://IDUG.ORG/Events *
> ______________________________________________________________________
>
>
>
>
> IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html
>
>

______________________________________________________________________

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




IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html