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:

• 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. Since ArcGIS does not use XML documents directly, it is unlikely you'll need to set these parameters.

Note:

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 function 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 run the Enable Enterprise Geodatabase tool. Uncomment, change values in this file, and save it before running the tool to create the geodatabase.

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

Db2 z/OS dbtune parameters to update before creating a geodatabase

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

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.

Configuration parameter summary

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

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 only applies to Db2 for Linux, UNIX, and Windows, and it 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 is only used for Db2 in Linux, UNIX, and Windows, and it 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:

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>

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.

Log file table parameters

Log file tables are used by ArcGIS to maintain sets of selected records. Log file parameters affect log file 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 session-based log file tables.
• SESSION_INDEX* defines the storage of indexes created on session-based log file tables.
• 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 not used by default; you must alter the configuration parameter to specify table names for their storage before users start connecting to the geodatabase if you want to use these.

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

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 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. For Db2 for z/OS, leave the BLOB_OPTION parameter blank.

• 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. For Db2 for z/OS, leave the CLOB_OPTION parameter blank.

  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.

  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 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 Db2 on z/OS, it does not currently apply to Db2 on this platform; it only applies to Db2 on Linux, UNIX, and Windows.