Systems Engineering and RDBMS

Generating DDL in DB2 LUW

Posted by decipherinfosys on June 13, 2007

In our previous blog posts, we had discussed how to generate DDLs in Oracle and SQL Server. In this one, we will cover the same feature in DB2 LUW. One of the ways to do this is to use the db2look command. When executed with proper parameters, it extracts DDL from the database it is connected to and puts it into an output file. We can use this file later on with some changes to create objects in new database.

To see what parameters can be passed to db2look, invoke it by simply typing db2look from command window. You can pick and choose command parameters you need. Following is the command to generate DDL in its simplest form followed by a output generated by command.

db2look -d decipher -e -o d:\decipher\test_db2.sql

— USER is: DECIPHER
— Creating DDL for table(s)
— Output is sent to file: d:\decipher\test_db2.sql

-d is the parameter for database name. It is required parameter. When command will be invoked, it will establish new connection with this database to extract the information from it.

-o specifies the output file name. This is the file in which output of db2look command will be written to.

-e to indicate, we need to extract DDL statements for database objects.

For complete list of objects for which DDL can be extracted when –e parameter is used, and for information on all other command parameters, please look at the link http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/core/r0002051.htm

Following is the formatted content of the test_db2.sql file from our test run.

CONNECT TO DECIPHER;

————————————————
— DDL Statements for table “DECIPHER “.”TEST”
————————————————

CREATE TABLE “DECIPHER “.”TEST” (
“TEST_ID” INTEGER NOT NULL ,
“TEST_DESC” VARCHAR(50) )
IN “USERSPACE1” ;

— DDL Statements for indexes on Table “DECIPHER “.”TEST”

CREATE INDEX “DECIPHER “.”TEST_IND_1” ON “DECIPHER “.”TEST”
(“TEST_DESC” ASC)
ALLOW REVERSE SCANS;

— DDL Statements for primary key on Table “DECIPHER “.”TEST”

ALTER TABLE “DECIPHER “.”TEST”
ADD CONSTRAINT “PK_TEST” PRIMARY KEY
(“TEST_ID”);

COMMIT WORK;

CONNECT RESET;

TERMINATE;

When db2look utility is used with appropriate parameters, it can also
• generate update statements to replicate statistics.
• omit extracting DDL for views.
• generate db2set, UPDATE DATABASE CONFIGURATION and UPDATE DATABASE MANAGER CONFIGURATION commands so that the new database has exact same registry variables and configuration parameters to simulate the source database environment as close as possible.

Sorry, the comment form is closed at this time.

 
%d bloggers like this: