The process of adding a dbspace merely reserves pages for it in the directory. Before it can actually be used, it must be acquired. For details of how to acquire dbspaces, see the DB2 Server for VSE & VM Database Administration manual.
Packages and view definitions are stored in system dbspaces named SYS0002, SYS0003, .... SYSnnnn. Allocation of the initial system dbspace (SYS0002) is performed during database generation. You should probably acquire an additional package dbspace after installation, and then more as needs arise. Because unused dbspaces only require minimal directory space and no data pages, acquiring them is not costly. Thus, if your installation has many packages and views, it is a good idea to acquire several dbspaces for packages in advance for later use.
The database manager stores packages and view definitions as tables. A dbspace can contain up to 255 tables, and can therefore have up to 255 packages and view definitions.
Although packages and view definitions are stored as tables, information about them is found not in the SYSTEM.SYSCATALOG catalog table, but in the SYSTEM.SYSACCESS catalog table. When a dbspace is acquired for packages, 255 empty tables are preallocated in it. For each table that is created, a row is added to the SYSTEM.SYSACCESS catalog table that identifies the package table as unused. Unused package tables can be either available or unavailable. The TNAME value in SYSACCESS for unused package tables is represented either as !0x AVAILABLE or ¢0x UNAVAILABLE. (The x is a number from 1 to 5, which is used internally.) Initially, all package tables in a newly acquired dbspace are unused and available. As packages are created and views are defined, the TNAME value is changed to indicate the package or view name.
As mentioned above, you can usually fit 255 packages in a dbspace. However, if large packages are created, the dbspace pages may fill before all 255 package tables are used. In this situation, all remaining package tables are unused and unavailable and their TNAME value is marked in the dbspace as ¢0x UNAVAILABLE. When the dbspace is full, the FREEPCT column of the SYSTEM.SYSDBSPACES catalog table is updated.
A FREEPCT of 1 means that space is still available, while a FREEPCT of 0 means that this dbspace is full.
If a package or view is dropped from a dbspace that is not full, the database manager does not drop the package table from the dbspace. Instead, it deletes all the rows from the table, and marks the table as available in the SYSTEM.SYSACCESS catalog table. The table can then be reused.
If a package or view is dropped from a dbspace that has been marked as full (FREEPCT = 0), FREEPCT is reset to 1. Before these package tables can be reused, however, their TNAMEs in the SYSTEM.SYSACCESS catalog table must be changed to indicate that they are available. This is not done immediately, because if it were, the next time someone tried to create a package, the database manager would reuse the table from the package or view that was just dropped. It would try to place the newly created package in a dbspace that is almost full, and it probably would not fit. Thus, if you have used all the space in your package dbspaces, you should acquire another dbspace rather than try to free space by dropping one or two unused packages. The package tables will be marked available the next time the database manager does preallocation.
Preallocation is done when you acquire a new package dbspace. It is also done when you try to create a view or a new package, and there are no available packages. If the database manager cannot find an available package, it looks in all dbspaces that are not full (FREEPCT=1) for package tables that are marked unavailable, and marks them as available.
A user with DBA authority can acquire a package dbspace by issuing the following SQL statement when the database is running in multiple user mode:
ACQUIRE PUBLIC DBSPACE NAMED SYSnnnn (PAGES=xxxx)
wherennnn is the number of the package dbspace. (SYS0002 is the initial dbspace, so the next one will be called SYS0003, the next one, SYS0004, and so on.).
xxxx is the number of pages of address space for the dbspace. The usual value is 2048, but you can set it larger or smaller if your programs have a large or small number of SQL statements in them, or if you are adding many views to the database.
You should specify the PAGES parameter because the default value of 128 is usually too small. You can specify NHEADER or allow it to default to 8. The database manager sets PCTFREE to 1, PCTINDEX to 0, and LOCK to PAGE (page locking). If you try to specify any of these parameters, your settings will be ignored.
If no package tables are available in any package dbspace during preprocessing, SQLCODE -945 is returned, and the DBA must acquire another dbspace for packages.
If sufficient space is not available in the dbspace where the database manager attempts to create the package, it returns SQLCODE -946. The user's response depends on the availability of package tables in other dbspaces. If some are available, the user can try to preprocess the program again. (The database manager does not choose the same dbspace again because it sets FREEPCT=0 when the preprocess fails.) If no package tables are available, another dbspace for packages must be acquired.
To get information about unused packages
(available and unavailable), issue the following query:
SELECT * FROM SYSTEM.SYSACCESS WHERE TNAME LIKE '%AVAILABLE'
To determine which package dbspaces are full because all the space is taken, issue:
SELECT * FROM SYSTEM.SYSDBSPACES WHERE DBSPACENAME LIKE 'SYS0%'
If the FREEPCT value is 0, there is no free space in the dbspace.
To determine which package dbspaces are full because all 255 tables are occupied, issue:
SELECT DBSPACENO, COUNT(*) -
FROM SYSTEM.SYSACCESS -
WHERE TNAME NOT LIKE '%AVAILABLE' -
GROUP BY DBSPACENO
Dbspaces with a count of 255 have no available package tables. (For information on the syntax of the ACQUIRE DBSPACE and SELECT statements, see the DB2 Server for VSE & VM SQL Reference manual.)