|
ULT4DB2 - The Db2® Log Analyzer and Tracker. Track, Audit, Analyze and Propagate DB2 Changes
ULT4DB2 ("ULT") is a powerful log analysis tool that simplifies how
you can view and extract changes to Db2 for z/OS® tables. Whether
you want to propagate data, undo faulty changes that were
made to a production table, or populate an auditing table
with a detailed list of previously made changes, ULT makes
it easy for you to get the most from your Db2 log. With
ULT4DB2 the desired data can be output in many preferred
formats usable by z/OS® and other platforms including CSV, and SQL on LUW, Oracle®, SQL Server®, etc. ULT4DB2 z/OS is a powerful program that analyzes DB2 Archived Log Datasets and extracts valuable information. The program outputs the desired data in any conceivable format for z/OS and other platforms. It provides SQL for UNDO and REDO operations against the original table or against another target table. Users can add a WHERE clause to filter the records from the DB2 Log before they are applied to the target tables. The outstanding properties of ULT are its speed, efficiency and its ease of use. The product has low CPU consumption and executes very, very fast. It is basically a batch job that requires a one-time setup and can then be scheduled in daily or hourly manner. The product has many capabilities. One capability is as a data propagator to forward updates from one DB2 to another DB2 or to other DBMS (such as Oracle, SQL Server, etc). It can also be used to analyze the DB2 log periodically for audit reasons, to answer questions like, "who changed what?". Most often ULT is needed for repairs. Today, some shops have several systems linked, and the records soon after updates may be forwarded to other systems, and again updated there. It is hardly possible to repair in this scenario via traditional recovery methods. Instead a specific repair of affected records is required. ULT answers the question what was changed and presents immediately the UNDO statements.
ULT supports Db2 Versions 9, 10, 11 and 12 in single or data sharing mode.
Who needs ULT?
Database administration, application development and maintenance programmers use ULT to repair the results of a faulty program execution, a wrongly scheduled job or user errors. Auditors and administrators deploy ULT to determine update sequences.
Data centers replace expensive data propagator tools by ULT, because it distinguishes itself by little CPU consumption and a moderate license fee. Database administrators use ULT to analyze update frequencies and idle times.
Benefits
-
Let DBAs quickly and easily identify, isolate and restore unwanted changes
-
Help auditors locate updates to sensitive data.
-
SQL and data analysis tool for compliance and audit. (Sarbanes Oxley, HIPAA, et.al.)
-
Provide information about those updates, including who made them and when
-
Favorable license terms when compared to BMC, CA, and other DB2 log analyzer tools.
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 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.
ULT4DB2 also identifies points of no activity also known as “Quiet Points.”
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).
Faster, Better, More Economical. See for yourself with a Free Demo or 30 day Trial.
Join us in an online Web Demo to view and discuss the product in a live session. If you like what hear and see, these products are available for a free trial period. Nothing proves the value of a product more than running it in house and seeing what it does for you.
|
