PeopleSoft Tables Accessed and Updated

Many a times, I wondered is there a deliered PeopleSoft way to find out the tables modified by PeopleSoft processes? I was pondering on with many a COBOL programs during upgrades to find out which program accesses a particular PeopleSoft table and also cases where I wanted to know all the processes(App Engine, SQR, COBOL) that performs Select/Update/Insert/Delete on a PeopleSoft table. The result of this search is what I found in the form of the PeopleSoft delivered SQR report - TBLUSAGE.

PeopleSoft provides this SQR report - TBLUSAGE which could be run from the runcontrol page - TAU_RUN_CONTROL. This table access utility page can be accessed from the navigation:
Set Up HRMS > System Administration > Utilities > Tables Accessed and Updated

The Tables Accessed and Updated report analyzes the following type of PeopleSoft applications:

·        Application Engine programs

·        SQR Reports

·        COBOL Programs and additionally

·        PS Query views

This report generates a detailed PDF report with the record names referenced by the SQL statements within the chosen applications(from above). The report encloses the analysis of tables access by these programs specified above along with the way(Select, Update, Insert or Delete) in which those tables(records) were accessed.

 The Tables Accessed and Updated report may sometimes be incomplete in cases like:

1. SQRs using dynamic substitution tables - The condition is reported on encountering this type of SQR.
2. Application Engine makes use of meta-SQL statements that contains dynamic object references - This condition is reported on encountering this kind of meta-SQL.
3. SQR incorporates the construction of SQL clauses within string variables that can then be used in SQL statements and resolved by the SQR at runtime - Such type of SQRs and table references found are very less.
4. When The Application Engine program contains the meta-SQL phrase %Execute() with platform-specific procedural logic - Such stuff are reported as unanalyzed.
5. Application Engine program uses a SQL statement within a PeopleCode step - Irrespective of whether a Peoplecode action contains SQL statements or not, the Tables Accessed and Updated report - reports PeopleCode as unanalyzed.
6. SQR references libraries through syntax that includes (#INCLUDE) SQC files.
7. COBOL programs reference libraries through syntax that calls (CALL ... USING) routines in other modules.
   In case 6 and 7, if the module called is a very generic library with a large number of general-purpose routines in it, the analysis may overstate the number of tables referenced.

In the table access utility page, we could restrict the table access analysis based on:

·        Application Type (AE, SQR Report, Query and COBOL)

·        Product name

·        Application name (Enter ? for all applications in the product chosen above)

·        PeopleTools tables - especially for language control and currency conversions.

·        Location of Source Code - enabled only for COBOL programs and the path to COBOL source code on the server must be specified.

·        Location of Tools programs - enabled only for SQR reports and if an analysis is performed excluding PeopleTools tables, then enter the path of the PeopleTools SQR components' location on the server.