Command Reference

db2look - DB2 Statistics Extraction Tool

Generates the update statements required to make the catalog statistics of a test database match those of a 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. This tool queries the system catalogs of a database, and outputs table space, table, index, and column information about each table in that database.

Authorization

SELECT privilege on the system catalogs.

Required Connection

None. This command establishes a database connection.

Command Syntax

>>-db2look---d--DBname----+--------------+---------------------->
                          '--u--Creator--'
 
>-----+------------------------------------+-------------------->
      '-+----+--+----+--+----+---t--Tname--'
        '--s-'  '--g-'  '--a-'
 
>-----+------------------------------------------------------------+>
      '-+----+---o--Fname--+----+--+----+--+----+--+----+--+----+--'
        '--p-'             '--e-'  '--m-'  '--c-'  '--r-'  '--h-'
 
>--------------------------------------------------------------><
 

Command Parameters

-d DBname

Alias name of the production database that is to be queried.

-u Creator

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:

1. This option removes all LaTeX and .tmp PostScript files.

2. Required non-IBM software: LaTeX, dvips.

3. The psfig.tex file must be in the LaTeX input path.

-g

Use a graph to show fetch page pairs for indices.

Notes:

1. This option generates a filename.ps file, as well as the LaTeX file.

2. Required non-IBM software: Gnuplot.

3. The psfig.tex file must be in the LaTeX input path.

-a

Generate statistics for all users on the database. If used with option -e, it generates the DDL for all user tables in the database, but statistics are not generated.

Notes:

1. Some DDL characteristics are not extracted by db2look.

2. 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=<username> must be issued for the session.

-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 to recreate database data objects. This option generates a CLP script containing DDL statements to recreate tables and indexes. The CLP script can then be run against another database to recreate the database. This option can be used in conjunction with the -m option. It does not currently support:

• Creation of triggers

• Creation of UDFs

• Reference to UDFs.

-m

Run the program in mimic mode. This option generates a CLP script containing all the UPDATE statements required to capture the catalog statistics of the production database. The CLP script can then be run against a smaller test database, and the updated test database can be used to validate access plans for production. The -p, -g, and -s options are ignored in mimic mode.

-c

Do not generate COMMIT statements in mimic mode. The default action is to generate COMMIT statements. In addition, do not generate CONNECT or CONNECT RESET statements. If option -m is not specified, this option is ignored.

-r

Do not include the RUNSTATS command in mimic mode. The default action is to issue the RUNSTATS command. If option -m is not specified, this option is ignored.

-h

Display help information. When this option is specified, all other options are ignored, and only the help information is displayed.

Examples

Write the statistics for tables created by USER in database DEPARTMENT in LaTeX format to file output.tex:

   db2look -d department -o output

Write the statistics for all tables in database DEPARTMENT in LaTeX format to file output.tex:

   db2look -a -d department -o output

Write the statistics for all tables in database DEPARTMENT in plain text format to file output.txt:

   db2look -p -a -d department -o output

Write the statistics for all tables in database DEPARTMENT for user JAMES. Use a graph to show page fetch pairs. Save the LaTeX output in file output.tex. Save the PostScript output in file output.ps:

   db2look -g -s -u james -d department -o output

Write the replica CLP commands for table EMPLOYEE in database PAYROLL created by anyone to file employee.mimic:

   db2look -m -a -d payroll -t employee -o employee.mimic

[ DB2 List of Books | Search the DB2 Books ]