FairCom DB SQL configuration keywords

This chapter explains the keywords provided for configuring FairCom DB SQL.

These keywords can be added in ctsrvr.cfg.

FairCom DB SQL Configuration Keywords

FairCom DB SQL is generally configured with standard FairCom DB configuration options, (cache sizes, transaction logging, etc.). Specific configurations are available in FairCom DB SQL for additional SQL operational parameters and performance tuning. Only a few of these are considered for routine use. For example,

SQL_PORT

SETENV TPE_DFLT_DATE

SQL_LOGFILE (SQL_LOGFILE, SQL_LOGFILE)

SETENV JAVA_COMPILER

SETENV CLASSPATH

SETENV JVM_LIB

Your application may require other options based on how the product was developed. Consult your application vendor if you have questions about other options. The FairCom support team is always available to help guide the most applicable configurations and usage.

MAX_SQL_ISOLATION_LEVEL

MAX_SQL_ISOLATION_LEVEL <level>

Sets the maximum transaction isolation level at which SQL clients can operate. The supported values are:

MAX_SQL_ISOLATION_LEVEL 1 Support READ_COMMITTED only

MAX_SQL_ISOLATION_LEVEL 2 Support READ_COMMITTED and REPEATABLE_READ.

MAX_SQL_ISOLATION_LEVEL 3 Support READ_COMMITTED, REPEATABLE_READ, and SERIALIZABLE.

If a SQL client requests an isolation level that exceeds the maximum SQL isolation level specified in the server configuration file, the SQL client operates at the maximum SQL isolation level rather than its requested isolation level.

SQL_CONNECT_TIMEOUT_SEC

SQL_CONNECT_TIMEOUT_SEC <seconds>

Sets the FairCom DB SQL connection timeout in seconds. The default is 1 second. A setting of 0 will revert to the behavior prior to V10.3.

SQL_DATABASE

SQL_DATABASE <dbname>

Specifies the default FairCom DB SQL database name to create if one doesn't exist at server startup.

SQL_IDLE_WAKE

SQL_IDLE_WAKE #of_seconds

Sets a time to close tables that are not in use however, remain open in the open table cache.

A 0 or negative values disables this feature.

Default: 2

SQL_KEEP_OPEN_TABLES

SQL_KEEP_OPEN_TABLES <# of tables>

Sets how many tables to remain open per connection although not in use in SQL in order to improve performance. A negative value enables the default.

Default: 60

SQL_LOGFILE

Enable Logging of FairCom DB SQL debugging information.

In V10.3 and later, this configuration option can include an environment variable name that will be substituted with its value when the configuration file is read.

SQL_MIN_CARD

SQL_MIN_CARD <# of rows>

Forces the SQL optimizer to consider that the tables contain, at minimum, <# of rows>.

Setting this value to any number greater than 0 (let say X) forces the optimizer to think that any table with less than X records contain X records. This may potentially help the optimizer, in certain cases, to chose the proper execution plan for tables even if the tables are actually empty.

Default: 0

SQL_PARTOPEN_COST

SQL_PARTOPEN_COST <multiplier>

Adjusts the partition file open cost optimization.

A partitioned file open cost is available for the SQL cost analyzer such that SQL takes into account the cost of opening partitions. Costs are calculated as follows:

• For an index on a partitioned file that is the partition key, the original table open cost is reported.
• For an index on a partitioned file that is not related to the partition key, the original table cost plus a multiplier times the number of active partitions is reported.
• For an index on a partitioned file that covers the partition key but does not order like the partition key, the original table cost plus a multiplier times the number of active partitions divided by 2 is reported.

Default: 10000

SQL_PORT

TCP/IP Port Number for FairCom DB SQL

Default: 6597

SQL_TRACE_CTREE_ERROR

SQL_TRACE_CTREE_ERROR <error code>

Forces a stack trace to be generated in <error code> is encountered. This helps determine the exact location of difficult to diagnose errors. Note that unhandled errors returned in c-treeDB API layer result in 2 stack traces generated for these errors.

History

As of V11.6.0 this option is dynamically configurable at runtime. This is available through the existing ctadmn utility (Change Server Settings > Change the specified configuration option). Specify a non-zero error code to enable this diagnostic, or zero to disable.

SESSION_TIMEOUT

SESSION_TIMEOUT <seconds>

The SESSION_TIMEOUT option forces TCP/IP connections to be removed after the specified number of seconds has elapsed without activity. This option has been verified on Windows, Linux, and Mac OS X.

History

In V11 and later:

For 64-bit FairCom DB servers, a timeout allows each thread to detect and perform its own disconnection in case of a timeout.

• If SESSION_TIMEOUT is negative, it is ignored.
• If SESSION_TIMEOUT is less than 5, it is set to 5 so that the minimum SESSION_TIMEOUT value is 5 seconds.

In V11.6.1 and later, SESSION_TIMEOUT also applies to SQL connections.

Default: No timeout

SETENV CLASSPATH

Sets the path to the JAR file containing the Java classes. The exact path will depend on the version of Java you have installed.

Example

See Run-Time Settings for Java Stored Procedure Support.

See Also

• SETENV JAVA_COMPILER
• SETENV JVM_LIB

SETENV DEBUG_JVM

Y (client) | S (server) When specified, the stored procedure is compiled with debug information and the server modified Java source file and the class file are not removed from disk.

Example

SETENV DEBUG_JVM=Y

SETENV DEBUG_JVM_PORT

The Debug port to connect the Java debugger to (jdb).

Example

SETENV DEBUG_JVM_PORT=50001

Default: 5678

SETENV DH_CACHED_STATEMENTS

Number of static statements to cache.

SETENV DH_CACHED_STATEMENTS=250

FairCom DB SQL statement caches provide a performance benefit by allowing the SQL engine to reuse previously parsed and optimized statements. Proper sizing of this cache can offer significant performance improvements to applications. Two independent caches are used, a static statement cache and a dynamic statement cache. FairCom DB SQL supports setting the size of these statement caches (the number of entries that can be stored) up to 16000.

Default: On Windows the default size is 150; on Unix systems the default size is 250.

See Also

• SETENV DH_DYN_CACHED_STATEMENTS

SETENV DH_DO_AHEAD

Disables 'FETCH AHEAD' logic. Fetch ahead logic normally provides database stability with only a small performance hit on fetches.

Example

SETENV DH_DO_AHEAD=N

SETENV DH_DYN_CACHED_STATEMENTS

Number of prepared statements to cache.

SETENV DH_DYN_CACHED_STATEMENTS=250

FairCom DB SQL statement caches provide a performance benefit by allowing the SQL engine to reuse previously parsed and optimized statements. Proper sizing of this cache can offer significant performance improvements to applications. Two independent caches are used, a static statement cache and a dynamic statement cache. FairCom DB SQL supports setting the size of these statement caches (the number of entries that can be stored) up to 16000.

Default: On Windows the default size is 150; on Unix systems the default size is 250.

See Also

• SETENV DH_CACHED_STATEMENTS

SETENV DH_ENABLE_POOL

Enables connection pooling.

Example

SETENV DH_ENABLE_POOL=Y

SETENV DH_JVM_OPTION_STRINGS

Allows specifying specific options to the JVM.

JVM environments might require specific tuning parameters, for example, changing the default Garbage Collector (GC) or setting memory limits on JVM heap usage. Use this parameter to pass in those values to fine tune your specific JVM environment.

The example below limits JVM memory (this is the default specified in ctsrvr.cfg in V11 and later):

Example

SETENV DH_JVM_OPTIONS_STRINGS=-XX:+UseG1GC;-Xms100m;-Xmx300m

• -Xms sets the initial heap size for JVM usage
• -Xmx sets the maximum heap size for JVM usage
• -XX:<jvm_parameter> sets specific optional Java JVM parameters. For example, setting desired garbage collectors for specific workloads.

For most server side processing such as stored procedures FairCom recommends the G1GC or newer garbage collector tuned to these types of multi-threaded server workloads, +XX:UseG1GC;

Consult the current Java documentation for your configured Java version and distribution for specific options available.

Default

None. When not provided the JVM is loaded with the Java defaults for that Java version, distribution and platform. Therefore it is advised to pass in your desired JVM parameters to ensure consistent usage parameters.

SETENV DH_ENABLE_POOL

Configures the limit of open cursors available in a SQL session.

Example

SETENV DH_OPEN_CURSORS=xxx

Default: 50

SETENV DH_OPT_OR_CARD

An OR clause optimization was improved, ensuring that the execution node restriction was broken down even when there is only one table. Functionality was added to convert OR into UNION, which is beneficial if the cardinality of each ORed predicate is small. This keyword specifes the threshold for this conversion

Example

SETENV DH_OPT_OR_CARD=xxx

Default: 1000

SETENV DH_POOL_SIZE

Set number of pooled connections. 1< n <=100

Example

SETENV DH_POOL_SIZE=20

SETENV DH_REBUILD_SEL_CUTOFF

This controls the regeneration of the plan for queries with parameter references, during execution, based on the value set: -1 < n <= 100.

• If the value is set to -1, query plans are never regenerated during execution.
• If the value is set to 0, query plans are always regenerated during execution.
• If the value is set between 1 and 100, query plans will be regenerated whenever the percentage difference in selectivity (i.e., the selectivity that was used during the generation of the original plan and the selectivity for the actual parameter values passed during execution) exceeds this value. For example, if the value is set to 25, the plan will be regenerated only if the selectivity of the actual parameter is different from the original selectivity than 25%.

Example

SETENV DH_REBUILD_SEL_CUTOFF=25

Default: (-1) Query plans are never regenerated during execution.

SETENV DH_THREAD_STACK_SZ_KB

The stack space utilized by FairCom DB SQL threads (in kilobytes).

Example

SETENV DH_THREAD_STACK_SZ_KB=512

SETENV DH_SVR_DA_BUFFER

Controls the size of the SQLDA structure within the server and the size of the fetch ahead buffer.

SETENV JAVA_COMPILER

Indicates the full path to the Java compiler. The exact path will depend on the version of the compiler you have installed.

Example

See Run-Time Settings for Java Stored Procedure Support.

See Also

• SETENV CLASSPATH
• SETENV JVM_LIB

SETENV JVM_LIB

Indicates the full path to the Java Virtual Machine (JVM) installed on your system. The exact path will depend on the version of the JVM you have installed.

Example

See Run-Time Settings for Java Stored Procedure Support.

See Also

• SETENV CLASSPATH
• SETENV JAVA_COMPILER

SETENV TPESQLDBG

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 12 ‘Y’/‘N’ characters that determine which debug options are enabled.

Example

SETENV TPESQLDBG=NNNNNNNNNNYN

See

• Advanced FairCom DB SQL Logging

SETENV TPE_DFLT_DATE

Changes the default date format used by FairCom DB SQL. Changing the default date format affects:

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

The following date types are supported:

• US - US_DFLT_DATE
• UK - UK_DFLT_DATE
• ISO - ISO_DFLT_DATE

Examples

SETENV TPE_DFLT_DATE=UK

SETENV TPE_DFLT_DATE=ISO_DFLT_DATE

Default: US_DFLT_DATE

SETENV TPE_LOG

You can set the path for sql_server.log using this keyword in ctsrvr.cfg:

SETENV TPE_LOG=<directory>

; SQL statement diagnostic logging

;SQL_DEBUG LOG_STMT

;SQL_SERVER_LOG_SIZE 100MB

;SETENV TPE_LOG=<alternate path to sql_server.log>

You may find this section present and commented out in your default FairCom DB configuration file for your convenience.

See Also

• SQL_SERVER_LOG_SIZE (SQL_SERVER_LOG_SIZE, SQL_SERVER_LOG_SIZE)
• SQL_DEBUG LOG_STMT (SQL_DEBUG LOG_STMT, SQL_DEBUG LOG_STMT)

SETENV TPE_MM_CACHESIZE

LEGACY Option

Specifies the size, in kilobytes, of the memory cache used for temporary tables. The default value is 1,000 Kb 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. But note that this memory is allocated for each SQL client, so you should consider how many clients will connect to the server at any given time and make sure total cache memory doesn't exceed available physical memory on the system.

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 once the server has been properly shut down.

Example

SETENV TPE_MM_CACHESIZE=1024

Important: The MM subsystem was replaced with a new architecture beginning with V10.3. Only enable the legacy MM subsystem on the advice of FairCom's engineering team.

SETENV TPE_MM_COMPARE_OVHD

LEGACY Option

The type of query shown below was found to run slower than expected in certain situations:

SELECT (TOP k) * FROM tbl WHERE x BETWEEN a AND b ORDER BY y
 

The default cost estimates used in choosing an execution plan have been updated to improve general performance of this type of query. Additional options are also available to further adjust costing used in this optimization.

• TPE_MM_COMPARE_OVHD (default value is 1)
• TPE_MM_INSERT_OVHD (default value is 250)

Important: The MM subsystem was replaced with a new architecture beginning with V10.3. Only enable the legacy MM subsystem on the advice of FairCom's engineering team.

SETENV TPE_MM_INSERT_OVHD

LEGACY Option

The type of query shown below was found to run slower than expected in certain situations:

SELECT (TOP k) * FROM tbl WHERE x BETWEEN a AND b ORDER BY y
 

The default cost estimates used in choosing an execution plan have been updated to improve general performance of this type of query. Additional options are also available to further adjust costing used in this optimization.

• TPE_MM_COMPARE_OVHD (default value is 1)
• TPE_MM_INSERT_OVHD (default value is 250)

Important: The MM subsystem was replaced with a new architecture beginning with V10.3. Only enable the legacy MM subsystem on the advice of FairCom's engineering team.

SETENV TPE_MM_SWAPSIZE

LEGACY Option

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.

Setting this value too small will result in error -16001:MM No data block for queries that require more swap space than is available. A maximum memory limit of 4TB is available.

Example

SETENV TPE_MM_SWAPSIZE=500000

Note: Prior to FairCom DB V9.3.41446 Build 110507, a limit of 2Gb existed. Values larger than this improperly "wrapped" around.

Important: The MM subsystem was replaced with a new architecture beginning with V10.3. Only enable the legacy MM subsystem on the advice of FairCom's engineering team.

SETENV TPE_TMPDIR

SQL swap files are used for on-the-fly sorting of large query result sets. By nature, they are temporary, however, can unexpected consume more disk space than expected for some extremely large query results. For example, an ORDER BY clause may require a sort of a very large final result set that requires sending these to disk. This storage location can be specified in ctsrvr.cfg such that these files can be written to a volume of sufficient storage capacity.

Example

SETENV TPE_TMPDIR=<path>.

These files usually show up as LTS_* and are removed when a query execution requiring additional storage space completes. After killing a SQL connection creating (owning) these files it may in some cases continue to completion before removing files.

SQL_OPTION CHECK_HEAP

SQL_OPTION CHECK_HEAP

Enables the FairCom DB SQL memory free function to check for memory overwrites and double frees. If it detects one of these situations, it logs a message to CTSTATUS.FCS and raises an unhandled exception that terminates the process. The messages are:

SQL_HEAP_CHECK: operator delete detected double free of memory

SQL_HEAP_CHECK: operator delete detected memory overwrite

SQL_OPTION BADDATES_ASNULL

SQL_OPTION BADDATES_ASNULL

An invalid date (e.g., a pre-1700 date) is reported as CTDB_INVDATE. A configuration option, SQL_OPTION BADDATES_ASNULL, allows date fields that would normally return an error due to an out-of-range date to be returned to SQL as NULL.

The following criteria will return every bad date, and dates that are null, for the table containing the mydate column:

WHERE mydate > '12/31/9999' OR mydate IS NULL

Warning This option is only recommended when initially analyzing "dirty" data in tables, for example, linked legacy data. Using this option can mask actual invalid data that should be corrected.

See Also

SQL_OPTION BADTIMES_ASNULL (SQL_OPTION BADTIMES_ASNULL, SQL_OPTION BADTIMES_ASNULL )

SQL_OPTION BADTIMES_ASNULL

SQL_OPTION BADTIMES_ASNULL

An invalid time is reported as CTDBRET_INVTIME. A configuration option, SQL_OPTION BADTIMES_ASNULL, allows time fields that would normally return an error due to an out-of-range date to be returned vis SQL as NULL.

Warning This option is only recommended when initially analyzing "dirty" data in tables, for example, linked legacy data. Using this option can mask actual invalid data that should be corrected, for example, wrong query result if the affected fields are part of an index.

See Also

SQL_OPTION BADDATES_ASNULL (SQL_OPTION BADDATES_ASNULL, SQL_OPTION BADDATES_ASNULL).

SQL_OPTION CTSTRING_VARCHAR

SQL_OPTION CTSTRING_VARCHAR

Allows VARCHAR and LVARCHAR fields to be created as CT_STRING fields for backwards compatibility. These are normally created as CT_[ 2 | 4 ]_STRING.

See also

SQL_OPTION NO_TCPIP can prevent the server from accepting TCP/IPv4 and IPv6 SQL connections, while still allowing SQL shared memory connections.

SQL_OPTION NO_SHARED_MEMORY can prevent the server from accepting SQL shared memory connections.

SQL_OPTION CREATE_WITH_ROWID

SQL_OPTION CREATE_WITH_ROWID

Enables default ROWID for new tables. By default, tables are created with a ROWID and without a RECBYT index.

SQL_OPTION CREATE_WITH_RECBYT

SQL_OPTION CREATE_WITH_RECBYT

Enables default RECBYT fields for new tables. By default, tables are created with a ROWID and without a RECBYT index.

SQL_OPTION CTDB_ROWID

Revert to a different ROWID value starting points (0 vs. 1) for c-treeDB API and FairCom DB SQL.

SQL_OPTION DB_CASE_INSENSITIVE

Enables case insensitive comparisons, sorting, and identifiers within a database and is enabled on a database by database basis.

SQL_OPTION DROP_TABLE_DICTIONARY_ONLY

SQL_OPTION DROP_TABLE_DICTIONARY_ONLY

Affects SQL DROP TABLE command operation. This option retains files on disk when tables are dropped from the SQL dictionary. Use this option when you want to undo a DROP TABLE command. You can also use this option when you want files/tables to remain available in Low‑Level, ISAM, and c-treeDB API APIs, as well as FairCom RTG, and not in SQL.

This option is particularly useful when using c‑tree features, such as ctutil -sqllink, ctutil -sqlize, ctdbaddtable, and ctsqlimp utilities and the ctsqlimport API, which may bring extra files into the SQL data dictionaries that later need to be dropped from SQL without removing them from disk.

SQL_OPTION FORCE_TEMPLATE_DB

Use the legacy __Master.dbs database template for new databases.

LEGACY OPTION: NOT RECOMMENDED

SQL_OPTION HOT_ALTER_TABLE

SQL_OPTION HOT_ALTER_TABLE

(Supported in V11.5 and later) Change the default storage attributes to include 'HOTALTER' so that newly created tables will support Hot Alter Table unless they are specifically created with 'NOHOTALTER'.

SQL_OPTION HM_ACTIVE

Note: FairCom Internal option only .

SQL_OPTION LOAD_CALLBACK_LIB

Load the data type SDK callback library.

(In V10.3 and later) As the callback library is critical for proper data interpretation, when its loading is required by specifying the above configuration keyword, a failed load generates a panic condition and terminates the FairCom DB SQL Server operation with the following message logged in CTSTATUS.FCS:

- User# 00001 : PANIC - TPEUTIL LoadSQLSDK callback library load failed PID 5996

- User# 00001 SQL PANIC, attempting shutdown

SQL_OPTION NO_AUTO_ABORT

SQL_OPTION NO_AUTO_ABORT

Disables the automatic ABORT (ROLLBACK) feature should an error occur while processing.

Note: This option is disabled by default.

SQL_OPTION NO_BINARY_PAD

This is a behavior change that affects how the values of BINARY fields are stored in V11 and later.

Prior to V11, the server was not padding BINARY fields with 0x00 when the values were less than the defined length. Because of this, both the values stored on disk and the values returned may have been shorter than the defined length (basically BINARY and VARBINARY behaved in the same manner). This behavior has been corrected in V11 and later so that BINARY fields are properly padded with 0x00 both on disk and when retrieved. Existing data is not touched on disk, however the values returned in SQL are now padded.

The previous behavior can be restored by using the keyword SQL_OPTION NO_BINARY_PAD in ctsrvr.cfg.

This is only documented for a select few applications that may be impacted by the V11 change in behavior. Consult FairCom support should have you concerns about your current binary data prior to V11.

USE OF THIS OPTION WILL LIKELY RESULT IN UNEXPECTED BINARY FIELD BEHAVIOR.

SQL_OPTION NO_CARDINALITY

Disables cardinality logic that informs the optimizer about the number of rows in a data or index file.

SQL_OPTION NO_COSTS

Disables 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: This option is not implemented and is reserved for future use.

SQL_OPTION NO_CRYPT_SYSTBL

SQL_OPTION NO_CRYPT_SYSTBL

In V10.3 and later, FairCom DB SQL system tables are encrypted by default to protect sensitive database meta data. Any of the advanced encryption algorithms can be specified, so for example you can protect these tables using AES with a 128-bit key.

To have system tables in clear text, SQL_OPTION NO_CRYPT_SYSTBL must be placed in ctsrvr.cfg before the database is created.

SQL_OPTION NO_DB_CONVERSION

When upgrading between FairCom DB SQL versions, the database engine will attempt to upgrade system tables, etc. to the latest formats. In some cases this is not desired. This option disables the database conversion on startup.

SQL_OPTION NO_DUP_IDXNAME

SQL_OPTION NO_DUP_IDXNAME

Support was added that changed the way index file names are generated to guarantee name uniqueness. This naming convention can cause compatibility problems with existing ISAM application containing hard-coded index names for tables created using SQL. This option reverts this new support.

SQL_OPTION NO_HUGEFILE

Do not create tables as HUGE (> 4GB).

Default: HUGE files are enabled for all FairCom DB SQL created tables.

SQL_OPTION NO_IPV6

SQL_OPTION NO_IPV6

Native SQL clients on Windows will attempt to connect to the first address (IPv4 or IPv6) resolved for the host.

Note: In V11, IPv6 is supported on Windows and Linux. Java and ADO.NET SQL clients currently support only IPv4. Explicit IPv6 addresses in client connection strings are not currently supported.

Server keyword SQL_OPTION NO_IPV6 can be added to ctsrvr.cfg to accept only IPv4 connections. This is not generally recommended and is more likely to cause connection issues than to solve them.

An environment variable can be used for native clients to request only IPv4 address: CTSQL_IPV4_ONLY. Setting this variable to any value in the environment will effectively disable IPv6 connection attempts from the client. This may be needed on networks where both IPv4 and IPv6 are enabled, but the c-tree SQL server does not accept IPv6 connections.

SQL_OPTION NO_PUSHDOWN

Internal logic change for filtering of records.

WARNING: Only use on the advice of FairCom. This will negatively impact performance in most cases.

SQL_OPTION NO_REVERSE_INDEX

Note: FairCom Internal option only.

SQL_OPTION NO_SELECTIVITY

Disables selectivity logic that informs the optimizer about the percentage of rows returned when applying a given search criteria.

SQL_OPTION NO_SHARED_MEMORY

Do not use the shared memory protocol as the default on localhost connections. When this option is specified, TCP/IP is used by default.

Note: Unless this option is specified, shared memory is the default for local SQL connections regardless of the protocol used for ISAM connections. See the c-tree Server Administrator's Guide for information about the communication protocol for ISAM connections.

SQL_OPTION NO_SYSSUPERFILE

Creates FairCom DB SQL system tables as individual files rather than a single c-tree Superfile.

SQL_OPTION NO_TCPIP

SQL_OPTION NO_TCPIP

Prevents the server from accepting SQL connections using TCP/IPv4 and IPv6. Still allows SQL shared memory connections.

See also

SQL_OPTION NO_SHARED_MEMORY can prevent the server from accepting SQL shared memory connections.

SQL_OPTION OLD_DELFLD_LEN

Allows FairCom DB SQL CREATE TABLE and ALTER TABLE commands to use the $DELFLD$ size that was in effect prior to V9.5:

SQL_OPTION OLD_DELFLD_LEN

In FairCom DB SQL V9.5 the c-treeDB API $DELFLD$ size changed from 4 bytes (or 5 in case of non-HUGE files) to 9 bytes. This change required any C structures describing the record to be changed.

Any table created when this keyword is specified in the ctsrvr.cfg will have the 4-byte $DELFLD$ used in earlier releases.

Any table created or altered in structure using ALTER TABLE while SQL_OPTION OLD_DELFLD_LEN is in effect will have the $DELFLD$ set to 4 bytes regardless of the size of the original table.

At the c-treeDB API level, the ctdbAlterTable() function now takes a new mode, CTDB_ALTER_V8DELFLD, to indicate that the table (if re-created) must have the old $DELFLD$ size (CTDB_ALTER_V8DELFLD forces CTCREATE_V8DELFLD during ALTER TABLE).

SQL_OPTION OWNER_FILE_NAMES

FairCom DB SQL is a file based database engine. To avoid naming collisions between files it is usually necessary to maintain additional information in the file name. This option prepends the owner ('admin' by default) to the file front of the file name, much as a schema might be considered.

SQL_OPTION PAD_WITH_NULL

Set default padding of strings to NULL rather than spaces (SQL default). Use this option if you need to create tables in SQL that match legacy applications with ISAM tables using NULL padding.

SQL_OPTION PARTIAL_SEG

Allow use of imported c-tree indexes containing partial segments in keys.

Note: This option only applies to legacy imported tables.

SQL_OPTION PID_IN_NODEID

The ODBC API does not have an interface to specify extra identifying information that our server can display to identify a specific application using the ODBC driver. By default, c-tree Server set the "nodename" to "SQL:<dbname>" when a new connection was made.

It is now possible to display a client process ID connected to the server. On the server side, when SQL_OPTION PID_IN_NODEID is specified in ctsrvr.cfg, the client process ID is appended to the default nodename as "SQL: PID <client pid>". If the client application does not pass the PID information, the original default nodemane is still used. The default node name can be overwritten at any time after connection by a client calling the fc_set_nodename built-in stored procedure.

This nodename string is displayed for all SQL client APIs and tools except for JDBC-based APIs/tools.

SQL_OPTION PRESERVE_CURSOR_FREE_LOCK

Free all locks when isolation level is READ_COMMITTED and keep only read locks when isolation level is higher.

SQL_OPTION SUPPRESS_JVM_LOAD_ERR

SQL_OPTION SUPPRESS_JVM_LOAD_ERR

Disables JVM error messages at server startup. A "No Java capabilities" message is output instead.

SQL_OPTION USE_MM

SQL_OPTION USE_MM

(LEGACY) Switch back to the legacy MM temp sorting subsystem.

Deprecated: This keyword is no longer available as of V11.6.

See Also

• SETENV TPE_MM_CACHESIZE
• SETENV TPE_MM_COMPARE_OVHD
• SETENV TPE_MM_INSERT_OVHD
• SETENV TPE_MM_SWAPSIZE

Note: The MM subsystem was replaced with a new architecture beginning with V10.3. Only enable the legacy MM subsystem on the advice of FairCom's engineering team.

SQL_DEBUG ABORT_ON_PANIC

SQL_DEBUG ABORT_ON_PANIC

Enables FairCom DB SQL shutdown on a panic condition for debugging purposes. By default, a panic is only logged to CTSTATUS.FCS.

SQL_DEBUG ERROR_INFO

SQL_DEBUG ERROR_INFO

Enables FairCom DB SQL logging to sql_server.log when a query fails.

SQL_DEBUG INDEX

Advanced debugging of index key retrievals.

WARNING: Extreme performance impact.

See Also

• SQL_DEBUG INDEX_INFO

SQL_DEBUG INDEX_SEL

SQL_DEBUG INDEX_SEL

Enables output selectivity information also for index selectivity.

SQL_DEBUG INDEX_DEF_SEL

SQL_DEBUG INDEX_DEF_SEL

Enables checks for cases where calculating the default selectivity is suspect and logs "COMPLEX SELECTIVITY" messages in the function monitor window with information on why the calculation is suspect. There are no corrective actions.

SQL_DEBUG INDEX_INFO

Advanced debugging of index key retrievals.

WARNING: Extreme performance impact.

See Also

• SQL_DEBUG INDEX

SQL_DEBUG LOG_STUBS_HIGH

Logs sparse information detailing table create activities.

SQL_DEBUG LOG_STUBS_MED

Logs basic FairCom DB SQL Statement information.

SQL_DEBUG LOG_STUBS_LOW

Logs low level information, which can be quite voluminous.

Note: Due to the volume of information logged, this option can negatively impact performance. Please consult FairCom support before enabling this option.

SQL_DEBUG LOG_STMT

SQL_DEBUG LOG_STMT

This server configuration keyword allows logging executed SQL statements in sql_server.log.

The main advantage compared to SQl_DEBUG_LOG_STUB_MED is that the logging allows identifying which connection sent the statement so that it is easier to understand the statement executed by a specific connection.

Compared to the SQL_DEBUG LOG_STMT_TIMES, this keyword generates less output and the statement is logged before the execution (LOG_STMT_TIMES logs after execution). This makes it possible to identify a very long-running query by looking at the log.

Behavior Change

In V11.6 and later, the statement logging output produced by this configuration keyword has been changed to be in one line in JSON format and to include extra information such as the timestamp, the user, and the IP address of the client making the request.

For example:

{"timestamp":"Fri Aug 24 11:56:24 2018","ipaddr":"::ffff:127.0.0.1","db":"CTREESQL","user":"admin","thread":23,"operation":"OPEN","exectime":7,"tmptime":2,"statement":"select * from syscolumns order by width "}

See Also

• SQL_DEBUG LOG_STMT_TIMES (SQL_DEBUG LOG_STMT_TIMES, SQL_DEBUG LOG_STMT_TIMES), SQL_DEBUG LOG_STMT_TIMES_FETCH
• SQL Statement Diagnostic Logging Keyword Added to Default Server Config

SQL_DEBUG LOG_STMT_TIMES

SQL_DEBUG LOG_STMT_TIMES

Enables logging of start and end times for statements PREPARE, EXECUTE and OPEN. Logs to sql_server.log.

Behavior Change: In V11.5 and later, the output of SQL_DEBUG LOG_STMT_TIMES has been modified to add the time spent adding, sorting, and retrieving rows into a temp or a sort table. This information will help in understanding how time is distributed during an update statement.

The "time" is precise on the insert, sort, end_insert, and fetch operations occurring on temp/sort tables. It does not account for the time spent operating on other storage engines.

The output (if a temporary table is involved and timed) is now of the following form where the "tmptbl" value is the time in milliseconds spent in temporary tables:

id:%05d ms:%06d tmptbl:%06d op:%s

This modification changes the output format of SQL_DEBUG LOG_STMT_TIMES.

Behavior Change

In V11.6 and later, the statement logging output produced by this configuration keyword has been changed to be in one line in JSON format and to include extra information such as the timestamp, the user, and the IP address of the client making the request.

For example:

{"timestamp":"Fri Aug 24 11:56:24 2018","ipaddr":"::ffff:127.0.0.1","db":"CTREESQL","user":"admin","thread":23,"operation":"OPEN","exectime":7,"tmptime":2,"statement":"select * from syscolumns order by width "}

In addition, SQL_DEBUG LOG_STMT_TIMES has been modified so that it does not log the parameter value of a parameterized string.

See Also

• SQL_DEBUG LOG_STMT_TIMES_FETCH

SQL_DEBUG LOG_STMT_TIMES_FETCH

SQL_DEBUG LOG_STMT_TIMES_FETCH

Log start and end times of a fetch sequence (open cursor, all fetches and close cursor) to the sql_server.log file.

Behavior Change

In V11.6 and later, the statement logging output produced by this configuration keyword has been changed to be in one line in JSON format and to include extra information such as the timestamp, the user, and the IP address of the client making the request.

For example:

{"timestamp":"Fri Aug 24 11:56:24 2018","ipaddr":"::ffff:127.0.0.1","db":"CTREESQL","user":"admin","thread":23,"operation":"OPEN","exectime":7,"tmptime":2,"statement":"select * from syscolumns order by width "}

See Also

• SQL_DEBUG LOG_STMT_TIMES

SQL_DEBUG TRACE_ON_PANIC

SQL_DEBUG TRACE_ON_PANIC

When specified in ctsrvr.cfg and a SQL PANIC occurs, a stacktrace or minidump is requested on supported systems.

The stacktrace on Unix platforms, or minidump on Windows, will be created in the FairCom Server process' current working directory. (On Linux/Unix, symbol resolution will not work correctly if the working directory does not contain the server binary files.)

The file name convention on most Unix platforms will typically be pstackPID_N.log where PID = FairCom Server Process ID and N is a serial number. On Windows, the file name will have a file extension of .mdmp (e.g., stack12345_01.mdmp).

SQL_SERVER_LOG_SIZE

SQL_SERVER_LOG_SIZE <log_size>

where <log_size> is the log size in bytes. A suffix KB, MB, or GB can be used.

FairCom DB SQL Server limits the size of the SQL log file sql_server.log. When a write to sql_server.log exceeds a configured size limit, the log file is renamed sql_server.bak and a new sql_server.log file is created.

This limit is set using the configuration option SQL_SERVER_LOG_SIZE in ctsrvr.cfg. It can be set at runtime using the ctadmn utility.

Example 1:

; 1 MB limit

SQL_SERVER_LOG_SIZE 1MB

Example 2:

; 200,000,000 byte limit

SQL_SERVER_LOG_SIZE 200000000

Using ctadmn

ctadmn options are:

10. Change Server Settings

10. Change the specified configuration option

Enter the configuration option and its value >>

At this point, enter the desired value, such as: sql_server_log_size 50 mb

Using ctSETCFG()

See SetSystemConfigurationOption().

The syntax using ctSETCFG() is:

setcfgCONFIG_OPTION, "SQL_SERVER_LOG_SIZE value"

where value is the maximum log size of the sql_server.log file. The value defaults to bytes, or a suffix of KB, MB, or GB can be used.

Default

The default is 32 MB. The maximum size is 2 GB ‑ 1. Specifying a value of zero disables the limit. Specifying a non‑zero value that is less than 64 KB sets the limit to 64 KB.

See Also

• SETENV TPE_LOG

SQL_SESSION_TIMEOUT

SQL_SESSION_TIMEOUT <secs>

FairCom DB SQL offers the ability to set a session (connection) timeout. The value of this timeout can be different from the one set at ISAM or FairCom RTG level.

The rationale behind this feature is that ISAM access is used by the application, which may need to stay connected, whereas SQL is often used outside the application (e.g., for third-party reporting and analytics). Unexpectedly long-running SQL connections can be released without impacting applications connection timeouts.

• Values of 0 or less are considered "no timeout."
• Values between 1 and 5 are rounded up internally to 5 as is done by the SESSION_TIMEOUT keyword.
• If both SQL_SESSION_TIMEOUT and SESSION_TIMEOUT are specified in ctsrvr.cfg, SQL_SESSION_TIMEOUT takes precedence over SESSION_TIMEOUT for SQL sessions.

SUBSYSTEM SQL LATTE

The Latte engine is a disk based container for rapid sorting of data used extensively in SQL query temporary data tuple sorting. This subsystem has multiple options for performance and capacity configuration and accepts the following options:

• MAX_MEMORY <memory> - maximum advisable memory per environment.
• MAX_STORE <limit> - limits the per-connection amount of temporary disk space available for SQL query execution. Default is 512 GB. Max is 128 TB. Minimum is 2 GB. Setting this value too small could cause some SQL queries to fail with LT_ERR_STOREFULL (-16306). This option can be dynamically configured without restarting the server.
• CACHE_BLK <blocks> - default number of blocks in the temporary cache of a table.
• NONE - This option is used in conjunction with the tamper-proof settings file under the server. When specified, the entire SUBSYSTEM SQL LATTE cannot be overridden in ctsrvr.cfg

Example

SUBSYSTEM SQL LATTE

{

MAX_MEMORY 64M

MAX_STORE 1000 GB

}

History

V10.3 - Latte replaces the original MM temporary table sorting engine in prior releases.

V11.6.0 - MAX_STORE added.

SYSLOG

SYSLOG <option>

FairCom DB optionally maintains a system event log, SYSLOG. This is maintained in two system files: SYSLOGDT.FCS and SYSLOGIX.FCS. These files comprise a FairCom DB data file and index pair with a record for each recordable system event. Unlike the text based CTSTATUS.FCS, SYSLOG can be encrypted such that entries cannot be added, deleted, or modified with a simple text editor, and vendors can log application specific entries.

The System Event Log contents are controlled by SYSLOG configuration keywords in ctsrvr.cfg, the ctsrvr.set settings file, or from the command line. They are entered as pairs in the form of: SYSLOG <keyword>. As many of these pairs as desired may be used at the discretion of your application vendor.

Current SYSLOG options include:

Note: Build 210901 and earlier had a 4GB limit to the SYSLOG files. It is highly recommended to limit information recorded in this file in high volume systems, and include the LOGFAIL_PURGE option to clear data as the file grows. To disable the creation of huge system log files, add SYSLOG NONHUGE to the server configuration file. Builds after 210901 no longer have a 4GB file size limit.

History

• V11.8 and later support SQL_STATEMENTS for detailed statement logging.
• V11.8. and later support auditing SQL logon/logoff events with USER_INFO.

SYSLOG SQL_STATEMENTS Configuration Keyword

This configuration keyword logs executed SQL statements in SYSLOG:

SYSLOG SQL_STATEMENTS

A SYSLOG SQL_STATEMENTS (SYSLOG, SYSLOG) log entry is written after statement execution so it can also include the error code (if any).

The variable part of the SYSLOG entry contains statement information in JSON format similar to SQL_DEBUG LOG_STMT (SQL_DEBUG LOG_STMT, SQL_DEBUG LOG_STMT). (SQL_DEBUG LOG_STMT, SQL_DEBUG LOG_STMT)

Below is a sample showing how it is displayed by the ctalog utility:

Class = 16 (SQL)

Event = 1 (SQL statement)

Date = 09/24/2020

Time = 17:40:11

Sequence number = 37

Error code = -20005

User ID = 'admin'

Node name = 'isql'

Variable-length information:

---------------------------------------------------

{"timestamp":"Tue Sep 24 17:40:27 2020","ipaddr":"127.0.0.1","db":"CTREESQL","user":"admin","thread":29,"statement":"select * from missingtable"}

---------------------------------------------------