An IBM DB2 Universal Database "Stinger" Feature Preview: Dynamic Generated Columns

Friday May 28th 2004 by DatabaseJournal.com Staff
Share:

This article is the first in a series that introduces some of the new features in DB2 UDB 'Stinger.' Included are examples that you can use with the open beta today.

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.

  •   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.

  •   Add the identity attribute to an existing non-identity column.

    If you add the identity attribute to a column, only new rows will receive identity values. ("Stinger" will not go back and change existing data.)

    The following figure shows how to change the CUSTID column in the TOTALREVENUE table so that it automatically generates an identity value for all new rows. In this example, we used the Control Center to illustrate how these types of operations can be performed using a graphical user interface:

    Although they are outside the scope of this article, there are a number of ALTER TABLE operations that are new in "Stinger." Sometimes "Stinger" has to drop and reload a table in order to change some if its characteristics. (You can see that the Control Center alerts you to this requirement in the Note box.) The good news is that "Stinger" will not only save you the time of having to write the DDL to do this, but it will also take care of it for you.





    After "Stinger" has finished dropping, recreating, and loading the data back into the TOTALREVENUE table, you can see that the CUSTID column now automatically generates its identity values:




    connect to sample
    
    insert into totalrevenue (custid, insidesales, outsidesales,
     totalsales, regionid) values (50, 9856.67, 2345.60, 12202.27, 3)
    
    SQL0798N  A value cannot be specified for column "CUSTID" 
    	which is defined as GENERATED ALWAYS.  SQLSTATE=428C9
    
    insert into totalrevenue (insidesales, outsidesales, totalsales,
     regionid) values (9856.67, 2345.60, 12202.27, 3)
    
    insert into totalrevenue (insidesales, outsidesales, totalsales,
     regionid) values (2345.60, 987.56, 3333.16, 3)
    
    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
             50     9856.67      2345.60   12202.27           3
             55     2345.60       987.56    3333.16           3
    
      6 record(s) selected.
    

    Notice how the second INSERT generated a CUSTID=55 row; this is because we defined the identity column to increment by 5, after starting at 50.

  •   Drop a sequence from an identity column.

    You can drop a sequence from an identity column and change that column's behavior, so that it becomes a regular (nongenerated) column. If you perform this type of alteration on your table, the column's nullability attribute is not affected. Because all identity columns must be defined with the NOT NULL option, this column will continue to disallow null values after dropping the identity attribute.

    An example of dropping the identity attribute from a column is shown below:

    connect to sample
    
    insert into totalrevenue (custid, insidesales, outsidesales,
     totalsales, regionid) values (50, 9856.67, 2345.60, 12202.27, 3)
    
    SQL0798N  A value cannot be specified for column "CUSTID" 
    	which is defined as GENERATED ALWAYS.  SQLSTATE=428C9
    
    insert into totalrevenue (insidesales, outsidesales, totalsales,
     regionid) values (9856.67, 2345.60, 12202.27, 3)
    
    insert into totalrevenue (insidesales, outsidesales, totalsales,
     regionid) values (2345.60, 987.56, 3333.16, 3)
    
    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
             50     9856.67      2345.60   12202.27           3
             55     2345.60       987.56    3333.16           3
    
      6 record(s) selected.
    

    In this example, the first INSERT statement fails because the CUSTID column is not nullable, even after the identity attribute is dropped. The second INSERT statement succeeds because this column no longer has the identity property associated with it, and the key value (60) must be explicitly specified.

Wrapping it Up

As you can see, generated columns become a lot more flexible and usable in the DB2 UDB "Stinger" release. There are more options that we have not covered in this article. For example, you can also:

  • Alter an identity column's behavior from GENERATED ALWAYS to GENERATED BY DEFAULT (or the reverse). This operation will not change any data that already exists in the table, but it will affect new data.
  • Drop the default attribute from a user-defined default column, so that the column defaults to the null value for all new data.
  • Drop, or set a default, identity, or generation expression in a single ALTER statement.
  • Simply use the keyword GENERATED without the ALWAYS clause.

We hope you take the time to download the DB2 UDB "Stinger" beta and experiment with some of the new options that we outlined in this article. For a broader view of all the new features in the DB2 UDB "Stinger" release, see "That Bee in My Bonnet is IBM DB2 UDB Stinger: A look at the new functions coming".

About the Authors

Paul C. Zikopoulos, BA, MBA, is with IBM Canada Ltd. Paul has written numerous magazine articles and books about DB2. Paul has co-authored the books: DB2 Version 8: The Official Guide, DB2: The Complete Reference, DB2 Fundamentals Certification for Dummies, DB2 for Dummies, and A DBA's Guide to Databases on Linux. Paul is a DB2 Certified Advanced Technical Expert (DRDA and Cluster/EEE) and a DB2 Certified Solutions Expert (Business Intelligence and Database Administration). You can reach him at: paulz_ibm at msn.com.

Roman B. Melnyk, PhD, is with IBM Canada Ltd., specializing in database administration, DB2 utilities, and SQL. Roman has written numerous DB2 books, articles, and other related materials. Roman co-authored DB2 Version 8: The Official Guide, DB2: The Complete Reference, DB2 Fundamentals Certification for Dummies, and DB2 for Dummies. You can reach him at roman_b_melnyk at hotmail.com.

Disclaimers

The contents of this article represent those features that may or may not be available in the technology preview or beta. IBM reserves the right to include or exclude any functionality mentioned in this article for the "Stinger," or a subsequent release. The views expressed in this article are those of the author and not necessarily those of IBM Canada Ltd. or IBM Corporation.

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved