LOGSCAN load file definition

This syntax specifies the location and format of the load data file and load control file that BMC AMI Log Master creates.

When BMC AMI Log Master creates load output, the load data file contains information from the log records that you select, but the records are formatted for a Db2 Load utility (or a file import utility on another platform).

See also LOADFMT=LOGMAST.

CSV Field specification

Value

Description

ENCLOSED | ENCLOSEDBY

The ENCLOSEDBY keyword specifies the delimiter character or characters to use to enclose data in string or text fields. The default is a double quotation mark ('). You can specify up to eight characters for the delimiter variable, and delimiters can be represented in hexadecimal.

BMC AMI Log Master can use the same delimiter at the beginning and the end of a string, or it can use two different delimiters if you include the AND keyword.

AND

This keyword allows you to use different delimiters at the beginning and end of a string (ENCLOSED delimiter AND delimiter2). BMC AMI Log Master will use the value that you specify for delimiter on the left side of the string and your value for delimiter2 on the right side.

If you do not specify a value for this keyword, BMC AMI Log Master will use the value that you specify for the ENCLOSEDBY keyword. If you do not specify a value for the ENCLOSEDBY keyword, BMC AMI Log Master will use the default double quotation mark (').

To use the same delimiter in both places, omit the AND keyword (for example, ENCLOSED delimiter).

Important

You must ensure that any multiple-character delimiter that you specify is not contained in your column data.

TERMINATED | TERMINATEDBY

This keyword specifies the characters that your input data uses to delimit fields of data. You can specify up to eight characters. The default is a comma (,). The delimiters can be represented in hexadecimal.

DECIMAL POINT

This keyword specifies the character that BMC AMI Log Master will use as the decimal point for decimal columns. You can specify a single character, or the keyword COMMA or PERIOD. The default is a period (.).

TRANSLATE sourceChar TO targetChar

This keyword specifies the translation rule for string column data. Upon finding the specified source character (sourceChar) in the column string, BMC AMI Log Master will replace it with the specified target character (targetChar) in LOAD output CSV data.

UPDATES

Specifies which parts of a selected update log record appear in the output load data file. The default value is BOTH SEPARATE.

Value	Description
BEFORE	Include only the before the image of an update in the load output.
AFTER	Include only the after image of an update in the load output.
BOTH \| SEPARATE \| TOGETHER	BOTH includes both the before and after images in the load output. If you specify BOTH, you can choose whether the images appear as SEPARATE load records (the default value) or TOGETHER as a single load record. If you specify this keyword, the records in the load data file contain both before and after images of update log records, but the load control file contains field specifications for only the after image of the update.

The value of this keyword can determine the overall layout of the records in your output load file. For more information, see Layout-of-records-in-load-files.

INFO COLUMNS

BMC AMI Log Master adds several informational columns to the specific log record information in the load data file and load control file output. Either the INFO COLUMNS keyword or the URID FIELDS keyword control which informational columns are added to the records in the output load file. We recommend using the INFO COLUMNS keyword. You cannot specify values for both the INFO COLUMNS keyword and the URID FIELDS keyword in the same log scan. Remember the following points when you specify informational columns for an output load file:

BMC AMI Log Master always adds three informational columns to the load data and load control files to give context to the load records. The load records always contain the LOG RBA, CHANGE TYPE, and UPDATE TYPE columns.
BMC AMI Log Master includes the TABLE NAME and TABLE CREATOR columns when the value of the SEPARATE DATASETS keyword is YES.
BMC AMI Log Master includes the TABLE SEQNUM column when any of the following conditions exist:
- The value of the EXECUTION MODE keyword is OVERTIME.
- The input source is a logical log file created by BMC AMI Log Master in overtime mode.
- The INFO COLUMNS (or URID FIELDS ONLY) keyword specifies TABLE SEQNUM as an informational column.
When you use a load file to move data from one table to another table, you must define additional columns in the target table to match the informational columns included in the load file. If you do not want the informational columns in the receiving table, edit the load control file to filter out the unwanted columns.
If you specify the value of URID TABLE as YES, BMC AMI Log Master always includes a standard set of informational columns in the separate URID-related information (regardless of the value of either the INFO COLUMNS or URID FIELDS keywords). For a list of the standard informational columns, see Additional-informational-columns-in-load-files.

The infoColName can be any of the following values:

Value	Description
AUTH ID	A character string is used to connect to Db2. (It frequently represents an individual, a work group, or a function.)
CONNECTION ID	A string identifier associated with a specific address space connection.
CONNECTION TYPE	A string constant represents the manner in which a unit of recovery thread is attached to Db2 (for example, BATCH, TSO, UTILITY).
CORRELATION ID	A unique string identifier for a specific Db2 thread.
DATABASE NAME	The name of the database containing the load records.
LONG DATABASE NAME	Reserved for use in future versions of Db2.
LONG TABLE CREATOR	The complete authorization ID that was in use when the table represented in the load records was created. The complete creator name is 128 characters long. If the actual name is shorter than this length, the product adds spaces to the end of the name until it is 128 characters long.
LONG TABLE NAME	The complete name of the table containing the load records (length and space filling similar to LONG TABLE CREATOR).
LONG TABLESPACE NAME	Reserved for use in future versions of Db2.
PARTITION	The number of a partition (within a partitioned table space) that contains the load records.
PLAN NAME	A character string representing an application plan, the control structure used by Db2 to process SQL statements.
RECORD TIMESTAMP	The timestamp value is associated with the log record is represented in the individual load record.
RID	The record ID (RID) is associated with the log record that is represented in the individual load record. The RID contains a page number value and a row ID value.
SYSTEM ID	A character string is associated with the originating Db2 system.
TABLE CREATOR	A shortened version of the authorization ID that was in use when the table represented in the load records was created. When SEPARATE DATASETS is set to the default value of NO, BMC AMI Log Master includes this column even if you do not specify this keyword. BMC AMI Log Master provides the shortened creator name for compatibility with earlier versions of Db2 and earlier versions of logical log records. For the complete table name, see LONG TABLE CREATOR. The shortened table creator is no longer than 8 characters. If the actual name is longer than this limit, BMC AMI Log Master truncates the complete name and supplies the first 8 bytes in this column.
TABLE NAME	A shortened version of the name of the table is represented in the load records. When SEPARATE DATASETS is set to the default value of NO, BMC AMI Log Master includes this column even if you do not specify this keyword. BMC AMI Log Master provides the shortened name for compatibility with earlier versions of Db2 and earlier versions of logical log records. For the complete table name, see LONG TABLE NAME. The shortened name is no longer than 18 characters. If the actual name is longer than this limit, BMC AMI Log Master generates a unique 18-character name and supplies it in this column. To generate the shortened name BMC AMI Log Master uses a tilde (~) character and characters that represent the table’s 4-digit hexadecimal database ID (DBID) and object ID (OBID). BMC AMI Log Master truncates the initial portion of the complete name and concatenates it with the generated string so that the combination is no longer than 18 characters. For example, if a table has a DBID of 051C, an OBID of 0007, and a name of A_VERY_DESCRIPTIVE_AND_PRECISE_TABLE_NAME, then the shortened name would be A_VERY_DE~051C0007.
TABLE SEQNUM	A number represents the instance of a table during the time frame of your log scan. If you drop and re-create a table using the same fully qualified name, log records can relate to either the original or the re-created instance of the table. The table sequence number is greater than 1 only if the log scan includes log records relating to more than one instance of the same table. This column becomes important when your job runs in overtime mode or when your input is combined from multiple logical log files (for example, when weekly logical log files are combined to produce a single monthly output). In any situation where log records relate to multiple instances of the same table name, you can use the table sequence number to select the log records that you require.
TABLESPACE NAME	The name of the table space containing the load records.
URID COMMIT LRSN	The LRSN of the log record where the unit of recovery was committed.
URID COMMIT POINT	The RBA of the log record where the unit of recovery was committed.
URID DISP	The disposition of the unit of recovery (for example, committed or aborted).
URID BEGIN POINT	The RBA of the first log record in a unit of recovery.
URID LRSN	The LRSN of the first log record in a unit of recovery.
URID MEMBER ID	The numeric identifier of the data sharing member is associated with the unit of recovery.
URID TIMESTAMP	The timestamp value is associated with the start of the unit of recovery.

URID FIELDS

BMC AMI Log Master adds several informational columns to the specific log record information in the load data file and load control file output. Either the URID FIELDS keyword or the INFO COLUMNS keyword control which informational columns BMC AMI Log Master adds to the records in the output load file. We recommend using the INFO COLUMNS keyword. You cannot specify values for both the URID FIELDS keyword and the INFO COLUMNS keyword in the same log scan.

Some of the informational columns relate to the unit of recovery identifier (URID). The URID FIELDS keyword lets you select certain predefined sets of URID-related informational columns.

Value	Description
ALL	Indicates that BMC AMI Log Master includes all of the URID-related informational columns in each load record.
URID	Indicates that BMC AMI Log Master includes a specific subset of the URID-related informational columns in each load record.
NONE	Indicates that BMC AMI Log Master does not include any of the URID-related informational columns in each load record. Only a default set of informational columns are included. This is the default value.
ONLY infoColName	This keyword enables you to control both the URID-related and the other informational columns that are included in each load record. To use the ONLY keyword, you must specify at least one informational column for inclusion. Separate multiple columns with commas. The infoColName can be any value listed in INFO COLUMNS.

URID TABLE

Specifies whether BMC AMI Log Master includes a separate record in the output load data file for each unit of recovery. The records contain URID-related information (for example, activity counts). This keyword also determines whether the load control file includes additional control cards to create a separate table for the URID-related information. The name of the separate URID table in the control cards is always #ALPURID.#ALPURIDTABLE.

BMC AMI Log Master always includes the same set of informational columns with the URID-related information, regardless of the value of either the INFO COLUMNS or the URID FIELDS keywords.

Value	Description
YES	Directs BMC AMI Log Master to include URID-related information in the load data file and additional control cards in the load control file.
NO	Directs BMC AMI Log Master to exclude URID-related information from output load files. This is the default value.

BMC AMI Log Master produces the URID-related information differently, depending on the value of the SEPARATE DATASETS keyword. For more information about this keyword, see SEPARATE DATASETS.

When SEPARATE DATASETS is YES, BMC AMI Log Master writes the URID-related information into a separate load data file and writes the additional control cards in a separate load control file.
The data set names of the load data file and load control file to follow the specifications that you enter, except that BMC AMI Log Master automatically sets the value of the &TABOWN symbolic substitution to #ALPURID and the value of the &TABNAME symbolic to #ALPURIDTABLE. (For more information about symbolics, see Symbolic-substitutions.) Do not concatenate any additional characters with the &TABxxxx symbolics; you might generate a node in the data set name that is longer than the operating system allows.

When SEPARATE DATASETS is NO, BMC AMI Log Master includes the URID-related information in the load data file and includes the additional control cards in the load control file.

EXPAND VAR

Determines how BMC AMI Log Master writes variable length fields in the output load data files and load control files.

Value	Description
YES	Directs BMC AMI Log Master to expand (fill) variable-length fields with spaces up to the declared maximum length. YES is the default value except for LOB or XML columns in RECFM VBS or SPANNED YES.
NO	Directs BMC AMI Log Master not to expand (fill) variable-length fields.

BMC AMI Log Master ignores the EXPAND VAR keyword for an output load file in either the SDF or CSV formats. To conform to the requirements of the SDF format, BMC AMI Log Master creates output as if EXPAND VAR is set to YES. To conform to the CSV format, BMC AMI Log Master creates delimited output as if EXPAND VAR is set to NO.

SPANNED

Specifies whether records should be unloaded into a standard variable-block spanned (VBS) data set. The format of this data set is similar to the IBM proprietary spanned record format. This option is for UNLOAD PLUS for DB2 and BMC AMI Log Master formats only.

Value	Description
NO	Directs BMC AMI Log Master not to generate LOAD output with the spanned format. NO is the default value.
YES	Directs BMC AMI Log Master to generate LOAD outputs with LOB or XML data in spanned record format. All LOB or XML data is written to the end of data records. BMC AMI Log Master ignores the RECFM attribute of the data set. EXPAND VAR NO is used for LOB or XML data.

Tip

For larger LOB or XML output records, consider unloading to VBS data sets by using SPANNED YES.

Important

The BMC UNLOAD PLUS product and IBM Db2 UNLOAD utility with SPANNED YES require that you list XML and LOB columns at the end of the field specification. If you specify SPANNED YES, BMC AMI Log Master LOAD automatically generates LOAD output with XML and LOB columns at the end of the control field specification and data records.

INCLUDE ROLLBACK

Determines whether BMC AMI Log Master includes information from compensated log records in the output load file. Use the default value of NO for normal processing. In this context, compensated log records are the log records of database changes that Db2 subsequently 'compensates for' or 'reverses' (for example, log records of changes that are later reversed by a ROLLBACK statement). Depending on the value of this keyword, BMC AMI Log Master includes or excludes compensated log records. For more information, see INCLUDE ROLLBACK in LOGSCAN report definition.

Value	Description
NO	Directs BMC AMI Log Master to exclude compensated log records from the output load file. Use this default value for normal processing.
YES	Directs BMC AMI Log Master to include compensated log records. The output load file reflects both compensated log records and non-compensated log records.
ONLY	Directs BMC AMI Log Master to include only compensated log records. The output load file does not reflect any non-compensated log records.

Warning

Exercise caution before specifying a value of YES or ONLY. In most circumstances, when you include compensated log records, your output load file includes data that has not been applied to the original Db2 database. For example, if you migrate data from one database to another and you use an output load file that reflects compensated log records, you can corrupt the data in your target database.

If you do not specify INCLUDE ROLLBACK, BMC AMI Log Master ignores compensated log records. If you specify INCLUDE ROLLBACK without specifying YES or NO, the default value is YES (include both compensated and noncompensated log records).

INCLUDE LOBS

Determines whether BMC AMI Log Master includes the data from large object (LOB) columns in the output load file. When the value of this keyword is YES, BMC AMI Log Master writes temporary LOB VSAM files during processing, but does not harden them permanently to disk unless the log scan includes an output logical log file.

BMC AMI Log Master generates one LOB VSAM file for each LOB column (or each partition of a LOB column) that occurs in the selected log records. BMC AMI Log Master allocates an additional LOB VSAM file only when it has filled the initial data set and all possible extents, but more LOB column data remains to be written. Depending on your environment and your LOB data, the additional disk I/O required to process LOB VSAM files can slow BMC AMI Log Master performance considerably.

For more information about how BMC AMI Log Master processes LOB columns and data, see Working-with-large-volume-columns.

If you do not specify INCLUDE LOBS, BMC AMI Log Master does not include LOB data. If you specify INCLUDE LOBS without specifying YES, NO, or INLINE, the default value is YES (BMC AMI Log Master includes LOB data).

Value	Description
YES	Directs BMC AMI Log Master to include LOB data in the output load file. BMC AMI Log Master writes temporary LOB VSAM files and includes LOB data in the output files within the load data file.
NO	Directs BMC AMI Log Master to avoid including LOB data in the output load file.
INLINE	Directs BMC AMI Log Master to include only inline LOB data in the output load file, ignoring the auxiliary LOB tables. BMC AMI Log Master writes permanent LOB VSAM files and includes LOB data in the output files within the load data.

Tip

For larger LOB or XML output records, consider unloading to VBS data sets by using SPANNED YES.

INCLUDE XML

Determines whether BMC AMI Log Master includes the data from XML columns in the output load file. When the value of this keyword is YES, BMC AMI Log Master writes temporary XML VSAM files during processing, but does not harden them permanently to disk unless the log scan includes an output logical log file.

BMC AMI Log Master generates one XML VSAM file for each XML column (or each partition of an XML column) that occurs in the selected log records. BMC AMI Log Master allocates an additional XML VSAM file only when it has filled the initial data set and all possible extents, but more XML column data remains to be written. Depending on your environment and your XML data, the additional disk I/O required to process XML VSAM files can slow BMC AMI Log Master performance considerably.

Value	Description
YES	Directs BMC AMI Log Master to include XML data in the output load file. BMC AMI Log Master writes temporary XML VSAM files and includes XML data in the output files within the load data file.
NO	Directs BMC AMI Log Master to avoid including XML data in the output load file.

If you do not specify INCLUDE XML, BMC AMI Log Master does not include XML data. If you specify INCLUDE XML without specifying YES or NO, the default value is YES (BMC AMI Log Master includes XML data).

Tip

For larger LOB or XML output records, consider unloading to VBS data sets by using SPANNED YES.

IGNOREFIELDS

Determines whether BMC AMI Log Master generates the IGNOREFIELDS clause in the load control file. With the value of YES for this keyword, Db2 will ignore the values for the GENERATED ALWAYS columns that define the PERIOD for a system-maintained temporal table. To retain PERIOD values, use the PERIODOVERRIDE keyword.

Value	Description
YES	Directs BMC AMI Log Master to generate the IGNOREFIELDS clause in the output load file.
NO	Directs BMC AMI Log Master to avoid generating the IGNOREFIELDS clause in the output load file.

PERIODOVERRIDE

Directs BMC AMI Log Master to generate the PERIODOVERRIDE clause in the output load file so that, when the data is reloaded, Db2 maintains the values of the GENERATED ALWAYS columns that define the PERIOD for a system-maintained temporal table.

Value	Description
YES	Directs BMC AMI Log Master to generate the PERIODOVERRIDE clause in the output load file.
NO	Directs BMC AMI Log Master to avoid generating the PERIODOVERRIDE clause in the output load file.

SEPARATE DATASETS

Specifies whether BMC AMI Log Master creates separate output files for each individual table selected in the log scan. This keyword applies to both the load data file and the load control file.

Value

Description

Directs BMC AMI Log Master not to create separate output files. This is the default value.

YES

Directs BMC AMI Log Master to create separate output files, resulting in multiple load data files and multiple load control files. If you specify YES, you must mask the names of any data sets that you specify for either the output load data file or the output load control file. You must use at least one of the required symbolic names in the file name. BMC AMI Log Master resolves the symbolic substitutions for each table in the load data or load control file. For more information about symbolic, see Symbolic-substitutions.

GENERATE EMPTY FILES

Extends the effect of the SEPARATE DATASETS keyword. This keyword directs BMC AMI Log Master to create separate output files for all individual tables selected in your log scan regardless of whether BMC AMI Log Master finds log records related to a table. If your WHERE clause or filter selects a table, but the log contains no records related to that table, BMC AMI Log Master creates

A separate (empty) output load data file that contains no-load records (some environments can use the empty load data file to avoid JCL errors)
A separate output load control file that contains the syntax required for a Db2 Load utility to load data into that table

To use this keyword, your WHERE clause or filter can contain only the common filter predicates. In addition, the filter predicates in the WHERE clause or filter can be combined only with the OR logical operator. If you use other filter predicates or combine them with the AND or NOT operator, BMC AMI Log Master does not create empty files for tables with no log records.

Valid filter predicates, GENERATE EMPTY FILES keyword

Selectable field	Operator
DATABASE NAME	=
DATABASE NAME	LIKE
TABLESPACE NAME	=
	LIKE
	IN
TABLE NAME	=
	LIKE
	IN
tableName.columnName	=

You cannot use this keyword if the value of the FILTER METHOD keyword (or the FLTRMTHD installation option) is DYNAMIC.

DELIMIT COLUMN NAME

Specifies whether BMC AMI Log Master places quotation marks (') before and after column names in the LOAD control command output.

Value	Description
ONLY	BMC AMI Log Master places quotation marks before and after a column name only if the name is a KEYWORD or a delimited identifier. ONLY is the default.
NONE	BMC AMI Log Master does not place quotation marks before and after the column names in the LOAD control command.
ALL	BMC AMI Log Master places quotation marks before and after all of the column names in the LOAD control command.

NOSORT

Directs BMC AMI Log Master to not sort the records in the output load data file. When you specify NOSORT , the load records can appear in any order.

If you do not specify the NOSORT keyword, BMC AMI Log Master sorts the records by using the following columns as sort keys (in order):

Database name
Table space name
Table creator
Table name
RBA/LRSN

We recommend caution when specifying NOSORT. Use of this keyword could result in duplicate records being created in successive ongoing log scans (due to the dependent RBA/LRSN).

To avoid duplicate records when using NOSORT

Define tables being extracted with the Data Capture Changes (DCC) attribute
Ensure that application programs in your environment ignore records in the load data file if the records are dependent on a transaction that is incomplete at the end of an ongoing log scan
To determine whether programs should ignore a record, review the dependent RBA/LSRN value stored in the ALPURID Repository table for the run sequence number of the job that created the load data file.
- If the dependent RBA/LSRN value is x'FFFFFFFFFFFF', the load data file does not contain any records that should be ignored.
- If the dependent RBA/LSRN value is not x'FFFFFFFFFFFF', compare it to the informational column LOGRBA in the output load file.
- If the LOGRBA is greater than or equal to the dependent RBA/LSRN value, ignore the record. The same record will appear in the load data file produced by a subsequent run of the ongoing log scan.
- If the LOGRBA is less than the dependent RBA/LSRN value, process the record.

For more information about ongoing processing, see Logical-log-files-and-ongoing-processing (the section discusses logical log files, however, the ongoing processing information also applies to load data files). For more information about dependent RBA/LRSN values, see Dependent RBA/LRSN Value. For more information about informational columns, see Additional-informational-columns-in-load-files.

Column include/exclude

The Column include/exclude definition enables you to control the information in your load file based on table columns. For more information about including or excluding column data, see LOGSCAN-column-include-exclude-definition.

In this section:

LOGSCAN load file definition

On this page