db2look - DB2 Statistics and DDL Extraction Tool

Extracts the required DDL statements to reproduce the database objects of a production database on a test database. This tool can also generate the required UPDATE statements used to replicate the statistics on the objects in a test database, as well as the update database configuration and update database manager configuration parameters and the db2set statements so that the registry variables and configuration parameter settings on the test database match those of the production database.
It is often advantageous to have a test system contain a subset of the production system's data. However, access plans selected for such a test system are not necessarily the same as those that would be selected for the production system. Both the catalog statistics and the configuration parameters for the test system must be updated to match those of the production system. Using this tool makes it possible to create a test database where access plans are similar to those that would be used on the production system.
Authorization
SELECT privilege on the system catalogs.
Required Connection
None. This command establishes a database connection.
Command Syntax
>;>;-db2look---d--DBname----+--------------+---------------------->;
'--u--Creator--'
>;-----+-------------------------------------------------+------->;
'-+----+--+----+--+----+--+----+--+----+--+----+--'
'--s-'  '--g-'  '--a-'  '--h-'  '--r-'  '--c-'
>;-----+---------------------------------------------------------------------------------------------------------------------------------+>;
'-+----+---o--Fname--+-------------------+---+-------------------+---+---------------------------------------------------------+--'
'--p-'             '--e--+----------+--'   '--m--+----------+--'   '-+----+--+----+--+-----------+--+-------------+--+----+--'
'--t Tname-'            '--t Tname-'        '--l-'  '--x-'  '--i userid-'  '--w password-'  '--f-'
>;-------------------------------------------------------------->;<
Command Parameters
-d DBname
Alias name of the production database that is to be queried. DBname can be the name of a DB2 UDB for UNIX, Windows, OS/2, or DB2 UDB for OS/390 database. If the DBname is a DB2 UDB for OS/390 database, the db2look utility will extract the DDL and UPDATE statistics statements for OS/390 objects. These DDL and UPDATE statistics statements are statements applicable to a DB2 UDB database and not to a DB2 for OS/390 database. This is useful for users who want to extract OS/390 objects and recreate them in a DB2 UDB database.
If DBname is an OS/390 database then the db2look output is limited to the following:
Generate DDL for Tables, Indexes and Views
Generate UPDATE statistics statements for Tables, Columns, Column distributions and Indexes
-u Creator
Creator ID. Limits output to objects with this creator id. If option -a is specified, this parameter is ignored. If neither -u nor -a is specified, the environment variable USER is used.
-s
Generate a PostScript file.
Notes:
This option removes all LaTeX and .tmp PostScript files.
Required non-IBM software: LaTeX, dvips.
The psfig.tex file must be in the LaTeX input path.
-g
Use a graph to show fetch page pairs for indices.
Notes:
This option generates a filename.ps file, as well as the LaTeX file.
Required non-IBM software: Gnuplot.
The psfig.tex file must be in the LaTeX input path.
-a
When this option is specified the output is not limited to the objects created under a particular creator ID. All objects created by all users are considered. For example, if this option is specified with the -e option, DDL statements are extracted for all objects in the database. If this option is specified with the -m option, UPDATE statistics statements are extracted for all user created tables and indexes in the database. Note: If neither -u nor -a is specified, the environment variable USER is used. On UNIX based systems, this variable does not have to be explicitly set; on Windows NT, however, there is no default value for the USER environment variable: on this platform, a user variable in the SYSTEM variables must be set, or a set USER=; must be issued for the session.
-h
Display help information. When this option is specified, all other options are ignored, and only the help information is displayed.
-r
When this option is specified in conjunction with the -m option, db2look does not generate the RUNSTATS command. The default action is to generate the RUNSTATS command. The -r option is ignored if the -m option is not specified.
-c
When this option is specified in conjunction with the -m option, db2look does not generate COMMIT, CONNECT and CONNECT RESET statements. The default action is to generate these statements. The -c option is ignored if the -m option is not specified.
-t Tname
Table name. Limits the output to a particular table.
-p
Use plain text format.
-o Fname
If using LaTeX format, write the output to filename.tex. If using plain text format, write the output to filename.txt. If this option is not specified, output is written to standard output.
-e
Extract DDL statements for database objects. This option can be used in conjunction with the -m option. DDL for the following database objects are extracted when using the -e option:
Tables
Views
Automatic Summary Tables (AST)
Aliases
Indexes
Triggers
User defined Distinct Types
Primary Key, RI, and CHECK constraints
User Defined Structured Types
User Defined Functions
User defined Methods
User defined Transforms
Note: The DDL generated by db2look can be used to recreate user defined functions successfully. However, the user source code that a particular user defined function references (the EXTERNAL NAME clause, for example) must be available in order for the user defined function to be usable.
-m
Generate the required UPDATE statements to replicate the statistics on tables, columns and indexes. The -p, -g, and -s options are ignored when the -m option is specified.
-l
If this option is specified, then the db2look utility will generate DDL for user defined table spaces, nodegroups and buffer pools. DDL for the following database objects is extracted when using the -l option:
User defined table spaces
User defined nodegroups
User defined buffer pools
-x
If this option is specified, the db2look utility will generate authorization DDL (GRANT statement, for example).
-i userid
Use this option when working with a remote database.
-w password
Used with the -i option, this parameter allows the user to run db2look against a database that resides on a remote system. The user ID and the password are used by db2look to logon to the remote system.
-f
Use this option to extract configuration parameters and registry variables. Note: Only configuration parameters and registry variables that affect the DB2 query optimizer are extracted.
Examples
Generate the DDL statements for objects created by user walid in database DEPARTMENT. The db2look output is sent to file db2look.sql:
db2look -d department -u walid -e -o db2look.sql
Generate the UPDATE statements to replicate the statistics for the tables and indexes created by user walid in database DEPARTMENT. The output is sent to file db2look.sql:
db2look -d department -u walid -m -o db2look.sql
Generate both the DDL statements for the objects created by user walid and the UPDATE statements to replicate the statistics on the tables and indexes created by the same user. The db2look output is sent to file db2look.sql:
db2look -d department -u walid -e -m -o db2look.sql
Generate the DDL statements for objects created by all users in the database DEPARTMENT. The db2look output is sent to file db2look.sql:
db2look -d department -a -e -o db2look.sql
Generate the DDL statements for all user defined nodegroups, buffer pools and table spaces. The db2look output is sent to file db2look.sql:
db2look -d department -l -o db2look.sql
Generate the UPDATE statements for the database and database manager configuration parameters, as well as the db2set statements for the registry variables in database DEPARTMENT. The db2look output is sent to file db2look.sql:
db2look -d department -f -o db2look.sql
Generate the DDL for all objects in database DEPARTMENT, the UPDATE statements to replicate the statistics on all tables and indexes in database DEPARTMENT, the GRANT authorization statements, the UPDATE statements for the database and database manager configuration parameters, the db2set statements for the registry variables, and the DDL for all user defined nodegroups, buffer pools and table spaces in database DEPARTMENT. The output is sent to file db2look.sql.
db2look -d department -a -e -m -l -x -f -o db2look.sql