Log Analysis / Auditing
ULT4DB2 is a powerful log inspection tool that aids in auditing data changes, forwarding changes, and backing out or correcting errant updates. It simplifies all conceivable tasks associated with the DB2 Log. Its comprehensive automation features and its user-friendly ISPF interface allow instant table audits as well as general reviews of certain time periods.
Most application systems are interlinked. Very often records are soon after updated and forwarded to other systems and there again modified. Hence, it is nearly impossible to apply traditional recovery methods based on imagecopies. In other words, recovering the affected table to a point-in-time before the erroneous changes is not feasible because downstream systems are not considered this way. Instead, specific repairs are required to correct the flawed change. ULT4DB2 can create SQL statements that revert a specific change which happened at a given point-in-time. Filtering by various criteria helps to control the repair process.
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.
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.
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.
Accidentally dropping an object in a production environment can have severe consequences and cause prolonged outages. Restoring an object to the state before the DROP is usually a tedious and error-prone process. It requires a lot of resources on the mainframe, and means a vast amount of extra work for DBAs.
ULT4DB2 has the ability to bring back objects that have been accidentally dropped. Based on the information from the DB2 log and existing image copy data sets, ULT4DB2 can re-create objects and fi ll them with the same data that they had right before the DROP command was
issued. You can scan an arbitrary log range for drop operations and select the objects that you want to undrop from an easy-to-use ISPF interface. The entire process is
automated and does not require any manual intervention. ULT4DB2 is able to undrop databases, table spaces, tables and indexes. All views, synonyms, database authorities, table authorities, foreign key constraints and check constraints are automatically recreated as well. ULT4DB2 can also generate BIND statements for packages and plans that have been invalidated due to the DROP.
ULT4DB2 can undrop objects without doing a point-in-time recovery of the DB2 catalog, which means that all other objects stay fully available during the undrop process.
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.
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).
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, data propagation and undrop tasks, along with automation functions for these tasks. Automation comprises retrieval of DB2 Catalog and log information, taking care of seamless log examination, generation of robust and restartable JCL, job order queueing and tracking, synchronization of source and target tables for data propagation, task history, and more. To set up a complete data propagation process not more than defi ning and submitting a simple job chain is required.
Ease of Use
ULT4DB2 automatically detects if it is executed against a DB2 data sharing group and what the relevant log data sets are, and extracts all required table structures from
the DB2 catalogs. ULT4DB2 also has an efficient and flexible restart mode to minimize overhead in error situations. The output for different tables, or different sets of tables, can be written using templates to separate data sets. Optionally, with ULT4DB2 you can execute SQL statements directly without storing them in data sets if the 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 unwieldily 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.