When 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).
The following figure shows the Load file definition syntax of the LOGSCAN statement.
Log Master sorts the records in an output load file (unless you use the NOSORT keyword). It uses the following columns as sort keys (in order): database name, table space name, table creator, table name, and RBA/LRSN. For more information about Unicode characters in output load files, see Considerations-for-output-files-and-Unicode.
| |
|---|
DATASET Output definition | |
| Determines the record format of the output load file. | |
|---|
| Indicates that records are variable length and blocked. This is the default value. | | Indicates that records are variable length, blocked, and spanned. You must specify VBS to create an output load file when all of the following conditions exist: - The load file includes tables with a page size of 8 KB, 16 KB or 32 KB.
- The total length of any update record in the load file (including the load record header, the before image, and the after image) is greater than the system-determined block size in your environment.
|
|
Control Output definition | Specifies the characteristics of the output load control file. This keyword is optional, reflecting the optional status of the load control file itself. For more information, see LOGSCAN-output-definition. The load control file contains a set of control statements for a Db2 Load utility. These control statements define the data contained in the load data file, to - Identify the tables into which data is loaded
- Describe fields in the input records (columns in the tables)
- Describe the overall format of the load data file
If the load data file contains information from log records of updates, the field specifications in the load control file describe only the columns in the after image of the log records (for more information, see UPDATES). |
| Specifies the overall format for the load data and load control files. The default value is Log Master. The CSV and SDF formats use character representation for all data to make it easier to move data to other platforms. | |
|---|
| Indicates that the load data and load control files are formatted using an internal Log Master format. The LOADPLUS for Db2 product available from BMC Software can also read a load file in this format. | | Indicates that the load data and load control files use the native format of UNLOAD PLUS. This format is similar to the format generated by Log Master, with small differences in the position and content of the null byte used in load records. - In the Log Master format, the null byte precedes column data, contains x'FF' to indicate a null column, and contains x'00' to indicate column data that is not null.
- In the UNLOAD PLUS format, the null byte follows column data, contains x'6F' (question mark) to indicate a null column, and contains x'40' (space) to indicate column data that is not null.
| CSV | COMMA SEPARATED VALUE | Indicates that the load data and load control files are formatted using the Comma Separated Value (CSV) format available in some database software. Individual fields in the load records are separated by commas. | SDF | STANDARD DEFINITION FORMAT | Indicates that the load data and load control files are formatted using the Standard Definition Format (SDF) format available in some database software. Individual fields in the load records are filled with spaces so that each field has the same position and length in all load records. Use the EXCLUDE NULL INDICATOR keyword to prevent Log Master from adding the null byte at the end of each field that corresponds to a nullable column. The byte contains x'6F' (question mark) for a null column, and contains x'40' (space) when the column is not null. This keyword applies only to output load files in SDF format; it affects both the load data file and the load control file. The default value causes Log Master to include the null byte (also called a null indicator). |
See also LOADFMT=LOGMAST. |
| | |
|---|
| 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. 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. | This keyword allows you to use different delimiters at the beginning and end of a string (ENCLOSED delimiter AND delimiter2). 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, Log Master will use the value that you specify for the ENCLOSEDBY keyword. If you do not specify a value for the ENCLOSEDBY keyword, 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. | | This keyword specifies the character that 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, Log Master will replace it with the specified target character (targetChar) in LOAD output CSV data. |
|
| Specifies which parts of a selected update log record appear in the output load data file. The default value is BOTH SEPARATE. | |
|---|
| Include only the before image of an update in the load output. | | 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. |
| 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. BMC Software recommends 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: - 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.
- Log Master includes the DATABASE NAME, TABLESPACE NAME, TABLE NAME, and TABLE CREATOR columns when:
- The value of the SEPARATE DATASETS keyword is YES and INFO COLUMNS has not been specified.
- The value of the SEPARATE DATASETS keyword is NO.
- Log Master will only include the TABLE NAME and TABLE CREATOR columns when:
- The value of the SEPARATE DATASET keyword is NO and INFO COLUMNS has been specified
- 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 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, 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: | |
|---|
| A character string is used to connect to Db2. (It frequently represents an individual, a work group, or a function.) | | A string identifier associated with a specific address space connection. | | A string constant representing the manner in which a unit of recovery thread is attached to Db2 (for example, BATCH, TSO, UTILITY). | | A unique string identifier for a specific Db2 thread. | | The name of the database containing the load records. | | Reserved for use in future versions of Db2. | | 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. | | The complete name of the table containing the load records (length and space filling similar to LONG TABLE CREATOR). | | Reserved for use in future versions of Db2. | | The number of a partition (within a partitioned table space) that contains the load records. | | A character string representing an application plan, the control structure used by Db2 to process SQL statements. | | The timestamp value associated with the log record that is represented in the individual load record. | | The record ID (RID) 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. | | A character string associated with the originating Db2 system. | | A shortened version of the authorization ID that was in use when the table represented in the load records was created. 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, Log Master truncates the complete name and supplies the first 8 bytes in this column. | | A shortened version of the name of the table is represented in the load records. 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, Log Master generates a unique 18-character name and supplies it in this column. To generate the shortened name, Log Master uses a tilde (~) character and characters that represent the table’s 4-digit hexadecimal database ID (DBID) and object ID (OBID). 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. | | A number representing 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. | | The name of the table space containing the load records. | | The LRSN of the log record where the unit of recovery was committed. | | The RBA of the log record where the unit of recovery was committed. | | The disposition of the unit of recovery (for example, committed or aborted). | | The RBA of the first log record in a unit of recovery. | | The LRSN of the first log record in a unit of recovery. | | The numeric identifier of the data sharing member associated with the unit of recovery. | | The timestamp value associated with the start of the unit of recovery. |
|
| 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 Log Master adds to the records in the output load file. BMC Software recommends 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. | |
|---|
| Indicates that Log Master includes all of the URID-related informational columns in each load record. | | Indicates that Log Master includes a specific subset of the URID-related informational columns in each load record. | | Indicates that 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. | | 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. |
|
| Specifies whether 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. 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. | |
|---|
| Directs Log Master to include URID-related information in the load data file and additional control cards in the load control file. | | Directs Log Master to exclude URID-related information from output load files. This is the default value. |
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, 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 follow the specifications that you enter, except that 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, Log Master includes the URID-related information in the load data file and includes the additional control cards in the load control file.
|
| Determines how Log Master writes variable length fields in the output load data files and load control files. | |
|---|
| Directs 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. | | Directs Log Master not to expand (fill) variable-length fields. |
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, Log Master creates output as if EXPAND VAR is set to YES. To conform to the CSV format, Log Master creates delimited output as if EXPAND VAR is set to NO. |
| 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 Log Master formats only. | |
|---|
| Directs Log Master not to generate LOAD output with spanned format. NO is the default value. | | Directs 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. 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, Log Master LOAD automatically generates LOAD output with XML and LOB columns at the end of the control field specification and data records. |
| Determines whether 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, Log Master includes or excludes compensated log records. For more information, see INCLUDE ROLLBACK in LOGSCAN report definition. | |
|---|
| Directs Log Master to exclude compensated log records from the output load file. Use this default value for normal processing. | | Directs Log Master to include compensated log records. The output load file reflects both compensated log records and noncompensated log records. | | Directs Log Master to include only compensated log records. The output load file does not reflect any noncompensated 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, 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). |
| Determines whether Log Master includes the data from large object (LOB) columns in the output load file. When the value of this keyword is YES, 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. 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. 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 Log Master performance considerably. For more information about how Log Master processes LOB columns and data, see the BMC AMI Log Master for Db2 documentation. If you do not specify INCLUDE LOBS, Log Master does not include LOB data. If you specify INCLUDE LOBS without specifying YES, NO, or INLINE, the default value is YES (Log Master includes LOB data). | |
|---|
| Directs Log Master to include LOB data in the output load file. Log Master writes temporary LOB VSAM files and includes LOB data in the output files within the load data file. | | Directs Log Master to avoid including LOB data in the output load file. | | Directs Log Master to include only inline LOB data in the output load file, ignoring the auxiliary LOB tables. Log Master writes permanent LOB VSAM files and includes LOB data in the output files within the load data. |
Tip (SPE2010) For larger LOB or XML output records, consider unloading to VBS data sets by using SPANNED YES. |
| Determines whether Log Master includes the data from XML columns in the output load file. When the value of this keyword is YES, 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. 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. 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 Log Master performance considerably. | |
|---|
| Directs Log Master to include XML data in the output load file. Log Master writes temporary XML VSAM files and includes XML data in the output files within the load data file. | | Directs Log Master to avoid including XML data in the output load file. |
If you do not specify INCLUDE XML, Log Master does not include XML data. If you specify INCLUDE XML without specifying YES or NO, the default value is YES (Log Master includes XML data). Tip (SPE2010) For larger LOB or XML output records, consider unloading to VBS data sets by using SPANNED YES. |
| Determines whether 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. | |
|---|
| Directs Log Master to generate the IGNOREFIELDS clause in the output load file. | | Directs Log Master to avoid generating the IGNOREFIELDS clause in the output load file. |
|
| Directs 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. | |
|---|
| Directs Log Master to generate the PERIODOVERRIDE clause in the output load file. | | Directs Log Master to avoid generating the PERIODOVERRIDE clause in the output load file. |
|
| Specifies whether 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. | |
|---|
| Directs Log Master not to create separate output files. This is the default value. | | Directs 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. 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. |
|
| Extends the effect of the SEPARATE DATASETS keyword. This keyword directs Log Master to create separate output files for all individual tables selected in your log scan regardless of whether 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, 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, Log Master does not create empty files for tables with no log records. Valid filter predicates, GENERATE EMPTY FILES keyword You cannot use this keyword if the value of the FILTER METHOD keyword (or the FLTRMTHD installation option) is DYNAMIC. |
| Specifies whether Log Master places quotation marks (') before and after column names in the LOAD control command output. | |
|---|
| 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. | | Log Master does not place quotation marks before and after the column names in the LOAD control command. | | Log Master places quotation marks before and after all of the column names in the LOAD control command. |
|
| Directs 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, 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
BMC recommends 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. |
| 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. |
