学步园

Extracts the required DDL (data definition language) statements to reproduce the database objects of a production database on a test database. db2look generates the DDL statements by object type.

7 This tool can generate the required UPDATE statements used to 7 replicate the statistics on the objects in a test database. It can also be 7 used to generate UPDATE DATABASE CONFIGURATION and UPDATE DATABASE MANAGER 7 CONFIGURATION commands and db2set commands so 7 that query optimizer-related configuration parameters and registry variables 7 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.

7 7

SELECT privilege on the system catalog tables.

7 In some cases, such as generating table space container DDL 7 (which calls the APIs sqlbotcq, sqlbftcq, and sqlbctcq), you will require 7 one of the following:

7

7
• sysadm

7

• sysctrl

7

• sysmaint

7

• dbadm

None. This command establishes a database connection.

|>>-db2look-- -d--DBname--+-----+--+--------------+-------------->
|                         '- -e-'  '- -u--Creator-'
|
|>--+-------------+--+-------------------------------------+----->
|   '- -z--schema-'  '-+----------------+--+-------------+-'
|                      |      .-------. |  '- -tw--Tname-'
|                      |      V       | |
|                      '- -t----Tname-+-'
|
|>--+----------------+--+-----+--+------------+--+-----+--------->
|   |      .-------. |  '- -h-'  '- -o--Fname-'  '- -a-'
|   |      V       | |
|   '- -v----Vname-+-'
|
|>--+-----------------------+--+-----+--+-----+--+------+-------->
|   '- -m--+-----+--+-----+-'  '- -l-'  '- -x-'  '- -xd-'
|          '- -c-'  '- -r-'
|
|>--+-----+--+-----------------+--+-----+--+-----+--+-----+------>
|   '- -f-'  '- -td--delimiter-'  '- -p-'  '- -s-'  '- -g-'
|
|>--+----------+--+----------------------------+----------------->
|   '- -noview-'  '- -i--userid-- -w--password-'
|
|>--+------------------+--+---------+---------------------------><
|   +- -wrapper--Wname-+  '- -nofed-'
|   '- -server--Sname--'
|

|

| |

-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, or DB2 Universal Database for z/OS and OS/390 database. |If the DBname is a DB2 Universal Database for z/OS and OS/390 database, the db2look utility will extract the DDL and UPDATE statistics |statements for OS/390 and z/OS objects. These DDL and UPDATE statistics statements |are statements applicable to a DB2 UDB database and not to a DB2 Universal Database for z/OS and OS/390 database. |This is useful for users who want to extract OS/390 and z/OS objects and |recreate them in a DB2 UDB database. |

If DBname is |a DB2 Universal Database for z/OS and OS/390 database, the db2look output is |limited to the following:

|

|
• Generate DDL for tables, indexes, views, and user-defined distinct types

|

• Generate UPDATE statistics statements for tables, columns, column distributions |and indexes

|

|

-e

|

Extract DDL statements for database objects. DDL for the following database |objects are extracted when using the -e option: |

|
• Tables

|

• Views

|

• Automatic summary tables (AST)

|

• Aliases

|

• Indexes

|

• Triggers

|

• Sequences

|

• User-defined distinct types

|

• Primary key, referential integrity, and check constraints

|

• User-defined structured types

|

• User-defined functions

|

• User-defined methods

|

• User-defined transforms

|

• Wrappers

|

• Servers

|

• User mappings

|

• Nicknames

|

• Type mappings

|

• Function templates

|

• Function mappings

|

• Index specifications

|

• 7 Stored procedures|

| | |

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.

|

|

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

|

-z schema

|

Schema name. Limits output to objects with this schema name. If option |-a is specified, this parameter is ignored. If this parameter is not specified, |objects with all schema names are extracted. This |option is ignored for the federated DDL. |

1 1

-t Tname1 Tname2 ... TnameN

1

Table name list. Limits the output to particular tables in the table 1 list. The maximum number of tables is 30. Table names are separated by a 1 blank space. Case-sensitive names must be enclosed inside a backward slash 1 and double quotation delimiter, for example, /" MyTabLe /". For multiple-word 1 table names, the delimiters must be placed within quotation marks (for example, 1 "/"My Table/"") to prevent the pairing from being evaluated word-by-word 1 by the command line processor. If a multiple-word table name is not enclosed 1 by the backward slash and double delimiter (for example, "My Table"), all 1 words will be converted into uppercase and db2look will look for an uppercase table (for example, "MY TABLE"). 1

|7 7

-tw Tname

7

Generates DDL for table names that match the pattern criteria specified 7 by Tname. Also generates the DDL for all dependent objects of all 7 returned tables. Tname can be a single value only. The underscore 7 character (_) in Tname represents any single character. The percent 7 sign (%) represents a string of zero or more characters. Any other 7 character in Tname only represents itself. When -tw is specified, 7 the -t option is ignored. 7

|7 7

-v Vname1 Vname2 ... VnameN

7

Generates DDL for the specified views. The maximum number of views 7 is 30. If the -t option is specified, the -v option is ignored. 7

| |

-h

|

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

|

-o Fname

|

If using LaTeX format, write the output to filename .tex. If using plain text format, write the output to filename.txt. Otherwise, write the output |to filename.sql. If this option is not |specified, output is written to standard output. If a filename is specified |with an extension, the output will be written into that file. |

|

-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 systems, however, there is no default value |for the USER environment variable: a user variable in the SYSTEM variables |must be set, or a set USER=<username> must be issued for the |session.

|

|

-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. The -c, and -r options are optionally used with -m. |

|

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

|

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

|

|

|

-l

|

If this option is specified, then the db2look utility will generate DDL for user defined table spaces, database partition |groups and buffer pools. DDL for the following database objects is extracted |when using the -l option: |

|
• User-defined table spaces

|

• User-defined database partition groups

|

• User-defined buffer pools

|

|

-x

|

If this option is specified, the db2look utility |will generate authorization DDL (GRANT statement, for example). |

The authorizations |supported by db2look include:

|

|
• Table: ALTER, SELECT, INSERT, DELETE, UPDATE, INDEX, REFERENCE, CONTROL

|

• View: SELECT, INSERT, DELETE, UPDATE, CONTROL

|

• Index: CONTROL

|

• Schema: CREATEIN, DROPIN, ALTERIN

|

• Database: CREATEDB, BINDADD, CONNECT, CREWATE_NOT_FENCED, IMPLICIT_SCHEMA

|

• User-defined function (UDF): EXECUTE

|

• User-defined method: EXECUTE

|

• Stored procedure: EXECUTE

|

• Package: CONTROL, BIND, EXECUTE

|

• 7 Column: UPDATE, REFERENCES|

|

• 7 Tablespace: USE|

|

• 7 Sequence: USAGE, ALTER|

|

|

-xd

|

If this option is specified, the db2look utility will generate all authorization |DDL including authorization DDL for objects whose authorizations were granted |by SYSIBM at object creation time. |

|

-f

|

7 Use this option to extract the configuration parameters and 7 registry variables that affect the query optimizer. 7

The db2look utility generates an update command for the following configuration 7 parameters:

7

7
• Database manager configuration parameters 7
  7
  • cpuspeed

  7

  • intra_parallel

  7

  • comm_bandwidth

  7

  • nodetype

  7

  • federated

  7

  • fed_noauth

7

• Database configuration parameters 7
  7
  • locklist

  7

  • dft_degree

  7

  • maxlocks

  7

  • avg_appls

  7

  • stmtheap

  7

  • dft_queryopt

7

The db2look utility generates the 7 db2set command for the following DB2 registry 7 variables:

7

7
• DB2_PRED_FACTORIZE

7

• DB2_CORRELATED_PREDICATES

7

• DB2_LIKE_VARCHAR

7

• DB2_SORT_AFTER_TQ

7

• DB2_HASH_JOIN

7

• DB2_ORDERED_NLJN

7

• DB2_NEW_CORR_SQ_FF

7

• DB2_PART_INNER_JOIN

7

• DB2_INTERESTING_KEYS

| |

|

-td delimiter

|

Specifies the statement delimiter for SQL statements generated by db2look. If this option is not specified, the default | is the semicolon (;). It is recommended that this option be used if the -e option is specified. In this case, the extracted objects might contain |triggers or SQL routines. |

|

-p

|

Use plain text format. |

|

-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 indexes. | |

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.

|

|

|

-noview

|

If this option is specified, CREATE VIEW DDL statements will not be |extracted. |

|

-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 log on to the remote system. | |

Note:

|

If working with |remote databases, the remote database must be the same version as the local |database. The db2look utility does not have down-level |or up-level support.

|

|

-wrapper Wname

|

Generates DDL statements for federated objects that apply to this wrapper. |The federated DDL statements that might be generated include: CREATE WRAPPER, |CREATE SERVER, CREATE USER MAPPING, CREATE NICKNAME, CREATE TYPE MAPPING, |CREATE FUNCTION ... AS TEMPLATE, CREATE FUNCTION MAPPING, CREATE INDEX SPECIFICATION, |and GRANT (privileges to nicknames, servers, indexes). Only one wrapper name |is supported; an error is returned if less than one or more than one is specified. |This option cannot be used if the -server option is used. |

|

-server Sname

|

Generates DDL statements for federated objects that apply to this server. |The federated DDL statements that might be generated include: CREATE WRAPPER, |CREATE SERVER, CREATE USER MAPPING, CREATE NICKNAME, CREATE TYPE MAPPING, |CREATE FUNCTION ... AS TEMPLATE, CREATE FUNCTION MAPPING, CREATE INDEX SPECIFICATION, |and GRANT (privileges to nicknames, servers, indexes). Only one server name |is supported; an error is returned if less than one or more than one is specified. |This option cannot be used if the -wrapper option is used. |

|

-nofed

|

Specifies that no federated DDL statements will be generated. When |this option is specified, the -wrapper and -server options are ignored. |

• 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 DDL statements for objects that have schema name ianhe, created by user walid, in database DEPARTMENT. The db2look output is sent to file db2look.sql:

     db2look -d department -u walid -z ianhe -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 database partition groups, 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 optimizer-related database and database manager configuration parameters, as well as the db2set statements for optimizer-related 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 optimizer-related database and database manager configuration parameters, the db2set statements for optimizer-related registry variables, and the DDL for all user-defined database partition groups, 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 

• Generate all authorization DDL statements for all objects in database DEPARTMENT, including the objects created by the original creator. (In this case, the authorizations were granted by SYSIBM at object creation time.) The db2look output is sent to file db2look.sql:

     db2look -d department -xd -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 -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 db2look output is sent to file db2look.sql:

     db2look -d department -e -noview -o db2look.sql 

• |Generate the DDL statements for objects in database DEPARTMENT | related to specified tables. The db2look output |is sent to file db2look.sql: |

     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 db2look 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 db2look output is sent to |a file out.sql: |

     db2look -d feddepart -e -nofed -o out 

|On Windows systems, db2look must be |run from a DB2 command window.

db2look command line options can be specified in any order. All command line options are optional except the -d option which is mandatory and must be followed by a valid database alias name.

|Several of the existing options support a federated environment. |The following db2look command line options are |used in a federated environment:

| |
• -e | |

  When used, federated DDL statements are generated.

|

• 7 -x 7 7

  When used, GRANT statements are generated to grant privileges 7 to the federated objects.

  |

|

• -xd |

  When used, federated DDL statements are generated to |add system-granted privileges to the federated objects.

|

• -f |

  When used, federated-related information is extracted |from the database manager configuration.

|

• -m |

  When used, statistics for nicknames are extracted.

|The ability to use federated systems needs to be enabled in the |database manager configuration in order to create federated DDL statements. |After the db2look command generates the script |file, you must set the federated configuration |parameter to YES before running the script.

|You need to modify the output script to add the remote passwords |for the CREATE USER MAPPING statements.

|You need to 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.

7 Usage of the -tw option is as follows:

7

7
• To both generate the DDL statements for objects in the DEPARTMENT database 7 associated with tables that have names beginning with abc and send 7 the output to the db2look.sql file: 7 7

     db2look -d department -e -tw abc% -o db2look.sql 

7

• To generate the DDL statements for objects in the DEPARTMENT database 7 associated with tables that have a d as the second character of 7 the name and to send the output to the db2look.sql file: 7 7

     db2look -d department -e -tw _d% -o db2look.sql 

7

• db2look uses the LIKE predicate when evaluating 7 which table names match the pattern specified by the Tname argument. 7 Because the LIKE predicate is used, 7 if either the _ character or the % character is part of 7 the table name, the backslash (/) escape character must be used immediately 7 before the _ or the %. 7 In this situation, neither the _ nor 7 the % can be used as a wildcard character in Tname. 7 For example, 7 to generate the DDL statements for objects in the DEPARTMENT database associated 7 with tables that have a percent sign in the neither the first nor the last 7 position of the name: 7 7

     db2look -d department -e -tw string/%string 

7

• Case-sensitive and multi-word table names must be enclosed by both a backslash 7 and double quotation marks. For example: 7 7

     /"My TabLe/" 

  .

7

• The -tw option can be used with the -x option (to generate GRANT privileges), 7 the -m option (to return table and column statistics), and the -l option (to 7 generate the DDL for user-defined table spaces, database partition groups, 7 and buffer pools). 7 If the -t option is specified with the -tw option, the -t option (and its 7 associated Tname argument) is ignored.

7

• The -tw option accepts only one Tname argument.

7

• The -tw option cannot be used to generate the DDL for tables (and their associated 7 objects) that reside on federated data sources, or on DB2 Universal Database for z/OS and OS/390, 7 DB2 Universal Database for iSeries, or DB2 Server for VSE & VM.

7

• The -tw option is only supported via the CLP.