This section discusses the data definition statements for dbspaces and should be read in conjunction with the DB2 Server for VSE & VM SQL Reference manual, which contains the syntax, authorization rules, and usage rules of these statements.
| Note: | This section applies to DB2 Server for VSE & VM application servers only. |
A dbspace is a portion of the database that can contain one or more tables and any associated indexes. Each table that is stored is placed in a dbspace chosen by the creator of the table.
Dbspaces are defined when the database is generated and may be added later by the ADD DBSPACE process. Each dbspace remains unnamed and available until it is acquired with an ACQUIRE DBSPACE statement, generally by the Database Administrator (DBA). An acquired dbspace can be later returned to the list of available dbspaces by the DROP DBSPACE statement.
The user who acquires a dbspace can either specify from which storage pool the database manager is to acquire the dbspace, or can allow the system to choose the storage pool by default. Storage pool are collections of DB2 Server for VSE data sets or DB2 Server for VM minidisks called dbextents, and control the distribution of the database across direct access storage devices (DASD).
Storage pools can be recoverable or nonrecoverable. Recoverable storage pools protect their data using the automatic recovery for data updates. With nonrecoverable storage pools, system overhead is reduced, but if there is a system failure, some data may be lost, because the burden of recovery is placed on the user. Nonrecoverable storage pools are particularly useful in cases where large amounts of data are loaded from an external source, and that data is never modified thereafter. See the DB2 Server for VM System Administration or the DB2 Server for VSE System Administration manual for more information about storage pools.
The acquiring user also gives a name to the dbspace, and defines certain characteristics for it.
If it is to be private, the user who acquires it becomes its owner; if it is of type public, its owner becomes public.
If you have DBA authority, you can acquire a dbspace for another user by concatenating the userid to the dbspace-name:
ACQUIRE PRIVATE dbspace NAMED JONES.SPACE1
In the above statement, the owner of the dbspace is user JONES. User JONES can refer to the dbspace as simply SPACE1.
A user holding RESOURCE authority can create new tables in any public dbspace, or in any private dbspace owned by that user. Users who do not have RESOURCE authority can also create tables in any private dbspace that was acquired for that user by the DBA. Only users having DBA authority can create tables in a private dbspace owned by another user.
The ability to access and update tables belonging to another user is controlled by the system. Authorized users can access and update tables in any dbspace of any type, by adding the owner-name as a prefix to the table name (for example, SMITH.INVENTORY).
| Note: | Even users who are authorized to access data in someone else's dbspace may not be permitted to do so if the dbspace is in use. |
An attempt to read data in a private dbspace results in a negative SQLCODE if any data in the dbspace has been modified by a still-active logical unit of work. An attempt to modify data in a private dbspace results in a negative SQLCODE if any data in the dbspace has been read or modified by a still-active logical unit of work. If the locked data you attempt to access is in a public dbspace, your program waits and does not regain control until the lock is freed. If you attempt to update locked data in a private dbspace, the system immediately returns control to your program, with a negative SQLCODE.
The size of the space that is locked is the lock size. The lock size on a private dbspace is always the entire dbspace, while the default lock size on a public dbspace is somewhat smaller to allow for more concurrency. Thus, you should place tables in public dbspaces if you expect that more than one user may need concurrent access to them. On the other hand, because operations on private dbspaces do not pay the overhead of acquiring individual locks within the dbspace, a private dbspace is an efficient place to store tables for the exclusive use by one user at a time. The cost of smaller locks is higher overhead. Figure 82 and Figure 83 summarize the database manager locking mechanism.
Refer to the DB2 Server for VSE & VM Diagnosis
Guide and Reference manual for more information on locking.
Figure 82. Locking Summary for Private Dbspaces
| If you attempt to: | But another user has already: | |
| read the data (acquired a share lock) | modified the data (acquired an exclusive lock) | |
| Read data | You are allowed to read the data | You receive a negative SQLCODE |
| Modify data | You receive a negative SQLCODE | You receive a negative SQLCODE |
|
| ||
| The lock size for a private dbspace is always the entire dbspace. | ||
Figure 83. Locking Summary for Public Dbspaces
| If you attempt to: | But another user has already: | |
| read the data (acquired a share lock) | modified the data (acquired an exclusive lock) | |
| Read data | You are allowed to read the data | Your program waits |
| Modify data | Your program waits | Your program waits |
|
| ||
| The lock size of a public dbspace defaults to a page (4096 bytes). The lock size can be changed by the ACQUIRE DBSPACE or ALTER DBSPACE statements. | ||
The ACQUIRE DBSPACE statement causes the system to find an available dbspace of the requested type (public or private) and give it the dbspace-name you specify. The dbspace-name must be an SQL identifier, as described in the DB2 Server for VSE & VM SQL Reference manual; you can use it to refer to the DBSPACE in other SQL statements, such as CREATE TABLE.
If the dbspace type is public, its owner becomes public; if the type is private, its owner becomes the user who preprocessed the program in which the ACQUIRE DBSPACE is embedded. Dbspace names must be unique within all the dbspaces owned by the same user, but may duplicate the name of a dbspace owned by another user.
You can optionally specify one or more of the following properties of a dbspace, in any order. Separate the parameters with commas.
Notes:
Notes:
Notes:
Notes:
Notes:
Notes:
The ALTER DBSPACE statement enables you to alter the percentage of free space that is reserved on each data page when records are inserted into a public or private dbspace. It also enables you to alter the lock size of a public dbspace. (You cannot alter the lock size of a private dbspace.)
When you acquire a dbspace, you should set the percentage (0 to 99) of free space to some number greater than zero (the default is 15 percent). A typical use of ALTER DBSPACE is to set the percentage of free space to zero (PCTFREE=0) after initial loading of data into a dbspace; subsequent insertions can then take advantage of the free space that is reserved during the loading process. It is also possible to increase PCTFREE again for a later loading phase.
To alter the lock size of a public dbspace at any time, use the LOCK parameter. (You can specify both the PCTFREE and LOCK parameters when altering a public dbspace, in either order, separated with a comma. Each may be specified only once.)
The valid lock sizes are ROW, PAGE, and DBSPACE, as described under the ACQUIRE DBSPACE statement. When an ALTER DBSPACE statement is executed to alter the lock size of a dbspace, the system acquires an exclusive lock on the entire dbspace and holds the lock until the end of the current logical unit of work. The newly selected lock size then becomes effective for subsequent logical units of work.
When you operate the database manager in single user mode, there is no contention from other users when you attempt to access data; there may be however in multiple user mode. To provide for concurrent access, the system internally acquires locks on data accessed by a logical unit of work.
All LUWs automatically acquire exclusive locks on all data that they modify, and share locks on data that they are reading. Exclusive locks prevent other users from either reading or modifying the data; share locks permit other users to read, but prevent them from modifying the data.
For UPDATE and DELETE processing, the system acquires update locks. If the user wants to change the data, the update lock is changed to an exclusive lock; otherwise, the update lock is changed to a share lock. An update lock is acquired for a Positioned DELETE only if the cursor was declared with the FOR UPDATE clause. This type of lock is also acquired on a parent table when changes are made to its dependent tables.
In general, locks are held to the end of the LUW in which they are acquired. (See Selecting the Isolation Level to Lock Data (DB2 Server for VM) or Selecting the Isolation Level to Lock Data (DB2 Server for VSE) for more information.)
Potential deadlocks are automatically detected and corrected. A deadlock occurs when two LUWs are each waiting to access data that the other has locked. The system detects this situation and backs out the most recent LUW, meaning that all changes made to the database during the LUW are restored, and then the locks that were acquired for the LUW are released. The other application can then proceed. If your LUW is backed out, a negative SQLCODE is returned and SQLWARN6 is set to W.
Locking is automatic and requires no user intervention. However, certain statements permit users to adjust or override the normal locking. You can adjust the size of the lockable data units with the LOCK option of the ACQUIRE DBSPACE and ALTER DBSPACE statements. You can also override automatic locking and explicitly acquire certain kinds of locks with the LOCK statement as discussed below.
| Note: | Only single user mode prevents locking. |
The LOCK statement overrides the automatic locking mechanism and explicitly acquires a lock on a table or dbspace, which is held the end of the current LUW.
The LOCK statement is useful only in multiple user mode. In single user mode, there is no contention for resources, and, hence, no locking. When running in single user mode, all LOCK statements are ignored.
An exclusive lock prevents other users from either reading or changing any data in the locked table or dbspace.
A share lock permits other users to read, but prevents them from modifying, the data in the locked object.
The requested lock may be unavailable because other LUWs are reading or modifying the indicated data. If this is the case, the LUW that requested the lock waits until the other active LUWs have ended. The system then grants the lock, and the requesting LUW proceeds normally.
The LOCK statement is entirely optional, as the system has fully automatic locking. You may issue all SQL queries and updates independently of explicit LOCK statements.
The LOCK statement is useful mainly for avoiding the overhead of acquiring many small locks when scanning over a table. For example, suppose some dbspace has been acquired with a lock size of ROW. If you know that you will be accessing all the rows of a table within that dbspace, you may want to explicitly lock the entire table to avoid the overhead of acquiring locks on each individual row.
In a private dbspace, a LOCK statement on a table is the same as one on the entire dbspace, because locking is always done at the DBSPACE level for private dbspaces.
The DROP DBSPACE statement deletes the entire contents of a dbspace. When the logical unit of work is committed, the dbspace is available to be acquired. The DROP DBSPACE statement is a much faster way to delete the contents of a dbspace than by deleting the data one row at a time or dropping one table at a time. (You can use DROP DBSPACE with both public and private dbspaces.)
For any table that is dropped implicitly by the DROP DBSPACE statement, all referential constraints in which it is a dependent are dropped, and all referential constraints in which it is a parent are also dropped. Furthermore, any unique constraints defined in the table are dropped.
When a dbspace is dropped, packages for programs that operate on that dbspace are marked invalid. In addition, if a parent table has been dropped, the packages with tables dependent on that parent table are also marked invalid, because the relationship between the parent table and its dependent tables was dropped.
If one of these programs is running, the system does not drop the dbspace until the running program ends its current LUW. The invalid packages remain in the database until they are explicitly dropped using the DROP PACKAGE statement (discussed in the DB2 Server for VSE & VM SQL Reference manual).
When an invalid package is invoked, the system attempts to dynamically re-preprocess it. If the package was not invalidated because the relationship between a parent table and its dependent tables was dropped, and the program contains any SQL statement that refers to a dbspace or table that has been dropped, that SQL statement returns a negative SQLCODE at execution time.