Section 12.8. Generating Data Definition Language

12.8. Generating Data Definition Language

So far this chapter has introduced tools and utilities that you can use to extract data and table definitions using export. In cases when you just want to extract the definition of a table, the db2look command comes very handy.

db2look extracts the Data Definition Language (DDL) of database objects. Besides that, the tool can also generate the following:

• UPDATE statistics statements

• Authorization statements such as GRANT statements (also known as the Data Control Language (DCL)

• update commands for the following Database Manager Configuration parameters:

  - cpuspeed

  - intra_parallel

  - comm_bandwidth

  - nodetype

  - federated

  - fed_noauth

• update commands for the following database configuration parameters:

  - locklist

  - dft_degree

  - maxlocks

  - avg_appls

  - stmtheap

  - dft_queryopt

• The db2set command for the following DB2 registry variables:

  - DB2_PRED_FACTORIZE

  - DB2_CORRELATED_PREDICATES

  - DB2_LIKE_VARCHAR

  - DB2_SORT_AFTER_TQ

  - DB2_HASH_JOIN

  - DB2_ORDERED_NLJN

  - DB2_NEW_CORR_SQ_FF

  - DB2_PART_INNER_JOIN

  - DB2_INTERESTING_KEYS

The syntax diagram for the db2look command in Figure 12.33 shows all the supported options.

Figure 12.33. Syntax diagram of the db2look command

 >>-db2look---d--DBname--+----+--+-------------+----------------->                         '--e-'  '--u--Creator-' >--+------------+--+-----------------------------------+-------->    '--z--schema-'  '-+---------------+--+------------+-'                      |     .-------. |  '--tw--Tname-'                      |     V       | |                      '--t----Tname-+-' >--+---------------+--+----+--+-----------+--+----+------------->    |     .-------. |  '--h-'  '--o--Fname-'  '--a-'    |     V       | |    '--v----Vname-+-' >--+--------------------+--+----+--+----+--+-----+--+----+------>    '--m--+----+--+----+-'  '--l-'  '--x-'  '--xd-'  '--f-'          '--c-'  '--r-' >--+----------------+--+----+--+----+--+----+--+---------+------>    '--td--delimiter-'  '--p-'  '--s-'  '--g-'  '--noview-' >--+--------------------------+--+-----------------+------------>    '--i--userid---w--password-'  +--wrapper--Wname-+                                  '--server--Sname--' >--+--------+--------------------------------------------------><    '--nofed-' 

Refer to the DB2 Command Reference Manual for more information about each option. The following examples demonstrate how the command can be used.

• In the sample database, the command generates the DDL of objects created by db2admin under the schema prod. It also generates authorization statements. The output file db2look.sql captures this result..

   db2look d sample u db2admin z prod e x o db2look.sql 

  
  

• In the sample database, the command extracts the DDL from the staff, department, and employee tables, and generates UPDATE statements used to replicate statistics of the tables and the associated runstats commands.

   db2look d sample t staff department employee m -r 

  
  

• In the sample database, the command generates the DDL for all the database objects including the authorization statements, and stores the result in db2look.sql.

   db2look d sample xd o db2look.sql