CREATE LOB TABLESPACE

The CREATE LOB TABLESPACE statement defines a large object (LOB) table space at the current server. If your data for a table does not fit entirely within a data page, you can define one or more columns as LOB columns. Each LOB column must have an associated auxiliary table in a LOB table space. If the table space for the base table is partitioned, an associated auxiliary table in a LOB table space is required for each LOB column, for each partition of the table space for the base table.

Start of change Do not use this statement if Db2 implicitly creates the LOB table space. For more information, see LOB table space implicit creation. End of change

For information about explicitly creating an auxiliary table, which defines the relationship between the LOB column of the base table and a LOB table space, see CREATE AUXILIARY TABLE statement.

For information about creating table spaces other than LOB table spaces, see CREATE TABLESPACE statement.

Invocation for CREATE LOB TABLESPACE

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 in effect. For more information, see Authorization IDs and dynamic SQL.

Authorization for CREATE LOB TABLESPACE

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
Installation SYSOPR authority (when the current SQLID of the process is set to SYSINSTL)

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 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 for CREATE LOB TABLESPACE

Notes:

¹ The same clause must not be specified more than one time.

gbpcache-block:

locksize-block:

using-block:

Notes:

¹ The same clause must not be specified more than one time.

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.

IN database-name

Specifies the database in which the table space is created. The LOB table space must be in the same database as its associated base table space. database-name must identify a database that exists at the current server and must not specify the following:

DSNDB06
A work file database
A TEMP database
An implicitly created database

DSNDB04 is the default.

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 you do not specify the BUFFERPOOL clause, 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 in SQL for more details about bpname. See -ALTER BUFFERPOOL command (Db2) for a description of active and inactive buffer pools.

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. CLOSE YES is the default value.
NO: Eligible for closing after all eligible CLOSE YES data sets are closed.

COMPRESS

Specifies whether data compression applies to the LOB data in the table space.

The following conditions are required for LOB compression in Db2 for z/OS®:

The zEDC hardware and software must be available and configured in the z/OS system. For more information, see Requirements for zEnterprise Data Compression.
In data sharing, system performance can degrade dramatically if a member accesses compressed LOB data and the zEDC hardware and software are not configured in z/OS.
The LOB table space must be associated with a base table that is in a universal table space.
The total length of the entire LOB must be larger than the defined data page size, otherwise the LOB is not compressed.

For more information, see Compressing LOB data.

YES: Specifies data compression. The LOB data is not compressed until the LOAD or REORG utility is run on the table in the table space, or until an insert operation is performed through the insert operation.
NO: Specifies no data compression for the table space.

DEFINE

Specifies when the underlying data sets for the table space are physically created.

YES

The data sets are allocated when the table space is created (when the CREATE LOB TABLESPACE statement is executed). YES is the default.

NO

The data sets are not allocated 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).

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 of each data set in integer gigabytes for each data set in the LOB table space.

If DSSIZE is not specified, the default value is 4 G. The maximum number of data sets is 254.

To specify a value greater than 4 G, 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.

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 LOB table spaces.

GBPCACHE

In a data sharing environment, specifies what pages of the table space are written to the group buffer pool. In a non-data-sharing environment, you can specify GBPCACHE, but it is ignored.

CHANGED

When there is inter-Db2 R/W interest on the table space, 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 open, and at least one member has it open for update. GBPCACHE CHANGED is the default.

Recommendation: 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 member when no other Db2 member has 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.

NONE

Indicates that no pages are to be cached to the group buffer pool. Db2 uses the group buffer pool only for cross-invalidation.

LOCKMAX

Specifies the maximum number of locks an application process can hold simultaneously in the table space. If a program requests more than that number, locks are escalated. The locks are released and the intent lock on the table space is promoted to S or X mode.

integer: Specifies the number of locks allowed before escalating, in the range 0–2147483647.
Zero (0) indicates that the number of locks on the table space are not counted and escalation does not occur.
SYSTEM: Specifies that Db2 determines the maximum number of locks that a program can hold simultaneously in the table space from the SYSIBMADM.MAX_LOCKS_PER_TABLESPACE built-in global variable.
For more information see MAX_LOCKS_PER_TABLESPACE built-in global variable.

The following table summarizes the results of specifying a LOCKSIZE value while omitting LOCKMAX.

LOCKSIZE	Resultant LOCKMAX
ANY	SYSTEM
TABLESPACE, LOB	0

If the lock size is TABLESPACE, LOCKMAX must be omitted, or its value must be 0.

LOCKSIZE

Specifies the size of locks used within the table space and, in some cases, also the threshold at which lock escalation occurs.

ANY

Specifies that Db2 can use any lock size.

In most cases, Db2 uses 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 value of the SYSIBMADM.MAX_LOCKS_PER_TABLESPACE built-in global variable), the LOB locks are released and locking is set at the next higher level. For more information, see MAX_LOCKS_PER_TABLESPACE built-in global variable.

If the table space is implicitly created, Db2 uses LOCKSIZE ANY.

TABLESPACE

Specifies table space locks.

LOB

Specifies LOB locks.

LOGGED or NOT LOGGED

Specifies whether changes that are made to the data in the specified table space are recorded in the log. This setting applies to all indexes of the table. Auxiliary indexes inherit the logging attribute from the associated base table space.

LOGGED: Specifies that changes that are made to the data in the specified table space are recorded in the log.
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.
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.

USING block

If you omit USING, the default storage group of the database must exist. 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 are VSAM linear data sets cataloged in the integrated catalog facility catalog that catalog-name identifies. For more information about catalog-name values, see Naming conventions in SQL.

More than one Db2 subsystem can share the integrated catalog facility catalogs with the current server. To avoid the chance of those subsystems attempting to assign the same name to different data sets, specify a catalog-name value that is not used by the other Db2 subsystems.

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 alias¹ 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 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.
FL 507 For any page size, if integer is greater than 1073741824, n is 1073741824.

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 Primary space allocation for Db2 table spaces and indexes.

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.

Start of change FL 507 For a large PRIQTY, the data sets for the table space are recommended to be associated with a DFSMS data class that is specified with multivolume data sets. This allows Db2 to create the data set spanning multiple volumes as needed. End of change

Start of change FL 507 To define multiple volumes for a data class, VOLUME COUNT or DYNAMIC VOLUME COUNT may need to be modified to specify a value larger than 1. For more information, see Defining volume and data set attributes for data classes. End of change

Start of change FL 507 In addition, the SPACE RELIEF CONSTRAINT may also need to be modified. For more information, see Specifying attributes to handle space constraints during allocation. End of change

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 Primary space allocation for Db2 table spaces and indexes.

Start of change FL 507 If integer is greater than 209715200, n is 209715200. End of change

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 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.

Notes for CREATE LOB TABLESPACE

Rules for primary and secondary space allocation

You can specify the primary and secondary space allocation or let Db2 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. For more information, see Db2 space allocation.

Determining the number of LOB table spaces to create

For partitioned tables, each partition of the base table requires a separate LOB table space, auxiliary table, and auxiliary index for each LOB column.

Alternative syntax and synonyms

db2z_addpartition

For compatibility with previous Db2 releases or function levels, the following keywords are supported:

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.

Although these keywords are supported as alternatives, they are not the preferred syntax.

Example for CREATE LOB TABLESPACE

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 DSN8D13A for the auxiliary table that will hold the BLOB data.

Let Db2 define the data sets for the table space, using storage group DSN8G130. 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. (Because ERASE NO is the default, the clause does not have to be explicitly specified to get that behavior.)

   CREATE LOB TABLESPACE PHOTOLTS
     IN DSN8D13A
     USING STOGROUP DSN8G130
       PRIQTY 3200
       SECQTY 1600
     LOCKSIZE LOB
     BUFFERPOOL BP16K0
     GBPCACHE SYSTEM
     NOT LOGGED
     CLOSE NO;

¹ The alias of an integrated catalog facility catalog.