Deletion of Unrelated STOGROUPS

Document ID : KB000126261
Last Modified Date : 12/02/2019
Show Technical Document Details
Question:
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?
Answer:
 
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.