With function level 508 in Db2 13 for z/OS, you can use new option keywords in CREATE TABLESPACE statements to easily control how Db2 uses specific table spaces in a work file database. The two new syntax keywords, FOR SORT and FOR DGTT, can be used only when creating partition-by-growth (PBG) UTS table spaces in a work file database, and the application compatibility level must be V13R1M508 or higher when a CREATE TABLESPACE statement specifies the new keywords.
FOR SORT specifies that the table space is used for processing other than declared global temporary table (DGTT) work, such as sort, joins, created global temporary tables, query parallelism, trigger transition tables, and so forth.
FOR DGTT specifies that the table space is used for DGTT work and processes that use internal temporary tables, such as scrollable cursors and INSTEAD OF triggers.
This enhancement simplifies the process of creating and managing table spaces in the work file database. Before this enhancement, DBAs need to understand the behavior of the WFDBSEP subsystem parameter, in combination with sometimes esoteric rules for other table space attributes, often resulting in confusion. Also, a requirement that segmented tables spaces used for sort were must be defined with a secondary allocation quality set to 0, which required Db2 to locate separate table spaces when the storage reached the primary allocation spaced was used up.
A new USAGE column in the SYSIBM.SYSTABLESPACE catalog table when these keywords were specified for a PBG UTS. USAGE=’S’ when FORT SORT was specified and USAGE=’D’ when FOR DGTT was specified. The value is blank if neither keyword was specified or the table space is not in a work file database.
The -DISPLAY DATABASE command output is also updated with information about these keywords with new TYPE values for these keywords: WS for a PBG UTS created with FOR SORT, WD for a PBG UTS created with FOR DGTT, and WF for any non-UTS work file table space.
These new syntax keywords and simplified rules enable DBAs to more easily create table spaces and designate their specific usage. For more information about this new capability for managing table spaces in the work file databases, see:
#Db2forz/OS