Catalog changes in Db2 11

This version of Db2 for z/OS® includes changed catalog tables, new catalog tables, and new indexes on catalog tables.

New catalog tables in Db2 11

Begin general-use programming interface information.

Table 1. New catalog tables
Catalog table name Description
SYSINDEXCLEANUP catalog table The rows in the SYSIBM.SYSINDEXCLEANUP table specify time windows to control index cleanup processing. Each row specifies a time window to enable or disable the cleanup of pseudo-deleted index entries for specific database objects.
SYSQUERYPREDICATE catalog table The SYSIBM.SYSQUERYPREDICATE table contains information about predicates for queries in the SYSIBM.SYSQUERY table that have been identified for extended optimization. It correlates to the SYSIBM.SYSQUERY table by the QUERYID column.
SYSQUERYSEL catalog table The SYSIBM.SYSQUERYSEL table contains information about the selectivity of predicates for queries in the SYSIBM.SYSQUERY table that have been identified for extended optimization. It correlates to the SYSIBM.SYSQUERY table by the QUERYID column.
SYSSTATFEEDBACK catalog table The SYSIBM.SYSTATFEEDBACK table contains information about missing or conflicting catalog statistics for SQL statements.
SYSVARIABLEAUTH catalog table The SYSIBM.SYSVARIABLEAUTH table contains one row for each privilege of each authorization ID that has privileges on a global variable.
SYSVARIABLES_DESC catalog table The SYSIBM.SYSVARIABLES_DESC table is an auxiliary table for the SYSIBM.SYSVARIABLES table.
SYSVARIABLES catalog table The SYSIBM.SYSVARIABLES table contains one row for each global variable that is created.
SYSVARIABLES_TEXT catalog table The SYSIBM.SYSVARIABLES_TEXT table is an auxiliary table for the DEFAULTTEXT column of the SYSIBM.SYSVARIABLES table.

End general-use programming interface information.

Changed catalog tables in Db2 11

The following table shows a list of the new columns and the existing columns that are revised. Revisions to columns include new column descriptions, new values for a column, changed data types, changed column lengths, or both changed data types and lengths.

Begin general-use programming interface information.

Table 2. Summary of new and revised catalog table columns
Catalog table name New column Revised column
SYSCHECKS catalog table   RBA
SYSCOPY catalog table MODECREATED
  • PIT_RBA
  • START_RBA
  • ICTYPE
  • STYPE
  • TTYPE
SYSDATATYPES catalog table
  • ARRAYLENGTH
  • ARRAYINDEXTYPEID
  • ARRAYINDEXTYPELEN
  • ARRAYINDEXSUBTYPE
  • LENGTH
  • METATYPE
  • SCALE
  • SUBTYPE
SYSDEPENDENCIES catalog table DVERSION
  • BTYPE
  • DTYPE
SYSINDEXES catalog table  
  • CLUSTERED
  • CLUSTERRATIO
  • COPYLRSN
  • SPARSE
SYSINDEXES_HIST catalog table  
  • AVGKEYLEN
  • CLUSTERRATIOF
  • DATAREPEATFACTORF
SYSINDEXPART catalog table RBA_FORMAT
  • AVGKEYLEN
  • CARDF
  • FAROFFPOSF
  • NEAROFFPOSF
SYSINDEXPART_HIST catalog table  
  • AVGKEYLEN
  • CARDF
  • FAROFFPOSF
  • NEAROFFPOSF
SYSINDEXSPACESTATS catalog table   COPYUDPATELRSN
SYSINDEXSTATS catalog table  
  • CLUSTERRATIO
  • CLUSTERRATIOF
  • DATAREPEATFACTORF
  • KEYCOUNT
  • KEYCOUNTF
SYSINDEXSTATS_HIST catalog table  
  • CLUSTERRATIOF
  • DATAREPEATFACTORF
  • KEYCOUNTF
SYSPACKAGE catalog table
  • APPLCOMPAT
  • ARCHIVESENSITIVE
  • BUSTIMESENSITIVE
  • DESCSTAT
  • EXTSEQNO
  • APREUSE
  • DBPROTOCOL
  • SYSTIMESENSITIVE
SYSPACKCOPY catalog table
  • APPLCOMPAT
  • ARCHIVESENSITIVE
  • BUSTIMESENSITIVE
  • DESCSTAT
  • EXTSEQNO
  • SYSTIMESENSITIVE
  • DBPROTOCOL
  • RECORDTEMPORALHIST
  • SYSTIMESENSITIVE
SYSPACKDEP catalog table   BTYPE
SYSPACKSTMT catalog table EXPANSION_REASON  
SYSPARMS catalog table  
  • CCSID
  • ENCODING_SCHEME
  • LENGTH
  • SUBTYPE
SYSPENDINGDDL catalog table
  • COLNAME
  • COLUMN_KEYWORD
  • PARTITION
  • PARTITION_KEYWORD
  • CREATEDTS
  • OBJTYPE
  • OPTION_KEYWORD
  • OPTION_VALUE
  • STATEMENT_TYPE
SYSPLANDEP catalog table   BTYPE
SYSPLAN catalog table PROGAUTH  
SYSQUERY catalog table
  • ACCESS_PATH_HINT
  • OPTION_OVERRIDE
  • SELECTIVITY_VALID
  • SELECTVTY_OVERRIDE
 
SYSQUERYPLAN catalog table EXPANSION_REASON  
SYSRESAUTH catalog table   QUALIFIER
SYSROUTINES catalog table   COMMIT_ON_RETURN
SYSSEQUENCES catalog table
  • SEQNAME
  • SEQSCHEMA
 
SYSTABLEPART catalog table
  • PCTFREE_UDP
  • PCTRFREE_UPD_CALC
  • RBA_FORMAT
  • LIMITKEY
SYSTABLES catalog table
  • ARCHIVING_SCHEMA
  • ARCHIVING_TABLE
  • STATS_FEEDBACK
  • RBA1
  • RBA2
  • VERSION
SYSTABLESPACESTATS catalog table
  • UPDATESIZE
  • LASTDATACHANGE
COPYUPDATELRSN

End general-use programming interface information.

New and changed indexes in Db2 11

Db2 11 introduces the following new indexes.
Table 3. New indexes in Db2 11
Catalog table New index
SYSCHECKDEP DSNSDX01
SYSCHECKS DSNSCX01
SYSCHECKS2 DSNCHX01
SYSCOPY DSNUCH01
SYSINDEXCLEANUP DSNICX01
SYSINDEXSPACESTATS DSNRTX02
SYSOBD_AUX DSNOB03
SYSQUERYPREDICATE
  • DSNQEX01
  • DSNQEX02
SYSQUERYSEL
  • DSNQLX01
  • DSNQLX02
SYSSEQUENCES DSNSQX03
SYSSTATFEEDBACK
  • DSNSFX01
  • DSNSFX02
  • DSNSFX03
SYSSTRINGS DSNSSX01
SYSTABLESPACESTATS DSNRTX01
SYSTABSTATS DSNTTX02
SYSVARIABLEAUTH
  • DSNVAX01
  • DSNVAX02
  • DSNVAX03
SYSVARIABLES DSNOVX01
SYSVARIABLES_DESC DSNOVX03
SYSVARIABLES_TEXT DSNOVX02

Db2 11 introduces changes to the following indexes:

Table 4. Changed indexes in Db2 11
Catalog table Changed index
SYSXMLTYPMOD DSNTMX01
SYSXMLTYPMSCHEMA DSNMSX01

For more information about catalog index formats in Db2 11, see Catalog indexes.

New built-in global variables

Db2 11 introduces the following built-in global variables:

  • CLIENT_IPADDR
  • GET_ARCHIVE
  • MOVE_TO_ARCHIVE

For descriptions, see Built-in global variables.