Working storage is composed of:
While you do not have direct control over how much storage the database manager and its control blocks use (refer to DB2 Server for VM System Administration under "virtual storage requirements"), you can control the amount of storage used by the directory buffers, the local buffers, and by data space pages.
The amount of storage used by the directory buffers is NDIRBUF*560 bytes, where NDIRBUF is the number of directory buffers. This applies whether you are using Data Spaces Support or not. The storage used by the local buffers is NPAGBUF*4144 bytes. (Each buffer page requires 48 bytes of overhead. For example a 4KB page requires 4096+48 bytes or 4144 bytes of storage.) By reducing or increasing the number of directory and local buffers you are using you can reduce or increase your working storage.
There are five parameters to help you manage the number of data space pages in main and expanded storage that your database machine uses.
These parameters are discussed in the following sections.
The target working storage parameter (TARGETWS) helps you to balance the amount of main and expanded storage used by your database machine, with the amount used by other virtual machines in your VM system.
If you set TARGETWS too low, you may unnecessarily restrict the amount of available storage your database machine can use. You may also find that the operating system does not release pages fast enough and your current storage size always exceeds your target. If this happens, some working storage residence priorities are not effective. Remember, the database manager starts releasing most pages when the target working storage size is reached: if your current storage size is always greater than your target, the database manager only keeps those pages with a residence priority of 4 or 5. In this case, pages with any other priority will not be differentiated.
If you set it too high, your database machine may never reach the target you set. VM may not give your database machine the amount of main and expanded storage it asks for. You may find that the operating system restricts your working storage size before the database manager does. If this happens, the database manager only releases those pages with a residence priority of 1 or 2.
Once you find an acceptable target working storage size, it is important not to let your current size exceed it by too much. If it does it can have the same effect as setting TARGETWS too high (VM restricts storage). If your working storage is too high, it means that you are either:
For information on managing modified pages, refer to Managing Checkpoints, and for information on unmapped pages refer to Unmapped Internal Dbspaces. For more information about how the TARGETWS mechanism works, refer to Appendix C, Why is the TARGETWS Value Frequently Exceeded?.
If you set a realistic target working storage size (large enough to be effective but not so large as to overload the operating system), you will be able to use storage residence priorities to favor pages from certain storage pools. When the database manager copies a page from a data space into its buffers, it checks the residence priority of that page. At the default value of 3, it releases the data space page from main and expanded storage if the current working storage size is greater than the target. However, the buffer page stays in the buffer pool until the database manager needs the space for a new page.
For most applications, the default priority should be correct. However, if you can identify certain storage pools as "high priority" pools that contain performance-critical dbspaces, you can favor them by assigning them a high residence priority. Low priority pools can be assigned a low residence priority.
You can assign one of five storage residence priorities:
Table 13 summarizes the five storage residence priorities. An
R indicates that the database manager releases a page from main and
expanded storage after it has been moved to a local buffer.
Table 13. Storage Residence Priorities
| Page Type | Current Working Storage Size | Working Storage Residence Priority | ||||
|---|---|---|---|---|---|---|
| 1 | 2 | 3 | 4 | 5 | ||
| Data | <= target | R | R |
|
|
|
| > target | R | R | R | R |
| |
| Index | <= target | R |
|
|
|
|
| > target | R | R | R |
|
| |
Whether you are using mapped or unmapped internal dbspaces also affects your current working storage size. (The operating system controls it by moving pages from main storage to and from system paging DASD.) However, unmapped pages are included in your total current working storage size, and can inflate it beyond your target.
For example, if you are performing operations that use large amounts of internal dbspace storage (creating large indexes, or sorting large tables), you may fill unmapped internal dbspaces with pages that are not released until the index or sort is complete. Even if this increases your current working storage above your target, the database manager will not release internal dbspace pages internal dbspace pages when it no longer needs the internal dbspace; frequently not until the end of a logical unit of work.