SET OPTION command

This command enables and dis-enables numerous administrative options.

Syntax

SET OPTION OptionType  param1 [param2]
OptionType param1 param2 Description
COMPSORT ON | OFF - Sets sort page compression (default is ON)
DATETIME [n] 'string' - Allows the user to modify the DATETIME formatting string. The default is DD-MON-RR and the maximum size for the string is 64 bytes. The user can define up to three DATETIME formatting strings. These are identified by the optional n parameter
ERROR ON | OFF - Sets internal error dumping
EXPR ON | OFF - Sets internal expression dumping
HASH ON | OFF - Sets internal hash dumping
HEAPBLOCKSIZE bytes - Allows the user to set the heap block size used in allocating memory. Larger sizes require less CPU overhead but may result in excessive memory use

The range is 0 to 1000000. Setting 0 means that the exact required size will be used

LOGFILE 'filename' - Sets the name of the debugging logfile
MERGESIZE records - Sets the number of records used to check for duplicates while processing an IN or UNION clause. The default is 1000. Increase this value if you get a "Multi RID overflow" error
OJNESTON ON | OFF - Allows nested ON clauses (default is "OFF")
OPTIMIZE ON | OFF - Allows the user to control whether or not optimization is used to evaluate statements. (Default is "ON")
PLAN ON | OFF - Sets query plan dumping
SORTPAGES totalpages mempages Allows the user to modify the amount of disk and memory storage used for sort operations. The totalpages parameter is the total number of 4096 byte pages to use, and mempages is the number of these pages kept in memory.

totalpages must be greater than or equal to mempages.

The default values are 10000 and 1000. The default values are fine for most users. If you get a message indicating that Virtual Memory has been exceeded, increase the totalpages value

TRACE ON | OFF - Sets internal trace dumping
TREE ON | OFF - Sets internal tree dumping

Use

This statement is used to set a number of database administrative options, as described in the table above.

Example

SET OPTION SORT 8000 2000
SET OPTION DATETIME DD-MM-YYYY
SET OPTION LOGFILE mylogfile
SET OPTION ERROR ON