Catalog changes in Db2 12

Use this information to plan for catalog changes in Db2 12.

Important: Db2 12 introduces continuous delivery of new capabilities and enhancements in function levels. Most new capabilities become available only after activation of the function level that introduces them, or when applications run with the corresponding application compatibility level. For more information, see Activating Db2 12 function levels.

Catalog changes in catalog level 501 and higher

Begin general-use programming interface information.

The following catalog changes take effect in Db2 12 when you tailor the Db2 catalog for activation of the specified function level. The changes are listed in function level order with the highest at the top.

Catalog level Catalog object Change introduced Incompatible change?
V12R1M509 SYSTABLEPART catalog table New column: COMPRESS_USED

Changed column: COMPRESS

No
V12R1M509 SYSTABLESPACE catalog table Changed column: COMPRESS No
V12R1M507 MAX_LOCKS_PER_TABLE_SPACE New built-in global variable. No
V12R1M507 MAX_LOCKS_PER_USER New built-in global variable. No
V12R1M505 SYSPACKAGE catalog table Previously defined unused column now used: COPYID. No
V12R1M505 SYSPACKDEP catalog table Previously defined unused column now used: COPYID. No
V12R1M505 SYSPACKSTMT catalog table Previously defined unused column now used: COPYID. No
V12R1M503 REPLICATION_OVERRIDE New built-in global variable.  
V12R1M502 SYSINDEXES catalog table New column: KEYLABEL. No
V12R1M502 SYSSTOGROUP catalog table New column: KEYLABEL. No
V12R1M502 SYSTABLESPACE catalog table New column: KEYLABEL. No
V12R1M502 SYSTABLES catalog table New column: KEYLABEL. No

Catalog changes for function level 100

The following changes catalog take effect when you tailor the catalog Db2 for migration to Db2 12 function level 100 (catalog level V12R1M500).

Catalog object Change introduced Incompatible change?
CATALOG_LEVEL New built-in global variable. No
DEFAULT_SQLLEVEL New built-in global variable. No
PRODUCTID_EXT New built-in global variable. No
SYSDYNQRY catalog table New table.

New indexes:

  • DSNDQX01
  • DSNDQX02
  • DSNDQX11
No
SYSDYNQRYDEP catalog table New table.

New indexes:

  • DSNDQX03
  • DSNDQX04
  • DSNDQX05
  • DSNDQX12
No
SYSDYNQRY_EXPL catalog table New table.

New index: DSNDQX08.

No
SYSDYNQRY_OPL catalog table New table.

New index: DSNDQX10.

No
SYSDYNQRY_SHTEL catalog table New table.

New index: DSNDQX09.

No
SYSDYNQRY_SPAL catalog table New table.

New index: DSNDQX07.

No
SYSDYNQRY_TXTL catalog table New table.

New index: DSNDQX06.

No
SYSINDEXCONTROL catalog table New table. No
SYSIBM.SYSIXSPACESTATS_H catalog table New table. See Temporal versioning for Db2 catalog tables. No
SYSLEVELUPDATES catalog table New table. No
SYSSESSION catalog table New table. No
SYSSESSION_EX catalog table New table. No
SYSSESSION_STATUS catalog table New table. No
SYSAUDITPOLICIES catalog table New columns:
  • SYS_START1
  • SYS_END1
  • TRANS_START1
No
SYSCOLUMNS catalog table New column: ENCODING_SCHEME.

Changed column: PERIOD.

New index: DSNDCX06.

No
SYSCOLAUTH catalog table New columns:
  • SYS_START1
  • SYS_END1
  • TRANS_START1
No
SYSCONTEXTAUTHIDS catalog table New columns:
  • SYS_START1
  • SYS_END1
  • TRANS_START1
No
SYSCONTEXT catalog table New columns:
  • SYS_START1
  • SYS_END1
  • TRANS_START1
No
SYSCONTROLS catalog table New columns:
  • SYS_START1
  • SYS_END1
  • TRANS_START1
  • REGENERATETS
No
SYSCONTROLS_DESC_H catalog table New table for LOB data in the DESCRIPTOR column of SYSCONTROLS_H.

New index: DSNTRX04.

No
SYSCONTROLS_RTXT_H catalog table New table for LOB data in the RULETEXT column of SYSCONTROLS_H.

New index: DSNTRX03.

No
SYSCOPY catalog table Changed columns:
  • DSVOLSER
  • ICTYPE
  • STYPE
  • TTYPE
No
SYSCTXTTRUSTATTRS catalog table New columns:
  • SYS_START1
  • SYS_END1
  • TRANS_START1
No
SYSDBAUTH catalog table New columns:
  • SYS_START1
  • SYS_END1
  • TRANS_START1
No
SYSDEPENDENCIES catalog table New column: DTYPE No
SYSENVIRONMENT catalog table New columns:
  • CREATEDTS
  • APPLCOMPAT
No
SYSINDEXSPACESTATS catalog table New columns:
  • GETPAGES
  • SYS_START
  • SYS_END
  • TRANS_START

Changed column: GETPAGES.

No
SYSINDEXES catalog table New columns:
  • DSSIZE
  • PAGENUM
  • PARKEYCOLNUM 1
  • STATUS 1
  • INDEXSTATUS 1
  • PARTITIONS 1
  • PQTY
  • STORTYPE
  • STORNAME
  • VCATNAME
  • FREEPAGE
  • PCTFREE
  • GBPCACHE
  • SECQTYI
  • ENFORCED_CONS
  • IMPLICIT
  • REGENERATETS
No
SYSINDEXPART catalog table New columns:
  • DSSIZE
  • PAGENUM
  • LIMITKEY_EXTERNAL 1

Changed column: SPACE.

No
SYSKEYCOLUSE catalog table Changed column: PERIOD. No
SYSKEYS catalog table Changed column: PERIOD. No
SYSKEYTGTDIST catalog table Changed column: NUMKEYS. No
SYSLEVELUPDATES catalog table New table.

New indexes:

  • DSNLVX01
  • DSNLVX02
No.
SYSPENDINGDDL catalog table New columns:
  • REORG_SCOPE_LOWPART
  • REORG_SCOPE_HIGHPART

Changed column: PARTITION.

No
SYSPACKAGE catalog table New columns:
  • ORIGIN
  • APREUSE_NO_FL
  • APREUSE_NO_TS
  • CONC_STMT
  • FUNCTION_LVL

Changed columns:

  • TYPE
  • HOSTLANG
  • VERSION
  • DEFERPREPARE
  • APPLCOMPAT
  • KEEPDYNAMIC
No
SYSPACKAUTH catalog table New columns:
  • SYS_START1
  • SYS_END1
  • TRANS_START1
No
SYSPACKCOPY catalog table
  • ORIGIN
  • APREUSE_NO_FL
  • APREUSE_NO_TS
  • CONC_STMT
  • FUNCTION_LVL
No
SYSPACKDEP catalog table Changed column: DTYPE No
SYSPACKSTMT catalog table New columns:
  • QUERYID
  • QUERY_HASH
  • QUERY_HASH_VERSION
No
SYSPLANAUTH catalog table New columns:
  • SYS_START1
  • SYS_END1
  • TRANS_START1
No
SYSQUERY catalog table New index: DSNQYX04. No
SYSRELS catalog table Changed column: CHECKEXISTINGDATA. No
SYSRESAUTH catalog table New columns:
  • SYS_START1
  • SYS_END1
  • TRANS_START1
No
SYSROUTINES catalog table New columns:
  • WRAPPED
  • REGENERATETS
No
SYSSCHEMAAUTH catalog table New columns:
  • SYS_START1
  • SYS_END1
  • TRANS_START1
No
SYSSEQUENCEAUTH catalog table New columns:
  • SYS_START1
  • SYS_END1
  • TRANS_START1
No
SYSSEQUENCES catalog table Changed column: MAXASSIGNEDVAL. Yes
SYSSESSION catalog table New table.

New index: DSNSNX02.

No
SYSSESSION_EX catalog table New table.

New indexes:

  • DSNSNX04
  • DSNSNX05
No
SYSSESSION_GV catalog table New table for LOB data in the GLOBAL_VARIABLES column of the SYSSESSION table.

New index: DSNSNX01.

No
SYSSESSION_DATA catalog table New table for LOB data in the DATA column of the SYSSESSION_EX table.

New index:

No
SYSSESSION_STATUS catalog table

New index: DSNSNX06.

No
SYSTABAUTH catalog table New columns:
  • UNLOADAUTH
  • SYS_START 1
  • SYS_END 1
  • TRANS_START 1
No
SYSTABCONST catalog table Changed column: TYPE. No
SYSTABLEPART catalog table New columns:
  • TYPE
  • PAGENUM
  • BPOOL
  • PGSIZE
  • DSSIZE
  • MEMBER_CLUSTER
  • COMPRESSRATIO

Changed column: SPACE.

No
SYSTABLESPACE catalog table New columns:
  • PAGENUM
  • PQTY
  • STORTYPE
  • STORNAME
  • VCATNAME
  • FREEPAGE
  • PCTFREE
  • COMPRESS
  • COMPRESSRATIO
  • GBPCACHE
  • TRACKMOD
  • SECQTYI
  • PCTFREE_UPD
  • PCTFREE_UPD_CALC
  • INSERTALG
No
SYSTABLES catalog table New column: REGENERATETS.

Changed column: TABLESTATUS.

No
SYSTABLESPACESTATS catalog table New columns:
  • GETPAGES
  • SYS_START
  • SYS_END
  • TRANS_START

Changed column:

No
SYSSTATFEEDBACK catalog table Changed column: REASON. No
SYSTRIGGERS catalog table New columns:
  • ACTIVE
  • ALTEREDTS
  • ASUTIME
  • DEBUG_MODE
  • ORIGINAL_CONTOK
  • REGENERATETS
  • SQLPL
  • VERSION
  • WLM_ENVIRONMENT
  • WRAPPED
No
SYSUSERAUTH catalog table New columns:
  • SYS_START1
  • SYS_END1
  • TRANS_START1
No
SYSVARIABLES catalog table Changed columns:
  • DATATYPEID
  • SOURCETYPEID
  • DEFAULT
  • LENGTH
  • CCSID

New index: DSNSNX06.

No
SYSVIEWDEP catalog table New columns:
  • SYS_START1
  • SYS_END1
  • TRANS_START1
No
SYSIBM.SYSTABSPACESTATS_H catalog table New table. See Temporal versioning for Db2 catalog tables. No
SYSAUDITPOLICIES_H catalog table New unused table. No
SYSCOLAUTH_H catalog table New unused table. No
SYSCONTEXT_H catalog table New unused table. No
SYSCONTEXTAUTHID_H catalog table New unused table. No
SYSCONTROLS_H catalog table New unused table. No
SYSCTXTTRUSTATTR_H catalog table New unused table. No
SYSDBAUTH_H catalog table New unused table. No
SYSPACKAUTH_H catalog table New unused table. No
SYSPLANAUTH_H catalog table New unused table. No
SYSRESAUTH_H catalog table New unused table. No
SYSROUTINEAUTH_H catalog table New unused table. No
SYSSCHEMAAUTH_H catalog table New unused table. No
SYSSEQUENCEAUTH_H catalog table New unused table. No
SYSTABAUTH_H catalog table New unused table. No
SYSUSERAUTH_H catalog table New unused table. No
SYSVIEWDEP_H catalog table New unused table. No
TEMPORAL_LOGICAL_TRANSACTION_TIME New built-in global variable. No
TEMPORAL_LOGICAL_TRANSACTIONS New built-in global variable. No
Notes:
  1. Reserved for future IBM use.

End general-use programming interface information.