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 CMS minidisk defined on a different device.
You can allocate a table and all its indexes to a specific device or CMS minidisk. To do this, create the table in a dbspace with no other tables, assign the dbspace to its own storage pool, and define the dbextent (or dbextents) as the CMS minidisk (or minidisks) 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.
Before adding a dbextent to the database, you must define a minidisk for that dbextent. The minidisk definition allocates the DASD space and establishes the size of the dbextent. You define a minidisk by adding an MDISK control statement to the VM directory of the database machine.
Figure 55 shows example MDISK control statements for three minidisks that are to be database dbextents.
Figure 55. Example MDISK Statements for Adding Dbextents
MDISK 31A 3380 cylr 50 DBDSK7 R DBX01 AFRT
MDISK 323 3380 cylr 20 DBDSK8 R DBX01 AFRT
MDISK 43A 3380 cylr 30 DBDSK8 R DBX01 AFRT
|
| Note: | Refer to Table 44 for minimum space allocation values. |
In the example shown in Figure 55, one dbextent minidisk with a virtual device address of 31A is defined on volume DBDSK7. Two more, on virtual device addresses 323 and 43A, are defined on volume DBDSK8.
Read access mode (R) is specified with a read (DBX01) and write (AFRT) password for each minidisk. A user who knows the passwords can access the minidisks when the database manager is running in single user mode.
Database minidisks must always have a read password, a write password, and an access mode of R. If the passwords or access mode are overlooked, the minidisks are susceptible to careless or malicious access. For more information on the MDISK control statement, see the VM/ESA: Planning and Administration manual.
In the example, the sizes of the dbextent minidisks are specified in cylinders. The number of storage pool slots represented depends on the device types of DBDSK7 and DBDSK8. Because both are IBM 3380 volumes, the slots represented are:
virtual device 31A -- 7467 slots
virtual device 323 -- 2964 slots
virtual device 43A -- 4446 slots
1 slot = 1 4-kilobyte block
The tables in Appendix B, Estimating Database Storage show how many slots are held on each of the different count-key-data (CKD) devices. For FB-512 devices, the sizes are specified in blocks. One dbextent page equals 8 blocks of an FB-512 device.
You can move dbextents between device types so 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 dbextent of 600000 blocks on a 9335, you could not move that dbextent to a 9332 (which is limited to 360032 blocks). If you defined 3 dbextents, each of 200000 blocks, on a 9335 (for a total of 600000 blocks), you could move them to three 9332 devices.
When adding MDISK control statements for a database machine, you must increase the MAXCONN value by the number of dbextents added. The MAXCONN value is a parameter of the VM OPTION control statement. This value determines the number of VM IUCV or APPC/VM connections allowed for a virtual machine. The MAXCONN parameter is unique to each database machine. For more information, see Setting the MAXCONN Value.
The SQLADBEX EXEC updates the database directory to include the control information for the dbextent. It also adds the appropriate CP LINK and CMS FILEDEF commands to the database SQLFDEF file. Multiple dbextents can be defined in one run of the SQLADBEX EXEC. For more information, see Running the SQLADBEX EXEC.
Figure 56 illustrates the sequence of commands required to add a dbextent to a database named TEST. It is added to storage pool 1 at disk address 307. The SQLADBEX EXEC then automatically calls the SQLSTART EXEC.
Before you run the SQLADBEX EXEC, you should know the disk addresses of the dbextents and the numbers of the pools to which the dbextents are being assigned. The SHOW DBEXTENT command indicates pool numbers and the number of dbextents currently defined.
The example assumes that the new minidisk has already been defined and added to the VM directory. The minidisk has been formatted and reserved. This step is optional, but it allows the minidisk to be formatted and reserved without stopping the application server. Entries you would make are indicated in the example.
Figure 56. SQLADBEX Example of Adding a Dbextent
Notes for Figure 56:
After (9), the SQLSTART EXEC is automatically called. When this EXEC ends, the SQLADBSP EXEC also ends, and the dbextent has been added.
Deleting a dbextent does not delete any data in the database. Data in the deleted dbextent is moved to another dbextent in the same pool before the dbextent is removed from the database.
To delete dbextents:
The SQLADBEX EXEC updates the database directory to remove the control information for the dbextent. It also deletes the appropriate CP LINK and CMS FILEDEF commands to the database SQLFDEF file. Multiple dbextents can be deleted in one run of the SQLADBEX EXEC.
For more information, see Running the SQLADBEX EXEC and Example of Deleting a Dbextent from a Database.
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.
| Attention |
|---|
|
You must not delete the only dbextent from the storage pool that contains the internal dbspaces. |
Figure 57 illustrates the sequence of commands required to delete a dbextent from a database named TEST. You can use the SHOW POOL command to determine pool numbers and the number of dbextents currently defined. The SQLADBEX EXEC then automatically calls the SQLSTART EXEC.
Entries you would make are indicated in the example.
Figure 57. SQLADBEX Example of Deleting a Dbextent
Notes for Figure 57:
After (6), the SQLSTART EXEC is automatically called. When this EXEC ends, SQLADBEX also ends, and the dbextent has been deleted.
Deleted dbextents are sometimes counted in determining the MAXCONN setting. The MAXCONN value is a parameter of the VM OPTION control statement. This value determines the number of VM IUCV or APPC/VM connections allowed for a virtual machine. The MAXCONN parameter is unique to each database machine. For more information, see Setting the MAXCONN Value.
The SQLADBEX EXEC starts the application server in single user mode with STARTUP=E. It also calls the ADD and DELETE DBEXTENT operations. The DELETE DBEXTENT operation removes control information in the database directory for the dbextents being deleted. The ADD DBEXTENT operation initializes control information in the database directory for the dbextents being added, and defines new storage pools as being recoverable or nonrecoverable.
The SQLADBEX EXEC resides on the service disk (V-disk) and can only be run from an application server. Figure 58 shows the format of the SQLADBEX EXEC.
>>-SQLADBEX----Dbname(server_name)---+------------+------------->
'-dcssID(id)-'
.-POOL(LOG)---.
>-----+-------------+---+------------------+-------------------><
'-POOL(NOLOG)-' '-PARM(parameters)-'
|
The parameters of SQLADBEX are as follows:
If you specify POOL(NOLOG) to indicate that you want to define storage pools that are nonrecoverable, SQLADBEX prompts you for the numbers of any nonrecoverable storage pools you want to create. When prompted, you can respond with any value from 2 to the MAXPOOLS value for your database. The storage pools you select to be nonrecoverable must not already have dbextents assigned to them, and must not already have been defined in the SQLADBEX file.
If you omit the POOL parameter or specify POOL(LOG), which is the default, you are not prompted for the numbers of nonrecoverable storage pools. Nonrecoverable storage pools are described in Nonrecoverable Storage Pools.
If you specify the PARM parameter, it must follow the other SQLADBEX parameters. For a list of the valid initialization parameters, refer to Figure 11. That figure lists the parameters that apply in single user mode. Do not specify the SYSMODE and STARTUP parameters. The SQLADBEX EXEC automatically supplies SYSMODE=S and STARTUP=E. Also, do not specify the PROGNAME, DUALLOG, and LOGMODE parameters. The SQLADBEX EXEC ignores any LOGMODE parameter. The LOGMODE setting is determined by other parameters specified on the SQLADBEX EXEC. See page *** for more information on the LOGMODE setting.
You can specify the DUMPTYPE, TRACDBSS, TRACCONV, TRACDSC and TRACRDS parameters. For the definition of these parameters see Multiple User Mode Initialization Parameters. Because the ADD and DELETE DBEXTENT operations can be run only 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).
If you choose to use tracing, you may want to issue your own CMS FILEDEF and LABELDEF commands for the trace file. These optional FILEDEF and LABELDEF commands are discussed in the DB2 Server for VSE & VM Diagnosis Guide and Reference manual. More general information about tape capabilities is in Tape Support.
You can use PARMID to specify a CMS file that contains parameter specifications for the ADD or DELETE DBEXTENT operation.
For examples of using the SQLADBEX EXEC, see Example of Adding a Dbextent to a Database and Example of Deleting a Dbextent from a Database.
The SQLADBEX processing has three parts:
When you start the SQLADBEX EXEC, it copies the file resid SQLFDEF from the production disk to the database machine A-disk. (Any file on the A-disk that has the name resid SQLFDEF is replaced.)
If you are doing an ADD DBEXTENT operation, the SQLADBEX EXEC prompts you for the dbextent number, the storage pool number and virtual device address (cuu). If the minidisk has not been formatted and reserved, SQLADBEX will issue a CMS FORMAT and RESERVE command for it. If the minidisk is already formatted and reserved, SQLADBEX prompts you to proceed. Respond YES to the already formatted message displayed by SQLADBEX. The SQLADBEX EXEC prompts you to run the commands. You can choose to skip the FORMAT/RESERVE process if the minidisk has been previously formatted and reserved properly. Respond 1 (Yes) to run the commands or 0 (No) to skip them.
| Attention |
|---|
|
Be sure that you are accessing the correct minidisk before you respond 1 to the FORMAT and RESERVE notification. |
The SQLADBEX EXEC then adds the appropriate CMS FILEDEF and CP LINK commands to the resid SQLFDEF file for the new dbextent.
When all the minidisks have been added SQLADBEX copies the updated resid SQLFDEF file to the production disk. The SQLFDEF file on the production disk is replaced.
If the action is delete, SQLADBEX prompts you for the dbextent number and optionally the storage pool number. The update to the resid SQLFDEF file is delayed until the update to the directory is done.
The SQLADBEX EXEC updates the database directory by using the ADD and DELETE DBEXTENT operations. The ADD and DELETE DBEXTENT operations require (as input) the specifications for the dbextents to be added and deleted. The EXEC generates the specifications for you, based on the storage pool numbers you provided in the previous step.
The SQLADBEX EXEC creates the file resid SQLADBEX on the database machine's A-disk. Figure 59 shows the format of a resid SQLADBEX file. Any existing file with the name resid SQLADBEX is erased from the database machine's A-disk.
If you specify POOL(NOLOG) when running SQLADBEX, you are prompted for the numbers of storage pools that you want to define as nonrecoverable. Based on your responses to the prompts, SQLADBEX creates POOL control statements and inserts them in the resid SQLADBEX file. These POOL control statements are used by the ADD DBEXTENT operation to define nonrecoverable storage pools. Do not supply the numbers of storage pools that have already been defined. After definition, a storage pool cannot have its recovery status changed. Even if the storage pool contains no dbextents, once you have defined it as nonrecoverable, you cannot redefine it as recoverable. To see the storage pools that have been defined, use the SHOW POOL ALL command. Figure 59 shows a POOL control statement that defines storage pool 8 as nonrecoverable. A subsequent control statement, which was also generated because of responses to other prompts, assigns dbextent number 6 to the storage pool.
As soon as the file is created, you are given the opportunity to review it.
Figure 59. Format of the resid SQLADBEX File
POOL 8 NOLOG DELETE 3 1 DELETE 2 2 ADD 6 8 DELETE 4 ARCHIVE |
The ARCHIVE control statement must be the last statement if present. The valid options are ARCHIVE (database archive), UARCHIVE (user archive) or NOARCHIVE (no archive). If the ARCHIVE control statement is not specified, the default (ARCHIVE) is used.
| Attention |
|---|
|
After a dbextent is deleted, the database cannot be restored from an archive taken prior to the deletion. |
Therefore, the user should choose ARCHIVE or UARCHIVE to backup the database. If NOARCHIVE is chosen, the LOGMODE will be switched to Y. The LOGMODE parameter is set to A if ARCHIVE or UARCHIVE is chosen.
When the resid SQLADBEX file is complete, the SQLADBEX EXEC starts the ADD and DELETE DBEXTENT operation.
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.
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 60 shows the dbextent control area in the directory.
Figure 60. 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 60 above is deleted, the control area in the directory will look like Figure 61.
Figure 61. 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.
The storage pool numbers you enter can range from 1 to MAXPOOLS, where MAXPOOLS is the maximum number of storage pools for the database as specified during the database generation. You can use the storage pool numbers in any sequence.
For a dbextent to be deleted, the dbextent number must be one of the dbextents currently defined to the database. If the storage pool number is specified, it must be where the dbextent resides.
After the file resid SQLADBEX is created and reviewed, the SQLADBEX EXEC starts the application server in single user mode with the ADD or DELETE DBEXTENT operation. When the application server ends, the dbextents are deleted, or added and ready to be used.
The SQLADBEX EXEC removes CMS FILEDEF and CP LINK commands for all deleted dbextents from the resid SQLFDEF file.
When all the minidisks have been deleted, SQLADBEX copies the updated resid SQLFDEF file to the production disk. The SQLFDEF file on the production disk is replaced, and SQLADBEX then erases the resid SQLFDEF file from your A-disk.
Message ARI0620I resid SQLFDEF successfully copied to production disk indicates the successful completion of the ADD and DELETE DBEXTENT operation. If the operation does not complete successfully, the action you take depends on what part of the processing failed:
Table 13. Recovering from Errors during SQLADBEX
| Messages Issued | ||||
|---|---|---|---|---|
| ARI0922I | ARI0650E | ARI0620I | Action | Notes |
| No | Yes | No | None required; you can rerun SQLADBEX if you want. | Failed. SQLFDEF file not updated. Directory not updated. |
| Yes | Yes | No | You must rerun SQLADBEX with the same input. | Failed. SQLFDEF file not updated. Directory updated. |
| Yes | No | No | You must rerun SQLADBEX and reply YES to ARI0646D or delete the dbextents that you tried to delete. | Failed. SQLFDEF file updated for added dbextents only. Directory updated. |
| No | No | No | You must rerun SQLADBEX and reply YES to ARI0646D or delete the dbextents that you tried to add. | Failed. SQLFDEF file updated for added dbextents only. Directory not updated. |
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.
Sometimes you must relocate the dbextents to another device because of disk migration or to control device utilization. A dbextent can be moved using the SQLCDBEX EXEC. The "move" is actually a "copy". The dbextent is copied from one device to another. The dbextent remains on the old device until the copy is successfully committed, then the old device is released by the database manager. At this point, the dbextent has been moved.
The new device should be the same size as the old device. Moving a dbextent to a larger device does not expand the size of the dbextent. The extra space available on the larger device is not available to the dbextent. If you need to increase the size of your database, you must use the SQLADBEX EXEC. For more information on this EXEC, see Adding Dbextents to a Storage Pool.
The SQLCDBEX EXEC invokes the application server in single user mode, so before a dbextent can be moved, the application server must be shut down. The EXEC is located on the service disk.
Figure 62 shows the format of the SQLCDBEX EXEC.
>>-SQLCDBEX----Dbname(server_name)----------------------------->< |
When the EXEC is run, it prompts for all the information it requires to carry out the operation. The dbextents are copied to the new devices defined and the resid SQLFDEF file is updated on the A-disk.
The changes to the database are committed when all the copies have been performed successfully and the user indicates to end the EXEC. If the system crashes or the user quits from the EXEC, the dbextents remain on the old devices.
The old devices are released by the database manager when the changes are committed.
The SQLCDBEX EXEC can be used to move the directory.
Figure 63 illustrates the sequence of commands required to move a dbextent. The example assumes that the new minidisk has already been defined and added to the VM directory. The step to format and reserve the minidisk does not need to be performed if the minidisk has been formatted and reserved before running the EXEC.
Figure 63. SQLCDBEX Example of Moving a Dbextent
(1) **> sqlcdbex db(sqldba)
ARI0717I Start SQLCDBEX EXEC: 01/19/93 18:01:47 EST.
ARI0721I Get DB2 Server for VM production minidisk WRITE access: SQLDBA 195.
ARI6102A Enter DBEXTENT number (or LOGDSK1, LOGDSK2,
or BDISK) to copy.
(Enter a null response to end input or
enter QUIT to exit.)
(2) **> 2
ARI6103A Enter virtual address for new DBEXTENT 2.
(Enter a null response to end input or
enter QUIT to exit.)
(3) **> 204
ARI6110D Disk 204 is already formatted. Continuing will erase
all data on this disk. Do you want to use the disk?
Enter 0(No), 1(Yes), or 111(Quit).
(4) **> 1
ARI0647D Do you want to do a CMS FORMAT/RESERVE command on disk 204?
Enter 0(No) or 1(Yes).
(5) **> 0
ARI6131I Copying in progress. Please wait...
ARI6108I Minidisk copied successfully. The SQLDBA SQLFDEF file
will be updated.
ARI6109I SQLDBA SQLFDEF file has been updated on the A disk.
ARI6102A Enter DBEXTENT number (or LOGDSK1, LOGDSK2,
or BDISK) to copy.
(Enter a null response to end input or
enter QUIT to exit.)
(6) **>
ARI0620I SQLDBA SQLFDEF file
successfully copied to production disk.
ARI0673I All COPY DBEXTENT processing completed successfully.
ARI0796I End SQLCDBEX EXEC: 01/19/93 18:03:12 EST
ARI0721I Get DB2 Server for VM production minidisk READ access: SQLDBA 195.
Notes for Figure 63:
Sometimes you must relocate the log disks to another device because of disk migration or to control device utilization. The SQLCDBEX EXEC can be used to copy the log disk only if:
If these conditions are met, SQLCDBEX can be used to make an exact copy of the original log disk, and it is not necessary to reformat or reconfigure the log. If these conditions are not met, you must do a COLDLOG to reconfigure the new log disk. For more information about COLDLOG, see Reconfiguring and Reformatting the Logs.