How to report on dormant tables and columns

Guardium® offers functionality that can help data architects and DBAs discover which tables and which fields are not being used.

1. Upload all the tables from the system catalog. Do this by creating a custom table.

   Prerequisites

   1. Define datasource/test database connection
   2. Upload data (create custom table)
   3. Create new domain (merge custom tables with existing reports)

   See External Data Correlation for further information.

   The following example is available from Comply > Custom Reporting > Custom Table Builder > Upload Definition > Import Table Structure.

   When the configuration is complete, click the Retrieve button.

   

   Configuration - Upload Definition, Import Table Structure

   Upload the data so that it is in the Guardium system (as a custom table) and if desired, schedule this upload. This data will be used to determine the superset of all tables defined in the system.

   Mapping all the objects (and/or objects-fields) in the system

   In this example, dormant data based on table names is used. But the analysis can include columns, provided the upload tasks are defined to bring back pairs of <object,field> and use tuple groups to compare with an observed tuple of object+field.

   For instances of Object-Field, replace the DW Dormant Objects report with the DW Dormant Objects-Fields report. For instances of Object-Field, replace the DW Select Object Access report with the DW Object-Field Access report.

   Once you complete the upload, define a custom domain based on this single custom table and define a report that retrieves the table names.

   Next, populate the group DW All Objects group from this report and schedule this Import from Query action if desired. This creates a group that has all the tables as defined by the system catalog.

   Note: When populating the group DW All Objects group, it should include the information to click Run Once Now -> Select All -> Click on Import button. Do the same for Group Name "DW SELECT Accessed Objects". It needs to import all scheduled definitions.

   When done, click the Save button.

2. Mapping the object directly

   Use monitored data to determine which tables and/or table/columns have been accessed over a period of time.

   Look at some additional predefined reports. The DW SELECT Object Access report shows all object names that have been accessed through a SELECT statement.

   Now, populate the group DW SELECT Accessed Objects group from the report, filling in the filtering attributes that you require.

   Note: When populating the group DW All Objects group, it should include the information to click in 'Run Once Now' -> Select All -> Click on Import button. Do the same for Group Name "DW SELECT Accessed Objects". It needs to import all scheduled definitions.

   The following example is available from Setup > Tools and Views > Group Builder > Chose DW All Objects > Populate from Query > DW Select Object Access.

   When done, click the Save button.

   

   Configuration - Populate Group from Query, Object Name

3. Create a report of all items of step 1 that are not in step 2.

   Use the DW Dormant Objects report to view objects that are in the all objects group, but have not been used in a Select.

   Contrast this report with the earlier Report – Table Names. Notice that EMP is not in this report because it was used in a SELECT statement.

   Note: Because group members are centrally managed and synchronized between the Central Manager and managed units, the content of this report may be delayed by up to 30-minutes. If you need access to the information that is most up-to-date, run this report on the Central Manager or ask your Guardium administrator to synchronize the managed unit from the Central Manager.

   Further ways to access tables

   Mapping objects indirectly

   In addition to direct SELECT access, tables may be accessed through stored procedures and functions. In this case, you will need to do a bit more mapping to allow Guardium to calculate such SELECTs.

   First, use the report DW EXECUTE Object Access to fill in the group called DW EXECUTE Objects with a set of stored procedure names that are being executed. Then, use indirect mapping to generate all the objects being used from within these procedures.

   Assume that you have a procedure defined:

   create or replace procedure num_depts(deptnums out NUMBER) is
   begin
       select count(*) into deptnums from dept;
   end;

   In this case, every execution of num_depts also does a select on DEPT.

   Use the “populate group from query” feature to use the Object Name column in the DW EXECUTE Object Access report to populate the “DW Execute Objects” group. Then, use this group to populate the DW EXECUTE Accessed Objects group.

   In the Group Builder select the DW EXECUTE Objects from the list and click on Auto Generate Calling Prox. Select either Using Reverse Dependencies, which is supported only for Oracle in Guardium 8, or Generate Selected Objects.

   If you choose to use dependencies then you will need to choose a database that has access to DBA_DEPENDENCIES and what type of dependencies to follow.

   Choose to append members to the DW EXECUTE Accessed Objects group.

   The following example is available from Setup > Tools and Views > Group Builder > Chose DW EXECUTE Accessed Objects > Auto Generated Calling Prox > Using Reverse Dependences > Analyze Stored Procedures.

   

   Configuration - Auto Generated Calling Prox, Using Reverse Dependencies

   This will add the dependent objects to the group DW EXECUTE Accessed Objects.