Benefits
-
Let DBAs quickly and easily identify, isolate and restore unwanted changes
-
Help auditors locate updates to sensitive data
-
Provide information about those updates, including who made them and when
Features
- Identifies log records by various attributes as transaction, user, group, job
-
Presents DB2 log data decompressed
- Prints DB2 log data and reports
-
Generates Undo and Redo SQL
|
What users say about ULT:
... boosts performance and lowers costs ...
... it is easier to handle and executes faster ...
... it saved us money and time ...
... getting updates made to certain tables requires to submit a single job.
|
ULT for DB2 on z/OS is a program that analyzes DB2 Archived Logs. It
was created in order to assist in auditing changes and in the
cumbersome task of backing out erroneous updates. ULT reports data
modifications to a set of specified tables within a given period of
time. Output is provided as binary before and after images of changed
rows.
DB2's
logging facility is optimized for recovery purposes, but it’s condensed
record layout is difficult to analyze and to map. A DSN1LOGP report
demonstrates this problem; the data printed does not contain complete
records, some columns are in DB2 internal format, sometimes one SQL
statement causes up to three DB2 log records, and even worse, the data
may be compressed, and therefore unreadable without additional
programming effort. It is obviously a real challenge to isolate,
reconstruct and output comprehensive data changes from DB2's Archived
Logs.
It is also rather difficult to identify log records for
specific transactions, e.g. for all transactions of a user, a group of
users or for a group of jobs.
ULT makes it easy to utilize DB2
log data for auditing or correction purposes. The user simply specifies
what is be extracted in terms of
-
the table/s to be monitored,
- start and end time of the log analysis,
- scope rules i.e. selection criteria for the 'units of recovery' to be considered.
ULT table selection allows DB2 LIKE patterns:
DTL.DTLTAB
DTL.%ABC
DTL%.MONTH__
The first line selects a specific table DTLTAB with creator DTL. The second line selects all tables of creator DTL, with names ending with ABC. The third line selects all tables having names beginning with MONTH and with two additional characters, whose creator begin with the letters DTL.
The following attributes may be used as scope rules:
USER DB2 authorization ID,
[USER = HUGO, USER = SYS*]
CORRID DB2 Correlation ID,
PLAN DB2 Plan name,
[PLAN = MYAPP]
CONNID DB2 Connection ID
CONNTYPE DB2 Connection Type.
Based
on user specifications ULT provides clear and easily understandable log
data records for the specified transaction/s and time period.
Workflow
A
ULT execution cycle consists of three steps, which may be executed
either as separate jobs or combined within one job: The first step
extracts the table definitions of the tables to be monitored from DB2
Catalog, the second step extracts the relevant records from DB2's log
datasets, and the third step formats the extracted log records.
Data Capture Changes Option
In
order to save resources and to accelerate the monitor process tables to
be monitored by ULT should be defined with the 'Data Capture Changes
Option' (DCC). DCC is either invoked by the DATA CAPTURE CHANGES clause
of CREATE TABLE or for existing tables by an ALTER TABLE ... DATA
CAPTURE CHANGES. When tables are defined with the DCC, the entire
'before image' of an updated row is captured on the log. This
additional information can represent an increase in log data compared
to tables that are not defined with the DATA CAPTURE CHANGES option.
However, for tables which don't have DCC switched on ULT calculates the
impact of DCC, i.e. reports the increase in log data that would occur
if DATA CAPTURE CHANGES is invoked. Usually the increase is around or
below 1%.
Compressed Tablespaces
ULT
fully supports compressed tablespaces and is able to expand compressed
records. For REORGs of compressed tablespaces the KEEPDICTIONARY clause
should be specified. This enables ULT to extract and report data
changes that took place before the REORG as well as after it. ULT
receives the compression dictionary from the tablespace partitions,
hence it always works with the last or most recent version of the
dictionary. Hence REORGs executed within ULT monitor period which do
not keep the dictionary can cause unpredictable results.
ULT Output
- ULT reports who changed which record at what time.
-
ULT
outputs 'undo' and 'redo' data which is applicable as input for data
correction after execution of faulty application programs.
Output is obtained in the following formats:
As
sequential dataset, variable length record format, all fields
converted, easy to use for COBOL or other application programs.
As
Sequential dataset, fixed length record format, SQL text, all fields
converted to printable characters, the text is ready to use for dynamic
SQL processors (SPUFI, DSNTEP2).
|
ULT for DB2 z/OS
|
