Description:
As Jobs and/or Procs are imported into the CA Repository for z/OS via the Exchange for JCL, STEP associations are created linking a JOB/PROC (Entity) to the corresponding JOB STEP (Entity). Over time if the Job Steps are no longer a part of a re-scanned job/proc, the corresponding STEP associations are deleted from the repository cross reference table. As this process continues through the re-scanning process this can result in the accumulation of dangling JOB STEP entities that are no longer related to a source type (JOB/PROC); however, are linked to the FILE entity via the STEPFILE association. This does not cause any harm nor does it impact the credibility of impact analysis; however, it can clog repository space and produce false impressions if impact analysis is performed from JOB STEP versus the JOB/PROC entity.
Since the JOB STEPs are entities and have a corresponding row in the DBX_XREF table, the orphan cleanup process (PRMORPH) would not touch them.
Solution:
You can use the DBXLOAD utility to delete obsolete JOB STEPs and FILEs. By doing it this way, DBXLOAD cleans up what is left behind in DBX_XREF table for you.
In this first example, JOB STEPS which are no longer connected to a JOB and/or PROC are deleted from the Repository.
This delete job not only deletes the JOB STEPs but also cleaned up the STEPFILE connections.
Note.
Change SUBS to your DB2 subsystem id.
Change repowner to your repository creator.
Change 'YOUR.HLQ' to your high level qualifier
-
//STEP1 EXEC PGM=DBXLOAD,PARM=('ERRCOUNT(0)/N,SUBS,,,,Y,')
//STEPLIB DD DSN=YOUR.HLQ.LOADLIB,DISP=SHR
// DD DSN=YOUR.DB2.SDSNLOAD,DISP=SHR
//DBXPARM DD DSN=YOUR.HLQ.ISPPLIB(DBXPARM),
// DISP=SHR
//SYSPRINT DD SYSOUT=*
//TEMPSQL DD DSN=&&SQLTEMP,DISP=(NEW,DELETE,DELETE),UNIT=SYSDA,
// SPACE=(TRK,(50,20),RLSE),
// DCB=(LRECL=4044,BLKSIZE=16176,RECFM=FB)
//SYSUDUMP DD SYSOUT=*
//SYSIN DD *
DELETE SQL FROM SYSRC00 ERROR SYSER02
VALIDATE NO
INTO DIALOG 'SYSTEM' ENTITY 'JOB STEP'
FIELDS
(
JOB_STEP_NAME POS(00001) CHAR(00128) READONLY
STATUS POS(00129) CHAR(00008) READONLY
VERSION POS(00137) CHAR(00005) READONLY
);
/*
//SYSRC00 DD *
SELECT
SUBSTR(JOB_STEP_NAME,1,128)
,STATUS
,DIGITS(VERSION)
FROM repowner.DBX_SYS_JOB_STEP J,
repowner.DBX_XREF X
WHERE J.ENT_ID = X.ENT_ID
AND X.ENT_TYPE = 257
AND J.ENT_ID NOT IN
(SELECT X1.TARGET_ID
FROM repowner.DBX_XREF X1
WHERE X1.ENT_TYPE = 263)
;
/*
//SYSER02 DD DSN=YOUR.HLQ.ERROR,DISP=(NEW,CATLG,DELETE),
// SPACE=(TRK,(1,15)),
// UNIT=SYSDA,
// DCB=(LRECL=300,BLKSIZE=18000,RECFM=FB)
-
In this second example, DBXLOAD will detect and delete those FILES which no longer have a STEPFILE.
Note.
Change 'SUBS' to your DB2 subsystem id.
Change 'repowner' to your repository creator.
Change 'YOUR.HLQ' to your high level qualifier
-
//STEP1 EXEC PGM=DBXLOAD,PARM=('ERRCOUNT(0)/N,SUBS,,,,Y,')
//STEPLIB DD DSN=YOUR.HLQ.LOADLIB,DISP=SHR
// DD DSN=YOUR.DB2.SDSNLOAD,DISP=SHR
//DBXPARM DD DSN=YOUR.HLQ.ISPPLIB(DBXPARM),
// DISP=SHR
//SYSPRINT DD SYSOUT=*
//TEMPSQL DD DSN=&&SQLTEMP,DISP=(NEW,DELETE,DELETE),UNIT=SYSDA,
// SPACE=(TRK,(50,20),RLSE),
// DCB=(LRECL=4044,BLKSIZE=16176,RECFM=FB)
//SYSUDUMP DD SYSOUT=*
//SYSIN DD *
DELETE SQL FROM SYSRC00 ERROR SYSER02
VALIDATE NO
INTO DIALOG 'SYSTEM' ENTITY 'FILE'
FIELDS
(
ELEMENT_NAME POS(00001) CHAR(00128) READONLY
STATUS POS(00129) CHAR(00008) READONLY
VERSION POS(00137) CHAR(00005) READONLY
);
/*
//SYSRC00 DD *
SELECT
SUBSTR(ELEMENT_NAME,1,128)
,STATUS
,DIGITS(VERSION)
FROM repowner.DBX_DDL_EFILE E,
repowner.DBX_XREF X
WHERE E.ENT_ID = X.ENT_ID
AND X.ENT_TYPE = 259
AND E.ENT_ID NOT IN
(SELECT X1.TARGET_ID
FROM repowner.DBX_XREF X1
WHERE ENT_TYPE = 25106)
;
/*
//SYSER02 DD DSN=YOUR.HLQ.ERROR,DISP=(NEW,CATLG,DELETE),
// SPACE=(TRK,(1,15)),
// UNIT=SYSDA,
// DCB=(LRECL=300,BLKSIZE=18000,RECFM=FB)