CREATE TABLESPACE
The CREATE TABLESPACE statement defines a segmented (non-UTS), partitioned (non-UTS), or universal (UTS) table space at the current server.
Invocation
This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared only if DYNAMICRULES run behavior is implicitly or explicitly specified.
Authorization
The privilege set that is defined below must include at least one of the following:
- The CREATETS privilege for the database
- DBADM, DBCTRL, or DBMAINT authority for the database
- SYSADM or SYSCTRL authority
System DBADM
If the database is implicitly created, the database privileges must be on the implicit database or on DSNDB04.
Additional privileges might be required, as explained in the description of the BUFFERPOOL and USING STOGROUP clauses.
Privilege set: If the statement is embedded in an application program, the privilege set is the privileges that are held by the of the owner of the plan or package. If the application is bound in a trusted context with the ROLE AS OBJECT OWNER clause specified, a role is the owner. Otherwise, an authorization ID is the owner.
If the statement is dynamically prepared, the privilege set is the privileges that are held by the SQL authorization ID of the process unless the process is within a trusted context and the ROLE AS OBJECT OWNER clause is specified. In that case, the privileges set is the privileges that are held by the role that is associated with the primary authorization ID of the process.
Syntax
>>-CREATE--+-----+--TABLESPACE--table-space-name----------------> '-LOB-' .---------------------------------------------------. V (1) | >--------+-------------------------------------------+-+------->< | .-DSNDB04-------. | +-IN--+-database-name-+---------------------+ +-+---------------------------------------+-+ | +-| partition-by-growth-specification |-+ | | '-| partition-by-range-specification |--' | +-BUFFERPOOL--bpname------------------------+ +-CCSID--+---------+------------------------+ | +-ASCII---+ | | +-EBCDIC--+ | | '-UNICODE-' | | .-CLOSE YES-. | +-+-----------+-----------------------------+ | '-CLOSE NO--' | | .-COMPRESS NO--. | +-+--------------+--------------------------+ | '-COMPRESS YES-' | | .-DEFINE YES-. | +-+------------+----------------------------+ | '-DEFINE NO--' | +---DSSIZE--integer--G----------------------+ +-| free-block |----------------------------+ +-| gbpcache-block |------------------------+ +-LOCKMAX--+-SYSTEM--+----------------------+ | '-integer-' | +-| locksize-block |------------------------+ | .-LOGGED-----. | +-+------------+----------------------------+ | '-NOT LOGGED-' | +-MAXROWS--integer--------------------------+ +-SEGSIZE--integer--------------------------+ | .-TRACKMOD YES-. | +-+--------------+--------------------------+ | '-TRACKMOD NO--' | '-| using-block |---------------------------'
- The same clause must not be specified more than one time.
![Start of change](KC_ROOT/images/ddita/delta.gif)
partition-by-growth-specification:
![End of change](KC_ROOT/images/ddita/deltaend.gif)
.---------------------------. V (1) | >>-MAXPARTITIONS--integer--------+-------------------+-+------->< +-MEMBER CLUSTER----+ '-NUMPARTS--integer-'
- The same clause must not be specified more than one time.
![Start of change](KC_ROOT/images/ddita/delta.gif)
partition-by-range-specification:
![End of change](KC_ROOT/images/ddita/deltaend.gif)
.------------------------------------------------------------------------------------------------------------------. V (1) | >>-------+----------------------------------------------------------------------------------------------------------+-+->< +-NUMPARTS--integer--+---------------------------------------------------------------+--+----------------+-+ | | .-,---------------------------------------------------. | '-MEMBER CLUSTER-' | | | | .-----------------------------. | | | | | V V (1) | | | | | '-(----PARTITION--integer---------+-| using-block |----+-+-+--)-' | | +-| free-block |-----+ | | +-| gbpcache-block |-+ | | | .-COMPRESS NO--. | | | +-+--------------+---+ | | | '-COMPRESS YES-' | | | | .-TRACKMOD YES-. | | | '-+--------------+---' | | '-TRACKMOD NO--' | '-MEMBER CLUSTER-------------------------------------------------------------------------------------------'
- The same clause must not be specified more than one time.
free-block:
.---------------------------. V .-FREEPAGE 0--------. | (1) >>---+-+-------------------+-+-+------------------------------->< | '-FREEPAGE--integer-' | | .-PCTFREE 5---------. | '-+-PCTFREE--smallint-+-'
- The same clause must not be specified more than one time.
gbpcache-block:
.-GBPCACHE CHANGED-. >>-+------------------+---------------------------------------->< +-GBPCACHE ALL-----+ +-GBPCACHE SYSTEM--+ '-GBPCACHE NONE----'
![Start of change](KC_ROOT/images/ddita/delta.gif)
locksize-block:
![End of change](KC_ROOT/images/ddita/deltaend.gif)
.-LOCKSIZE ANY--------. >>-+---------------------+------------------------------------->< +-LOCKSIZE TABLESPACE-+ +-LOCKSIZE TABLE------+ +-LOCKSIZE PAGE-------+ +-LOCKSIZE ROW--------+ '-LOCKSIZE LOB--------'
using-block:
>>-USING--------------------------------------------------------> (1) >--+-VCAT--catalog-name-----------------------------------+---->< | .---------------------. | | V | (2) | '-STOGROUP--stogroup-name----+-----------------+-+-----' +-PRIQTY--integer-+ +-SECQTY--integer-+ | .-ERASE NO--. | '-+-----------+---' '-ERASE YES-'
- USING VCAT must not be specified if MAXPARTITIONS is also specified.
- The same clause must not be specified more than one time.
Description
- LOB
- 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. Do not specify LOB for a table space in a work file database.
- table-space-name
- Names the table space. The name, qualified with the database-name implicitly
or explicitly specified by the IN clause, must not identify a table
space, index space, or LOB table space that exists at the current
server
or that exists in the SYSPENDINGOBJECTS catalog table
.
A table space that is for declared temporary tables must be in the work file database. PUBLIC implicitly receives the USE privilege (without GRANT authority) on any table space created in the work file database. This implicit privilege is not recorded in the DB2® catalog, and it cannot be revoked.
- IN database-name
- Specifies the database in which the
table space is created. database-name must
identify a database that exists at the current server and must not
specify the following:
- DSNDB06 for any type of table space
- A work file database for a LOB table space
- A TEMP database
- An implicitly created database
DSNDB04 is the default.
- MAXPARTITIONS integer
- Specifies that the table space is a partition-by-growth (UTS) table
space. The data set for the first partition is allocated unless the DEFINE NO clause is specified
for the partition. The data sets for additional partitions are not allocated until they are needed.
integer specifies the maximum number of partitions to which the table space can grow. integer must be in the range of 1 to 4096, depending on the corresponding value of the DSSIZE clause. The following table shows the maximum value for MAXPARTITIONS in relation to the page size or DSSIZE value for the table space.
Table 1. Maximum value for MAXPARTITIONS given the page size or DSSIZE value for the table space DSSIZE value 4K page size 8K page size 16K page size 32K page size 1G - 4G (1 GB to 4 GB) 4096 4096 4096 4096 8G (8 GB) 2048 4096 4096 4096 16G (16 GB) 1024 2048 4096 4096 32G (32 GB) 512 1024 2048 4096 64G (64 GB) 254 512 1024 2048 128G (128 GB)
128
256
512
1024
256G (256 GB)
64
128
256
512
If MAXPARTITIONS is specified for a table space in a work file database, the table space will be a partition-by-growth (UTS) table space. If the DSSIZE or NUMPARTS clauses are not specified in addition to the MAXPARTITIONS clause for a table space in a work file database, the default values for DSSIZE and NUMPARTS will be used. If MAXPARTITIONS is not specified for a table space in a work file database, the table space will be a segmented table space.
Although physical data sets are not defined when the MAXPARTITIONS value is issued, there can be storage and cpu overhead. If an increase in the number of partitions is expected by using the MAXPARTITONS clause, be aware that specifying an value larger than necessary, such as 4096 (the maximum value), as a default for all of your partition-by-growth (UTS) table spaces can cause larger than expected storage requests.
- MEMBER CLUSTER
- Specifies
that data inserted by an insert operation is
not clustered by the implicit clustering index (the first index) or
the explicit clustering index. Instead, DB2 chooses
where to locate the data in the table space based on available space.
Do not specify MEMBER CLUSTER for segmented table spaces.
Do not specify MEMBER CLUSTER for a LOB table space or a table space in a work file database.
MEMBER CLUSTER can be specified with MAXPARTITIONS.
NUMPARTS integer
- integer
- Specifies the number of partitions. If n is specified for a partition-by-growth (UTS) table space, integer indicates the number of partitions that are initially created unless DEFINE NO is specified. integer must be a value between 1 and 4096 inclusive and must be less than or equal to the value that is specified for the MAXPARTITIONS clause.
The maximum size of each partition depends on the value that is specified for DSSIZE. If DSSIZE is not specified, the number of partitions that are specified determines the maximum size of each partition. For a summary of the values for the maximum size of each partition, see Table 2 and Table 3.
The maximum number of partitions that a table space with absolute numbering can have depends on the page size and DSSIZE. The total table space size depends on how many partitions it has and DSSIZE. Page size affects table size because it affects how many partitions a table space can have. Table 5 shows the maximum number of partitions for DSSIZE and page size and total table space size for both EA-enabled (extended addressability) and non-EA-enabled data sets. (Specifying a DSSIZE greater than 4GB requires EA-enabled data sets.)
For a description of the maximum size of a LOB table space (or the maximum size of LOB data for each column of a base table), see Large object table spaces.
If you omit NUMPARTS and MAXPARTITIONS, the table space is segmented with a SEGSIZE of 4, LOCKSIZE ANY (unless it is explicitly specified), is not partitioned, and initially occupies one data set.
Do not specify NUMPARTS for a LOB table space. Do not specify NUMPARTS for a table space in a work file database unless the MAXPARTITIONS clause is also specified.
- PARTITION integer
- Specifies to which partition the following
using-block or free-block applies.
integer can range from 1 to the number of partitions given by NUMPARTS.
You can code the PARTITION clause (and any using-block or free-block that follows it) as many times as needed. If you use the same partition number more than once, only the last specification for that partition is used.
The PARTITION clause must not be specified if the table space is a partition-by-growth (UTS) table space.
- BUFFERPOOL bpname
- Identifies
the buffer pool to be used for the table space and determines the
page size of the table space. For 4KB, 8KB, 16KB and 32KB page buffer
pools, the page sizes are 4 KB, 8 KB, 16 KB, and 32 KB, respectively.
The bpname must identify an activated buffer
pool, and the privilege set must include SYSADM or SYSCTRL authority,
or the USE privilege on the buffer pool. If the table space is to
be created in a work file database, you cannot specify 8KB and 16KB
buffer pools.
If you do not specify the BUFFERPOOL clause, the default buffer pool of the database is used unless the table space that is being created is a LOB table space. If you do not specify the BUFFERPOOL clause and the table space that is being created is a LOB table space, the default buffer pool is the buffer pool that is specified in the DEFAULT BUFFER POOL FOR USER LOB DATA field on installation panel DSNTIP1.
See Naming conventions for more details about bpname. See -ALTER BUFFERPOOL (DB2) for a description of active and inactive buffer pools.
- CCSID encoding-scheme
- Specifies the encoding scheme for tables stored in the table space.
If you do not specify a CCSID when it is allowed, the default is the encoding scheme of the database in which the table space resides, except for table spaces in database DSNDB04; for table spaces in DSNDB04, the default is the value of field DEF ENCODING SCHEME on installation panel DSNTIPF.
- ASCII
- Specifies that the data is to be encoded using ASCII CCSIDs. If the database in which the table space is to reside is already defined as ASCII, the ASCII CCSIDs associated with that database are used. Otherwise, the default ASCII CCSIDs of the server are used.
- EBCDIC
- Specifies that the data is to be encoded using EBCDIC CCSIDs.
- UNICODE
- Specifies that the data is to be encoded using the UNICODE CCSIDs of the server.
Usually, each encoding scheme requires only a single CCSID. Additional CCSIDs are needed when mixed, graphic, or Unicode data is used.
All data stored within a table space must use the same encoding scheme unless the table space is in a work file database.
Do not specify CCSID for a LOB table space or a table space in a work file database. The encoding scheme for a LOB table space is inherited from the base table space. A table space in a work file database does not have an associated encoding scheme because the table space can contain created and declared temporary tables with a mixture of encoding schemes.
- CLOSE
- When
the limit on the number of open data sets is reached, specifies the
priority in which data sets are closed.
- YES
- Eligible for closing before CLOSE NO data sets. This is the default unless the table space is in a work file database.
- NO
- Eligible for closing after all eligible CLOSE YES data sets are closed.
- COMPRESS
- Specifies whether data
compression applies to the rows of the table space or partition.
Do not specify COMPRESS for a LOB table space or a table space in a work file database.
For partitioned table spaces, the COMPRESS attribute for each partition is the value from the first of the following conditions that apply:
- The value specified in the COMPRESS clause in the PARTITION clause for the partition
- The value specified in the COMPRESS clause that is not in any PARTITION clause
- An implicit COMPRESS NO by default.
For more information about data compression, see Compressing your data.
- YES
Specifies data compression. The rows are not compressed until the LOAD or REORG utility is run on the table in the table space or partition, or until an insert operation is performed through the INSERT statement or the MERGE statement.
- NO
- Specifies no data compression for the table space or partition.
- DEFINE
- Specifies
when the underlying data sets for the table space are physically created.
- YES
- The data sets are created when the table space is created (the CREATE TABLESPACE statement is
executed). YES is the default.
If MAXPARTITIONS is also specified, only the first partition is created when the table space is created. Additional partitions are created as needed.
- 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 is specified). DEFINE NO is ignored for user-managed data sets (USING VCAT is specified). DB2 uses the
SPACE column in catalog table SYSTABLEPART to record the status of the data sets (undefined or
allocated).
Do not specify DEFINE NO for a table space in a work file database. DEFINE NO is not recommended if you intend to use any tools outside of DB2 to manipulate data, such as to load data, because data sets might then exist when DB2 does not expect them to exist. When DB2 encounters this inconsistent state, applications will receive an error.
For table spaces that are created with DEFINE NO, point-in-time recover will not work before data sets exist and before a recovery copy exists.
- DSSIZE integer G
- Specifies the maximum size for each partition, or for
LOB table spaces, each data set. If you specify DSSIZE, you must also specify the NUMPARTS,
MAXPARTITIONS, or LOB clause.
Do not specify DSSIZE for a table space in a work file database unless MAXPARTITIONS is also specified.
The following values are valid:- 1G
- 1 gigabyte
- 2G
- 2 gigabytes
- 4G
- 4 gigabytes
- 8G
- 8 gigabytes
- 16G
- 16 gigabytes
- 32G
- 32 gigabytes
- 64G
- 64 gigabytes
128G
128 gigabytes
256G
256 gigabytes
To specify a value greater than 4G, the data sets for the table space must be associated with a DFSMS data class that has been specified with extended format and extended addressability.
If NUMPARTS is also specified, the maximum size of each partition depends on the value of NUMPARTS, as shown in the following table. Otherwise, the maximum size of each partition is 4G.
Table 2. Maximum partition size depending on value of NUMPARTS Value of NUMPARTS Maximum partition size (default for DSSIZE) 1 to 16 4GB (4G) 17 to 32 2GB (2G) 33 to 64 1GB (1G) 65 to 254 4GB (4G) If NUMPARTS is greater than 254, the maximum partition size (and the default for DSSIZE) depends on the page size of the table space, as shown in the following table. 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.
Table 3. Maximum partition size depending on page size Page size Maximum partition size (default for DSSIZE) 4K 4GB (4G) 8K 8GB (8G) 16K 16GB (16G) 32K 32GB (32G) If DSSIZE is explicitly specified, the maximum number of partitions that can be specified or is the default is limited by the maximum table space size. For example:
- For a partitioned table space with a 4K page size, if DSSIZE 64GB is specified, the maximum NUMPARTS value is 256.
- For a partitioned table space with an 8K page size, if DSSIZE 64GB is specified, the maximum NUMPARTS value is 512.
For a partitioned table space with a 32K page size, if DSSIZE 128GB is specified, the maximum NUMPARTS value is 1024.
See Table 5 for more information on the relationship between DSSIZE, NUMPARTS, and the table space size.
For LOB table spaces, if DSSIZE is not specified, the default for the maximum size of each data set is 4GB. The maximum number of data sets is 254.
For a description of the maximum size of a LOB table space (or the maximum size of LOB data for each column of a base table), see Large object table spaces.
- FREEPAGE integer
- Specifies how often to leave a
page of free space when the table space or partition 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 value. However, if the table space is segmented and the
integer you specify is not less than the segment size, n is one less than the
segment size.
If the table space is segmented, the number of pages left free must be less than the SEGSIZE value. If the number of pages to be left free is greater than or equal to the SEGSIZE value, then the number of pages is adjusted downward to one less than the SEGSIZE value.
The default is FREEPAGE 0, leaving no free pages. Do not specify FREEPAGE for a LOB table space or a table space in a work file database.
For XML table spaces, this change has no effect until data in the table space is reorganized.
FREEPAGE does not apply to hash-organized table spaces.
Related information: - PCTFREE
smallint
- Indicates what percentage of each page to leave as free space when
the table is loaded or reorganized. smallint is in the range from 0 to 99.
The first record on each page is loaded without restriction. When additional records are loaded, at
least smallint percent of free space is left on each page.
The default is PCTFREE 5, which means that 5% of the space on each page is reserved as free space. Do not specify PCTFREE for a LOB table space or a table space in a work file database.
For XML table spaces, this change has no effect until data in the table space is reorganized.
PCTFREE does not apply to table spaces for hash-organized tables except when AUTOESTSPACE(YES) is specified in a REORG TABLESPACE invocation.
If the table space is partitioned, the values of FREEPAGE and PCTFREE for a particular partition are given by the first of these choices that applies:
- The values of FREEPAGE and PCTFREE given in the PARTITION clause for that partition
- The values given in a free-block that is not in any PARTITION clause
- The default values are FREEPAGE 0 and PCTFREE 5.
Related information: - GBPCACHE
- In a
data sharing environment, specifies what pages of the table space or partition are written to the
group buffer pool in a data sharing environment. In a non-data-sharing environment, you can specify
GBPCACHE for a table space other than one in a
work file database, but
it is ignored. Do not specify GBPCACHE for a table space in a work file database in either
environment (data sharing or non-data-sharing).
- CHANGED
- When there is inter-DB2 R/W interest on the table space or
partition, updated pages are written to the group buffer pool. When there is no inter-DB2 R/W interest, the group buffer pool is not used. Inter-DB2 R/W interest exists when more than one member in the data
sharing group has the table space or partition open, and at least one member has it open for update.
GBPCACHE CHANGED is the default. Recommendation:
For LOB table spaces, use the GBPCACHE CHANGED option. Due to the usage patterns of LOBs, the use of GBPCACHE CHANGED can help avoid excessive and synchronous writes to disk and to the group buffer pool.
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.
- ALL
- Indicates that pages are to be cached in the group buffer pool as they are read in from DASD.
Exception: In the case of a single updating DB2 when no other DB2s have any interest in the page set, no pages are cached in the group buffer pool.
If the table space is in a group buffer pool that is defined to be used only for cross-invalidation (GBPCACHE NO), ALL is ignored and no pages are cached to the group buffer pool.
- SYSTEM
- Indicates that only changed system pages within the LOB table space are to be cached to the
group buffer pool. A system page is a space map page or any other page that does not contain actual
data values.
SYSTEM applies only to LOB table spaces.
- NONE
- Indicates that no pages are to be cached to the group buffer pool. DB2 uses the group buffer pool only for cross-invalidation.
If you specify NONE, the table space or partition must not be in recover pending status and must be in the stopped state when the CREATE TABLESPACE statement is executed.
If the table space is partitioned, the value of GBPCACHE for a particular partition is given by the first of these choices that applies:
- The value of GBPCACHE given in the PARTITION clause for that partition. Do not use more than one gbpcache-block in any PARTITION clause.
- The value given in a gbpcache-block that is not in any PARTITION clause.
- The default value CHANGED.
- LOCKMAX
- 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 a table
space in a work file 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.
The following table summarizes the results of specifying a LOCKSIZE value while omitting LOCKMAX.LOCKSIZE Resultant LOCKMAX ANY SYSTEM TABLESPACE, TABLE, PAGE, ROW, or LOB 0 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.
- LOCKSIZE
- 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.
- ANY
- Specifies that DB2 can use any lock size.
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 (the NUMLKTS subsystem 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 segmented and not partitioned, the next higher level is the table. If the table space is partitioned, the next higher level is the partition.
If the table space is implicitly created, DB2 uses LOCKSIZE ROW.
- TABLESPACE
- Specifies table space locks.
- TABLE
- Specifies table locks. Use TABLE only for a segmented (non-UTS) table space. Do not use TABLE for a universal 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.
- LOGGED or NOT LOGGED
- Specifies whether changes that are made to the data in the
specified table space are recorded in the log.
- LOGGED
- Specifies that changes that are made to the data in the specified table space are recorded in
the log. This applies to all tables that are created in the specified table space and to all indexes
of those tables. XML table spaces and their indexes inherit the logging attribute from the
associated base table space. Auxiliary indexes also inherit the logging attribute from the
associated base table space.
LOGGED cannot be specified for table spaces in DSNDB06 (the DB2 catalog) or in a work file database.
LOGGED is the default.
- NOT LOGGED
- Specifies that changes that are made to data in the specified table space are not recorded in
the log. This parameter applies to all tables that are created in the specified table space and to
all indexes of those tables. XML table spaces and their indexes inherit the logging attribute from
the associated base table space. Auxiliary indexes inherit the logging attribute from the associated
base table space.
NOT LOGGED prevents undo and redo information from being recorded in the log; however, control information for the specified table space will continue to be recorded in the log.
NOT LOGGED cannot be specified for table spaces in the following databases:
- DSNDB06 (the DB2 catalog)
- a work file database
- MAXROWS integer
- 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 operations, LOAD, and REORG.
For LOAD and REORG (which do not apply for a table space in the work file 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.
- SEGSIZE integer
Specifies the type of table space that will be created depending on the values of the SEGSIZE, MAXPARTITIONS, and NUMPARTS clauses. integer specifies the number of pages that are to be assigned to each segment of the table space. integer must be a multiple of 4 between 0 and 64 (inclusive). integer cannot be 0 for an XML table space. Do not specify SEGSIZE for a LOB table space.
If SEGSIZE is not specified and only the NUMPARTS clause is specified, either a partitioned (non-UTS) table space or a partition-by-range (UTS) table space is created depending on the value of the DPSEGSZ subsystem parameter:
- If the value of DPSEGSZ is greater than 0 (zero), the table space will be a partition-by-range (UTS) table space with a SEGSIZE value that is equal to the value of DPSEGSZ.
- If the value of DPSEGSZ is equal to 0 (zero), the table space will be a partitioned (non-UTS) table space.
If SEGSIZE is not specified and only the MAXPARTITIONS clause is specified, a partition-by-growth (UTS) table space is created with a SEGSIZE that depends on the value of the DPSEGSZ subsystem parameter:
- If the value of DPSEGSZ is greater than 0 (zero), the table space will have a SEGSIZE value that is equal to the value of DPSEGSZ.
- If the value of DPSEGSZ is equal to 0 (zero), the table space will have a SEGSIZE value of 32.
The DPSEGSZ subsystem parameter has no effect on segmented table spaces.
The following table lists the type of table space depending on the specification of the SEGSIZE, MAXPARTITIONS, and NUMPARTS clauses:Table 4. Type of table space depending on value of SEGSIZE, MAXPARTITIONS, and NUMPARTS clauses SEGSIZE clause MAXPARTITIONS clause NUMPARTS clause Type of table space specified specified not specified partition-by-growth (UTS) table space specified not specified not specified segmented (non-UTS) table space specified not specified specified partition-by-range (UTS) table space specified with a value of 0 not specified specified partitioned (non-UTS) table space not specified specified specified partition-by-growth (UTS) table space - SEGSIZE = 32 if DPSEGSZ = 0
- SEGSIZE = n if DPSEGSZ = n, where n is a non-zero value for DPSEGSZ.
not specified specified not specified partition-by-growth (UTS) table space - SEGSIZE = 32 if DPSEGSZ = 0
- SEGSIZE = n if DPSEGSZ = n, where n is a non-zero value for DPSEGSZ.
not specified not specified specified One of the following: - partitioned (non-UTS) table space if DPSEGSZ is specified with a value of 0.
- partition-by-range (UTS) table space with a SEGSIZE = n if DPSEGSZ = n, where n is a non-zero value for DPSEGSZ.
not specified not specified not specified segmented (non-UTS) table space with an implicit specification of SEGSIZE 4 - TRACKMOD
- 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. Do not specify
TRACKMOD for a table space in a work file database.
- YES
- DB2 tracks changed pages in the space map pages to improve the performance of incremental image copy.
- NO
- DB2 does not track changed pages in the space map pages. It uses the LRSN value in each page to determine whether a page has been changed.
If the table space is partitioned, the value of TRACKMOD for a particular partition is given by the first of these choices that applies:
- The value of TRACKMOD given in the PARTITION clause for that partition.
- The value given in a trackmod-block that is not in any PARTITION clause.
- The default value YES.
If TRACKMOD is not specified, the default value as specified in the subsystem parameter IMPTKMOD is used.
- The components of the USING block are discussed below, first for nonpartitioned table spaces and then for partitioned table spaces. If you omit USING, the default storage group of the database must exist.
- USING block for nonpartitioned table spaces:
- For
nonpartitioned table spaces, the USING clause indicates whether the data set for the table space is
defined by you or by DB2. If DB2 is to define the data set, the clause also gives space allocation parameters and an erase
rule.
If you omit USING, DB2 defines the data sets using the default storage group of the database and the defaults for PRIQTY, SECQTY, and ERASE.
- VCAT catalog-name
- 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.
The data sets defined for the table space are linear VSAM data sets cataloged in an integrated catalog facility catalog identified by catalog-name. An alias1 must be used if the catalog name is longer than eight characters.
Conventions for table space data set names are given in DB2 Administration Guide. catalog-name is the first qualifier for each data set name.
One or more DB2 subsystems could share integrated catalog facility catalogs with the current server. To avoid the chance of having one of those subsystems attempt to assign the same name to different data sets, select a value for catalog-name that is not used by the other DB2 subsystems.
VCAT must not be specified if MAXPARTITIONS is also specified.
- STOGROUP stogroup-name
- Specifies that DB2 will
define and manage the data sets for the table space. Each data set will be defined on a volume of
the identified storage group. The values specified (or the defaults) for PRIQTY and SECQTY determine
the primary and secondary allocations for the data set. The storage group supplies the name of a
volume for the data set and the first-level qualifier for the data set name. The first-level
qualifier is also the name of, or an alias1 for,
the integrated catalog facility catalog on which the data set is to be cataloged. The naming
conventions for the data set are the same as if the data set is managed by the user. As was
mentioned above for VCAT, the first-level qualifier could cause naming conflicts if the local DB2 can share integrated catalog facility catalogs with other DB2 subsystems.
stogroup-name must identify a storage group that exists at the current server. SYSADM or SYSCTRL authority, or the USE privilege on the storage group, is required.
The description of the storage group must include at least one volume serial number, or it must indicate that the choice of volumes is left to Storage Management Subsystem (SMS). If volume serial numbers appear in the description, each must identify a volume that is accessible to z/OS® for dynamic allocation of the data set, and all identified volumes must be of the same device type.
The integrated catalog facility catalog used for the storage group must not contain an entry for the first data set of the table space. If the integrated catalog facility catalog is password protected, the description of the storage group must include a valid password.
- PRIQTY integer
Specifies the minimum primary space allocation for a DB2-managed data set. integer must be a positive integer, or -1. In general, when you specify PRIQTY with a positive integer value, the primary space allocation is at least n kilobytes, where n is the value of integer. However, the following exceptions exist:
For non-LOB table spaces, the exceptions are:
- For 4KB page sizes, if integer is greater than 0 and less than 12, n is 12.
- For 8KB page sizes, if integer is greater than 0 and less than 24, n is 24.
- For 16KB page sizes, if integer is greater than 0 and less than 48, n is 48.
- For 32KB page sizes, if integer is greater than 0 and less than 96, n is 96.
For any page size, if integer is greater than 67108864, n is 67108864.
For LOB table spaces, the exceptions are:
- For 4KB page sizes, if integer is greater than 0 and less than 200, n is 200.
- For 8KB page sizes, if integer is greater than 0 and less than 400, n is 400.
- For 16KB page sizes, if integer is greater than 0 and less than 800, n is 800.
- For 32KB page sizes, if integer is greater than 0 and less than 1600, n is 1600.
For any page size, if integer is greater than 67108864, n is 67108864.
If you do not specify PRIQTY, or specify PRIQTY with a value of -1, DB2 uses a default value for the primary space allocation; for information on how DB2 determines the default value, see Rules for primary and secondary space allocation.
If you specify PRIQTY, and do not specify a value of -1, DB2 specifies the primary 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 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 DEFINE CLUSTER command.
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 enable a data set to reach its maximum size without running out of extents, it is recommended that you allow DB2 to automatically choose the value of the secondary space allocations for extents.
If you do choose to explicitly specify SECQTY, 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)
- SECQTY integer
- Specifies the minimum secondary space allocation for a DB2-managed data set.
integer must be a positive integer, 0, or -1.
If you do not specify SECQTY, or specify SECQTY with a value of -1, DB2 uses a formula to determine a value. For information on the actual value that is used for secondary space allocation, whether you specify a value or not, see Rules for primary and secondary space allocation.
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 integer, 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. To more closely estimate the actual amount of storage, see DEFINE CLUSTER command.
- ERASE
- Indicates whether the DB2-managed data sets for the table space or partition are to be erased when they are deleted during the execution of a utility or an SQL statement that drops the table space.
- NO
- Does not erase the data sets. Operations involving data set deletion will perform better than ERASE YES. However, the data is still accessible, though not through DB2. This is the default.
- YES
- Erases the data sets. As a security measure, DB2 overwrites all data in the data sets with zeros before they are deleted.
- USING block for partitioned table spaces:
- If the
table space is partitioned, there is a USING clause for each partition; either one you give
explicitly or one provided by default. Except as explained below, the meaning of the clause and the
rules that apply to it are the same as for a nonpartitioned table space.
The USING clause for a particular partition is the first of these choices that can be found:
- A USING clause in the PARTITION clause for the partition
- A USING clause that is not in any PARTITION clause
- An implicit USING STOGROUP clause that identifies the default storage group of the database and accepts the defaults for PRIQTY, SECQTY, and ERASE
- VCAT catalog-name
- Indicates that the
data set for the partition is managed by the user using the naming conventions set forth in
DB2 Administration Guide. As was true for the
nonpartitioned case, catalog-name identifies the catalog for the data set
and supplies the first-level qualifier for the data set name.
One or more DB2 subsystems could share integrated catalog facility catalogs with the current server. To avoid the chance of having one of those subsystems attempt to assign the same name to different data sets, select a value for catalog-name that is not used by the other DB2 subsystems.
DB2 assumes one and only one data set for each partition.
- STOGROUP stogroup-name
- Indicates that DB2 will
create a data set for the partition with the aid of a storage group named
stogroup-name. The data set is defined during the execution of this
statement. DB2 assumes one and only one data set for each
partition.
The stogroup-name must identify a storage group that exists at the current server and the privilege set must include SYSADM authority, SYSCTRL authority, or the USE privilege for the storage group. The integrated catalog facility catalog used for the storage group must not contain an entry for that data set.
When USING STOGROUP is specified for a partition, the defaults for PRIQTY, SECQTY, and ERASE are the values specified in the USING STOGROUP clause that is not in any PARTITION clause. If that USING STOGROUP clause is not specified, the defaults are those specified in the description of PRIQTY, SECQTY, and ERASE.
Notes
- Segmented table spaces:
- If neither LOB, NUMPARTS, nor SEGSIZE are specified, the table
space that is created is a segmented table space. Related links: Types of DB2 table spaces
- Universal (UTS) table spaces:
- If NUMPARTS and SEGSIZE are specified, the table space that is created is a partition-by-range
(UTS) table space. If MAXPARTITIONS or MAXPARTITIONS and SEGSIZE are specified, the table space that
is created is a partition-by-growth universal table space. If a partition-by-range (UTS) table space
contains an XML column, the corresponding XML table space will also be partition-by-range (UTS)
table space. Related links: Universal (UTS) table spaces
Applications that use currently committed access with table spaces that specify LOCKSIZE PAGE:
To ensure that readers of data in a table space that is defined with LOCKSIZE PAGE can always access currently committed data, set MAXROWS to 8 or less. If MAXROWS is greater than 8, readers might need to wait for insert or delete operations on tables in the table space to commit before the readers can access rows in the tables.
Related links: Accessing currently committed data to avoid lock contention- Table spaces in a work file database:
- The following restrictions apply to table spaces created in a
work file database:
- They can be created for another member only if both the executing DB2 subsystem and the other member can access the work file data sets. That is required whether the data sets are user-managed or in a DB2 storage group.
- They cannot use 8 KB or 16 KB page sizes. (The buffer pool in which you define the table space determines the page size. For example, a table space that is defined in a 4 KB buffer pool has 4 KB page sizes.)
- When you create a table space in a work file database, the following
clauses are not allowed:
- CCSID
- COMPRESS
- DEFINE NO
- DSSIZE1
- FREEPAGE
- GBPCACHE
- LARGE
- LOB
- LOCKPART
- LOCKSIZE
- LOGGED
- MAXROWS
- MEMBER CLUSTER
- NOT LOGGED
- NUMPARTS1
- PCTFREE
- TRACKMOD
1: DSSIZE and NUMPARTS must not be specified for a table space in a work file database unless MAXPARTITIONS is also specified.
- Table spaces for declared temporary tables:
- Declared temporary tables and sensitive static scrollable cursors
must reside in segmented table spaces in the work file database. At
least one table space with a 32KB page size must exist in the work
file database before a declared temporary table can be defined and
used or before sensitive static scrollable cursors are opened.
Table spaces in the work file database are shared by work files, created and declared global temporary tables and sensitive static scrollable cursor result tables. You cannot specify which table space is to be used for any specific object.
When you create table spaces in the work file database, it is recommended that you give each table space the same segment size, with the same minimum primary and secondary space allocation values for the data sets, to maximize the use of all the table spaces for all objects in all application processes.
- Creating LOB table spaces:
- When you create a LOB table space, the following clauses are not
allowed:
- CCSID
- COMPRESS
- FREEPAGE
- LOCKSIZE TABLE
- LOCKSIZE PAGE
- LOCKSIZE ROW
- MAXPARTITIONS
- NUMPARTS
- PCTFREE
- SEGSIZE
- TRACKMOD
- Recommended GBPCACHE setting for LOB table spaces:
- For LOB table spaces, use the GBPCACHE CHANGED option instead of the GBPCACHE SYSTEM option. Due to the usage patterns of LOBs, the use of GBPCACHE CHANGED can help avoid excessive and synchronous writes to disk and the group buffer pool.
- Altering the logging attribute of a table space:
- See ALTER TABLESPACE for information about altering the logging attributes of a table space.
- Table space row formats:
- Depending on the value of the RRF subsystem parameter, newly created table spaces will be in either re-ordered row format or basic row format. When the value of the RRF parameter is ENABLE, table spaces will be created in re-ordered row format. When the value of the RRF parameter is DISABLE, newly created table spaces will be created in basic row format. This includes universal table spaces, except for XML table spaces and hash table spaces, which are always created in re-ordered row format, regardless of the value of the RRF parameter.
- Making a partitioned table space larger:
- Depending on the needs of your application, you might need to
increase the size of a partitioned table space to hold more data by
either adding more partitions or by increasing the size of the existing
partitions:
- To add more partitions, use the ALTER TABLE statement with the ADD PARTITION clause.
To increase the size of the partitions, use the following steps:
- If the table space is a partition-by-range (UTS) table space, specify a larger DSSIZE using the ALTER TABLESPACE statement if the DSSIZE of the table space is not already at the maximum.
- If the table space is not a partition-by-range (UTS) table space:
- Convert the table space to a partition-by-range (UTS) table space by specifying a SEGSIZE value and a NUMPARTS value using the ALTER TABLESPACE statement
- Run the REORG utility with the SHRLEVEL CHANGE or SHRLEVEL REFERENCE option on the table space to apply the new SEGSIZE value
- Specify a larger DSSIZE using the ALTER TABLESPACE statement
Related links:
- Redistributing data between existing partitions:
- If you need to redistribute the data between the existing partitions
to make better use of the space within the existing table, you can
use either of these two methods:
- Use the ALTER TABLE statement with the ALTER PARTITION clause. You can alter the partitions to specify new partition boundaries to explicitly specify how to redistribute the data. Any affected partitions are set to REORG-pending status.
- Use the REORG utility with the REBALANCE keyword. REBALANCE specifies that the data is evenly redistributed across the partitions that are reorganized. SeeREORG TABLESPACE for information about using the REORG utility.
- Rules for primary and secondary space allocation:
- You can specify the primary and secondary space allocation for
table spaces and indexes or allow DB2 to
choose them. Having DB2 choose
the values, especially the secondary space quantity, increases the
possibility of reaching the maximum data set size before running out
of extents.In the following rules that describe how allocation works, these terms are used:
- PRIQTY, SECQTY
- The keywords for CREATE TABLESPACE, ALTER TABLESPACE, CREATE INDEX, and ALTER INDEX.
- specified-priqty
- The user-specified value for PRIQTY.
- specified-secqty
- The user-specified value for SECQTY.
- actual-priqty
- The actual primary space allocation, in kilobytes.
- actual-priqty-cylinders
- The actual primary space allocation, in cylinders.
- actual-secqty
- The actual secondary space allocation, in kilobytes.
- actual-secqty-cylinders
- The actual secondary space allocation, in cylinders.
- calculated-extent-cylinders
- A value that is calculated by DB2 using
a sliding scale. A sliding scale means that the first secondary
extent allocations are smaller than later secondary allocations. For
example, Figure 1 shows
the sliding scale of secondary extent allocations that DB2 uses for a 64-GB data set. The size of each
secondary extent is larger for each secondary extent that is allocated
up to the 127th extent. For the 127th secondary extent and any subsequent
extents, the secondary size allocation is 559 cylinders.
Figure 1. Sliding scale allocation of secondary extents for a 64 GB data set
The rules are:
- Rule 1 (for primary space allocation)
If PRIQTY is specified and specified-priqty is not equal to -1, actual-priqty is at least specified-priqty KB.
If PRIQTY is not specified or specified-priqty is equal to -1, actual-priqty is determined as follows:- For a table space, if the TSQTY subsystem parameter value is specified
and is greater than 0, actual-priqty is at least the value
of TSQTY.
If the TSQTY subsystem parameter is not specified or is 0, actual-priqty is one cylinder for a non-LOB table space. actual-priqty is 10 cylinders for a LOB table space.
- For an index, if the IXQTY subsystem parameter value is specified
and is greater than 0, actual-priqty is at least the value
of IXQTY.
If the IXQTY subsystem parameter is not specified or is 0, actual-priqty is one cylinder.
- For a table space, if the TSQTY subsystem parameter value is specified
and is greater than 0, actual-priqty is at least the value
of TSQTY.
- Rule 2 (for secondary space allocation) If SECQTY is not specified, the following formulas determine actual-secqty:
- If the maximum size of a data set in the table space or index
is less than 32 GB, the formula is:
actual-secqty-cylinders= MAX(0.1*actual-priqty-cylinders, MIN(calculated-extent-cylinders, 127))
- If the maximum size of a data set in the table space or index
is 32 GB or greater, the formula is:
actual-secqty-cylinders= MAX(0.1*actual-priqty-cylinders, MIN(calculated-extent-cylinders, 559))
- If the maximum size of a data set in the table space or index
is less than 32 GB, the formula is:
- Rule 3 (for secondary space allocation)
If SECQTY is 0, actual-secqty is 0.
- Rule 4 (for secondary space allocation)
This is the only rule that depends on the value of subsystem parameter MGEXTSZ (field OPTIMIZE EXTENT on installation panel DSNTIP7).
If MGEXTSZ is YES:- If SECQTY is specified and specified-secqty is not equal
to -1 or 0, the following formulas determine actual-secqty:
- If the maximum size of a data set in the table space or index
is less 32 GB, the formula is:
actual-secqty-cylinders= MAX(MIN(calculated-extent-cylinders, 127),specified-secqty-cylinders)
- If the maximum size of a data set in the table space or index
is 32 GB or greater, the formula is:
actual-secqty-cylinders= MAX(MIN(calculated-extent-cylinders, 559),specified-secqty-cylinders)
- If the maximum size of a data set in the table space or index
is less 32 GB, the formula is:
If MGEXTSZ is NO:- For a table space, if SECQTY is n, the secondary space
allocation is at least n kilobytes, with the following exceptions:
- If SECQTY is greater than 4194304, n is 4194304 kilobytes.
- For LOB table spaces:
- For 4KB page sizes, if integer is greater than 0 and less than 200, n is 200.
- For 8KB page sizes, if integer is greater than 0 and less than 400, n is 400.
- For 16KB page sizes, if integer is greater than 0 and less than 800, n is 800.
- For 32KB page sizes, if integer is greater than 0 and less than 1600, n is 1600.
- For any page size, if integer is greater than 4194304, n is 4194304.
- For an index, if SECQTY is integer, the secondary space
allocation is at least n kilobytes, where n is:
- 12
- If SECQTY and PRIQTY are omitted
- 4194304
- If integer is greater than 4194304
- integer
- If integer is not greater than 4194304
- If SECQTY is specified and specified-secqty is not equal
to -1 or 0, the following formulas determine actual-secqty:
- Rule
5 (for secondary space allocation): When a table space requires
a new piece, the primary allocation quantity of the new piece is determined
as follows:
- If the value of subsystem parameter MGEXTSZ is NO, the primary quantity from rule 1 is used.
- Otherwise, the maximum of the following values is used:
- The quantity that is calculated through sliding scale methodology
- The primary quantity from rule 1
- The specified SECQTY value
Determining the maximum number of partitions that a table space with absolute numbering can have
Table 5. Table space size given page size and partitions (for table spaces with absolute numbering) Type of RID Page size DSSIZE Maximum number of partitions Total table space size 5-byte EA 4KB 1GB 4096 4TB 5-byte EA 4KB 2GB 4096 8TB 5-byte EA 4KB 4GB 4096 16TB 5-byte EA 4KB 8GB 2048 16TB 5-byte EA 4KB 16GB 1024 16TB 5-byte EA 4KB 32GB 512 16TB 5-byte EA 4KB 64GB 256
16TB 5-byte EA
4KB
128GB
128
16TB
5-byte EA
4KB
256GB
64
16TB
5-byte EA 8KB 1GB 4096 4TB 5-byte EA 8KB 2GB 4096 8TB 5-byte EA 8KB 4GB 4096 16TB 5-byte EA 8KB 8GB 4096 32TB 5-byte EA 8KB 16GB 2048 32TB 5-byte EA 8KB 32GB 1024 32TB 5-byte EA 8KB 64GB 512 32TB 5-byte EA
8KB
128GB
256
32TB
5-byte EA
8KB
256GB
128
32TB
5-byte EA 16KB 1GB 4096 4TB 5-byte EA 16KB 2GB 4096 8TB 5-byte EA 16KB 4GB 4096 16TB 5-byte EA 16KB 8GB 4096 32TB 5-byte EA 16KB 16GB 4096 64TB 5-byte EA 16KB 32GB 2048 64TB 5-byte EA 16KB 64GB 1024 64TB 5-byte EA
16KB
128GB
512
64TB
5-byte EA
16KB
256GB
256
64TB
5-byte EA 32KB 1GB 4096 4TB 5-byte EA 32KB 2GB 4096 8TB 5-byte EA 32KB 4GB 4096 16TB 5-byte EA 32KB 8GB 4096 32TB 5-byte EA 32KB 16GB 4096 64TB 5-byte EA 32KB 32GB 4096 128TB 5-byte EA 32KB 64GB 2048 128TB 5-byte EA
32KB
128GB
1024
128TB
5-byte EA
32KB
256GB
512
128TB
5-byte (non-EA) LARGE 4KB (4GB) 4096 16TB - Alternative syntax and synonyms:
- For
compatibility with previous releases of DB2,
the following keywords are supported:
- You can specify the LOCKPART clause, but it has no effect. Starting with Version 8, DB2 treats all table spaces as if they were defined
as LOCKPART YES. LOCKPART YES specifies the use of selective
partition locking. When all the conditions for selective partition
locking are met, DB2 locks only
the partitions that are accessed. When the conditions for selective
partition locking are not met, DB2 locks
every partition of the table space.
LOCKSIZE TABLESPACE and LOCKPART YES are mutually exclusive.
- When creating a partitioned table space, you can specify PART as a synonym for PARTITION.
- When specifying the logging attributes for a table space, you can specify LOG YES as a synonym for LOGGED, and you can specify LOG NO as a synonym for NOT LOGGED.
You can specify the LARGE clause when creating partitioned table spaces, but DSSIZE is the preferred clause to use when specifying the partition size.
Although these keywords are supported as alternatives, they are not the preferred syntax.
- You can specify the LOCKPART clause, but it has no effect. Starting with Version 8, DB2 treats all table spaces as if they were defined
as LOCKPART YES. LOCKPART YES specifies the use of selective
partition locking. When all the conditions for selective partition
locking are met, DB2 locks only
the partitions that are accessed. When the conditions for selective
partition locking are not met, DB2 locks
every partition of the table space.
Examples
Example 1: Create table space DSN8S10D in database DSN8D10A. Let DB2 define the data sets, using storage group DSN8G100. The primary space allocation is 52 kilobytes; the secondary, 20 kilobytes. The data sets need not be erased before they are deleted.
CREATE TABLESPACE DSN8S10D
IN DSN8D10A
USING STOGROUP DSN8G100
PRIQTY 52
SECQTY 20
ERASE NO
LOCKSIZE PAGE
BUFFERPOOL BP1
CLOSE YES;
For the above example, the underlying data sets for the table space will be created immediately, which is the default (DEFINE YES). If you want to defer the creation of the data sets until data is first inserted into the table space, you would specify DEFINE NO instead of accepting the default behavior.
Example 2: Assume that a large query database application uses a table space to record historical sales data for marketing statistics. Create large table space SALESHX in database DSN8D10A 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 table space, using storage group DSN8G100. 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.
CREATE TABLESPACE SALESHX
IN DSN8D10A
USING STOGROUP DSN8G100
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;
Example 3: 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 DSN8D10A for the auxiliary table that will hold the BLOB data.
CREATE LOB TABLESPACE PHOTOLTS
IN DSN8D10A
USING STOGROUP DSN8G100
PRIQTY 3200
SECQTY 1600
LOCKSIZE LOB
BUFFERPOOL BP16K0
GBPCACHE SYSTEM
NOT LOGGED
CLOSE NO;
CREATE TABLESPACE TS1
IN DSN8D10A
USING STOGROUP DSN8G100
NUMPARTS 55
SEGSIZE 16
LOCKSIZE ANY;
CREATE TABLESPACE TS2
IN DSN8D10A
USING STOGROUP DSN8G100
NUMPARTS 7
(
PARTITION 1 COMPRESS YES,
PARTITION 3 COMPRESS YES,
PARTITION 5 COMPRESS YES,
PARTITION 7 COMPRESS YES
)
SEGSIZE 64
DEFINE NO;
CREATE TABLESPACE TS01TS IN TS01DB USING STOGROUP SG1
DSSIZE 2G
MAXPARTITIONS 24
LOCKSIZE ANY
SEGSIZE 4;