When you say the the optimizer assumes the datatypes match, then
implies to me that you are suggesting that at runtime it may demote
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
variable, then the optimizer can also determine at bindtime that it
stage 2 predicate. The plan table output will NOT show this as a
This was the focus of Harish's question. And I believe we gave two
answers. I am suggesting that this determination is at bindtime,
although you say you are "not specific about runtime", it appears
suggesting that this determination may change at runtime. Otherwise
I may be
misinterpreting your answer.
Yevich Lawson & Assoc Inc (YL&A)
IBM Gold Consultant - DB2
IBM Certified Solutions Expert
DB2 V7 Database Administration OS/390
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.
We've said the same thing two different ways. The reason the
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
evaluation (that is what DB2 will do)takes place.
Senior Systems Advisor
IBM Certified Solutions Expert:
DB2 V7.1 Database Administration
DB2 V7.1 Family Application Development
----- Original Message -----
From: "Terry Purcell" <[login to unmask email]>
To: <[login to unmask email]>
Sent: Monday, December 23, 2002 9:53 AM
Subject: Re: Data Length Mismatches.
> I don't necessarily agree.
> The predicate is in fact stage 1, as indicated by the plan
> This is determined at bind time.
> Generally speaking, if the host variable (or literal) can be
> implying it is currently smaller) than the column, then the
> stage 1 and indexable. This is provided that the length
> 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
> match the host variable, then the comparison is stage 2.
> Instead of having to worry about this however, you should use
> supplied variables wherever possible.
> Terry Purcell
> Yevich Lawson & Assoc Inc (YL&A)
> IBM Gold Consultant - DB2
> IBM Certified Solutions Expert
> DB2 V7 Database Administration OS/390
> -----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.
> the PLAN_TABLE shows you a stage 1 predicate. The actual
> 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
> > is since the value compared against the column is a host
> > the "actual decision" regarding the Stage processing
taken at run-time
> > this case. Does DB2 V6, consider the example provided as
Stage 1 since
> > length of the host variable is less than that of the
> > 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
> > 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
> > have to be greater than the column definition.
> > Thank you.
> > Harish.