ULT4DB2

DB2 Log Examination

Print

Log Analysis / Auditing
ULT4DB2 is a powerful log analysis tool that simplifies how you can extract and  view changes to DB2 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, ULT4DB2 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. The comprehensive automation features of ULT4DB2 and its friendly ISPF interface make the ULT4DB2 log analyzer applications very easy to implement.

Repair
Most application systems are  interlinked. Soon after updates are completed the records are forwarded to other systems and again updated. It is   nearly impossible to apply traditional recovery methods across these systems. Instead, specific repairs of the changed records are required to correct the flawed change. Recovering the affected table to a point-in-time before the update may not be practical because it would undo all subsequent changes as well. ULT4DB2 can create SQL  statements that revert a specific change which  happened at a given point-in-time. Filtering by various criteria helps you isolate the original operation that was executed against a single table or a set of tables.

Auditing
Businesses want to keep track of changes to sensitive information – who made a change to a table, when was it made, and what exactly was changed. The DB2 log contains all this data in assorted places. ULT4DB2 helps you to put the pieces together and populate your auditing  tables with the information you require. You can analyze all the changes over a given period of time and filter by user name, plan, column contents or other criteria. If you already have auditing tables in place, ULT4DB2 can vary its output to match your existing table structures.

more»

Auditing Read Access
ULT4DB2′s log analysis functions provide a broad range of efficient means for tracking data modifications within DB2 for z/OS. However, the DB2 log does not contain information about read access – although it may be very important, from an auditing point of view, to identify unauthorized read access to sensitive enterprise data.

For such purposes ULT4DB2 integrates and automates DB2 audit trace which is based on SMF event recording. ULT4DB2 facilitates table set up and selection for auditing, converts and reads relevant SMF data, handles trace activation and provides conditioned data in a set of well-designed auditing tables for easy evaluation. In this way ULT4DB2′s auditing function becomes a really complete solution.

Quiet Points
Quiesce points are good candidates for recovery processes. However, in busy environments it is normally not possible to create preventively quiesce points. Even an unsuccessful attempt to create a quiesce point will have an impact on the availability. Hence, consistent point-in-time recovery can become a challenge. With ULT4DB2  you can scan the log for so-called “quiet points” which  are points in time where there happened to be no  update activity for the set of table spaces in question, spontaneous quiet points so to say. You can use ULT4DB2 to scan for quiet points on unit of recovery (UR) or logical  unit of work (LUW) level, whichever is more appropriate for your needs. You can easily recover to a quiet point that ULT4DB2 detected and then reset the CHECK-pending status using the REPAIR utility.

Rollback Report
The ULT4DB2 rollback report lists all rollbacks that occurred within the observation period, sorted by number of UNDO  compensation records and lists the percentage of Undos.

Long Running Job Report
This report identifies potentially broken jobs and those transactions with low commit frequency. Filters are available for number of log records, execution time, number of DB2 checkpoints and number of updated rows.

Data Propagation
Keep tables synchronized with ULT’s data propagation feature. ULT4DB2 can directly execute the same INSERTs, UPDATES and DELETEs to different target tables. Alternatively, you can have ULT4DB2 generate SQL statements  for examination or later execution. If your target tables are  in a different database system or platform like Oracle, SQL  Server, or other DBMS, then you can change the syntax  of the generated statements to suit your needs. ULT4DB2  forwards DB2 data in an efficient way. Different from other propagation tools it does not increase the load on the source DB2 system as ULT4DB2 simply reads the log datasets. This results in significant savings in CPU time. Furthermore, the ULT runtime schedule can be controlled by  the user to run at non-peak times and avoid higher cost  levels (breach capping).

Automation
ULT4DB2 presents itself to the user as a friendly ISPF application with easily operated screens and broad user guiding features. The ISPF application enables the user to define repair, auditing, reporting, and data propagation tasks, along with automation functions for these tasks. Automation comprises retrieval of DB2 Catalog and log information, maintenance of database and application profiles, scope rules, generation of robust and restartable JCL, job order queueing and tracking, transparent creation of process-required DB2 tables and data sets, task history, and more. To set up a complete data  propagation process not more than defining and submitting two jobs is required.

Ease of Use
ULT4DB2 automatically detects if it is executed against a DB2 data sharing group, the relevant archived log datasets, and assembles the table structures from the DB2 catalogs. ULT4DB2 also has an efficient and  flexible restart mode should the target table become unexpectedly full. The output for different tables, or different sets of tables, can be written using templates to separate data sets. With ULT4DB2 you can optionally generate SQL statements ready for execution if your  target database system is DB2 for z/OS. If you use DB2 tables to store legacy data then you will sometimes want to treat this data as binary even if the DB2 catalog states otherwise. ULT4DB2 allows you to override the catalog information on a per-column-basis enabling you to manage this unwieldy data. A change to your source tables may “trigger” additional changes that need to be taken into consideration during data propagation. Where these triggers do not exist in the target environment, ULT4DB2 can generate SQL statements to produce the same changes that the original triggers did.

What Challenges Does ULT4DB2 Meet?
In most enterprises you are dealing with multi-platforms and applications are interlinked. Data is changed  and shortly afterwards delivered to other business  units or partners. Incorrect changes are often first detected  when they are already in use at other places. Hence, standard recovery based on image-copies may be  excluded from the process and instead specific repairs to  the data are required. ULT4DB2 quickly isolates the undesired changes to database tables and provides easy  to use UNDO SQL statements to revoke wrong updates. It also provides REDO statements to apply a set of changes to other tables. It provides the ability to quickly recover  mission-critical DB2 data. This capability to prevent prolonged unavailability of vital applications can translate into a large sum of money for the different businesses involved.

ULT4DB2 also makes data propagation affordable. The immense costs in CPU consumption on the production DB2 that other propagator programs require are significantly decreased by ULT.

Who Utilizes ULT4DB2?
Database administration, application development and  maintenance programmers use ULT4DB2 to repair the  result of an incorrect program execution, a wrongly  scheduled job or some user error. Auditors and  administrators deploy ULT4DB2 to determine update sequences. Data centers replace expensive data  propagator tools by ULT4DB2 because ULT offers reduced CPU consumption and reasonable license conditions.  Database administrators use ULT4DB2 to analyze update frequencies and idle times. ULT4DB2 supports DB2  Versions 8, 9, and 10 in single and data-sharing modes.