CREATE AUXILIARY TABLE

Invocation

Do not use this statement if the value of special register CURRENT RULES is 'STD' when the statement is executed. When the register's value is 'STD' and a base table is created with LOB columns or altered such that LOB columns are added, DB2 automatically creates the LOB table space, auxiliary table, and index on the auxiliary table for each LOB column. DB2 chooses the names and characteristics of these objects.

This statement can be embedded in an application program or issued interactively if the value of special register CURRENT RULES is 'DB2' when the statement is executed.

Authorization

The privilege set that is defined below must include at least one of the following:

The CREATETAB privilege for the database implicitly or explicitly specified by the IN clause
DBADM, DBCTRL, or DBMAINT authority for the database
SYSADM or SYSCTRL authority

Privilege set

If the statement is embedded in an application program, the privilege set is the privileges that are held by the authorization ID of the owner of the plan or package. If the specified table name includes a qualifier that is not the same as this authorization ID, the privilege set must include SYSADM or SYSCTRL authority, DBADM authority for the database, or DBCTRL authority for the database.

If the statement is dynamically prepared, the privilege set is the privileges that are held by the SQL authorization ID of the process. However, if the specified table name includes a qualifier that is not the same as this authorization ID, the following rules apply:

If the privilege set includes SYSADM or SYSCTRL authority, DBADM authority for the database, or DBCTRL authority for the database, any qualifier is valid.
If the privilege set does not include any of the authorities listed in item 1 above, the qualifier is valid only if it is the same as one of the authorization IDs of the process and the privilege set that are held by that authorization ID includes all privileges needed to create the table.

Syntax

CREATE {AUXILIARY | AUX} TABLE aux-table-name 
    IN [database-name] table-space-name STORES table-name
    COLUMN column-name [PART integer]

Parameters:

aux-table-name

Names the auxiliary table. The name must not identify a table, view, alias, or synonym that exists at the current server.

If qualified, the name can be a two-part or three-part name. If a three-part name is used, the first part must match the value of field DB2 LOCATION NAME on installation panel DSNTIPR at the current server. (If the current server is not the local DB2, this name is not necessarily the name in the CURRENT SERVER special register.) Whether the name is two-part or three-part, the authorization ID that qualifies the name is the table's owner.

If the table name is unqualified and the statement is embedded in a program, the owner of the table is the authorization ID that serves as the implicit qualifier for unqualified object names. This is the authorization ID in the QUALIFIER operand when the plan or package was created or last rebound. If QUALIFIER was not used, the owner of the table is the owner of the package or plan.

If the table name is unqualified and the statement is dynamically prepared, the SQL authorization ID is the owner of the table.

IN database-name.table-space-name or IN table-space-name

Identifies the table space in which the auxiliary table is created. The name must identify an empty LOB table space that currently exists at the current server.

If you specify a database and a table space, the table space must belong to the specified database. If you specify only a table space, it must belong to database DSNDB04.

Description

AUXILIARY or AUX

Specifies a table that is used to store the LOB data for a LOB column (or a column with a distinct type that is based on a LOB data type).

STORES table-name COLUMN column-name

Identifies the base table and the column of that table that is to be stored in the auxiliary table. If the base table is nonpartitioned, an auxiliary table must not already exist for the specified column. If the base table is partitioned, an auxiliary table must not already exist for the specified column and specified partition.

The encoding scheme for the LOB data stored in the auxiliary table is the same as the encoding scheme for the base table. It is either ASCII or EBCDIC depending on the value of the CCSID clause when the base table was created.

The auxiliary table can store a BLOB, CLOB, or DBCLOB value that is greater than 1 gigabyte in length only if the LOB table space for the auxiliary table was defined with LOG NO.

PART integer

Specifies the partition of the base table for which the auxiliary table is to store the specified column. You can specify PART only if the base table is defined in a partitioned table space, and no other auxiliary table exists for the same LOB column of the base table.

Note:

The number of auxiliary tables to create depends on the number of LOB columns in the base table and whether the base table is partitioned. If the base table is nonpartitioned, you need one LOB table space and one auxiliary table for each LOB column in the base table. If the base table is partitioned, you need one LOB table space and one auxiliary table for each partition for each LOB column. For example if the base table has four partitions and two LOB columns, you need to create a total of eight auxiliary tables in eight different LOB table spaces.

Example:

Assume that a column named EMP_PHOTO with a data type of BLOB(110K) has been added to sample employee table DSN8610.EMP for each employee's photo. Create auxiliary table EMP_PHOTO_ATAB to store the BLOB data for the BLOB column in LOB table space DSN8D61A.PHOTOLTS.

CREATE AUX TABLE EMP_PHOTO_ATAB
    IN DSN8D61A.PHOTOLTS
    STORES DSN8610.EMP
    COLUMN EMP_PHOTO;