The dbtune.sde file, initially found under the SDEHOME/etc directory, contains the default object storage parameters for ArcSDE geodatabases. When the ArcSDE sdesetup command executes (or when you run the ArcSDE Post Installation wizard on Windows systems), you can specify that the configuration parameters be read from the file and written into the DBTUNE table. If the dbtune file is absent, empty, or not specified, the DBTUNE table is created and populated with default configuration keywords representing the minimum ArcSDE configuration. In most cases, you will populate the DBTUNE table with specific storage parameters for your database.

You can alter the dbtune file prior to creating the ArcSDE geodatabase schema by

1. Making a backup copy of the default dbtune file. If you later decide to change any of the parameter values back to the default settings, you can use this backup file for reference.
2. Open the dbtune file with a text editor.
3. Edit the configuration strings you want to change or add any custom configuration keywords you want to use.
4. Create the system tables by running sdesetup or the ArcSDE Post Installation on Windows.

The ArcSDE for Oracle Windows installation includes several versions of the dbtune file; each specifies a different geometry storage in the DEFAULTS keyword. If you are performing a new installation of ArcSDE for Oracle (not upgrading the database), you can use one of the alternate versions of the file to populate your DBTUNE table during the postinstallation setup if you want your default geometry storage to be a type other than ST_GEOMETRY.

The ArcSDE for DB2 installation includes two dbtune.sde files—one for DB2 (dbtune.sde) and one for DB2 for z/OS (dbtune.sde.zos). Be sure to alter and use the correct dbtune file for your installation.

NOTE: If you are using ArcSDE for Oracle or DB2, you must uncomment some parameters under the DATA_DICTIONARY keyword and edit the associated configuration strings to specify a table space name before you create the ArcSDE geodatabase schema. There are also commented parameters under all the other keywords for which you should uncomment and specify a table space name before you begin adding data to your geodatabase. Commented lines are prefaced with a single pound sign (#). Remove this pound sign and replace the <text> with the name of the correct table space.

After the geodatabase has been created, you will use the sdedbtune administration command to edit the contents of the DBTUNE table. For instructions on how to use the sdedbtune command, see the ArcSDE Administration Command Reference included with your ArcSDE installation.

The anatomy of a dbtune file

The following example is from a SQL Server dbtune.sde file. Each number corresponds to a component of the dbtune file, which are described below.

1. Configuration keyword—Keywords are prefixed by two pound signs (##) in the dbtune file. These pound signs are not actually part of the keyword; they don't get stored in the DBTUNE table. They are present in the dbtune.sde file to signify which line in the dbtune file contains the configuration keyword.

NOTE: Make sure there are no spaces between the pound signs or between the pound signs and the configuration keyword in the dbtune file. If there are, ArcSDE will read the keyword as being part of the previous keyword.

ArcSDE records the configuration keyword (or DEFAULTS if one is not specified) for every layer in the system tables. It also records a keyword for nonspatial tables created in the geodatabase. Whenever you create a new object for a layer or registered table, such as an index, ArcSDE will use the current parameter values in the DBTUNE table for the keyword that was used to create the table. For example, if you create a column index with sdetable –o create_index, ArcSDE will refer back to the DBTUNE table for the storage information that was used to create the table and use it to create the index. For this reason, you must be careful if you delete DBTUNE entries. If you need to, you can alter the registration information for a feature class or table to use a new configuration keyword with sdelayer –o alter or sdetable –o alter_reg. See DBTUNE configuration keywords for more information on keywords.
2. Parameter name—Parameters come in three basic types: meta parameters, table parameters, and index parameters. Meta parameters define the way certain types of data will be stored, the environment of a configuration keyword, or a comment that describes it. Table parameters define the storage configuration of a DBMS table. When a client application sends a CREATE TABLE statement, ArcSDE appends the configuration string associated with the parameter to the CREATE TABLE statement prior to submitting the statement to the database. Index parameters define the storage configuration of a DBMS index. When a client application sends a CREATE INDEX statement, ArcSDE appends the index parameter to it prior to submitting the statement to the database.
For example, if you create a new layer in your SQL Server or Oracle database and you choose the WKB_GEOMETRY keyword, ArcSDE will add GEOMETRY_STORAGE="OGCWKB" to the CREATE TABLE statement it sends to the database. See DBTUNE configuration parameter name-configuration string pairs for more information on configuration parameters.
3. Configuration string—A configuration string value may span multiple lines. If the value is an actual string value, it must be enclosed in double quotes in the dbtune file. Boolean or numeric values don't require quotes. In some database management systems (DBMS), double quotes with no space between them indicate a value is blank. The double quotes are used to signify a literal value in the dbtune.sde file; they are not part of the value stored in the DBTUNE table.
The configuration string is always paired with a parameter name and provides the specifics for how an object will be created and stored in the database.
4. End keyword—Each parameter group must be followed by the END keyword. If the END keyword is missing from the end of any of the parameter groups, the file cannot be loaded into the DBTUNE table.

Any number of parameter groups may be specified in a dbtune file. However, certain groups and certain parameter names within groups are expected to exist and will be automatically created in the DBTUNE table if they do not exist in the dbtune.sde file.

Comments can be placed within the dbtune file and are indicated by a single pound sign (#). The default version of the dbtune.sde file provided with your ArcSDE component installation contains lines that are commented out. These lines are used as placeholders for certain storage parameters, which you can use by removing the comment character and editing the line.

The DBTUNE table contains the following columns:

• keyword—The configuration keyword
• parameter_name—The configuration parameter
• config_string—The value assigned to the configuration parameter

At its most basic level, the DBTUNE table is a lookup table; when you create data in, or add data to, a geodatabase, ArcSDE looks in the DBTUNE table for the configuration keyword you specify. ArcSDE takes the parameters and configuration strings associated with the keyword and appends them to the CREATE TABLE or CREATE INDEX statements the client application sends to the DBMS.

For example:

A user named pablo creates a new mines feature class using the New Feature Class wizard in ArcCatalog. Pablo specifies a name for the feature class (mines), field names, types, lengths, the DEFAULTS configuration keyword, and all the other characteristics of the feature class using the wizard. The wizard causes CREATE TABLE and CREATE INDEX statements to be sent to the Oracle DBMS. ArcSDE searches the DBTUNE table for all records with DEFAULTS as the keyword. It adds the config_string values for each parameter associated with the DEFAULTS keyword to the CREATE TABLE and CREATE INDEX commands sent by the wizard to the database. For the B_STORAGE parameter (which specifies the storage for the business table of the feature class) under DEFAULTS, the statement might be as follows:

CREATE TABLE pablo.mines (OBJECTID NUMBER(38), NAME NVARCHAR2(40), STATUS NUMBER(4)...)
IN TABLESPACE gisdata
WHERE PCTFREE = 0 and INITRANS = 4

NOTE: ESRI does not recommend you use SQL from a SQL editor to directly alter the contents of the DBTUNE table. Doing so would bypass certain protections written into the sdedbtune utility, possibly leading to reduced performance.

The DBTUNE table is created during the postinstallation setup and populated with default keywords, parameter names, and configuration string values or any customized values you made in the dbtune file prior to running the postinstallation tasks.

If you want to alter the contents of the DBTUNE table after it has been created—for instance, you want to create a new configuration keyword or alter the parameters of an existing keyword—you would use the sdedbtune administration command. You will use different operations with the sdedbtune command depending on what you want to change.

If you want to change the value of a parameter that already exists in the DBTUNE table, use the alter operation.

sdedbtune –o alter –k <configuration keyword>
–P <parameter_name> –v <configuration_string_value>
[–i <service>] [–D <database>] –u <user_name>
[–p <password>] [–N] [–q]

To delete one parameter from a parameter group or delete a configuration keyword and all its parameters, you must use the delete_data operation. Before deleting a configuration keyword, be sure the keyword is truly no longer needed by other users or applications, such as an ArcIMS map service.

sdedbtune –o delete_data –k <keyword>
[–P <parameter_name>] [–i <service>] [–s <server_name>]
[–D <database>] –u <user_name> [–p <password>] [–N] [–q]

To delete one parameter from a configuration keyword, you would specify both the keyword and the parameter in the command string (provide values for both –k and –P). To delete a configuration keyword and all the parameters that are part of that keyword, you only need to specify the keyword in the command.

Beginning with ArcSDE 9.3, if you need to add a new parameter to a keyword, use the sdedbtune command with the insert operation. This operation inserts one parameter at a time. For instance, in the following example, a UI_TEXT parameter is added to a custom keyword, RASTER:

sdedbtune -o insert -k RASTER -P UI_TEXT -v 'Use for raster data' -i 5000 -u sde

Remember that you can only insert valid configuration parameters. See DBTUNE configuration parameter name-configuration string pairs for a list of valid configuration parameters.

If you need to add a new keyword with multiple parameters to the DBTUNE table or if you are using ArcSDE 9.1 or lower, it is necessary to export, edit, then import the contents of the DBTUNE table. These are the steps you would take:

1. Export the DBTUNE table to a text file using the sdedbtune –o export command.
2. Make a backup copy of your text file.
3. Edit the exported text file with a UNIX file-based editor, such as vi, or a Windows file-based editor, such as Notepad.
4. Import the edited file to the DBTUNE table using the sdedbtune –o import command.

For ArcSDE 9.2 and later releases, you can export the DBTUNE table's contents to a file in a directory you specify. If you don't specify a directory, the file will be exported to the directory you are in when you issue the command. In ArcSDE 9.1 and lower, the sdedbtune administration tool always exported the file into the etc directory of the ArcSDE home directory; you could not relocate the file to another directory with a qualifying path name.

To run any of the edit operations of sdedbtune, you must provide the ArcSDE administrator user name and password.

For details on using the sdedbtune command to administer the DBTUNE table, see the Administration Command Reference provided with your ArcSDE component installation. You can also download a PDF version of the file from the Geodatabase Resource Center.

Some things for which the DBTUNE table is used in a DB2 for Linux, UNIX, or Windows database include the following:

• Loading tables and indexes into specific table spaces
• Setting the default BLOB and CLOB sizes on column creation
• Running statistics on a business table after a layer is loaded into the database
Tip
• Set the B_RUNSTATS parameter to YES to automatically run a full runstats on the business table 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 checking B_RUNSTATS. If B_RUNSTATS is equal to YES (the default), a full runstats will be performed on the table. If it is set to anything else, a runstats will not happen. The vast majority of users will 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.
• Controlling how many cursors per user can be allocated to the cache
Tip
• On heavily loaded systems, you'll want to be able to control the maximum cursor value or disable the cache entirely. For this, the DBTUNE parameter MAX_CACHED_CURSORS was added as a DEFAULTS keyword. The current default value is 80. To disable caching, set it to 0.

The DBTUNE table for DB2 for z/OS is used for the following:

• Loading tables and indexes into specific database subsystems
• Setting the default BLOB and CLOB sizes on column creation
• Controlling how many cursors per user can be allocated to the cache

Some things for which the DBTUNE table is used in Informix databases include these:

• Loading tables and indexes onto specific dbspaces
Tips
• S_STORAGE—which represents the smart BLOB sbspace—must be set for B_RTREE to work. S_STORAGE is used to store the spatial feature shape data. The S_STORAGE is the equivalent of the "put feature in <smart large object sbspace>" from the SDE 3.x dbtune.sde file. If S_STORAGE is not set, spatial feature data will be put in the default sbspace.
• The SDE 3.x A_SBLOB_DBS parameter is not automatically converted to the S_STORAGE parameter. You need to manually convert the A_SBLOB_DBS parameter into the new S_STORAGE parameter; otherwise, the default smart large object subspace is used. All other SDE 3.x parameters convert automatically to ArcSDE 9 parameters using the sdedbtune -o import command.
• Setting exclusive locking, to allow for database compression while existing users are still connected to the database
Tip
• During the compression of large geodatabases, the Informix database frequently runs out of available locks. To prevent this, tables are locked in exclusive mode during compress. However, if you want to be able to compress the database with existing users connected, the USE_EXCLUSIVE_LOCKING parameter can be set to false, disabling exclusive locking during compress.
• Specifying the fillfactor for indexes
• Setting the maximum number of cached cursors to use

Some of the things for which the DBTUNE table is used in Oracle databases include the following:

• Loading tables and indexes into specific table spaces
• Setting the geometry storage format
Tip
• For convenience, four predefined configuration keywords are provided in the installed dbtune.sde file to allow the use of each of the supported geometry storage methods. These are SDELOB, SDO_GEOMETRY, ST_GEOMETRY, and WKB_GEOMETRY.
During the installation of ArcSDE on Windows, the ArcSDE installation allows you to choose the default spatial storage type. Depending on your selection, ArcSDE will load the DBTUNE table with the contents of one of four files: dbtune.st, dbtune.blob, dbtune.sdo, or dbtune.sde. See About geometry storage types for information on spatial storage.
• Setting the raster storage format
ArcSDE provides three formats for Oracle: binary large object (BLOB), the default setting, LONGRAW, and Oracle GeoRaster type.
Tips
• If all the raster columns in your database use the same raster storage format, set the RASTER_STORAGE parameter once in the DEFAULTS configuration keyword. For example, to change the default RASTER_STORAGE from BLOB to SDO_GEORASTER, the following change is made:

## DEFAULTS
RASTER_STORAGE "SDO_GEORASTER"
<other parameters>
END




NOTE: The RASTER_STORAGE parameter supersedes the RASTER_BINARY_TYPE, which continues to work but is no longer supported.

• Be aware that Oracle has deprecated support of LONGRAW data types. Raster data storage should be converted to BLOB or GeoRaster types.
• Creating a separate, large rollback segment for compress transactions
Tip
• ESRI recommends you do this if you are using the Oracle manual undo method. However, beginning with Oracle 10g, Oracle recommends you don't use the manual undo method.
• Indicating whether logging for indexes is enabled

In PostgreSQL databases, the values set in the sde_dbtune table include the following:

• Loading tables and indexes into specific tablespaces
• Setting the storage type for geometric data
• Specifying how a full text index gets updated and the format in which XML document content is stored

The SDE_dbtune table in a geodatabase on SQL Server is used for the following:

• Loading tables and indexes into specific file groups
• Specifying an index created as either clustered or nonclustered
• Dictating the percentage to which an index page is filled
• Specifying the amount of binary data that can be stored in data pages (as opposed to image pages)
• Setting the geometry storage format
• Specifying grid sizes for spatial indexes for datasets stored in Microsoft spatial types
• Setting specific serverwide parameters such as whether to store data in Unicode format, the collation to use, whether to store certain types of data in row or out of row, and whether to enforce spatial reference ID integrity on registered third-party tables