SQL Operations Guide

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:

Offset	Debug Option
0	SQL Statements Details of how the SQL engine processes SQL statements: Original SQL statement passed by the application Decomposition of the statement by the engine parser Optimization strategy chosen by the engine optimizer sql_debug
1	Cache Logs the size of the binary trees created during processing. cache_debug
2	Data dictionary manager Logs details of the internal logic used during processing. ddm_debug
3	Execution manager Log details of runtime operations performed by the SQL engine execution manager. xec_debug
4	Optimizer Log details of runtime operations performed by the SQL engine optimizer. opt_debug
5	Remote operations Log details of remote operations. remt_debug
6	Display cost Log cost assessments calculated for each node in the SQL tree. display_cost
7	Heap manager handles Log summary information about the heap and parameter handles maintained by the heap manager. hm_hdl_debug
8	Heap manager items Log details of parameter and heap handle items. hm_itm_debug
9	Primitive heap manager Log debug information about the primitive heap manager phm_debug
10	Java debugging Log Java error and debugging information if Java Stored Procedures are enabled. This is ignored when operating without a defined Java environment. Note that activating this element activates internal logging and log() method for ANY stored procedure language, not just Java. java_debug
11	Enable generic SQL error logging mechanism. log_error
12	(In V11 and later) A subset of 10 where the log it is turned on only for the log produced by the log() method used in stored procedures to generate log messages. stp_logging

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');