Db2 configuration parameters

Configuration parameters identify objects (such as tables, indexes, and columns) to be stored in the database. Their corresponding values identify how the object will be stored in the database. The parameters and their configuration strings are grouped by configuration keywords.

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

Legacy:

Configuration keywords and parameters are largely legacy functionality. They were implemented at a time when databases required far more intervention and tinkering on the part of the database administrator to get them to perform well. For the most part, you will not need to alter the configuration parameters in your geodatabase.

When you create an enterprise geodatabase, it is populated with default configuration keywords and parameters. In the majority of cases, the default parameter values are sufficient.

  • Establish the storage characteristics of tables and indexes. You need to understand how Db2 creates and stores tables and indexes to properly alter these settings. Therefore, consult the IBM documentation for the version of Db2 you are using before you alter these settings.
  • Make keywords available for users in the ArcGIS interface.
  • Provide comments that describe the configuration keyword.
  • Define how XML documents are stored. Because ArcGIS does not use XML documents directly, it is unlikely you'll need to set these parameters.

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

Values in bold are the default values.

Parameter nameDescriptionValueNotes

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.

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.

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 or NO

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 or LOGGED COMPACT or NOT LOGGED COMPACT or NOT LOGGED NOT COMPACT

Set to NOT LOGGED COMPACT if you store raster data in the geodatabase. 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 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 the Db2 documentation for LOB options for the CREATE TABLE statement.

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 1 GB. IBM recommends logged BLOB columns not be larger than 10 MB.

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 or LOGGED COMPACT or NOT LOGGED COMPACT or NOT LOGGED NOT COMPACT

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 1 GB. IBM recommends logged LOB columns not be larger than 10 MB.

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

Session-based log file index storage definition

See your Db2 documentation for CREATE INDEX parameters.

SESSION_STORAGE

Session-based log file table storage definition

See your Db2 documentation for CREATE TABLE parameters.

SESSION_TEMP_TABLE

Not used by geodatabases in Db2

0 or 1

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 the Db2 documentation for information on the INLINE LENGTH parameter and how it is used during table creation.

STATE_LINEAGES_TABLE

State_lineages table storage definition

See your Db2 documentation for CREATE TABLE parameters.

STATES_INDEX

States table index's 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 appears 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 appears 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 appears 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 appears 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 database 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.

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

XML_DOC_STORAGE

Storage clause for sde_xml_doc<n> table

See your Db2 documentation for CREATE TABLE parameters.

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.

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

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 the Db2 documentation for valid CCSID values.

XML_IDX_FULLTEXT_IDXDIRECTORY

Path to text index directory

Value depends on your directory

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 the Db2 documentation for valid language settings.

XML_IDX_FULLTEXT_UPD_FREQUENCY

Index update frequency

Number of days and hours between updates

There is no default value set for this parameter.

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.

XML_IDX_FULLTEXT_WKDIRECTORY

Path to text index working directory

Value depends on your directory

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.

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.

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.

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.

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.

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.

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.

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

The business table is the attribute table of a feature class or nonspatial table. 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.
  • The B_INDEX_ROWID parameter holds the storage configuration of the index ArcGIS creates on a registered table's ObjectID column, also referred to as the row ID.
  • The B_INDEX_SHAPE parameter holds the storage configuration of the spatial column index that ArcGIS creates when a spatial column is added to a business table. ArcGIS creates this index when it creates a feature class.
  • 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. ArcGIS creates two tables for each business table that you register to participate in a traditional version: 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 geodatabase system table. For instance, if the business table ROADS is listed with a registration ID of 10, ArcGIS creates the adds table as A10 and the deletes table as D10 when you register the ROADS table to participate in traditional versioning.

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 ArcGIS 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 ArcGIS 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 holds the storage configuration of the index that ArcGIS 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 geodatabase system table.

The A_INDEX_USER parameter holds the storage configuration of user-defined indexes that ArcGIS 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 ArcGIS creates on the deletes table. The D_INDEX_STATE_ROWID parameter holds the storage configuration of the D<n>_IDX1 index that ArcGIS 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 ArcGIS 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:

ParameterTable

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>

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

Log file table parameters

Log file tables are used by ArcGIS to maintain sets of selected records. Log file parameters are no longer used in geodatabases in Db2 to alter the type of log file tables used.

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_NETWORK_TEXT is used for network keywords. The default configuration keywords that need UI parameters already have them. You would only add one of these parameters if you created your own custom keywords.

XML parameters

Note:

ArcGIS clients do not work directly with XML columns. If you do not use XML columns and XML documents in your geodatabase in Db2, 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 the 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 causes 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, are 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.

Additional parameters

The following parameters do not fall into any particular category:

  • BLOB parameters

    Db2 requires a size on BLOB column creation. Set the BLOB_SIZE parameter to a size between 0 and 2 GB. IBM recommends logged BLOB columns not be larger than 10 MB.

    If you set the BLOB_OPTION parameter to LOGGED NOT COMPACT (the default value) or LOGGED COMPACT, BLOB columns will never be larger than 1 GB, no matter what you set for the BLOB_SIZE parameter.

    You can also use the BLOB_OPTION parameter to specify how much of the BLOB column should be stored in-line. In the following 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.

  • CLOB parameters

    Db2 requires a size on CLOB column creation. Use the CLOB_SIZE parameter to define the size of CLOB columns, between 0 and 2 GB. The default value is 32 KB. IBM recommends logged LOB columns not be larger than 10 MB.

    The default and recommended setting for the CLOB_OPTION parameter for Db2 on Linux, UNIX, and Windows is LOGGED NOT COMPACT.

    If CLOB_OPTION is set to LOGGED NOT COMPACT or LOGGED COMPACT, the CLOB column cannot be larger than 1 GB.

  • MAX_CACHED_CURSORS parameter

    The MAX_CACHED_CURSORS parameter allows you some control 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.

    Applying the default maximum cursor value may cause issues on heavily loaded systems. To better control this or to disable caching entirely, alter the setting for the MAX_CACHED_CURSORS configuration parameter in the DEFAULTS keyword parameter list. The current default value is 80. To disable caching, set it to 0.

  • B_RUNSTATS parameter

    This parameter updates statistics Db2 stores about the business table. After you load data, ArcGIS checks the B_RUNSTATS setting.

    B_RUNSTATS only applies to the business table. If B_RUNSTATS is equal to YES or yes, the Db2 runstats command is run on the table. YES is the default if no B_RUNSTATS parameter is present in the DEFAULTS configuration keyword. If B_RUNSTATS is present and set to anything other than YES or yes, the runstats command is not run.

    If you don't want ArcGIS to run this command, set B_RUNSTATS to NO and manually run the RUNSTATS command in the database, using any options you require.

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