Again against generated columns or (missing) row value constructor inwhere clause

[Bernard Dhooghe]

Table definition:

CREATE TABLE "SCHEMA1 "."X2" (
"C1" CHAR(20) NOT NULL ,
"C2" CHAR(10) NOT NULL ,
"C3" CHAR(30) NOT NULL GENERATED ALWAYS AS (C1||
C2) )
IN "USERSPACE1 " ;

-- DDL Statements for primary key on Table "SCHEMA1 "."X2"

ALTER TABLE "SCHEMA1 "."X2"
ADD CONSTRAINT "P1" PRIMARY KEY
("C1",
"C2");

IBM Data Studio Version 1.1.1.

Try edit data , insert row. Will fail.

The editor has problems with the generated columns.

I would say: great.

The generated columns capability breaks the relational model. What is
a column in a base table that is not a column?

It has been discussed already several times in the past, but one usage
of generated columns in DB2 LUW is due to the the lack of (SQL92!)
support of row value constructor in the where clause (and to close the
circle, in the cursor positioning clause as extension to SQL92 but
compatible with it in it's simple format) and corresponding optimizer
support.

Quo usque ...

Bernard Dhooghe

[Tonkuma]

Re: Again against generated columns or (missing) row valueconstructo r in where clause

I couldn't understand your issue(might be by my poor English
capability).

Here are some thoughts which are inspired by your article.
1) Although it is not documented, you can specify row comparison
predicate in(on?) DB2 for LUW 9.1.
For example:
------------------------------ Commands Entered
------------------------------
SELECT empno, workdept
, firstnme || RTRIM(' '||midinit) || ' '||lastname AS fullname
, edlevel
FROM employee
WHERE (workdept, edlevel) = ('D11', 16)
;
------------------------------------------------------------------------------

EMPNO WORKDEPT FULLNAME EDLEVEL
------ -------- ------------------------------ -------
000060 D11 IRVING F STERN 16
000150 D11 BRUCE ADAMSON 16
000170 D11 MASATOSHI J YOSHIMURA 16
000190 D11 JAMES H WALKER 16
000200 D11 DAVID BROWN 16
200170 D11 KIYOSHI YAMAMOTO 16

6 record(s) selected.

And you can specify full-select (including VALUES clause) in predicate
on DB2 for LUW prior V9.1.
Here are two examples:
------------------------------ Commands Entered
------------------------------
SELECT empno, workdept
, firstnme || RTRIM(' '||midinit) || ' '||lastname AS fullname
, edlevel
FROM employee
WHERE (workdept, edlevel) = ANY (VALUES ('D11', 16) )
;
------------------------------------------------------------------------------

EMPNO WORKDEPT FULLNAME EDLEVEL
------ -------- ------------------------------ -------
000060 D11 IRVING F STERN 16
000150 D11 BRUCE ADAMSON 16
000170 D11 MASATOSHI J YOSHIMURA 16
000190 D11 JAMES H WALKER 16
000200 D11 DAVID BROWN 16
200170 D11 KIYOSHI YAMAMOTO 16

6 record(s) selected.


------------------------------ Commands Entered
------------------------------
SELECT empno, workdept
, firstnme || RTRIM(' '||midinit) || ' '||lastname AS fullname
, edlevel
FROM employee
WHERE (workdept, edlevel) IN (VALUES ('D11', 16), ('D11', 17) )
;
------------------------------------------------------------------------------

EMPNO WORKDEPT FULLNAME EDLEVEL
------ -------- ------------------------------ -------
000060 D11 IRVING F STERN 16
000150 D11 BRUCE ADAMSON 16
000160 D11 ELIZABETH R PIANKA 17
000170 D11 MASATOSHI J YOSHIMURA 16
000180 D11 MARILYN S SCOUTTEN 17
000190 D11 JAMES H WALKER 16
000200 D11 DAVID BROWN 16
000210 D11 WILLIAM T JONES 17
200170 D11 KIYOSHI YAMAMOTO 16

9 record(s) selected.

2) One usage of generated column is to create a functional index.

http://groups.google.com/group/comp.databases.ibm-db2/browse_frm/thread/eb4bdb66f5868e16/b66e3ac5739087f7?lnk=gst&q=functional+index#b66e3ac5739087f7

[Bernard Dhooghe]

Re: Again against generated columns or (missing) row valueconstructo r in where clause

On May 7, 3:54 pm, Tonkuma <tonk...@fiberb it.netwrote:

I couldn't understand your issue(might be by my poor English
capability).
>
Here are some thoughts which are inspired by your article.
1) Although it is not documented, you can specify row comparison
predicate in(on?) DB2 for LUW 9.1.
For example:
------------------------------ Commands Entered
------------------------------
SELECT empno, workdept
, firstnme || RTRIM(' '||midinit) || ' '||lastname AS fullname
, edlevel
FROM employee
WHERE (workdept, edlevel) = ('D11', 16)
;
------------------------------------------------------------------------------
>
EMPNO WORKDEPT FULLNAME EDLEVEL
------ -------- ------------------------------ -------
000060 D11 IRVING F STERN 16
000150 D11 BRUCE ADAMSON 16
000170 D11 MASATOSHI J YOSHIMURA 16
000190 D11 JAMES H WALKER 16
000200 D11 DAVID BROWN 16
200170 D11 KIYOSHI YAMAMOTO 16
>
6 record(s) selected.
>
And you can specify full-select (including VALUES clause) in predicate
on DB2 for LUW prior V9.1.
Here are two examples:
------------------------------ Commands Entered
------------------------------
SELECT empno, workdept
, firstnme || RTRIM(' '||midinit) || ' '||lastname AS fullname
, edlevel
FROM employee
WHERE (workdept, edlevel) = ANY (VALUES ('D11', 16) )
;
------------------------------------------------------------------------------
>
EMPNO WORKDEPT FULLNAME EDLEVEL
------ -------- ------------------------------ -------
000060 D11 IRVING F STERN 16
000150 D11 BRUCE ADAMSON 16
000170 D11 MASATOSHI J YOSHIMURA 16
000190 D11 JAMES H WALKER 16
000200 D11 DAVID BROWN 16
200170 D11 KIYOSHI YAMAMOTO 16
>
6 record(s) selected.
>
------------------------------ Commands Entered
------------------------------
SELECT empno, workdept
, firstnme || RTRIM(' '||midinit) || ' '||lastname AS fullname
, edlevel
FROM employee
WHERE (workdept, edlevel) IN (VALUES ('D11', 16), ('D11', 17) )
;
------------------------------------------------------------------------------
>
EMPNO WORKDEPT FULLNAME EDLEVEL
------ -------- ------------------------------ -------
000060 D11 IRVING F STERN 16
000150 D11 BRUCE ADAMSON 16
000160 D11 ELIZABETH R PIANKA 17
000170 D11 MASATOSHI J YOSHIMURA 16
000180 D11 MARILYN S SCOUTTEN 17
000190 D11 JAMES H WALKER 16
000200 D11 DAVID BROWN 16
000210 D11 WILLIAM T JONES 17
200170 D11 KIYOSHI YAMAMOTO 16
>
9 record(s) selected.
>
2) One usage of generated column is to create a functional index.http://groups.google.com/group/comp....rowse_frm/thre...

For point 2: I know of this, unfortunately, it breaks the relational
model, a base table contains a column that is not a column; an index
(functional or not) should not impact a table structure, generated
columns do, in french they call it "une fausse bonne idée" (an idea
that looks good but isn't)

Point 1: what about ( c1,c2,..) >= (value1,value2, ...) in where
clause ?

Bernard Dhooghe

[Serge Rielau]

Re: Again against generated columns or (missing) row value constructorin where clause

Bernard Dhooghe wrote:

On May 7, 3:54 pm, Tonkuma <tonk...@fiberb it.netwrote:

>I couldn't understand your issue(might be by my poor English
>capability).
>>
>Here are some thoughts which are inspired by your article.
>1) Although it is not documented, you can specify row comparison
>predicate in(on?) DB2 for LUW 9.1.
>For example:
>------------------------------ Commands Entered
>------------------------------
>SELECT empno, workdept
> , firstnme || RTRIM(' '||midinit) || ' '||lastname AS fullname
> , edlevel
> FROM employee
> WHERE (workdept, edlevel) = ('D11', 16)
>;
>------------------------------------------------------------------------------
>>
>EMPNO WORKDEPT FULLNAME EDLEVEL
>------ -------- ------------------------------ -------
>000060 D11 IRVING F STERN 16
>000150 D11 BRUCE ADAMSON 16
>000170 D11 MASATOSHI J YOSHIMURA 16
>000190 D11 JAMES H WALKER 16
>000200 D11 DAVID BROWN 16
>200170 D11 KIYOSHI YAMAMOTO 16
>>
> 6 record(s) selected.
>>
>And you can specify full-select (including VALUES clause) in predicate
>on DB2 for LUW prior V9.1.
>Here are two examples:
>------------------------------ Commands Entered
>------------------------------
>SELECT empno, workdept
> , firstnme || RTRIM(' '||midinit) || ' '||lastname AS fullname
> , edlevel
> FROM employee
> WHERE (workdept, edlevel) = ANY (VALUES ('D11', 16) )
>;
>------------------------------------------------------------------------------
>>
>EMPNO WORKDEPT FULLNAME EDLEVEL
>------ -------- ------------------------------ -------
>000060 D11 IRVING F STERN 16
>000150 D11 BRUCE ADAMSON 16
>000170 D11 MASATOSHI J YOSHIMURA 16
>000190 D11 JAMES H WALKER 16
>000200 D11 DAVID BROWN 16
>200170 D11 KIYOSHI YAMAMOTO 16
>>
> 6 record(s) selected.
>>
>------------------------------ Commands Entered
>------------------------------
>SELECT empno, workdept
> , firstnme || RTRIM(' '||midinit) || ' '||lastname AS fullname
> , edlevel
> FROM employee
> WHERE (workdept, edlevel) IN (VALUES ('D11', 16), ('D11', 17) )
>;
>------------------------------------------------------------------------------
>>
>EMPNO WORKDEPT FULLNAME EDLEVEL
>------ -------- ------------------------------ -------
>000060 D11 IRVING F STERN 16
>000150 D11 BRUCE ADAMSON 16
>000160 D11 ELIZABETH R PIANKA 17
>000170 D11 MASATOSHI J YOSHIMURA 16
>000180 D11 MARILYN S SCOUTTEN 17
>000190 D11 JAMES H WALKER 16
>000200 D11 DAVID BROWN 16
>000210 D11 WILLIAM T JONES 17
>200170 D11 KIYOSHI YAMAMOTO 16
>>
> 9 record(s) selected.
>>
>2) One usage of generated column is to create a functional index.http://groups.google.com/group/comp....rowse_frm/thre...

>
For point 2: I know of this, unfortunately, it breaks the relational
model, a base table contains a column that is not a column; an index
(functional or not) should not impact a table structure, generated
columns do, in french they call it "une fausse bonne idée" (an idea
that looks good but isn't)

Let's not get all excited about this relational part.
The REASON for the avoidance of functionally dependent columns is the
risk of inconsistency. expression-generated columns assert consistency.
Thus there is no problem. So let's not get hung up by the letter of the
law and stick with it's spirit.
This good idea has since been copied by both MS SQL Server and Oracle
(which has expression based indexes, so they must have seen some
goodness in it beyond mere indexing).
I can't help but being proud :-)

Point 1: what about ( c1,c2,..) >= (value1,value2, ...) in where
clause ?

Believe it or not. Actually making some progress towards your pet peeve.
Don't give up hope.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Bernard Dhooghe]

Re: Again against generated columns or (missing) row valueconstructo r in where clause

I don't want to comment negatively on functional indexes or any other
kind of indexes. They can help, but they best stay out of the table
column definition. Even a simple index is just a way to help in search
speed, or validate a uniqueness, helping to alleviate the fact data
processors (computers) do not have infinite speed.

Concerning row-value constructor support, and hope, OK, the discussion
just started (...mid 1998 with the upcoming DB2 V5.2).

Bernard (Dhooghe)


On May 8, 5:37 pm, Serge Rielau <srie...@ca.ibm .comwrote:

Bernard Dhooghe wrote:

On May 7, 3:54 pm, Tonkuma <tonk...@fiberb it.netwrote:

I couldn't understand your issue(might be by my poor English
capability).

>

Here are some thoughts which are inspired by your article.
1) Although it is not documented, you can specify row comparison
predicate in(on?) DB2 for LUW 9.1.
For example:
------------------------------ Commands Entered
------------------------------
SELECT empno, workdept
, firstnme || RTRIM(' '||midinit) || ' '||lastname AS fullname
, edlevel
FROM employee
WHERE (workdept, edlevel) = ('D11', 16)
;
------------------------------------------------------------------------------

>

EMPNO WORKDEPT FULLNAME EDLEVEL
------ -------- ------------------------------ -------
000060 D11 IRVING F STERN 16
000150 D11 BRUCE ADAMSON 16
000170 D11 MASATOSHI J YOSHIMURA 16
000190 D11 JAMES H WALKER 16
000200 D11 DAVID BROWN 16
200170 D11 KIYOSHI YAMAMOTO 16

>

6 record(s) selected.

>

And you can specify full-select (including VALUES clause) in predicate
on DB2 for LUW prior V9.1.
Here are two examples:
------------------------------ Commands Entered
------------------------------
SELECT empno, workdept
, firstnme || RTRIM(' '||midinit) || ' '||lastname AS fullname
, edlevel
FROM employee
WHERE (workdept, edlevel) = ANY (VALUES ('D11', 16) )
;
------------------------------------------------------------------------------

>

EMPNO WORKDEPT FULLNAME EDLEVEL
------ -------- ------------------------------ -------
000060 D11 IRVING F STERN 16
000150 D11 BRUCE ADAMSON 16
000170 D11 MASATOSHI J YOSHIMURA 16
000190 D11 JAMES H WALKER 16
000200 D11 DAVID BROWN 16
200170 D11 KIYOSHI YAMAMOTO 16

>

6 record(s) selected.

>

------------------------------ Commands Entered
------------------------------
SELECT empno, workdept
, firstnme || RTRIM(' '||midinit) || ' '||lastname AS fullname
, edlevel
FROM employee
WHERE (workdept, edlevel) IN (VALUES ('D11', 16), ('D11', 17) )
;
------------------------------------------------------------------------------

>

EMPNO WORKDEPT FULLNAME EDLEVEL
------ -------- ------------------------------ -------
000060 D11 IRVING F STERN 16
000150 D11 BRUCE ADAMSON 16
000160 D11 ELIZABETH R PIANKA 17
000170 D11 MASATOSHI J YOSHIMURA 16
000180 D11 MARILYN S SCOUTTEN 17
000190 D11 JAMES H WALKER 16
000200 D11 DAVID BROWN 16
000210 D11 WILLIAM T JONES 17
200170 D11 KIYOSHI YAMAMOTO 16

>

9 record(s) selected.

>

2) One usage of generated column is to create a functional index.http://groups.google.com/group/comp....rowse_frm/thre...

>

For point 2: I know of this, unfortunately, it breaks the relational
model, a base table contains a column that is not a column; an index
(functional or not) should not impact a table structure, generated
columns do, in french they call it "une fausse bonne idée" (an idea
that looks good but isn't)

>
Let's not get all excited about this relational part.
The REASON for the avoidance of functionally dependent columns is the
risk of inconsistency. expression-generated columns assert consistency.
Thus there is no problem. So let's not get hung up by the letter of the
law and stick with it's spirit.
This good idea has since been copied by both MS SQL Server and Oracle
(which has expression based indexes, so they must have seen some
goodness in it beyond mere indexing).
I can't help but being proud :-)
>

Point 1: what about ( c1,c2,..) >= (value1,value2, ...) in where
clause ?

>
Believe it or not. Actually making some progress towards your pet peeve.
Don't give up hope.
>
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab