Working with Generated Columns

LUW allows a column to be declared as a generated column. It is a column that derives the values for each row from an expression, and is used to embed your business logic into the table definition. The syntax for generated columns is shown in Figure 3.4. Generated columns have to be defined with either the CREATE TABLE or ALTER TABLE statements.

Figure 3.4. Generated column syntax for LUW.

 |---column-name----+---------------------+----------------------------------->                    |                     |                    '-| data-type |-------' |--+-GENERATED--+-ALWAYS-----+--AS--+-(--generation-expression--)-------------|                 '-BY DEFAULT-' 

Values will be generated for the column when a row is inserted into the table. Two options are supported, namely GENERATED ALWAYS and GENERATED BY DEFAULT. For a GENERATED ALWAYS identity column, DB2 has full control over the values generated, and uniqueness is guaranteed. An error will be raised if an explicit value is specified. On the other hand, the GENERATED BY DEFAULT option does not guarantee uniqueness. DB2 will only generate a value for the column when no value is specified at the time of insert.

Figure 3.5 shows an example of a table using a generated column.

Figure 3.5. An example of generated columns using a simple expression for LUW.

 CREATE TABLE payroll ( employee_id INT NOT NULL , base_salary DOUBLE , bonus DOUBLE , commission DOUBLE , total_pay DOUBLE GENERATED ALWAYS AS             (base_salary*(1+bonus) + commission) ) 

In this example, there is a table named payroll in the department. Three columns are related to an employee's total pay, namely base_salary, bonus, and commission. The base_salary and commission are in dollars, and the bonus is a percentage of the base_salary. The total_pay is calculated from these three numbers. The benefit of using a generated column here is to perform pre-calculation before the query time and to save the calculated value in the column. If your application has to use the value frequently, using the generated column will obviously improve the performance.

Note

An alternative to using generated columns (applicable to all platforms) is presented in Figures 3.21 and 3.22 toward the end of this chapter.

To insert a record into the payroll table, you can either use the DEFAULT keyword, as in

 INSERT INTO payroll VALUES (1, 100, 0.1, 20, DEFAULT); 

You could also not enumerate the column:

 INSERT INTO payroll (employee_id, base_salary, bonus, commission) VALUES (1, 100, 0.1, 20); 

Both will generate the same result. Because the column is always defined as generated, you cannot supply a real value for the total_pay column. If not all identity columns are specified in the INSERT statement, DB2 will automatically substitute them with default values according to the column definitions.

It is a good practice to specify all the columns defined in the table and the associated values. This allows you to easily identify if there is a mismatch or missing column names and values. Notice how the reserved word DEFAULT is used so that DB2 will supply the default value for the generated column.

Note

DEFAULT is a DB2 reserved word. It is mandatory if a GENERATED ALWAYS column name is specified in an INSERT statement. Specifying all values for the columns in the VALUES clause of an INSERT statement is a good practice because it gives a clear view of what values are being inserted.

The generation expression in Figure 3.5 is a very simple arithmetic formula. More logic could be built into it by using a CASE statement. The CASE statement will be discussed in detail in Chapter 4, "Using Flow of Control Statements." For now, it is sufficient to know that a CASE statement checks conditions and chooses which statement to execute depending on the result. In the next example, the company has decided that each employee will be either a bonus employee or a commission employee, but not both. A bonus employee receives a base salary and a bonus. A commission employee receives a base salary and a commission. A more complex table definition is shown in Figure 3.6.

Figure 3.6. An example of generated columns using a CASE expression for LUW.

 CREATE TABLE payroll2 ( employee_id INT NOT NULL , employee_type CHAR(1) NOT NULL , base_salary DOUBLE , bonus DOUBLE , commission DOUBLE , total_pay DOUBLE GENERATED ALWAYS AS     (   CASE employee_type         WHEN 'B' THEN base_salary*(1+bonus)         WHEN 'C' THEN (base_salary + commission)         ELSE 0         END     ) ) 

When the total pay is calculated, the employee type is checked first. If the type is 'B', indicating a bonus employee, the total pay is the total of the base salary and the bonus. If the type is 'C', indicating a commission employee, the total pay is calculated by adding the base salary and the commission. If a wrong employee type is entered, the total pay is set to 0, indicating a problem.