DB SQL Features

Advanced Encryption for FairCom DB SQL Tables

FairCom DB SQL supports the ability to encrypt tables from the CREATE TABLE command. Advanced encryption, including standard AES (Rijndael), Twofish, Blowfish, and DES, is available for industry-standard hardening of data. The AES encryption standard may be required with some forms of data, such as that used in the health care industry (for example, to conform to U.S. HIPAA regulations) and the financial industry.

The following encryption options can be used with the STORAGE_ATTRIBUTES keyword:

STORAGE_ATTRIBUTES 'ENCR=crypt'

crypt can be one of the following:

• AES16 (Rijndael)
• AES24
• AES32
• DES8
• DES16
• DES24
• BLF8 through BLF56 (Blowfish)
• TWF16 (Twofish)
• TWF24
• TWF32

Example

CREATE TABLE encrypted_table (name CHAR(10), age SMALLINT) STORAGE_ATTRIBUTES 'ENCR=AES32' ;

To combine STORAGE_ATTRIBUTE options, separate them with a semicolon (;). For example, to create a non-HUGE, AES32 encrypted table, you would issue a statement such as the following:

CREATE TABLE small_camo_table (name CHAR(10), age SMALLINT) STORAGE_ATTRIBUTES 'ENCR=AES32;NOHUGE' ;

FairCom DB SQL must have the ADVANCED_ENCRYPTION configuration keyword specified in the ctsrvr.cfg configuration file. The table creation will fail with FairCom DB SQL error -17454 (feature not supported) if you request an encryption option without this server configuration option enabled.

Using an encryption mode not listed above will also result in the FairCom DB SQL error -17454 (not supported).

Default HUGE File Tables with FairCom DB SQL

HUGE files (c-tree files larger than four gigabytes) are frequently encountered with today’s massive storage needs. To accommodate these increased table sizes, FairCom DB SQL creates files as HUGE by default. (Prior to FairCom DB SQL V9, new tables were created as standard non-HUGE c-tree files.) An optional STORAGE_ATTRIBUTES clause containing 'HUGE' was available to create FairCom DB SQL HUGE tables.

Reversing the previous behavior, the STORAGE_ATTRIBUTES clause supports a NOT HUGE option to create standard size c-tree tables.

For backward compatibility, it is possible to revert to the original FairCom DB SQL behavior and set theFairCom DB SQL default to ‘NOT HUGE’ by adding the following configuration keyword to ctsrvr.cfg:

SQL_OPTION NO_HUGEFILE

The STORAGE_ATTRIBUTES clause will override any default configuration settings. Whether a table is created HUGE or NOT HUGE with respect to the ctsrvr.cfg configuration and the STORAGE_ATTRIBUTES statement clause is summarized in the following table:

Example

CREATE TABLE table1 (column1 INT, column2 CHAR(10)) STORAGE_ATTRIBUTES 'NOHUGE'

CREATE TABLE table2 (column1 MONEY, column2 VARCHAR(20)) STORAGE_ATTRIBUTES 'HUGE'
 

To combine STORAGE_ATTRIBUTE options, separate them with a semicolon (;).

Note: HUGE file support is not available for the Windows CE version of FairCom DB SQL.

PREIMAGE Tables in FairCom DB SQL

A significant performance gain can be obtained in some situations by avoiding transaction logging of files, thereby foregoing the protection of recovery. With FairCom DB it is possible to maintain atomicity of transactions in this state with a transaction mode of ctPREIMG. This support is also extended to FairCom DB SQL Tables when they are created.

An additional option has been added to theFairCom DB SQL keyword STORAGE_ATTRIBUTES.

STORAGE_ATTRIUBUTES 'PREIMG'

This option disables transaction logging for the table. This can be useful for temporary tables that are not required to be recovered in the event of catastrophic failure, yet retain atomicity of transactions.

To combine STORAGE_ATTRIBUTE options, separate them with a semicolon (;). For example, to create a non-HUGE, PREIMAGE table, you would issue a statement such as the following.

non-HUGE PREIMAGE Example

CREATE TABLE small_preimage_table (name CHAR(10), age SMALLINT) STORAGE_ATTRIBUTES 'NONHUGE;PREIMG'

Shared Memory Connections for FairCom DB SQL Clients on Windows

FairCom DB SQL for the Microsoft Windows OS supports a shared memory communication protocol for FairCom DB SQL clients. This communications option is the default for clients connecting locally, gaining a huge performance boost over the standard TCP/IP connections. A configuration option is available to disable this support if desired. See SQL_OPTION NO_SHARED_MEMORY.

Server-Side Connection Pooling

Connection pooling is a mechanism for pooling existing connections so they can be reused by subsequent clients. The connection and disconnection process adds a substantial amount of overhead to a SQL client performing a transaction. FairCom DB SQL offers options to enable connection pooling. This reuse of existing connections can provide significant increases in performance with your existing FairCom DB SQL ODBC and JDBC client applications.

SETENV DH_ENABLE_POOL=y enable the connection pooling

SETENV DH_POOL_SIZE=#n #n of pooled connections. ;1<#n<=100

These performance gains can be demonstrated in the simple test case of a client connecting and disconnecting repeatedly with a small amount of work performed during each connection. In one example, the client gained a 100% reduction in total application time. This feature offers potentially enormous gains in performance for applications with clients that perform repeated connects and disconnects.

Supported Transaction Isolation Levels

FairCom DB SQL supports both SQL transaction isolation levels 1 and 2. Transaction isolation levels provide defined levels as to which transaction isolation succeeds. Four transaction isolation levels are defined (SQL-92) by several phenomena. These phenomena are defined as follows:

• Dirty Reads. A dirty read occurs when a transaction reads data that is not yet committed by another transaction.
• Non-repeatable Reads. A non-repeatable read is when a transaction reads data from the same row twice, and gets different results, usually as the result of updates from another, simultaneous transaction.
• Phantoms. A phantom is a row that matches some search criteria, and is not initially seen, as adds or deletes from another transaction have changed the result set available. Upon a second attempt, the transaction is returned a different set of rows.

The SQL-92 defined isolation levels are shown in the following table. An ‘X’ identifies an included phenomenon; ‘--’ indicates absence.

Keep in mind that a transaction isolation level only affects data changes between transactions. Changes within one’s own transaction are always available and will be seen.

The FairCom DB SQL configuration keyword MAX_SQL_ISOLATION_LEVEL sets the maximum transaction isolation level at which FairCom DB SQL clients can operate. The supported values are:

MAX_SQL_ISOLATION_LEVEL 1; READ_COMMITTED only

MAX_SQL_ISOLATION_LEVEL 2; READ_COMMITTED and REPEATABLE_READ

The FairCom DB SQL default without this configuration keyword is a maximum isolation level of 1. Transaction isolation levels 0 (READ_UNCOMMITTED) and 3 (SERIALIZABLE) are not available with FairCom DB SQL at this time.

If a FairCom DB SQL client requests an isolation level that exceeds the default maximum isolation level, or a maximum level specified in the server configuration file, the FairCom DB SQL client only operates at the maximum isolation level of FairCom DB SQL rather than the requested isolation level.

A FairCom DB SQL ODBC client can request a specific transaction isolation level to work in, provided the server supports this level. The SQL_ATTR_TXN_ISOLATION connection attribute is used to request the level with the SQLSetConnectAttr() ODBC function call.

ODBC Example

/* After a connection handle is established... */

if (SQL_ERROR == (SQLSetConnectAttr(connection_hdl,
SQL_ATTR_TXN_ISOLATION, SQL_TXN_REPEATABLE_READ, 0)) )

printf(“Failed to set the Transaction Isolation Level\n”);

The client application can also determine the transaction isolation level in effect with the SQL_TXN_ISOLATION_OPTION and SQL_DEFAULT_TXN_ISOLATION options with the SQLGetInfo() function call.

ODBC Example

/* After a connection handle is established... */

if (SQL_ERROR == (SQLGetInfo(connection_hdl, SQL_TXN_ISOLATION_OPTION, buf,
MAX_BUF, &buf_length)))

do_error(“Failed to Retrieve Current Transaction Isolation Level\n”);
 

if (SQL_ERROR == (SQLGetInfo(connection_hdl, SQL_DEFAULT_TXN_ISOLATION, buf,
MAX_BUF, &buf_length)))

printf(“Failed to Retrieve Default Transaction Isolation Level\n”);

Requesting transaction isolation level 2, REPEATABLE_READ can introduce delays as additional overhead is involved in acquiring blocking read locks to ensure repeatable reads.

Case Insensitive Search Options for FairCom DB SQL LONG Field Types

A limitation ofFairCom DB SQL LVARCHAR and LVARBINARY fields is the inability to use SQL functions such as UPPER() or LOWER() to perform case insensitive searches. To address this disadvantage of these field types, a proprietary FairCom DB SQL options clause is available to allow a case insensitive search. The ctoption(icontains) clause will allow a case insensitive search on a specific CONTAINS query. Include this clause on a query similar to the following:

SELECT * FROM mytable WHERE bigfield CONTAINS 'Search Phrase' ctoption(icontains)

The option is only valid for the current query. ctoption(icontains) can be used with both FairCom DB SQL LONG fields allowing both character and binary searching.

Case sensitivity in SQL databases

A FairCom SQL database is case-sensitive by default. A case-sensitive database can safely store and retrieve UTF-8 characters; thus, FairCom recommends you create case-sensitive SQL databases.

Case sensitivity applies to string comparisons in queries, expressions, values stored in indexes, and identifiers.

Although the SQL standard provides functions and syntax for case-insensitive string comparisons in a case-sensitive database, using a function for case-insensitive comparisons can significantly slow down a SQL query because the engine will not use an index on the field. This limitation applies if you use a function over a field on the right or left side of a comparison, in an ORDER BY, or in a JOIN clause.

If your application only uses ASCII characters and wants all string comparisons to be case-insensitive, you may want to create a case-insensitive SQL database.

Consequences of creating a case-insensitive SQL database

You can create a case-insensitive SQL database if you exclusively use case-insensitive ASCII characters. A case-insensitive database irreversibly changes how it stores some string characters:

• The SQL database stores string identifiers in SQL dictionaries as lowercase characters unless the database is case-sensitive and you enclose the identifier in double quotes, such as "my_TABLE".
• The SQL database stores string values in indexes as uppercase.
• The SQL database performs all string comparisons in expressions and queries using case-insensitivity.
• You cannot change the case sensitivity of a SQL database after you create it.
• NOTE: You cannot use UTF-8 characters because the database changes case using ASCII rules rather than UTF-8 ones.

How to create a case-insensitive SQL database

You change the server configuration keyword to SQL_OPTION DB_CASE_INSENSITIVE to cause the server to create all new SQL databases as case-insensitive. The server configuration keyword applies to all newly created SQL databases. It cannot change existing databases.

The ctsqlcdb command-line utility can create a case-insensitive database; it cannot change an existing database.

The fc_createdb stored procedure allows you to create a case-insensitive database; it cannot change an existing one.

How does case sensitivity affect SQL identifiers?

Following the SQL standard, a case-sensitive SQL database treats all identifiers as case-insensitive unless you enclose them in double quotes. For example, myTABLE equals mytable, and "myTABLE" does not equal mytable. Thus, your SQL statements can choose to be case-sensitive or case-insensitive.

A case-insensitive SQL database treats all identifiers as case-insensitive, even when you enclose them in double quotes. For example, myTABLE equals mytable, and "myTABLE" equals myTABLE. Thus, your SQL statements are always case-insensitive.

How does case sensitivity interact with the JSON, CTDB, and ISAM APIs?

If you use a case-insensitive SQL database to create indexes, they will store string field values as uppercase. Other APIs give you the choice in the case sensitivity of indexes. You must ensure that indexes created outside of SQL are compatible with SQL.

The SQL dictionary for a case-insensitive SQL database stores identifiers in lowercase. The NoSQL APIs store identifiers as case-sensitive. If your code works with SQL and NoSQL APIs, you must be aware of this difference.

Query Timeout Option

FairCom DB SQL supports a timeout option for an executing query. This feature can ensure that an unintended query statement does not consume excessive processing time for FairCom DB SQL.

Examples

With FairCom DB SQL ODBC you can set the query timeout value for the statement with the SQLSetStmtAttr() FairCom DB SQL ODBC API function and the SQL_ATTR_QUERY_TIMEOUT parameter set to the number of seconds to wait for the query to execute before returning to the application. A value of 0 indicates no timeout value, which is also the default. The following example code will set a query timeout value of five seconds for the referenced statement handle.

ODBC Example

/* Set the Query timeout to 5 seconds */

SQLSetStmtAttr(hstmt, (void*)SQL_ATTR_QUERY_TIMEOUT, 5, 0);

In Java with the FairCom DB SQL JDBC Driver, you can use the setQueryTimeout() method of the java.sql.Statement interface as shown here.

JDBC Example

Class.forName (“ctree.jdbc.ctreeDriver”);

Connection myConnection = DriverManager.getConnection(“jdbc:ctree:6597@localhost:ctreeSQL”, ADMIN, ADMIN”);

Statement myStatement = myConnection.createStatement();
 

String query = “SELECT TOP 50000 FROM my_big_table WHERE this < that AND this_string = 'that_string' ORDER BY foo”

myStatement:setQueryTimeout(5);

myStatement.executeUpdate(query);

Using ADO .NET with the FairCom DB SQL Data Provider, you specify the query timeout with the CommandTimeout property of the CtreeSQLCommand component.

ADO .NET Example

myCommand = new CtreeSqlCommand(““, this.myConnection);

myCommand.CommandText = “SELECT TOP 50000 FROM my_big_table WHERE this < that AND this_string = 'that_string' ORDER BY foo”;
 

myCommand.CommandTimeout = 5;

try {

myCommand.ExecuteReader();

}

catch (CtreeSQLException ex) {

//Log some error.

}

Using ODBC through ADO.NET, you can specify the OdbcConnection.CommandTimeout property to set a query timeout value on an ODBC statement as demonstrated with the following syntax.

FairCom DB SQL ODBC via ADO .NET Example

OdbcConnection myConnection = new OdbcConnection();

myConnection.ConnectionString = "DSN=c-treeSQL ODBC Database";

myConnection.Open();
 

OdbcCommand oc = new OdbcCommand("SELECT TOP 50000 FROM my_big_table WHERE this < that AND this_string = 'that_string' ORDER BY foo”, myConnection);
 

// Set a query timeout of 5 seconds.

oc.CommandTimeout = 5;
 

try

{

oc.ExecuteReader();

}

catch (Exception ex)

{

// Log some error

}

Logging FairCom DB SQL Query Times

A FairCom DB SQL feature is available to log query start and end times as an additional aid for tuning and performance monitoring. The query times are output to the file sql_server.log.

The output is controlled by two server configuration keywords and specified in the file ctsrvr.cfg.

SQL_DEBUG LOG_STMT_TIMES

This option logs start and end times for the statement PREPARE, EXECUTE and OPEN operations.

SQL_DEBUG LOG_STMT_TIMES_FETCH

This option logs start and end times of a fetch sequence (open cursor, all fetches, and close cursor).

Both options can be specified at the same time. The actual log entry occurs when the query operation is finished.

Example Output

Start: Thu Mar 09 13:50:12 2006

- User# 00014 Elapsed: 0 sec. for PREPARE select top 5000 * from facility

Start: Thu Mar 09 13:50:12 2006

- User# 00014 Elapsed: 0 sec. for OPEN select top 5000 * from facility

Start: Thu Mar 09 13:50:12 2006

- User# 00014 Elapsed: 2 sec. for FETCH select top 5000 * from facility

Built-in Stored Procedure to Switch Transaction Mode

It can be useful for performance reasons to avoid transaction memory usage in some common maintenance operations. Consider the case of upgrading a large database where some tables are added with a SQL query from another table that returns a large number of rows. As this occurs inside a single SQL statement the transaction consumes memory until committed. For large files, this can potentially exhaust memory on some systems and result in complete failure.

FairCom DB can allow the transaction processing mode of a file to be disabled which avoids consuming memory for a very large operation. To make this available to FairCom DB SQL, a built-in stored procedure allowing the transaction mode of a file to be changed has been added. This operation requires exclusive file access.

fc_set_file_tran_state(VARCHAR owner, VARCHAR tablename, TINYINT mode)

Valid values for mode are

• 0 : No transaction control.
• 1 : Transaction control without recoverability. (ctPREIMG)
• 2 : Transaction control and recoverability. (ctTRNLOG)

When a FairCom DB SQL file is set to mode of 0 (no transaction control), all changes to the file will have immediate and permanent effect, such that a ROLLBACK operation will have no effect on this file. If another user is able to open the file, isolation levels are undefined. Furthermore, FairCom DB SQL operations that modify multiple rows of this table will not be atomic in the event of an error.

For update statements, it is important to ensure no errors are encountered during execution. For example, a statement such as

UPDATE tbl SET col=99

that encounters an error midway through the update, will leave the state of the table undefined and the operation should be re-examined. It is possible it will be necessary to restore from a backup to return to a previous state.

Because of these effects, the intended use of this mode is limited in scope. No file definition/schema changes to this file should occur while in this mode. All previous schema changes should be committed before calling this procedure. If an error occurs while operating on a file with a mode 0, the table should be deleted and the transaction rolled back.

Note: The stored procedure fc_check_file_tran_state() can be used to ensure that no files remain in modes other than the database default.