SEO

Hi all,

db2 => create table emp(empno int not null primary key, \
db2 (cont.) => sex char(1) not null constraint s_check check \
db2 (cont.) => (sex in ('m','f')) \
db2 (cont.) => not enforced \
db2 (cont.) => enable query optimization)
DB20000I The SQL command completed successfully.

db2 => insert into emp values(1,'m')
DB20000I The SQL command completed successfully.
db2 => insert into emp values(2,'f')
DB20000I The SQL command completed successfully.
db2 => insert into emp values(2,'q')
db2 => insert into emp values(3,'q')
DB20000I The SQL command completed successfully.

db2 => select * from emp

EMPNO SEX
----------- ---
1 m
2 f
3 q

3 record(s) selected.

db2 => select * from emp where sex = 'q'

EMPNO SEX
----------- ---

0 record(s) selected.


db2 => select * from emp where sex <> 'm'

EMPNO SEX
----------- ---
2 f
3 x

2 record(s) selected.


I have add the enable query optimization clause.. But how come the
last query is showing records with sex x??
According to doc "The ENABLE QUERY OPTIMIZATION keyword also allowed
DB2 to use this constraint information when optimizing SQL
statements."

I am using db2 8.1

Thiru.
WantedToBeDBA.

In article <e6babb47.0503120305.348df6d7@posting.google.com >,
WantedToBeDBA (WantedToBeDBA@gmail.com) says...
>
>
> db2 => select * from emp where sex <> 'm'
>
> EMPNO SEX
> ----------- ---
> 2 f
> 3 x
>
> 2 record(s) selected.
>

Hi,

I can't reproduce this on DB2 8.2 with FP8, it shows the expected
results:

select * from emp

EMPNO SEX
----------- ---
1 m
2 f
3 q

3 record(s) selected.


select * from emp where sex = 'q'

EMPNO SEX
----------- ---

0 record(s) selected.


select * from emp where sex <> 'm'

EMPNO SEX
----------- ---
2 f
3 q

2 record(s) selected.

Gert van der Kooij wrote:
> In article <e6babb47.0503120305.348df6d7@posting.google.com >,
> WantedToBeDBA (WantedToBeDBA@gmail.com) says...
>
>>
>>db2 => select * from emp where sex <> 'm'
>>
>>EMPNO SEX
>>----------- ---
>> 2 f
>> 3 x
>>
>> 2 record(s) selected.
>>
>
>
> Hi,
>
> I can't reproduce this on DB2 8.2 with FP8, it shows the expected
> results:
>
> select * from emp
>
> EMPNO SEX
> ----------- ---
> 1 m
> 2 f
> 3 q
>
> 3 record(s) selected.
>
>
> select * from emp where sex = 'q'
>
> EMPNO SEX
> ----------- ---
>
> 0 record(s) selected.
>
>
> select * from emp where sex <> 'm'
>
> EMPNO SEX
> ----------- ---
> 2 f
> 3 q
>
> 2 record(s) selected.
I also cannot possible come up with way for DB2 to invent 'x' on its
own. DB2 doesn't know constants that are not in the query, the data or
the DDL....

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Gert,

I guess that during compilation of
select * from emp where sex = 'q'
the optimizer recognized the check constraint sex in ('m','f') and
eliminated the select altogether. Should the constraint be enforced, no
rows would meet the search criteria sex = 'q'. The optimizer has acted
as if the constraint were enforced, which is exactly as it should.

Regarding
select * from emp where sex <> 'm'
I guess the execution plan was tablespace scan, matching rows against
the search criteria
sex <> 'm'

I don't see any problem here, all worked as it should.

What do you think?

In article <1110683719.132452.33100@l41g2000cwc.googlegroups. com>,
(ak_tiredofspam@yahoo.com) says...
> Gert,
>
> I guess that during compilation of
> select * from emp where sex = 'q'
> the optimizer recognized the check constraint sex in ('m','f') and
> eliminated the select altogether. Should the constraint be enforced, no
> rows would meet the search criteria sex = 'q'. The optimizer has acted
> as if the constraint were enforced, which is exactly as it should.
>
> Regarding
> select * from emp where sex <> 'm'
> I guess the execution plan was tablespace scan, matching rows against
> the search criteria
> sex <> 'm'
>
> I don't see any problem here, all worked as it should.
>
> What do you think?
>
>

You should read the first post
I didn't have a problem, it was Thiru (a.k. WantedToBeDBA) who had a
problem when running the last SQL, it returned an 'x':

>>db2 => select * from emp where sex <> 'm'
>>
>>EMPNO SEX
>>----------- ---
>> 2 f
>> 3 x
>>
>> 2 record(s) selected.
>>

Hi all,
Sorry for some mistakes in early post.
Here is the correct one.
db2 => create table emp(empno int not null primary key, \
db2 (cont.) => sex char(1) not null constraint s_check check \
db2 (cont.) => (sex in ('m','f')) \
db2 (cont.) => not enforced \
db2 (cont.) => enable query optimization)
DB20000I The SQL command completed successfully.

db2 => insert into emp values(1,'m')
DB20000I The SQL command completed successfully.
db2 => insert into emp values(2,'f')
DB20000I The SQL command completed successfully.
db2 => insert into emp values(3,'x')
DB20000I The SQL command completed successfully.

db2 => select * from emp

EMPNO SEX
----------- ---
1 m
2 f
3 x

3 record(s) selected.

db2 => select * from emp where sex = 'x'

EMPNO SEX
----------- ---

0 record(s) selected.


db2 => select * from emp where sex <> 'm'

EMPNO SEX
----------- ---
2 f
3 x

2 record(s) selected.


I have add the enable query optimization clause.. But how come the
last query is showing records with sex x??

My question is "If query optimization is enabled and the query has
been issued, the informational constraint having check values m and f
has to be considered." Am i Right??? Can any one justify for final
query response??

db2level
--------
DB21085I Instance "DB2" uses "32" bits and DB2 code release
"SQL08010" with
level identifier "01010106".
Informational tokens are "DB2 v8.1.0.36", "s021023", "", and FixPak
"0".
Product is installed at "C:\PROGRA~1\IBM\SQLLIB".

Thiru.
WantedToBeDBA.

Gert van der Kooij <gert@invalid.nl> wrote in message news:<MPG.1c9e146db62bae9b989804@news.xs4all.nl>.. .
> In article <1110683719.132452.33100@l41g2000cwc.googlegroups. com>,
> (ak_tiredofspam@yahoo.com) says...
> > Gert,
> >
> > I guess that during compilation of
> > select * from emp where sex = 'q'
> > the optimizer recognized the check constraint sex in ('m','f') and
> > eliminated the select altogether. Should the constraint be enforced, no
> > rows would meet the search criteria sex = 'q'. The optimizer has acted
> > as if the constraint were enforced, which is exactly as it should.
> >
> > Regarding
> > select * from emp where sex <> 'm'
> > I guess the execution plan was tablespace scan, matching rows against
> > the search criteria
> > sex <> 'm'
> >
> > I don't see any problem here, all worked as it should.
> >
> > What do you think?
> >
> >
>
> You should read the first post
> I didn't have a problem, it was Thiru (a.k. WantedToBeDBA) who had a
> problem when running the last SQL, it returned an 'x':
>
> >>db2 => select * from emp where sex <> 'm'
> >>
> >>EMPNO SEX
> >>----------- ---
> >> 2 f
> >> 3 x
> >>
> >> 2 record(s) selected.
> >>

DB2 today does not consider that if you exclude 'm' only 'f' and NULL
are left over.
Note that ENABLE is not equal to MUST ;-)
Cheers
Serge


--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Hi Serge,
Does that mean that we can't trust the output from a table
containing informational contraints??

Thiru.
WantedToBeDBA.

Thiru wrote:
> Hi Serge,
> Does that mean that we can't trust the output from a table
> containing informational contraints??
>
> Thiru.
> WantedToBeDBA.
>
If DB2 cannot trust the app to put correct information inside, of course
not.
Not that that this doe snot even require informational constraints.
You can use SET INTEGRITY IMMEDIATE UNCHECKED to aqccept badly loaded
data. IMMEDIATE UNCHECKED can also cause trouble when altering a
generated column, or worse: If you use an external UDF in a generetaed
column or a check constraint and you change the implementation of the UDF.

Check out the word "assumed" below:
ENABLE QUERY OPTIMIZATION or DISABLE QUERY OPTIMIZATION
Specifies whether the constraint or functional dependency can be
used for query optimization under appropriate circumstances. The default
is ENABLE QUERY OPTIMIZATION.

ENABLE QUERY OPTIMIZATION
The constraint is _assumed_to_be_true_ and can be used for
query optimization.
DISABLE QUERY OPTIMIZATION
The constraint cannot be used for query optimization.


--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Thanks Serge for making me see things in black and white.

Thiru.