Creating and Editing Auxiliary Tables

Go Up to Creating and Editing Database Dependent Objects

Auxiliary Tables are created to store the data of large object (LOB) Columns. LOB Columns are columns in a regular table, called the Base Table, whose data type is BLOB or CLOB and whose data is large objects. Since the data is very large, it is not stored with the other data in the table. Even though the base table holds the LOB Column logically, as a VARCHAR(4), the LOB Column's data is physically stored in the auxiliary table. Each LOB Column has at least one auxiliary table associated with it. Either one auxiliary table for each LOB Column of a non-partitioned table, or one auxiliary table for each LOB Column for each partition, if the LOB Column is defined in a partitioned base table. For example, if you have two LOB Columns in a Base Table with four partitions, you must also have a total of eight auxiliary tables.

Note: You must store Auxiliary tables each in their own auxiliary tablespaces, and you must create a unique index on them.

The following database platforms support auxiliary tables:

• IBM DB2 for OS/390 5.x - 10.x
• IBM DB2 for z/OS 7.x, 8.x, 9.x, 10.x, and 11.x

Note: The Auxiliary Table Wizard and Auxiliary Table Editor share the same options, except for Attachment Bindings options which are present only in the editor.

Add an Auxiliary Table

1. In the Data Model Explorer, expand the Physical Main Model, right-click the Auxiliary Tables node, and then select New Auxiliary Table.
2. Complete the Auxiliary Tables Wizard and then click Finish to create the auxiliary table.

TIP: Once you have created the auxiliary table, you can edit it by right-clicking the auxiliary table you want to change, and then selecting Edit Auxiliary Table.

Name page/tab

Enter the name of auxiliary table and its owner.

• The table name is the fully-qualified name of the auxiliary table and must not be the same as any table, view, alias, or synonym on the current server.
• The owner name is the name of the owner of the base table whose column you want to store in the auxiliary table. This name is the authorization ID that qualifies the table name. If not specified, the DBS subsystem user identifier for the connection used to create the database is used.

Base Table page/tab

• The base table identifier is the name of the base table whose column you want to store in the auxiliary table. The auxiliary table is created to hold the data of a LOB column. The LOB column logically belongs to a regular table, called the base table. However, because its data is too large to physically stored in the base table, the LOB column data is physically stored in the auxiliary table. The base table identifier is also made up of owner name, dot, table name. The drop-down list displays the name of all tables in the model that have tables defined with BLOB or CLOB columns. The base table identifier is optional ER/Studio Data Architect, but required for a valid CREATE AUX TABLE SQL statement.
• You must have one auxiliary table per LOB column for a non-partitioned base table. However, if a LOB Column is defined in a partitioned base table, then each LOB column must have a separate auxiliary table for each partition in the base table. The drop-down list displays the name of all columns in the selected base table that are defined as BLOB or CLOB datatypes. The LOB column name is optional ER/Studio Data Architect, but required for a valid CREATE AUX TABLE SQL statement.
• When creating an auxiliary table for a partitioned table, you can specify the number of the partition whose data you want to store. This is only valid for LOB Columns defined in base tables stored in partitioned tablespaces.

Storage page/tab

• If you have any defined databases in the model, you can click the list and select the database where the auxiliary table will be created. For more information, see Creating and Editing DataBases.
• Each auxiliary table must be defined in its own tablespace; i.e. you must have exactly one tablespace per auxiliary table. The tablespace name can be qualified with the database name in which the tablespace resides. Together the database.tablespace make up the tablespace identifier. The tablespace name is optional ER/Studio Data Architect, but required for a valid CREATE AUX TABLE SQL statement. For more information, see Creating and Editing Tablespaces.
• Each auxiliary table requires a Unique Index.

Index Options page/tab

• Concurrent Copy: ( COPY ) If selected, concurrent copies of the index are allowed, which can increase index availability by supporting index recovery.
• Do Not Close Dataset: ( CLOSE ) If selected, the index will not be closed when not in use. If not selected, the dataset can be closed if it is not in use and the maximum number of open datasets is reached or exceeded.
• None: If selected, no USING clause is included in the CREATE INDEX statement.
• VCAT: ( VCAT ) If selected, allocates space for the index on a data set managed using the virtual catalog ( VCAT ) whose name is specified next to this option, and the user manages the data sets required for the index. The VCAT method is usually used for defining system catalogs. It requires that the VSAM dataset be pre-defined before the index space is created.
• STOGROUP: ( STOGROUP ) If selected, stores the index on a data set managed by DB2 in the named storage group. Stogroup defined index spaces let DB2 do all the VSAM allocation work for you. If you have created a Stogroup for the data model, using the Stogroup Editor (see Creating and Editing StoGroups), you can select that Stogroup from the list. If you select STOGROUP, you can also define values for PRIQTY and SECQTY. To prevent wasted space for non-partitioned indexes, you should not define PRIQTY and SECQTY ; instead let DB2 manage the amount of primary and secondary space allocated for the index.
• PRIQTY:(PRIQTY) Specifies the minimum number of disk space in kilobytes to initially allocate for the index. The primary allocation should be large enough to handle the storage needs that you anticipate.
• SECQTY: (SECQTY) Specifies the amount of disk space in kilobytes to allocate to the index when it reaches the size of the primary allocation. If the secondary allocation space is too small, the data set might have to be extended more times to satisfy those activities that need a large space. The default value of -1 indicates that DB2 should determine the size of the secondary allocation, which is usually 10% of the primary allocation.
• Erase Data: ( ERASE ) If selected, specifies to erase the index data set when the index is deleted.
• Buffer Pool: ( BUFFERPOOL ) Identifies the buffer pool to be used for the index. The buffer pool used determines how the data blocks are cached in memory, in order to facilitate faster data access. BP0 is the default buffer pool for sorting.
• Percent Free: ( PCTFREE ) Specifies the maximum percentage of space in each data block to reserve for future updates. This reserved space helps to avoid row migration and chaining caused by an update operation that extends a data row's length. Tables that will not be updated should have this value set to 0.
• Free Page: ( FREEPAGE ) Specifies how often to leave a page of free space when index entries are created. One free page is left for every number of pages specified here in the range of 0 to 255.
• Piece Size: ( PIECESIZE ) Specifies the maximum addressable space of each data set for a secondary index. Specify the size followed by K (kilobyte), M (Megabyte), of G (Gigabyte). If you do not specify the unit of measure, kilobytes is assumed. Sizes specified in gigabytes are converted to megabytes in the DDL.
• GBP Cache: ( GPBCACHE ) In a data sharing environment, specifies what index pages are written to the group buffer pool. This option is ignored in a non-data-sharing environment unless the index is on a declared temporary table.
• Changed: Writes updated pages to the group buffer pool.
• All: Caches all pages to the group buffer pool as they are read in from DASD.
• None Uses the buffer pool only for cross-invalidation.

Definition page/tab

Enter or edit a definition for the auxiliary table. If the target database supports it, ER/Studio Data Architect adds this definition as a comment when generating SQL code.

DDL page/tab

Displays the CREATE AUXILIARY TABLE.statement needed to build the auxiliary table. ER/Studio Data Architect uses the platform-specific parser of the model's selected database platform to generate the auxiliary table.

Attachment Bindings tab

Bind an external piece of information, or attachment to the auxiliary table. You can also remove an attachment from an object, override an attachment binding's default value, or change the position of a bound attachment. To override the value of the attachment you have moved to the Selected Attachments grid, double-click the Value field of the target attachment. ER/Studio Data Architect opens the Value Override Editor or a list depending on the attachment datatype. Attachments are created in the Attachments folder of the Data Dictionary. For more information, see Attaching External Documents to the Data Model.

See Also

• Creating and Editing Tablespaces
• Attaching External Documents to the Data Model
• Creating and Editing DataBases