Summary: 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:
Create Table 000038000 ABC 000039000 (COL_D INT) 000039010 ; 000034000
The correct script should look like this :
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 IBM RPG Analyzers extension.
