Creating an Audit Trail Dataset

An Audit Trail dataset contains a record of all successful modifications to Db2 table data, including inserts, deletes, updates, commits, and rollbacks. The report displays the before and after image of the data. A sample Audit Trail report displays in Sample Audit Trail Report. When in your File-AID for Db2 edit session you edit a LOB column with the XLOB command (see XLOB), instead of displaying the before and after image of the LOB data, the report lists the names of the saved before and after XLOB audit trail files. Refer to XLOB Audit Trail Datasets for more information on XLOB audit.

Your ability to generate an audit trail is largely determined by decisions which were made when File-AID for Db2 was installed. If your site chose to never produce an audit trail, the Create Audit Trail field is not present on the Edit Options (Create Audit Trail Field on Edit Options Screen) or Batch Execute SQL (Batch Execute SQL) screen. If your site chose to always produce an audit trail, the field is present with a prefilled value of YES. You cannot change this value. The last option is to let the user, along with an audit trail exit routine, choose to produce an audit trail. In this case, the Create Audit Trail field is present on the Edit Options or Batch Execute SQL screen and the user can enter either YES or NO. If you enter YES, an audit trail is generated. If you enter NO, an audit trail is not generated unless an audit trail exit routine determines that one is mandatory for the selected object.

Create Audit Trail Field on Edit Options Screen

SMF Audit Trail Option

If your site chooses to direct File-AID for Db2 audit trail records to the SMF log, File-AID for Db2 creates an audit record each time you browse or edit an object and modify table data, then writes it to the SMF log. In addition, the Create Audit Trail field does not appear on the Edit Options or Batch Execute SQL screen, however, the message “Audit Trail Activated” displays at the start of the browse/edit session. Furthermore, Audit Trail Print Facility, and Option 5, Print Audit Trail will not be available. For more information, see the Configuring BMC Compuware File-AID for Db2 on how to print File-AID for Db2’s audit trail records from the SMF log or how to modify the audit trail exit routine.

Audit Trail Print Facility

If you requested an audit trail report and made modifications to your data, the Audit Trail Print Facility window (Audit Trail Print Facility screen) displays when you exit from your edit session. If you want to print your audit trail, enter the required information and either SUB to submit the job, or JCL to edit the JCL, on the command line. If you enter JCL, you must still enter SUB on the command line to submit the job. Press END to save the audit trail dataset and exit the print facility without printing the data set. You can print an audit trail dataset at a later time through Option 5, Print. See Print-Table-Data-or-Audit-Trail-function for more information.

Audit Trail Print Facility screen

Specify Printer Characteristics Area

Printer Destination

Enter the printer destination for the audit trail report.

Lines Per Page

Enter the number of lines to print per page.

Sysout Class

Enter the sysout class for the audit trail report.

Specify Audit Trail Options Area

Dataset Name

Specifies the audit trail dataset name. This name defaults to your most current audit trail dataset. You may not override the name.

Description

Specifies comments that are printed on the audit trail report. Up to four lines of comments may be entered.

Job Card Information

Enter a valid job card. See User Parms, Batch-Options for entering a default job card.

Sample Audit Trail Report

Audit Trail Dataset Usage and Maintenance

Each time an audit trail is generated, a new, variable blocked sequential dataset is created. Unless modified by the audit trail exit routine, the default audit trail dataset name is:

   userid.FADB2.Dyymmdd.Thhmmss

          For example:

File-AID for Db2 does not delete an existing audit trail dataset. It is therefore the user’s responsibility to perform regular DASD management.

While you are editing a table, a message may display advising you that the audit trail dataset is filled. If this happens, exit the edit session and begin a new session. File-AID for Db2 closes the filled audit trail dataset and allocates a new, uniquely named data set for the second edit session.

Once all editing for the specified object is completed, the audit trail datasets are merged if necessary. If this condition occurs frequently, ask the installer to increase the amount of space allocated to each audit trail dataset.

XLOB Audit Trail Datasets

When in your File-AID for Db2 edit session you edit a LOB column with the XLOB command (see XLOB) and “Create Audit Trail” is enabled, File-AID saves two copies of the XLOB work file, the before (old) and the after (new) XLOB audit trail files.

Before (Old):

   hlq.userid.FADB2.X.Dyymmdd.Thhmmss.O

         For example:

       FD.TEMP1.USERID.FADB2.X.D160817.T073459.O

After (New):

    hlq.userid.FADB2.X.Dyymmdd.Thhmmss

           For example:

       FD.TEMP1.USERID.FADB2.X.D160817.T073459

The after XLOB audit trail file has the same name as the temporary XLOB work file when Audit Trail is not used. The before XLOB audit trail file is identified by the suffix .O.

In an audit trail report with XLOB update (Sample Audit Trail Report with XLOB Update), instead of showing the before and after images, the report shows the message:

and lists the saved before and after XLOB audit trail file names:

If in the same Edit session more Update actions for other columns were made after the XLOB update process, instead of showing the before and after images, the report shows the message:

Just like the audit trail datasets, File-AID for Db2 does not delete any of the XLOB audit trail files. It is therefore the user’s responsibility to perform regular DASD management.

Sample Audit Trail Report with XLOB Update