Deletion of Unrelated STOGROUPS

Issue/Introduction:

We have identified roughly 1000k STOGROUPS not connected to a database which are versioned. These connect to SPACE entity and propagate down to PART and TBSPACE entity. How can we delete these STOGROUP entities from the Repository?

Environment:

Release:
Component: XJC

Resolution:

1. Delete all unconnected STOGROUP

Create list of deleting STOGROUP using the following query. This query identifies STOGROUP entities that are not related to the DATABASE entity or the SPACE entity. Change DBXREL30 to your Repository creator. The query result returned by this query can be used for deletion with DBXLOAD.

SELECT SUBSTR(ST0.STORAGE_NAME,1,15),
               ST0.STATUS,
               ST0.VERSION,
     FROM     DBXREL30.DBX_DB2_STORAGE ST0,
                     DBXREL30.DBX_XREF X
  WHERE
         ST0.STATUS = 'PROD'
         AND X.ENT_TYPE=122
         AND ST0.ENT_ID = X.ENT_ID
              AND ST0.ENT_ID NOT IN(
         SELECT X1.TARGET_ID
           FROM
              DBXREL30.DBX_XREF X1
       WHERE
              X1.ENT_TYPE IN (106,142));

2. Delete STOGROUP connected to SPACE only:

Upload the following 'STGDEL' path into the Repository control tables. Change SUBS to your DB2 subsystem ID. Change 'your.hlq' to your high level qualifier. Change DBXREL30 to your Repository creator.

//LOADTBL EXEC PGM=DSNUTILB,
//         PARM='SUBS,LOADTBL'
//SYSPRINT DD SYSOUT=*
//STEPLIB DD DISP=SHR,
//             DSN=your.hlq.SDSNLOAD
//         DD DISP=SHR,
//             DSN=your.hlq.SDSNEXIT
//UTPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSDISC DD SPACE=(CYL,(1,1)),UNIT=SYSDA
//SYSERR   DD SPACE=(CYL,(1,1)),UNIT=SYSDA
//SYSMAP   DD SPACE=(CYL,(1,1)),UNIT=SYSDA
//SORTWK01 DD SPACE=(CYL,(1,1)),UNIT=SYSDA
//SORTWK02 DD SPACE=(CYL,(1,1)),UNIT=SYSDA
//SYSUT1   DD SPACE=(CYL,(1,1)),UNIT=SYSDA
//SORTOUT DD SPACE=(CYL,(1,1)),UNIT=SYSDA
//SYSREC00 DD *
STGDEL     122   142   139 Y U N 0 A DELETE STOGROUP
STGDEL     139   119   116 Y U N 0 A DELETE STOGROUP
STGDEL     139   130   129 Y U N 0 T DELETE STOGROUP
STGDEL     116   117   113 Y D N 0 A DELETE STOGROUP
STGDEL     116   141   129 Y U N 0 T DELETE STOGROUP
//SYSIN    DD *
LOAD DATA INDDN SYSREC00 RESUME YES DISCARDDN SYSDISC
      INTO TABLE DBXREL30.DBX_PATH_TBL
     (PATHNAME          POSITION (1)   CHAR (8),
      FROM_TYPE          POSITION (10) INTEGER EXTERNAL(5),
      VIA_TYPE           POSITION (16) INTEGER EXTERNAL(5),
      TO_TYPE            POSITION (22) INTEGER EXTERNAL(5),
      INCLUDE            POSITION (28) CHAR (1),
      DIRECTION          POSITION (30) CHAR (1),
      PRINT_VIA          POSITION (32) CHAR (1),
      PRIORITY           POSITION (34) INTEGER EXTERNAL(2),
      DELETE_RULE        POSITION (37) CHAR (1),
      DESCRIPTION        POSITION (39) CHAR (40))
/*
//

2a. Create list of deleting Stogroup. This query will return STOGROUP not related to Database but is the target of a SPACE relation.
Change DBXREL30 to your repository creator.

   SELECT      SUBSTR(ST0.STORAGE_NAME,1,15),
                                 ST0.STATUS,
               ST0.VERSION
     FROM     DBXREL30.DBX_DB2_STORAGE ST0,
              DBXREL30.DBX_XREF X
       WHERE
              ST0.STATUS = 'PROD'
              AND X.ENT_TYPE=122
              AND ST0.ENT_ID = X.ENT_ID
              AND ST0.ENT_ID NOT IN(
         SELECT X1.TARGET_ID
           FROM
              DBXREL30.DBX_XREF X1
           WHERE
              X1.ENT_TYPE=106)
      AND
       ST0.ENT_ID IN(
         SELECT X1.TARGET_ID
           FROM
              DBXREL30.DBX_XREF X1
       WHERE
              X1.ENT_TYPE=142)
        ;

2b. Navigate to Repository and access VD DB2;VT STOGROUP
VLE any of stogroup in the list from 2a. Then issue the command
EDIT.SPECIAL.PATHDEL and select the new STGDEL path from the list.

COMMAND ===>
SIZE ------------ CURRENT DIALOG: DB2 ENTITY TYPE: STOGROUP
¦ FILE EDIT VIEW OPTIONS SYSTEM PROFILE NAVIGATE HELP
¦ | SELECT ¦
¦ | INSERT ¦
¦ | UPDATE ¦ ---------------------------------------
¦ ___ | DELETE ¦
¦ | DOMAIN ¦
¦ | MINIEDIT ¦
¦ | SYNC ¦
¦ | CHGSCR > ¦
¦ | COPY > ¦
¦ | LOCK > ¦
¦ | MGRATION > ¦
¦ | NAVIGATE > ------------
¦ | SPECIAL > | CHGTYPE ¦
¦ | TEXT > | MERGE ¦
¦ ------------ | REPLACE ¦
¦ | PATHADD ¦
¦ | PATHDEL ¦
¦

SIZE -------------------------- PATH LIST
¦ NAVIGATE HELP
¦ SEL PATH DESCRIPTION
¦ --- -------- ------------------------
¦ _ DB2ALL DB2 PATH
¦ _ DB2TBDEL DB2 PATHDEL TABLES
¦ S STGDEL DELETE STOGROUP
******************************************

Press enter to generate the JCL
At the end you can add whole list of Stogroup from 2a query result above

//SYSIN    DD DATA
PARM//CHKPNT=Y
PARM//DIALOG=DB2
PARM//PATH=STGDEL
STOGROUP//AZCNTL2//JAN28C//0//
STOGROUP//AAAAAAA//JAN28C//0//
etc.

You can change the CHKPNT parm to R to run the job in 'report mode' if desired.
This will show you what will be deleted but no commits will actually occur.
When you are ready to run in commit mode you can run with CHKPNT=Y.

Additional Information:

For more information regarding CA Repository Paths, refer to Chapter 18 in the CA Repository for z/OS Administration Guide.