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. You can add dbspaces to the database using the SQLADBSP EXEC. This EXEC resides on the service disk (V-disk) and can be run only by a database machine in single user mode. For this reason, you should add enough dbspaces for your future needs.
You cannot remove a dbspace after it has been added to the database. After it has been acquired, you can drop its contents with the DROP DBSPACE operation so that another user can acquire it.
Figure 50 shows the format of the SQLADBSP EXEC.
>>-SQLADBSP----Dbname(server_name)---+------------+------------->
'-dcssID(id)-'
>-----+------------------+-------------------------------------><
'-PARM(parameters)-'
|
| Note: | If you have been accessing the database with archiving, you must specify LOGMODE=A or L, as appropriate. |
If you specify the PARM parameter, it must follow the other SQLADBSP 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 SQLADBSP EXEC automatically supplies SYSMODE=S and STARTUP=S. Also, do not specify the PROGNAME and DUALLOG parameters. The PROGNAME parameter is not valid with STARTUP=S, and the DUALLOG parameter does not apply.
You can specify the DUMPTYPE, TRACDBSS, TRACRDS, TRACDSC and TRACCONV parameters. For more information, see Multiple User Mode Initialization Parameters. The ADD DBSPACE operation requires that the database manager be run in single user mode, therefore, the TRACDBSS, TRACRDS, TRACCONV and TRACDSC initialization parameters are the only means of doing a trace of the ADD DBSPACE operation. (Operator TRACE commands can only be entered when the database manager runs in multiple user mode.)
If you use tracing, consider issuing your own CMS FILEDEF and LABELDEF commands for the trace file. For more information on the FILEDEF and LABELDEF commands, see the DB2 Server for VSE & VM Diagnosis Guide and Reference manual. For more general information about tape capabilities, see Tape Support.
You can use PARMID to specify a CMS file that contains parameter specifications for the ADD DBSPACE operation.
When running, the SQLADBSP EXEC must have information about the dbspaces to be added to the database. The SQLADBSP EXEC gets this information in either of these ways:
If you choose to have the file created dynamically, the SQLADBSP EXEC prompts you to enter the number of public, private, and internal dbspaces to be added to the database. You are then prompted for the number of pages and storage pool assignments for each.
For more information on using the SQLADBSP EXEC to add dbspaces, see Example of Adding a Dbspace to a Database.
In either situation, the SQLADBSP EXEC gives you the option of editing the resid SQLADBSP file with the CMS XEDIT facility. For example, if you wish to decrease the number of internal dbspaces, you will have to edit the file to change the number.
Figure 51. Example of a resid SQLADBSP File
PUBLIC 1024 7
PUBLIC 1024 8
PRIVATE 256 5
PRIVATE 256 5
PRIVATE 256 5
PRIVATE 256 5
INTERNAL 50 1024 9
|
Notice that the format for the ADD DBSPACE control statements is the same as the format for defining them during database generation.
When the resid SQLADBSP file is created and (optionally) edited, the SQLADBSP EXEC starts the application server in single user mode with the ADD DBSPACE option. The ADD DBSPACE operation uses the control statements in the resid SQLADBSP file.
Specify each dbspace to be added as a record in the resid SQLADBSP file 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.
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.
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, run the
SQLADBSP EXEC. When you are prompted to enter the number of public and
private dbspaces, respond "0" to these. When you are prompted to
enter the number of internal dbspaces to be added, enter a value. The
number you specify is added to the number of internal dbspaces with which the
database was generated. You are then prompted for the number of pages
and storage pool assignment; enter these. Finally, you are asked
if you want to modify the SQLADBSP file; respond "1" (for
yes). You are now given the opportunity to change the number of
internal dbspaces. This allows you, for example, to decrease the number
of internal dbspaces in the database.
For example, suppose your database was generated with 50 internal dbspaces, you want to add 4 dbspaces, and you want 4096 for the number of pages. When you run SQLADBSP, you receive a message saying that the database was generated with 50 internal dbspaces. Then you are asked to enter the number of internal dbspaces you want to add and the number of pages for each dbspace. Specify 4 and 4096, respectively. If you check the SQLADBSP control file, the INTERNAL statement shows that there are 54 internal dbspaces having 4096 pages. Now, suppose you rerun SQLADBSP. Again, you receive a message saying that the database was generated with 50 internal dbspaces, and you are asked to enter the number of internal dbspaces you want to add and the number of pages for each dbspace. Specify 2 for the number of dbspaces you are adding and 1024 for the number of pages. If you now check the SQLADBSP control file, the INTERNAL statement shows that there are 52 internal dbspaces each having 1024 pages. |
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 by rerunning the SQLADBSP EXEC. 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:
SQLSTART DB(server-name) PARM(SYSMODE=S,STARTUP=W,PROGNAME=ARISEGB)
When you create a new storage pool, you must also assign at least one dbspace to the new pool to make it usable. Assigning dbspaces requires that you run the SQLADBSP EXEC. Figure 52 shows the procedure to add one public dbspace to pool number 2 in the database named TEST. Note that the example indicates the entries you make.
Figure 52. SQLADBSP Example of Adding a Dbspace
Notes for Figure 52:
After (6), the SQLSTART EXEC is automatically called. When this EXEC ends, the SQLADBSP EXEC also ends, and the dbspace has been added.
To confirm that a dbspace has been added to the new storage pool, restart the application server use either the DBS utility or ISQL to issue this query:
SELECT DBSPACENO, DBSPACENAME, -
POOL FROM SYSTEM.SYSDBSPACES -
ORDER BY DBSPACENO
This query produces a table showing the dbspace numbers, dbspace names, and the number of the pool each dbspace is assigned to.