Blog
DB2utor

Categories

March 08, 2011

DB2 10 New Catalog Tables

After I wrote about the changes with existing catalog tables, a reader asked me for a list of new catalog tables. Luckily for me, my colleauge Steen Ramuseen used RC/Compare to generate a list of new tables for me. Steen, in fact, will be presenting “The DB2 10 Catalog: A Revolution” at IDUG North America 2011. He'll provide many more details about the dropped, changed and new catalog tables.

Autonomic Procedures
•    SYSIBM.SYSAUTOALERTS: Recommendations from autonomic procedures.
•    SYSIBM.SYSAUTOALERTS_OUT: AUX table for 2M CLOB column.
•    SYSIBM.SYSAUTORUNS_HIST: History from every execution of autonomic procedures.
•    SYSIBM.SYSAUTOALERTS_HISTOU: AUX table for 2M CLOB column.
•    SYSIBM.SYSAUTOTIMEWINDOWS: Time periods for execution of autonomic procedures.
•    SYSIBM.SYSTABLES_PROFILES: Profile associated with SYSTABLES (used by RUNSTATS and ADMIN_UTL_MONITOR).
•    SYSIBM.SYSPROFILE_TEXT: AUX for 1M CLOB PROFILE_TEXT column.

Audit and Permission
•    SYSIBM.SYSAUDITPOLICIES: Contains one row for each audit policy. SECADM users have the privilege to select/insert/update/delete from this table. Users with SQLADM, system DBADM, DATAACCESS, ACCESSCTRL, SYSCTRL or SYSADM authority can select from this catalog table.
•    SYSIBM.SYSCONTROLS: Row permissions and column masking.
•    SYSIBM.SYSCONTROLS_DESC: AUX table for 2M BLOB column.
•    SYSIBM.SYSCONTROLS_RTXT: AUX table for 2M CLOB column.

Object DDL related
•    SYSIBM.SYSINDEXES_RTSECT: AUX for 1GB BLOB RTSECTION column.
•    SYSIBM.SYSINDEXES_TREE: AUX for 1GB BLOB PARSETREE column.
•    SYSIBM.SYSTRIGGERS_STMT: AUX for 2M CLOB STATEMENT column.
•    SYSIBM.SYSVIEWS_STMT: AUX for 2M CLOB STATEMENT column.
•    SYSIBM.SYSVIEWS_TREE: AUX for 1G BLOB PARSETREE column.
•    SYSIBM.SYSROUTINES_TREE: AUX for 1G BLOB PARSETREE column.
•    SYSIBM.SYSXMLTYPMOD: XML type modifiers and XML columns.
•    SYSIBM.SYSXMLTYPMSCHEMA: XML schema information.
•    SYSIBM.SYSPENDINGDDL: Objects which have pending DDL changes (e.g., DSSIZE, SEGSIZE etc.)
•    SYSIBM.SYSPENDINGDDLTEXT: AUX for 2M CLOB STATEMENT_TEXT column.
•    SYSIBM.SYSPENDINGOBJECTS: Specific objects for SYSPENDINGDDL.
 
Package and Statement related
•    SYSIBM.SYSPACKCOPY: Copies of packages.
•    SYSIBM.SYSPACKSTMT_STMT: AUX for 2M CLOB STATEMENT column (SYSPACKSTMT).
•    SYSIBM.SYSPACKSTMT_STMB: AUX for 2M BLOB STMTBLOB column (SYSPACKSTMT).
•    SYSIBM.SYSQUERY: Holds QUERIES (dynamic PLANMGMT).
•    SYSIBM.SYSQUERYOPTS: Optimization parameters/attributes for dynamic queries in SYSQUERY.
•    SYSIBM.SYSQUERY_AUX: AUX for 2M CLOB STMTTEXT column (SYSQUERY).
•    SYSIBM.SYSQUERYPLAN: OPTHINT information for SYSQUERY – including versioning/historic OPTHINTs.
 
Thanks again to the reader who contacted me and of course to Steen for compiling this information. If you haven't seen Steen present, I highly recommend attending his presentation if you're going to IDUG NA. Steen is one of the best speakers and most knowledgeable DB2 experts I know.