LOGSCAN SQL file definition

This syntax specifies overall characteristics of the SQL statements that BMC AMI Log Master creates using the log records selected in your log scan.

For more information about Unicode characters in SQL output files, see Considerations-for-output-files-and-Unicode.
The following figure shows the SQL file definition syntax of the LOGSCAN statement:

Related topics

LOGSCAN-statement

SQL-output-files


LOGSCAN SQL file_SPE2110.png

Option

Description

SQL type/output definition

The SQL type/output definition defines the type of SQL that BMC AMI Log Master generates and where it writes the generated SQL. For more information, see SQL-type-output-definition.

QUALIFY

Specifies the columns that BMC AMI Log Master includes in the WHERE clauses of generated SQL. By default, BMC AMI Log Master uses available index information to determine what columns to include. To explicitly control the columns used, enter one of the following choices:

Value

Description

ALL

Specifies that BMC AMI Log Master includes all columns in the generated WHERE clauses.

PRIMARY KEY

Specifies that BMC AMI Log Master includes the column or columns associated with a table’s primary key index in the generated WHERE clauses.

If no primary key index exists for the table, BMC AMI Log Master selects an index using Db2’s internal list of indexes for that table. BMC AMI Log Master uses the following hierarchy to select an index. (The order of Db2’s internal list can be different than the order you receive when you select records in the SYSIBM.SYSINDEXES table of the Db2 catalog.)

  1. If no primary key index exists, BMC AMI Log Master selects the first unique key index that it encounters.

    Important

    A hash overflow index can also be the primary key or a unique key index.

  2. If no unique key index exists, BMC AMI Log Master selects the first clustering key index that it encounters.
  3. If no clustering key index exists, BMC AMI Log Master selects the first index that it encounters.

If a primary key or a unique key index exists, BMC AMI Log Master includes the columns associated with that index in the generated WHERE clauses. If no primary or unique key index exists, BMC AMI Log Master issues message BMC097492 as a warning and includes the columns associated with the first non-unique index it encounters. If no indexes exist at all, BMC AMI Log Master issues the warning message and includes all columns in the generated WHERE clauses.

CHANGED COLUMNS

Specifies whether BMC AMI Log Master includes all changed columns in the generated WHERE clauses. If you specify the ALL keyword, BMC AMI Log Master ignores this keyword.

USE OVERRIDES

Directs BMC AMI Log Master to include a unique set of columns (called an alternate index) in the WHERE clause. The alternate indexes must have been defined for the current work ID through the BMC AMI Log Master online interface.

TSOID tsoID

Directs BMC AMI Log Master to include the columns of an alternate index in the WHERE clause. The keyword enables you to use alternate indexes that have been defined by a different user ID than the one running the product. The alternate indexes must have been defined through the BMC AMI Log Master online interface as defaults for the user ID specified by tsoID.

VERBOSE

Directs BMC AMI Log Master to embed ANSI-standard comments in the generated SQL. These comments describe unit of recovery identifier (URID) information and other information related to the database structures. This information is useful under some circumstances. This keyword is optional.

Regardless of whether you specify this keyword, BMC AMI Log Master can include comments in the generated SQL to provide information about conditions detected during SQL generation.

COMMIT FREQUENCY

Specifies how frequently BMC AMI Log Master includes COMMIT statements in the generated SQL. The value can represent a number of SQL statements or a number of transactions. In this context, transactions are considered to be the same as unit of recovery identifiers (URIDs).

Important

BMC AMI Log Master enables you to express commit frequency for SQL generation differently than for DDL generation. You can express the SQL frequency as a number of statements or a number of transactions. You must express the DDL frequency as a number of statements.

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.


Value

Description

NONE | 0

Specifies that all statements or transactions are committed at one time. If you set this value to zero, BMC AMI Log Master interprets it as equivalent to the NONE keyword (all statements or transactions at once).

nnnn

Specifies the number of SQL statements or transactions (URIDs) between generated COMMIT statements. If you set the value to one and use the TRANSACTIONS keyword, BMC AMI Log Master inserts COMMIT statements in the output SQL at the same points where they were included in the original SQL.

TRANSACTIONS

Directs BMC AMI Log Master to interpret the commit frequency as a number of transactions (units of recovery).

STATEMENTS

Directs BMC AMI Log Master to interpret the commit frequency as a number of statements.

If you omit the COMMIT FREQUENCY keyword, the default value is equivalent to one transaction (insert COMMIT statements in output at the same points as in the original SQL). If you specify a value without specifying STATEMENTS or TRANSACTIONS, the default value is TRANSACTIONS.

UPDATE ALL COLUMNS

Directs BMC AMI Log Master to generate SQL that updates all columns in a table row (whether the column is changed or not). If you omit this keyword, BMC AMI Log Master generates SQL that updates only the changed columns in a row.

DECIMAL POINT

Determines which character BMC AMI Log Master uses to represent the decimal point in the generated SQL. BMC AMI Log Master extracts the default character for DECIMAL POINT from the DSNHDECP module of the subsystem for BSDS and Db2 log input.

Value

Description

PERIOD

Defines the decimal point character as a period (.).

COMMA

Defines the decimal point character as a comma (,).

When BMC AMI Log Master obtains input from a logical log, it uses the same decimal point character that was in use when the logical log was created.

SQLID sqlid

Directs BMC AMI Log Master to generate a SET CURRENT SQLID statement and insert it at the beginning of the generated SQL. When you execute the generated SQL, 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.

CONCAT OPERATOR

Directs BMC AMI Log Master to use the specified characters as the SQL concatenation operator in output SQL statements. BMC AMI Log Master uses a concatenation operator to generate SQL statements that contain any string constants that are longer than the maximum length supported by the current version of Db2 (for example, 255 or 32,704 characters).

Value

Description

CONCAT

Defines the concatenation operator as the keyword CONCAT. This is the default value. For example:

UPDATE owner.tableName SET column01='text01' CONCAT 'appendedText' WHERE column01 LIKE 'text01%';


BARS

Defines the concatenation operator as vertical bars (||). For example:

UPDATE owner.tableName SET column01='text01' || 'appendedText' WHERE column01 LIKE 'text01%';


INCLUDE or EXCLUDE INSERT COLUMN NAMES

Directs BMC AMI Log Master to include or exclude column names in SQL INSERT statements. The default value is INCLUDE INSERT COLUMN NAMES.

By excluding column names and including only the values, SQL files are smaller.

Value

Description

INCLUDE INSERT COLUMN NAMES

Includes column names in SQL INSERT statements. This is the default value.

EXCLUDE INSERT COLUMN NAMES

Excludes column names from SQL INSERT statements.

Important

For ALTER-added columns, generated-always columns, or LOB auxiliary table spaces with LOG NO, excluding INSERT column names might cause an error in SQL.

Column include/exclude

The Column include/exclude definition enables you to control the information in your SQL file based on table columns. For more information, see LOGSCAN-column-include-exclude-definition.

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.

This option is for SQL with INCLUDE DDL only.

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

This section contains the following topics:

 

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