Why is this happening in `Enable Query Optimization`

[WantedToBeDBA]

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.

[Gert van der Kooij]

In article <e6**************************@posting.google.com >,
WantedToBeDBA (Wa***********@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.

[Serge Rielau]

Gert van der Kooij wrote:

In article <e6**************************@posting.google.com >,
WantedToBeDBA (Wa***********@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

[ak_tiredofspam]

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?

[Gert van der Kooij]

In article <11*********************@l41g2000cwc.googlegroups. com>,
(ak************@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.

[WantedToBeDBA]

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 <ge**@invalid.nl> wrote in message news:<MP************************@news.xs4all.nl>.. .

In article <11*********************@l41g2000cwc.googlegroups. com>,
(ak************@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.

[Serge Rielau]

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

[Thiru]

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

Thiru.
WantedToBeDBA.

[Serge Rielau]

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

[Thiru]

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

Thiru.

[kenfar]

Serge,

Any chance that we'll soon see informational constraint versions of
primary keys and unique constraints?

Ken

[Serge Rielau]

kenfar wrote:

Serge,

Any chance that we'll soon see informational constraint versions of
primary keys and unique constraints?

Ken

You mean like this?:
http://publib.boulder.ibm.com/infoce...n/r0002169.htm

I haven't heard about this requirement for tables thus far.
You would want informational uniqueness without the index to go with it?
(it's the index that's costly, having a non unique index and an
informational unique constraint IMHO makes little sense)

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[kenfar]

Serge Rielau wrote:

kenfar wrote:

Serge,

Any chance that we'll soon see informational constraint versions of
primary keys and unique constraints? You mean like this?:
http://publib.boulder.ibm.com/infoce...n/r0002169.htm

yep, but for tables rather than nicknames
I haven't heard about this requirement for tables thus far.
You would want informational uniqueness without the index to go with it? (it's the index that's costly, having a non unique index and an
informational unique constraint IMHO makes little sense)

In warehousing environments there are sometimes performance reasons for
avoiding primary keys & unique constraints. According to documentation
regarding star joins, primary keys on dimensions are a requirement:
http://publib.boulder.ibm.com/infoce...onstraints.htm
Unfortunately, some dimensions (customer, household, etc) can easily
grow to tens of millions of rows.

I've automated data quality testing on our warehouse - by generating
tests out of the constraints defined in the catalog. Even though we're
careful about managing data quality, this speeds up testing and catches
the occasional problem. I've currently got unique constraints defined
on a number of seldom-used columns - and would prefer to rely on our
etl application & test harness than db2.

ken