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 BMC AMI Log Master creates using the log records selected in your log scan.

Related topic

 LOGSCAN-statement

LOGSCAN DDL file_SPE2110.png

Be aware of the following points regarding DDL output files:

  • When you generate a DDL file, BMC AMI 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, BMC AMI 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 BMC AMI Log Master encounters log records that reflect the implicit creation of an object, BMC AMI 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, BMC AMI Log Master cannot distinguish an implicitly created index from an explicitly created one. BMC AMI 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 BMC AMI 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 BMC AMI 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 BMC AMI 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, BMC AMI Log Master cannot generate UNDO DDL to reverse the effects of an ALTER TABLE ADD COLUMN or an ALTER TABLE ALTER COLUMN statement. If BMC AMI 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, BMC AMI Log Master generates a CREATE statement to re-create the dropped object. However, BMC AMI Log Master does not generate statements to reinsert the data that was removed by the drop action. To recover the data, use the BMC AMI Log Master automated drop recovery feature (see DROPRECOVERY-statement).
  • The DDL objects that BMC AMI 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 BMC AMI 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 a 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, BMC AMI 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 in 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:


    • BMC AMI Log Master ignores the presence of the VERBOSE keyword.
    • Dynamic DDL processors (SPUFI or DSNTEP2) might not process the file.
    • BMC AMI 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 BMC AMI 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 BMC AMI Log Master includes COMMIT statements in the output DDL file.

Value

Description

NONE | 0

Causes BMC AMI Log Master to commit all DDL statements at one time (at the end of processing). If you set this value to zero, BMC AMI 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 BMC AMI Log Master to generate a COMMIT statement after each generated DDL statement.

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


Important

BMC AMI 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, BMC AMI 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 BMC AMI 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.

SCHEMA



Directs BMC AMI 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.

TRANSFER OWNERSHIP



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.

Value

Description

YES

Directs BMC AMI Log Master to generate CREATE TABLE, VIEW, or INDEX statements with unqualified names, then generate TRANSFER OWNERSHIP to original owner values from the logs if OWNER values do not match CREATOR values in the log. For CREATE DATABASE, TABLESPACE, and STOGROUP, generate TRANSFER OWNERSHIP to original CREATOR values in the log if CREATOR and CREATEDBY values do not match in the log.

This option only applies to CREATE Object DDL.

YES USER|OWNER NewOwner

For a user issued TRANSFER OWNERSHIP statement, set the owner to the specified NewOwner.

This option only applies to CREATE objects and TRANSFER OWNERSHIP statements.

NO

Directs BMC AMI Log Master to generate CREATE statements with fully qualified names, using the CREATOR values from the log as the qualifiers. TRANSFER OWNERSHIP statements are not generated after the CREATE object statements.

For more information about TRANSFER OWNERSHIP, see TRANSFER OWNERSHIP.

More information about SCHEMA and TRANSFER OWNERSHIP YES mutual exclusive


When the SCHEMA option is specified in DDL, SQL, or DROPRECOVERY syntax, BMC AMI 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 afterward. With TRANSFER OWNERSHIP YES specified, BMC AMI 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


 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, BMC AMI 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.

 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*