DB SQL options

Environment Variables

FairCom DB SQL can set environment variables for the server process on startup. This functionality is implemented via the keyword SETENV. For example, the FairCom DB SQL looks at the CLASSPATH to find required classes for proper operation of the server. When the FairCom DB SQL is running as a Windows 32-bit versions Service, FairCom DB SQL is launched automatically by the SCM (Service Control Manager) when the computer starts up. Setting the CLASSPATH via a keyword eliminates the manual operation of setting environment variables. The syntax is as follows:

SETENV <var>=<value>

Where var is the environment variable and value is the setting. For example:

SETENV CLASSPATH=%CLASSPATH%;C:\FairCom\ctreeJQL\classes

The input line length for the server configuration file is 1024 to allow for very long configuration values.

The FairCom DB SQL considers existing environment variables during startup processing for the Java environment used with stored procedures. The following variables are used in the FairCom DB SQL configuration file, ctsrvr.cfg, to set the parameters for the Java JVM and compiler. These configurations are required for FairCom DB SQL Stored Procedures, Triggers, and User Defined Functions:

SETENV CLASSPATH=

SETENV JVM_LIB=

SETENV JAVA_COMPILER=

SETENV DEBUG_JVM

When these values are not explicitly set within the server configuration file, the server considers existing system environment variables. This feature simplifies setup and configuration changes for FairCom DB SQL Stored Procedures. Refer to the Appendix Server Configuration Keywords for a complete list of FairCom DB SQL-specific keywords.

Default Date Handling

FairCom DB SQL allows for internationalization of date formats. The following configuration option changes the default date handling:

SETENV TPE_DFLT_DATE=XX

or

SETENV_TPE_DFLT_DATE=XX_DFLT_DATE

where XX can be one of the following options:

• US US_DFLT_DATE
• UK UK_DFLT_DATE
• ISO ISO_DFLT_DATE

Changing the default date format affects:

• The default output format of date values.
• How SQL interprets date literals in queries and INSERT statements.

The format value determines how FairCom DB SQL interprets character strings inserted as DATE values or compared to DATE columns. For example, a format value of UK_DFLT_DATE allows users to supply date literals in British format (dd/mm/yyyy). The format value also determines the default output format of date data.

The following table details the different formats each supported value. The boldface entries indicate the default output format for each setting.

Case-Sensitivity Options

Sorting of fetched rows is performed as a case sensitive operation. Some applications may require sorting that should, instead, be case insensitive with NULL values appearing first in the sort. For example, the values “a, Z, B, g, NULL” should be sorted as “NULL, a, B, g, Z”.

A feature is available such that a database can be implied to be case sensitive (current default) or case insensitive. When a database is enabled as case insensitive, the sorting, comparisons and identifier will be considered case insensitive.

This feature is activated with the FairCom DB SQL configuration keyword

SQL_OPTION DB_CASE_INSENSITIVE

This should be done before creating the template database. Once the template database is created, ALL the databases will have the same settings of the template.

When using case insensitive searches, all search conditions, sorting and grouping is done in a case insensitive manner. Metadata and character indexes are also considered to be case insensitive. Case insensitivity is enabled on a database by database basis.

Optimizer Configuration Options

The FairCom DB SQL query optimizer plays an important role in reducing the execution time of FairCom DB SQL queries. The role of the optimizer is to minimize the number of read requests that are made for the data and index files.

This minimization process is computed by the optimizer based on the cardinality (the number of records selected), selectivity (the fraction of the records selected based on an operator), and a set of index key values. For many operations on records or indexes, a cost, in terms of time units, is used to decide between the use of a record or index-oriented scan.

Users may wish to disable the cardinality, selectivity and/or costing features to force particular optimizations of FairCom DB SQL. Having these features enabled or disabled will induce the optimizer to perform queries using different strategies to select the resulting dataset.

Three FairCom DB SQL configuration file keywords were added to control the optimizer's usage of cardinality, selectivity and costing of record and index operations: NO_CARDINALITY, NO_SELECTIVITY and NO_COSTS. These keywords must be used with the SQL_OPTION configuration command.

• NO_CARDINALITY - disables the cardinality logic that informs the optimizer about the number of rows in a data or index file.
• NO_SELECTIVITY - disables the selectivity logic that informs the optimizer about the percentage of rows returned when applying given search criteria.
• NO_COSTS - disables the logic that returns to the optimizer the cost in units of time for given record and index operations. The optimizer will use this information to decide whether to use a record or an index scan.

Note: Costs are not implemented in the current version of FairCom DB SQL. Behavior may change in future releases.

To disable cardinality, selectivity, and costing logic, add the following keywords to the FairCom DB SQL configuration file:

Example

SQL_OPTION NO_CARDINALITY

SQL_OPTION NO_SELECTIVITY

SQL_OPTION NO_COSTS

c-treeDB API ROWID Options

When using c-treeDB API and FairCom DB SQL, it is possible to encounter situations where c-treeDB API ROWIDs do not match FairCom DB SQL ROWIDs. With c-treeDB API the first ROWID starts with 1 and so on, as this is how the SRLSEG mode works. Conversely, FairCom DB SQL’s ROWIDs start with 0 and so on.

Now the ROWID at the FairCom DB SQL level is “zero” based by default, as it previously was. For backward compatibility with those applications requiring so, it is possible to force the “one” based option, identical to c-treeDB API, with the following FairCom Server configuration keyword:

SQL_OPTION CTDB_ROWID

Refer to the Appendix Configuration Keywords for a complete list of FairCom DB SQL-specific keywords.

Automatic Database Conversion Options

Enhancements and features to FairCom DB SQL sometimes require changes to the base system tables and internal workings of the FairCom DB SQL engine. To make this transition as seamless as possible, an automatic verification and conversion to the new format takes place when the server detects system tables from a previous version of the FairCom DB SQL.

To prevent this activity from occurring, a new FairCom DB SQL configuration keyword was introduced.

SQL_OPTION NO_DB_CONVERSION

This server configuration keyword is used to instruct the FairCom DB SQL to not perform the verification and the conversion process. To prevent a failed conversion attempt, these steps are controlled within a transaction such that upon failure a transaction abort or automatic recovery will restore the original data.

Refer to the Appendix Configuration Keywords for a complete list of FairCom DB SQL specific keywords.

Template Database __Master.dbs Handling

FairCom DB SQL previously created new databases by copying a supplied template database, __Master.dbs. (This template was automatically created if it did not exist when needed.) FairCom DB SQL databases are now created directly without copying from a template database. This change typically results in faster database creation in the usual case of a single database.

It is possible to revert the behavior of the template database with the following configuration keyword:

SQL_OPTION FORCE_TEMPLATE_DB

This keyword forces template database creation upon server startup even if the template already exists (to ensure a consistent starting database). When this keyword is specified, FairCom DB SQL creates all new databases by copying the template database. Refer to the Appendix Server Configuration Keywords for a complete list of FairCom DB SQL-specific keywords.

SQL_DEBUG Options

Additional FairCom DB SQL debugging control can be used with these configuration keywords:

;SQL Debugging information

;SQL_DEBUG LOG_STUBS_HIGH

;SQL_DEBUG LOG_STUBS_LOW

;SQL_DEBUG LOG_STUBS_MED

;SQL_DEBUG INDEX

;SQL_DEBUG INDEX_INFO

;SQL_LOGFILE C:\sql_debug.txt

This will log additional information to a specific SQL_LOGFILE if desired.

LOG_STUBS_HIGH logs very sparse information detailing table create activities.

LOG_STUBS_MED logs basic SQL Statement information.

LOG_STUBS_LOG logs very low level stubs information, which can be quite voluminous.

INDEX and INDEX_INFO may not be active at the current time (Oct 2005).

Advanced FairCom DB SQL Logging

FairCom DB SQL has extensive internal debugging logic that can be accessed through various means. SQL errors can be logged to the file sql_server.log in the FairCom DB SQL directory.

TPESQLDBG is an array of 'Y'/'N' characters that determine which debug options are enabled. The order of the debug operations in the string is shown below:

Options are enabled by specifying 'Y'. The string value to disable all debug options would look as follows:

NNNNNNNNNNNN

This value can be set through various means: a server configuration keyword; the built in fc_set_debug() stored procedure; and the ctsqlcdb.exe utility.

To set the debugging logic at server startup, place the following configuration keyword with the desired debug string in ctsrvr.cfg. For example, to enable Java stored procedure debug output, use the following configuration string:

SETENV TPESQLDBG=NNNNNNNNNNYN

A stored procedure fc_set_debug(varchar (20)) is available to dynamically change the internal server debug features. This stored procedure sets the debug features according to the TPESQLDBG string passed as an argument and returns a row containing the actual debug setting (after applying the new debug settings). If an empty string ("") is passed, fc_set_debug() returns the current debug setting. For example, to set the Java stored procedure debug output, call fc_set_debug() as follows:

CALL fc_set_debug('NNNNNNNNNNYN');

Additionally, the ctsqlcdb utility can be used as follows:

ctsqldb -setdbg NNNNNNNNNNYN <databasename> <servername>

You can retrieve the current settings in use with the following ctsqlcdb parameter:

ctsqldb -getdbg <databasename> <servername>

To enable Java stored procedure syntax error logging, use the following:

call fc_set_debug('NNNNNNNNNYY');

See Also

• fc_set_debug

Memory Manager (MM) System for Temporary Storage

For queries involving sorting and/or joins, FairCom DB SQL may create temporary tables which exist in memory and/or on disk, depending on the amount of data in the temporary table. FairCom DB SQL provides options to optimize these queries with regard to physical memory space available on the server. Tuning these options can greatly enhance performance when dealing with large result sets generating extremely large temporary tables.

An internal memory storage system provides a mechanism for FairCom DB SQL to store data in memory instead of on disk. By using this internal storage system for volatile data such as temporary tables and dynamic indexes, the FairCom DB SQL improves the performance of many queries, such as joins. Depending on the amount of memory available on a system, certain queries may create temporary tables too large to be stored in memory. In these cases, the internal storage system swaps blocks of data to a disk file as necessary. The following variables allow implementations to control the characteristics of how this internal storage system uses memory to create temporary tables.

• SETENV TPE_MM_CACHESIZE: Specifies the size, in kilobytes, of the memory cache used for temporary tables. The default value is 1,000 KB (1MB) of memory. The internal storage system uses this cache for storing temporary tables when sorting and creating dynamic indexes during processing. Increasing TPE_MM_CACHESIZE improves performance by reducing the need to write to the on-disk swap file.

The TPE_MM_CACHESIZE setting determines how much memory the SQL engine uses for its temporary tables for each SQL client. Increasing this setting increases the amount of temporary table data the FairCom DB SQL stores in memory instead of writing this data to the disk-based swap file. However, note that this memory is allocated for each SQL client, so you should consider how many clients will connect to FairCom DB SQL at any given time and make sure total cache memory doesn't exceed available physical memory on the system.

Up to 4 TB total memory is available in this subsystem.

• SETENV TPE_MM_SWAPSIZE: Specifies the maximum size, in kilobytes, of the swap file the internal storage system uses when it writes to disk from the main memory cache. The default is 500,000 KB (500 MB). The sorting of data larger than this size results in the following FairCom DB SQL error

(-16001):MM No data block

To resolve this error, the TPE_MM_SWAPSIZE limit needs to be increased.

Note: These swap files can be found during an active connection with a filename beginning with ‘M’ followed by a string of random alphanumerics. These files can sometimes be left around after an abnormal server shutdown. They can be safely deleted in such a case.

This keyword can be used to resolve error (-16025):MM sorting error.

Increasing the FairCom DB cache configurations DAT_MEMORY (data cache size) and IDX_MEMORY (index cache size) settings can significantly reduce the number of bytes read from disk while executing a query.

Note: The MM subsystem was replaced with a new architecture beginning with V10.3. See SQL_OPTION USE_MM.