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 report definition

Use this syntax to specify the reports that Log Master generates from the log records selected by current log scan.

You can specify multiple Report definitions in one LOGSCAN statement, but Log Master scans the DB2 log only once for the entire work ID.

Report definition syntax diagram

(PTF BQU1148 applied)

Use the Report definition to specify a report type and an output location. You can include or exclude specific column data.

Log Master reports (like all other forms of product output) include log data from transactions that are completed within the scan range that you specify. In this context, completed means either committed or aborted. To obtain information about transactions that are not complete at the end of a scan range, use the Open Transaction report or the Object Activity Summary report. For more information, see OPEN TRANS or ALL ACTIVITY.

The following pages contain information about report types, along with references to output and column include/exclude syntax. For more information on Unicode characters in reports, see Considerations for output files and Unicode.

TEMPLATE

Directs Log Master to format the generated report by using a report template that is stored in an external data set or in the Log Master Repository. To create a report template, use the Log Master online interface to define the template. Use options in the interface to export the template into a data set or store it in the Repository. Log Master stores the report template in the form of XML elements and attribute values.

The report type that you specify in your job or job step must match the report type defined in the template. A template reflects the report options (for example, order by fields) that were selected when the template was created. To change the order by fields or other options, modify the template. You cannot specify a template and an Order By definition for the same report.

Log Master also provides sample report templates. For more information, see Sample files and other information. You can import the templates in the Log Master online interface and modify them if needed, or you can use them via the REPORT TEMPLATE DATASET syntax.

DATASETDefines a data set that contains a template that Log Master uses to format the generated report. The data set must contain a valid Log Master report template.
IDDefines the identity of a template in the Log Master Repository. Log Master uses the template to format your generated report. To use a template from the Repository, the template must have been created in or imported into the Repository on the DB2 subsystem (SSID) where your job or job step runs. To use the same template on multiple subsystems, export the template to a data set.
TYPE

Specifies the type of report that Log Master generates. The following paragraphs list and describe the reports generated by each TYPE keyword.

For more information about different types of reports, such as sample reports or ordering defaults and restrictions, see the section about Log Master for DB2 reports in the Log Master for DB2 documentation .

BACKOUT INTEGRITY

Provides information about changes in data that would complicate an UNDO or REDO action. For each change that is part of an UNDO or REDO action, the report shows information about the change and about subsequent changes that would be affected if the original change is executed. The report also supplies information about the set of affected objects and the volume of information affected by the changes. The report presents only data associated with committed transactions.

To generate a Backout Integrity report, Log Master scans the DB2 log to the current time, even if you do not specify CURRENT as your end point. Log Master takes this action because the log records that you select might be affected by subsequent transactions that occur after your time frame. Log Master defines the current time as the last relative byte address (RBA) or log record sequence number (LRSN) that DB2 has written to the log when the Log Master job begins executing.

Log Master can produce the Backout Integrity report in two formats: Detail (including field data and all URID information) or Summary (a more concise report, omitting field data and some URID information). Use the SUMMARY keyword to generate the concise version of the Backout Integrity report.

Log Master cannot produce either version of a Backout Integrity report when the input source of your log scan is individual DB2 log files (INPUT DB2LOG).

QUIET POINT

Provides information about ranges within the DB2 log when no activity is present for a given set of DB2 objects. The report lists only the quiet ranges common to all table spaces defined (either directly or indirectly) by your WHERE clause or filter. The optional MAXURIDS keyword enables you to list ranges when the amount of activity is below a level that you specify ('almost quiet' ranges).

To generate this report, ensure that your WHERE clause or filter refers to at least one specific DB2 object (such as a table name or a column name). This action ensures that you define (either directly or indirectly) a set of table spaces that Log Master can search for quiet ranges.

Log Master cannot produce a Quiet Point report when the input source for your work ID is individual DB2 log files (INPUT DB2LOG) or a logical log file generated by Log Master or another program (INPUT LLOG).

DURATION HH.MM.SS.mmmmmm

By default, the Quiet Point report includes all quiet ranges for the specified DB2 objects (no minimum). Use this keyword to exclude shorter quiet ranges from the report. Log Master suppresses any quiet ranges that are shorter than the duration that you specify.

Enter a minimum duration, using the time format shown in the syntax diagram. Enter any value from one microsecond to 24 hours. You must enter numeric values for hours, minutes and seconds, but microsecond values are optional. For example, to specify five seconds, enter 00.00.05 in the syntax.

MAXURIDS uridCount

By default, the Quiet Point report includes only quiet ranges that are completely quiet (absolutely no activity for the table spaces directly or indirectly defined by your filter). By using this keyword, you can include 'almost quiet' ranges in the report. These ranges are periods within the log when the number of open transactions (units of recovery) that affect your table spaces is less than or equal to the number that you specify. Use this keyword to report on periods of high activity for your specified objects, when completely quiet ranges might not exist.

The report includes both the almost quiet ranges and the completely quiet ranges. For each quiet range, the report lists the number of open transactions that are present during the range.

Enter a number of open transactions between zero and thirty-two. Zero indicates that the report includes only completely quiet ranges. The default value is zero.

QUIESCE

You can direct Log Master to insert a quiesce entry into the DB2 catalog table SYSIBM.SYSCOPY for each table space defined (directly or indirectly) by the WHERE clause or filter. Specify YES to insert a quiesce record in SYSIBM.SYSCOPY using the RBA/LRSN of the latest quiet range included in the report. Specify NO to avoid inserting a quiesce record. The default value is NO.

When you specify YES, Log Master also inserts index quiesce records into SYSIBM.SYSCOPY for any COPY YES indexes associated with the table spaces.Log Master records the correct quiet point registration so all recoverable objects have the proper QUIESCE entry in SYSCOPY.

Log Master does not insert a quiesce record if:

  • Log Master finds no quiet ranges that meet your criteria

  • The report includes only almost quiet ranges

If you use a report template to customize the Quiet Point report, you can direct Log Master to sort the quiet ranges based on duration. If you sort in ascending order, Log Master uses the longest quiet range in your report as the point to insert a quiesce record in SYSIBM.SYSCOPY.

NOQUIETPOINTRC

Specifies the return code (RC) with which Log Master will end if no quiet points are found. INFO, WARN, ERROR, SEVERE, and UNRECOV translate to RCs of 0, 4, 8, 12, and 16, respectively. You can also use a value between 0 and 4095. The default is WARN.

The NOQUIETPOINTRC value determines the severity of message BMC097547. The setting of the severity code is as follows:

Message suffix

Message severity

Return code values

I

Informational

0

W

Warning

>0 and <=4

E

Error

>4 and <= 8

S

Severe

>8 and <=12

U

Unrecoverable

>12

Note

NOQUIETPOINTRC must be greater than the resulting return code set during normal processing; otherwise, it will be overwritten. For example, if NOQUIETPOINTRC is set to 6 and Log Master encounters an unrecoverable error, Log Master will end with RC=16, not RC=6.

MARK logMarkName

By specifying a log mark name with the MARK option, you can direct Log Master to record a log mark with the RBA/LRSN associated with the quiet point.

Log Master does not record the log mark if:

  • Log Master finds no quiet ranges that meet your criteria

  • The report includes only almost quiet ranges

If you use a report template to customize the Quiet Point report, you can direct Log Master to sort the quiet ranges based on duration. If you sort in ascending order, Log Master uses the longest quiet range in your report as the point to record the log mark.

DESC "description"Defines an optional, 65-character text string. Use the text description to store any comments that you want to associate with the log mark name.
AUDIT

Provides a record of changes to data, identifying who changed the data, when it was changed, and how much data was changed. This report also gives related index and record ID (RID) information.

The Audit report is similar to the report produced by the DETAIL keyword, except that the Audit report presents only changed data from update log records, and it includes primary key information.

You can also use an Audit report to show information about changes to the DB2 catalog, including changes to DB2 security. The Catalog Activity report also provides this information.

The REDO keyword is valid for only Audit, Detail, or Summary reports. Use the REDO keyword only when you want a report to show you which log records would be selected for a REDO operation based on your current WHERE clause or filter. The keyword determines how Log Master uses your filter to select log records for the output report. For more information, see REDO.

CATALOG ACTIVITY

Provides information about changes that affect the DB2 catalog, including

  • The type of DB2 object that was changed

  • The type of data definition language (DDL) statement that changed the object

  • URID-related information about the process, program, or user that made the change (for example, authorization ID or correlation ID)

A Catalog Activity report can show all changes that affect a given object type (for example, indexes), or all changes that result from a given DDL statement type (for example, GRANT and REVOKE statements to show security changes). This report contains the same basic information that would be included if you generated a MIGRATE DDL output file with the VERBOSE keyword.

Be aware of the following points as you define a Catalog Activity report:

  • To generate a Catalog Activity report, you must set the value of the DB2CATALOG keyword to YES.

  • The Catalog Activity report uses a wide format for data. Specify a logical record length (LRECL) value of at least 132 bytes to support the data format.

  • Catalog activity definition and Catalog object definition provide filter options that are helpful with the Catalog Activity report.

  • ORDER BY definition provides the ACTVITY TYPE and OBJECT TYPE keywords to conveniently sort a Catalog Activity report.

  • To obtain the information that you need, you might need to include some additional DB2 objects in your filter. For more information about filter optimization and usage, see the section on defining the log scan step in the Log Master for DB2 documentation .

  • When you generate a Catalog Activity report, Log Master can generate an output DDL file 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.

COLUMN UPDATE FREQUENCY

Provides a summary of activity with the addition of column detail if the table was updated. The report maintains counts for each changed column and provides a total number of changed columns. You can use this report to assist in index tuning and to determine whether placing an index on a column would have a negative impact.

The report details include:

  • Name

  • Number

  • Type

  • Length

  • Indexed column (Y/N)

  • Frequency (the number of times a column was updated)

ORDER BY definition provides information about the update frequency of the table column.

COMMAND

Provides information about DB2 commands that were issued within a specified period on a DB2 subsystem.

Be aware of the following points as you define a Commands report:

  • ORDER BY definition provides the COMMAND keyword to conveniently sort a Commands report.

  • When you generate a Commands report, Log Master 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.

COMMIT

Provides information about commit frequency for application tuning and system performance purposes. Depending on how you order the report, counts are maintained for each unit of recovery to indicate the activity volume between commit points.

Use this report to determine if batch jobs are not committing changes according to standards or performance recommendations. You can also use it to determine if online transactions are performing too many operations in a given unit of work.

DATA CAPTURE ANALYSISProvides information about log records with either the data capture changes (DCC) or data capture none (DCN) attributes. This report provides information to help determine the impact of setting the data capture changes attribute on specific tables. Log Master estimates the number of bytes that would be logged if you set data capture changes on or off.
DETAIL

Provides information about log records. Use this report to display log data to help diagnose application problems, or to show log data as an audit trail for applications.

This report is similar to the report produced by the AUDIT keyword, except that the Detail report presents all data from update log records, and it does not include primary key information.

You can also use a Detail report to show 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.

The REDO keyword is valid for only Audit, Detail, or Summary reports. Use the REDO keyword only when you want a report to show you which log records would be selected for a REDO operation based on your current WHERE clause or filter. The keyword determines how Log Master uses your filter to select log records for the output report. For more information, see REDO.

IMAGE COPY

Provides information about the volume of table space activity between image copies. For the table spaces selected (directly or indirectly) by your filter, the report shows data on all applicable image copies logged in the SYSIBM.SYSCOPY table of the DB2 catalog during your time frame. For each image copy listed, the report shows the activity (the number of insert, update, and delete actions) since the preceding image copy.

Use this report to determine if a table space has too many updates between copies (which could lead to excessive recovery times) or if a table space is being copied more often than necessary.

OPEN TRANS

Provides information about transactions (units of recovery) that are not committed or aborted at the end point of your log scan, including any transactions that begin before the start point of your log scan. Use this report to identify any work that might not be represented on other product reports. Be aware of the following points regarding this report:

  • Log Master reads DB2 checkpoint records before the start point of your log scan to obtain information about the transactions that were open when the checkpoint record was created.

  • The report can include open transactions that do not affect the DB2 objects selected (directly or indirectly) by your filter. Log Master includes these transactions because an open transaction might include activity affecting your selected objects past the end point of your log scan.

  • The transactions included in the report can vary depending on whether your filter selects log records based on unit of recovery criteria (for example, by plan name or authorization ID).

    • If your filter selects based on only unit of recovery criteria, the report includes only the open transactions selected by your filter.

    • If your filter selects based on either 1) only object-specific criteria (such as table name or column name), or 2) both unit of recovery and object-specific criteria, the report includes all open transactions on the current DB2 subsystem.

  • The activity counts in the report are based on the log records selected by your filter and the range of your log scan. Treat the counts as estimates of the activity associated with open transactions; do not use them as definitive data.

ROLLBACK

Provides information about all rollback actions performed over a specified period of time, including the frequency and number of associated transactions. Depending on how you order the report, it can present activity counts for each rolled-back transaction (in this context, transactions are considered to be the same as URIDs). The Rollback report contains only aborted transactions and cannot include committed transactions.

Use this report to determine the amount of activity associated with rollback actions or to pinpoint jobs or transactions that are frequently rolled back.

LOG BYTES

Provides information about the distribution of log records in the DB2 log. It focuses on the type, size and distribution of log records, and not on the actual data contained in the log records. The Log Bytes report displays the following information about log records:

  • Counts of many log record types

  • Total number of bytes used by the log records

  • Percentage of space the log records use

Use the Log Bytes report for tuning purposes, to help you reduce unnecessary overhead in the log.

When you generate a Log Bytes report, Log Master 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.

SUMMARY

Provides summaries of transaction count information (the number of insert, update, and delete actions). Use this report to determine how many transactions are processed by various sort keys, or to show how much SQL would be generated by a REDO or UNDO operation on a given table space.

REDO

This keyword is valid for only Audit, Detail, or Summary reports. Use this keyword only when you want a report to show you which log records would be selected for a REDO operation based on your current WHERE clause and Range definition. The keyword determines how Log Master selects the log records for the output report.

When REDO is not specified, Log Master selects log records for your report that are selected by your filter and Range definition (as it does when it generates MIGRATE or UNDO SQL statements.) This value is the default and is recommended for most uses.

When REDO is specified, Log Master selects log records for your report that are within the table spaces defined (directly or indirectly) by the filter, and within the period from the REDO recovery point to the current time. Then Log Master excludes the log records that are selected by your filter and Range definition. Log Master selects log records in this manner because it assumes that any REDO operation takes place after a recovery, and that you want to reapply all of the log records except the problem log records defined by your filter.

For more information about REDO operations, see REDO.

ALL ACTIVITY

Use the ALL ACTIVITY keyword to generate an Object Activity Summary report. This report presents database activity (insert, update, and delete actions) from all transactions, including transactions that relate to DB2 objects that are no longer in the DB2 catalog, and transactions that are not complete within your scan range (not yet committed or aborted). With few exceptions, this version of the report presents information from all selected data management (DM) records in the DB2 log.

You cannot generate an Object Activity Summary report if Log Master is reading log records from a logical log file previously created by Log Master or an application program, or if your filter selects log records based on a column’s value (for example, WHERE TABLE01.QUANTITY > 500). For more information, see the Object Activity Summary report in the Log Master for DB2 documentation .

FORMAT

Use the FORMAT keyword to generate Summary report output that includes only data (no report headings, column headings, or blank lines). Log Master provides two different formats, both of which use character representation for all data to make it easy to move data to other platforms:

  • Specify CSV to generate data in the Comma Separated Value (CSV) format. Individual items of data are separated by commas.

  • Specify SDF to generate data in the Standard Definition Format (SDF) format. Individual columns of data are filled with spaces so that each column has the same position and length on all lines of the output.

For either format, ensure that the logical record length (LRECL) of your report output file is long enough to contain the longest line in the report. For examples of report output in the CSV or SDF formats, see the Log Master for DB2 documentation .

INCLUDE TOTALUse the INCLUDE TOTAL keyword to include subtotal and report total lines in the output. This keyword is valid only when the format of report is CSV or SDF. By default, subtotal and report total lines are not included.
STATUS CHANGES

(PTF BQU1148 applied)

Provides information about database exception (DBE) records that are logged by DB2 utilities, including MODIFY and REPAIR. These records reflect changes to table space status, data pages, deleted rows, DBD repairs, and versioning. This information can be crucial for recovery purposes because it includes data changes and other actions that cause spaces and indexes to go in and out of restricted status.

Consider the following when you define a Status Changes report:

  • ORDER BY definition provides the STATUS and UTILITY keywords to conveniently sort a Status Changes report.

  • When Log Master processes a DBE record, some of the report dictionary items might not be accurately populated if the Begin UR record is logged before the start of the Log Master scan range. In this case, the following will be reported as UNKNOWN:

    • AUTH ID

    • CONNECTION ID

    • CONNECTION TYPE

    • CORRELATION ID

    • DATA SHARING MEMBER NAME

    • DATA SHARING MEMBER ID

    • PLAN NAME

    • SSID

ORDER BY definition

By default, Log Master sorts each report according to a set of sort criteria. To change the sort order of a report, use the ORDER BY definition. For more information about changing the order, see ORDER BY definition.

DATASET Output definition

Directs Log Master to place the generated report in a data set. When you specify the DATASET keyword, you also must specify the data set name and disposition. For more information about specifying a location for the data set, see LOGSCAN output definition.

SYSOUT SYSOUT definition

Directs Log Master to send the generated report to the Job Entry System (JES) spool queue (known as SYSOUT). For more information about specifying JES SYSOUT parameters, see SYSOUT definition.

DDNAME

Directs Log Master to place the generated report in a DD name. When you specify the DDNAME keyword, you also must specify ddname.

ddnameSpecifies the eight-byte-maximum DD name in which Log Master places the generated report. You can specify a partitioned data set, a sequential data set, or a generation data group (GDG). If you do not specify ddname, Log Master generates SYSOUT output.
INCLUDE ROLLBACK

Determines whether the output report includes information from compensated log records selected by your filter. Use a 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.' DB2 reverses changes in response to several situations, including ROLLBACK statements, ROLLBACK TO SAVEPOINT statements, or some negative SQL codes.

  • Noncompensated log records reflect original changes that DB2 does not reverse

  • Compensated log records reflect original changes (that are subsequently reversed)

  • Compensation log records reflect actions taken by DB2 to reverse the original changes

The Include Rollback field affects how Log Master processes compensated log records, not compensation log records.

Depending on the value of this keyword, Log Master includes or excludes compensated log records.

NODirects Log Master to exclude compensated log records. The output report reflects only noncompensated log records (log records of database changes that are not reversed). Use this value for normal processing.
YESDirects Log Master to include compensated log records. The output report reflects both compensated log records and noncompensated log records.
ONLYDirects Log Master to include only compensated log records. The output report reflects compensated log records, but does not reflect noncompensated log records.

Warning

Exercise caution before specifying a value of YES or ONLY. In most circumstances, when you include compensated log records, your report includes data that has not been applied to the original DB2 database. If you take actions or apply changes to your database based on a Log Master report that reflects compensated log records, you can corrupt the data in your database.

The following table shows the default values of the INCLUDE ROLLBACK keyword and how it interacts with the different report types.

INCLUDE ROLLBACK values by report type

Report type

INCLUDE ROLLBACK valid?

INCLUDE ROLLBACK default value

Audit

Yes

NO

Backout Integrity

No (includes only committed transactions)

NO

CommandNoNot applicable

Column Update Frequency

Yes

NO

Commit

No (includes only committed transactions)

NO

Catalog ActivityNoNot applicable

Data Capture Analysis

Yes

NO

Detail

Yes

NO

Image Copy

Yes

YES

Object Activity Summary

No (includes all transactions, regardless of presence of compensated log records)

YES

Open Transaction

No (includes only open transactions, regardless of presence of compensated log records)

Not applicable

Quiet Point

No (does not include database transactions)

Not applicable

Rollback

No (includes only aborted transactions)

ONLY

(PTF BQU1148 applied) Status ChangesNoNot applicable

Summary

Yes

NO

Column include/exclude

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

INCLUDE LOBS

(PTF BQU1148 applied)

Determines whether Log Master includes the data from large object (LOB) columns in the output Report file. When the value of this keyword is YES, Log Master writes temporary LOB VSAM files during processing, but does not save 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.

For more information about how Log Master processes LOB columns and data, see the Log Master for DB2 documentation .

YESDirects Log Master to include LOB data in the output Report file.
NODirects Log Master to avoid including LOB data in the output Report file.
INLINEThis is the default value. Directs Log Master to include only inline LOB data in the output Report file, ignoring the auxiliary LOB tables.

If you do not specify INCLUDE LOBS, Log Master considers the default value, INLINE, and includes INLINE LOB data. If you specify INCLUDE LOBS without specifying YES, NO, or INLINE, Log Master includes LOB data.

When LOB data is not found, or the value is a NULL or EMPTY string, the Report displays the following text for the LOB data:

TextDescription
*NULL*Null data or empty string
*LOB NOT AVAILABLE*LOB data is not available
*LOB NOT INCLUDED*LOB data is not available for INCLUDE LOB INLINE
*LOB LOGGING OFF*LOB logging is turned off
INCLUDE XML

(PTF BQU1148 applied)

Determines whether Log Master includes the data from XML columns in the output Report file. When the value of this keyword is YES, Log Master writes temporary XML VSAM files during processing, but does not save 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.

YESDirects Log Master to include XML data in the output Report file.
NODirects Log Master to avoid including XML data in the output Report file.

If you do not specify INCLUDE XML, Log Master considers the default value, NO, and does not include XML data. If you specify INCLUDE XML without specifying YES or NO, Log Master includes XML data.

When XML data is not found, or the value is a NULL or EMPTY string, the Report displays the following text for the XML data:

TextDescription
*NULL*Null data or empty string
*XML NOT AVAILABLE*XML data is not available


In this section:

Related topic



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

Comments