ESAI - Enterprise Systems Associates, Inc.

Main DB2 Product Page

Enterprise Systems Associates, Inc. DB2 Log Analysis.

ESAI DB2 ProductsESAI ServicesESAI TrialsAbout ESAIContact ESAIESAI Home Page

Using BMC Logmaster, CA Log Analyzer, or IBM Log Analyzer Tool? Try ULT with excellent price and performance.

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.

  • 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,
  • 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.
  • 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:

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,
CORRID DB2 Correlation ID,
PLAN DB2 Plan name,
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.”

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. Image

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.

Click to demo or trial BCV5

©Enterprise Systems Associates, Inc.
All trademarks including IBM, DB2, z/OS, IMS, VSAM are of IBM or their respective trademark holders.