Work file database storage requirements

The work file database is used as storage for processing SQL statements that require working storage.

Work file storage is used for sorting, RID pool overflow to work files, and for temporary tables and common table expressions.

Work file usage varies, depending on the number of operations that require work files, and the number of concurrent users that require work files. You can monitor work file usage by gathering Db2 statistics traces. Use the high water mark value for work file usage from the traces as a guideline for determining the amount of space to allocate for work files.

If you are installing Db2, see Storage requirements for installation.

Storage requirements for migration

You might need more storage for the work file database if you have a large amount of data to sort and a large amount of concurrent sort activity. If you are sorting compressed data, allow for the same amount of storage that you would need if the data were not compressed. The maximum amount of storage that you need is enough to satisfy the requirements of the largest combination of concurrent activities that use the work file database. The amount of storage that is required for a sort depends on the following variables:

  • Data size
  • Sort key size

You can estimate the total amount of work file space that is needed to perform the sort as follows:

  • Let MIN be the operation of selecting the lowest value from a set of values.
  • Let FLOOR be the operation of discarding the decimal portion of a real number.
  • Let CEILING be the operation of rounding a real number up to the next-highest integer.
  • Let Data be the total data length in bytes.
  • Let Key be the total length of the sort key.
  • Let Row-overhead be the 6-byte record header plus the 2-byte record page map entry.
  • Let Prefix be the 16-byte header.
  • Let Rows be the total number of rows that are being sorted.
  • Let Segsize be the segment size.
Then calculate as follows: 
Records per page = MIN(MAXROWS, FLOOR (4056 / (Data + Key + Row-overhead)))

Total pages = CEILING (Rows / Records per page)

Total segments = CEILING (Total pages / Segsize)

Total pages used = Total segments + Segsize)

The number of records per page cannot exceed 255 (the value of MAXROWS).

This result tells you how much storage is needed in the work file database after sort processing. However, if a merge phase was required during sort processing, an additional intermediate copy of the records might exist at any given time. For most subsystems, you can assume that about half of the records that are involved in a sort have two copies. Therefore, a multiplier value of 1.5 is safe. If you want to be conservative, choose 2 for your multiplier value. Therefore, the amount of storage that is used in the work file database during sort processing can vary in the range 1–2 times the storage that is needed after sort processing. The actual storage that is used might also increase if you have little available buffer pool storage.

When a large object (LOB) column is part of a result table, and the result table must be placed in a work file for sorting, the actual LOB column data is not placed in the work file. Therefore, LOB columns do not require large increases in the amount of work file space that Db2 requires. For work file calculations, you can assume 51 bytes of storage per LOB column for the work file.

However, if a large object (LOB) column is defined with the INLINE attribute and the result table must be placed in a work file, the inline LOB data will be placed in the work file. Therefore, work file calculations need to account for the length of the inline LOB.

To determine the number of tracks that are needed, convert the number of pages into bytes, and divide the result by the number of bytes per unit. Let r be the number of 4096-byte records per track, and let safety_factor be a number from 1.5 to 2.0. For 3390 devices, r is 12. 
Tracks = CEILING (Total pages used / r) * safety_factor

Example 1: Consider a table (TABLE1) that contains 45,327 rows, for which you want to create a nonunique padded index on COL1 CHAR(3) NOT NULL, COL2 CHAR(4), COL3 VARCHAR(20), and COL4 SMALLINT. Determine the amount of temporary storage that Db2 needs to create this index as follows:

  • Data = 3 + (4 + 1) + (20 + 1) + (2 + 1) + 4 = 36
  • Key = 36 (Data plus RID is key for CREATE INDEX)
  • Rows = 45,327
  • Record overhead = 8
  • Records per page = MIN(MAXROWS, FLOOR (4056 / (36 + 36 + 12))) = 48
  • Total pages = CEILING (45,327 / 50) = 907
  • Segments = CEILING (907 / 24) = 38
  • Total pages used = CEILING (38 * 24) = 912
  • Tracks = CEILING (912 / 12) * 1.5 = 114

Example 1 is a data page calculation for storing index keys in the work file database. For this example, 114 tracks of a 3390 storage device are needed. The 2-byte length field of a VARCHAR column is not a part of Data for CREATE INDEX. The RID field is a part of Data, and the Key includes the entire Data portion, including the RID.

Example 2: Consider TABLE1 again and the following SQL query:

Begin general-use programming interface information.
SELECT COL1,COL2,COL3,COL4
   FROM TABLE1
   ORDER BY COL2,COL3,COL1;
End general-use programming interface information.

This query, which includes an ORDER BY clause, requires a sort. Determine the amount of temporary storage that is required for this table as follows:

  • Data = 3 + (4 + 1) + (20 + 2 + 1) + (2 + 1) = 34
  • Key = (4 + 1) + (20 + 1) + 3 = 29
  • Rows = 45,327
  • Record overhead = 8
  • Segsize = 24
  • Records per page = MIN(MAXROWS, FLOOR (4056 / (34 + 29 + 12))) = 54
  • Total pages (final result) = CEILING (45,327 / 57) = 796
  • Segments (final result) = CEILING (796 / 24) = 34
  • Total pages used = CEILING (34 * 24) = 816
  • Tracks = CEILING (816 / 12) * 1.5 = 102
  • Total pages (during processing) = CEILING (1.5 * 796) = 1194
  • Segments (during processing) = CEILING (1.5 * 34) = 51
  • Total pages used (during processing) = CEILING (51 * 24) = 1224
  • Tracks (during processing) = CEILING (1224 / 12) * 1.5 = 153

For this example, which is a table calculation, 102 tracks of a 3390 storage device are needed. The 2-byte length field of a VARCHAR column is a part of Data, and the Key does not include the entire Data portion.

You can use the sort summary trace record, IFCID 96, to simplify some of the calculations. This record shows the number of records that are sorted, the sort record size (Data + Key), and an indication of whether a merge phase was required for an individual sort request.

Also note that if the overall record length (Data + Key + Prefix) exceeds 100 bytes, Db2 attempts to create the work file in a table space with a 32 KB page size. If the overall record length is 100 bytes or less, Db2 uses a table space with a 4 KB page size.

Storage requirements for installation

If you are installing Db2, use the following calculations to determine either PRIQTY (primary space allocation) or DSSIZE and MAXPARTITION values for creating work file table spaces. During installation, you must create at least one 4 KB work file table space and one 32 KB work file table space.

For 4 KB work file table spaces:
  • Let total space be the total amount of space in megabytes (from the TEMP 4K SPACE field on panel DSNTIP9.
  • Let table spaces be the number of table spaces (from the TEMP 4K TBL SPACES field on panel DSNTIP9.
  • Let CEILING be the operation of rounding a real number up to the next-highest integer.
  • Let GB per table space be the amount of space per work file table space in gigabytes.
Calculate the following:
GB per table space = CEILING (total space / table spaces / 1024)

If GB per table space < 1 GB, then PRIQTY = CEILING (total space / table spaces
If GB per table space ≤ 16 384 GB, then DSSIZE = 4 GB 
	and MAXPARTITIONS = GB per table space / 4
Otherwise, DSSIZE = 4 GB and MAXPARTITIONS = 4096
For 32 KB work file table spaces:
  • Let total space be the total amount of space in megabytes (from the TEMP 32K SPACE field on panel DSNTIP9.
  • Lettable spaces be the number of table spaces (from the TEMP 32K TBL SPACES field on panel DSNTIP9.
  • Let CEILING be the operation of rounding a real number up to the next-highest integer.
  • Let GB per table space be the amount of space per work file table space in gigabytes.
Calculate the following:
GB per table space = CEILING (total space / table spaces / 1024)

If GB per table space < 1 GB, then PRIQTY = CEILING (total space / table spaces)
If GB per table space ≤ 16 384 GB, then DSSIZE = 4 GB 
	and MAXPARTITIONS = GB per table space / 4
If GB per table space ≤ 131 072 GB, then DSSIZE = 32 GB 
	and MAXPARTITIONS = GB per table space / 32
Otherwise, DSSIZE = 32 GB and MAXPARTITIONS = 4096