LOGSCAN DDL file definition
The following figure specifies the type of data definition language (DDL) statements in (and the location of) the DDL file that Log Master creates using the log records selected in your log scan.
Be aware of the following points regarding DDL output files:
When you generate a DDL file, Log Master can generate a Catalog Activity report or update the Repository, but it cannot generate other forms of output in the same log scan (such as other reports or SQL). To generate other output in the same job, include an additional, separate log scan step.
To generate DDL statements, Log Master must perform completion processing on Db2 catalog log records. Because of the large number of log records related to the catalog, this processing can cause your job to run longer and require more resources than a job that does not read the Db2 catalog. If you frequently operate on objects stored in the catalog, you can improve performance by creating more-frequent image copies of the catalog, or by defining tables in the Db2 catalog with Data Capture Changes (DCC).
Db2 can implicitly create objects based on the DDL that you execute (for example, when you create a table without specifying an IN clause, Db2 can create a database and table space for you). When Log Master encounters log records that reflect the implicit creation of an object, Log Master includes comments within the generated output file that contain the corresponding CREATE statement.
Because of the way that Db2 logs the creation of an index, Log Master cannot distinguish an implicitly created index from an explicitly created one. Log Master includes a corresponding CREATE INDEX statement in the output file without comments.
When Db2 implicitly creates a database, it logs the implicit create action in a different unit of recovery from the original (explicit) create action. The URID of the implicit create action has different URID-related attributes (such as authorization ID and plan name) than the URID of the explicit create action. Be aware of this difference if you use URID-related criteria to select log records for an output DDL file.
For more information about Unicode characters in DDL output files, see Considerations for output files and Unicode.
| Option | Description | ||||||||
|---|---|---|---|---|---|---|---|---|---|
| MIGRATE | Directs Log Master to create DDL used for migration, replicating the DDL that was originally executed. For example, if the original DDL contains a CREATE TABLE statement, the MIGRATE DDL contains the same statement. | ||||||||
| UNDO | Directs Log Master to create UNDO DDL, reversing the statement type of the DDL that was originally executed. For example, if the original DDL contains a DROP TABLE statement, the UNDO DDL contains a corresponding CREATE TABLE statement.
Be aware of the following points as you generate UNDO DDL:
| ||||||||
| DATASET Output definition | Specifies the characteristics of the output data set that contains the MIGRATE or UNDO DDL. For more information, see LOGSCAN output definition. | ||||||||
| RECFM | Determines the record format of the output DDL file.
When you specify this value, the following items apply: | ||||||||
| VERBOSE | Directs Log Master to embed ANSI-standard comments in the generated DDL file. These comments describe unit of recovery identifier (URID) information and other information related to the database structures. The URID information can be useful. For example, you can use it to determine which user issued GRANT or REVOKE statements to change Db2 authorizations. This keyword is optional. By generating a verbose DDL file with a filter that includes catalog activity or catalog objects, you can select information about changes to the Db2 catalog, including changes to Db2 security. The Catalog Activity report offers a more concise version of the same basic information. For more information, see CATALOG ACTIVITY. | ||||||||
| COMMIT FREQUENCY | Specifies how frequently Log Master includes COMMIT statements in the output DDL file.
Important Log Master enables you to express commit frequency for DDL generation differently than for SQL generation. Where you must express DDL frequency as a number of statements, you can express SQL frequency as a number of transactions or a number of statements. In this context, 'transactions' are considered to be the same as URIDs. If you specify INCLUDE DDL in the SQL type/output definition, Log Master treats the commit frequency value as a number of statements for DDL, and a number of transactions or statements for SQL, depending on what you specify. | ||||||||
| SQLID sqlid | Directs Log Master to generate a SET CURRENT SQLID statement and insert it at the beginning of the generated DDL. When you execute the generated DDL, Db2 runs it under the authority granted to the authorization ID represented by sqlid. Specify a valid authorization ID that has the appropriate authority in your environment. | ||||||||
Directs Log Master to generate a SET SCHEMA statement and insert it at the beginning of the generated DDL statements. The SET SCHEMA statement sets the default schema for the current environment to the specified schema. The default schema is used as the target schema for all statements issued from the environment that do not explicitly specify a schema name. Important SET SCHEMA and TRANSFER OWNERSHIP YES are mutually exclusive. For more information about SCHEMA, see SCHEMA. | |||||||||
Indicates whether to generate a TRANSFER OWNERSHIP statement after the CREATE of an object when the original OWNER value in the log does not match the CREATOR or CREATEBY value. The execution of the DDL will transfer the ownership of the object to the original owner value in the log, or to the specified new owner. Important TRANSFER OWNERSHIP YES and SET SCHEMA are mutually exclusive.
For more information about TRANSFER OWNERSHIP, see TRANSFER OWNERSHIP. |
More information about SCHEMA and TRANSFER OWNERSHIP YES mutual exclusive
(
BMC.DB2.SPE2110
)
When the SCHEMA option is specified in DDL, SQL, or DROPRECOVERY syntax, Log Master generates a SET SCHEMA statement (the default SCHEMA) at the beginning of the output dataset and does not generate other SET SCHEMA statements afterwards. With TRANSFER OWNERSHIP YES specified, Log Master builds CREATE TABLE, INDEX, or VIEW with an unqualified name, the default SCHEMA is the qualifier. However, a fully qualified name is still used in other DDL statements, such as ALTER and GRANT. These DDL statements may get an SQL error if the qualifier in the log does not match the default SCHEMA.
More information about TRANSFER OWNERSHIP
(
BMC.DB2.SPE2110
)
If you run a TRANSFER OWNERSHIP statement without the proper authorization to perform the transfer, you will get an SQL error. The error will occur when the user executing the statement is not the owner of the object or does not have SECADM authority.
To prevent the SQL error, Log Master will generate a SET CURRENT SQLID statement with the OWNER value from the log before the TRANSFER OWNERSHIP statement for TABLE, INDEX, and VIEW objects. However, the error can still occur when any one of the following conditions exists:
- The old owner is a ROLE or the owner name is longer than 8 bytes in length and cannot be used in a SET CURRENT SQLID statement.
- The use ID executing the DDL is not the owner of the DATABASE, TABLESPACE, or STOGROUP.
- The CREATOR and OWNER of the TABLE, INDEX, or VIEW are different.
Use one of the following resolutions for the error:
- Run the generated DDL using the user ID that initially ran the statements being migrated.
- Generate DDL with TRANSFER OWNERSHIP YES if the CREATOR and OWNER of an object are different.
- Manually add or modify the SET CURRENT SQLID statement to the object owner before the TRANSFER OWNERSHIP statement.
- Run the generated DDL with a user ID that has SECADM authority.
Comments
Log in or register to comment.