DSNTIP9: Work file database panel
The fields on the DSNTIP9 panel configure the 4 KB and 32 KB page size table spaces in the Db2 work file database.
- The total amount of space that is available for each type of table space.
- The number of table spaces that are to be created during the installation or migration process for each type of table space. In INSTALL mode, you must specify at least 1 for each of the 4 types of work file table spaces: 4 KB sort work, 32 KB sort work, 4 KB temporary work, and 32 KB temporary work. In MIGRATE mode, you can specify 0 for any or all of these table spaces.
- The segment size of each type of table space.
The settings on this panel are used to tailor installation job DSNTIJTM, which creates the work file database. For more information about job DSNTIJTM, see Installation step 18: Create default storage group, define temporary work files, and bind Db2 REXX Language Support: DSNTIJTM. To determine the storage requirements for work file database table spaces, see Work file database storage requirements.
Sort work table space in the work file database are used as temporary space for SQL statements and triggers that require working storage. In particular, this includes statements that use the following types of operations:
- GROUP BY or HAVING (without index)
- ORDER BY (without index)
- DISTINCT (without index)
- UNION (except UNION ALL)
- EXISTS (subselect)
- ANY (subselect)
- SOME (subselect)
- ALL (subselect)
- Some joins
Temporary work table spaces in the work file database are used for declared global temporary tables. They can increase in size beyond the primary allocation. When the value of the INSTALL TYPE field is INSTALL, temporary work table spaces are created as either partition-by-growth or segmented table spaces, depending on the amount of primary space per table space.
Unlike temporary work table spaces, sort work table spaces are always segmented table spaces and cannot increase in size.
You can add a work file table space or change the size of an existing one by deleting and redefining it. Db2 needs to be started for these activities and you do not need to stop the work file database. All Db2 users share the work file database table spaces. You cannot use utilities on the work file database table spaces.
You can create additional work file table spaces at any time, including during migration. Creating additional work file table spaces can improve Db2 performance by reducing device contention among applications that require working storage.
For temporary work only, the work file database can contain partition-by-growth table spaces to increase the available space for declared global temporary tables and reduce the possibility of receiving SQLCODE -904. A partition-by-growth table space can grow up to 128 TB. The maximum number of partitions to which the partition-by-growth table space can grow and the maximum size of the table space are determined by the MAXPARTITIONS and DSSIZE values that are specified for the table space.
Updating the parameters
You can alter the characteristics of the Db2 catalog, directory, work file databases, BSDS, and active and archive logs by using the methods described on Updating subsystem parameter and application default values.