DB2 for z/OS database maintenance

These maintenance and tuning guidelines apply to IBM® DB2® for z/OS® databases.

These guidelines assume that the DB_USER is the primary DB2 database user ID and ARCHIVE_USER is the secondary DB2 database user ID.

1. Use the Discovery Management Console to run a discovery.
   This method populates the domain database with data.
2. Stop the TADDM server.
3. Generate and run the REORG control statement for each table spaces used by TADDM.

   SELECT 'REORG TABLESPACE '||DBNAME||'.'||NAME FROM SYSIBM.SYSTABLESPACE
    WHERE CREATOR IN ('DB_USER', 'ARCHIVE_USER') ORDER BY 1;
   

4. Generate and run the REORG control statement for indexes used by TADDM.

   SELECT 'REORG INDEX '||CREATOR||'.'||NAME FROM SYSIBM.SYSINDEXES
    WHERE CREATOR IN ('DB_USER', 'ARCHIVE_USER');

5. Generate and run the RUNSTATS control statement for table spaces used by TADDM.

   SELECT 'RUNSTATS TABLESPACE '||DBNAME||'.'||NAME||' INDEX(ALL)
   SHRLEVEL REFERENCE' FROM SYSIBM.SYSTABLESPACE
   WHERE CREATOR IN ('DB_USER', 'ARCHIVE_USER') ORDER BY 1;

6. Regenerate and run the UPDATE index statistics statements for each TADDM DB user.

   SELECT 'UPDATE SYSIBM.SYSINDEXES SET FIRSTKEYCARDF=FULLKEYCARDF 
   WHERE NAME = '||''''||CAST(RTRIM(name) AS VARCHAR(40))||''''||' 
   AND CREATOR = '||''''||CAST(RTRIM(creator) AS VARCHAR(40))||''''||' 
   AND TBNAME = '||''''||CAST(RTRIM(tbname) AS VARCHAR(40))||''''||' 
   AND TBCREATOR = '||''''||CAST(RTRIM(tbcreator) AS VARCHAR(40))||''''||';'
   from sysibm.sysindexes a
   where tbcreator in ('DB_USER', 'ARCHIVE_USER')
   AND NAME IN
   (SELECT IXNAME
   FROM SYSIBM.SYSKEYS B
   WHERE A.CREATOR = B.IXCREATOR
   AND A.NAME = B.IXNAME
   AND COLNAME = 'PK__JDOIDX')
   AND TBNAME IN
   (SELECT NAME
   FROM SYSIBM.SYSTABLES C
   WHERE A.TBCREATOR = C.CREATOR
   AND A.TBNAME = C.NAME
   AND CARDF > 0);
   

   where DB_USER is the primary DB2 database user ID, and ARCHIVE_USER is the secondary DB2 database user ID.
7. Regenerate and run the UPDATE column statistics statements for each TADDM DB user.

   SELECT 'UPDATE SYSIBM.SYSCOLUMNS SET COLCARDF=(SELECT FULLKEYCARDF FROM 
   SYSIBM.SYSINDEXES WHERE NAME = '||''''||CAST(RTRIM(name) 
   AS VARCHAR(40))||''''||' 
   AND CREATOR = '||''''||CAST(RTRIM(creator) AS VARCHAR(40))||''''||' 
   AND TBNAME = '||''''||CAST(RTRIM(tbname) AS VARCHAR(40))||''''||' 
   AND TBCREATOR = '||''''||CAST(RTRIM(tbcreator) AS VARCHAR(40))||''''||') 
   WHERE NAME = '||''''||'PK__JDOIDX'||''''||' AND TBNAME = '||''''||
   CAST(RTRIM(tbname) AS VARCHAR(40))||''''||' 
   AND TBCREATOR = '||''''||CAST(RTRIM(tbcreator) AS VARCHAR(40))||''''||';'
   from sysibm.sysindexes a
   where tbcreator in ('DB_USER', 'ARCHIVE_USER')
   AND NAME IN
   (SELECT IXNAME
   FROM SYSIBM.SYSKEYS B
   WHERE A.CREATOR = B.IXCREATOR
   AND A.NAME = B.IXNAME
   AND COLNAME = 'PK__JDOIDX')
   AND TBNAME IN
   (SELECT NAME
   FROM SYSIBM.SYSTABLES C
   WHERE A.TBCREATOR = C.CREATOR
   AND A.TBNAME = C.NAME
   AND CARDF > 0);

8. Regularly monitor your largest tables based on your use of TADDM, and adjust their storage attributes if necessary. In particular, monitor the size of the following database tables, which can become large:
   • ALIASES
   • CHANGE_CAUSE_TABLE
   • CHANGE_HISTORY_TABLE
   • MSSOBJLINK_REL
   • PERSOBJ
   • SUPERIORS

   Use ALTER statements to modify the PRIQTY and SECQTY attributes according to the needs of your environment. If appropriate, consider moving tables to separate table spaces.
9. Use the REBIND command on the following packages with the KEEPDYNAMIC(YES) option:
   • SYSLH200
   • SYSLH201
   • SYSLH202