DROP TABLE "ITSO"."STAFF";

RENAME TABLE "ITSO"."SHADOWTABLE" to "STAFF";

ALTER TABLE "ITSO"."STAFF"

ADD CONSTRAINT "C_JOB" CHECK ("JOB" IN ( 'Mgr', 'Clerk', 'Sales'));

CREATE TRIGGER do_not_del_sales NO CASCADE BEFORE DELETE ON itso.staff

REFERENCING

OLD AS oldstaff FOR EACH ROW MODE DB2SQL WHEN(oldstaff.job = 'Sales') BEGIN ATOMIC SIGNAL SQLSTATE '75000' ('Sales staff cannot be deleted... see the DO_NOT_DEL_SALES trigger.');--

END;

5.5.3 Automating update using DB2 metadata with a Java application

So far we have assumed that we knew the delta between the current and the new configuration. In this section, we discuss how you can determine this delta. The delta is created based on a comparison of two database configurations. One way of achieving this is by utilizing the DB2 metadata. Whenever an object is created in DB2, metadata is stored in the system catalog tables. You can access these tables through a wide set of views supplied by DB2.

Instead of manually keeping track of the changes and manually generate the delta script, you can use an application to do it automatically based the DB2 metadata. We demonstrate this automation with a Java application.

The DB2 system catalog tables for database layout, objects, and object dependences are as follows:

￿Database layout:

Table 5-3lists where to find information about database layout in the DB2 system catalog.

Table 5-3 DB2 metadata for database layout

Database layout item

DB2 metadata

 

 

Tablespace

sysibm.systablespaces

 

 

Bufferpool

sysibm.sysbufferpools

 

 

￿Database objects:

Table 5-4lists where to find information about some of the most important database objects in the DB2 system catalog.

246DB2 Deployment Guide

Page 260
Image 260
IBM manual DB2 metadata for database layout, 246 DB2 Deployment Guide