Extracts the Data Definition Language (DDL)
statements that are required to reproduce the database objects of
a production database on a test database.
The db2look command generates the DDL statements
by object type. Note
that this command ignores all objects under SYSTOOLS schema except
user-defined functions and stored procedures.
It is often advantageous to have a test
system that contains a subset of the data of a production system,
but access plans selected for such a test system are not necessarily
the same as those that would be selected for the production system.
However, using the db2look tool, you can create
a test system with access plans that are similar to those that would
be used on the production system. You can use this tool to generate
the UPDATE statements that are required to replicate the catalog statistics
on the objects in a production database on a test database. You can
also use this tool to generate UPDATE DATABASE CONFIGURATION, UPDATE
DATABASE MANAGER CONFIGURATION, and db2set commands
so that the values of query optimizer-related configuration parameters
and registry variables on a test database match those of a production
database.
You should check the DDL statements that are generated
by the db2look command because they might not reproduce
all characteristics of the original SQL
objects. For table spaces on partitioned database environments, DDL
might not be complete if some database partitions are not active.
Make sure all database partitions are active using the ACTIVATE
DATABASE command.
Authorization
SELECT privilege on the system
catalog tables.
In some cases, such as generating
table space container DDL, you will require one of the following:
- SYSADM
- SYSCTRL
- SYSMAINT
- SYSMON
- DBADM
Command syntax
>>-db2look-- -d--DBname--+-----+--+--------------+-------------->
'- -e-' '- -u--creator-'
>--+-------------+--+-------------------------------------+----->
'- -z--schema-' '-+----------------+--+-------------+-'
| .-------. | '- -tw--Tname-'
| V | |
'- -t----Tname-+-'
>--+------+--+------+--+----------------+--+-----+--+------+---->
'- -ct-' '- -dp-' | .-------. | '- -h-' '- -ap-'
| V | |
'- -v----Vname-+-'
>--+------------+--+-----+--+-----------------------+----------->
'- -o--Fname-' '- -a-' '- -m--+-----+--+-----+-'
'- -c-' '- -r-'
(1) (2)
>--+-----+------+--------+------+---------+--+-----+------------>
'- -l-' +- -x----+ +- -xd----+ '- -f-'
'- -xdep-' '- -xddep-'
>--+-----------------+--+----------+---------------------------->
'- -td--delimiter-' '- -noview-'
>--+----------------------------+--+-------+--+--------+-------->
'- -i--userid-- -w--password-' '- -wlm-' '- -wrap-'
>--+------------------+--+---------+--+-----------+------------->
+- -wrapper--Wname-+ '- -nofed-' '- -fedonly-'
'- -server--Sname--'
>--+-------+--+---------------------------+--+-------+---------->
'- -mod-' '- -xs--+-----------------+-' '- -cor-'
'- -xdir--dirname-'
>--+----------------+------------------------------------------><
'- -noimplschema-'
Notes:
- You cannot specify both the -x parameter
and -xdep parameter
- You cannot specify both the -xd parameter
and -xddep parameter
Command parameters
- -d DBname
- Alias name of the production database that is to be queried. DBname can
be the name of a DB2® for Linux, UNIX, and Windows or DB2 Version 9.1 for z/OS® database. If the DBname is
a DB2 for z/OS database,
the db2look command generates the following statements
for OS/390® and z/OS objects:
- DDL statements for tables, indexes, views, and user-defined distinct
types
- UPDATE statistics statements for tables, columns, column distributions,
and indexes
These DDL and UPDATE statistics statements are applicable to
a DB2 for Linux, UNIX, and Windows database
and not to a DB2 for z/OS database.
These statements are useful if you want to extract OS/390 and z/OS objects
and re-create them in a DB2 for Linux, UNIX, and Windows database.
- -e
- Extracts DDL statements for the following database objects:
If you use DDL statements that are generated by the db2look command
to re-create a user-defined function, the source code that the function
references (the EXTERNAL NAME clause, for example) must be available
for the function to be usable.
- -u creator
- Generates DDL statements for objects that were created with the
specified creator ID. Limits output to objects that were created with
the specified creator ID. The output does not include any inoperative
objects. To display inoperative objects, use the -a parameter.
If you specify the -a parameter, the -u parameter
is ignored.
- -z schema
- Generates DDL statements for objects that have the specified schema
name. Limits output to objects that have the specified schema name.
The output does not include any inoperative objects. To display inoperative
objects, use the -a parameter. If you do not
specify the -z parameter, objects with all schema
names are extracted. If you specify the -a parameter,
the -z parameter is ignored. This parameter is
also ignored for federated DDL statements.
- -t Tname1 Tname2 ... TnameN
- Generates DDL statements for the specified tables and their dependent
objects. Limits output to the tables that are specified in the table
list and generates the DDL statements for
all dependent objects of all user specified tables. The maximum
number of tables is 30.
The dependent objects include:
- Comments
- Indexes
- Primary keys
- Unique keys
- Aliases
- Foreign key constraints
- Check constraints
- Views
- Triggers
Specify the list as follows:- Separate table names by a blank space.
- Enclose case-sensitive names and double-byte character set (DBCS)
names with the backslash (\) and double quotation marks (" ") (for
example, \" MyTabLe \").
- Enclose multiword table names with the backslash and two sets
of double quotation marks (for example, "\"My Table\"")
to prevent the pairing from being evaluated word-by-word by the command
line processor (CLP). If you use only one set of double quotation
marks (for example, "My Table"), all words are converted
into uppercase, and the db2look command looks for
an uppercase table name (for example, MY TABLE).
If you specify the -t parameter with
the -l parameter, partitioned tables are supported.
You can use two-part table names of the format schema.table to
fully qualify a table name without using the -z schema parameter.
Use a two-part table name when the table has dependent objects that
are in a different schema than that of the table and you require DDL
statements to be generated for the dependent objects. If you use the -z schema parameter
to specify the schema, the parameter excludes dependent objects that
do not have the same parent schema, thereby preventing the generation
of DDL statements for the dependent objects.
- -tw Tname
- Generates DDL statements for tables with names that match the
pattern that you specify with Tname and generates
the DDL statements for all dependent objects of those tables. Tname must
be a single value only. The underscore character (_) in Tname represents
any single character. The percent sign (%) represents a string of
zero or more characters. When -tw is specified,
the -t option is ignored.
You
can use two-part table names of the format schema.table to
fully qualify a table name without using the -z schema parameter.
Use a two-part table name when the table has dependent objects that
are in a different schema than that of the table and you require DDL
statements to be generated for the dependent objects. If you use the -z schema parameter
to specify the schema, the parameter excludes dependent objects that
do not have the same parent schema, thereby preventing the generation
of DDL statements for the dependent objects.
- -ct
- Generates DDL statements by object creation time. The object DDL
statements might not be displayed in correct dependency order. If
you specify the -ct parameter, the db2look command
supports only the following additional parameters: -e, -a, -u, -z, -t, -tw, -v, -l, -noview,
and -wlm. If you use the -ct parameter
with the -z and -t parameters,
the db2look command generates the required UPDATE
statements to replicate the statistics on tables, statistical views,
columns, and indexes.
- -dp
- Generates a DROP statement before a CREATE statement. The DROP
statement might not work if there is an object that depends on the
dropped object. For example, you cannot drop a schema if there is
a table that depends on the schema, and you cannot drop a user-defined
type or user-defined function if there is a type, function, trigger,
or table that depends on that user-defined type or user-defined function.
For typed tables, the DROP TABLE HIERARCHY statement is generated
for the root table only. A DROP statement is not generated for indexes,
primary and foreign keys, and constraints because they are always
dropped when the table is dropped. You cannot drop a table that has
the RESTRICT ON DROP attribute.
- -v Vname1 Vname2 ... VnameN
- Generates DDL statements for the specified views, but not for their dependent objects. The maximum
number of views is 30. The rules governing case-sensitive, DBCS, and
multiword table names also apply to view names. If you specify the -t parameter,
the -v parameter is ignored.
You can use a two-part view name of the format schema.view to
fully qualify a view.
- -h
- Display help information. If you specify this parameter, all other
parameters are ignored.
- -ap
- Generates the AUDIT USING statements that are required to associate
audit policies with other database objects.
- -o Fname
- Writes the output to the Fname file.
If you do not specify an extension, the .sql extension
is used. If you do not specify this parameter, output is written to
standard output.
- -a
- Generates DDL statements for objects that were created by any
user, including inoperative objects. For example, if you specify this
parameter with the -e parameter, DDL statements
are extracted for all objects in the database. If you specify this
parameter with the -m parameter, UPDATE statistics
statements are extracted for all user-created tables and indexes in
the database.
If you do not specify either the -u or
the -a parameter, the USER environment variable
is used. On UNIX operating systems,
you do not have to explicitly set this variable. On Windows operating systems, however, there
is no default value for the USER environment variable. Therefore,
you must set a user variable in the SYSTEM variables or issue the set
USER=username command for the session.
- -m
- Generates the UPDATE statements that are required to replicate
the statistics on tables, statistical views, columns, and indexes.
Using the -m parameter is referred to as running
in mimic mode.
- -c
- If you specify this option, the db2look command
does not generate COMMIT, CONNECT, and CONNECT RESET statements. The
default action is to generate these statements. This option is ignored
unless you also specify the -m or -e parameter.
- -r
- If you specify this option with the -m parameter,
the db2look command does not generate the RUNSTATS command.
The default action is to generate the RUNSTATS command.
Important: If
you intend to run the command processor script that is created using
the db2look command with the -m parameter
against another database (for example, to make the catalog statistics
of the test database match those in production), both databases must
use the same codeset, territory, collation, and uniqueness determination.
- -l
- Generates DDL statements for the following database objects:
- User-defined table spaces
- User-defined database partition groups
- User-defined buffer pools
- -x
- Generates authorization DDL statements such as GRANT statements.
The supported authorizations include the following ones:
- Columns: UPDATE, REFERENCES
- Databases: ACCESSCTRL, BINDADD, CONNECT, CREATETAB,
CREATE_EXTERNAL_ROUTINE, CREATE_NOT_FENCED_ROUTINE, DATAACCESS, DBADM,
EXPLAIN, IMPLICIT_SCHEMA, LOAD, QUIESCE_CONNECT, SECADM, SQLADM, WLMADM
- Exemptions
- Global variables
- Indexes: CONTROL
- Packages: CONTROL, BIND, EXECUTE
- Roles
- Schemas: CREATEIN, DROPIN, ALTERIN
- Security labels
- Sequences: USAGE, ALTER
- Stored procedures: EXECUTE
- Tables: ALTER, SELECT, INSERT, DELETE, UPDATE, INDEX, REFERENCE,
CONTROL
- Views: SELECT, INSERT, DELETE, UPDATE, CONTROL
- User-defined functions (UDFs): EXECUTE
- User-defined methods: EXECUTE
- Table spaces: USE
- Workloads: USAGE
Note: This parameter does not generate
authorization DDL statements for dependent objects when used with
either the -t or -tw parameter.
Use the -xdep parameter to generate authorization
DDL statements for parent and dependent objects.
- -xdep
- Generates authorization DDL statements, for example, GRANT statements,
for parent and dependent objects. This parameter is ignored if either
the -t or -tw parameter
is not specified. The supported authorizations include the following
ones:
- Columns: UPDATE, REFERENCES
- Indexes: CONTROL
- Stored procedures: EXECUTE
- Tables: ALTER, SELECT, INSERT, DELETE, UPDATE, INDEX, REFERENCE,
CONTROL
- Table spaces: USE
- User-defined functions (UDFs): EXECUTE
- User-defined methods: EXECUTE
- Views: SELECT, INSERT, DELETE, UPDATE, CONTROL
- -xd
- Generates authorization DDL statements, including authorization
DDL statements for objects whose authorizations were granted by SYSIBM at
object creation time. It does not generate the authorization DDLs for system catalog tables
and catalog views.
Note: This parameter
does not generate authorization DDL statements for dependent objects
when used with either the -t or -tw parameter.
Use the -xddep parameter to generate authorization
DDL statements for parent and dependent objects.
- -xddep
- Generates all authorization DDL statements for parent and dependent
objects, including authorization DDL statements for objects whose
authorizations were granted by SYSIBM at object creation time. This
parameter is ignored if either the -t or -tw parameter
is not specified.
- -f
- Extracts the configuration parameters and registry variables that
affect the query optimizer.
- -td delimiter
- Specifies the statement delimiter for SQL statements that are
generated by the db2look command. The default
delimiter is the semicolon (;). Use this parameter if you specify
the -e parameter because the extracted objects
might contain triggers or SQL routines.
- -noview
- Specifies that CREATE VIEW DDL statements will not be extracted.
- -i userid
- Specifies the user ID that the db2look command
uses to log on to a remote system. When you specify this parameter
and the -w parameter, the db2look command
can run against a database on a remote system. The local and remote
database must use the same DB2 version.
- -w password
- Specifies the password that the db2look command
uses to log on to a remote system. When you specify this parameter
and the -i parameter, the db2look command
can run against a database on a remote system. The local and remote
database must use the same DB2 version.
- -wlm
- Generates WLM-specific DDL output, which can serve to generate
CREATE and ALTER statements for the following items:
- Histograms
- Service classes
- Thresholds
- WLM event monitors
- Workloads
- Work action sets
- Work class sets
- -wrap
- Generates obfuscated versions of DDL statements for routines,
triggers, views, and PL/SQL packages.
- -wrapper Wname
- Generates DDL statements for federated objects that apply to the
specified wrapper. The federated DDL statements that might be generated
include the following ones:
- CREATE FUNCTION ... AS TEMPLATE
- CREATE FUNCTION MAPPING
- CREATE INDEX SPECIFICATION
- CREATE NICKNAME
- CREATE SERVER
- CREATE TYPE MAPPING
- CREATE USER MAPPING
- CREATE WRAPPER
- GRANT (privileges to nicknames, servers, indexes)
An error is returned if you do not specify a wrapper name or
specify more than one.
- -server Sname
- Generates DDL statements for federated objects that apply to the
specified server. The federated DDL statements that might be generated
include the following ones:
- CREATE FUNCTION ... AS TEMPLATE
- CREATE FUNCTION MAPPING
- CREATE INDEX SPECIFICATION
- CREATE NICKNAME
- CREATE SERVER
- CREATE TYPE MAPPING
- CREATE USER MAPPING
- CREATE WRAPPER
- GRANT (privileges to nicknames, servers, indexes)
An error is returned if you do not specify a server name or specify
more than one.
- -nofed
- Specifies that no federated DDL statements will be generated.
If you specify this parameter, the -wrapper and -server parameters
are ignored.
- -fedonly
- Specifies that only federated DDL statements will be generated.
- -mod
- Generates DDL statements for each module, and for all of the objects
that are defined in each module.
- -xs
- Exports all files that are necessary to register XML schemas and
DTDs at the target database and generates appropriate commands for
registering them. The set of XSR objects that is exported is controlled
by the -u, -z, and -a parameters.
- -xdir dirname
- Exports XML-related files into the specified path. If you do not
specify this parameter, all XML-related files are exported into the
current directory.
- -cor
- Generates DDL statements with the CREATE OR REPLACE clause, regardless
of whether or not the statements originally contained that clause.
- -noimplschema
- Specifies that CREATE SCHEMA DDL statements for implicitly created
schemas are not generated. If you specify this parameter, you must
also specify the -e parameter.
Examples
The following examples show how to
use the
db2look command:
- Generate the DDL statements for objects created by user walid in
database DEPARTMENT. The output is sent to the db2look.sql file.
db2look -d department -u walid -e -o db2look.sql
- Generate the DDL statements for objects that have schema name ianhe,
created by user walid, in database DEPARTMENT. The
output is sent to the db2look.sql file.
db2look -d department -u walid -z ianhe -e -o db2look.sql
- Generate the UPDATE statements to replicate the statistics for
the database objects created by user walid in database
DEPARTMENT. The output is sent to the db2look.sql file.
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 database
objects created by the same user. The output is sent to the db2look.sql file.
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 output is sent to the db2look.sql file.
db2look -d department -a -e -o db2look.sql
- Generate the DDL statements for all user-defined database partition
groups, buffer pools and table spaces. The output is sent to the db2look.sql file.
db2look -d department -l -o db2look.sql
- Generate the UPDATE statements for optimizer-related database
and database manager configuration parameters and the db2set commands
for optimizer-related registry variables in database DEPARTMENT. The
output is sent to the db2look.sql file.
db2look -d department -f -o db2look.sql
- Generate the db2set commands for optimizer-related
registry variables and the following statements for database DEPARTMENT:
- The DDL statements for all database objects
- The UPDATE statements to replicate the statistics on all tables
and indexes
- The GRANT authorization statements
- The UPDATE statements for optimizer-related database and database
manager configuration parameters
- The db2set commands for optimizer-related registry
variables
- The DDL statements for all user-defined database partition groups,
buffer pools, and table spaces
The output is sent to the db2look.sql file.
db2look -d department -a -e -m -l -x -f -o db2look.sql
- Generate all authorization DDL statements for all objects in database
DEPARTMENT, including the objects that were created by the original
creator. (In this case, the authorizations were granted by SYSIBM
at object creation time.) The output is sent to the db2look.sql file.
db2look -d department -xd -o db2look.sql
- Generate the DDL statements for objects created by all users in
the database DEPARTMENT. The output is sent to the db2look.sql file.
db2look -d department -a -e -td % -o db2look.sql
The
output can then be read by the CLP: db2 -td% -f db2look.sql
- Generate the DDL statements for objects in database DEPARTMENT,
excluding the CREATE VIEW statements. The output is sent to the db2look.sql file.
db2look -d department -e -noview -o db2look.sql
- Generate
the DDL statements for objects in database DEPARTMENT related to
specified tables. The output is sent to the db2look.sql file.
db2look -d department -e -t tab1 "\"My TaBlE2\"" -o db2look.sql
- Generate the DDL statements for all objects (federated and non-federated)
in the federated database FEDDEPART. For federated DDL statements,
only those that apply to the specified wrapper, FEDWRAP, are generated.
The output is sent to standard output.
db2look -d feddepart -e -wrapper fedwrap
- Generate a script file that includes only non-federated DDL statements.
The following system command can be run against a federated database
FEDDEPART and yet only produce output like that found when run against
a database which is not federated. The output is sent to the out.sql file.
db2look -d feddepart -e -nofed -o out
- Generate the DDL statements for objects that have schema name walid in
the database DEPARTMENT. The files required to register any included
XML schemas and DTDs are exported to the current directory. The output
is sent to the db2look.sql file.
db2look -d department -z walid -e -xs -o db2look.sql
- Generate the DDL statements for objects that were created by all
users in the database DEPARTMENT. The files that are required to register
any included XML schemas and DTDs are exported to the /home/ofer/ofer/ directory.
The output is sent to standard output.
db2look -d department -a -e -xs -xdir /home/ofer/ofer/
- Generate only WLM-specific DDL statements for database DEPARTMENT:
db2look -d department -wlm
- Generate the DDL statements for all objects in database DEPARTMENT:
db2look -d department -wlm -e -l
- Generate the DDL statements for both the
parent table TAB1 in schema TABLES and the dependent view of TAB1
that is called VIEW1 in the VIEWS schema in database DB1. The output
is sent to the db2look.sql file.
db2look -d DB1 -t TABLES.TAB1 -e -o db2look.sql
- Generate the DDL statements and authorization
DDL statements for the parent table TAB1 in schema TABLES and the
dependent view of TAB1 that is called VIEW1 in the VIEWS schema in
database DB1. The output is sent to the db2look.sql file.
db2look -d DB1 -t TABLES.TAB1 -e -xdep -o db2look.sql
- Generate the
RUNSTATS DDL statements on the TABLE1 table in mimic mode. The output
is sent to the db2look.sql file. Not all available
RUNSTATS clauses of the command are supported.
db2look -d DB1 -t TABLE1 -m -e -o db2look.sql
Usage notes
On Windows operating systems, you must issue
the db2look command from a DB2 command window.
By
default, the instance owner has the EXECUTE privilege on
db2look packages.
For other users to run the
db2look command, the
instance owner has to grant the EXECUTE privilege on
db2look packages.
To determine the
db2look package names, the
db2bfd command
can be used as follows:
cd .../sqllib/bnd
db2bfd -b db2look.bnd
db2bfd -b db2lkfun.bnd
db2bfd -b db2lksp.bnd
To create DDL statements
for federated objects, you must enable the use of federated systems
in the database manager configuration. After the
db2look command
generates the script file, you must set the
federated configuration
parameter to
YES before running the script. The following
db2look command
parameters are supported in a federated environment:
- -ap
- Generates AUDIT USING statements.
- -e
- Generates DDL statements for federated objects.
- -f
- Extracts federated-related information from the database manager
configuration.
- -m
- Extracts statistics for nicknames.
- -x
- Generates GRANT statements to grant privileges on federated objects.
- -xd
- Generates DDL statements to add system-granted privileges to federated
objects.
- -wlm
- Generates WLM-specific DDL statements.
If the nickname column and the remote table
column are of different data types, then the db2look command
will generate an ALTER COLUMN statement for the nickname column.
You
must modify the output script to add the remote passwords for the
CREATE USER MAPPING statements.
You must modify the db2look command
output script by adding AUTHORIZATION and PASSWORD to those CREATE
SERVER statements that are used to define a DB2 family instance as a data source.
Usage
of the
-tw option is as follows:
- To both generate the DDL statements for objects in the DEPARTMENT
database associated with tables that have names beginning with abc and
send the output to the db2look.sql file:
db2look -d department -e -tw abc% -o db2look.sql
- To generate the DDL statements for objects in the DEPARTMENT database
associated with tables that have a d as the second
character of the name and to send the output to the db2look.sql file:
db2look -d department -e -tw _d% -o db2look.sql
- The db2look command uses the LIKE predicate
when evaluating which table names match the pattern specified by the Tname argument.
Because the LIKE predicate is used, if either the _ character or the
% character is part of the table name, the backslash (\) escape character
must be used immediately before the _ or the %. In this situation,
neither the _ nor the % can be used as a wildcard character in Tname.
For example, to generate the DDL statements for objects in the DEPARTMENT
database associated with tables that have a percent sign in the neither
the first nor the last position of the name:
db2look -d department -e -tw string\%string
- Case-sensitive, DBCS, and multi-word table and view names must
be enclosed by both a backslash and double quotation marks. For example:
\"My TabLe\"
If a multibyte character
set (MBCS) or double-byte character set (DBCS) name is not enclosed
by the backward slash and double quotation delimiter and if it contains
the same byte as the lowercase character, it will be converted into
uppercase and db2look will look for a database
object with the converted name. As a result, the DDL statement will
not be extracted.
- The -tw option can be used with the -x option
(to generate GRANT privileges), the -m option (to
return table and column statistics), and the -l option
(to generate the DDL for user-defined table spaces, database partition
groups, and buffer pools). If the -t option is
specified with the -tw option, the -t option
(and its associated Tname argument) is ignored.
- The -tw option cannot be used to generate the
DDL for tables (and their associated objects) that reside on federated
data sources, or on DB2 Universal Database™ for z/OS and OS/390, DB2 for i ,
or
DB2 Server for VSE & VM.
- The -tw option is only supported via the CLP.
If you try to generate DDL statements on systems with
a partitioned database environment, a warning message is displayed
in place of the DDL statements for table spaces that are on inactive
database partitions. To ensure that correct DDL statements are produced
for all table spaces, you must activate all database partitions.
You
can issue the db2look command from a DB2 client to a database that is of the same
or later release as the client, but you cannot issue this command
from a client to a database that is of an earlier release than the
client. For example, you can issue the db2look command
from a Version 9.7 client to a Version 9.8 database, but you cannot
issue the command from a Version 9.8 client to a Version 9.7 database.
When you invoke
the db2look utility, the db2look command generates
the DDL statements for any object created using an uncommitted transaction.
When
you extract a DDL statement for a security label component of type
array, the extracted statement might not generate a component whose
internal representation (encoding of elements in that array) matches
that of the component in the database for which you issued the db2look command.
This mismatch can happen if you altered the security label component
by adding one or more elements to it. In such cases, data that you
extract from one table and moved to another table that you created
from db2look output will not have corresponding
security label values, and the protection of the new table might be
compromised.