LOGSCAN DDL file definition

This topic describes the DDL file definition syntax of the LOGSCAN statement.

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.

Value	Description
EXECUTE	Directs Log Master to execute the generated UNDO DDL immediately after it is generated, without any review or intervention. This keyword is most frequently used in test environments where any unintended consequences do not affect important production data.

Be aware of the following points as you generate UNDO DDL:

Because of DB2 constraints, Log Master cannot generate UNDO DDL to reverse the effects of an ALTER TABLE ADD COLUMN or an ALTER TABLE ALTER COLUMN statement. If Log Master encounters these statements as it generates UNDO DDL, it issues a warning message, ignores the ALTER statement, and continues generating other DDL statements.
If the original DDL includes a DROP statement, and you generate UNDO DDL, Log Master generates a CREATE statement to re-create the dropped object. However, Log Master does not generate statements to reinsert the data that was removed by the drop action. To recover the data, use the Log Master automated drop recovery feature (see DROPRECOVERY statement).
The DDL objects that Log Master writes in an output logical log file cannot be used to generate UNDO DDL, and they cannot be processed by the High-speed Apply Engine as UNDO. The DSNDB06 insert, update, and delete records must be included in the logical log file to generate UNDO DDL.
When Log Master creates UNDO DDL, it attempts to reverse the effect and the order of the original DDL statements. In some cases, the dependencies or constraints on existing DB2 objects prevent the reversed DDL statements from executing correctly.
For example, consider the following actions:
- Create partitioned table space
- Create a distinct type
- Create table (in partitioned table space) using the distinct type
The generated UNDO DDL reverses the order of these actions. When you execute the DDL, the following actions occur:
- Drop table: DB2 requires that you drop a partitioned table space, not a partitioned table. Because of this requirement, Log Master inserts a comment in the generated DDL file to indicate that it cannot drop the partitioned table.
- Drop distinct type: This action fails because the type is still used in the table, which was not dropped by the preceding action.
- Drop table space: This action succeeds (dropping both the table and the table space), but the distinct type remains in existence.
Other similar situations result from DB2 dependencies and constraints. To avoid these situations, examine and edit your generated UNDO DDL statements before executing them.

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.

Value	Description
FB	(default) Indicates that records are fixed length and blocked.
VB	Indicates that records are variable length and blocked. This value allows individual DDL statements to be written on a single record, regardless of their length. This type of file can be easier to process on other platforms.

When you specify this value, the following items apply:

- Log Master ignores the presence of the VERBOSE keyword.
- Dynamic DDL processors (SPUFI or DSNTEP2) might not process the file.
- Log Master sets the logical record length of the output file (LRECL) to the system-determined block size (SDB) of the storage device where the output file is written (minus 4 bytes).

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.

Value

Description

NONE | 0

Causes Log Master to commit all DDL statements at one time (at the end of processing). If you set this value to zero, Log Master interprets it as equivalent to the NONE keyword.

nnnn

Specifies the number of DDL statements between COMMIT statements.

The maximum limit for this value is 9999. The default value is 1, which causes Log Master to generate a COMMIT statement after each generated DDL statement.

To prevent errors as it generates UNDO DDL, Log Master includes COMMIT statements at different places than in the original DDL.

Note

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.

Related topic

LOGSCAN statement

LOGSCAN DDL file definition

Comments