Chapter 7: Generating DML Activity Reports
This section contains the following topics:
Generate a DML Activity Report (see page 119) Common DML Activity Report Sections (see page 121) DML Activity Summary Report Description (see page 122) DML Activity Totals Report Description (see page 122) DML Activity Detail Report Description (see page 123)
DML Activity Image Copy Detail Report Description (see page 124) DML Activity Key Detail Report Description (see page 124) DML Activity Update Report Description (see page 125)
DML Activity Subsequent Update Report Description (see page 125)
Generate a DML Activity Report
The DML Activity report provides information about the inserts, updates, and deletes that occurred on your DB2 subsystem during a specified range. You control the level of detail that is provided in the report:
■ The summary report summarizes the number of changes that occurred to each table.
■ The totals report provides the total number of changes that occurred to all of the specified tables.
■ The detail and image copy reports show a before-and-after row image for each change.
Note: These two reports differ only in the data that is used to construct the row images. The detail report uses the DB2 logs to construct the row images. The image copy report uses both DB2 logs and image copies to construct the images.
■ The key detail report is identical to the detail report, except it also shows the key columns for the tables.
■ The update report is a streamlined version of the key detail report that shows only the changed columns instead of showing the entire row.
■ The subsequent update report is generated together with one of the preceding reports. This report shows any table changes that occurred after the log range specified in the preceding report.
Generate a DML Activity Report
120 User Guide
Follow these steps:
1. Select 1 (Process Log) from the CA Log Analyzer Main Menu and press Enter.
The Report Specification panel appears.
2. Select DML Activity and press Enter.
The DML Activity Report Options panel appears.
3. Select the report options (see page 45) and press PF3 (End).
The Report Specification panel appears.
4. Press PF3 (End) again.
The Report Source Specifications panel appears.
5. Specify the log data to analyze by entering the following information:
■ Starting and ending parameters under LOG PROCESSING RANGE
■ The data sources to use under SOURCE OPTIONS Press PF3 (End).
The Output Specifications panel appears.
6. Define the specifications for the generated output (see page 38).
Press PF3 (End).
If you included LOB column data in your report options, a second Output Specifications panel appears. Go to Step 7.
If you excluded LOB column data, the Report Submission panel appears. Go to Step 8.
7. Enter a data set name for the LOB file.
Press PF3 (End).
The Report Submission panel appears.
8. Submit the job using one of the following options:
■ O (online mode) (see page 39)
■ B (batch mode) (see page 41)
Chapter 7: Generating DML Activity Reports 121
Common DML Activity Report Sections
All DML activity reports include a set of common sections. However, these sections appear only when you write the report to a data set or to SYSOUT. These sections do not appear when the report is generated online for display at your terminal.
Note: The sections that are specific to your selected report type (for example, summary or detailed) appear after the parmlib options. These report-specific sections always appear, regardless of where the report is written.
The following sections are included in all DML activity reports:
LOG PROCESSING RANGE
Identifies the log range that was processed to generate the report. You specified the log range on the Report Source Specifications panel.
REPORT OPTIONS
Identifies the report options (such as output format and level of detail) that you selected on the Report Options panel.
SOURCE OPTIONS
Identifies the DB2 log and table sources that were used to generate the report. You specified which sources to use on the Report Source Specifications panel.
LOG EXTRACT FILE INFORMATION
Identifies the log extract file and Batch Processor statements that were used to generate the report. You specified a destination for the extract and its control file on the Process Log - Output Specifications panel. These files are created whenever you use the BSDS or specific log data sets as the source of log data. You specified your batch execution parameters on the Batch JCL Specification panel.
This report section also provides the following information about the extract file:
PLA determined DB2 High log point
Identifies the highest log point that CA Log Analyzer has determined was written to the log when the extract file was generated.
PLR determined DB2 High log point
Identifies the highest log point the log reader has determined was written to the log when the extract file was generated.
High Processed DB2 log point
Identifies the log point of the highest UR that was encountered within the log processing range.
DML Activity Summary Report Description
122 User Guide
PARMLIB OPTIONS
Identifies the highlevel.CDBAPARM(PLA) options that were in effect when you generated the report.
INDEX
Appears at the end of the report. If multiple reports are generated, the index appears after the last report. The index identifies each report that has been generated, the subheadings for each report, and their corresponding page
numbers. For DML activity reports, the index also shows the specified sort order for each report.
DML Activity Summary Report Description
A DML Activity Summary report summarizes the number of updates, deletes, and inserts that occurred for the specified objects during a specified log range. The report is sorted by the value you specified in the Order Output By field on the Report Options field. For example, if you specify Order Output By => T (table name), the report groups the information by table.
This report provides the following information, in the order shown:
■ The common report sections (see page 121).
■ The activity summary, which shows the following information:
– The UR information for each set of object activity shown in the report.
– The number of updates, deletes, and inserts that occurred for each object within that UR.
– The number of updates, deletes, and inserts that were made to compensate for a previous update within the URID, or to maintain referential integrity.
■ The summary totals, showing the total number of updates, deletes, and inserts that occurred to all of the objects in the report.
DML Activity Totals Report Description
A DML Activity Totals report shows the total number of inserts, updates, and deletes that occurred during the specified log range.
This report provides the following information, in the order shown:
■ The common report sections (see page 121).
■ The activity totals, showing the total number of inserts, updates, and deletes that occurred to the specified tables during the specified log range.
Chapter 7: Generating DML Activity Reports 123 More information:
Specify the DML Activity Options (see page 45)
DML Activity Detail Report Description
A DML Activity Detail report shows a before and after-image for each update, delete, and insert that occurred during the specified log processing range.
Note: This report uses only log data to construct the before and after-images of the changed rows. The DML Activity Image Copy Detail report uses both log data and image copy data sets to construct these images.
This report provides the following information, in the order shown:
■ The common report sections (see page 121).
■ The summary report section, which shows the before and after-image for each update, delete and insert.
– If any log data is missing, CA Log Analyzer formats the available data and it represents the missing characters with asterisks.
– If CA Log Analyzer cannot determine the log data format, it displays the before and after-images in hexadecimal format. When possible, CA Log Analyzer also displays the hex data as printable EBCDIC characters.
The following conditions can inhibit the ability to format log data:
■ Table definition—When a table definition cannot be found for the DBID and OBID recorded in the log, the table name, creator, database, and tablespace fields remain blank.
■ Unformatted hex display—When nondisplayable hex data is found in a character type data column, the displayable data precedes the unformatted hex display.
■ Variable-length column—When a variable-length column appears before the last changed byte recorded in the log, the beginning offsets for columns cannot be determined. The length of the variable column is unknown.
■ Sign byte—When a sign byte for numeric data is missing from the log, the displayable portion of the data precedes the unformatted hex display.
■ Compressed log records—When the log contains compressed data and the compression dictionary has been rebuilt, the compression dictionary is unavailable.
– Changes that DB2 initiated to maintain referential integrity are identified with the comment "Due to RI constraints."
DML Activity Image Copy Detail Report Description
124 User Guide
– SQL that was generated through a trigger is identified with the comment
"Triggered SQL."
– Compensation log records that DB2 wrote to reverse changes that would cause error conditions are identified with the comment "Compensation
record-logged."
– Unqualified deletes cannot be displayed or reconstructed because the log records no longer exist. Instead, the report displays a message that unqualified deletes have been detected.
DML Activity Image Copy Detail Report Description
A DML Activity Image Copy Detail report displays the same information as a DML Activity Detail report. However, the Image Copy Detail report uses both log data and image copy data sets to construct a full before and after-image of each row. Selecting the Image Copy Detail report eliminates most of the hexadecimal format data that is shown on the DML Activity Detail report. A hexadecimal listing of the update appears only if an image copy is not available to reconstruct a row.
More information:
Specify the DML Activity Options (see page 45)
DML Activity Key Detail Report Description
A DML Activity Key Detail report is identical to the DML Activity Detail report except it also displays the key columns for the tables. Key columns are displayed only for updates, because DB2 does not typically record the entire row when logging an update. DB2 does, however, log the entire row for inserts and deletes, therefore the key column information is not displayed separately. If you want DB2 to log full row images for updates, enable DATA CAPTURE CHANGES for that table.
More information:
Specify the DML Activity Options (see page 45)
Chapter 7: Generating DML Activity Reports 125
DML Activity Update Report Description
A DML Activity Update Report is a streamlined version of a DML Activity Key Detail Report. Instead of showing the entire row, this report shows only key columns (if defined) and changed columns. As with the Key Detail Report, key columns are shown only for updates, and not for inserts or deletes. If the key columns are unchanged or the table is defined with DATA CAPTURE NONE, no key column information is logged or displayed. Also, this report does not include a report header section at each page break or a report index.
More information:
Specify the DML Activity Options (see page 45)
DML Activity Subsequent Update Report Description
A DML Activity Subsequent Update Report identifies the inserts, updates, and deletes that occurred after the requested log range. If you selected S (Summary) as the level of detail, the report shows only the number of table changes. If you selected D (Detail), the report shows the before and after data for each table change.
Note: This report is always produced together with another DML Activity Report. You generate this report by specifying Y (Yes) specified in the Subsequent Opts field on the DML Activity Report Options panel (see page 45).