Sample Application - DB2 Using SQL

Consider the following two tables, which make up a very simple application:

Sample Views used in Join Examples

Customer Balance Table

We want DB2 to enforce the following business rules:

  • CUST_ID will be a unique positive integer value, always ascending, never reused, and automatically generated by DB2. This field cannot be updated by a user.
  • CUST_NAME has the customer name. It can be anything, but not blank.
  • CUST_SEX must be either "M" or "F".
  • NUM_SALES will have a count of the sales (for the customer), as recorded in the related

US-sales table.

The value will be automatically maintained by DB2. It cannot be updated directly by a user.

  • TOTAL_SALES will have the sum sales (in US dollars) for the customer. The value will be automatically updated by DB2. It cannot be updated directly by a user.
  • MASTER_CUST_ID will have, if there exists, the customer-ID of the customer that this customer is a dependent of. If there is no master customer, the value is null. If the master customer is deleted, this row will also be deleted (if possible).
  • CUST_INSERT_TS has the timestamp when the row was inserted. The value is automatically generated by DB2. Any attempt to change will induce an error.
  • CUST_UPDATE_TS has the timestamp when the row was last updated by a user (note: not by a trigger as a result of a change to the US-sales table). The value is automatically generated by DB2. Any attempt to change will induce an error.
  • A row can only be deleted when there are no corresponding rows in the US-sales table

US Sales Table

We want DB2 to enforce the following business rules:

  • INVOICE#: will be a unique ascending integer value. The uniqueness will apply to the US-sales table, plus any international sales tables (i.e. to more than one table).
  • CUST_ID is the customer ID, as recorded in the customer-balance table. No row can be inserted into the US-sales table except that there is a corresponding row in the customerbalance table. Once inserted, this value cannot be updated.
  • SALE_VALUE is the value of the sale, in US dollars. When a row is inserted, this value is added to the related total-sales value in the customer-balance table. If the value is subsequently updated, the total-sales value is maintained in sync.
  • SALE_INSERT_TS has the timestamp when the row was inserted. The value is automatically generated by DB2. Any attempt to change will induce an error.
  • SALE_UPDATE_TS has the timestamp when the row was last updated. The value is automatically generated by DB2. Any attempt to change will induce an error.
  • Deleting a row from the US-sales table has no impact on the customer-balance table (i.e. the total-sales is not decremented). But a row can only be deleted from the latter when there are no more related rows in the US-sales table.

Enforcement Tools

To enforce the above business rules, we are going to have to use:

  • Unique indexes.
  • Secondary non-unique indexes (needed for performance).
  • Primary and foreign key definitions.
  • User-defined distinct data types.
  • Nulls-allowed and not-null columns.
  • Column value constraint rules.
  • Before and after triggers.

Distinct Data Types

Two of the fields are to contain US dollars, the implication being the data in these columns should not be combined with columns that contain Euros, or Japanese Yen, or my shoe size. To this end, we will define a distinct data type for US dollars:
Customer-Balance Table

Now that we have defined the data type, we can create our first table:

Customer-Balance table DDL

The following business rules are enforced above:

  • The customer-ID is defined as an identity column (see page 257), which means that the value is automatically generated by DB2 using the rules given. The field cannot be updated by the user.
  • The customer-ID is defined as the primary key, which automatically generates a unique index on the field, and also enables us to reference the field using a referential integrity rule. Being a primary key prevents updates, but we had already prevented them because the field is an identity column.
  • The total-sales column is uses the type us-dollars.
  • Constraints C1 and C2 enforce two data validation rules.
  • Constraint C3 relates the current row to a master customer, if one exists. Furthermore, if the master customer is deleted, this row is also deleted.
  • All of the columns, except for the master-customer-id, are defined as NOT NULL, which means that a value must be provided.

We still have several more business rules to enforce - relating to automatically updating fields and/or preventing user updates. These will be enforced using triggers.
US-Sales Table

Now for the related US-sales table:

US-Sales table DDL

The following business rules are enforced above:

  • The invoice# is defined as the primary key, which automatically generates a unique index on the field, and also prevents updates.
  • The sale-value uses the type us-dollars.
  • Constraint U1 checks that the sale-value is always greater than zero.
  • Constraint U2 checks that the customer-ID exists in the customer-balance table, and also prevents rows from being deleted from the latter if their exists a related row in this table.
  • All of the columns are defined as NOT NULL, so a value must be provided for each.
  • A secondary non-unique index is defined on customer-ID, so that deletes to the customerbalance table (which require checking this table for related customer-ID rows) are as efficient as possible.

Triggers

Triggers can sometimes be quite complex little programs. If coded incorrectly, they can do an amazing amount of damage. As such, it pays to learn quite a lot before using them. Below are some very brief notes, but please refer to the official DB2 documentation for a more detailed description. See also page 311 for a brief chapter on triggers. Individual triggers are defined on a table, and for a particular type of DML statement:

  • Insert.
  • Update.
  • Delete.

A trigger can be invoked once per:

  • Row changed.
  • Statement run.

A trigger can be invoked:

  • Before the change is made.
  • After the change is made.

Before triggers change input values before they are entered into the table and/or flag an error. After triggers do things after the row is changed. They may make more changes (to the target table, or to other tables), induce an error, or invoke an external program. SQL statements that select the changes made by DML (see page 64) cannot see the changes made by an after trigger if those changes impact the rows just changed.

The action of one "after" trigger can invoke other triggers, which may then invoke other triggers, and so on. Before triggers cannot do this because they can only act upon the input values of the DML statement that invoked them. When there are multiple triggers for a single table/action, each trigger is run for all rows before the next trigger is invoked - even if defined "for each row". Triggers are invoked in the order that they were created.
Customer-Balance - Insert Trigger

For each row inserted into the Customer-Balance table we need to do the following:

  • Set the num-sales to zero.
  • Set the total-sales to zero.
  • Set the update-timestamp to the current timestamp.
  • Set the insert-timestamp to the current timestamp.

All of this can be done using a simple before trigger:

Set values during insert

Customer-Balance - Update Triggers

For each row updated in the Customer-Balance table we need to do:

  • Set the update-timestamp to the current timestamp.
  • Prevent updates to the insert-timestamp, or sales fields.

We can use the following trigger to maintain the update-timestamp:

Set update-timestamp during update

We can prevent updates to the insert-timestamp with the following trigger:

Prevent update of insert-timestamp

We don't want users to update the two sales counters directly. But the two fields do have to be updated (by a trigger) whenever there is a change to the us-sales table. The solution is to have a trigger that prevents updates if there is no corresponding row in the us-sales table where the update-timestamp is the current timestamp:

Prevent update of sales fields

US-Sales - Insert Triggers

For each row inserted into the US-sales table we need to do the following:

  • Determine the invoice-number, which is unique over multiple tables.
  • Set the update-timestamp to the current timestamp.
  • Set the insert-timestamp to the current timestamp.
  • Add the sale-value to the existing total-sales in the customer-balance table.
  • Increment the num-sales counter in the customer-balance table.

The invoice-number is supposed to be unique over several tables, so we cannot generate it using an identity column. Instead, we have to call the following external sequence:

Define sequence

Once we have the above, the following trigger will take of the first three items:

Insert trigger

We need to use an "after" trigger to maintain the two related values in the Customer-Balance table. This will invoke an update to change the target row:

Propagate change to Customer-Balance table

US-Sales - Update Triggers

For each row updated in the US-sales table we need to do the following:

  • Set the update-timestamp to the current timestamp.
  • Prevent the customer-ID or insert-timestamp from being updated.
  • Propagate the change to the sale-value to the total-sales in the customer-balance table.

We can use the following trigger to maintain the update-timestamp:

Maintain update-timestamp

Prevent updates to selected columns

We need to use another "after" trigger to maintain sales values in the Customer-Balance table:

Propagate change to Customer-Balance table


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

DB2 Using SQL Topics