Filter
Top Products
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-5
shows 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 -l option 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 -o option 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.