Parameters for ArcSDE XML in SQL Server

The XML parameters set in the SDE_dbtune table affect how documents published to an ArcSDE XML column are stored and indexed by the database. You should review information on XML parameters before creating an XML column.

The XML_IDX_FULLTEXT_CAT and XML_IDX_FULLTEXT_UPDATE_METHOD parameters affect the SQL Server full-text catalog.

If your XML column will store documents written in a language other than English, you should 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 section, "XML_IDX_FULLTEXT_LANGUAGE," at the end of this topic.

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:

Different XML columns can be associated with different full-text catalogs. Based on Microsoft's recommendations, use different full-text catalogs for XML columns with documents that change frequently and infrequently.

For example, if you have one ArcIMS Metadata Service with documents that are regularly updated and another ArcIMS Gazetteer Metadata Service with place-names that are rarely updated, the XML columns associated with these two services should be associated with different full-text catalogs. Create a second full-text catalog in the database such as IMS_GAZETTEER_CAT. Then, create a custom DBTUNE keyword such as IMS_GAZETTEER; in this keyword add the XML_IDX_FULLTEXT_CAT parameter set to the value IMS_GAZETTEER_CAT. Reference this custom keyword when creating the gazetteer service.

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 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 used for linguistic analysis of the column that is indexed in the full-text catalog. This setting affects characteristics such as word breaks and words to be ignored (for example, in English, articles such as a and the are ignored). If you do not properly set the language for linguistic analysis and the published documents are written, for example, 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 for linguistic analysis 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. The installation steps directed you to query the sys.fulltext_languages view to determine which languages are supported by your version of SQL Server. Set the XML_IDX_FULLTEXT_LANGUAGE parameter to either the identifier or the name of one of the supported languages; for example, you can use either 1036 or French to index the text in your XML documents in that language.

When a column is set to a specific language in an international version of SQL Server, you can publish and search documents in both the specific language and English. If the XML column contains 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 some text with the neutral setting.

To change the XML_IDX_FULLTEXT_LANGUAGE parameter in an ArcSDE geodatabase licensed through ArcGIS Server at the Enterprise level, use the sdedbtune administration command. Consult the ArcSDE Administration Command Reference for specifics on using the sdedbtune command.

To change the XML_IDX_FULLTEXT_LANGUAGE parameter in an ArcSDE geodatabase licensed through ArcGIS Server at the Workgroup level, you must install and run WorkgroupXMLLanguage.exe on the computer where SQL Server Express is installed. This executable and instructions for how to use it are provided with ArcIMS.

See the SQL Server Books Online help topics for more information on linguistic analysis and language support.

XML_COLUMN_STORAGE

The XML_COLUMN_STORAGE parameter determines whether XML columns are created as ArcSDE XML or native SQL Server XML. The default setting under the DEFAULTS configuration keyword in the SDE_dbtune table is DB_XML, which means the SQL Server native XML type is used. You can change this to SDE_XML if you want the majority of your XML columns to be created in the ArcSDE XML type.

The default setting for XML_COLUMN_STORAGE under the IMS_GAZETTEER keyword is SDE_XML. This is required for loading gazetteer data, since ArcIMS metadata services and gazetteer metadata services only use ArcSDE XML.


11/18/2013