IBM manual Db2look, Dependencies between database objects, 220 DB2 Deployment Guide

ADD CONSTRAINT "PK_DEPARTMENT" PRIMARY KEY ("DEPTNO");

-- DDL Statements for index

CREATE INDEX "ITSO"."XDEPT2" ON "ITSO"."DEPARTMENT" ("MGRNO" ASC)

ALLOW REVERSE SCANS;

-- DDL Statements for alias

CREATE ALIAS "ITSO"."DEPT" FOR "ITSO"."DEPARTMENT";

Dependencies between database objects

When creating the DDL statements, you should be aware of object dependencies and arrange the DDL statements in the proper sequence. If object B depends on object A, then object A must be created before object B. In terms of the DDL statements, you must have the DDL statement for object A executed before the DDL statement for object B. This is a rule of thumb, but exceptions do exist.

If we look at the DDL statements in Example 5-4,we notice that the table is created before the primary key and the alias — obeying the rule of thumb. The exception is the alias. We can create the alias before the table, this will only lead to a warning. On the other hand, we are not allowed to create the primary key up front, this will lead to an error.

db2look

DB2 provides one powerful tool, db2look, that can be used to extract database layout and database object definitions from an existing database. Example 5-5shows how to extract the information from the sample database and save the result in the file itsodb.ddl.

Example 5-5 Using db2look to get DDL statements

db2look -d itsodb -l -e -o itsodb.ddl

The -loption generates the DDLs for the database layout. Using the -e option, you can get a set of DDL statements that define all the objects in the database. The -ooption defines the output file.

Creating a database with the output from db2look

The output generated by db2look can be used to replicate the database structures. It might require modification because db2look does not preserve all of the object dependencies. The output, however, is a simple text file, and it can be edited in any tool such as VI on UNIX or Notepad on Windows.

220DB2 Deployment Guide