Configuring BMC Compuware File-AID for Db2

Tasks

Perform the following tasks for additional File-AID for Db2 configuration options.

Task 7.1 Activate the File-AID for Db2 Audit Trail Facility 

Audit Trail Facility Overview

The Audit Trail Facility allows you to keep a record of table data modifications made with File-AID for Db2 Edit. If you request an audit trail, an audit trail exit routine is executed prior to the edit session. File-AID for Db2 is delivered with the audit trail facility inactive. The procedures for activating the facility are optional. If you choose to activate the facility and optionally modify the exit routine, you may wish to have the exit reviewed while the installation is in progress.

The File-AID for Db2 Audit Trail Facility creates a data set containing a record of any successful modification to table data made during a File-AID for Db2 Edit session; that is, whenever Db2 processes inserts, updates, deletes, commits, and rollbacks. The audit trail report details the before and after image of the data. The audit trail report can be directed to either the SMF log or the File-AID for Db2 Audit Trail dataset. The following figure shows a sample audit trail report.

Sample Audit Trail Report

Task 7.1.1 Create File-AID SVC for the File-AID for Db2 SMF Audit Trail Facility 

The File-AID SVC is required for SMF auditing within the File-AID products. The File-AID/MVS, File-AID/Data Solutions, File-AID for Db2, and File-AID for IMS products share the same user SVC.

1. Modify the SVC parameters in PARMLIB member FACMnnnn to your site’s values (refer to SVC-information)
2. Save the FACMnnnn PARMLIB member into the CMSC parameter library.
3. Perform Update CMSC with PARMLIB Information.
4. Link Edit File-AID SVC/ESR (refer to Link Edit File-AID SVC/ESR).
5. If the File-AID SVC is installed as a user SVC (IGC00nnn), the MVS SVC table (IEASVC00 in SYS1.PARMLIB) must be updated to include the new SVC number. For example:SVCPARM 253,REPLACE,TYPE(3)   /* FILEAID SVC */
6. Copy the module (IGC00nnn) from hlq.CXVJLOAD into SYS1.LPALIB. Then perform an IPL (with a CLPA) or use the SETPROG command to make the SVC available.

Task 7.1.2 Update the AUDIT Parameters

1. Modify the AUDIT_USAGE parameter in PARMLIB member FAFDnnnn (see AUDIT_USAGE) to one of the following values:
   • NO: Indicates that no audit trail is generated. Individual users won’t have the ability to override this parameter. In addition, the Create Audit Trail field (Create Audit Trail Field on Edit Options screen) does not appear on the Edit Options screen.
   • QUERY: (Default) The Create Audit Trail field appears on the Edit Options screen, giving the user the option of entering YES or NO (Create Audit Trail Field on Edit Options screen). If YES is entered, an audit trail is generated. If NO is entered, an audit trail is generated only if the audit trail exit determines that a mandatory audit trail should be produced for the selected object.
   • FORCE: An audit trail is generated for each object edited with File-AID for Db2. The Create Audit Trail field is prefilled with a YES value. This value is protected, preventing the user from changing it.
   • SMF: 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.
     For the SMF option, you also need to set additional parameters:

     • Set the SMF record number with the 

       AUDIT_SMF_RECORD_NUMBER. The record number must be between 128 and 255.

     • Set the AUDIT_MAXIMUM_RECORDS parameter to the SMF record number for audit trail. See AUDIT_MAXIMUM_RECORDS for additional information on modifying this parameter.

     • With this SMF option and the SVC set up, File-AID for Db2 can call the SVC that writes File-AID for Db2 audit records out to the SMF log.

       Important

       When AUDIT_USAGE is set to SMF:

       • The Create Audit Trail field (Create Audit Trail Field on Edit Options screen) does not appear on the Edit Options screen, however, the message “Audit Trail Activated” appears at the start of the browse/edit session.
       • Option 5, Print Audit Trail will not be available. See Printing the Audit Trail SMF Log for information on how to print File-AID for Db2’s audit trail records from the SMF log.
       • When using APF checking SVC (see Link Edit File-AID SVC/ESR), Load libraries hlq.CXVJLOAD and hlq.SXVJLOAD must be APF authorized.
2. Save the FAFDnnnn PARMLIB member into your site’s CMSC PARMLIB library.
3. Perform Update CMSC with PARMLIB Information for procedures.

Create Audit Trail Field on Edit Options screen

Task 7.1.3 Modify the File-AID for Db2 Audit Trail Exit 

The audit trail exit is called each time an object is about to be edited. The exit allows a site to control the conditions under which an audit trail is generated. For example, you may wish to modify the exit so that an audit trail is created only when modifications are made to designated tables.

1. Edit the sample audit trail exit based upon the coding language which can be found in the File-AID sample library (hlq.SXVJSAMP).
   1. F2AUDITX is coded in Assembler; any modifications must be coded in Assembler.
   2. F2AUDITC is coded in COBOL; any modifications must be coded in COBOL.
   3. These exits, as delivered, will produce an audit trail, based on the value of the DOAUDIT parameter which initially is determined by the product parameter AUDIT_USAGE. You can manually change DOAUDIT to one of the three values in these exits (see DOAUDIT Values).
   4. Read all comments in the sample audit exits. You can modify this information in your audit exit, and the modified information will be used in the File-AID for Db2 process.
   5. You can override the parameters marked with asterisk (*), and the values will be used in File-AID for Db2 audit process.
   6. Unless modified, all parameters are returned to File-AID for Db2 with the same values as when the exit was called.

DOAUDIT Values

Audit Trail Exit Considerations

When coding the audit trail exit, several factors should be considered.

• The File-AID for Db2 process mode identifier (PROCMODE) is one of the parameters passed between File-AID for Db2 and this exit. You can check the process mode (Online, Batch, or Workbench Editor) and set the desired audit process with the DOAUDIT variable for the exit output. Refer to DOAUDIT Values for the available DOAUDIT values.

• The data set name can be modified to virtually any valid system name. However, File-AID for Db2 and the exit assume that the disposition of each audit trail dataset is NEW. The exit should be coded so each audit trail dataset name is unique. The disposition cannot be modified by the user. See About the Audit Trail Dataset for more information on the audit trail dataset.

  Important

  The audit trail dataset is not used when the AUDIT_USAGE parameter is set to SMF.

• The maximum number of records limits the number of records that can be written to the audit trail dataset. A table is audited as follows:

  • One record describing a column is written to the audit dataset for each column in the table.
  • Two records are written to the audit data set for each row changed. The first record contains the row data prior to the change. The second row contains row data after the change.
  • Records are written to the data set when commits and rollbacks are performed.

  The first and last records in the data set identify the contents for the data set and are used as delimiters.

• The record length is calculated by File-AID for Db2 and represents the total number of bytes for all columns of data in the object. You are strongly discouraged from decreasing this value. If the actual number of bytes that File-AID for Db2 attempts to write to the audit trail dataset is greater than the defined record length, an abend occurs.
• The amount of space allocated to the audit trail dataset is a function of both the maximum number of records parameter value and the object’s record length. If a significant number of out-of-space errors occur, you should increase the maximum number of records parameter, AUDIT_MAXIMUM_RECORDS. Follow the procedure under Update the AUDIT Parameters , modifying instead the AUDIT_MAXIMUM_RECORDS parameter. See AUDIT_MAXIMUM_RECORDS for additional information on modifying this parameter.

About the Audit Trail Dataset

If no changes are made to the F2AUDITX sample exit, a new audit trail dataset is generated each time an object is edited with File-AID for Db2 when requested.

The default audit trail dataset name is generated by File-AID for Db2 in the following format:

   creator_ID.FADB2.datestamp.timestamp

• The default unit value is SYSDA. You can change this default by modifying parameter AUDIT_UNIT (see AUDIT_UNIT) in PARMLIB FAFDnnnn.
• The default VOLSER value is blank. You can change this default by modifying parameter AUDIT_VOLUME (see AUDIT_VOLUME) in PARMLIB FAFDnnnn.
• The record length passed to the exit is the sum of all columns in the object being edited, while the block size is calculated by File-AID for Db2. The maximum size of the audit trail dataset defaults to 1000 records. You can change this default by modifying parameter AUDIT_MAXIMUM_RECORDS (see AUDIT_MAXIMUM_RECORDS) in PARMLIB FAFDnnnn.

This data set is a sequential dataset containing variable length records. Because a new audit trail dataset is generated each time an object is edited with File-AID for Db2, DASD management is the responsibility of the user.

Task 7.1.4 Assemble and Link-edit the Audit Trail Exit

• If you modified the Assembler sample audit trail exit F2AUDITX, link-edit the exit into the File-AID load library (hlq.CXVJLOAD) by executing the JCL in F2JCLXIT in the File-AID sample library (hlq.SXVJSAMP).
• If you modified the COBOL sample audit trail exit F2AUDITC, link-edit the exit into the File-AID load library (hlq.CXVJLOAD) by executing the JCL in F2JCLCIT in the File-AID sample library (hlq.SXVJSAMP). The audit trail must be compiled and linked in COBOL.

Task 7.1.5 Printing the Audit Trail SMF Log

If you direct the audit trail to the SMF log you cannot use the File-AID for Db2 Option 5.2 Print Audit Trail. Instead, the member XFDSMFPR of the File-AID sample data set (hlq.SXVJSAMP) includes sample JCL that you must edit to meet your site specific requirements:

1. Modify member XFDSMFPR. Read the comments in the job for the options that need to be changed for all steps.
2. Save and submit the updated JCL.

Sample Audit Trail Report shows a sample print report.

Task 7.2 File-AID for Db2 — Object Administration Setup for DSN Command 

When you are in File-AID for Db2 Object Administration and if you want to use Object Administration functions against objects on other Db2 subsystems defined in Db2 configuration, there are 3 ways:

1. Exit out of Object Administration and change the subsystem ID in the DB2 SSID field on the File-AID for Db2 Primary Option Menu or in the SSID field on the Utility Selection Menu. Then return to Object Administration.
2. Use either the CONNECT or LOCATION command with location-id to access remote Db2 subsystems defined in your Db2 configuration.
3. Use DSN command to dynamically switch the current Db2 subsystem connection and allocated Db2 data sets.

To use the DSN command (#3), then you must perform the following procedure the first time you install File-AID for Db2 Object Administration.

1. Move member DBIDLIBX from the File-AID for Db2 Object Administration load library (hlq.SXVJLOAD) to an APF library that is in the link list (LNKLSTxx).
2. Rename the APF library module from DBIDLIBX to DBIDLIB.
   DBIDLIB uses TCB manipulation to dynamically change Db2 load and exit libraries.

3. Update your external security product.If your site uses security processing (such as RACF, CA ACF2, or CA Top Secret) that restricts programs that can be used as TSO commands, verify the following command is not restricted:

   DBIDLIB

   Important

   The DBIDLIB command must be available to users, and cannot be restricted from being used as a TSO command.

4. Update your TSO command table (IKJTSOxx) with DBIDLIB as an authorized command.

Task 7.3 Set Product Interfaces

File-AID for Db2 provides interfaces to the following products:

• Xpediter and Abend-AID
• Xpediter for DB2
• Library Management Tools

Xpediter and Abend-AID

Xpediter and Abend-AID batch compile processes can include the JCL necessary to run File-AID for Db2’s Batch Explain. You can receive an explain report at compile time. No special installation procedure is required to activate this feature. For more information, see Getting started with File-AID for Db2 options.

Task 7.3.1 Set Interface for Xpediter for DB2

The Xpediter for DB2 extension is an option (XT) with File-AID for Db2. It allows you to dynamically enter and experiment with SQL statements during an Xpediter/TSO interactive test session without stopping to compile or bind.

• 
  
  1. To install the interface, set parameter ALLOW_BRIDGE_TO_XPEDITER to YES (see ALLOW_BRIDGE_TO_XPEDITER) in your FAFDnnnn PARMLIB member.

  2. Review the instructions in member XPSTART in the hlq.SXVJCLIB library.

     By default, the CLIST will use the “Simple Deploy” method to allocate BMC data set names as specified with FANODE(PARMLIB) and the default CMSC PARMLIB member DDSNnnnn. When using a different DDSN suffix, just update the DDSNSFX() with the suffix, for example DDSNSFX(CPWR) points to DDSNCPWR.

     The “Simple Deploy” method requires Enterprise Common Components (ECC) Release 17.02, with all current maintenance applied. The comments in the member also provide instructions when you choose NOT to use the “Simple Deploy” method.

     • 
       
       1. If your File-AID and Xpediter/TSO run-time libraries are included in the CMSC PARMLIB default member DDSNnnnn as specified in your site’s CMSC start-up, then you don’t need to modify XPSTART.

       2. Otherwise, copy member XPSTART from the hlq.SXVJCLIB library to the hlq.CXVJCLIB library and follow the instructions in the member to reflect your environment.

          Important

          You can also make the Xpediter/TSO libraries available via your own linklist or logon proc.

  3. Additionally, you must complete the Xpediter/TSO installation procedure found in the Xpediter/TSO Installation and Configuration Guide as well as the procedures described in this space.

Task 7.3.2 Set Interface for Library Management Tools

File-AID for Db2 provides an interface to both CA Panvalet and CA Librarian. The SQL Source Analysis and Development facility can be used to analyze members stored in either CA Panvalet or CA Librarian without the need to copy the member into a sequential or partitioned dataset.

If you have a library management tool, for example CA Librarian or CA Panvalet, review the CLIST member that corresponds to your source editor and modify it if necessary (see the following figure). The CLISTs are located in hlq.SXVJCLIB.

 CLIST by Source Editor

Task 7.4 Support Display DATE Value as ISO Format in Db2 Subsystem Configured with DATE Format LOCAL

This optional task enables File-AID for Db2 Browse or Edit to always display DATE values in ISO format for a Db2 subsystem that uses a DATE format LOCAL exit routine to perform date formatting.

Member XVJDBRMU in File-AID sample library hlq.SXVJSAMP includes sample JCL that creates a clone of the File-AID DBRM library, and updates some File-AID for Db2 DBRM members in the cloned DBRM library, to reflect the DATE(ISO) parameter was used during the pre-compile process.

1. Follow the instructions in the member and submit the job.
2. Execute the File-AID BIND process (see also Task 4.7.2 Create and Run Bind JCL in the File-AID Installation and Configuration Guide. Use the DBRM clone (updated) library in your File-AID BIND jobs for your Db2 LOCAL DATE format subsystems.