DML Activity Report Options

The DML Activity Report options let you specify the format of your output and what type of DB2 activity is included in the analysis.
caladb220
The DML Activity Report options let you specify the format of your output and what type of DB2 activity is included in the analysis.
 
Contents
 
 
 
3
 
2
 
 
Format Options
The DML activity format options let you specify the format of your output. These format options include the level of detail to provide, how to sort the output, and whether to use a customized report form. Careful consideration is required when selecting a level of detail to include in the report, SQL statements, or load file. Some selections are valid only with certain output formats. Your selection can also affect processing time and whether the generated output contains complete data.
 For more information about these options, press PF1 to see the online help.
How to Select the Level of Detail
When selecting the level of detail, consider how rows are written to the DB2 log.
Inserted and deleted rows are written to the DB2 log in their entirety. However, updated rows are typically logged only from the beginning of the change to the end of the change. Therefore the log contains only partial images of the before- and after-images of an updated row. Important information such as primary key data can be missing from these partial images. This missing data can prevent the generation of complete SQL statements or stop the display of a full before- or after-image of an updated row on a report.
The Key (K) and Image Copy (I) level of detail can provide some missing data, but they also incur more processing overhead. You can obtain the best results by having DB2 log a full image copy of all updated rows. This feature is controlled at the table level through the DATA CAPTURE CHANGES clause of the CREATE TABLE and ALTER TABLE statements.
 Image, log, and key data is formatted whenever possible. If the format cannot be determined (for example, due to unavailable table definitions or partial rows), the data appears in hexadecimal format.
Key Considerations
For tables possessing a unique key that is not updated, the Key option in the Level of Detail field lets you identify updated information without incurring the overhead required by DATA CAPTURE CHANGES or the Image Copy option. 
CA Log Analyzer™ for DB2 for z/OS
 retrieves the key field information from the tablespace using the RID stored in the log record. By default, the Key option uses the tablespace's primary key. However, you can specify a different key using the Specify KeyCols field on this panel. You may want to specify a different key when the table has no primary key; otherwise, the report will not include key data.
Key data specifications are independent of any log data filters that you define. 
CA Log Analyzer™ for DB2 for z/OS
 applies the filters to the log data and then retrieves the key data for the filtered log records.
Do not select the Key level of detail if the following conditions are present:
  • A row has been deleted and another row has been inserted at the same location.
  • The key value has been updated.
  • The table has variable length columns.
  • The data is compressed.
Image Copy Considerations
When the DB2 log contains partial information for updated rows, you can specify the Image Copy option in the Level of Detail field to retrieve the missing information from image copies. When you select Image Copy, 
CA Log Analyzer™ for DB2 for z/OS
 detects the current SITETYPE setting for the DB2 subsystem (LOCALSITE or RECOVERYSITE) and processes the appropriate image copy data sets for that setting.
Consider the following when selecting the Image Copy option:
  • This option can require significant processing time. For best results, always use the filtering options to limit the scope of data to be analyzed.
  • When creating load files for use with LOAD LOG RESUME, we recommend that you specify Image Copy to help ensure accuracy. The IBM LOAD utility loads and logs one page at a time. If you run the LOAD utility with the REPLACE option, the utility writes to clean pages so that all rows on the page are new and all INSERT statements are included in processing. However, if you run the utility with the RESUME option, the utility starts writing on a page that may already have data rows. These existing data rows should not be included in processing. If you specify Image Copy, 
    CA Log Analyzer™ for DB2 for z/OS
     can determine that these rows previously existed and exclude them from processing.
  • If the concurrent copy was taken at the DSNUM level 
    and 
    the DSNUM order was not ASCENDING, 
    CA Log Analyzer™ for DB2 for z/OS
     does not support concurrent image copies of partitioned tablespaces.
Miscellaneous Options
The miscellaneous options let you control whether certain types of DB2 activity are included in the analysis.
The following fields require special consideration:
Report Discards
The Report Discards field on the DML Activity Report Options panel lets you specify whether to generate a report showing the rows that were discarded during processing. 
CA Log Analyzer™ for DB2 for z/OS
 typically discards rows when it cannot reconstruct a complete image of a row. Incomplete image rows can occur under the following circumstances:
  • The selected level of detail is D (Detail) but the log does not contain complete before- and after-images of a row.
  • The selected level of detail is I (Image Copy) but image copies do not exist.
 
CA Log Analyzer™ for DB2 for z/OS
 discards incomplete rows to avoid writing bad records to the load file and to avoid generating incomplete REDO or UNDO SQL statements.
 The Report Discards field is valid only when generating load files and SQL statements.
Request a Subsequent Update Report
You can generate a Subsequent Update Report that identifies data changes that occurred after the requested log range. This report lets you evaluate the changes before proceeding with any UNDO activity.
 
Follow these steps:
 
  1. Type 
    in the Subsequent Opts field on the DML Activity Report Options panel and press Enter.
    The DML Subsequent Update Options panel appears.
  2. Specify the report options:
    • Type
       Y
       in the Generate Report field.
    • Type 
      (Summary) or 
      D
       (Detail) in the Level of Detail field. The summary report provides only the total number of changes that were made after the end of the requested log range. The detail report shows the before and after data for each row that was updated after the end of the requested log range.
    • Use the ENDING LOG SCAN POINT fields to specify where to end the scan. You can scan to the current end of the log, or to a certain point in the log.
  3. Press PF3 (End).
    The DML Activity Report Options panel reappears. You can now select the rest of your options.
Select Key Columns
You can select the table columns to be considered as key data. These key columns are included in your generated output. If you are generating REDO or UNDO SQL, the key columns control how 
CA Log Analyzer™ for DB2 for z/OS
 constructs search conditions for the UPDATE and DELETE SQL statements. If you are generating a report, the key columns provide more information in the analysis to help identify the changed rows. If you are sorting your output by key, the selected key columns are used to determine the sort order.
Key column definitions are valid only when generating SQL statements or a DML Activity Report with the Key level of detail. These definitions are also valid when sorting your output by key. When selecting key columns, you can use selection criteria to display a list of columns that meet your criteria. You can then select columns from the list.
The following mask characters are allowed for object selection:
  • Asterisk (*)–Includes all items.
  • Per cent sign (%)--Represents any string of zero or more characters in the item name.
  • Underscore (_)--Represents any single character.
 
Follow these steps:
 
  1. Type 
    Y
     in the Specify KeyCols field on the DML Activity Report Options panel and press Enter.
     The Key Column Selection panel appears.
  2. Complete one or more of the following fields:
    • Item Name
      Specifies a table name or selection criteria.
    • Creator
      Specifies a creator name or selection criteria.
    • Where
      Lets you define a selection query to refine your filter further.
    Press Enter.
    The panel displays the available table columns that match your selection criteria.
  3. Select key columns by entering the following values in the SEL field next to the table names:
    •  
      C (Column)
      Lets you select and order the columns to be used as key columns.
    •  
      I (Index)
      Lets you select key columns by index.
    •  
      S (Select All)
      Selects all columns in the tables. All column data is included in the report or SQL statements.
    Press Enter.
    If you typed C, the Column List panel appears. Go to Step 4.
    If you typed I, the Index List panel appears. Skip to Step 5.
    If you typed S, a message notifies you that the selected objects have been queued. Skip to Step 6.
  4. Type 
    S
     (Select) next to a column name. You can also enter a number next to the column to indicate the relative position of the column within the key. Press PF3 (End).
    The selected columns are added to your selection queue and the Key Column Selection panel reappears.
  5. Select the key columns on the Index List panel:
    1. Enter 
      one 
      of the following values next to an index name:
      •  
        C (Column)
        Displays a list of all columns in the index.
      •  
        S (Select All)
        Selects all columns in the index as key columns. All column data is included in the report or SQL statements.
      •  
        U (Unique)
        Selects all unique columns in the index as key columns. Any nonunique columns are ignored. This setting is the default.
      Press PF3 (End).
      If you typed C, the Column List panel appears. Go to Step 5b.
      If you typed S, all columns in the index are selected and the Key Column Selection panel reappears. Skip to Step 6.
      If you typed U, all unique columns are selected and the Key Column Selection panel reappears. Skip to Step 6.
    2. Type 
      S
       (Select) next to a column name. You can also enter a number next to the column to indicate the relative position of the column within the key. Press PF3 (End).
      The selected columns are added to your selection queue and the Index List panel reappears.
    3. Press PF3 (End).
      The Key Column Selection panel reappears. Go to Step 6.
  6. (Optional) Type 
    S
     (Shrink) on the command line and press Enter.
    The Selected Object Queue panel appears, showing the objects that are currently selected.
     You can alter or remove objects from the list that is shown in this panel. For more information, press PF1 to view the online help.
  7. Press PF3 (End).
    The Report Options panel reappears. You can now select the rest of your options.
Key Column Considerations
When you generate SQL statements, the key columns control how search conditions are constructed for the UPDATE and DELETE SQL statements. The search statements are generated as WHERE KEY1 = 
data
 AND KEY2= 
data
, with 
data
 being the key column information. If you do not define key columns, 
CA Log Analyzer™ for DB2 for z/OS
 uses the table's primary key columns to construct the search conditions for the SQL statements. If the table has no primary key columns, all columns are used.
When you generate a DML Activity Report using the Key level of detail, 
CA Log Analyzer™ for DB2 for z/OS
 retrieves the key columns from the VSAM file containing the table data. The key columns are retrieved only when a complete row image is not available in the log. If you do not define key columns, 
CA Log Analyzer™ for DB2 for z/OS
 retrieves the table's primary key columns from the VSAM file. If the table does not have a primary key, no column data is retrieved and only the row data found in the log is used.
 If you regularly generate SQL statements or key level reports against the same tables, consider enabling DATA CAPTURE CHANGES for those tables so that full row images are logged. DATA CAPTURE CHANGES is enabled through the ALTER TABLE or CREATE TABLE SQL statement when defining the table.
If you use the key columns option with log data filters, the log filters are applied to the log records, not to the key column data.
Key Size
Key size applies only when a key is used for key ordering (that is, when you use the Order Output By field on the DML Activity Options panel to sort your DML output by key or by key undo). The key size is not applicable when generating SQL statements or a DML Activity Report with the Key level of detail.
The number of columns that you can select for the key depends on the length of each column in bytes. The keys are stored in internal DB2 format. The total concatenated key cannot be more than 2,000 bytes long. If the key is too large, it is truncated. If a column exceeds the maximum bytes allowed, that entire column is removed from the key.
 For information about the internal length of each column data type, see the IBM 
DB2 SQL Reference 
guide.
Set the Load Options
The Set LOAD Options field on the DML Activity Report Options panel lets you specify the parameters for the load file. This option is valid only when you select L (Load) as the output format. When you select this option, you can specify the output options, URID options, and load utility table specifications.
 
Follow these steps:
 
  1. Type 
    Y
     in the Set LOAD Options field on the DML Activity Report Options panel and press Enter.
    The DML Load Format File Options panel appears.
  2. Specify the parameters for the load file:
    • The OUTPUT OPTIONS fields let you specify the format and number of load files to generate.
    • The URID DATA INCLUSION OPTIONS let you select which URID fields to include or exclude from the load file.
    • The LOAD UTILITY TABLE SPECIFICATIONS fields let you specify the source and target tables. The load data is extracted from the source table and applied to the target table.
  3. Press PF3 (End).
    The DML Activity Report Options panel reappears. You can now select the rest of your options.
Specify Related Updates Options
When generating REDO SQL or Log Apply statements, you can include system-initiated updates in the generated output. System-initiated updates occur as a result of referential integrity constraints and triggers. These updates can also consist of DB2 history table updates, which occur when a SYSTEM_TIME temporal table is processed. Including these updates is useful when you are updating tables with identical referential constraints and triggers, or when you process temporal tables.
 
Follow these steps:
 
  1. Type 
    Y
     (Yes) in the Related Updates field on the DML Activity Report Options panel and press Enter.
    The DML Related Updates Options panel appears.
  2. Specify whether to generate REDO SQL or Log Apply statements for each update type that is shown on this panel.
    • Specifying N (No) for Redo History Table Updates excludes 
      all 
      updates to that table, including updates that occurred before it was converted to a history table.
    • For more information about these fields, see the online help panels.
    Either type 
    SAVE 
    on the command line and press Enter, or press PF3 (End).
    • If you typed SAVE, the options are saved in your profile for later reuse.
    • If you pressed PF3 (End), the options are used only with the current report.
    The DML Activity Options panel reappears. You can now select the rest of your options.
Specify URID Header
 
When generating REDO or UNDO SQL statements, you can include a URID header. Include the URID header to see who issued DML statements and when the statements were issued. The URID header contains the following information:
  • URID
  • URID Status
  • LRSN
  • Primary Auth-ID
  • Connection-ID
  • Connection type
  • Member
  • Correlation-ID
  • Plan Name
  • Timestamp
To specify the URID header, type 
Y
 (Yes) in the URID Header field on the DML Activity Report Options panel and press enter. The URID header prints for the first SQL statement generated. The header precedes each SQL statement if the URID is different from the previous SQL statement generated. The header outputs in the following format:
 
Example: URID Header
 
-------------------------------------------------------------------------------- -- URID: 0000000006DF20761935 URID Status : Commited -- LRSN: 00D1EF31660011000000 Primary Auth-ID: TEST101 -- Connection-ID : SERVER Connection type: Distributed -- Member : 0 Correlation-id : db2jcc_a -- Plan name : DISTSERV Timestamp : 2017-01-12-05.01.11.515219 --------------------------------------------------------------------------------
Application Commits
When generating REDO or UNDO SQL statements, you can choose to use either of the following options to determine the placement of the commits in your job output:
  •  
    Y
    Select 
    to use the application commit frequency to place commits in the output after each unit of recovery. This option requires that you order output by unit of recovery.
  •  
    N
    Select 
    N  
    to place commits in the output as often as you specified in the 
    Log Reporting Profile Variables
     panel.