To execute the CREATE TABLESPACE command, users must possess either DBADM, DBCTRL or DBMAINT authority for the database, overall SYSADM or SYSCTRL authority for the location, or the CREATETS privilege for the database.
Bold text indicates clauses or options that are supported only syntactically.
CREATE [LARGE | LOB] TABLESPACE tablespace-name
[IN {DSNDB04 | database-name}]
[using-block]
[free-block]
[GBPCACHE {CHANGED | ALL}]
[trackmod-block]
[LOG {YES | NO}]
[DEFINE {YES | NO}]
[DSSIZE integer G]
[{NUMPARTS integer]
[(PART integer {using-block | free-block | gbpcache-block | trackmod-block | COMPRESS {YES | NO})]
[COMPRESS {NO | YES}] [,...])]|
SEGSIZE integer}]
[BUFFERPOOL bpname]
[CLOSE {YES | NO}]
[DSETPASS password]
[COMPRESS {NO | YES}]
[LOCKSIZE {ANY | TABLESPACE | TABLE | LOB | {PAGE | ROW}
[LOCKMAX {0 | integer | SYSTEM}]}]
[CCSID {ASCII | EBCDIC | UNICODE}]
[LOCKPART {NO | YES}]
[MAXROWS integer]
using-block:
USING {VCAT catalog-name
| STOGROUP stogroup-name
[PRIQTY {3 | integer}]
[SECQTY {3 | integer}]
[ERASE {NO | YES}]}
free-block:
[FREEPAGE {0 | integer}] [PCTFREE {5 | integer}]gbpcache-block:[GBPCACHE {CHANGED | ALL | SYSTEM | NONE}]
trackmod-block:
[TRACKMOD {YES | NO}]
| tablespace-name | Identifies a unique table space name under the current location. The table space can be explicitly qualified with a database name by using the IN database-name clause.but must not identify a table space, index space, or LOB table space that exists at the current server. If a database is not specified, the table space will be associated with the dsndb04 default database for the location. |
| database-name | Identifies a unique database name (under the current location) in which the table space is created. The system catalog database (dsndb06) must not be specified for any type of table space and a work file database must not be specified for a LOB table space. The default database is the DSNDB04 database for the current location. |
| bpname | A name that identifies a bufferpool. The 4K bufferpools are named BP0, BP1, BP2...BP49. The 32K bufferpools are named BP32K, BP32K1, BP32K2...BP32K9. |
In DB2, when you create tables in a table space, all of the tables are physically stored in the same DASD volume (assuming no storage groups). Both logically and physically, each table is in the same table space. On an XDB Server, you can create a table space and have several different tables logically associated with that table space. Physically, however, the tables are stored in their own .TAB files.
All mainframe-specific option clauses of the CREATE TABLESPACE command are recorded in the XDB Server system catalog. The XDB Server also checks the syntax for each option. The XDB Server system architecture does not physically support many of these clauses. Refer to appropriate DB2 documentation for complete descriptions of these mainframe-specific options.
With DB2 V7, XDB locations (either EBCDIC or ASCII) can now contain Unicode data. If you create a table space with CCSID UNICODE, all character data in corresponding tables is stored in UTF-8 format and all graphic data is stored in UTF-16 format.
Identifies that each partition of a partitioned table space has a maximum partition size of 4 GB, which enables the table space to contain more than 64 GB of data. The preferred method to specify a maximum partition size of 4 GB and larger is the DSSIZE clause. The LARGE clause is for compatibility of releases of DB2 for OS/390 prior to Version 6. Do not specify LARGE if LOB or DSSIZE is specified.
Identifies the table space as LOB table space. A LOB table space is used to hold LOB values.
The LOB table space must be in the same database as its associated base table space.
Specifies the minimum primary space allocation for a DB2-managed data set. The primary space allocation is at least n kilobytes, where n is the value of integer with the following exceptions:
If PRIQTY integer is specified:
For LOB table spaces, the exceptions are:
If PRIQTY integer is specified:
If you do not specify PRIQTY or specify PRIQTY -1, DB2 uses a default value for the primary space allocation.
DB2 specifies the primary space allocation to access method services using the smallest multiple of pKB not less than n, where p is the page size of the table space. The allocated space can be greater than the amount of space requested by DB2.
For example, it could be the smallest number of tracks that will accommodate the request. The amount of storage space requested must be available on some volume in the storage group based on VSAM space allocation restrictions. Otherwise, the primary space allocation will fail. To more closely estimate the actual amount of storage, see the description of the DEFINE CLUSTER command in DFSMS/MVS: Access Method Services for the Integrated Catalog.
Executing this statement causes only one data set to be created. However, you might have more data than this one data set can hold. DB2 automatically defines more data sets when they are needed. Regardless of the value in PRIQTY, when a data set reaches its maximum size, DB2 creates a new one. To avoid wasting space, use the following formula to make sure that PRIQTY and its associated secondary extent values do not exceed the maximum size of the data set:
PRIQTY + (number of extents * SECQTY) <= DSSIZE (implicit or explicit)
This clause is only implemented syntactically in the XDB Server operating environment.
Specifies the minimum secondary space allocation for a DB2-managed data set. If you do not specify SECQTY DB2 uses a formula to determine a value.
If you specify SECQTY and do not specify a value of -1, DB2 specifies the secondary space allocation to access method services using the smallest multiple of p KB not less than n, where p is the page size of the table space. The allocated space can be greater than the amount of space requested by DB2. For example, it could be the smallest number of tracks that will accommodate the request.
The secondary space allocation is at least n kilobytes, where n is the value of integer with the following exceptions:
For LOB table spaces the exceptions are:
DB2 specifies the secondary space allocation to access method services using the smallest multiple of pKB not less than n, where p is the page size of the table space. The allocated space can be greater than the amount of space requested by DB2. For example, it could be the smallest number of tracks that will accommodate the request.
This clause is only implemented syntactically in the XDB Server operating environment.
Indicates what percentage of each page to leave as free space when the table 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.
The default is PCTFREE 5. Do not specify PCTFREE for a LOB table space, or a table space in a work file database or a TEMP database.
This clause is only implemented syntactically in the XDB Server operating environment.
Specifies whether DB2 tracks modified pages in the space map pages of the table space or partition. Do not specify TRACKMOD for a LOB table space. For a table space in a TEMP database, DB2 uses TRACKMOD NO regardless of the value specified.
This clause is only implemented syntactically in the XDB Server operating environment.
Specifies whether changes to a LOB column in the table space are to be written to the log. You can use the LOG clause only for a LOB table space.
| YES | Indicates that changes to a LOB column are to be written to the log. You cannot use YES if the auxiliary table in the table space stores a LOB column that is greater than 1 gigabyte in length.
YES is the default. |
| NO | Indicates that changes to a LOB column are not to be written to the log.
LOG NO has no effect on a commit or rollback operation; the consistency of the database is maintained regardless of whether the LOB value is logged. All committed changes and changes that are rolled back reflect the expected results. Even when LOG NO is specified, changes to system pages and to the auxiliary index are logged. During the log apply operation of the RECOVER utility, LPL recovery, or GPB recovery, all LOB values that were not logged are marked invalid and cannot be accessed by a SELECT or FETCH statement. Invalid LOB values can be updated or deleted. |
This clause is only implemented syntactically in the XDB Server operating environment.
A value in gigabytes that indicates the maximum size for each partition or, for LOB table spaces, each data set. If you specify DSSIZE, you must also specify NUMPARTS or LOB.The following values are valid:
To specify a value greater than 4G, the following conditions must be true:
For all table spaces except LOB table spaces, if DSSIZE (or LARGE) is omitted, the default for the maximum size of each partition depends on the value of NUMPARTS:
If NUMPARTS is ...Maximum partition size is...
The partition size shown is not necessarily the actual number of bytes used or allocated for any one partition; it is the largest number that can be logically addressed. Each partition occupies one data set.
For LOB table spaces, if DSSIZE is not specified, the default for the maximum size of each data set is 4 GB. The maximum number of data sets is 254.
When you give a table space a DSSIZE value, you also give the same size to all the indexes that point to that tablespace.
This clause is only implemented syntactically in the XDB Server operating environment.
Indicates that the table space is partitioned.
Integer is the number of partitions, and can range from 1 to 254 inclusive. NUMPARTS must be specified if DSSIZE is specified and LOB is omitted, or LARGE is specified.
The maximum size of each partition depends on the value specified for DSSIZE or LARGE. If DSSIZE or LARGE is not specified, the number of partitions specified determines the maximum size of each partition
If you omit NUMPARTS, the table space is not partitioned and initially occupies one data set. Do not specify NUMPARTS for a LOB table space, or a table space in a work file database or a TEMP database.
Specifies the size of locks used within the table space and, in some cases, also the threshold at which lock escalation occurs. Do not use this clause for a table space in a work file database or a TEMP database.
| ANY | Specifies that DB2 can use any lock size. Currently, DB2 never chooses row locks, but reserves the right to do so. In most cases, DB2 uses LOCKSIZE PAGE LOCKMAX SYSTEM for non-LOB table spaces and LOCKSIZE LOB LOCKMAX SYSTEM for LOB table spaces. However, when the number of locks acquired for the table space exceeds the maximum number of locks allowed for a table space (an installation parameter), the page or LOB locks are released and locking is set at the next higher level. If the table space is segmented, the next higher level is the table. If the table space is nonsegmented, the next higher level is the table space. |
| TABLESPACE | Specifies table space locks. |
| TABLE | Specifies table locks. Use TABLE only for a segmented table space. |
| PAGE | Specifies page locks. Do not use PAGE for a LOB table space. |
| ROW | Specifies row locks. Do not use ROW for a LOB table space. |
| LOB | Specifies LOB locks. Use LOB only for a LOB table space. |
This clause is only implemented syntactically in the XDB Server operating environment.
The CCSID clause is only syntactically supported for EBCDIC and ASCII. This is because the definition of the XDB location itself defines how character data is stored.
If you specify UNICODE, your character and graphic data is stored as UNICODE within the table space.
Specifies the maximum number of page, row, or LOB locks an application process can hold simultaneously in the table space. If a program requests more than that number, locks are escalated. The page, row, or LOB locks are released and the intent lock on the table space or segmented table is promoted to S or X mode. If you specify LOCKMAX for table space in a TEMP database, DB2 ignores the value because these types of locks are not used.
| Integer | Specifies the number of locks allowed before escalating, in the range 0 to 2 147 483 647.
Zero (0) indicates that the number of locks on the table or table space are not counted and escalation does not occur. |
| SYSTEM | Indicates that the value of LOCKS PER TABLE(SPACE), on installation panel DSNTIPJ, specifies the maximum number of page, row, or LOB locks a program can hold simultaneously in the table or table space. |
If the lock size is TABLESPACE or TABLE, LOCKMAX must be omitted, or its operand must be 0.
For an application that uses Sysplex query parallelism, a lock count is maintained on each member.
This clause is only implemented syntactically in the XDB Server operating environment.
XDB records either the catalog-name (preceded by VCAT keyword), or the stogroup-name (preceded by STOGROUP) that is associated with the table space. If the USING clause is left out, a default stogroup for the database must already exist. If a path has already been associated with stogroup-name (through the SYSIBM.SYSVOLUMES catalog table), then any objects created within the table space are implicitly located in that stogroup path.
When the limit on the number of open data sets is reached, specifies the priority in which data sets are closed.
For a table space in a TEMP database, DB2 uses CLOSE NO regardless of the value specified.
Either the option specified or the default value is recorded in the SYSIBM.SYSTABLEPART system catalog table. This option does not compress data in any way. Do not specify COMPRESS for a LOB table space or a table space in a TEMP database.
Indicates that the table space will be segmented. integer specifies how many pages are to be assigned to each segment. integer must be a multiple of 4 such that 4 <= integer <= 64. If the SEGSIZE clause is not specified, the table space is not segmented.
SEGSIZE must be specified for a table space in a TEMP database because the table space must be segmented. Do not specify SEGSIZE for a LOB table space or a table space in work file database; neither can be segmented.
A segmented table space cannot be partitioned. Therefore, do not specify NUMPARTS if you specify SEGSIZE.
This clause is only implemented syntactically in the XDB Server operating environment.
Specifies the maximum number of rows that DB2 will consider placing on each data page. The integer can range from 1 through 255. This value is considered for INSERT, LOAD, and REORG. For LOAD and REORG (which do not apply for a table space in the TEMP database), the PCTFREE specification is considered before MAXROWS; therefore, fewer rows might be stored than the value you specify for MAXROWS.
If you do not specify MAXROWS, the default number of rows is 255.
Do not use MAXROWS for a LOB table space or a table space in a work file database.
This clause is only implemented syntactically in the XDB Server operating environment.
This clause has no functional equivalent within the XDB Server operating environment. However, for DB2 compatibility purposes, the XDB Server records any of the sixty possible bufferpools (BP0...BP49, and BP32K, BP32K1...BP32K9). The first fifty bufferpools are for table spaces with 4K page sizes, while the last ten are for table spaces with 32K page sizes. Although not functionally supported, this new information is checked syntactically and recorded in the XDB Server system catalog tables.
If the table space is in a group buffer pool that is defined to be used only for cross-invalidation (GBPCACHE NO), CHANGED is ignored and no pages are cached to the group buffer pool.
When a table space is created in a workfile database, the following clauses are not allowed:
When a table space is created in a TEMP database, the following clauses are not allowed:
When a LOB table space is created, the following clauses are not allowed:
If neither LOB, NUMPARTS, nor SEGSIZE are specified, the table space that is created is a simple table space.
The following example creates a table space named abc within the xyz database, while specifying the stor1 stogroup for that table space:
CREATE TABLESPACE abc IN xyz
USING STOGROUP stor1;
In the above case, all objects created within the abc table space are stored in the path associated with the stor1 stogroup. This last result is true even if the logical database xyz is associated with a different stogroup.
In this example, assume that a column named EMP_PHOTO with a data type of BLOB(110K) has been added to the sample employee table for each employee's photo. Create LOB table space PHOTOLTS in database DSN8D61A for the auxiliary table that will hold the BLOB data.
Let DB2 define the data sets for the table space, using storage group DSN8G610. For each data set, the primary space allocation is 3200 kilobytes, and the secondary space allocation is 1600 kilobytes. The data sets do not need to be erased before they are deleted.
CREATE LOB TABLESPACE PHOTOLTS
IN DSN8D61A
USING STOGROUP DSN8G610
PRIQTY 3200
SECQTY 1600
LOCKSIZE LOB
BUFFERPOOL BP16K0
GBPCACHE SYSTEM
LOG NO
CLOSE NO;