Finding the Access Modules (AMs) and Relational Command Modules (RCMs) that reference specific tables

Document ID : KB000029747
Last Modified Date : 14/02/2018
Show Technical Document Details

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 ;