column "generated always as" generated from multiple columns

[Darek]

Hi,

I have a table, something similar to:

create table my_table (
id char(32) not null primary key,
num integer not null,
code varchar(2) not null,
name varchar(60) not null,
[...]
)

Now I need to add another column 'calc_field' of type varchar that is
always automatically
derived from columns: id, num, code (concatenated)
I was trying to do something with concatenation and CAST but it always
fails.
Even if I try to test my expression with simple select to see generated
column,
the same expression fails in the 'generated always as' statement.

Any hint how to do it?
Thanks in advance

Darek

[Gert van der Kooij]

In article <11*********************@i40g2000cwc.googlegroups. com>,
dw*****@gmail.com says...

Hi,

I have a table, something similar to:

create table my_table (
id char(32) not null primary key,
num integer not null,
code varchar(2) not null,
name varchar(60) not null,
[...]
)

Now I need to add another column 'calc_field' of type varchar that is
always automatically
derived from columns: id, num, code (concatenated)
I was trying to do something with concatenation and CAST but it always
fails.
Even if I try to test my expression with simple select to see generated
column,
the same expression fails in the 'generated always as' statement.

It might help if you post what you allready tried.

[Serge Rielau]

Darek wrote:

Hi,

I have a table, something similar to:

create table my_table (
id char(32) not null primary key,
num integer not null,
code varchar(2) not null,
name varchar(60) not null,
[...]
)

Now I need to add another column 'calc_field' of type varchar that is
always automatically
derived from columns: id, num, code (concatenated)
I was trying to do something with concatenation and CAST but it always
fails.
Even if I try to test my expression with simple select to see generated
column,
the same expression fails in the 'generated always as' statement.

Have you tried?
id || CHAR(num) || code

I take a guess and suspect you tried to cast to VARCHAR which is not
supported directly.

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

[Artur]

Darek,

I don't think it should a problem. Here is a ddl that works:

create table my_table (
id char(32) not null primary key,
num integer not null,
code varchar(2) not null,
name varchar(60) not null,
gen generated always as (id concat char(num) concat code)
);

Probably you should modify the code to remove white characters from the
generated value (I suggest to write SQL function to do this).

I think I know what your problem is. If you have data in your table,
you cannot just alter the table to add a generated column. Here is what
you should do:

set integrity for my_table off;
alter table my_table
add column gen generated always as (id concat char(num) concat code)
;
set integrity for my_table immediate checked force generated;

One important remark. If your my_table is in reference with other
tables (primary, foreign keys) the first statement wil also move the
tables into "integrity off" state. So, in the last statement you must
put all the table names, and (this is important) as a one SQL
instruction, eg.

set integrity for my_table, fk_my_table, fk2_my table immediate checked
force generated;

Radosnych Swiat, Alleluja

-- Artur Wronski

[Bernard Dhooghe]

I would try to avoid generated always in the design.

It's drifting away from the relational model (it is an expression but
not in a view), programmer's will use the column instead of the
expression, tools will have problems to cope with it.

Bernard Dhooghe

[Serge Rielau]

Bernard Dhooghe wrote:

I would try to avoid generated always in the design.

It's drifting away from the relational model (it is an expression but
not in a view), programmer's will use the column instead of the
expression, tools will have problems to cope with it.

Generated columns are a performance feature just like MQTs and
replicated tables.
There are two main reasons for them:
1. Improve performance of where clauses with expressions in them.
2. Improve performance of queries with expensive expressions.

Functionally dependent columns are discouraged because they can become
inconsistent. Generated columns are designed from teh ground up to
remain consistent.
So, all you pay is storage.

So, while they may not belong into the first schema design they have
their rightful place in reality.

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

[Darek]

Thanks for all replies.
The hints above helped me a lot.
And yes, I need it to speed up some expensive "where" conditions.
Without doing this I had problems with full scans, now it started to
use indexes on generated columns. Just a few such columns in a big data
model should not be a problem.

Darek

[Bernard Dhooghe]

Seeing colums in the physical data model that are there for query
purposes is to far away from the relation model. That "just ..." could
be more than ennoying in the long term.

Company's management could ask IBM to implement standards. In this case
of SQL 92...

Point n'est besoin d'espérer ...

Bernard Dhooghe

Darek wrote:

Thanks for all replies.
The hints above helped me a lot.
And yes, I need it to speed up some expensive "where" conditions.
Without doing this I had problems with full scans, now it started to
use indexes on generated columns. Just a few such columns in a big data
model should not be a problem.

Darek

[Knut Stolze]

Bernard Dhooghe wrote:

Company's management could ask IBM to implement standards. In this case
of SQL 92...

Why use that old and deprecated standard?

The SQL standard doesn't give any specifications regarding performance
things. Not even regular indexes are defined there. So we're talking
product-specific anyway...

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Serge Rielau]

Knut Stolze wrote:

Bernard Dhooghe wrote:

Company's management could ask IBM to implement standards. In this case
of SQL 92...

Why use that old and deprecated standard?

The SQL standard doesn't give any specifications regarding performance
things. Not even regular indexes are defined there. So we're talking
product-specific anyway...

Matter of fact GENERATED COLUMNS ARE SQL Standard.
(and so are sequences and identity columns).
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Bernard Dhooghe]

Being part of standard (2003, IBM presentation on
http://www.wiscorp.com/SQL2003Features.pdf) does not mean that in this
case it's usage will not bite.

SQL92 is the standard to use (and to have in DB2 UDB) to solve the
problem. Nicely relational, short SQL notation, no data overhead, no
sqlda problems

Bernard Dhooghe

[Knut Stolze]

Serge Rielau wrote:

Knut Stolze wrote:

Bernard Dhooghe wrote:

Company's management could ask IBM to implement standards. In this case
of SQL 92...

Why use that old and deprecated standard?

The SQL standard doesn't give any specifications regarding performance
things. Not even regular indexes are defined there. So we're talking
product-specific anyway...

Matter of fact GENERATED COLUMNS ARE SQL Standard.
(and so are sequences and identity columns).

True. But this makes the discussion here even more strange. I still don't
get what standards have to do with the use of generated columns in this
particular case and what the company's management of the OP is supposed to
do.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Knut Stolze]

Bernard Dhooghe wrote:

Being part of standard (2003, IBM presentation on
http://www.wiscorp.com/SQL2003Features.pdf) does not mean that in this
case it's usage will not bite.
The national standardization bodies (i.e. ANSI, DIN, ...) participate in
ISO. Companies are only so much involved as they might sponsor a member of
these national bodies.
SQL92 is the standard to use (and to have in DB2 UDB) to solve the
problem.

No, its not. SQL:2003 is the one.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Bernard Dhooghe]

In this case, the answer is in SQL92, with row value constructors.

Select .. where (id ,num, code) > >= = < <= ...

Optimizer can just pick up the index, no table scans, easy semantic
mapping.

Would you not ask your management to ask the vendor to implement
standards that can help, this being here part of ... SQL92?

For those who want more complex things, index definition based on an
expression, working behind the scenes.

Bernard Dhooghe

[Knut Stolze]

Bernard Dhooghe wrote:

In this case, the answer is in SQL92, with row value constructors.

Select .. where (id ,num, code) > >= = < <= ...
You can do a "(id, num, code) = (val1, val2, val3)" in DB2. The other
comparison operators are not handled that way, though. Semantically, this
is identical to "(id = val1 AND num = val2 AND code = val3)". So the
combined variation is just syntactic sugar.
Optimizer can just pick up the index, no table scans, easy semantic
mapping.
The DB2 optimizer will do just that if it estimates that this would lead to
better execution times.
Would you not ask your management to ask the vendor to implement
standards that can help, this being here part of ... SQL92?

Sure, I would. However, I wouldn't ask for minor syntactical things but
rather for real important features like the MERGE statement or range
partitioning.

p.s: I still don't understand why you're referring to a deprecated standard.
SQL92 is superseeded by SQL:1999, which in turn is out-of-date since
December 2003 wher SQL:2003 was published by ISO.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Bernard Dhooghe]

I refer to SQL92 as in this standard, the row-value constructor clause
was introduced to solve the problem of this posting (and a number of
others in this newsgroup).

So it is part of a standard that is 13-14 years old. Let us respect
what was crafted by the committee then.

Is is not minor, there is no fully semantical equivalent way to express
what is contained in the clause. When (c1,c2) >= (v1,v2) is needed,
written as c1 > v1 or c1 = v1 and c2 >= v2 can the optimizer detect
what is meant exactly? This is certainly much more difficult than
implementing row value constructors.
Bernard Dhooghe
Knut Stolze wrote:

Bernard Dhooghe wrote:

In this case, the answer is in SQL92, with row value constructors.

Select .. where (id ,num, code) > >= = < <= ...

You can do a "(id, num, code) = (val1, val2, val3)" in DB2. The other
comparison operators are not handled that way, though. Semantically, this
is identical to "(id = val1 AND num = val2 AND code = val3)". So the
combined variation is just syntactic sugar.

Optimizer can just pick up the index, no table scans, easy semantic
mapping.

The DB2 optimizer will do just that if it estimates that this would lead to
better execution times.

Would you not ask your management to ask the vendor to implement
standards that can help, this being here part of ... SQL92?

Sure, I would. However, I wouldn't ask for minor syntactical things but
rather for real important features like the MERGE statement or range
partitioning.

p.s: I still don't understand why you're referring to a deprecated standard.
SQL92 is superseeded by SQL:1999, which in turn is out-of-date since
December 2003 wher SQL:2003 was published by ISO.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany2

[--CELKO--]

Put this in a VIEW. The coce will be Standard, portable SQL and always
up to date. Do not make it hard and proprietary.

[--CELKO--]

>> I still don't understand why you're referring to a deprecated standard.
SQL92 is superseeded by SQL:1999, which in turn is out-of-date since
December 2003 wher SQL:2003 was published by ISO. <<

But then you get into the problems of porting code to products that are
just getting to SQL-92 and the way the US Government (largest user of
computers on Earth) only wants SQL-92 code. Then local syntax for this
feature varies between SQL:2003 and SQL Server, etc.

I'd go with the VIEW fopr practical reasons right now and then switch
over when more products have it.