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.
SELECT privilege on the system catalog tables.
None
>>-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-'
For the parameters that support the AUTOMATIC value, you might need to add AUTOMATIC at the end of the generated UPDATE DB CFG command.
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.
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.
You can use a two-part view name of the format schema.view to fully qualify a view.
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.
db2look -d department -u walid -e -o db2look.sql
db2look -d department -u walid -z ianhe -e -o db2look.sql
db2look -d department -u walid -m -o db2look.sql
db2look -d department -u walid -e -m -o db2look.sql
db2look -d department -a -e -o db2look.sql
db2look -d department -l -o db2look.sql
db2look -d department -f -o db2look.sql
db2look -d department -a -e -m -l -x -f -o db2look.sql
db2look -d department -xd -o db2look.sql
db2look -d department -a -e -td % -o db2look.sql
The
output can then be read by the CLP: db2 -td% -f db2look.sql
db2look -d department -e -noview -o db2look.sql
db2look -d department -e -t tab1 "\"My TaBlE2\"" -o db2look.sql
db2look -d feddepart -e -wrapper fedwrap
db2look -d feddepart -e -nofed -o out
db2look -d department -z walid -e -xs -o db2look.sql
db2look -d department -a -e -xs -xdir /home/ofer/ofer/
db2look -d department -wlm
db2look -d department -wlm -e -l
db2look -d DB1 -t TABLES.TAB1 -e -o db2look.sql
db2look -d DB1 -t TABLES.TAB1 -e -xdep -o db2look.sql
db2look -d DB1 -t TABLE1 -m -e -o db2look.sql
db2look -d department -createdb -o db2look.sql
db2look -d department -printdbcfg -o db2look.sql
db2look -d department -createdb -printdbcfg -e -o db2look.sql
On Windows operating systems, you must issue the db2look command from a DB2 command window.
cd .../sqllib/bnd
db2bfd -b db2look.bnd
db2bfd -b db2lkfun.bnd
db2bfd -b db2lksp.bnd
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.
db2look -d department -e -tw abc% -o db2look.sql
db2look -d department -e -tw _d% -o db2look.sql
db2look -d department -e -tw string\%string
\"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.
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.