Db2 Archive Log Viewer for Db2 z/OS

Db2 is responsible for keeping a log for all data changes and a whole lot more. It is written away by Db2 as it does its normal work. Several large VSAM datasets are allocated for the active logs which, when full, get written off  to Archive Log 1 to N. 

The golden rule is time based and demands at least 24 hours of coverage on the Active Logs and at least 30 days on the Archive Logs.

The regular business requires that these Logs must be looked after and cared for.

The business of the Db2 Archive Log Viewer is different:

It reads as many Archive logs as you can give it and reports on the contents in absolute numbers and size.

The primary reason of this light-weight Db2 Archive Log Viewer was to give insight with a single and simple method and to get quantities and frequencies about the actual status of Db2 systems without touching the productive ones. The responsible Db2 Experts get a simple and transparent new view of what´s going on and who is responsible for the data written.

The output lists Log physical (e.g. 1M) and Log logical (e.g. 13M) read.Record and event Types reported are (in Count and Length):

Type 0002 Page Set Control Record
Type 0004 SYSCOPY utility
Type 0008 Non UR Control Record
Type 0010 System Event Record
Type 0020 UR Control Record
Type 0100 Checkpoint Record
Type 0200 UR-UNDO Record
Type 0400 UR-REDO Record
Type 0800 Archive quiesce Record
Type 1000 Resource MGR assigned 1
Type 2000 Resource MGR assigned 2
Type 4000 Resource MGR assigned 3
Type 8000 Resource MGR assigned 4
Unknown Record Type

The following examples show what some customers have seen in their tests:

1. Begin abort record81K
2. Update space map bits141K17M
3. Lock ptr if overflow updated3K469K
4. Type 2 index update6M1G

Ad 1

Aborts are never good. This is not just a negative SQLCODE. It is normally a program Abend and of course never good news to see.

Ad 2

This is a vast amount of spacemap updates. This raises the question „Are incremental Image Copies being taken?“ And the beneficial usage of TRACKMOD NO tablespace definition.

Ad 3

This is an awful lot of pointer records being created in a short period of time. This is hinting at the need to raise the FREEPAGE, PCTFREE and /or PCTFREE FOR UPDATE of the tablespaces.

Ad 4 

This is a massive amount of data being logged. These index updates must be investigated and checked for VARCHAR columns being PADDED/NOT PADDED or inappropriate usage of columns or even excess indexes. The spectre of INDEX Splits is also present. Here the SOFTWARE ENGINEERING/SEGUS Product Workload Expert (WLX) Use Case „Index Split“ would greatly help.