Guru: Getting Meaningful Audit Information from a Journal

July 26, 2021 Paul Tuohy

Journaling is an invaluable tool that is used for data recovery, data replication, commitment control and, of course, auditing. But getting at the audit information in an easy-to-use manner can be cumbersome. In this article I want to introduce you to a stored procedure that will create an audit table for any table/physical file and a corresponding view that can be used for easy auditing of changes.

For example, if I am auditing the EMPLOYEE table (I will be using the EMPLOYEE table in the standard Db2 Sample Database) for a change to the SALARY column, I would use the following simple SQL statement:

select audit_type, jodate, jotime, jojob, jouser, jonbr, jopgm,
       old_empno, new_empno, old_salary, new_salary
  from AUDIT_EMPLOYEE_VIEW 
 where audit_type in ('INSERT', 'DELETE') or
       old_salary $lt;%gt; new_salary 
 order by joseqn;

The resulting result set would look like this:

The Process

There are three stages to the audit process.

• Define the required audit table and view. We need to create an audit table for each journaled table that we want to audit. This step only needs to be repeated if the definition of the journaled table changes (e.g., a new column was added to the table).
• Populate the audit table by copying journal entries from the journal to the audit table. This can be performed on an hourly, weekly, monthly, or sporadic basis, as required.
• Perform the required audit analysis (as in the example above).

Getting Journal Audit Information into a Table

The Display Journal (DSPJRN) command allows us to output journaled data to a database file. We are interested in auditing record changes, therefore the command to copy all record journals from a journal for a specific table from a specific date would look something like this:

DSPJRN JRN(SQLSTAND/QSQJRN) FILE((SQLSTAND/EMPLOYEE)) RCVRNG(*CURCHAIN)
       FROMTIME('03/01/21') ENTTYP(*RCD) OUTPUT(*OUTFILE) 
       OUTFILE(QTEMP/JRNOUT1) 

The resulting table will have a row per journal entry. Each row will contain columns of journal information (the date and time of the change, the job and program that performed the change, etc.) and one column (JOESD), which contains an image of the complete row. The Journal Entry Type (JOENTT) column indicates if the row image is an insert, update, before-update or after-update image.

Whereas the journal information columns are fine, the contents of the JOESD column are of little use as is.

Creating the Audit Table and View

An audit table is a copy of the file generated by the DSPJRN command with the JOESD column replaced by the column definitions of the journaled table.

An audit view is a view of the audit table that combines before and after images so that each row provides an old and new image of the affected row. Deleted rows are shown in the old columns and inserted rows are shown in the new columns.

I recommend that you have a separate schema for audit tables and views. This makes it easier to secure the audit information, if required, or to control whether or not the data is included in backups, which may not be required if the journals are being saved.

We can write a stored procedure to create an audit table and view for any table. This is an example of calling the MAKE_AUDIT_TABLE() stored procedure to create an audit table and view for the table EMPLOYEE in schema SQLSTAND. The audit table will be AUDIT_EMPLOYEE in the schema AUDITLIB. The table will have a system name of AUDITEMP. The audit view will automatically name AUDIT_EMPLOYEE_VIEW — the name of the audit table with _VIEW appended.

call make_audit_table(JOURNAL_TABLE_SCHEMA => 'SQLSTAND', 
                      JOURNAL_TABLE => 'EMPLOYEE', 
                      AUDIT_SCHEMA => 'AUDITLIB', 
                      AUDIT_TABLE => 'AUDIT_EMPLOYEE', 
                      AUDIT_TABLE_SYSTEM => 'AUDITEMP'); 

This is the SQL to create the MAKE_AUDIT_TABLE() stored procedure. Copy it and paste it into Run SQL Scripts and run it. These are the main points to note in the procedure. Refer to the callouts in the code.

• Create a copy of the *OUTFILE template file (QSYS.QADSPJRN) in QTEMP and drop the definition of the JOESD column from the definition. The calls to the QSYS2.override_qaqqini() stored procedure are to ignore inquiry messages that will stop the column from being dropped. There are four other template files that can be used to provide more detailed journal columns, if required. If this information is required, simply replace every reference of QSYS.QADSPJRN to the name of the alternative template (e.g. QSYS.QADSPJRN2).
• Use dynamic SQL to create the audit table which will consist of all of the all the columns in the table defined in (A) above and all of the columns in the table being audited. Effectively, the audit table is the file generated by the DSPJRN command but the definition of the column JOESD is replaced by the definition of all of the columns in the table being audited.
• Use the LISTAGG() aggregate function to retrieve the column lists required for the audit view. For the Journal columns the view does not include JOESD (because it is replaced by the columns of the table being audited) and JORES (which is for padding and does not contain any information).
• Use dynamic SQL to create the audit view. Each row in the view consists of journal columns, before (OLD_) columns for the table being audited and after (NEW_) columns for the table being audited. The view is a union between three select statements on the audit table — one for changes, one for inserts and one for deletes. The AUDIT_TYPE columns indicates the change type.

  create or replace procedure make_audit_table
    (
      journal_table_schema varchar(256),
      journal_table        varchar(256),
      audit_schema         varchar(256),
      audit_table          varchar(256),
      audit_table_system   varchar(10) default ''
    )
      set option dbgview = *source,                        
                 commit  = *none 
    BEGIN
    
      declare audit_qualified         varchar(512);
      declare journal_table_qualified varchar(512);
      declare for_system_name         varchar(100);
      
      declare execute_statement varchar(32000);
      
      declare journal_columns     varchar(32000);
      declare journal_columns_old varchar(32000);
      declare old_name_select     varchar(32000);
      declare old_name_list       varchar(32000);
      declare new_name_select     varchar(32000);
      declare new_name_list       varchar(32000);
      declare default_list        varchar(32000);
      
      -- Ignore File not found when deleting temporary table
      declare CONTINUE HANDLER for SQLSTATE '42704'
        BEGIN
        END;
    
      -- Set qualified names
      set audit_qualified = audit_schema concat 
                            '.' concat 
                            audit_table;
      set journal_table_qualified = journal_table_schema concat 
                                    '.' concat 
                                    journal_table;
      
      -- Copy the *OUTFILE template to a work file in QTEMP and
      --  drop the JOESD (record image) column                              
 -- (A)
      drop table QTEMP.MYAUDIT;
      create table QTEMP.MYAUDIT like QSYS.QADSPJRN;
      call QSYS2.override_qaqqini(1, '', '');
      call QSYS2.override_qaqqini(2, 'SUPPRESS_INQUIRY_MESSAGES', '*YES');
      alter table QTEMP.MYAUDIT drop column JOESD;
      call QSYS2.override_qaqqini(3, '', '');
    
 -- (B)  
      -- Create the Audit table.
      --   This is the *OUTFILE template but JOESD is replaced with the
      --   definition of the columns in the journaled table
      if (audit_table_system <> '') then
        set for_system_name = ' for system name ' concat audit_table_system;
      end if;
      set execute_statement = 
         'create or replace table ' concat audit_qualified concat 
         for_system_name concat ' as ( ' concat
         'select MYAUDIT.*,  ' concat journal_table concat '.* ' concat
         'from QTEMP.MYAUDIT cross join ' concat 
         journal_table_qualified concat ') WITH NO DATA';
    
      prepare execute_make_table from execute_statement;                 
      execute execute_make_table;  
      
      drop table QTEMP.MYAUDIT;
      
      -- Create the required Column Name lists for the Journal Entry Columns, 
      --  the OLD and NEW columns in the journaled table and a set of defaut
      --  values for the columns in the journaled table
-- (C) 
      select LISTAGG(COLUMN_NAME, ', ') 
               WITHIN GROUP(ORDER BY ordinal_position)
        into journal_columns 
        from QSYS2.SYSCOLUMNS 
       where (table_schema, table_name) = ('QSYS', 'QADSPJRN') 
         and column_name not in ('JORES', 'JOESD');
    
      select LISTAGG('OLD.' concat COLUMN_NAME, ', ') 
                WITHIN GROUP(ORDER BY ordinal_position)
        into journal_columns_old 
        from QSYS2.SYSCOLUMNS 
       where (table_schema, table_name) = ('QSYS', 'QADSPJRN') 
         and column_name not in ('JORES', 'JOESD');
    
      select LISTAGG('OLD.' concat COLUMN_NAME, ', ') 
                WITHIN GROUP(ORDER BY ordinal_position) 
        into old_name_select
        from QSYS2.SYSCOLUMNS 
      where (table_schema, table_name) 
          = (journal_table_schema, journal_table);
    
      select LISTAGG('NEW.' concat COLUMN_NAME, ', ') 
               WITHIN GROUP(ORDER BY ordinal_position)
        into new_name_select
        from QSYS2.SYSCOLUMNS 
      where (table_schema, table_name) 
          = (journal_table_schema, journal_table);               
    
      select LISTAGG('OLD_' concat COLUMN_NAME, ', ') 
               WITHIN GROUP(ORDER BY ordinal_position) 
        into old_name_list
        from QSYS2.SYSCOLUMNS 
      where (table_schema, table_name) 
          = (journal_table_schema, journal_table);
    
      select LISTAGG('NEW_' concat COLUMN_NAME, ', ') 
               WITHIN GROUP(ORDER BY ordinal_position)
        into new_name_list
        from QSYS2.SYSCOLUMNS 
      where (table_schema, table_name) 
          = (journal_table_schema, journal_table); 
      
      select LISTAGG(CASE
                       WHEN NUMERIC_SCALE IS NOT NULL THEN '0'
                       WHEN DATA_TYPE = 'DATE' THEN 'CURRENT_DATE'
                       WHEN DATA_TYPE = 'TIME' THEN 'CURRENT_TIME'
                       WHEN DATA_TYPE = 'TIMESTAMP' THEN 'CURRENT_TIMESTAMP'
                       ELSE ''''''
                     END, ', ') WITHIN GROUP(ORDER BY ordinal_position)
        into default_list 
        from QSYS2.SYSCOLUMNS 
       where (table_schema, table_name) 
           = (journal_table_schema, journal_table);
    
      -- Create the Audit View over the Audit Table
      --  Each row in the view contains:
      --  - an AUDIT_TYPE column (contains CHANGE, INSERT or DELETE)
      --  - the Journal entry columns
      --  - the values of the Before Change/Delete columns 
      --  - the values of the After Change/Insert columns     
      set execute_statement = 
        'create or replace view ' concat audit_schema concat 
         '.' concat audit_table concat '_view ' concat
         '( AUDIT_TYPE, ' concat journal_columns concat ', ' concat
         old_name_list concat ', ' concat new_name_list concat 
         ') as select ''CHANGE'' as AUDIT_TYPE, ' concat
         journal_columns_old concat ', ' concat
         old_name_select concat ', ' concat
         new_name_select concat ' from ' concat 
         audit_qualified concat ' OLD inner join ' concat
         audit_qualified concat ' NEW on ' concat
         '(OLD.JODATE, OLD.JOTIME, OLD.JOJOB, OLD.JOUSER, OLD.JONBR, ' concat 
         'OLD.JOPGM, OLD.JOOBJ, OLD.JOLIB, OLD.JOMBR, OLD.JOCTRR) = ' concat
         '(NEW.JODATE, NEW.JOTIME, NEW.JOJOB, NEW.JOUSER, NEW.JONBR, ' concat
         'NEW.JOPGM, NEW.JOOBJ, NEW.JOLIB, NEW.JOMBR, NEW.JOCTRR) ' concat
         'where (OLD.JOENTT, NEW.JOENTT) = (''UB'', ''UP'') or ' concat
         '(OLD.JOENTT, NEW.JOENTT) = (''BR'', ''UR'') ' concat
         'UNION select ''INSERT'' as AUDIT_TYPE, ' concat
         journal_columns concat ', ' concat
         default_list concat ', ' concat
         old_name_select concat ' from ' concat  
         audit_qualified concat ' OLD ' concat
         'where JOENTT IN (''PT'', ''PX'') '  concat
         'UNION select ''DELETE'' as AUDIT_TYPE, ' concat
         journal_columns concat ', ' concat
         old_name_select concat ', ' concat
         default_list concat ' from ' concat  
         audit_qualified concat ' OLD ' concat
         'where JOENTT IN (''DL'', ''DR'') '
         ;
    
      prepare execute_make_view from execute_statement;                 
      execute execute_make_view;  
      
                   
    END; 

Populating the Audit Table

Getting the information from the journal to the audit table is a two-step process — use the Display Journal (DSPJRN) command to write the required journals to an output file and then use the Copy File (CPYF) command to copy the output file to the audit table.

This is an example of how we could achieve this in Run SQL Scripts. Note the following about the CPYF.

The TOFILE parameter uses the system name for the audit table.

The value for the Format Option (FMTOPT) parameter is *NOCHK. This means that the value of the JOESD column (the record image) is copied/overlaid onto all of the columns (defined in the audit table) for the audited table — we are doing a byte-by-byte copy.

CL: DSPJRN JRN(SQLSTAND/QSQJRN) FILE((SQLSTAND/EMPLOYEE)) RCVRNG(*CURCHAIN)
           FROMTIME('03/01/21') ENTTYP(*RCD) 
           OUTPUT(*OUTFILE) OUTFILE(QTEMP/JRNOUT1);                                                                                       
CL: CPYF FROMFILE(QTEMP/JRNOUT1) TOFILE(AUDITLIB/AUDITEMP) MBROPT(*REPLACE)
         FMTOPT(*NOCHK) ;              

In a production environment this would be done in a scheduled CL program with the from date/time being programmatically set, as required. This CL program would also run any required audit programs you may have written.

But you now have a view which can be easily queried, as in the earlier example.

Other Considerations

A few items that you may want to consider:

• Instead of using the existing column names for the Journal columns (JODATE, JOTIME etc.), provide a hard-coded list of more meaningful names in the view. This list would replace the use of journal_columns in the column list in the view.
• Use the value of the COLUMN_DEFAULT column (in SYSCOLUMNS) as opposed to using a CASE to determine what the default values should be. The reason I used the CASE was to ensure that I did not end up with any NULL values.
• If you are auditing a “busy” table you may want to run your extracts/audits on a more frequent basis as opposed to copying large amounts of data from the journal. That is, copy in smaller chunks.
• Have a look at what is offered by the other Output File Formats on the DSPJRN command to determine if any of the extra information if of benefit to you. This example uses the default blue of *TYPE1.

I hope you find this stored procedure of some use.

RELATED STORY

Journal Forensics 101