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 values in these fields determine the following:
  • 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.

Important: Start of changeDb2 12 uses more 32KB work files than previous Db2 releases. For migration to Db2 12, you might need to increase your 32KB work file allocations for work file data sets and work file buffer pools.End of change
Recommendation: If you have not already done so, drop work file table spaces for temporary work and re-create them as partition-by-growth table spaces. (Existing table spaces cannot be altered to become partition-by-growth table spaces.)

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.

Figure 1. Work file database panel: DSNTIP9
 DSNTIP9         INSTALL DB2 - WORK FILE DATABASE
 ===> 

Enter options for creating sort work table spaces:
 4K SORT TABLE SPACE:
  1  4K SORT PRIMARY SPACE ===> 20         Total primary space (MB)
  2  4K SORT WORK TS’S     ===> 1          Number of table spaces to create
  3  4K SORT WORK SEG SIZE ===> 16         Segment size
 32K SORT TABLE SPACE:
  4  32K SORT PRIMARY SPACE===> 20         Total primary space (MB)
  5  32K SORT WORK TS’S    ===> 1          Number of table spaces to create
  6  32K SORT WORK SEG SIZE===> 16         Segment size
Enter options for creating temporary (DGTT) work file table spaces:
 4K TEMP TABLE SPACE:
  7  4K TEMP PRIMARY SPACE ===> 20         Total primary space (MB)
  8  4K TEMP WORK TS’S     ===> 1          Number of table spaces to create
  9  4K TEMP WORK SEG SIZE ===> 16         Segment size
 32K SORT TABLE SPACE:
 10  32K TEMP PRIMARY SPACE===> 20         Total primary space (MB)
 11  32K TEMP WORK TS’S    ===> 1          Number of table spaces to create
 12  32K TEMP WORK SEG SIZE===> 16         Segment size





 PRESS:   ENTER to continue   RETURN to exit   HELP for more information