Book Image

IBM DB2 9.7 Advanced Application Developer Cookbook

Book Image

IBM DB2 9.7 Advanced Application Developer Cookbook

Overview of this book

With lots of new features, DB2 9.7 delivers one the best relational database systems in the market. DB2 pureXML optimizes Web 2.0 and SOA applications. DB2 LUW database software offers industry leading performance, scale, and reliability on your choice of platform on various Linux distributions, leading Unix Systems like AIX, HP-UX and Solaris and MS Windows platforms. This DB2 9.7 Advanced Application Developer Cookbook will provide an in-depth quick reference during any application's design and development. This practical cookbook focuses on advanced application development areas that include performance tips and the most useful DB2 features that help in designing high quality applications. This book dives deep into tips and tricks for optimized application performance. With this book you will learn how to use various DB2 features in database applications in an interactive way.
Table of Contents (15 chapters)
IBM DB2 9.7 Advanced Application Developer Cookbook
Credits
About the Authors
About the Reviewers
www.PacktPub.com
Preface

Using generated columns in tables


Generated columns are special types of columns in a table whose values are generated by an expression. In this recipe, we will talk about how to create tables with generated columns and will see the use cases for it. In this recipe, we will create a table with a generated column.

Getting ready

We need the privileges to create a table.

How to do it...

  1. 1. Use the GENERATED clause in CREATE TABLE to create the generated columns:

    CREATE TABLE tab1 (c1 INT,
    c2 INT,
    max_col GENERATED ALWAYS AS
    (CASE WHEN c1 > c2 THEN c1 ELSE c2 END));
    
    
  2. 2. Let's see how DB2 populates the values for generated columns automatically:

INSERT INTO tab1(c1, c2) VALUES(5, 10);
INSERT INTO tab1(c1, c2) VALUES(30, 20);
SELECT * FROM tab1;
Results:
C1 C2 MAX_COL
------ ------- --------
5 10 10
30 20 30

How it works...

  • GENERATED ALWAYS: If this clause is specified, then the column value is always generated automatically.

  • GENERATED BY DEFAULT: If this option is specified and if the column...