Calculating working storage requirements

Working storage is private virtual storage in the Db2 database services address space, which is primarily above the 2 GB bar. Working storage is used to hold temporary data that is needed for the processing of an SQL request.

About this task

If you use static SQL, you have larger plan sizes, typically varying from 15 KB to 1 MB. Typical sites would use about 300 KB for each thread of working storage for dynamic SQL users, and 100 KB per thread for static SQL users. A thread is a structure that describes an application connection to Db2. The CLIST does not include information about open compressed table spaces. Compression dictionaries are stored above the 2-GB bar. Therefore, if you use compressed table spaces, you need additional storage.

Procedure

To estimate the amount of working storage:

  1. Start with 600 KB.
  2. Determine the amount of space in KB that is needed for active users.
    • Start with 11,920 bytes for each concurrent Db2 user. This value is specified by the CTHREAD subsystem parameter.
    • Add 11,920 bytes for each remote Db2 user. This value is specified by the MAXDBAT subsystem parameter.
    • Divide the total by 1,024 to convert to KB.
  3. Determine the amount of space in KB that is needed for inbound DDF connections.
    • Start with 298 bytes per connection.
    • Divide the number of bytes by 1,024 to convert to KB.
    • Subtract the result from the maximum number of concurrent inbound DDF connections. This value is specified by the CONDBAT subsystem parameter.
    • Multiply the result by 4.
  4. Estimate the working storage by adding the results from steps 1, 2, and 3:
    Start of change
    600+(((CTHREAD+MAXDBAT)*11920)/1024)+((CONDBAT-(MAXDBAT*298)/1024)*4)
    End of change

Example

The default, as calculated by the DSNTINST CLIST, is:
600+(((200+200)*11920)/1024)+((10000-(200*298)/1024)*4)=45024