Finding the AMs and RCMs that reference specific tables

Products

• IDMS
• IDMS - Database

Issue/Introduction

This article describes how to determine which tables are referenced by which AMs (Access Modules) and RCMs (Relational Command Modules).

 

Environment

Release: All supported releases.
Component: SQL Option.

Resolution

There is a system table called SYSTEM.AMDEP which contains the correlations between Access Modules and tables which they include. The complete structure of this table is documented at SYSTEM.AMDEP.  To see the most relevant information from it, connect to the application catalog where the AMs are created, and issue this query to see a report as shown below: 

SELECT NAME, VERSION AS V, SUBSTR(TABSCHEMA,1,8) AS SCHEMA, TRIM(TABLE), TYPE
  FROM SYSTEM.AMDEP ORDER BY NAME;

 *+ NAME           V  SCHEMA    TRIM(FUNCTION)      TYPE
 *+ ----           -  ------    --------------      ----
 *+ GETBONUS       1  DEMOEMPL  EMPLOYEE            T
 *+ GETBONUS       1  DEMOEMPL  BENEFITS            T
 *+ SQLUDEA        1  DEMOEMPL  EMPLOYEE            T
 *+ SUMBONUS       1  DEMOEMPL  EMPLOYEE            T
 *+ SUMBONUS       1  DEMOEMPL  BENEFITS            T

Each row in this SELECT represents the fact that the named table is referenced in the named access module.

To search for which access modules reference a particular table, add a where clause specifying the schema and table names, as follows:

SELECT NAME, VERSION AS V, SUBSTR(TABSCHEMA,1,8) AS SCHEMA, TRIM(TABLE), TYPE
  FROM SYSTEM.AMDEP
  WHERE TABSCHEMA = 'DEMOEMPL' AND TABLE = 'EMPLOYEE'
    ORDER BY NAME;

There is no similar cross-reference for RCMs.

If running in batch, more meaningful output can be created by running a more standard SQL command, without all of the scalar functions in the above query which were designed to produce one line of information per table, so that it would fit an online screen. The unaltered command to retrieve data from the table would be simply

SELECT *  FROM SYSTEM.AMDEP ORDER BY NAME;

or, to search for access modules which reference a particular table,

SELECT *  FROM SYSTEM.AMDEP
  WHERE TABSCHEMA = 'schema-name' AND TABLE = 'table-name'
    ORDER BY NAME;