Data Length Mismatches.

Harish Gangadharanunni

Data Length Mismatches.
Esteemed List,

v6 DB2 for OS/390

Is the following predicate a Stage 2 predicate.

Col_A = :H

Column Col_A is S9(9) COMP, and the Host variable H, S9(4) COMP. The
EXPLAIN of the program shows a matching index scan using Col_A. My question
is since the value compared against the column is a host variable, is
the "actual decision" regarding the Stage processing taken at run-time in
this case. Does DB2 V6, consider the example provided as Stage 1 since the
length of the host variable is less than that of the column definition.

DB2 Admin. Guide suggests that when the length of the host variable is
greater than the column compared to the predicate qualifies as a Stage 2
predicate. The App. programming Guide suggests that when the lengths
disagree the predicate qualifies as a Stage 2 predicate. In cases of BIN
and CHAR fields, does the lengths only have to disagree or does the lengths
have to be greater than the column definition.

Thank you.

Harish.



David Cohn

Re: Data Length Mismatches.
(in response to Harish Gangadharanunni)
Hi Harish,
The optimizer assumes that the terms of the predicate match. That is why
the PLAN_TABLE shows you a stage 1 predicate. The actual evaluation stage is
determined at run time.
Hope this helps,
David Cohn
Senior Systems Advisor
Themis Training
IBM Certified Solutions Expert:
DB2 V7.1 Database Administration
DB2 V7.1 Family Application Development



----- Original Message -----
From: "Harish G" <[login to unmask email]>
Newsgroups: bit.listserv.db2-l
To: <[login to unmask email]>
Sent: Monday, December 23, 2002 8:25 AM
Subject: Data Length Mismatches.


> Esteemed List,
>
> v6 DB2 for OS/390
>
> Is the following predicate a Stage 2 predicate.
>
> Col_A = :H
>
> Column Col_A is S9(9) COMP, and the Host variable H, S9(4) COMP. The
> EXPLAIN of the program shows a matching index scan using Col_A. My
question
> is since the value compared against the column is a host variable, is
> the "actual decision" regarding the Stage processing taken at run-time in
> this case. Does DB2 V6, consider the example provided as Stage 1 since the
> length of the host variable is less than that of the column definition.
>
> DB2 Admin. Guide suggests that when the length of the host variable is
> greater than the column compared to the predicate qualifies as a Stage 2
> predicate. The App. programming Guide suggests that when the lengths
> disagree the predicate qualifies as a Stage 2 predicate. In cases of BIN
> and CHAR fields, does the lengths only have to disagree or does the
lengths
> have to be greater than the column definition.
>
> Thank you.
>
> Harish.
>
>
>


>



Harish Gangadharanunni

Re: Data Length Mismatches.
(in response to David Cohn)
Thank you, Dave. In this particular case that I was referring to, does the
predicate qualify as a Stage 2 predicate at run-time. How can I determine
the actual Stage of processing at run-time. Monitors ? Traces ?

Harish.



Terry Purcell

Re: Data Length Mismatches.
(in response to Harish Gangadharanunni)
David/Harish,

I don't necessarily agree.

The predicate is in fact stage 1, as indicated by the plan table output.
This is determined at bind time.

Generally speaking, if the host variable (or literal) can be promoted (thus
implying it is currently smaller) than the column, then the predicate is
stage 1 and indexable. This is provided that the length difference is within
the same datatype, such as CHAR or DEC. INT & SMALLINT should also be
considered the same datatype.

If the host variable is larger, and thus the column must be promoted to
match the host variable, then the comparison is stage 2.

Instead of having to worry about this however, you should use the DCLGEN
supplied variables wherever possible.

Regards
Terry Purcell
Yevich Lawson & Assoc Inc (YL&A)
IBM Gold Consultant - DB2
IBM Certified Solutions Expert
DB2 V7 Database Administration OS/390
http://www.ylassoc.com


-----Original Message-----
From: DB2 Data Base Discussion List
[mailto:[login to unmask email]On Behalf Of David Cohn
Sent: Monday, December 23, 2002 11:14 AM
To: [login to unmask email]
Subject: Re: Data Length Mismatches.


Hi Harish,
The optimizer assumes that the terms of the predicate match. That is why
the PLAN_TABLE shows you a stage 1 predicate. The actual evaluation stage is
determined at run time.
Hope this helps,
David Cohn
Senior Systems Advisor
Themis Training
IBM Certified Solutions Expert:
DB2 V7.1 Database Administration
DB2 V7.1 Family Application Development



----- Original Message -----
From: "Harish G" <[login to unmask email]>
Newsgroups: bit.listserv.db2-l
To: <[login to unmask email]>
Sent: Monday, December 23, 2002 8:25 AM
Subject: Data Length Mismatches.


> Esteemed List,
>
> v6 DB2 for OS/390
>
> Is the following predicate a Stage 2 predicate.
>
> Col_A = :H
>
> Column Col_A is S9(9) COMP, and the Host variable H, S9(4) COMP. The
> EXPLAIN of the program shows a matching index scan using Col_A. My
question
> is since the value compared against the column is a host variable, is
> the "actual decision" regarding the Stage processing taken at run-time in
> this case. Does DB2 V6, consider the example provided as Stage 1 since the
> length of the host variable is less than that of the column definition.
>
> DB2 Admin. Guide suggests that when the length of the host variable is
> greater than the column compared to the predicate qualifies as a Stage 2
> predicate. The App. programming Guide suggests that when the lengths
> disagree the predicate qualifies as a Stage 2 predicate. In cases of BIN
> and CHAR fields, does the lengths only have to disagree or does the
lengths
> have to be greater than the column definition.
>
> Thank you.
>
> Harish.
>
>
>


>








David Cohn

Re: Data Length Mismatches.
(in response to Terry Purcell)
Hi Terry,
We've said the same thing two different ways. The reason the predicate
is stage 1 is because the optimizer assumes that both terms match. I was not
specific about run time. When at run time the terms don't match the actual
evaluation (that is what DB2 will do)takes place.
Regards,
David Cohn
Senior Systems Advisor
Themis Training
IBM Certified Solutions Expert:
DB2 V7.1 Database Administration
DB2 V7.1 Family Application Development



----- Original Message -----
From: "Terry Purcell" <[login to unmask email]>
Newsgroups: bit.listserv.db2-l
To: <[login to unmask email]>
Sent: Monday, December 23, 2002 9:53 AM
Subject: Re: Data Length Mismatches.


> David/Harish,
>
> I don't necessarily agree.
>
> The predicate is in fact stage 1, as indicated by the plan table output.
> This is determined at bind time.
>
> Generally speaking, if the host variable (or literal) can be promoted
(thus
> implying it is currently smaller) than the column, then the predicate is
> stage 1 and indexable. This is provided that the length difference is
within
> the same datatype, such as CHAR or DEC. INT & SMALLINT should also be
> considered the same datatype.
>
> If the host variable is larger, and thus the column must be promoted to
> match the host variable, then the comparison is stage 2.
>
> Instead of having to worry about this however, you should use the DCLGEN
> supplied variables wherever possible.
>
> Regards
> Terry Purcell
> Yevich Lawson & Assoc Inc (YL&A)
> IBM Gold Consultant - DB2
> IBM Certified Solutions Expert
> DB2 V7 Database Administration OS/390
> http://www.ylassoc.com
>
>
> -----Original Message-----
> From: DB2 Data Base Discussion List
> [mailto:[login to unmask email]On Behalf Of David Cohn
> Sent: Monday, December 23, 2002 11:14 AM
> To: [login to unmask email]
> Subject: Re: Data Length Mismatches.
>
>
> Hi Harish,
> The optimizer assumes that the terms of the predicate match. That is
why
> the PLAN_TABLE shows you a stage 1 predicate. The actual evaluation stage
is
> determined at run time.
> Hope this helps,
> David Cohn
> Senior Systems Advisor
> Themis Training
> IBM Certified Solutions Expert:
> DB2 V7.1 Database Administration
> DB2 V7.1 Family Application Development
>
>
>
> ----- Original Message -----
> From: "Harish G" <[login to unmask email]>
> Newsgroups: bit.listserv.db2-l
> To: <[login to unmask email]>
> Sent: Monday, December 23, 2002 8:25 AM
> Subject: Data Length Mismatches.
>
>
> > Esteemed List,
> >
> > v6 DB2 for OS/390
> >
> > Is the following predicate a Stage 2 predicate.
> >
> > Col_A = :H
> >
> > Column Col_A is S9(9) COMP, and the Host variable H, S9(4) COMP. The
> > EXPLAIN of the program shows a matching index scan using Col_A. My
> question
> > is since the value compared against the column is a host variable, is
> > the "actual decision" regarding the Stage processing taken at run-time
in
> > this case. Does DB2 V6, consider the example provided as Stage 1 since
the
> > length of the host variable is less than that of the column definition.
> >
> > DB2 Admin. Guide suggests that when the length of the host variable is
> > greater than the column compared to the predicate qualifies as a Stage 2
> > predicate. The App. programming Guide suggests that when the lengths
> > disagree the predicate qualifies as a Stage 2 predicate. In cases of BIN
> > and CHAR fields, does the lengths only have to disagree or does the
> lengths
> > have to be greater than the column definition.
> >
> > Thank you.
> >
> > Harish.
> >
> >
> >
> the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list
can
>
> >
>
>
>
the
> DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can
be
>
>
>
>


>



Terry Purcell

Re: Data Length Mismatches.
(in response to David Cohn)
David,

When you say the the optimizer assumes the datatypes match, then this
implies to me that you are suggesting that at runtime it may demote this to
a stage 2 predicate. In fact the optimizer can determine at bindtime that it
will be able to match.

If the reverse is true, thus that the column is shorter than the host
variable, then the optimizer can also determine at bindtime that it is a
stage 2 predicate. The plan table output will NOT show this as a matching
index column.

This was the focus of Harish's question. And I believe we gave two different
answers. I am suggesting that this determination is at bindtime, and
although you say you are "not specific about runtime", it appears you are
suggesting that this determination may change at runtime. Otherwise I may be
misinterpreting your answer.

Regards
Terry Purcell
Yevich Lawson & Assoc Inc (YL&A)
IBM Gold Consultant - DB2
IBM Certified Solutions Expert
DB2 V7 Database Administration OS/390
http://www.ylassoc.com


-----Original Message-----
From: DB2 Data Base Discussion List
[mailto:[login to unmask email]On Behalf Of David Cohn
Sent: Monday, December 23, 2002 12:19 PM
To: [login to unmask email]
Subject: Re: Data Length Mismatches.


Hi Terry,
We've said the same thing two different ways. The reason the predicate
is stage 1 is because the optimizer assumes that both terms match. I was not
specific about run time. When at run time the terms don't match the actual
evaluation (that is what DB2 will do)takes place.
Regards,
David Cohn
Senior Systems Advisor
Themis Training
IBM Certified Solutions Expert:
DB2 V7.1 Database Administration
DB2 V7.1 Family Application Development



----- Original Message -----
From: "Terry Purcell" <[login to unmask email]>
Newsgroups: bit.listserv.db2-l
To: <[login to unmask email]>
Sent: Monday, December 23, 2002 9:53 AM
Subject: Re: Data Length Mismatches.


> David/Harish,
>
> I don't necessarily agree.
>
> The predicate is in fact stage 1, as indicated by the plan table output.
> This is determined at bind time.
>
> Generally speaking, if the host variable (or literal) can be promoted
(thus
> implying it is currently smaller) than the column, then the predicate is
> stage 1 and indexable. This is provided that the length difference is
within
> the same datatype, such as CHAR or DEC. INT & SMALLINT should also be
> considered the same datatype.
>
> If the host variable is larger, and thus the column must be promoted to
> match the host variable, then the comparison is stage 2.
>
> Instead of having to worry about this however, you should use the DCLGEN
> supplied variables wherever possible.
>
> Regards
> Terry Purcell
> Yevich Lawson & Assoc Inc (YL&A)
> IBM Gold Consultant - DB2
> IBM Certified Solutions Expert
> DB2 V7 Database Administration OS/390
> http://www.ylassoc.com
>
>
> -----Original Message-----
> From: DB2 Data Base Discussion List
> [mailto:[login to unmask email]On Behalf Of David Cohn
> Sent: Monday, December 23, 2002 11:14 AM
> To: [login to unmask email]
> Subject: Re: Data Length Mismatches.
>
>
> Hi Harish,
> The optimizer assumes that the terms of the predicate match. That is
why
> the PLAN_TABLE shows you a stage 1 predicate. The actual evaluation stage
is
> determined at run time.
> Hope this helps,
> David Cohn
> Senior Systems Advisor
> Themis Training
> IBM Certified Solutions Expert:
> DB2 V7.1 Database Administration
> DB2 V7.1 Family Application Development
>
>
>
> ----- Original Message -----
> From: "Harish G" <[login to unmask email]>
> Newsgroups: bit.listserv.db2-l
> To: <[login to unmask email]>
> Sent: Monday, December 23, 2002 8:25 AM
> Subject: Data Length Mismatches.
>
>
> > Esteemed List,
> >
> > v6 DB2 for OS/390
> >
> > Is the following predicate a Stage 2 predicate.
> >
> > Col_A = :H
> >
> > Column Col_A is S9(9) COMP, and the Host variable H, S9(4) COMP. The
> > EXPLAIN of the program shows a matching index scan using Col_A. My
> question
> > is since the value compared against the column is a host variable, is
> > the "actual decision" regarding the Stage processing taken at run-time
in
> > this case. Does DB2 V6, consider the example provided as Stage 1 since
the
> > length of the host variable is less than that of the column definition.
> >
> > DB2 Admin. Guide suggests that when the length of the host variable is
> > greater than the column compared to the predicate qualifies as a Stage 2
> > predicate. The App. programming Guide suggests that when the lengths
> > disagree the predicate qualifies as a Stage 2 predicate. In cases of BIN
> > and CHAR fields, does the lengths only have to disagree or does the
> lengths
> > have to be greater than the column definition.
> >
> > Thank you.
> >
> > Harish.
> >



Mike Turner

Re: Data Length Mismatches.
(in response to Terry Purcell)
David

I agree with Terry. In DB2 for OS/390, which was the platform specified in
the original question, the determination of Stage 1 or Stage 2 in this case
is made at bind time and is not changed at runtime.

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

Message text written by DB2 Data Base Discussion List
>David,

When you say the the optimizer assumes the datatypes match, then this
implies to me that you are suggesting that at runtime it may demote this to
a stage 2 predicate. In fact the optimizer can determine at bindtime that
it
will be able to match.

If the reverse is true, thus that the column is shorter than the host
variable, then the optimizer can also determine at bindtime that it is a
stage 2 predicate. The plan table output will NOT show this as a matching
index column.

This was the focus of Harish's question. And I believe we gave two
different
answers. I am suggesting that this determination is at bindtime, and
although you say you are "not specific about runtime", it appears you are
suggesting that this determination may change at runtime. Otherwise I may
be
misinterpreting your answer.

Regards
Terry Purcell
<