DB2 for OS/390 Version 5

Create Table Space -- Field and controls

Type of table space

Nonpartitioned or simple: This type of table space can contain more than one table but the rows of different tables are not kept separate. To find all the rows of one table can require scanning the entire table space. If a table is dropped, its rows are not deleted. The space occupied by the rows does not become available until the table space is reorganized. All tables in a table space must be either user defined or in the same storage group.
Partitioned: This type of table space can only contain one table. The available space is divided into separate units of storage called partitions, each containing a part of one table. The partitions can be assigned to different storage groups, but the entire collection of data is logically one table. This type of table space can contain up to 64 partitions. A partition can be 1, 2, or 4 gigabytes in length, depending on the number of partitions contained in the entire table space. If only one partition is defined on the table space, then its maximum size is 4 gigabytes.
Large partitioned: This type of table space is a partitioned table space that can contain up to 254 partitions.
Segmented: This type of table space holds more than one table. The available space is divided into groups of pages called segments, each the same size. Each segment contains rows from only one table. A segmented table space can have between 1 and 32 VSAM linear data sets, all of which are either user defined or in the same storage group. The maximum size of a data set in the segmented table space is 2 gigabytes. And so, the maximum size of a segmented table space is 64 gigabytes (2 gigabytes multiplied by 32 data sets).

Buffer pool name

The buffer pool name field is optional; with no specification, the default buffer pool of the data base is used. The buffer pool name identifies the buffer pool to be used for the table space and determines the page size of the table space. For 4 KB and 32 KB page buffer pools, the page sizes are 4 KB and 32 KB, respectively.

Size of locks

The size of locks can also determine the threshold at which lock escalation occurs. This is not applicable for a table space in a work file database. The hierarchy of lock sizes starting with the largest is:

table space lock
table lock (only for segmented table spaces)
page lock and row lock

In all other cases, LOCKSIZE has no effect on an SQL statement until the statement is rebound. The lock sizes are:

ANY: Specifies that DB2 can use any lock size. Generally, DB2 will choose PAGE locks. When the number of locks acquired for the table space is greater than the maximum number of locks allowed for a table space (an installation parameter), the page locks are released and locking is set at the next higher level. If the table space is not a segmented table space, the next higher level is the table space.
TABLESPACE: The table space locks.
TABLE: The table locks. This is only applicable for a segmented table space.
PAGE: The page locks.
ROW: The row locks

Maximum number of locks

Locks are escalated if a program asks for more than that number. The page or row locks are released and the intent lock on the table space or segmented table is promoted to S or X mode. In a data sharing environment, a lock count is kept on each member.

You can select to use the value that was established at the time you installed DB2.
Or, you can specify the number of locks allowed before escalating. Enter a value in the range 0 to 2147483647. If you enter a 0, the number of locks allowed is unlimited and escalation does not occur. If the lock size is TABLESPACE or TABLE, then do not specify any value, or enter a value of 0.

Encoding scheme

Usually, each encoding scheme requires only a single CCSID. Additional CCSIDs are needed when mixed or graphic data is used. All data stored within a table space must use the same encoding scheme. The default encoding scheme is the one used by the database in which this table space resides, unless your current database is DSNDB04. In this case, the default encoding scheme is the value established at the time you installed DB2.

Maximum level password

This password value should be a short identifier. If delimited, the password can contain any characters acceptable to access method services. The password does not apply to the data sets managed by Storage Management Subsystem (SMS). To protect data sets defined to SMS, use RACF or a similar external security system. If you do not enter anything in this field, a password is not passed.

Maximum number of rows

This value is important for INSERT , LOAD, and REORG. For LOAD and REORG, free space percentages (an option on the Table Space Management tab in the main task) are considered before this value. So, fewer rows might be stored than the value you enter here.

CLOSE rule

This rule affects whether the data sets can be closed when the table space is not in use, and the limit on the number of open data sets is reached. If you de-select this field, no data sets can be closed. However, if DSMAX is reached and no CLOSE YES page sets exist, then even CLOSE NO page sets will be closed.

Selective partition locking

If you select to use LOCKPART YES, and all conditions required for SPL are met, only the partitions accessed are locked. If LOCKPART is YES and all conditions required for SPL are not met, every partition of the table space is locked.

Managed by user

This value applies to every data set that is eligible for the table space. (A nonpartitioned table space can have more than one data set if PRIQTY + 118 X SECQTY is at least 2 gigabytes). The catalog name used with this option identifies the catalog for the data set and supplies the first level qualifier for the data set name. You must use the short identifier (no more than 8 characters) of the catalog name in the VSAM catalog name field. So, if the name of the integrated catalog facility (ICF) catalog is longer than eight characters, you must enter an alias name in this field. For a partitioned table space, DB2 assumes only one data set for each partition.

Managed by DB2

This value applies to every data set that is eligible for the table space. (A nonpartitioned table space can have more than one data set if PRIQTY + 118 X SECQTY is at least 2 gigabytes). DB2 will create a data set for the table space with the aid of this storage group name. The data set is defined during the execution of this statement. DB2 assumes one and only one data set for each partition. The storage group must exist at the current server. Each volume serial number must identify a volume that is accessible to MVS for dynamic allocation of the data set. All identified volumes must be of the same device type. The ICF catalog must not contain an entry for the data set.

Minimum primary space allocation

The PRIQTY is in kilobytes. DB2 specifies the PRIQTY to access method services using the smallest multiple of pKB, where p=the page size of the table space. The allocated space can be greater than the amount of space requested by DB2. At least one of the volumes of the identified storage group must have enough available space for the primary quantity. Otherwise, the primary space allocation will fail.

Minimum secondary space allocation

The SECQTY is in kilobytes. DB2 specifies the SECQTY to access method services using the smallest multiple of pKB, where p=the page size of the table space. The allocated space can be greater than the amount of space requested by DB2.

Erase data sets

The ERASE option indicates whether the data sets for the table space are to be erased before they are deleted during the execution of a utility or an SQL statement that drops the table space. ERASE NO means do not erase the data sets. The data in the data sets is still accessible, though not through DB2. ERASE YES means erase the data sets. DB2 overwrites all the data in the data sets with zeroes before deletion.

Manage the free space

The integer you enter for the free space (FREEPAGE) specifies how many data pages there should be between each free page. The value you enter can be in the range of 0 to 255. If you enter a value of 0, no pages are left free. Do not specify an amount here if the table space is in a work file database.
Enter the percentage of each page to leave as free space (PCTFREE) when the table space is loaded or reorganized. The value you enter can be in the range of 0 to 99. Do not specify an amount here if the table space is in a work file database.

Cache pages

If you select to cache the pages only on update, when more than one member in the data sharing group has the table space open, and at least one member has it open for update, the updated pages are written to the group buffer pool. If you select to always cache the pages, this means that the pages are cached as they are read in from DASD.

Compress data

If this field is checked, the rows are compressed after you have completed the table space LOAD or REORG dialog (insert a link) on the table space. If this field is not checked, no data compression is used.

Partition

A nonlarge table space can contain up to 64 partitions. A partition can be 1, 2, or 4 gigabytes in length, depending on the number of partitions contained in the entire table space. If only one partition is defined on the table space, then its maximum size is 4 gigabytes. A Large partitioned table space can contain up to 254 partitions.

Create Table Space -- Field and controls

Properties page

Table space management page

Partition Management page

Type of table space