Db2 Tools for z/OS

 View Only

Log Analysis Tool - Automatic table versioning and dropped column support (PH20156)

By Robert Bersano posted Tue January 28, 2020 09:36 AM


This new feature will allow automated table versioning support in IBM Db2 Log Analysis Tool for z/OS. 

Current situation

When IBM Db2 Log Analysis Tool for z/OS processes log records, it must know the column definitions and the column order to be able to reconstruct the contents of each column. If a log record contains a format that is not the same as the current column definition, then the definition has to be supplied somehow. Log Analysis Tool will attempt to obtain that information from an image copy or from the table space automatically. If that information cannot be found, then Log Analysis Tool stops and issues "ALAA486E -Versioning data not available for table: DBID, PSID, OBID and Version Number" and the information will have to be entered manually.

Versioning information would only be needed if processing log records from before the REORG that removed the SYSIBM.SYSOBD records while processing in log backward mode. If processing is in log forward mode and before the REORG, the SYSOBD records would be in the current image copy; if not, the customer must enter the information manually.

If the customer did an ALTER immediately followed by a REORG and IC, then wanted to process the records before the ALTER/REORG, the SYSOBD records would not be in the image copy because the SYSOBD records are not written to the table space until after the first insert or update occurs after the ALTER.

Db2 V11 introduced the concept of dropping a column. Though the net result of dropping a column is very similar to table versioning, it requires some unique information. Db2 does not provide any trail of a dropped column action, so it must be manually entered in the TVFILE if any job run incurs table activity that spans the old and new formats of the table.


Table versioning and dropped columns both alter a table's mapping based on a point in time. A TVFILE is supplied to help support these concepts while Log Analysis Tool runs.

This file is the repository for table versioning information. The TVFILE is not deleted or overwritten, as it may be needed in multiple runs and quite possibly multiple jobs.

Any entry can be either a table versioning entry, or a dropped column entry.

The file format is fixed 80-byte record:

  • Common header:
    • DBID – hexadecimal 2-byte value of the DBID for the table;
    • PSID – hexadecimal 2-byte value of the PSID for the table;
    • OBID – hexadecimal 2-byte value of the OBID for the table;
    • VERSION – hexadecimal 1-byte value of the version number. Not used if entry is DROP COLUMN type).
  • If table version record (not DROP COLUMN):
    • COLNUM – hexadecimal 2-byte value of the number of the modified column. If this field = X'0000', it is a DROP COLUMN record and must be mapped using that format below;
    • COLTYPE – character 8-byte value of the column type;
    • COLLEN – hexadecimal 2-byte value of the column length;
    • COLSCAL – hexadecimal 2-byte value of the column scale, zero if not a decimal field, a Db2 V10 timestamp field, or a timestamp with time zone field.
  • If DROP COLUMN record (not table version):
    • DROPFLG – hexadecimal 2-byte value = X'0000';
    • DROPLP – hexadecimal 10-byte value of the SYSIBM.SYSCOPY logpoint when the DROP COLUMN took effect;
    • COLNUM – hexadecimal 2-byte value of the number of the dropped column;
    • COLTYPE – character 8-byte value of the column type;
    • COLLEN – hexadecimal 2-byte value of the column length;
    • COLSCAL – hexadecimal 2-byte value of the column scale, zero if not a decimal field, a Db2 V10 timestamp field, or a timestamp with time zone field;
    • COLNULL – character 1-byte value of Y (yes) or N (no) if nullable.

The remainder of record bytes are reserved. 


The purpose of this enhancement is the automation of adding entries to the TVFILE, which will implement the self-regulating table versioning support in the IBM Db2 Log Analysis Tool for z/OS.

A new "Track Schema Changes" utility will be added to IBM Db2 Log Analysis Tool. It will allow to generate a job that enables automatic table versioning support and analyzes catalog tables (SYSIBM.SYSTABLES and SYSIBM.SYSCOLUMNS) activity to detect table versioning during the specified period of time.

This type of job can be run whenever entries need to be added to the TVFILE or can be set up in the continuous mode to run on a regular basis.

The TVFILE can be shared between jobs or there can be a separate TVFILE per job depending on the customer's needs. 

User Interface

A new "Track schema changes" panel was added in the IBM Db2 Log Analysis Tool to enable automatic table versioning support. 

To invoke this panel, select option 7 to go to Utilities menu.


The "Utilities" menu option was also added to the Quick Start menu:


Then select option 3 for "Track Schema Changes" panel.


The "Track Schema Changes" panel is shown.

The panel has fields, that should be familiar from the "Generate Database Activity Report (General)" panel:

*DB2 subsystem name-

Enter the four-character subsystem identifier for the DB2 subsystem. DB2 log activity will be analyzed using this subsystem.


Enter either an 'E' or an 'S'. 'E' indicates that the temporary JCL created as a result of the data entered on the panel will be displayed in edit mode. 'S' indicates the temporary JCL will be submitted automatically.

Job Identifier-

This optional field is an 8-character maximum field that can be used to identify datasets associated with a given run of the created JCL. It will be used in the formation of the dataset names used in the JCL. For example, if set to TEST01, the REFILE will be named USERID.REFILE.TEST01.

Log Range fields:

These fields provide the range of log activity to be read. The ranges can be based on date/time, RBAs, or LRSNs, and are all mutually exclusive. At least one value must be provided, and the other left blank.

Start Date/Start Time/End Date/End Time-

Enter these fields in the format that is indicated on the panel. The DB2 logs will be searched for any activity found between these date/time values. The default formats are displayed to help you enter the correct date or time format; internally, they are considered equivalent to spaces. Therefore, they do not need to be blanked out if you specify RBA or LRSN ranges. A relative End Date and End Time can be requested by entering a plus sign or a minus sign and three digits in the End Date field and blanking out the End Time field. The three-digit amount field represents the number of minutes to be used in the relative End Date/Time calculation. If the plus sign is used, then the amount will be added to the Start Date/Time to obtain the End Date/Time. If the minus sign is used then, the amount will be subtracted from the current date and time to obtain the End Date/Time. The three-digit amount must be greater than or equal to 000 and less than or equal to 1440 (which is 24 hours).

Start/End RBA (URID)-

Enter a set of starting and ending RBA values. These values must be entered in full hexadecimal display format with a length 20, such as 000000000001ACCDEF02. If RBAs are desired, the date/time and LRSN log range fields must be blank. RBAs are relative byte addresses of log records. If you wish to filter on a single URID, the start RBA can be set equal to the URID, and the end RBA left as spaces. Only that specific URID will be reported on. In all other cases, the start and end RBA values are intended as a range of RBAs to process.

Start/End LRSNs-

Enter a set of starting and ending LRSN values. These values must be entered in full hexadecimal display format with a length of  20, such as 00B877232DEDD0000000. If RBAs are desired, the date/time and RBA log range fields must be blank. LRSNs are log recording timestamps associated with log records.

Continuous mode file-

Enter the name of a previously created continuous mode file if you want to generate JCL to run in continuous mode. The file MUST exist; use the 'Create continuous mode dataset' option on the 'Defaults' panel if you have not already created this file and place the name of that created file here. To run in continuous mode, typically you would generate the JCL here, and place this JCL into a job scheduler for daily runs. If left as spaces, you will create JCL that will not run in continuous mode. See the tutorial for more details on running in continuous mode.

*Override GMT offset-

This field allows the system defined GMT offset to be overridden. Set the field to 'Y' to override the GMT offset. An 'N' in this field will cause the product to continue using the system defined GMT offset. When choosing to override the GMT offset, be sure to enter the proper GMT offset to use.

with this GMT offset-

This field defines the GMT offset to use when overriding the system defined GMT offset. Specify the value with a leading plus sign to indicate GMT plus some offset or with a leading minus sign to indicate GMT minus some offset. The rest of the field should be specified in HH:MM format. For example, if the desired GMT offset is GMT -5 hours, the value '-05:00' should be specified. This field is used with the Override GMT offset field to convert the starting date and times to a different GMT value than the GMT value that would be used by system default. See the tutorial option "Running reports after a time change" for more information.