Chapter 13: Environment Variables

XDB_ index-name_ location-name

XDB_index-name_location-name is an optional environment variable that is used in conjunction with the VCAT NAME option of the SQL CREATE INDEX command. To physically locate each index on a specific storage device (drive and path), specify a path in the [Server] topic of the xdb.ini file for each index/location combination, and subsequently create the index using the VCAT NAME option.

The format for this environment variable is:

XDB_index-name_location-name=drive-and-path

If a qualifier is required, enter a two-part name for the index-name, such as:

XDB_eric.orderidx_location1=m:\anypath

For example, to create and store an index called customerindex on d:\indexes\customer/indexes/customer and another index called ordersindex on e:\indexes\orders/indexes/orders (both for tables in the TUTORIAL location), you would make two entries in the xdb.ini file, and issue two SQL CREATE INDEX commands as follows:

In the [Server] topic of the xdb.ini file, enter the following:

XDB_customerindex_tutorial=d:\indexes\customer XDB_ordersindex_tutorial=e:\indexes\orders

Using the USING VCAT Index Options under the Create/Alter Table function in SQL Wizard, issue the following commands:

CREATE INDEX customerindex ON customer(c_no) 
    USING VCAT NAME; 
CREATE INDEX ordersindex ON orders(o_no) 
    USING VCAT NAME;

XDBCP

To support a number of non-American code page character sets, set the parameter XDBCP=codepage in the xdbsrvco.ini file [environment] group. Codepages that are directly supported are:

Number	Name
850	MS-DOS Latin1 (used in the entire European Community except Greece)
863	MS-DOS French Canada
865	MS-DOS Nordic
942	Japanese
949	Korean
437	MS-DOS Latin US
860	MS-DOS Portuguese

XDBIDX_ location-name

XDBIDX_location-name is an optional environment variable that is used in conjunction with the VCAT PATH option of the SQL CREATE INDEX command. To physically locate all indexes for a location on a specific storage device (drive and path), specify an index path in the xdb.ini file for each location, and subsequently create indexes using the VCAT PATH option.

The format for this environment variable is:

XDBIDX_location-name=drive-and-path

For example, to create and store all indexes for the TUTORIAL location in the path d:\indexes\tutorial, you would:

Make the following entry in the XDB Server machine's configuration file:
```
XDBIDX_tutorial=d:\indexes\tutorial
```
Using the USING VCAT PATH Index Option under the Create/Alter Table function in SQLWizard, issue the following command:
```
CREATE INDEX customerindex ON customer(c_no) 
    USING VCAT PATH;
```

XDBINI

The XDBINI variable specifies the directory where the xdb.ini file lives; this file contains both client and server side information for the SQL Option.

XDBNETINI

The XDBNETINI variable specifies the directory containing the xdbnet.ini file is; this file contains the TCP/IP information necessary so SQL Option client-side software can speak to a XDB server.

XDBRIDPOOL

XDBRIDPOOL is an optional environment variable that is specified in the [Server] topic of the xdb.ini file to enhance performance of the Query Optimizer.

The Query Optimizer may at times choose to use an algorithm called list prefetch when analyzing data access paths. This algorithm entails retrieving Record IDs (RIDs) from an index file and then sorting them. List prefetch is invoked when multiple index access is required or when data needs to be retrieved in physical order.

The format for this environment variable is:

XDBRIDPOOL=number-of-kilobytes

where number-of-kilobytes is the amount of memory to allocate for each statement's RID pool. The maximum size that can be specified is dependent on the amount of system memory that is available.

The RID Pool is the buffer that the server uses to store RIDs of this type. The default RID pool size is 512K per query. Since each RID contains 4 bytes, this default RID pool size can hold a maximum of 131,072 RIDs. If the RID pool size is exceeded, the list prefetch algorithm may be aborted, severely hampering performance.

The number of RIDs that can possibly enter the RID pool is determined by the selection criteria defined by a particular WHERE clause. A good rule of thumb for estimating an appropriate RID Pool Size is to determine the number of records that exist in the largest table, and then use the following formula:

RID Pool Size = (max records) * (.20) * (4 bytes per RID)

For example, a table containing 3,000,000 records would have an optimal RID Pool Size determined as shown below:

3,000,000 * .20 * 4 = 2,400,000 bytes

Converting this to kilobytes, we get:

2,400,000 / 1024 = 2344K

This RID Pool Size would allow an index condition that qualifies up to 20% of the table's rows to fit into the RID Pool.

The following XDBRIDPOOL environment variable setting would make available a 2400 kilobyte RID Pool per statement.

XDBRIDPOOL=2400

XSRVCFG

XSRVCFG is an environment variable (an entry in the xdb.ini file) that indicates the drive and path on which the XDB Server configuration files reside:

The crvconfg.xdb file contains configuration parameters for the XDB Server as entered via the XDB Server Configuration utility.
The xdbsrvco file contains settings for the XDB Server logging and checkpoint features.
The xdbnet.ini file contains information for XDB Servers and clients who use TCP/IP.

The [Server] topic of the xdb.ini file contains various settings for the XDB Server, including the environment variables described in this chapter. If a copy of SQLWizard is on the same machine, it also uses xdb.ini to store configuration settings.

These files are usually located in the directory where the XDB Server software is installed. The XSRVCFG environment variable is entered in the [Server] section of the xdb.ini file when you install the XDB Server.

The format for this environment variable is:

XSRVCFG=drive-and-path

For example, if the XDB Server and its configuration files are located on the \xdbnt directory of the XDB Server machine's C drive, the XSRVCFG environment variable would be entered as follows:

XSRVCFG=c:\xdbnt