DB2 DBTUNE configuration parameters

Configuration parameters, which are stored in the parameter_name column of the DBTUNE table, identify database objects to be stored in the database. Their corresponding values, which are stored in the config_string column of DBTUNE, identify how the object will be stored in the database. The parameters and their configuration strings are grouped in the DBTUNE table by configuration keywords. Keyword–parameter name combinations are unique, but most parameter names are not and are reused under a number of different keywords throughout the DBTUNE table.

Valid values for the parameter_name column are fixed; you cannot invent new parameter names. Likewise, the config_string column accepts only certain numeric values or SQL strings. In most cases, these strings are appended to SQL CREATE TABLE and CREATE INDEX statements, so they must match the SQL syntax necessary for the given DBMS.

In geodatabases stored in an IBM DB2 database, parameter name–configuration string pairs are used by ArcSDE to do the following:

If you want to create a geodatabase in DB2 on an IBM z operating system (z/OS), you must uncomment and alter parameters in the dbtune.sde configuration file before you create the geodatabase. If you do not uncomment and provide storage information for these parameters prior to creating the geodatabase, the geodatabase will not be functional because tables will be created in the wrong database in the subsystem. To alter these values prior to geodatabase creation, open the dbtune.sde file for DB2 z/OS that is installed in the DatabaseSupport folder of the ArcGIS client installation directory from which you will run the Enable Enterprise Geodatabase tool. Uncomment and change values in this file before running the tool to create the geodatabase.

The parameters that should be altered before creating the geodatabase, grouped by keyword, include the following:

Configuration keyword

Parameters

DATA_DICTIONARY

B_STORAGE

MVTABLES_MODIFIED_TABLE

STATE_LINEAGES_TABLE

STATES_TABLE

VERSIONS_TABLE

DEFAULTS

A_STORAGE

AUX_STORAGE

B_STORAGE

BLK_STORAGE

BND_STORAGE

D_STORAGE

MAX_CACHED_CURSORS

RAS_STORAGE

LOGFILE_DEFAULTS

LD_STORAGE

LF_STORAGE

SESSION_INDEX

SESSION_STORAGE

NETWORK_DEFAULTS

A_STORAGE

B_STORAGE

D_STORAGE

NETWORK_DEFAULTS::DESC

A_STORAGE

B_STORAGE

D_STORAGE

TOPOLOGY_DEFAULTS

A_STORAGE

B_STORAGE

D_STORAGE

TOPOLOGY_DEFAULTS::DIRTYAREAS

A_STORAGE

B_STORAGE

D_STORAGE

TERRAIN_DEFAULTS

A_STORAGE

B_STORAGE

D_STORAGE

TERRAIN_DEFAULTS::EMBEDDED

A_STORAGE

B_STORAGE

D_STORAGE

DB2 z/OS dbtune parameters to update before creating geodatabase

To change other parameter values after the DBTUNE table has been created, use the sdedbtune command with the alter operation. See Altering the contents of the DBTUNE table for instructions.

The following table is an alphabetic list of all the possible configuration parameters that can be used in a DB2 geodatabase. Following that is a more in-depth explanation of the parameters grouped by their functionality.

Values in bold are the default values.

Parameter name

Description

Value

Notes

A_INDEX_ROWID

Adds table object ID column index storage definition

See your DB2 documentation for CREATE INDEX parameters.

A_INDEX_SHAPE

Adds table spatial column index storage definition

See your DB2 documentation for CREATE INDEX parameters.

Does not apply to DB2 for z/OS

A_INDEX_STATEID

Adds table sde_state_id column index storage definition

See your DB2 documentation for CREATE INDEX parameters.

A_INDEX_USER

Adds table index storage definition

See your DB2 documentation for CREATE INDEX parameters.

A_STORAGE

Adds table storage definition

See your DB2 documentation for CREATE TABLE parameters.

AUX_INDEX_COMPOSITE

Raster AUX table composite column index storage definition

See your DB2 documentation for CREATE INDEX parameters.

AUX_STORAGE

Raster AUX table storage definition

See your DB2 documentation for CREATE TABLE parameters.

B_INDEX_ROWID

Business table object ID column index and raster row ID index R<N>_SDE_ROWID_UK storage definition

See your DB2 documentation for CREATE INDEX parameters.

B_INDEX_SHAPE

Business table spatial column index storage definition

See your DB2 documentation for CREATE INDEX parameters.

Does not apply to DB2 for z/OS

B_INDEX_TO_DATE

Storage parameter info for creating the index R<registration_id>_sde_todate, which is used when updating the history table during an archive operation

See your DB2 documentation for CREATE INDEX parameters.

B_INDEX_USER

Business table user index storage definition

See your DB2 documentation for CREATE INDEX parameters.

B_RUNSTATS

Default value for RUNSTATS

YES (default for DB2 on Linux, UNIX, and Windows) or NO (default for DB2 z/OS)

B_STORAGE

Business table and raster attribute table storage definition

See your DB2 documentation for CREATE TABLE parameters.

BLK_BLOB_OPTION

Storage configuration properties of BLOB columns in the raster BLK table

LOGGED NOT COMPACT (default for DB2 on Linux, UNIX, and Windows) or LOGGED COMPACT or NOT LOGGED COMPACT or NOT LOGGED NOT COMPACT

The default value for this parameter in DB2 for z/OS is blank, and it should remain blank. For DB2 for Linux, UNIX, or Windows, set to NOT LOGGED COMPACT. Setting this parameter to any other value can take up a lot more table space when loading raster data.

BLK_INDEX_COMPOSITE

Raster BLK table composite column index storage definition

See your DB2 documentation for CREATE INDEX parameters.

BLK_STORAGE

Raster BLK table storage definition

See your DB2 documentation for CREATE TABLE parameters.

BLOB_OPTION

Storage configuration properties of the BLOB column

LOGGED NOT COMPACT (default for DB2 on Linux, UNIX, and Windows) or LOGGED COMPACT or NOT LOGGED COMPACT or NOT LOGGED NOT COMPACT

You can also specify an INLINE LENGTH parameter with the BLOB_OPTION.

See your DB2 documentation for BLOB column storage parameters.

The default value for this parameter in DB2 for z/OS is blank, and it should remain blank.

BLOB_SIZE

Size of BLOB column

> 0 and < 2GB

1MB is the default value.

If BLOB_OPTION is set to LOGGED, BLOB size cannot be larger than 1G. (DB2 recommends logged BLOB columns not be larger than 10M.)

BND_INDEX_COMPOSITE

Raster BND table composite column index storage definition

See your DB2 documentation for CREATE INDEX parameters.

BND_INDEX_ID

Raster BND table RID column index storage definition

See your DB2 documentation for CREATE INDEX parameters.

BND_STORAGE

Raster BND table storage definition

See your DB2 documentation for CREATE TABLE parameters.

CLOB_OPTION

Storage configuration properties of the CLOB column

LOGGED NOT COMPACT (default for DB2 on Linux, UNIX, and Windows) or LOGGED COMPACT or NOT LOGGED COMPACT or NOT LOGGED NOT COMPACT

The default value for this parameter in DB2 for z/OS is blank, and it should remain blank.

CLOB_SIZE

Size of CLOB column

> 0 and < 2GB

The default value is 32KB.

If CLOB_OPTION is set to LOGGED, CLOB column cannot be larger than 1G. (DB2 recommends logged LOB columns not be larger than 10M.)

COMMENT

Line used for comments

Can place any comment up to 2,048 characters

D_INDEX_DELETED_AT

Deletes table sde_deleted_at column index storage definition

See your DB2 documentation for CREATE INDEX parameters.

D_INDEX_ STATE_ROWID

Deletes table sde_states_id and sde_deletes_row_id column index storage definition

See your DB2 documentation for CREATE INDEX parameters.

D_STORAGE

Deletes table storage definition

See your DB2 documentation for CREATE TABLE parameters.

LD_INDEX_DATA_ID

SDE_logfile_data storage definition

See your DB2 documentation for CREATE INDEX parameters.

LD_INDEX_ROWID

SDE_logfile_data table SDE_ROWID column index storage definition

See your DB2 documentation for CREATE INDEX parameters.

LD_STORAGE

SDE_logfile_data table storage definition

See your DB2 documentation for CREATE TABLE parameters.

LF_INDEXES

SDE_logfiles table column indexes storage definition

See your DB2 documentation for CREATE INDEX parameters.

LF_STORAGE

SDE_logfiles table storage definition

See your DB2 documentation for CREATE TABLE parameters.

MAX_CACHED_CURSORS

Maximum number of cached cursors

0 or higher

0 = disabled

The default value is 80.

MVTABLES_MODIFIED_INDEX

Mvtables_modified index storage definition

See your DB2 documentation for CREATE INDEX parameters.

MVTABLES_MODIFIED_TABLE

Mvtables_modified table storage definition

See your DB2 documentation for CREATE TABLE parameters.

RAS_INDEX_ID

Raster RAS table RID index storage definition

See your DB2 documentation for CREATE INDEX parameters.

RAS_STORAGE

Raster RAS table storage definition

See your DB2 documentation for CREATE TABLE parameters.

SESSION_INDEX

ArcSDE session-based and stand-alone log file indexes storage definition

See your DB2 documentation for CREATE INDEX parameters.

SESSION_STORAGE

ArcSDE session-based and stand-alone log file tables storage definition

See your DB2 documentation for CREATE TABLE parameters.

SESSION_TEMP_TABLE

Not used by geodatabases in DB2

1 or 0

ST_GEOM_STORAGE_INLINE

Defines whether the content of the ST_Geometry column is stored inline or out of line

INLINE LENGTH <size in bytes>

See your DB2 documentation for information on the INLINE LENGTH parameter and how it is used during table creation.

DB2 9.x on z/OS does not use this parameter. Therefore, this parameter should be left blank in the DBTUNE table for geodatabases in DB2 on z/OS.

Does not apply to DB2 9.x for z/OS

STATE_LINEAGES_TABLE

State_lineages table storage definition

See your DB2 documentation for CREATE TABLE parameters.

STATES_INDEX

States table storage definition

See your DB2 documentation for CREATE INDEX parameters.

STATES_TABLE

States table storage definition

See your DB2 documentation for CREATE TABLE parameters.

UI_NETWORK_TEXT

User interface parameter, which indicates associated configuration keyword will appear in the ArcGIS user interface; contains description of network configuration

Description up to 2,048 characters

UI_TERRAIN_TEXT

User interface parameter, which indicates associated configuration keyword will appear in the ArcGIS user interface; contains description of terrain configuration

Description up to 2,048 characters

UI_TEXT

User interface parameter, which indicates associated configuration keyword will appear in the ArcGIS user interface; contains description of associated noncomposite configuration keyword

Description up to 2,048 characters

UI_TOPOLOGY_TEXT

User interface parameter, which indicates associated configuration keyword will appear in the ArcGIS user interface; contains description of topology configuration

Description up to 2,048 characters

VERSIONS_INDEX

Versions table index storage definition

See your DB2 documentation for CREATE INDEX parameters.

VERSIONS_TABLE

Versions table storage definition

See your DB2 documentation for CREATE TABLE parameters.

XML_COLUMN_STORAGE

Specifies the type of XML columns to create: either ArcSDE XML or native DBMS XML

DB_XML or SDE_XML

Under the DEFAULTS keyword, the default value is DB_XML. Under the IMS_GAZETTEER keyword, the default value is SDE_XML.

Does not apply to DB2 for z/OS

XML_DOC_LOB_SIZE

Defines the size of the XML documents in the xml_doc column of the sde_xml_doc<n> table

1 M

Does not apply to DB2 for z/OS

XML_DOC_STORAGE

Storage clause for sde_xml_doc<n> table

See your DB2 documentation for CREATE TABLE parameters.

Does not apply to DB2 for z/OS

XML_DOC_UNCOMPRESSED_TYPE

When the XML_DOC_MODE parameter is set to UNCOMPRESSED, the XML_DOC_UNCOMPRESSED_TYPE parameter determines the storage format for XML documents.

Since XML_DOC_MODE is set to COMPRESSED by default, the XML_DOC_UNCOMPRESSED_TYPE parameter is not present by default. If you add the XML_DOC_UNCOMPRESSED parameter, possible values are BLOB, CLOB, or NCLOB.

Does not apply to DB2 for z/OS

XML_DOC_VAL_LOB_SIZE

Defines size of the XML document in the xml_doc_val column of the sde_xml_doc<n> table

1 M

Does not apply to DB2 for z/OS

XML_IDX_FULLTEXT_CCSID

Text index Coded Character Set Identifier (CCSID); must specify the CCSID of the documents if the text documents are stored as binary

Consult your DB2 documentation for valid CCSID values.

Does not apply to DB2 for z/OS

XML_IDX_FULLTEXT_IDXDIRECTORY

Path to text index directory

Value depends on your directory

Does not apply to DB2 for z/OS

XML_IDX_FULLTEXT_LANGUAGE

The language 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

There is no default value set for this parameter. Consult your DB2 documentation for valid language settings.

Does not apply to DB2 for z/OS

XML_IDX_FULLTEXT_UPD_FREQUENCY

Index update frequency

Number of days and hours between updates

There is no default value set for this parameter.

Does not apply to DB2 for z/OS

XML_IDX_FULLTEXT_UPD_MINIMUM

Minimum number of new or edited documents before full text index is updated

There is no default value set for this parameter.

If the value is left blank, the index is updated for all additions/edits made during the update frequency.

Does not apply to DB2 for z/OS

XML_IDX_FULLTEXT_WKDIRECTORY

Path to text index working directory

Value depends on your directory

Does not apply to DB2 for z/OS

XML_IDX_INDEX_DOUBLE

Storage clause for the xmlix<n>_db index on the double_tag column of the sde_xml_idx<n> table

See your DB2 documentation for CREATE INDEX parameters.

Does not apply to DB2 for z/OS

XML_IDX_INDEX_ID

Storage clause for the xmlix<n>_id index on the ID column of the xml_idx<n> table

See your DB2 documentation for CREATE INDEX parameters.

Does not apply to DB2 for z/OS

XML_IDX_INDEX_STRING

Storage clause for xmlix<n>_st index on the string_tag column of the sde_xml_idx<n> table

See your DB2 documentation for CREATE INDEX parameters.

Does not apply to DB2 for z/OS

XML_IDX_INDEX_TAG

Storage clause for the xmlix<n>_tg index on the tag_id column of the sde_xml_idx<n> table

See your DB2 documentation for CREATE INDEX parameters.

Does not apply to DB2 for z/OS

XML_IDX_STORAGE

Storage clause for sde_xml_idx<n> table (the index table of an XML column)

See your DB2 documentation for CREATE INDEX parameters.

Does not apply to DB2 for z/OS

XML_INDEX_TAGS_INDEX

Storage clause for xml_indextags_pk index of the sde_xml_indexes table

See your DB2 documentation for CREATE INDEX parameters.

Does not apply to DB2 for z/OS

XML_INDEX_TAGS_TABLE

Storage clause for sde_xml_index_tags table and the xml_indextags_ix1 and xml_indextags_ix2 indexes on the tag_name and tag_alias columns, respectively

See your DB2 documentation for CREATE TABLE parameters.

Does not apply to DB2 for z/OS

XMLDB_DOC_COMPRESSED

Specifies whether XML documents are stored in a compressed form

YES or NO

XMLDB_DOC_STORAGE_INLINE

Specifies the type of XML storage used for a PureXML column

XML storage object or INLINE LENGTH <# in bytes>

XMLDB_DOC_STORAGE_TABLESPACE

Specifies the table space in which the XML document will be stored

LONG IN <table space>

XMLDB_IDX_TAG_INVALID_VALUES

Specifies whether an invalid XML tag should be rejected

IGNORE INVALID VALUES or REJECT INVALID VALUES

XMLDB_INDEX_FULLTEXT

Specifies whether to create Text Search indexes on XML columns when the XML column is created

YES or NO

XMLDB_INDEX_TAG

Specifies whether XML indexes should be created on XML columns when the column is created

YES or NO

Configuration parameter summary
NoteNote:

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 function SE_table_create. Use the B_STORAGE parameter to define the storage configuration of a business table.

There are four index storage parameters used for 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 row ID.
  • 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 for each business table that is registered as versioned: the adds table and deletes table.

The adds table is named A<n> and the deletes table is 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 adds table as A10 and the deletes table as D10.

TipTip:

For more information on the structure of adds and deletes tables and how they are used, see Versioned tables in a geodatabase in DB2.

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 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 row ID. The adds table row ID 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 D_INDEX_STATE_ROWID parameter holds the storage configuration of the D<n>_IDX1 index that ArcSDE creates on the SDE_STATE_ID and SDE_DELETES_ROW_ID columns in the deletes table. The D_INDEX_DELETED_AT parameter holds the storage configuration of the D<n>_IDX2 index that ArcSDE creates on the SDE_DELETED_AT column of the deletes table.

Raster table and index storage parameters

There is one table storage parameter for each of the raster tables. Each of these holds the DB2 CREATE TABLE storage configuration of its associated raster table. The parameters and tables are as follows:

Parameter

Table

RAS_STORAGE

SDE_RAS_<raster_column_ID>

BND_STORAGE

SDE_BND_<raster_column_ID>

AUX_STORAGE

SDE_AUX_<raster_column_ID>

BLK_STORAGE

SDE_BLK_<raster_column_ID>

For a description of these tables, see Raster datasets and raster catalogs in a geodatabase in DB2.

Each of these tables also has indexes. The parameters to control the storage of these indexes are as follows:

  • The RAS_INDEX_ID storage parameter holds the DB2 CREATE INDEX storage configuration of the RAS table index.
  • The BND_INDEX_COMPOSITE storage parameter holds the DB2 CREATE INDEX storage configuration of the composite column index on the BND table.
  • The BND_INDEX_ID storage parameter holds the DB2 CREATE INDEX storage configuration of the ID column index of the BND table.
  • The AUX_INDEX_COMPOSITE storage parameter holds the DB2 CREATE INDEX storage configuration of the AUX table's index.
  • 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. It is 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.

To learn more about raster attribute tables, see Raster dataset attribute tables. To learn about custom configuration keywords, see Custom configuration keywords. For more information on how rasters are stored, see Raster datasets and raster catalogs in a geodatabase in DB2.

XML parameters

NoteNote:

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 do not need to configure these parameters.

DB2 XML parameters include the following:

  • XML_COLUMN_STORAGE
  • XMLDB_DOC_COMPRESSED
  • XMLDB_DOC_STORAGE_INLINE
  • XMLDB_DOC_STORAGE_TABLESPACE
  • XMLDB_IDX_TAG_INVALID_VALUES
  • XMLDB_INDEX_FULLTEXT
  • XMLDB_INDEX_TAG
  • 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 in DBTUNE 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_IDXDIRECTORY
  • XML_IDX_FULLTEXT_WKDIRECTORY
  • XML_IDX_FULLTEXT_LANGUAGE
  • XML_IDX_FULLTEXT_CCSID

The XML_COLUMN_STORAGE parameter determines whether XML columns are created as ArcSDE XML (SDE_XML) or native DB2 PureXML (DB_XML). The default behavior is to use DB2 PureXML.

The next six XML parameters (XMLDB_DOC_COMPRESSED, XMLDB_DOC_STORAGE_INLINE, XMLDB_DOC_STORAGE_TABLESPACE, XMLDB_IDX_TAG_INVALID_VALUES, XMLDB_INDEX_FULLTEXT, and XMLDB_INDEX_TAG) apply to DB2 PureXML columns. If you use the default XML_COLUMN_STORAGE of DB_XML, these are the parameters you use to control XML documentation storage.

XMLDB_DOC_COMPRESSED indicates whether XML documents that are stored in-line will be stored in a compressed form. The default behavior is to store the XML documents uncompressed. Storing the documents in compressed form allows you to fit more records per page. However, if the data is compressed, it must be uncompressed every time it is queried. Therefore, in most cases, uncompressed XML documents are preferred.

XMLDB_DOC_STORAGE_INLINE specifies whether the XML document is stored as a separate XML storage object (the default) or stored in-line in the page. If stored in the page, INLINE LENGTH <# in bytes> defines the size (in bytes) used for this storage. The maximum limit is just under the page size. For example, if you use the default page size of 4 KB, the maximum size of the in-line data is just under 4 KB. See your DB2 documentation for information on calculating the maximum size of in-line XML documents.

For small XML documents (less than the page size set for your database), in-line storage can provide better performance because there is less input/output needed for inserting, updating, and deleting contents. The XMLDB_DOC_STORAGE_TABLESPACE parameter allows you to specify a different table space in which to store the XML storage object. By default, it is stored in the same table space as the business table. You can specify a different table space with this parameter. This parameter cannot be used if you are using in-line storage.

The XMLDB_IDX_TAG_INVALID_VALUES essentially enables or disables validation of XML tags. The default value, IGNORE INVALID VALUES, does not enforce XML tag validity. Setting this parameter to REJECT INVALID VALUES will cause the tag value to be rejected for indexing, and DB2 will return an error if the tag is incorrect.

The XMLDB_INDEX_FULLTEXT and XMLDB_INDEX_TAG parameters allow you to set the creation of indexes to happen automatically when the XML column is created. With the default value for XMLDB_INDEX_FULLTEXT, a full-text index is not created on the XML column when the column is created. The default value for XMLDB_INDEX_TAG creates XML indexes on the XML column when it is created.

The last set of XML parameters only applies to ArcSDE XML columns.

XML_INDEX_TAGS_TABLE and XML_INDEX_TAGS_INDEX are found in the DATA_DICTIONARY keyword's parameter list. 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 before creating your geodatabase. Similarly, the value for the XML_INDEX_TAGS_INDEX parameter, which specifies how the xml_indexes_pk index of SDE_XML_INDEXES is 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 for 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 and XML_IDX_FULLTEXT_UPD_MINIMUM 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.

The XML_IDX_FULLTEXT_IDXDIRECTORY parameter indicates the path to the 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 CCSID of the documents, if your text documents are stored in binary format.

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> table.
  • LD_INDEX_ROWID configures creation of the LOGFILE_DATA_idx1 index on the SDE_LOGFILE_DATA table and the LOGPOOL_<SDE_ID>_idx1 index 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 whether 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 XML columns in a geodatabase in DB2.

User interface parameters

User interface parameters begin with UI and indicate whether 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.

Additional parameters

The following parameters do not fall into any particular category:

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

    Additionally, you could use the BLOB_OPTION parameter to specify how much of the BLOB column should be stored in-line. In the following simple example, a table is created with a BLOB column specifying an in-line length of 200 (536,000,000 bytes); the rest is stored out of line:

    CREATE TABLE btab (col1 BLOB INLINE LENGTH 220)
    

    For DB2 on Linux, UNIX, and Windows, the default setting for the BLOB_OPTION parameter is LOGGED NOT COMPACT. You could instead specify LOGGED COMPACT, NOT LOGGED COMPACT, or NOT 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.

  • ST_GEOM_STORAGE_INLINE

    This parameter defines whether the data in the spatial column of a feature class will be stored in-line as a VARCHAR FOR BIT DATA type or out of line as a LOB.

    If the size of the spatial column's binary content is less than the value (in bytes) specified with the ST_GEOM_STORAGE_INLINE parameter during feature class creation, the content is stored in-line as a VARCHAR FOR BIT DATA type. It is read in-line with the business table's row fetch and read into the database process by the database I/O operations in asynchronous and fully buffered mode.

    In the following example, the dbtune file is edited to set the length to 3,000 bytes. This means any content less than 3,000 bytes will be stored in-line:

    ST_GEOM_STORAGE_INLINE "INLINE LENGTH 3000"

    If the size of the spatial column's binary content is greater than the value specified with the ST_GEOM_STORAGE_INLINE parameter during feature class creation, the binary content is stored as a LOB out of line. The column is read out of line with the business table's row fetch and read into the database process by the database I/O operations in synchronous and nonbuffered mode.

    The value of this parameter is blank in the DBTUNE table by default. Therefore, the default DB2 value for in-line LOB storage of 659 bytes is used. To store more bytes in-line, alter the value of the ST_GEOM_STORAGE_INLINE parameter in the DBTUNE table.

    Although the parameter is also present in the DBTUNE table in DB2 on z/OS, it does not currently apply to DB2 on this platform; it only applies to DB2 on Linux, UNIX, and Windows.

11/6/2014