Configuration settings to use ArcSDE with an ArcIMS Metadata Service

NOTE: ArcSDE Enterprise only

After you enable full text indexing, there are other configuration settings for using an ArcSDE geodatabase with an ArcIMS Metadata Service. The first section below applies to the three databases that support XML columns and ArcIMS Metadata Services; the other sections contain DBMS-specific information.

To help you navigate this topic, follow the links below to go to the sections in which you are interested:

General settings

Metadata Service user account

You should treat the ArcIMS Metadata Service as a unique user of the ArcSDE database. As such, create a specific account in the ArcSDE database that will be used for all connection and modification of the database specific to the ArcIMS Metadata Service. The user name and password for this account should be specified in the <SDEWORKSPACE/> element within the MetadataServer.axl file.

The account should have CREATE permissions for database objects since the ArcIMS Metadata Service creates one ArcSDE layer and nine ArcSDE tables.

MAXBLOBSIZE SDE_CONFIG parameter

XML documents that contain metadata describing GIS resources may contain an image or a reference to an image that illustrates the contents of the resource. When these XML documents are published to an ArcIMS Metadata Service, a copy of the image is stored in a BLOB column in the feature class associated with the service.

For metadata XML documents created using ArcCatalog, a small thumbnail image may be embedded within the XML document. There should be no trouble publishing XML documents containing these thumbnail images to a Metadata Service with the default MAXBLOBSIZE setting for ArcSDE.

When a metadata XML document is published to a Metadata Service using ArcCatalog and a thumbnail is not embedded within the document, the document's content is checked to see if there is a reference to an external image file. Specifically, the FGDC metadata element Browse Graphic File Name is checked. If a file path or HTTP address is found that references a JPEG image and ArcCatalog can access the image, the file will be loaded into the Metadata Service as the document's thumbnail. ArcCatalog will only attempt to access the first JPEG image referenced in the metadata.

External image files referenced by a metadata document may be large. If the file is larger than approximately one megabyte in size, publishing the document to the Metadata Service may fail. You must adjust the default value of the MAXBLOBSIZE parameter in ArcSDE if you want to support publishing larger images as thumbnails.

Updating database statistics for a Metadata Service's table

As people publish documents to, update documents in, and delete documents from a Metadata Service, the statistics in the database for the service's objects will become stale over time. When this happens, performance may decline for searches that use criteria that are evaluated against data in the Metadata Service's tables rather than the XML column's indexes. These criteria include the UPDATED, AREA, DOCUMENTINFO, and SUBSET ArcXML requests.

To keep your Metadata Service operating at peak performance, you should periodically update the statistics for the business table associated with the Metadata Service's feature class and the relationships table. You should do this whenever approximately 20 percent of the records in the Metadata Service have changed as a result of adding, updating, or deleting documents.

Update statistics using the ArcSDE sdetable command from the operating system's command line. For DB2 and Oracle databases, you would execute this command as follows:

sdetable -o update_dbms_stats -t <table_name> -i <service> -s <server_name> -D <database> -u <DB_User_name> -p <DB_User_password>

NOTE: Database statistics for SQL Server are set to update automatically, so you don't need to do this for that DBMS.

Run the above command twice, once for the feature class and once for the relationships table. For the feature class, set the <table_name> argument to the table name prefix defined in the Metadata Service's ArcXML configuration file, such as imsmetadata. For the relationships table, set the <table_name> argument to <prefix>R, such as imsmetadataR if the table name prefix is imsmetadata. Modify the remaining arguments to provide the appropriate ArcSDE connection information.

Top of page

SQL Server configuration

Setting ArcSDE XML SDE_dbtune parameters

The XML parameters set in the SDE_dbtune table affect how documents published to an ArcSDE XML column are indexed by the database. Information about the following XML parameters should be reviewed before creating an XML column: XML_IDX_FULLTEXT_CAT and XML_IDX_FULLTEXT_UPDATE_METHOD.

If your XML column will store documents written in a language other than English, you will also want to review information about the XML_IDX_FULLTEXT_LANGUAGE parameter in addition to verifying the language settings in the database. If you need to change the default values for these SDE_dbtune parameters and your XML column is associated with an ArcIMS Metadata Service, the changes need to be made for the DEFAULTS keyword. See the 'XML_IDX_FULLTEXT_LANGUAGE' section below.

Learn more about the DEFAULTS keyword

Learn more about language support in the geodatabase

XML_IDX_FULLTEXT_CAT

The value of the XML_IDX_FULLTEXT_CAT parameter must match the name of the full text catalog that you created in the database. If the full text catalog's name is different, you must update the SDE_dbtune value to match it. Information for the multiple and single spatial database models follows:

XML_IDX_FULLTEXT_UPDATE_METHOD

The frequency and method with which the full text catalog will be updated in the database needs to be considered based on the amount and rate of change of the documents published to the XML column, the hardware resources available to the database, and whether or not it is important for a newly published document to be immediately available for a search.

Documents can only be found by a search once they have been indexed by the database; that is, once the full text catalog has been updated. The XML_IDX_FULLTEXT_UPDATE_METHOD parameter in the SDE_dbtune table determines how and when the full text catalog will be updated.

By default, the XML_IDX_FULLTEXT_UPDATE_METHOD parameter is set to "CHANGE_TRACKING BACKGROUND". Change tracking means that when the full text catalog is updated, only documents added since the last update will be indexed. ESRI recommends that you always use change tracking with your full text catalog. Background instructs the database to manage when the full text catalog is updated using a background process. If this parameter is changed to "CHANGE_TRACKING MANUAL", the full text catalog must be updated manually.

Typically, "CHANGE_TRACKING BACKGROUND" will provide better overall performance for your database. Background updates will be affected by the resources available on the database server, the priority of the background process, and the load of other operations on the database. Manual updates will have a higher priority in the database and, thus, will probably be faster but they will affect the performance of searches and other operations in the database. Updates to the full text catalog should be scheduled to take place when database traffic is low, for example, nightly. You may want to use the SQL Server database's tools to schedule updates of the full text catalog.

XML_IDX_FULLTEXT_LANGUAGE

If your Metadata Service will store documents written in a language other than English, you may need to set the XML_IDX_FULLTEXT_LANGUAGE parameter in the SDE_dbtune table. The XML_IDX_FULLTEXT_LANGUAGE parameter is used to set the language of the column that is indexed in the full text catalog. This setting affects the linguistic analysis used to index the text in the published documents—if the published documents are written in Japanese but the language of the column is set to English, the text won't be indexed properly and you will have trouble finding documents with a search.

If you are using a localized version of SQL Server, the default full text language option will be set to the language of the server. For example, if you are using a Japanese version of SQL Server, the language of the database, the full text catalog, and the column to be indexed should all be set to Japanese because the default full text language option will be set to Japanese. You should not have to change the value of the XML_IDX_FULLTEXT_LANGUAGE parameter in the SDE_dbtune table.

If you are using an international version of SQL Server, the default full text language option will be set to U.S. English. Even if you specifically set the language of the database and the full text catalog to Japanese, the language of the column that will be indexed will default to U.S. English. Set the XML_IDX_FULLTEXT_LANGUAGE parameter in the SDE_dbtune table to the appropriate value for the language of the published documents; this value is used to set the language of the column. See the SQL Server Books Online help topic 'sp_fulltext_column' for the appropriate value; for example, for Japanese you would set the value of the XML_IDX_FULLTEXT_LANGUAGE parameter in the SDE_dbtune table to be "0x0411".

When a column is set to a specific language, such as Japanese, you will be able to publish and search documents in both Japanese and English. If the XML column will contain documents in many languages or in an unsupported language, the language of the column to be indexed should be set to neutral; however, you may have trouble searching with some text.

See the SQL Server Books Online help topics for more information.

Top of page

Oracle configuration

Configuring LOB storage

XML documents are stored in BLOB format in the XML document table in the xml_doc and xml_doc_val columns. It is important to configure these columns accurately to achieve the best possible search performance.

Using in-line or out-of-line storage

LOBs are stored inline if the LOB data is stored in the same block as the rest of the data in the row. However, in-line storage is only possible if the LOB data is less than 4KB in size. With out-of-line storage, the data is stored in the LOB segment and only the LOB locator is stored with the rest of the data in the row.

By default, Oracle stores LOB data inline. You can specify whether LOB data associated with an XML column is stored inline or out of line using the ArcSDE DBTUNE parameters XML_DOC_LOB_STORAGE and XML_DOC_VAL_LOB_STORAGE.

When LOB data is stored out of line for an XML column, by default, ArcSDE places that data in the same tablespace as the XML document table. The LOB data may be moved to a different tablespace than the one containing the XML document table by using the XML DBTUNE parameters XML_DOC_STORAGE and XML_DOC_VAL_STORAGE.

A typical XML document that contains metadata describing a GIS resource will be greater than 4KB in size. Tests show XML columns associated with ArcIMS Metadata Services perform best when the LOB data is stored out of line in a separate tablespace from the XML document table. Specify out-of-line storage for the XML column by setting the XML_DOC_LOB_STORAGE and XML_DOC_VAL_LOB_STORAGE parameters to "DISABLE STORAGE IN ROW". After configuring another tablespace to store the LOB data, specify that tablespace's name in the XML_DOC_STORAGE and XML_DOC_VAL_STORAGE parameters. For XML columns associated with ArcIMS Metadata Services, set these parameters with the DEFAULTS DBTUNE keyword.

A Metadata Service may contain gazetteer data instead of typical metadata XML documents. Gazetteer data is very small, typically less than 100 bytes in size. Metadata Services containing gazetteer data will perform best when the LOB data is stored in-line. Default gazetteer data is provided with ArcIMS in sdeexport format and is loaded into ArcSDE using a batch script that calls the sdeimport command. This script references the DBTUNE keyword IMS_GAZETTEER, which specifies in-line storage by setting the XML_DOC_LOB_STORAGE and XML_DOC_VAL_LOB_STORAGE parameters to "ENABLE STORAGE IN ROW".

For more information about in-line and out-of-line storage, see Oracle's LOB performance guidelines.

Set the optimizer parameters for Oracle9i

For Oracle9i, a few optimizer parameters should be set in the database for searches to perform well. Setting these parameters will not adversely affect other client software that accesses your ArcSDE database. You do not need to set these parameters for Oracle 10g.

For Oracle9i, the following parameter must be added to your Oracle server's init.ora file:

OPTIMIZER_MODE=CHOOSE

Only the optimizer mode must be set; the other parameters used with Oracle8i aren't required.

NOTE: At ArcSDE 9.2, Oracle 8i is not supported. However, if you are using ArcSDE 9.1 or lower with Oracle 8i, the following parameters must be added to your Oracle server's init.ora file:

OPTIMIZER_INDEX_CACHING = 90
OPTIMIZER_INDEX_COST_ADJ = 10
OPTIMIZER_MODE=CHOOSE

Refer to the Oracle8i documentation for a complete discussion of these parameters. In general, setting these parameters as specified directs the Oracle8i optimizer to favor index scans and nested loop execution plans over a full table scan or a sort merge.

Top of page

DB2 configuration

DB2 has a known limitation for the Net Search Extender that affects how documents published to an ArcSDE XML column are indexed. Currently, only a user with DB2ADM privileges can index documents. This can present problems, particularly for XML columns that are associated with ArcIMS Metadata Services.

A Metadata Service's configuration file has an index_words attribute on the METADATA_CONTENT element. When set to automatic—the default—a command is sent to the database to update the text index immediately after a document is published. When set to manual, the text index is updated when the ArcIMS aimsmetaindx command is run from the command line.

The user who owns a Metadata Service's objects in the database generally won't have DB2ADM privileges. Because of this known limitation, the automatic setting in a Metadata Service's configuration file won't work. Also, with the manual setting, only a user with DB2ADM privileges will be able to run the aimsmetaindx command to update the text index. The arguments for the aimsmetaindx command are:

aimsmetaindx <sde_server_machine> <sde_instance> <database> <username> <password> {table_name_prefix} {logfile} {localcodepage}

If the DB2ADM user runs this command to index published documents, the table_name_prefix argument must be specified and it must be prefaced with the user who owns the Metadata Service's objects. For example:

aimsmetaindx sdeServer 5151 sde sdeUser sdePassword aUser.imsmetadata

One solution for this problem is for the DB2ADM user to schedule the aimsmetindx command to run periodically, for example using a cron job. Another solution is to set the XML_IDX_FULLTEXT_UPD_FREQUENCY parameter in the SDE_dbtune table to set a schedule in the database for updating the text index. If, for instance, you want all documents published or updated during the day to be indexed each night, you should not specify a minimum number of documents with the XML_IDX_FULLTEXT_UPD_MINIMUM parameter.

For information about how to set the XML_IDX_FULLTEXT_UPD_FREQUENCY and XML_IDX_FULLTEXT_UPD_MINIMUM parameters in the SDE_dbtune table, see the documentation for DB2 Net Search Extender. You should review these parameters before creating a Metadata Service.

For more information about the index_words attribute in the configuration file and the aimsmetaindx command, see the Metadata section of the ArcIMS help, provided with ArcIMS software.

Top of page