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

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

  Click for larger image
  

  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.