Learning Visually with Examples [Electronic resources]

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