DB2 DBTUNE configuration parameters

DB2 DBTUNE configuration parameters
Release 9.3

• Establish the storage characteristics of tables and indexes
• Define the way certain types of data will be stored
• Define the environment of a configuration keyword
• Include comments that describe the configuration keyword

NOTE: For the XML parameters, <n> refers to the xml_column_id associated with a specific XML column.

Functional descriptions of parameters

• Business table and index storage parameters
A business table is any DB2 table created by an ArcSDE client, the sdetable administration command, or the ArcSDE C API SE_table_create function.
Use the DBTUNE table's B_STORAGE parameter to define the storage configuration of a business table.
Four index storage parameters exist to support the creation of business table indexes.
The B_INDEX_USER parameter holds the storage configuration for user-defined indexes created with the C API function SE_table_create_index and the create_index operation of the sdetable command.
The B_INDEX_ROWID parameter holds the storage configuration of the index ArcSDE creates on a registered table's objectID column, also referred to as the ROWID.

NOTE: ArcSDE registers all tables that it creates. Tables not created by ArcSDE can also be registered with the register operation of the sdetable or sdelayer commands. The SDE.TABLE_REGISTRY system table maintains a list of the currently registered tables.

The B_INDEX_SHAPE parameter only applies to DB2 for Linux, UNIX, and Windows, and it holds the storage configuration of the spatial column index that ArcSDE creates when a spatial column is added to a business table. This index is created by the ArcSDE C API function SE_layer_create. This function is called by ArcGIS when it creates a feature class and by the add operation of the sdelayer command.
The B_INDEX_TO_DATE parameter specifies the storage for the index R<registration_id>_sde_todate. This index is created when archiving is enabled on a business table, and is used when updating the history table during an archive operation.
• Adds and Deletes tables storage parameters
Registering a business table as versioned allows multiple users to maintain and edit an object. At appropriate intervals, each user merges the changes he or she has made with the changes made by other users and reconciles any conflicts that arise when the same rows are modified.
ArcSDE creates two tables—the adds table and deletes table—for each business table that is registered as versioned.
Adds table parameters
The A_STORAGE parameter maintains the storage configuration of the adds table. Four other storage parameters hold the storage configuration of the indexes of the adds table. The adds table is named A<n>, where <n> is the registration ID of the business table listed in the TABLE_REGISTRY system table. For instance, if the business table ROADS is listed with a registration ID of 10, ArcSDE creates the adds table as A10.
The A_INDEX_ROWID parameter holds the storage configuration of the index that ArcSDE creates on the versioned object ID column, also referred to as the ROWID. The adds table ROWID index is named A<n>_ROWID_IX1, where <n> is the business table's registration ID, with which the adds table is associated.
The A_INDEX_STATEID parameter holds the storage configuration of the index that ArcSDE creates on the adds table's SDE_STATE_ID column. The SDE_STATE_ID column index is called A<n>_STATE_IX2, where <n> is the business table's registration ID, with which the adds table is associated.
The A_INDEX_SHAPE parameter is only used for DB2 in Linux, UNIX, and Windows, and it holds the storage configuration of the index that ArcSDE creates on the adds table's spatial column. If the business table contains a spatial column, the column and the index on it are duplicated in the adds table. The adds table's spatial column index is called A<n>_IX1_A, where <n> is the layer ID of the feature class as it is listed in the LAYERS table.
The A_INDEX_USER parameter holds the storage configuration of user-defined indexes that ArcSDE creates on the adds table. The user-defined indexes on the business tables are duplicated on the adds table.
Deletes table parameters
The D_STORAGE parameter holds the storage configuration of the deletes table. Two other storage parameters hold the storage configuration of the indexes that ArcSDE creates on the deletes table. The deletes table is named D<n>, where <n> is the registration ID of the business table listed in the TABLE_REGISTRY system table. For instance, if the business table ROADS is listed with a registration ID of 10, ArcSDE creates the deletes table as D10.
The D_INDEX_STATE_ROWID parameter holds the storage configuration of the D<n>_IDX1 index that ArcSDE creates on the deletes table's SDE_STATE_ID and SDE_DELETES_ROW_ID columns.
The D_INDEX_DELETED_AT parameter holds the storage configuration of the D<n>_IDX2 index that ArcSDE creates on the deletes table's SDE_DELETED_AT column.
For more information on the structure of adds and deletes tables and how they are used, see Versioned tables in a geodatabase in DB2 in the "Geodatabase data storage and schema" section of the help.
• Raster table and index storage parameters
A raster column added to a business table is actually a foreign key reference to raster data stored in a schema consisting of four tables and five supporting indexes.
The RAS_STORAGE storage parameter holds the DB2 CREATE TABLE storage configuration of the RAS table.
The RAS_INDEX_ID storage parameter holds the DB2 CREATE INDEX storage configuration of the RAS table index.
The BND_STORAGE storage parameter holds the DB2 CREATE TABLE storage configuration of the BND table.
The BND_INDEX_COMPOSITE storage parameter holds the DB2 CREATE INDEX storage configuration of the BND table's composite column index.
The BND_INDEX_ID storage parameter holds the DB2 CREATE INDEX storage configuration of the BND table's ID column index.
The AUX_STORAGE storage parameter holds the DB2 CREATE TABLE storage configuration of the AUX table.
The AUX_INDEX_COMPOSITE storage parameter holds the DB2 CREATE INDEX storage configuration of the AUX table's index.
The BLK_STORAGE storage parameter holds the DB2 CREATE TABLE storage configuration of the BLK table.
The BLK_INDEX_COMPOSITE storage parameter holds the DB2 CREATE TABLE storage configuration of the BLK table's index.
The BLK_BLOB_OPTION storage parameter stores the configuration properties of BLOB columns in the raster BLK table.
For DB2 on Linux, UNIX, and Windows, you should create a very large table space to store the raster blocks (BLK) table and create a second, medium-sized table space to store the remaining raster tables and indexes.
It is also recommended that, for DB2 on Linux, UNIX, and Windows, you set the BLK_BLOB_OPTION to NOT LOGGED COMPACT. Setting this parameter to any other value can take up a lot more table space when loading raster data.
When you create the table spaces for the raster blocks table in DB2, it is recommended you use an extent size of 64. The extent size specifies the number of page-size pages that will be written to a container before proceeding to the next container. The extent size is defined at the time of table space creation and cannot be easily modified afterward.
There is an additional type of raster table—the raster attribute table. These tables store attribute values based on cell values in the raster. The B_STORAGE parameter defines the storage of these tables. If you need to define a different storage location for these tables than you do for other feature class business tables, be sure to create a raster keyword you can use when creating raster datasets and raster catalogs that specifies different storage information for the raster attribute tables.
To learn more about raster attribute tables, see Raster dataset attribute tables in the "Image and raster data management" section of the help. To learn how to create custom configuration keywords, see DBTUNE configuration keywords.
For more information on how rasters are stored, see Raster datasets and raster catalogs in a geodatabase in DB2 in the "Geodatabase data storage and schema" section of the help.
• XML type parameters

NOTE: XML parameters do not apply to geodatabases on DB2 for z/OS. Additionally, if you do not use XML columns and XML documents in your geodatabase in DB2 on Linux, UNIX, or Windows, you don't need to configure these parameters.

DB2 XML parameters include the following:
XML_INDEX_TAGS_TABLE
XML_INDEX_TAGS_INDEX
XML_DOC_STORAGE
XML_DOC_LOB_SIZE
XML_DOC_VAL_LOB_SIZE
XML_DOC_MODE (not present by default)
XML_DOC_UNCOMPRESSED_TYPE
XML_IDX_STORAGE
XML_IDX_INDEX_ID
XML_IDX_INDEX_TAG
XML_IDX_INDEX_DOUBLE
XML_IDX_INDEX_STRING
XML_IDX_FULLTEXT_UPD_FREQUENCY
XML_IDX_FULLTEXT_UPD_MINIMUM
XML_IDX_FULLTEXT_UPD_COMMIT
XML_IDX_FULLTEXT_IDXDIRECTORY
XML_IDX_FULLTEXT_WKDIRECTORY
XML_IDX_FULLTEXT_LANGUAGE
XML_IDX_FULLTEXT_CCSID
There are two XML parameters in the DATA_DICTIONARY keyword's parameter list, XML_INDEX_TAGS_TABLE and XML_INDEX_TAGS_INDEX. The XML_INDEX_TAGS_TABLE parameter specifies in which table space the SDE_XML_INDEX_TAGS system table and its indexes, xml_indextags_ix1 and xml_indextags_ix2, will be created. If you want to specify a separate table space for these objects, you must uncomment the XML_INDEX_TAGS_TABLE parameter and provide an existing table space name prior to creating your geodatabase. Similarly, the value for the XML_INDEX_TAGS_INDEX parameter, which specifies how the xml_indexes_pk index of the SDE_XML_INDEXES will be stored, must also be altered before you create the geodatabase.
The XML_DOC_STORAGE, XML_DOC_LOB_SIZE, and XML_DOC_VAL_LOB_SIZE parameters specify storage for the SDE_XML_DOC<n> table. XML_DOC_STORAGE defines the storage of the table itself, XML_DOC_LOB_SIZE specifies the size of the XML documents in the xml_doc column in the table, and XML_DOC_VAL_LOB_SIZE defines the size of the XML documents in the xml_doc_val column.
If you want to store XML documents in an uncompressed state, you need to add the XML_DOC_MODE parameter to the DBTUNE table and set it to UNCOMPRESSED. If you want your XML documents to be stored as uncompressed most of the time, add this parameter and value to the DEFAULTS keyword list. If you want to store only some XML documents in an uncompressed format, create a custom configuration keyword and add the XML_DOC_MODE parameter to it and set it to UNCOMPRESSED. In some cases, XML documents can be quite large even in their compressed state (4 or more MB); therefore, it is unlikely you will want to set the XML_DOC_MODE parameter to UNCOMPRESSED in the DEFAULT keyword's parameter list.
The XML_DOC_MODE parameter works in concert with the XML_DOC_UNCOMPRESSED_TYPE parameter. If you set XML_DOC_MODE to UNCOMPRESSED in any keyword's parameter list, you must also set the XML_DOC_UNCOMPRESSED_TYPE parameter in the same keyword. The XML_DOC_UNCOMPRESSED_TYPE parameter defines the storage format to be used for the uncompressed XML documents.
The XML_IDX_STORAGE specifies storage for the index table of an XML column, sde_xml_idx<n>.
The parameters XML_IDX_INDEX_ID, XML_IDX_INDEX_TAG, XML_IDX_INDEX_DOUBLE, and XML_IDX_INDEX_STRING define the storage for different indexes on columns in the sde_xml_idx<n> table. XML_IDX_INDEX_ID specifies storage for the xmlix<n>_id index on the ID column, XML_IDX_INDEX_TAG specifies storage for the xmlix<n>_tg index on the tag_id column, XML_IDX_INDEX_DOUBLE defines storage the xmlix<n>_db index on the double_tag column, and XML_IDX_INDEX_STRING defines the storage for the xmlix<n>_st index on the string_tag column.
The XML_IDX_FULLTEXT_UPD_FREQUENCY, XML_IDX_FULLTEXT_UPD_MINIMUM, and XML_IDX_FULLTEXT_UPD_COMMIT parameters define when the full text index is updated. XML_IDX_FULLTEXT_UPD_FREQUENCY specifies the number of days and hours between updates to the full text index. XML_IDX_FULLTEXT_UPD_MINIMUM indicates the minimum number of new or edited documents that can be added to the full text index before it is updated. XML_IDX_FULLTEXT_UPD_COMMIT is set to a number (the commit count) that defines when interim commits occur. For example, if you set the commit count to 1, each transaction is committed. If you set the commit count to 50, a commit is made after 50 transactions.
The XML_IDX_FULLTEXT_IDXDIRECTORY parameter indicates the path to directory that contains the text index. XML_IDX_FULLTEXT_WKDIRECTORY specifies the path to the working directory for the text index.
The XML_IDX_FULLTEXT_LANGUAGE parameter specifies the language to be used for linguistic analysis when building the text index on the contents of the xml_doc_val column in the sde_xml_doc<n> table and the text_tag column of the sde_xml_idx<n> table.
You should set the XML_IDX_FULLTEXT_CCSID parameter, which specifies the Coded Character Set Identifier (CCSID) of the documents, if your text documents are stored in binary format.
An additional note about indexing
By default, when XML documents are stored in an XML column in a geodatabase in DB2, the text indexes associated with the column will not be automatically updated to include text from the new documents. Until the text indexes have been updated, any XML documents that have not been indexed can't be found by a search.
ArcSDE does not provide any utilities that can be used to manually trigger updates to an XML column's text indexes. For XML columns associated with an ArcIMS Metadata Service, ArcIMS provides configuration options and a command-line utility can be used to update the text indexes.
DB2 has a known limitation for the Net Search Extender that affects how documents published to an ArcSDE XML column are indexed. Only a user with DB2ADM privileges can update the text indexes. The user who owns the XML column must either be granted DB2ADM privileges to support text index updates, or a user who does have the appropriate privileges must perform the updates.
One solution for this problem is to set the XML_IDX_FULLTEXT_UPD_FREQUENCY parameter in the SDE_dbtune table to set a schedule in the database for updating the text index. If, for instance, you want all documents that were added or updated during the day to be indexed each night, you should not specify a minimum number of documents with the XML_IDX_FULLTEXT_UPD_MINIMUM parameter.
For information about how to set the XML_IDX_FULLTEXT_UPD_FREQUENCY and XML_IDX_FULLTEXT_UPD_MINIMUM parameters in the SDE_dbtune table, see the documentation for DB2 Net Search Extender. You should set appropriate values for these parameters before creating an XML column.
See the ArcIMS Help for information about the options provided by ArcIMS for updating text indexes.
See Configuring a DB2 database to support XML columns for more information on using XML columns in your geodatabase.
• Log file table parameters
Log file tables are used by ArcSDE to maintain sets of selected records.
Log file parameters affect log file data tables and indexes. They begin with the letter L or SESSION. The parameters are as follows:
LD_INDEX_DATA_ID configures the creation of the LOGFILE_DATA_idx2 index on the SDE_LOGFILE_DATA table and of the LOGPOOL_<SDE_ID>_idx1 index on the SDE_LOGPOOL_<SDE_ID>.
LD_INDEX_ROWID configures creation of the index LOGFILE_DATA_idx1 on the SDE_LOGFILE_DATA table and the index LOGPOOL_<SDE_ID>_idx1 on the SDE_LOGPOOL_<SDE_ID> table.
LD_STORAGE* defines configuration for the SDE_LOGFILE_DATA and SDE_LOGPOOL_<SDE_ID> tables.
LF_STORAGE* defines the configuration for the SDE_LOGFILES table.
SESSION_STORAGE* defines the storage of the session-based and stand-alone log file tables.
SESSION_INDEX* defines the storage of the session-based and stand-alone log file table indexes.
SESSION_TEMP_TABLE only applies to geodatabases in DB2 on Linux, UNIX, and Windows and specifies if the tables should be created in a temporary table space.
*These parameters are commented out in the default dbtune.sde file; you must uncomment them and specify table names for their storage before users start connecting to the geodatabase.
For more information on how log file tables are used in the geodatabase, see Log file configuration options.
• User interface parameters
User interface parameters begin with UI and indicate whether or not their associated configuration keyword will be available through the ArcGIS user interface and ArcObjects.
UI_TEXT is used for noncomposite configuration keywords.
UI_TOPOLOGY_TEXT is used for topology keywords.
UI_TERRAIN_TEXT is used for terrain keywords.
UI_NETWORK_TEXT is used for network keywords.
See Making configuration keywords available in ArcGIS for more information on how to use UI parameters.
• Comments
You can add a COMMENT parameter in the dbtune.sde file if you like by adding a line beginning with a single pound sign (#). You might do this if you create your own custom keywords and want to add comments on how or when the keyword should be used.
For example, you could add a comment to a user's log file keyword:

#COMMENT		"This keyword is used by ArcSDE to create log file tables for all users logged in as editor"



See DBTUNE configuration keywords for information on adding custom keywords.
• BLOB parameters
DB2 requires a size on BLOB column creation.
If a BLOB column is to be created and it has a size greater than 2 GB, this size is ignored and the default LOB_SIZE parameter of 1 MB is used. This allows the database administrator (DBA) to carefully craft the database parameters.
For DB2 on Linux, UNIX, and Windows, the recommended setting for the BLOB_OPTION parameter is "LOGGED NOT COMPACT". For DB2 for z/OS, leave the BLOB_OPTION parameter blank.
• CLOB parameters
DB2 requires a size on CLOB column creation.
If a CLOB column is to be created and it has a size greater than 2 GB, the size is ignored and the default CLOB_SIZE parameter of 32 KB is used. This allows the DBA to carefully craft the database parameters.
The default and recommended setting for the CLOB_OPTION parameter for DB2 on Linux, UNIX, and Windows is "LOGGED NOT COMPACT". For DB2 for z/OS, leave the CLOB_OPTION parameter blank.
• MAX_CACHED_CURSORS parameter
Some control should be available over how many cursors per user can be allocated to the cache. While there are database tuning parameters related to the maximum number of cursors (SQL_MAX_CONCURRENT_ACTIVITIES for DB2), these are of limited use or often are not set and are effectively limited only by available resources and the complexity of the query executed. Simply applying the default maximum cursor value may cause issues on heavily loaded systems. To better control this, or to disable caching entirely, the DBTUNE parameter MAX_CACHED_CURSORS was added to the DEFAULTS keyword parameter list. The current default value is "80". To disable caching, set it to "0".
• B_RUNSTATS parameter
This parameter applies to the business table and is used at the end of a data load after all the records are inserted and the layer is being readied to put into normal_io mode. The last part of switching to normal_io mode is the checking of B_RUNSTATS. YES is the default if no B_RUNSTATS parameter is present in the DEFAULTS keyword of the DBTUNE table.
B_RUNSTATS only applies to the business table. If B_RUNSTATS is equal to YES or yes, a full runstats is performed on the table automatically. If it is set to anything else, a runstats does not happen. The vast majority of users want to have the full runstats done on the table. For those who want to do something special with it for some reason, such as only do indexes, they can set B_RUNSTATS to NO and perform a manual RUNSTATS command with any options they choose.
In geodatabases stored in DB2 for z/OS, set the B_RUNSTATS parameter to NO.