Partial Update Reconstruction

caladb220
By default, tables that are created in Db2 are defined with DATA CAPTURE NONE (DCN). When a table with the attribute DCN is updated, the whole image of the row does not register in the Db2 log for the update statement. Instead, the log record contains the before and after image of the row from only the first changed byte to the last changed byte. To create a complete image of the row for reports or to generate UNDO/REDO SQL to reverse the update to the table,
CA Log Analyzer™ for DB2 for z/OS
(CA Log Analyzer) needs the both the before and after values of the table column. To obtain this data, CA Log Analyzer extracts a full image of the row from either the table space VSAM data set or from any existing full and incremental image copies of the table space. The complete image of the update is then reconstructed by applying the update data from the log records over the extracted image of the full row. This process is Partial Update Reconstruction (PUR).
The source for the extracted full image of the row is obtained from either after the update or before the update. Either method works independent of the filters that are used for the report.
2
2
Partial Update Reconstruction from Table Space VSAMs or Image Copies Taken After the Update (PUR Source After)
Partial updates that are reconstructed with table space VSAMs or from an image copy which was taken after the update create a complete image of the row in the following steps:
  1. Reads the Db2 log starting from the update:
    • Reads up to the present-time to extract the information from the table space VSAM.
    • Or reads up to the START_RBA in SYSIBM.SYSCOPY of the first image copy of the table space that was registered after the update.
  2. Applies the UNDO and REDO data that is read from the log records to the full image of the row that is taken from the image copy or table space VSAM. This process constructs a full image of the update.
Partial Update Reconstruction from Image Copies Taken Before the Update (PUR Source Before)
Partial updates that are reconstructed from an image copy which was taken before the update create a complete image of the row in the following steps:
  1. Finds in SYSIBM.SYSCOPY the current full or incremental image copy of the table from before the update.
    If you are using SHRLEVEL CHANGE image copies, rows can be updated while the image copies are being made. If a row is updated after the copy process starts, but before the row is copied, then the image copy captures the updated row. If
    CA Log Analyzer™ for DB2 for z/OS
    reads an image copy under these conditions, the before image of the row is unobtainable for PUR because only the after image row is available. To skip the current image copy and use the next most recent image copy, specify SHRLEVEL S in the PLA parmlib.
  2. Reads the Db2 log starting from the START_RBA of the image copy up to the update to obtain a complete image of the row.
  3. Applies the REDO data that is read from the log records to the full image of the row that is taken from the image copy. This process constructs a full image of the update.
PUR Source Before was previously named Image Copy Merge Processing.
Partial Update Reconstruction Example
This example explains a scenario that invokes PUR when reporting on the table, OUR_TABLE.
Example: OUR_TABLE
ID (INT)
CHAR (20)
1
Hello to the world
The following diagram shows the information in the Db2 log for OUR_TABLE:
Partial Update Reconstruction Db2 Log Diagram
Db2 Log with an Image Copy, Table Space VSAM and an Update
OUR_TABLE has an update to a row that exists within the report log processing range:
UPDATE OUR_TABLE SET TEXT = "Hello world" WHERE ID = 1;
The update changes the value of the text column in OUR_TABLE from "Hello to the world" to "Hello world". Because the table has DCN, the log record for this update contains only partial information about the row. The value of the ID column and the first six characters of the text column are not recorded.
Update 1 log record
Update 1 log record
To have a full image of the updated row in the report or to generate the proper SQL UPDATE, the complete row must be retrieved from an existing image copy or the table space VSAM data set or the Db2 log. Relative to the log processing range, the complete row can be retrieved from the following sources:
  • The row can be retrieved from the table space VSAM data set and the Db2 log from after Update 1 happened. This method is PUR source After.
  • The row can be retrieved from Image Copy 1 and the Db2 log from before Update 1 happened. This method is PUR source Before.
Invoking Partial Update Reconstruction
CA Log Analyzer invokes PUR only in DML reports with the Image Copy Level of Detail and in DDL reports when an object that is in the report contains an incomplete row.
PUR Source After
For PUR from table space VSAM or image copies that are taken after the update to be invoked, the following conditions must be met:
  • A partial row exists in the log processing range.
  • There exists Db2 log data between the update and the source of the complete row.
  • The following activity does not exist for the table between the update and the source of the complete row:
    • REORG
    • LOAD REPLACE
    • RECOVER
    • MASS DELETE
Use PUR source After to complete partial rows for table spaces that are missing an image copy or when your log processing range is close to the present-time. Because fewer log records are processed when the log processing range for the report is nearer to the present-time stamp, the number of log data sets that are read is reduced. As a result, using PUR source After can lower the elapsed time for reports.
When the conditions to invoke PUR from a source after the update are not met, the update is reconstructed using a source from before the update instead.
PUR Source Before
For PUR from image copies that are taken before the update to be invoked, the following conditions must be met:
  • A partial row exists in the log processing range.
  • Information about a usable image copy for the object exists in SYSIBM.SYSCOPY.
  • The Db2 log from the image copy to the update has not been deleted.
  • Information about Db2 log is available in the BSDS.
    Running utility DSNJU003 to modify the BSDS can remove information about the Db2 log from the BSDS.
PUR Source Auto
The source auto option for PUR determines whether using a source from before or after the update produces a lower elapsed reporting time uses that method to compete the row.
Set where Partial update Reconstruction Obtains the Row Source Image
Set where PUR obtains the source image of the row in either the CA Log Analyzer profile or PLA parmlib.
Set the Partial Update Reconstruction Source Location in the Profile
The method that is specified in the profile is used whenever the control card for the report is generated through the CA Log Analyzer panels. For control cards created through the panels, the profile parameter supersedes the parmlib parameter.
Follow these steps:
  1. Type
    P
    (Profile) on the CA Log Analyzer Main Menu and press Enter.
    The Profile panel appears.
  2. Type
    2
    (Log Reporting Profile Variables) on the CA Log Analyzer Profile panel and press Enter.
    The Profile panel appears.
  3. Specify the parameter to use and press Enter or PF3 (End):
    The specified option is processed and saved for execution.
Set the Partial Update Reconstruction Source Location in the PLA Parmlib
The method that is specified in the PLA parmlib is used whenever the control card for the report was not generated through the CA Log Analyzer panels or when the control card omits the PURSRC control statement.
Follow these steps:
  1. Type
    EP
    (Edit Parmlib Members) on the
    CA Database Management Solutions for DB2 for z/OS
    Main Menu and press Enter.
    The Edit Parmlib members panel appears.
  2. Select the PLA parmlib member and press Enter.
    The Edit Parmlib member PLA panel appears.
  3. Specify the parameter to use and press Enter.
    The specified option is processed and saved for execution.