This article describes how to determine which tables are referenced by which AMs (Access Modules) and RCMs (Relational Command Modules).
Release: All supported releases.
Component: SQL Option.
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;