Monitored Table Access

This feature adds a “Last Assessed” field to relevant tables, for interaction with Optim™ Designer data lifecycle products.

This feature is also called “Table Last Referenced”.

This feature uses Guardium’s External Feed that is preconfigured with the data (a predefined External Feed map), and an audit process to run it.

Follow these Steps

  1. Create the target (Optim) tables on any Informix® database. Use the script.
  2. Open the Audit Process Builder by navigating to Comply > Tools and Views > Audit Process Builder, then edit the process named Table Last Referenced. Add a datasource to the External Feed task (the Informix datasource that contains the tables) and setup the run-time parameter for servers group. All the rest is predefined and there is no need to change it.
  3. Run (or schedule to run periodically) the audit process.
Note: The resulting table will show only the last run. The receiver count is the count of the receivers, and not the count of run results since the last run only.

IBM® Guardium® can detect external references to database objects, specifically tables. This capability, in conjunction with Optim Designer, can be used to manage the retirement of inactive tables or archiving with certain retention policies.

Guardium collects and maintains a list of tables with the date of last reference. The list is built using policies in Guardium that dictate the interval of last reference and the frequency to be used for updating the list content. The information captured by Guardium is referred to as the “last reference” list and supplies the following information: What tables are no longer referenced? What table access trends exist for retirement candidates?

Having the ability to accurately plan for the retirement of applications will help to:
  • Plan for hardware retirement or redeployment
  • Reduce cost of ownership by moving or retiring those resources supporting the applications (for example, hardware, DBA(s), Application owners, IT operations such as backups).
  • Know what tables are rarely or never accessed

This functionality of IBM Guardium has been added directly to the Optim Designer user interface.

The information supplied by Guardium to Optim consists of the following attributes per table entry:

Table 1. Monitored Table Access List Entry
List Entry Description

Field

Comment

DataSourceDesc

Description

Server IP

 

Host Name

 

DB Vendor

for example, Oracle, DB2®

User Name

for example, for Oracle it mostly defines the schema

Database Name

 

Schema

 

Table

 

Date

Date of last access

Script to create Informix tables in the Optim product

Last_referenced_datasource

create table last_referenced_datasource (

    id                  serial(1) not null,

    datasource_desc     varchar(100),

    server_ip           char(39),

    host_name           varchar(200),

    db_vendor           char(40),

    primary key (id) constraint last_referenced_datasource_pk

);

Last_referenced_table

create table last_referenced_table (

    id                  serial(1) not null,

    datasource_id       int not null,

    user_name           char(32),

    db_name             char(128) not null,

    schema_name         char(128) not null,

    table_name          char(128) not null,

    last_reference      datetime year to second not null,

    primary key (id) constraint last_referenced_table_pk,

    foreign key (datasource_id) references last_referenced_datasource(id) constraint last_referenced_table_fk

);