Typically, you set up your database to be supported by multiple storage pools, so that you can control what data resides on what devices, and can manage physical DASD allocations differently for different data. The following sections discuss uses of storage pools and how to define them.
A storage pool consists of a large collection of 4-kilobyte DASD pages, called slots, for storing allocated public and private dbspace pages and shadow pages (old copies of dbspace pages that have changed since the last checkpoint). Dbspace pages that are not allocated are not stored. For internal dbspaces, slots are occupied only by nonempty pages of data for active logical units of work.
The placement of dbspace pages in storage pool slots is determined by the database manager; however, you control which pool of slots the dbspace pages are assigned to. This allows you to control device utilization and the use of different DASD allocation schemes for different data.
You may often choose to undercommit the actual DASD space available for the dbspaces. Because a dbspace cannot be extended after it is defined, and because it is really only a logical allocation of space, many dbspaces are defined to be much larger than needed. As a result, the actual storage pool slots required are fewer than the dbspace sizes imply. The number of dbextent pages should be defined to support the expected number of dbspace pages that will actually be used.
The undercommitting approach to managing storage pools is particularly useful if the tables involved are expected to grow over time. The sizes of the dbspaces are set based on how large the tables can grow, while the size of the storage pool is defined based on current storage requirements. As the tables grow, you can extend the storage pool by adding dbextents to it.
Undercommitting is also useful for supporting internal dbspaces. It is unlikely that you will ever need all the pages of all of the internal dbspaces at the same time. The number of internal dbspaces defined is based on the most the database manager would need at one time, and the size for each is defined based on the worst possible situation that could occur. (Note that internal dbspaces are all the same size.)
If you want to guarantee space availability, or have more dynamic dbspace storage requirements, you should overcommit the DASD space available for dbspaces. For example, you might want to do so to handle the storage requirements for private dbspaces. User requests for more or bigger dbspaces can be relatively frequent. Rather than repeatedly going through an ADD DBEXTENT operation, you could overcommit the storage pool for private dbspaces and handle the user requests through the ADD DBSPACE and ACQUIRE DBSPACE operations. (You may still have to run the ADD DBEXTENT operation, but not as often.) For overcommitting, allocate sufficient slots to handle all dbspace pages plus the potential shadow pages.
Storage pools enable you to control device and channel utilization through one of two basic approaches:
Two highly active dbspaces can be placed on different devices by assigning them to different storage pools and defining the dbextents of these storage pools on different devices.
A single highly active dbspace can be spread across multiple devices by defining its storage pool as small, multiple dbextents, each of which is a VSAM data set defined on a different device.
You can allocate a specific table and all its indexes to a specific device or VSAM data set. To do this, create the table in a dbspace with no other tables, assign that dbspace to its own storage pool, and define the dbextents of that pool as the VSAM data sets on the volume that you want.
Use the SHOW POOL command to display physical storage information about each storage pool defined, including:
You can issue the SHOW POOL command from either the operator console or from ISQL. For more information about it, refer to the DB2 Server for VSE & VM Operation manual. To see information about reusable deleted dbextent numbers, use the SHOW POOL DELETED command.
To maintain storage pools, you:
You add a storage pool to a database by adding a dbextent to a nonexistent storage pool, using the ADD DBEXTENT process described in Adding Dbextents to a Storage Pool.
If any of your storage pools are short on storage, you can use the ADD DBEXTENT process to increase their size.
You can use the DELETE DBEXTENT process to release DASD for other uses.
Dbextents can be added to a nonexistent storage pool (which defines a new storage pool), or to an existing storage pool (which increases the size of the storage pool) using the following two-step process:
These steps are described in more detail below.
Run the VSAM IDCAMS program to define the VSAM data sets. This step
allocates the DASD space and establishes the size of the dbextent. Table 14 shows an example of a job for defining three
dbextents.
Table 14. Example of a Job for Allocating Dbextent Data Sets
// DLBL IJSYSCT,'AMASTCAT',,VSAM
// EXEC IDCAMS,SIZE=AUTO
DEFINE SPACE -
(DEDICATE -
VOL(DBDISK7)) -
CAT(SQLCAT01/PASSWORD)
DEFINE CLUSTER -
(NAME(SQL.DDSK15.DBNAME01.DB) -
CNVSZ(4096) -
CYL(50) -
NONINDEXED -
VOL(DBDISK7) -
CONTROLPW(PASSWORD) -
RECSZ(4089) -
REUSE -
SHR(1)) -
CAT(SQLCAT01/PASSWORD)
DEFINE SPACE -
(DEDICATE -
VOL(DBDISK8)) -
CAT(SQLCAT01/PASSWORD)
DEFINE CLUSTER -
(NAME(SQL.DDSK16.DBNAME01.DB) -
CNVSZ(4096) -
CYL(20) -
NONINDEXED -
VOL(DBDISK8) -
CONTROLPW(PASSWORD) -
RECSZ(4089) -
REUSE -
SHR(1)) -
CAT(SQLCAT01/PASSWORD)
DEFINE CLUSTER -
(NAME(SQL.DDSK17.DBNAME01.DB) -
CNVSZ(4096) -
CYL(30) -
NONINDEXED -
VOL(DBDISK8) -
CONTROLPW(PASSWORD) -
RECSZ(4089) -
REUSE -
SHR(1)) -
CAT(SQLCAT01/PASSWORD)
/*
| ||
|
In this example, one dbextent data set called SQL.DDSK15.DBNAME01.DB is defined on volume DBDISK7, and two more, SQL.DDSK16.DBNAME01.DB and SQL.DDSK17.DBNAME01.DB, are defined on volume DBDISK8.
You can move dbextents between device types as long as the dbextent is not larger than the size of the device. When you define dbextents, you should keep this in mind. For example, if you defined a single dbextent of 600000 blocks on a 9335 device, you could not move that dbextent to a 9332 device which is limited to 360032 blocks. However, if you defined three dbextents, each of 200000 blocks, on a 9335 (for a total of 600000 blocks), you could move them to three 9332 devices.
If you are using cataloged procedures to include the DLBL statements for your database, you must update those procedures to include the DLBL statements for the new dbextents. For the example shown in Table 14, you would add the following three DLBL statements:
// DLBL DDSK15,'SQL.DDSK15.DBNAME01.DB',,VSAM
// DLBL DDSK16,'SQL.DDSK16.DBNAME01.DB',,VSAM
// DLBL DDSK17,'SQL.DDSK17.DBNAME01.DB',,VSAM
Run the ARIS250D procedure to add dbextents to the storage pool. This step updates the database directory to include the control information for the dbextents. If the dbextents are being added to a new storage pool, this procedure also defines the new storage pool as are being recoverable or nonrecoverable. Multiple dbextents can be defined in one run of each of these jobs. For a description of this procedure, see Using the ARIS250D Procedure.
Deleting a dbextent does not delete any data in the database. Data in the deleted dbextent is first moved to another dbextent in the same pool before it is removed from the database.
Dbextents can be deleted from a storage pool using the following three-step process:
These three steps are described in more detail below.
Run the ARIS250D procedure to delete dbextents from the storage pool. This step updates the database directory to remove the control information for the dbextents. For a description of this procedure, see Using the ARIS250D Procedure.
| Attention |
|---|
|
You must not delete the only dbextent from the storage pool that contains the internal dbspaces. |
If you are using cataloged procedures to hold the DLBL statements for your database, you must remove them for the deleted VSAM data sets. For the example shown in Figure 61, you would delete the following three DLBL statements from your job control procedures:
// DLBL DDSK15,'SQL.DDSK15.DBNAME01.DB',,VSAM // DLBL DDSK16,'SQL.DDSK16.DBNAME01.DB',,VSAM // DLBL DDSK17,'SQL.DDSK17.DBNAME01.DB',,VSAM
Run the VSAM IDCAMS program to physically delete the DASD space for the dbextent. Figure 61 shows how to delete the three VSAM data sets that were defined in Table 14.
Figure 61. Example Job Step for Deleting Dbextent Data Sets
| Note: | You can move a dbextent from one storage pool to another by deleting it and adding it back to the new pool; however, you cannot delete, add, and then delete the same dbextent in a single run. |
A dbextent is added to or deleted from the database using the procedure ARIS250D shown in Figure 62. This procedure starts the application server in single user mode (SYSMODE=S) with STARTUP=E. The job control to run this procedure is shown in Figure 63. The specifications for the dbextents to be added or deleted are provided in the member ARISADD, shown in Figure 65.
***********************************************************
* ARIS250D: ADD AND DELETE DBEXTENTS
* THE PROGRAM SCANS THE INPUT TWICE. FIRST PASS
* TO CHECK FOR ERRORS, SECOND PASS TO EXECUTE.
***********************************************************
// EXEC ARISQLDS,SIZE=AUTO,PARM='SYSMODE=S,STARTUP=E'
READ MEMBER ARISADD
/*
READ MEMBER ARISADD
/*
/&
|
The job control to run ARIS250D is shown in the figure below:
Figure 63. Example Job Control for ARIS250D procedure
// JOB ARIS71HD ADD AND DELETE DBEXTENTS // LIBDEF PROC,SEARCH=(PRD2.DB2710) // EXEC PROC=ARIS71PL *-- PRODUCTION LIBRARY ID PROC // EXEC PROC=ARIS71DB *-- DATABASE ID PROC // EXEC PROC=ARIS250D *-- ADD AND DELETE DBEXTENT PROC /& |
If you are using your own startup job stream instead of ARIS250D, you must code READ MEMBER ARISADD twice, and separate each line with /*. If you are coding the control statements in stream, you must code them twice, and separate them with /* as shown in Figure 64. The ARISQLDS program requires two sets of identical specifications for efficiency reasons. The first set is for syntax checking, and the second set is for processing.
Figure 64. Example Job Control for Adding or Deleting Dbextents
// JOB ADD AND DELETE DBEXTENTS
// LIBDEF PROC,SEARCH=(PRD2.DB2710)
// EXEC PROC=ARIS71PL
// EXEC PROC=ARIS71DB
// EXEC ARISQLDS,SIZE=AUTO,PARM='SYSMODE=S,STARTUP=E'
POOL 8 NOLOG
DELETE 3 1
DELETE 2 2
ADD 6 8
DELETE 4
ARCHIVE
/*
POOL 8 NOLOG
DELETE 3 1
DELETE 2 2
ADD 6 8
DELETE 4
ARCHIVE
/*
/&
|
The following figure contains examples of the control statements typically found in a member such as ARISADD, used by the ARIS250D procedure to add or delete dbextents.
Figure 65. Example ARISADD for Adding or Deleting Dbextents
POOL 8 NOLOG DELETE 3 1 DELETE 2 2 ADD 6 8 DELETE 4 ARCHIVE |
If the ARCHIVE control statement is specified, it must come last. The valid options are ARCHIVE (database archive), UARCHIVE (user archive) or NOARCHIVE (no archive). If you do not specify it, the default (ARCHIVE) is used.
| Attention |
|---|
|
The database cannot be restored from an archive taken prior to the deletion of a dbextent after the dbextent is removed from the database. Therefore, the user should choose ARCHIVE or UARCHIVE to backup the database. |
The optional POOL control statements must precede the statements that define the dbextents. They are required only for defining new nonrecoverable storage pools with POOL(NOLOG). They are unnecessary if you are adding dbextents to an existing pool because a storage pool's status has already been defined as either nonrecoverable or recoverable. POOL statements are also not necessary for new recoverable storage pools, because by default, storage pools are recoverable. The POOL control statement shown in Figure 65 defines storage pool 8 as nonrecoverable.
You cannot specify pool number 1 on any POOL control statement.
| Attention |
|---|
|
Once a storage pool is defined as either nonrecoverable or recoverable, you must not change it from recoverable to nonrecoverable (or from nonrecoverable to recoverable). |
The records following the POOL control statements contain the dbextent definitions. Each control statement must contain a control word (ADD or DELETE) and the specification of one dbextent. The first number in the input record is the number designator of the dbextent. The second number, if specified, is the number designator of its storage pool. (For the ADD action, if this number is not specified, the default is storage pool 1; for the DELETE action, the default is the storage pool where the dbextent resides.) The numbers must be separated by at least one blank.
When you add a dbextent, its number must either be one more than the number of dbextents currently defined, or the number of any dbextent that was deleted by the DELETE DBEXTENT operation. The total amount of space allocated in the directory as the dbextent control area is fixed for a database, and cannot be changed without regenerating the database. When a dbextent is deleted, the control area is not compressed. Therefore, you should reuse deleted dbextent numbers whenever possible so as to reuse the directory control area. Figure 66 shows area the dbextent control in the directory.
Figure 66. Dbextent Control Area in the Database Directory
|
In this example, a new dbextent can take on the numbers 5, 7 or 8, which are available for reuse, or 11, which is the next sequential number. The value 2+ indicates that there is empty directory space between dbextents 2 and 3. Because no dbextent number is associated with this space, you must first delete dbextent 2 or dbextent 3 to reclaim it.
You can determine the number of dbextents currently defined in a database by using the SHOW POOL operator command. To determine the maximum number of dbextents or storage pools that can be defined for the database, issue the SHOW DBCONFIG operator command. For more information, see the DB2 Server for VSE & VM Operation manual.
You can determine the deleted dbextent numbers that are available to be reused by using the SHOW POOL DELETED command. There is a maximum size associated with each deleted dbextent number. The maximum size is determined by the previous use of the dbextent number. The highest number is an exception; if it is deleted, the control area it used to occupy will be combined with the rest of the free area and this number will be treated as if it has never been used.
For example, if dbextent 10 in Figure 66 above is deleted, the control area in the directory will look like Figure 67.
Figure 67. Dbextent Control Area in the Directory after Dbextent 10 Is Deleted
 |
When the SHOW POOL DELETED command is issued, dbextent number 10 will not be listed.
Storage pool numbers can range from 1 to MAXPOOLS, where MAXPOOLS is the maximum number of storage pools for the database, as specified during the database generation. Storage pool numbers can be used in any sequence.
Neither the ADD nor the DELETE DBEXTENT operation is recorded in the log. Because these operations update the directory, and not the database itself, you can encounter a problem if you normally archive the database, and then try to restore it. For an ADD DBEXTENT operation, suppose the following events occur in the following order:
The directory and the database are not synchronized. The directory was restored from an archive file that did not reflect the ADD DBEXTENT operation; the database is also restored from that file however, the use of the changed dbextents is also restored from updates recorded in the log or log archives. Thus, the directory does not reflect the changed dbextents, but the database does.
For a DELETE DBEXTENT operation, suppose the following occurs:
The restore operation fails because it attempts to put data on the dbextents that have been removed.
You can prevent this problem by using the ARCHIVE or UARCHIVE option in the ADD or DELETE DBEXTENT operation. This will ensure that your current database archive reflects the changed dbextents.
The same problems occur if you use log archiving and restore the database using a database archive taken before the ADD or DELETE DBEXTENT operation. That is, if you use a back-level database archive and subsequent log archives to restore the database, the database archive that records the changes to the dbextents are skipped. For ADD DBEXTENT operations, the directory, restored from the back-level database archive, does not show the changes to the dbextents; the subsequent log archives, however, do record the use of those dbextents. Restoring the database from an old database archive and subsequent log archives can thus put the database out of synchronization with the directory. For DELETE DBEXTENT operations, the restore fails when it tries to use the removed dbextents.
If a system failure occurs during the ADD or DELETE DBEXTENT operation, restart the operation after determining and correcting the cause of the failure.
Instead of using the procedure ARIS250D, you can choose to run program
ARISQLDS with additional parameters. The initialization parameters that
you can specify for running the ADD or DELETE DBEXTENT operation, are shown in
Table 15.
Table 15. Initialization Parameters for the ADD and DELETE DBEXTENT Operation
| Parameter | Default | Minimum | Maximum |
|---|---|---|---|
|
DBNAME=name SYSMODE=S PARMID=name STARTUP=E DBPSWD=password LOGMODE=Y|A|L|N DSPLYDEV=L|C|B DUMPTYPE=P|F|N TRACDBSS=nnn... TRACRDS=nnnnnn TRACDSC=nn TRACCONV=n TRACSTG=n TRACEBUF=n |
SQLDS --- None --- None Y L P Zeros Zeros Zeros Zero Zero Zero |
--- --- --- --- --- --- --- --- Zeros Zeros Zeros Zero Zero Zero |
--- --- --- --- --- --- --- --- Twos Twos Twos Twos Ones 99999 |
The DBNAME, SYSMODE and STARTUP parameters are required as shown to define the run as an ADD or DELETE DBEXTENT operation. Also, PASSWORD will be required if the database VSAM data sets are password protected.
The PARMID parameter can be used to specify a source member that contains parameter specifications for the ADD or DELETE DBEXTENT operation.
The DSPLYDEV, DUMPTYPE, TRACDBSS, TRACDSC, TRACRDS, TRACCONV, TRACSTG and TRACEBUF parameters can optionally be specified. For a description of these parameters, see Multiple User Mode Initialization Parameters. Because ADD and DELETE DBEXTENT operations can only be done when the database manager is running in single user mode, the initialization parameters are the only means of tracing them. (Operator TRACE commands are only valid when the database manager operates in multiple user mode).
Sometimes it may be necessary to relocate the dbextents to another device due to disk migration or to control device utilization. This is done using the VSAM BACKUP and RESTORE commands after the application server is shut down. See Figure 70 for an example of IDCAMS BACKUP, commands and Figure 72 for IDCAMS RESTORE commands.
Sometimes you must relocate the log data set to another device because of disk migration or to control device utilization. If the following conditions are met, VSAM BACKUP and RESTORE commands can be used to make an exact copy of the original log data set and it is not necessary to reformat or reconfigure the new log data set:
For more information about reconfiguring or reformatting the log data set, see Reconfiguring and Reformatting the Logs.