by Paul C. Zikopoulos and Roman B. Melnyk
DB2 UDB “Stinger” is the technology preview of the next release of the IBM DB2 Universal Database (DB2 UDB) for Linux, UNIX , and Windows . As of the publication date of this article, it is available as an open beta for download at: www.ibm.com\data\stinger. DB2 UDB “Stinger” release is full of new features from manageability to scalability to developer productivity.
This article is the first in a series that will introduce some of the new key features in DB2 UDB “Stinger,” with “work-through” examples that you can use with the open beta today. In this article, we will cover some of the new options available to programmers and database administrators (DBAs) who implement generated columns in a DB2 UDB environment.
A One-Minute Overview of Generated Columns
A generated column is a column whose row values are derived from an identity, a sequence, or an expression that involves one or more columns in the table (as opposed to a direct INSERT or UPDATE operation).
Generated columns first appeared in DB2 UDB V7; their widespread use is attributable to the fact that programmers and DBAs can avoid writing cumbersome business logic or circumvent the performance penalty of “counter tables.” It is outside the scope of this article to cover the existing behavior of generated columns in detail. Instead, we will discuss the new options available in the “Stinger” release. If you are not familiar with generated columns in DB2 UDB, or would like a quick refresher, we suggest that you visit the DB2 Information Center.
Generated Column Enhancements in DB2 UDB “Stinger”
There are many enhancements to generated columns in the “Stinger” release, and we are going to take you through some examples to illustrate most of them. The examples in this article are all based on the following table and build on its single row of data:
connect to sample
create table totalrevenue (custid integer not null,
insidesales decimal(8,2),
outsidesales decimal(8,2),
totalsales decimal(8,2),
regionid integer)
insert into totalrevenue values (10, 5675.55, 1245.40, 6920.95, 1)
select * from totalrevenue
CUSTID INSIDESALES OUTSIDESALES TOTALSALES REGIONID
———– ———– ———— ———- ———–
10 5675.55 1245.40 6920.95 1
1 record(s) selected.
When working with generated columns in DB2 UDB “Stinger,” you can now perform the following tasks:
Add a generated expression attribute to an existing nongenerated column.
If you add a generated expression to a column, that column can no longer have an explicit value assigned to it through an INSERT or UPDATE operation (unless that value is a default value).
Sometimes adding a generated expression to a column places the table in check- pending state. For example, adding a new column to a table with a GENERATED AS (C1+C2) expression would require that table to be placed in check-pending state because all of the values in the new column would have to be consistent with the new generation expression (C1+C2).
An example of adding a generated property to a column that would not require it to be placed in check-pending state would be the addition of an identity column. In this case, check-pending state is not necessary, because only new rows would have values generated for them.
There are some restrictions in the “Stinger” release that you should be aware of when adding a generation expression to your tables. Specifically, the column for which you want to add a generation expression cannot be a:
- Dimension in a multidimensional clustered (MDC) table
- Key in a range-clustered table (RCT)
- Partitioning key in a partitioned table.
The following code shows an example of adding a generation expression to an existing column that does not already have a generation expression defined on it:
connect to sample
set integrity for totalrevenue off
alter table totalrevenue alter column totalsales
set generated always as (insidesales + outsidesales)
set integrity for totalrevenue immediate checked force generated
insert into totalrevenue values (20, 3456.20, 1396.90, 4853.10, 1)
SQL0798N A value cannot be specified for column “TOTALSALES”
which is defined as GENERATED ALWAYS. SQLSTATE=428C9
insert into totalrevenue (custid,insidesales,outsidesales,regionid)
values (20, 3456.20, 1396.90, 1)
select * from totalrevenue
CUSTID INSIDESALES OUTSIDESALES TOTALSALES REGIONID
———– ———– ———— ———- ———–
10 5675.55 1245.40 6920.95 1
20 3456.20 1396.90 4853.10 1
2 record(s) selected.
The first INSERT statement in the example above fails because the TOTALSALES column was changed to a generated column. Applications can no longer explicitly insert values into it. The second INSERT statement succeeds, because the TOTALSALES column is automatically generated.
- Dimension in a multidimensional clustered (MDC) table
Drop the generated column attribute from an existing column.
If a generation expression is dropped from a generated column, that column will use a null value for all subsequent default values.
connect to sample
alter table totalrevenue alter column totalsales drop expression
insert into totalrevenue (custid, insidesales,outsidesales,regionid)
values (30, 7897.65, 3430.80, 2)
insert into totalrevenue (custid, insidesales, outsidesales,
totalsales, regionid) values (40, 4597.65, 1930.80, 6528.45, 2)
select * from totalrevenue
CUSTID INSIDESALES OUTSIDESALES TOTALSALES REGIONID
———– ———– ———— ———- ———–
10 5675.55 1245.40 6920.95 1
20 3456.20 1396.90 4853.10 1
30 7897.65 3430.80 – 2
40 4597.65 1930.80 6528.45 2
4 record(s) selected.
Notice that both INSERT statements succeeded, despite the fact that one of them did not specify a value for the TOTALSALES column. The first INSERT statement, as you would expect, produces a null value for the TOTALSALES column.
which is defined as GENERATED ALWAYS. SQLSTATE=428C9
