Generate DDL for a Db2 database

Introduction

this page explains how to generate DDL for a Db2 database using the using the db2look tool tool for use with the SQL Analyzer extension.

Note that the db2look tool is only available with an installation of a Db2 LUW instance. If you are using a Db2 z/OS instance, CAST recommends the use of the RC/Migrator tool which is part of the CA Database Management Solution for Db2 for Z/OS, Version 19. There are no instructions provided for this.

Extraction example

The db2look tool provided by IBM generates the DDL statements by object type. See the full documentation for the tool here: https://www.ibm.com/support/knowledgecenter/en/SSEPGG_10.5.0/com.ibm.db2.luw.admin.cmd.doc/doc/r0002051.html.

For example:

DDL extraction of all objects from the TEST database

db2look -d TEST -a -e -o c:\temp\DB2_SQL\TEST_DDL.sql

DDL extraction for a specific schema using a specific user

db2look -d TEST -z CASTPUBS -i db2admin -w db2admin -e -o c:\temp\DB2_SQL\CASTPUBS_DDL.sql

Check if your code is valid SQL

For example, the following is not valid SQL, it seems to be more Mainframe script:

Mainframe alike script - NOK

Create Table                 000038000
    ABC                     000039000
    (COL_D INT)             000039010
    ;                       000034000 

 The correct script should look like this :

SQL alike script - OK

Create Table                 
    ABC                     
    (COL_D INT)             
    ;                       

Generating XXL tablesize statistics

See SQL Analyzer - working with XXL or XXS tables.

Db2 for i physical and logical files

Starting with 3.5.4-funcrel, SQL Analyzer is able to create Table/Unique Key/View declared in physical/logical files. In order to extract and deliver them, you should refer to source code delivery instructions for the IBM RPG Analyzer extension.