Using Audit mode
Audit mode provides the capability to use the current LOAD process to load detail level report data into a single audit table. This audit table can be a central resource for querying against, and can contain every table's modification activity. All table modifications can be loaded into the audit table, or only selected tables can be loaded. Also, in conjunction with advanced filters, a subset of a table's columns for a table can be loaded as well.
Using an audit table
The basic procedure for using an audit table is similar to other data tables. You must:- Create the audit table using the Audit table specifications panel.
- Set up the JCL to populate the audit table.
- Populate the audit table
- Maintain the audit table:
- Perform backups
- Allow for table recovery
- Archive the table
- Clean up obsolete data in the table.
Creating an audit table
When you type S on the Audit mode field, the "Audit table specifications" panel displays. This panel allows you to define the layout of the audit table.
V3.5.0 ---------------- Audit table specifications -------------- RS22/DC1A
Hit 'ENTER' to process panel; 'PF3' to exit and bypass saving specifications
*Audit Table Creator..................
*Audit Table Name..................... AUDIT_TABLE
*Create audit table................... N (Y/N)
Audit Database Name.................
Audit Tablespace Name...............
Target SSID.........................
*Customize audit table header columns. N (Y/N)
*Max number of audited table columns.. 50 (1-750)
*Max length of audited table columns.. 50 (1-255)
Audited table column names:
*Column name prefix................ LAT_COLNAME_ (max 15 chars)
*Column value prefix............... LAT_COLVALUE_ (max 15 chars)
COMMAND ===> Audit table specifications panel fields
- Audit Table Creator
- This is the creator of the audit table and is a maximum of 35 characters in length.
- Audit Table Name
- This is the name of the audit table and is a maximum of 35 characters long. Though a default name is provided, set this value to any name appropriate for your site.
- Create audit table
- Set this field to
Yto create audit table. - Audit Database Name
- This is the name of database where audit table will be created if the Create audit
table field is set to
Y. The database should already exist. - Audit Tablespace Name
- This is the name of table space where audit table will be created if the Create audit
table field is set to
Y. The tablespace should already exist. - Target SSID
- This is the ID of a subsystem on the current system where audit table will be created
if Create audit table is set to
Y. - Customize audit table header columns
- Type Y to view and/or change the default settings for the header columns. You can change which header columns to include, and the actual column names for those header columns. Type N to use the header column default settings. If set to Y, another panel is displayed showing the current settings for the header-type columns. After you finish customizing the header columns, you will be returned to this panel.
- Max number of audited table columns
- This is the number of audited table columns that will exist in your audit table (in addition to the header columns). Use a number between 1 and 750. For example, assume your largest audited table has 100 columns, and you will need all those columns tracked in the audit table. Therefore, a value of 100 would be appropriate.
- Max length of audited table columns
- This is the maximum length of the audited table columns. Use a
number between 1 and 255. If any audited table column is longer than
255, it will be truncated. Every audited table column will be defined
as VARCHAR with the length you specify. That is, all column data in
the audit table is saved in its displayable format, as varying length
character columns, not as its original column type. This is how the
audit table can save all different column types from different tables
in the same table. Note that you cannot use these audit columns in
anyway other than as displayable values. If the original column was
an integer, you cannot now perform arithmetic operations on it in
the audit table. Note: Keep in mind that the audit table row length may require that this table be created in a table space with greater than 4K page sizes. For example, if you specify 100 columns with a length of 255, you would need a table space with 32K page sizes to hold that table.
- Column name prefix
- This is prefix of the column names in the audit table that contain the column names of the audited tables. It is a maximum of 15 characters so that a number from 1 to 750 can be appended to the column name. It can be changed to any valid column name prefix.
- Column value prefix
- This is prefix of the column names in the audit table that contain the column values of the audited tables. It is a maximum of 15 characters so that a number from 1 to 750 can be appended to the column name. It can be changed to any valid column name prefix.
The audit table is created with columns of two distinct
types:
- Header type columns. These columns hold information about the table modification, such as what table was modified, who did it, what type of action they performed (update/insert/delete). These header columns help identify the rest of the rows of audit table data.
- Audited table columns. These columns hold the actual row data for the table modification, and identify the column names. These generic columns come in groups of two. Each audited table column must have a column to hold the name of the table column, as well as a column to hold the data itself. All column data is stored as character data, not as the type of data it was in the table. The column data comes directly from the detail report, and is always character based.
This is an example of an audit table layout with a requested
maximum column length = 40 and maximum number of audited columns =
50:
Name: LOCAL.AUDIT_TABLE
Column definitions:
(start header columns)
LAT_TABLE_CREATOR CHAR(8)
LAT_TABLE_NAME CHAR(18)
LAT_DATE CHAR(10)
LAT_TIME CHAR(8)
LAT_AUTHID CHAR(8)
LAT_ACTION CHAR(2)
LAT-ROW_STATUS CHAR(4)
(end of header columns)
(start audited table columns)
LAT_COLN_001 VARCHAR(18) (holds column name)
LAT_COLV_001 VARCHAR(40) (holds column value)
LAT_COLN_002 VARCHAR(18)
LAT_COLV_002 VARCHAR(40)
.
.
.
.
.
LAT_COLN_050 VARCHAR(18)
LAT_COLV_050 VARCHAR(40) To customize the column headers:
When you type Y in the Customize audit table header columns field, the "Customize audit table" panel appears:
V3.5.0 ------------------ Customize audit table ----------------- SC01/SS1A
COMMAND ===>
More: +
Hit 'ENTER' to process panel; 'PF3' to exit and bypass saving customization
Column ID *Column Name *Include?(Y/N)
----------- ------------------------------ --------------
DBID LAT_DBID Y
PSID LAT_PSID Y
OBID LAT_OBID Y
DBNAME LAT_DBNAME Y
TSNAME LAT_TSNAME Y
TABLE CREATOR LAT_TABLE_CREATOR Y
TABLE NAME LAT_TABLE_NAME Y
DATE LAT_DATE Y
TIME LAT_TIME Y
TIMESTAMP LAT_TIMESTAMP Y
LRSN LAT_LRSN Y
URID LAT_URID Y
AUTHID LAT_AUTH Y
JOB NAME LAT_PLAN Y
PLAN NAME LAT_JOBN Y
CONN ID LAT_CONNID Y
CONN TYPE LAT_CONNTY Y
MEMBER ID LAT_MEMID Y Important: This is a scrollable
panel, because not all column headers fit onto a single displayable
page. Though not all column headers are displayed, they are all listed
in Column identifiers.
This scrollable panel allows for both specification of desired header type columns in the audit table and definition of those column names. Though all columns can be excluded, the data in the resulting audit table would be very difficult to identify completely without these heading columns.
Customize audit table panel fields
- Column Name
- This field allows you to change the default name of the columns. Specify a column name up to 30 characters in length.
- Include
- Type Y to include the column in the audit table. Type N to exclude the column from the audit table.