Space announcements

   

This space provides the same content as before, but the organization of the home page has changed. The content is now organized based on logical branches instead of legacy book titles. We hope that the new structure will help you quickly find the content that you need.

LOGSCAN SQL file definition


This syntax specifies overall characteristics of the SQL statements that 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.

SQL file definition syntax diagram

OptionDescription

SQL type/output definition

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

QUALIFY

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

ValueDescription
ALLSpecifies that Log Master includes all columns in the generated WHERE clauses.
PRIMARY KEY

Specifies that 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, Log Master selects an index using DB2’s internal list of indexes for that table. 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, Log Master selects the first unique key index that it encounters.

    Note

    In DB2 Version 10 and later, a hash overflow index can also be the primary key or a unique key index.

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

If a primary key or a unique key index exists, Log Master includes the columns associated with that index in the generated WHERE clauses. If no primary or unique key index exists, 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, Log Master issues the warning message and includes all columns in the generated WHERE clauses.

CHANGED COLUMNSSpecifies whether Log Master includes all changed columns in the generated WHERE clauses. If you specify the ALL keyword, Log Master ignores this keyword.
USE OVERRIDESDirects 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 Log Master online interface.
TSOID tsoIDDirects 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 Log Master online interface as defaults for the user ID specified by tsoID.
VERBOSE

Directs 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, Log Master can include comments in the generated SQL to provide information about conditions detected during SQL generation.

COMMIT FREQUENCY

Specifies how frequently 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).

Note

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


ValueDescription
NONE | 0Specifies that all statements or transactions are committed at one time. If you set this value to zero, Log Master interprets it as equivalent to the NONE keyword (all statements or transactions at once).
nnnnSpecifies the number of SQL statements or transactions (URIDs) between generated COMMIT statements. If you set the value to one and use the TRANSACTIONS keyword, Log Master inserts COMMIT statements in the output SQL at the same points where they were included in the original SQL.
TRANSACTIONSDirects Log Master to interpret the commit frequency as a number of transactions (units of recovery).
STATEMENTSDirects 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 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, Log Master generates SQL that updates only the changed columns in a row.

DECIMAL POINT

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

ValueDescription
PERIODDefines the decimal point character as a period (.).
COMMADefines the decimal point character as a comma (,).

When 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 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 Log Master to use the specified characters as the SQL concatenation operator in output SQL statements. 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).

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

ValueDescription
INCLUDE INSERT COLUMN NAMESIncludes column names in SQL INSERT statements. This is the default value.
EXCLUDE INSERT COLUMN NAMES

Excludes column names from SQL INSERT statements.

Note

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.

This section contains the following topics:

Related topic


Was this page helpful? Yes No Submitting... Thank you

Comments