How can I dynamically identify and REORG only the base tables associated with all LOBs in my DB2 subsystem?

Version 2
    Share This:

    This document contains official content from the BMC Software Knowledge Base. It is automatically updated when the knowledge article is modified.


    PRODUCT:

    Next Generation Technology Reorg for DB2 for z/OS


    COMPONENT:

    Next Generation Technology Reorg for DB2 for z/OS



    QUESTION:

    How can I dynamically identify and REORG only the base tables associated with all LOBs in my DB2 subsystem?
     


    ANSWER:

    Create an OBJECTSET with Recovery Manager or DASD Manager using the USER DEFINED SQL option.  When prompted for the USER DEFINED SQL, paste this:

    SELECT 'TS',                   
           C.DBNAME,               
           C.NAME                  
    FROM   SYSIBM.SYSAUXRELS    A, 
           SYSIBM.SYSTABLES     B, 
           SYSIBM.SYSTABLESPACE C  
    WHERE  A.TBOWNER = B.CREATOR   
      AND  A.TBNAME  = B.NAME      
      AND  B.TYPE    = 'T'         
      AND  B.DBNAME  = C.DBNAME    
      AND  B.TSNAME  = C.NAME      
      AND  C.TYPE   <> 'O'         
      AND  C.DBNAME NOT LIKE 'BMC%'
      AND  C.DBNAME NOT LIKE 'DSN%'
      AND  C.DBNAME NOT LIKE 'MQ%' 
      AND  C.DBNAME NOT LIKE 'TMB%'
      AND  C.NAME   NOT LIKE 'BMC%'
     ORDER BY 1,2,3


    Reference the OBJECTSET in your NGT REORG syntax as follows:
    //SYSIN DD *                                            
       REORG TABLESPACE OBJECTSET userid.objectsetname

    The contents of the OBJECTSET will be resolved at runtime.
     


    Article Number:

    000188675


    Article Type:

    FAQ/Procedural



      Looking for additional information?    Search BMC Support  or  Browse Knowledge Articles