DB2 10.5 for Linux, UNIX, and Windows

db2look - DB2 statistics and DDL extraction tool command

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 authorities:

SYSADM
SYSCTRL
SYSMAINT
SYSMON
DBADM
EXECUTE privilege on the ADMIN_GET_STORAGE_PATHS table function

Required connection

None

Command syntax


>>-db2look-- -d--DBname--+------------+--+--------------+------->
                         '- -createdb-'  '- -printdbcfg-'   

>--+-----+--+--------------+--+-------------+------------------->
   '- -e-'  '- -u--creator-'  '- -z--schema-'   

>--+-------------------------------------+--+------+------------>
   '-+----------------+--+-------------+-'  '- -ct-'   
     |      .-------. |  '- -tw--Tname-'               
     |      V       | |                                
     '- -t----Tname-+-'                                

>--+------+--+----------------+--+-----+--+------+-------------->
   '- -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.

-createdb

Generates the CREATE DATABASE command that was used to create the source database.

The generated CREATE DATABASE command contains the usual parameters and options found in the CREATE DATABASE syntax except the following parameters:

ALIAS
NUMSEGS
RESTRICTIVE
WITH
AUTOCONFIGURE

-printdbcfg

Generates UPDATE DB CFG commands for the database configuration parameters. The printdbcfg command generates UPDATE DB CFG commands in a similar order as the results returned from the GET DB CFG command.

For the parameters that support the AUTOMATIC value, you might need to add AUTOMATIC at the end of the generated UPDATE DB CFG command.

The generated UPDATE DB CFG command contains the usual parameters and options found in the UPDATE DATABASE CONFIGURATION syntax except for the following parameters:

PAGE_AGE_TRGT_MCR
DFT_TABLE_ORG
STRING_UNITS
NCHAR_MAPPING
EXTENDED_ROW_SZ
CONNECT_PROC

-e

Extracts DDL statements for the following database objects:

Aliases
Audit policies
Check constraints
Function mappings
Function templates
Global variables
Indexes (including partitioned indexes on partitioned tables)
Index specifications
Materialized query tables (MQTs)
Nicknames
Primary key, referential integrity, and check constraints
Referential integrity constraints
Roles
Schemas
Security labels
Security label components
Security policies
Sequences
Servers
Stored procedures
Tables
Note: Values from column STATISTICS_PROFILE in the SYSIBM.SYSTABLES catalog table are not included.
Triggers
Trusted contexts
Type mappings
User mappings
User-defined distinct types
User-defined functions
User-defined methods
User-defined structured types
User-defined transforms
Views
Wrappers

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.

-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 storage groups
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
```
Generate the CREATE DATABASE command that was used to create the database DEPARTMENT. The output is sent to the db2look.sql file.
```
   db2look -d department -createdb -o db2look.sql
```
Generate the UPDATE DB CFG statements from the database DEPARTMENT. The output is sent to the db2look.sql file.
```
   db2look -d department -printdbcfg -o db2look.sql
```
Generate the CREATE DATABASE command that was used to create the database, the UPDATE DB CFG statements and the DDL statements for objects created in the database DEPARTMENT. The output is sent to the db2look.sql file.
```
   db2look -d department -createdb -printdbcfg -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.8 client to a Version 10.1 database, but you cannot issue the command from a Version 10.1 client to a Version 9.8 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.

In a partitioned database environment, if the database was created with table spaces managed by Database Managed Space (DMS) or System Managed Space (SMS) with specified container paths including those defined by $N expressions, the db2look -createdb generated CREATE DATABASE command will list all container paths on each database partition, not just the original specified path or the $N expression. Before you run the generated statement you must adjust the container setting. There is no restriction with the automatic storage option in a partitioned database environment.

In a pureScale environment, the db2look -printdbcfg command generates the UPDATE DATABASE CONFIGURATION values based on the values of the database member from where the db2look -printdbcfg command is run.