Impact analysis for VSAM files

When changes are made to a VSAM file such as changing the file length, key changes, or field changes, it is important to drop and recreate the tables and views that reference the VSAM file to ensure that the metadata catalog definitions match the current file information.

You can run the following union query to get a list of VSAM tables and views that can be affected by changes that are made to a specific file. In this example, the file name is USER1.EMPLOYE.FILE0001:

    (SELECT T.CREATOR AS "TABLE_CREATOR",
            T.NAME AS "TABLE_NAME", 
            T.TYPE,
            'NULL' AS "VIEW_CREATOR",
            'NULL' AS "VIEW_NAME", 
            T.FILE_NAME, 
            T.RECORD_LENGTH,
            T.KEY_OFFSET,
            T.KEY_LENGTH
        FROM SYSCAC.SYSVSAMTABLES T
        WHERE T.FILE_NAME = 'USER1.EMPLOYEE.FILE0001') 
     UNION 
     (SELECT V.BCREATOR AS "TABLE_CREATOR",
             V.BNAME AS "TABLE_NAME",   
             'V' AS "TYPE", 
             V.DCREATOR AS "VIEW_CREATOR",
             V.DNAME AS "VIEW_NAME",
            T.FILE_NAME, 
            T.RECORD_LENGTH,
            T.KEY_OFFSET,
            T.KEY_LENGTH 
        FROM SYSCAC.SYSVSAMTABLES T, SYSIBM.SYSVIEWDEP V 
          WHERE 
             (T.FILE_NAME ='USER1.EMPLOYEE.FILE0001') AND
             (T.NAME = V.BNAME) AND (T.CREATOR = V.BCREATOR)
          )
          ORDER BY 6,1,2;

You can run the query from CDA or by using the USERSAMP(CACCLNT) sample job.

Example output:

Example output from USERSAMP(CACCLNT) sample job

In this example output:

  • The USER1.EMPLVSAM_1 table accesses the USER1.EMPLOYE.FILE0001 file.
  • Three views reference the USER1.EMPLVSAM_1 table.