DB2 LUW db2look DDL Extraction

db2look  is one of the efficient and handy tool for DBAs. Be it migration across Platforms or moving working DDL from one environment to other always involves extracting DDL(Create, Alter Drop statements). 

From the description in IBM's knowledge center, "db2look command generates the DDL statements by object type. Note that this command ignores all objects under SYSTOOLS schema except  user-defined functions and stored procedures."

To generate DDL, syntax of db2look would be like below

Syntax: db2look -d <dbname> -e -a
Example: db2look -d SAMPLE -e -a

To generate DDL of a specific table, E.g. DB2INST1.EMPLOYEE from SAMPLE database

Syntax: db2look -d <dbname> -e -a -z <schemaname> -t <tablename>
Example: db2look -d SAMPLE -e -a -z DB2INST1 -t EMPLOYEE

To generate DDL of a specific table without views(run "-noview" option without -a)

Syntax: db2look -d <dbname> -e -z <schemaname> -t <tablename> -noview
Example: db2look -d SAMPLE -e -z DB2INST1 -t EMPLOYEE -noview

To generate DDL of a specific table along with "drop" statement, use "-dp"

Syntax: db2look -d <dbname> -e -z <schemaname> -t <tablename> -dp
Example: db2look -d SAMPLE -e -z DB2INST1 -t EMPLOYEE -dp

To generate DDL for storage related objects(Bufferpools, Tablespaces, Storagegroups), use "-l"

Syntax: db2look -d <dbname> -z <schemaname> -t <tablename> -l
Example1: db2look -d SAMPLE -l

Example2: db2look -d SAMPLE -z DB2INST1 -t EMPLOYEE -l

To generate DCL(grant) or Permissions, use "-x"

Syntax: db2look -d <dbname> -x
Example: db2look -d SAMPLE -x

To generate the mimic statements, use "-m". Mimic statements are used to replicate statistics in other database with/without data. 

Syntax: db2look -d <dbname> -m

Syntax: db2look -d dbname -m -r -a -o <outfile>
Example: db2look -d SAMPLE -m

Example2: db2look -d dbname -m -r -a -o <outfile>

To store the output of generated DDL in a file, use "-o <outputfilename>"

Syntax: db2look -d <dbname> -e -a -z <schemaname> -t <tablename> -o <dbname>.<schemaname>.<tablename>.db2look.out
Example: db2look -d SAMPLE -e -a -z DB2INST1 -t EMPLOYEE -o DB2INST1.EMPLOYEE.db2look.out

To generate db2look statements for numerous tables,

db2 -x +o -z db2look_common_schema.ddl "SELECT 'db2look -d <db_name> -e -z ' || '<schema_name>' || ' -T ' || TABNAME ||  ' -x  -dp -o '  || 'schema_name' || '.' || TABNAME ||'.out' FROM SYSCAT.TABLES WHERE TABSCHEMA = '<schema_name>' AND TYPE = 'T'"

To generate DDL of a stored procedure, use below statement

db2 "export to filename.del of del lobs to DIR_NAME modified by lobsinfile select * from syscat.routines where ROUTINESCHEMA='procschema' and ROUTINENAME='procname' with ur"