Product Spotlight: AUDITRE

by Joseph Brady, Manager of Marketing and Technical Documentation at Treehouse Software, Inc.

AUDITRE is Treehouse Software‘s generalized auditing facility for Adabas, applicable to a wide variety of use cases. Summary reports show the number of adds, deletes, and updates made to specified files and fields, while detail reports can track specific user activity and updating of individual fields and files. Data may be extracted to output datasets for use with reporting, analysis, and other software. As a batch facility processing the Adabas PLOG, AUDITRE does not require Adabas to be active in order for reports and extracts to be run.

The Adabas Auditing Challenge

Compliance testing presents the biggest challenge to the organization’s Auditors. Compliance testing requires the Auditor to learn if applications use the proper update procedure to change Adabas data. Compliance testing requires a record of exactly what changes an application program made to the database and the order in which the changes took place. Adabas provides no automatic method to generate audit data, making compliance testing difficult. This challenge leads some sites to invest substantial resources in the development of Embedded Audit Routines (EARs).

The Latest…

The new version of AUDITRE (v5.2.1) runs on z/OS and VSE, and can process Adabas protection log (PLOG) data that contains spanned records. What is a spanned record? – Adabas v8 allows a compressed record that exceeds one physical Data Storage Block by splitting the logical record into a number of physical record segments, each part fitting into a single Data Storage (DS) block. A spanned record is comprised of one primary record and one to four secondary records.

AUDITRE’s Function

As illustrated in the following diagram, a batch job or an on-line user executes an Adabas transaction. Adabas updates the physical database components and writes information about the transaction to the Adabas Protection Log.

The information on the Protection Log is voluminous and is not in an easily readable form. AUDITRE processes the Protection Log data.

Taking direction from the Auditor through AUDITRE parameter cards, and input from the Protection Log and the Field Definition Table, AUDITRE makes one pass over the audit data. In this one pass, AUDITRE determines the number and kinds of transactions (Adds, Updates, or Deletes) made and produces multiple reports or outputs the results to a sequential dataset(s) for later processing.

AUDITRE Provides the Best Auditing Solution

AUDITRE requires no additional support systems such as extra programs, “audit exit routines,” or specialized files. Audit reports can be generated on other CPUs at remote sites for security, if desired.

Further, AUDITRE requires minimal installation and training time for the DBA and other users. Creation of new reports requires coding only a few simple parameter statements.

AUDITRE produces clear, organized reports of database modification activity including only the information desired. AUDITRE can base reports on files, fields, sets of fields, and logically related updated files. Reports contain only the files and fields desired. One pass of the Protection Log allows AUDITRE to generate multiple reports simultaneously.

For maximum long-term benefit, selected data or reports can be archived to tape or disk for processing later. This allows audits to occur “after the fact” for certain critical systems if Auditors later discover a problem.

AUDITRE Makes Adabas Auditing Easier

Consider a Protection Log containing over one million records. Adabas stored these records in a compressed, non-standard form, which makes them difficult to read. The Log contains all updates for all files for all fields (entire records) whether all fields changed or not. Records on the log are ordered chronologically, and are difficult to sort because Adabas stored them in the non-standard form.

Various Adabas utilities display Before and/or After Images of updated records as one large field in character and in hex. However, determining which field(s) changed from such a “dump” is difficult, especially when updates cause different-sized values, or when fields change to/from null values.

Which Organizations Can Benefit from AUDITRE?

AUDITRE can benefit many different types of organizations. For example in the petroleum industry, AUDITRE can verify changes to ownership interests in oil leases. Magazine publishers can use AUDITRE to monitor changes to subscribers’ EXPIRATION-DATEs. Stock brokerages can use AUDITRE to verify that a stock transaction took place as ordered by the client. Colleges and universities can track changes to alumni addresses for fund-raising purposes. Any organization needing a report of changes to Adabas data can find a good use for AUDITRE.

AUDITRE’s archiving capability is useful to many departments in the same organization. The Payroll Department may discover unauthorized salary increases. Meanwhile, the Marketing Department might use summary reports for several months of Protection Log data to determine how many new customers were gained during the year by tracking adds and deletes to the CUSTOMER database. The Purchasing Department could examine RECEIVING data for changes to expected RECEIVING-DATEs, etc., to determine if any suppliers frequently postpone merchandise delivery, causing supply shortages for the organization.

There are many other uses for AUDITRE. If a question can be answered by examining changes to the database, or a problem investigated by reporting on archived update data, AUDITRE can help.

For more comprehensive information on AUDITRE, a Product Overview is available for download from the Treehouse Software website.

Reminder that CA PLEU Customers Can easily Transition to AUDITRE!

If your company uses CA PLEU and/or CA APAS/Insight, you know that support for these products is no longer provided by CA. Treehouse Software extends a special offer to CA APAS/Insight and CA PLEU customers to adopt comparable Treehouse Software solutions, and enjoy the assurance of our commitment to supporting and updating AUDITRE (as well as TRIM, NatCDCSP, NatQuery, and our other solutions that complement ADABAS and NATURAL).

AUDITRE has features that specifically aid in a customer’s transition from CA PLEU, including:

• Previous releases of AUDITRE limited the total size of the Protection Log Compressed Data Record Fields to be printed to 32K. AUDITRE now runs above the line, removing this limit.
• AUDITRE was updated to allow sites to route detail and summary statistics to a separate file.
• New analysis parameters allow specification of a begin point (STARTDATE/STARTTIME), which adds additional flexibility to the PLOG extract process.
• Additionally, we recently assisted a longtime CA PLEU site in converting their existing CA PLEU extract to an AUDITRE extract, which provided identical results!

Treehouse Software representatives are ready to help you implement our solutions to address your key requirements, and at specially discounted pricing.

Not currently using CA APAS/Insight or CA PLEU? You’re in luck, because we’ll extend our same special pricing offers to you as well!

---

Visit the Treehouse Software website, or contact us to discuss your needs.

Treehouse Software is Setting Sights on Many New Data Replication Projects

by Chris Rudolph, Senior Technical Representative for Treehouse Software and Joseph Brady, Marketing and Documentation Manager for Treehouse Software

Setting up a New Customer with tcVISION…

Treehouse Software representatives are busy these days, visiting customers and getting them started with our tcVISION product for complex data replication between mainframe, Linux, Unix, and Windows platforms.

As an example of a typical customer visit to implement data replication, we recently were on-site at a state government agency to configure tcVISION and set up bulk transfer and change data capture as well as train the State employees on using and managing tcVISION. Prior to our arrival, the customer had installed tcVISION and verified that its components were working correctly. After our three days on-site, the users were able to import their own ADABAS files, run scripts to perform bulk loads, and replicate transactions on the PLOG from ADABAS to SQL Server.

On the first day, we held a project kickoff meeting to discuss tcVISION’s architecture, the State’s ADABAS/NATURAL environment, and identified which ADABAS files should be replicated in the initial phase. We also presented a walkthrough of the product that covered metadata import, bulk transfer, and batch and real-time replication.

The group agreed that, for the purposes of the project at hand, real-time replication was not necessary immediately. It is possible that real-time replication and bi-directional replication may be needed in the future. Currently, monthly NATURAL extracts are used to transfer ADABAS data to SQL Server. The data is loaded into VARCHAR columns in the RDBMS, and then the data is copied to another set of tables. This process includes formatting dates, times, and numbers, but mostly ignores MUs and PEs (repeating fields in ADABAS). We showed how tcVISION can bulk transfer and replicate to a brand-new RDBMS schema created by tcVISION, as well as to an existing schema.

The setup process continued on the second day, and included creating two SQL Server databases for tcVISION, defining the connection to SQL Server, creating the tcVISION repository tables in one of the SQL Server databases, importing metadata from an ADABAS file into tcVISION, and creating the tables in SQL Server corresponding to that ADABAS file.

Next, we focused on populating these first tables. To do this, we ran the tcVISION bulk transfer script and then a control script to automatically execute the SQL Server bcp utility once the tcVISION Bulk Transfer completed.

We then imported another ADABAS file’s metadata into tcVISION. This second file contained several MUs, some of which we normalized, and others of which we denormalized on the parent table. After discussing the target structure, a bulk transfer on the ADABAS file was run. The ADABAS file contained over 100 fields and some 800,000 records. The bulk transfer ran in about 22 minutes, and the bcp into SQL Server ran for approximately two minutes. The customer’s existing NATURAL extract process takes a little under an hour for the same file, so tcVISION was able to perform the bulk transfer in about a quarter of the time.

As a last activity for the day, we demonstrated to the customer how to import an existing SQL Server table into tcVISION, and then map the table to an ADABAS file.

The tcVISION “Control Board” is the user’s MS Windows GUI Interface, providing a single and central point of administration, featuring the ability to view and manage transaction logs from other systems; built-in Wizards for mapping and modeling metadata; script generation for bulk transfer and data replication; and monitoring of replication status, script output, and DBMS extensions.

For our final activities on the third day, we ran a few more bulk transfers, and the users focused on importing ADABAS metadata and creating tables in SQL Server. During this time, we set up a new control script that allowed bulk transfer of multiple ADABAS files, instead of having to process each ADABAS file with a different script. The control script reads a list of ADABAS files, and then runs a bulk transfer script for each ADABAS file. The tcVISION manager uses a class setting to limit the number of bulk transfers running in parallel.

We then verified that the control script had submitted the processing script for each ADABAS file, and we demonstrated how to use tcVISION’s scheduler to automatically submit the control script. Once scheduling was working, we then turned our attention to processing the PLOG for change data capture.

A processing script was set up to read the PLOG on the mainframe, and then update the newly-populated SQL Server tables. We explained how some JCL could be added to the to the PLOG archive copy job to automatically process the PLOG whenever it flips.

The users were very pleased with the progress made during the three-day visit, and are well on their way to having tcVISION automatically handle and simplify their ADABAS replication efforts.

To learn more, or to request a tcVISION demonstration, contact Treehouse Software today.