Finding the Access Modules (AMs) and Relational Command Modules (RCMs) that reference specific tables
Article ID: 29747
Updated On:
Products
Issue/Introduction
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 in Appendix E of the CA IDMS SQL Reference Guide. To see the most relevant information from it, connect to the application catalog where your 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
As you can see from the first two lines, there will be one row returned for each unique table included in the AM, so AMs which reference multiple tables will have multiple rows in the result table.
If you want to search for AMs which include a particular table, you could add a where clause specifying your 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 you are running this in batch, you may get more meaningful output 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 unadulturated command to retrieve data from the table would be simply
SELECT * FROM SYSTEM.AMDEP ORDER BY NAME;
or, if you want to search for AMs which reference a particular table,
SELECT * FROM SYSTEM.AMDEP
WHERE TABSCHEMA = 'schema-name' AND TABLE = 'table-name'
ORDER BY NAME ;
Environment
Component:
Feedback
