Table space creation syntax is as follows:
CREATE TABLESPACE tablespace-name IN database-name using-block free-block DEFINE YES/NO LOGGED | NOT LOGGED TRACKMOD YES/NO DSSIZE integer MAXPARTITIONS integer MEMBER CLUSTER NUMPARTS integer PARTITION integer using-block free-block BUFFERPOOL bp-name CCSID ASCII/EBCDIC/UNICODE CLOSE YES/NO COMPRESS YES/NO LOCKMAX integer LOCKSIZE ANY/TABLESPACE/TABLE/PAGE/ROW MAXROWS integer SEGSIZE integer using-block: USING VCAT catalog-name STOGROUP stogroup-name PRIQTY integer SECQTY integer ERASE YES/NO free-block: FREEPAGE integer PCTFREE integer
database-name: If you omit database-name, then the default DB, DSNDB04 is used. But it is advisable to specify the database.
Specifies that the first data set for the table space is managed by the user, and following data sets, if needed, are also managed by the user.STOGROUP:
Specifies the storage group in which the datasets for the table space will be defined and managed by DB2.
PRIQTY: Specifies the minimum primary space allocation for a DB2-managed data set.
SEQQTY: Specifies the minimum secondary space allocation for a DB2-managed data set.
ERASE: Indicates whether the DB2-managed data sets for the table space are to be erased when the corresponding table space is deleted.
NO: It does not erase the data sets. This is the default.
YES: Erases the data sets.
Specifies how often to leave a page of free space when the table space is loaded or reorganized.
You must specify an integer in the range 0 to 255.
If you specify 0, no pages are left as free space. Otherwise, one free page is left after every n pages, where n is the specified integer.PCTFREE:
Indicates what percentage of each page to be left as free space when the table space is loaded or reorganized.
Integer can range from 0 to 99.
The first record on each page is loaded without restriction. When additional records are loaded, at least integer percent of free space is left on each page.
Specifies at which point (when) the underlying data sets for the table space are physically created.
YES: The data sets are created when the table space is created. This is the default.
NO: The data sets are not created until data is inserted into the table space. DEFINE NO is applicable only for DB2-managed data sets (USING STOGROUP specification). DEFINE NO is ignored for user-managed data sets (USING VCAT specification).LOGGED:
Specifies whether DB2 tracks modified pages in the space map pages of the table space.
YES: Tracked. This is the default.
NO: Not tracked.
Specifies the maximum size for each data set.MAXPARTITIONS:
Specifies the maximum number of partitions in a partitioned table space.MEMBER CLUSTER:
Specifies that data inserted by an “insert operation” is not clustered by the clustering index. Instead, DB2 chooses where to locate the data in the table space based on available space.Clustered and Non-Clustered Index:
Indexes are organized with the B-Tree structure.Clustered Index:
The leaf level (the lowest level of the tree) is the data.
For a table that has a clustered index, the data is actually stored in the order of the index.
The leaf contains bookmarks to the actual data.
The bookmarks in non-clustered indexes are in RID format (Row ID), that is, direct pointers to the physical location the row is stored in.
Indicates that the table space is partitioned.
Integer is the number of partitions.
Specifies to which partition the subsequent using-block or free-block applies.
Integer can range from 1 to the number of partitions given by NUMPARTS.
Identifies the buffer pool to be used for the table space.CCSID:
Specifies the encoding scheme for tables in the TS.CLOSE:
When the limit on the number of open data sets is reached, specifies the priority in which data sets to be closed.
YES: Eligible for closing data sets. This is the default.
NO: Eligible for closing after all CLOSE YES data sets is closed.
YES: Specifies data compression.
NO: Specifies no data compression.
LOCKMAX: Specifies the maximum number of page or row locks an application process can hold simultaneously in the table space.
LOCKSIZE: Specifies the size of locks used within the table space.
MAXROWS: Specifies the maximum number of rows that DB2 will consider placing on each data page.SEGSIZE:
Create table space DSN8S91D in database DSN8D91A. Let DB2 define the data sets, using storage group DSN8G910. The primary space allocation is 52 kilobytes; the secondary, 20 kilobytes. The data sets need not be erased before they are deleted. Locking on tables in the space is to take place at the page level. Associate the table space with buffer pool BP1. The data sets can be closed when no one is using the table space.
CREATE TABLESPACE DSN8S91D IN DSN8D91A USING STOGROUP DSN8G910 PRIQTY 52 SECQTY 20 ERASE NO LOCKSIZE PAGE BUFFERPOOL BP1 CLOSE YES;
Assume that a large query database application uses a tablespace to record historical sales data for marketing statistics. Create large tablespace SALESHX in database DSN8D91A for the application. Create it with 82 partitions, specifying that the data in partitions 80 through 82 is to be compressed.
Let DB2 define the data sets for all the partitions in the tablespace, using storage group DSN8G910. For each data set, the primary space allocation is 4000 kilobytes, and the secondary space allocation is 130 kilobytes. Except for the data set for partition 82, the data sets do not need to be erased before they are deleted. Locking on the table is to take place at the page level.
There can only be one table in a partitioned tablespace. Associate the tablespace with buffer pool BP1. The data sets cannot be closed when no one is using the tablespace. If there are no CLOSE YES data sets to close, then DB2 might close the CLOSE NO data sets when the DSMAX is reached.
CREATE TABLESPACE SALESHX IN DSN8D91A USING STOGROUP DSN8G910 PRIQTY 4000 SECQTY 130 ERASE NO NUMPARTS 82 (PARTITION 80 COMPRESS YES, PARTITION 81 COMPRESS YES, PARTITION 82 COMPRESS YES ERASE YES) LOCKSIZE PAGE BUFFERPOOL BP1 CLOSE NO;
If you have any doubts or queries related to this chapter, get them clarified from our Mainframe experts on ibmmainframer Community!