Catalog changes in Db2 13

You can use this information to plan for catalog changes at migration to Db2 13 and in continuous delivery.

Tip: The changes in the highest available function levels are listed first. If you are preparing for migration to Db2 13, start with Directory changes in function level 500 and Catalog changes in catalog level V13R1M501.

Db2 13 catalog levels

The catalog level of a data sharing group or subsystem indicates that a particular CATMAINT utility UPDATE LEVEL job was run on the Db2 catalog, and the data sharing group or Db2 subsystem is ready for the activation of certain function levels.

Db2 13 uses the following Db2 catalog levels:
  • V13R1M509
  • V13R1M508
  • V13R1M507
  • V13R1M505
  • V13R1M504
  • V13R1M501
  • V13R1M100

For more information, see Function levels and related levels in Db2 13.

Catalog changes for function levels V13R1M502 and higher

The following catalog changes take effect in Db2 13 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?
V13R1M509 Non-UTS table spaces Non-UTS catalog table spaces can be converted to UTS. No
V13R1M509 SYSTABLESPACESTATS catalog table

New column: NSYNCREADIO

 No
V13R1M509 SYSINDEXSPACESTATS catalog table

New column: NSYNCREADIO

 No
V13R1M509 SYSPACKSTMT catalog table

New columns:

  • STMT_HASHID2
  • STMT_HASH2VER
 No
V13R1M509 SYSPACKSTMTCOPY catalog table

New columns:

  • STMT_HASHID2
  • STMT_HASH2VER
 No
V13R1M509 SYSUTILITIES catalog table

New column: SHRLEVEL

  
V13R1M508 New DEFINE NO table spaces

Many new tables spaces are created with the DEFINE NO attribute in preparation for the eventual conversion of remaining non-UTS catalog and directory objects. The conversion process for the non-UTS catalog and directory objects is enabled starting in function level 509. For a list of these table spaces, see Conversion details for non-UTS catalog and directory objects.

 No
V13R1M508 SYSINDEXES catalog table
  • The data type of the DSSIZE column is changed from INTEGER to BIGNIT.
  • Db2 starts populating the DSSIZE and PIECESIZE columns for indexes that are created on UTS objects.
  • After the catalog level is updated to V13R1M508 or higher, the following one-time CATMAINT job can be run to consistently populate the DSSIZE column values for all indexes on tables in UTS and LOB table spaces:
    CATMAINT UPDATE UNLDDN UPDDSIZE
 No
V13R1M508 SYSINDEXPART catalog table
  • The data type of the DSSIZE column is changed from INTEGER to BIGNIT.
  • Db2 starts populating the DSSIZE column for indexes that are created on UTS objects.
  • After the catalog level is updated to V13R1M508 or higher, the following one-time CATMAINT job can be run to consistently populate the DSSIZE column values for all indexes on tables in UTS and LOB table spaces:
    CATMAINT UPDATE UNLDDN UPDDSIZE
 No
V13R1M508 SYSTABLESPACE catalog table The USAGE column is added. The column value applies to work file table spaces only. The value indicates whether the table space is used for declared temporary tables or scrollable cursors, or for all other sort operations. No
V13R1M507 SYSCOPY catalog table The following columns are updated:
  • A new value, TTYPE='PBR' means that the table was altered from range-based partitioning to the growth-based partitioning scheme (PBR to PBG).
  • When ICTYPE='A', STYPE='T', TTYPE='PBR', the LOWDSNUM column value is the number of partitions for the partition-by-range table space before the conversion
  • When ICTYPE='A', STYPE='T', TTYPE='PBR', the HIGHDSNUM column value is always 1.
 No
V13R1M507 SYSPLAN catalog table

Db2 populates the LASTUSED column in the SYSIBM.SYSPLAN catalog table when a plan is created and after it is used. The LASTUSED value is set to '0001-01-01' when the plan is created, and the value is updated within 24 hours after the plan is used while real time statistics are enabled, which means that the DISABLE_EDMRTS subsystem parameter is set to NO. The following commands and operations preserve the existing LASTUSED value:

  • BIND REPLACE
  • REBIND
  • Automatic rebinds
 No
V13R1M507 SYSTIME_PERIOD_ADJUST built-in global variable

The SYSTIME_PERIOD_ADJUST built-in global variable is added. It contains a string value that indicates the action that Db2 takes when a history row for a system-period temporal table is generated with an end timestamp that is less than the begin timestamp.

 No
V13R1M505 Security-related catalog history tables FL 505 Temporal support for security-related catalog tables is implemented by the following changes:
  • The addition of new catalog history tables for SYSIBM.SYSROLES and SYSIBM.SYSVARIABLEAUTH.
  • The enablement of existing catalog history tables for security-related catalog tables. The names of these new tables end in _H. For a complete list of these tables, see Security-related catalog history tables.
    Note: The history tables and the associated table spaces for all the security-related catalog tables other than SYSROLES and SYSVARIABLEAUTH are dropped and re-created.
  • Changed columns in security-related catalog tables:
    • SYS_START
    • SYS_END
    • TRANS_START
  • New column in security-related catalog tables:
    • GEN_SESSION_USER
 No
V13R1M504 SYSIBM. SYSOBJEVENTS catalog table
  • New table
  • New table space: DSNDB06.SYSTSOEV
  • New indexes: SYSIBM.DSNOEH01 and SYSIBM.DSNOEH02
 No
V13R1M504 SYSPACKAGE table The following column values are newly added or changed:
  • OPERATIVE='R' is added indicates a package in rebind-advisory status because autobind phase-in has failed. If the package is allocated and any invalidated statements are executed. they are incrementally bound before execution. An explicit rebind is recommended to make the package valid and operative.
  • VALID='S' changes to indicate that the package can be allocated without waiting for the autobind process, and any invalidated statements are incrementally bound before execution.
 No
V13R1M504 SYSPACKSTMTDEP table FL 504 This table can contain additional records for phased-out package copies that are generated by autobind phase-in processing.  
V13R1M501 SYSPACKAGE catalog table FL 502 New possible column value for statement-level invalidation: VALID='S' Yes
V13R1M501 SYSPACKCOPY catalog table FL 502 New possible column value for statement-level invalidation: VALID='S Yes
V13R1M501 SYSCOLUMNS table FL 503 The DEFAULTVALUE column value for newly added ROW CHANGE TIMESTAMP columns is set to a constant value derived from the timestamp of the ALTER TABLE statement that added the column. No

Catalog changes in catalog level V13R1M501

The following catalog changes take effect in Db2 13 when you tailor the Db2 catalog for function level 501 activation.

Catalog object Change introduced Incompatible change?
DSNKDX02 catalog index This index is removed. No
SYSCOPY table New column: EVENTID No
SYSINDEXSPACESTATS table Changed columns:
  • COPYCHANGES
  • EXTENTS
  • REORGAPPENDINSERT
  • REORGDELETES
  • REORGINSERTS
  • REORGPSEUDODELETES
  • SPACE
  • STATSDELETES
  • STATSINSERTS

New columns:

  • REORGEXCSPLITS
  • REORGSPLITTIME
  • REORGTOTALSPLITS
 No
SYSPACKAGE table
New column: DEPLEVEL
Changed column: VALID
 No
SYSPACKCOPY table
New column: DEPLEVEL
Changed column: VALID
 No
SYSPACKSTMT table
New column: VALID
 No
SYSPACKSTMTCOPY table
New table
New table space: DSNDB06.SYSTSPSC
New indexes:
  • DSNKTX01
  • DSNKTX02
 No
SYSPACKSTMTDEP table
New table
New table space: DSNDB06.SYSTSPSD
New indexes:
  • DSNKNX01
  • DSNKNX02
Tip: FL 502 Db2 starts populating starts this table at application compatibility level V13R1M502 or higher.
Tip: FL 504 This enhancement primarily introduces infrastructure to support a new capability that becomes available after you activate function level 504 or higher in Db2 13. For more information see Enabling autobind phase-in for packages invalidated at the statement level.
 No
SYSTABLESPACESTATS catalog table Changed columns:
  • COPYCHANGES
  • EXTENTS
  • REORGDELETES
  • REORGINSERTS
  • REORGUPDATES
  • STATSDELETES
  • STATSINSERTS
  • STATSUPDATES
 No
SYSUTILITIES table
New table
New table space: DSNDB06.SYSTSUTL
New indexes:
  • DSNULX01
  • DSNULX02
 No
SYSIBM.DSNSEQ_EVENTID sequence A Db2-supplied sequence that is intended to be used to generate a value for the EVENTID column when a row is inserted into the SYSIBM.SYSUTILITIES catalog table. No

For more information about these changes, see Function level 501 (Db2 13 installation or migration - May 2022).

Directory changes in function level 500

Function level 500 introduces the following directory changes:
SPT01 and SYSLGRNX table spaces are converted to DSSIZE 256 GB
Starting in function level 500, the first time that the REORG TABLESPACE utility runs for the following directory objects, it converts the DSSIZE to 256 GB.
  • DSNDB01.SPT01 to resolve issues that are related to the removal of the SPT01_INLINE_LENGTH subsystem parameter by APAR PH24358 in Db2 12.
  • DSNDB01.SYSLGRNX in anticipation of future growth in this table for increasing workloads and conversions of non-UTS table space to UTS.

The conversion is automatic and does not require any special utility syntax. It updates the following Db2 catalog table values for each table space:

  • The SYSIBM.SYSTABLESPACE and SYSIBM.SYSTABLEPART catalog tables are updated with DSSIZE = '256G'.
  • A SYSCOPY record is inserted for the table space, with the following values to indicate that REORG changed the DSSIZE: ICTYPE = 'A', STYPE = 'D', TTYPE = '64G'. In this situation, the TTYPE value records the previous DSSIZE.

If function level 100* is activated, already converted table spaces continue to use the larger DSSIZE, but the REORG utility does not convert unconverted table spaces.

Recovery to a point-in-time (PIT) before REORG converted the DSSIZE reverts the DSSIZE to 64GB. As always, if any one of the catalog or directory objects are recovered to a prior PIT, it is best to recover all catalog and directory objects to the same PIT.

Catalog changes in catalog level V13R1M100

No catalog changes take effect when you tailor the Db2 catalog for migration to Db2 13 function level 100.

Conversion details for non-UTS catalog and directory objects

When the CATMAINT utility updates the catalog level to function level 508 or higher, it also creates new universal table spaces (UTSs) with the DEFINE NO attribute for the catalog and directory objects listed in the following tables.

Note: The conversion process for the non-UTS catalog and directory objects is enabled starting in function level 509.
Table 1. UTS conversion details for Db2 catalog tables
Non-UTS table space Table name UTS name Attributes
SYSHIST SYSCOLDIST_HIST SYSTSCDH BP8K0
SYSHIST SYSCOLUMNS_HIST SYSTSCLH BP8K0
SYSHIST SYSINDEXES_HIST SYSTSIXH BP8K0
SYSHIST SYSINDEXPART_HIST SYSTSIPH BP8K0
SYSHIST SYSINDEXSTATS_HIST SYSTSINH BP8K0
SYSHIST SYSKEYTARGETS_HIST SYSTSKTH BP8K0
SYSHIST SYSKEYTGTDIST_HIST SYSTSKDH BP8K0
SYSHIST SYSLOBSTATS_HIST SYSTSLSH BP8K0
SYSHIST SYSTABLEPART_HIST SYSTSTPH BP8K0
SYSHIST SYSTABLES_HIST SYSTSTHI BP8K0
SYSHIST SYSTABSTATS_HIST SYSTSSTH BP8K0
SYSSTATS SYSCOLDIST SYSTSDIS BP16K0
SYSSTATS SYSCOLDISTSTATS SYSTSDST BP16K0
SYSSTATS SYSCOLSTATS SYSTSCST BP16K0
SYSSTATS SYSINDEXSTATS SYSTSXST BP16K0
SYSSTATS SYSKEYTARGETSTATS SYSTSKTS BP16K0
SYSSTATS SYSKEYTGTDIST SYSTSKTD BP16K0
SYSSTATS SYSKEYTGTDISTSTATS SYSTSKDS BP16K0
SYSSTATS SYSLOBSTATS SYSTSLOB BP16K0
SYSSTATS SYSTABSTATS SYSTSTST BP16K0
SYSDDF IPLIST SYSTSIPL BP0
SYSDDF IPNAMES SYSTSIPN BP0
SYSDDF LOCATIONS SYSTSLOC BP0
SYSDDF LULIST SYSTSLUL BP0
SYSDDF LUMODES SYSTSLUM BP0
SYSDDF LUNAMES SYSTSLUN BP0
SYSDDF MODESELECT SYSTSMOD BP0
SYSDDF USERNAMES SYSTSUSN BP0
SYSJAVA SYSJARCONTENTS SYSTSJRC BP0
SYSJAVA SYSJAROBJECTS SYSTSJRO BP0
SYSJAVA SYSJAVAOPTS SYSTSJVO BP0
SYSJAVA SYSJAVAPATHS SYSTSJPH BP0
SYSCONTX SYSCONTEXT SYSTSCTX BP16K0
SYSCONTX SYSCONTEXTAUTHIDS SYSTSCAH BP16K0
SYSCONTX SYSCTXTTRUSTATTRS SYSTSTRA BP16K0
SYSEBCDC SYSDUMMYE SYSTSDME BP0 CCSID EBCDIC
SYSEBCDC SYSDUMMY1 SYSTSDUM BP0 CCSID EBCDIC
SYSGRTNS SYSROUTINES_OPTS SYSTSGRO BP8K0
SYSGRTNS SYSROUTINES_SRC SYSTSGRS BP8K0
SYSROLES SYSOBJROLEDEP SYSTSRDP BP16K0
SYSROLES SYSROLES SYSTSROL BP16K0
SYSSEQ2 SYSSEQUENCEAUTH SYSTSSQA BP0
SYSSEQ2 SYSSEQUENCESDEP SYSTSSQD BP0
SYSXML SYSXMLRELS SYSTSXRL BP8K0
SYSXML SYSXMLSTRINGS SYSTSXSG BP8K0
SYSALTER SYSOBDS SYSTSOBD BP32K
SYSGPAUT SYSRESAUTH SYSTSRES BP0
SYSSEQ SYSSEQUENCES SYSTSSEQ BP0
SYSTARG SYSKEYTARGETS SYSTSKYT BP16K0
SYSTSASC SYSDUMMYA SYSTSASC (same) BP0 CCSID ASCII
SYSTSUNI SYSDUMMYU SYSTSUNI (same) BP0
SYSTSXTM SYSXMLTYPMOD SYSTSXTM (same) BP0
SYSTSXTS SYSXMLTYPMSCHEMA SYSTSXTS (same) BP0
SYSUSER SYSUSERAUTH SYSTSUSR BP0

The following table shows the details for converting Db2 directory table spaces to UTS.

Table 2. UTS conversion details for Db2 directory tables
Table name Original table space name UTS name Attributes
SYSUTL SYSUTILX 1 SYSTSUTM BP32K
SYSUTILX SYSUTILX 1 SYSTSUTX BP32K
SCTR SCT02 SYSTSSCT BP0, LOCKSIZE PAGE
Notes:

  1. The SYSUTILX directory table space must be empty when you convert it to UTS, which means that no active or stopped Db2 utilities are in progress. Otherwise, the conversion process fails, and Db2 issues the DSNU330I message.