Before tables and indexes can be stored in a dbspace, the dbspace must be added, and then acquired. Adding a dbspace to a database consists of reserving page tables in the directory, assigning the dbspace to a storage pool, and specifying it as public or private.
To create new dbspaces, use the ADD DBSPACE operation. The application server must be running in single user mode (SYSMODE=S), with STARTUP=S.
Specify each dbspace to be added on a SYSIPT input record that contains the type (public or private), the size (number of pages), and, optionally, the storage pool assignment. (The default storage pool number is 1.) The number you specify for the size should be a multiple of 128, since directory page tables are allocated in multiples of 128-page table entries. If it is not, the database manager rounds it up to the next higher multiple of 128. Separate all parameter values by at least one blank. Figure 59 shows an example.
Figure 59. Sample ADD DBSPACE Control Statements
// JOB ADD DBSPACES // EXEC PROC=ARIS71SL // EXEC PROC=ARIS71DB // EXEC PGM=ARISQLDS,SIZE=AUTO,PARM='DBNAME=SQL_DB1,SYSMODE=S,STARTUP=S' PUBLIC 1024 7 PUBLIC 1024 8 PRIVATE 256 5 PRIVATE 256 5 PRIVATE 256 5 PRIVATE 256 5 INTERNAL 50 1024 9 /* /& |
On the last dbspace specification record you must specify the internal dbspaces to be defined. This record contains the keyword INTERNAL, the number of internal dbspaces to be supported, the size of each (in number of pages), and, optionally, the storage pool assignments. Internal dbspaces can be assigned to either recoverable or nonrecoverable storage pools. However, for performance reasons, the internal dbspaces should not be assigned to storage pool 1 and preferably should be stored in their own storage pool. Internal dbspaces can also be stored in a virtual disk. For more information on the performance benefits of virtual disk support, see the DB2 Server for VSE & VM Performance Tuning Handbook.
It is necessary that you respecify the internal dbspace values each time you add a new public or private dbspace, even if you are not changing these values from what they were before. The internal dbspace specification overrides the previous one, including changing the storage pool assignment.
| Note: | You may sometimes want to change the internal dbspace specifications for reasons other than adding new user dbspaces. To do this, simply run the ADD DBSPACE operation omitting the control statements for public or private dbspaces, and enter the number of internal dbspaces you want. |
The ADD DBSPACE operation updates the directory and the catalog tables in the database. Only the updates to the catalog tables are recorded in the log; updates to the directory are not. Because of this, you can have a problem if you normally archive the database, and then try to restore it. Suppose the following events occur:
The directory and the database are not synchronized. The directory has been restored from a database archive file that does not reflect the ADD DBSPACE operation. The database is also restored from that file; but its restore includes the updates recorded in the log or log archives, which do reflect the ADD DBSPACE operation. Thus, the directory does not include the new dbspaces but the database does.
To prevent this problem, archive the database immediately after the ADD DBSPACE operation, as follows:
Following this procedure will ensure that your current database archive reflects the added dbspaces. (See Archiving Procedures and Restoring the Database for more information on archiving and restoring procedures.)
If you do log archiving and restore the database using a database archive taken before the ADD DBSPACE operation, the same problem that was described above occurs. If you use a back-level database archive and subsequent log archives to restore the database, the database archive that records the addition of the dbspaces is skipped: the directory is restored from the back-level database archive and does not show the addition of the dbspaces, but the subsequent log archives do.
If you used the ADD DBSPACE operation only to reconfigure your internal dbspaces, restoring a back-level database does not unsynchronize the directory and database, since information about internal dbspaces is stored in the directory but their use is not recorded in the database. Thus, if you restore a back-level database, the number and size of the internal dbspaces return to the back-level values.
The ADD DBSPACE operation is a two-phase process. The first phase updates the database directory with the information about the new dbspace. The second updates the SYSTEM.SYSDBSPACES catalog table.
Completion of the first phase is indicated by the message:
ARI0915I DBSPACE ADDED TO DATABASE
If an abnormal end occurs before message ARI0915I is issued, restart the ADD DBSPACE operation from the beginning. If an abnormal end occurs after message ARI0915I is issued, restart the ADD DBSPACE operation by doing a start up of the application server as follows:
// EXEC ARISQLDS,SIZE=AUTO,PARM='SYSMODE=S,STARTUP=W,PROGNAME=ARISEGB'
Table 13 shows the initialization parameters that you can specify for
the ADD DBSPACE operation.
Table 13. Initialization Parameters for the ADD DBSPACE Operation
| Parameter | Default | Minimum | Maximum |
|---|---|---|---|
|
DBNAME=name SYSMODE=S PARMID=name STARTUP=S DBPSWD=password LOGMODE=Y|A|L|N DSPLYDEV=L|C|B DUMPTYPE=P|F|N TRACDBSS=nnn... TRACRDS=nnnnnn TRACDSC=nnnnnn 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 of the database manager as an ADD DBSPACE operation. In addition, DBPSWD is required if the database VSAM data sets are password protected.
If you have been accessing the database with LOGMODE=A or L, you must continue to do so for the ADD DBSPACE operation.
You can use PARMID to specify a source member that contains parameter specifications for the ADD DBSPACE operation.
You can also specify the DSPLYDEV, DUMPTYPE, TRACDBSS, TRACDSC, TRACRDS, TRACCONV, TRACSTG and TRACEBUF parameters. For more information, see Multiple User Mode Initialization Parameters. Because the ADD DBSPACE operation requires that the database manager be run in single user mode, the TRACDBSS and TRACRDS initialization parameters are the only means of doing a trace of the ADD DBSPACE operation. (Operator TRACE commands are only valid when the database manager runs in multiple user mode).