The following table lists database-specific options, their allowed values, and their default settings.
See the sections “Transact-SQL compatibility options” and “DBISQL options” for lists of the other classes of options.
There are additional internal options (not listed in
this table) that Sybase Technical Support may ask you to use.
OPTION |
VALUES |
DEFAULT |
|---|---|---|
AGGREGATION_PREFERENCE |
-3 to 3 |
0 |
APPEND_LOAD |
ON, OFF |
OFF |
AUDITING |
ON, OFF |
OFF |
BIT_VECTOR_PINNABLE_CACHE_PERCENT* |
0-100 |
40 |
BLOCKING |
OFF |
OFF |
BT_PREFETCH_MAX_MISS |
0 - 1000 |
2 |
BT_PREFETCH_SIZE |
0 to 100 |
10 |
CACHE_PARTITIONS |
power of 2, 0 to 64 |
0 |
CHECKPOINT_TIME |
number of minutes |
60 |
CIS_ROWSET_SIZE |
integer |
50 |
CONVERT_HG_TO_1242 |
ON, OFF |
OFF |
CONVERT_VARCHAR_TO_1242 |
ON, OFF |
OFF |
COOPERATIVE_COMMIT_TIMEOUT |
integer |
250 |
COOPERATIVE_COMMITS |
ON, OFF |
ON |
CURSOR_WINDOW_ROWS |
20 to 100000 |
200 |
DATE_FIRST_DAY_OF_WEEK |
0 to 6 |
0 |
DATE_FORMAT |
string |
'YYYY-MM-DD' |
DATE_ORDER |
'YMD', 'DMY', 'MDY' |
'YMD' |
DBCC_LOG_PROGRESS |
ON, OFF |
OFF |
DBCC_PINNABLE_CACHE_PERCENT |
0 to 100 |
50 |
DDL_OPTIONS2 |
0 to 3 |
0 |
DEBUG_MESSAGES |
ON, OFF |
OFF |
DEDICATED_TASK |
ON, OFF |
OFF |
DEFAULT_LIKE_MATCH_SELECTIVITY |
0 to 100 |
15 |
DEFAULT_LIKE_RANGE_SELECTIVITY |
0 to 100 |
15 |
DELAYED_COMMIT_TIMEOUT |
integer |
500 |
DELAYED_COMMITS |
OFF |
OFF |
DISABLE_RI_CHECK |
ON, OFF |
OFF |
DISK_STRIPING |
ON, OFF |
ON |
EARLY_PREDICATE_EXECUTION |
ON, OFF |
ON |
EXTENDED_JOIN_SYNTAX |
ON, OFF |
ON |
FLATTEN_SUBQUERIES |
ON, OFF |
OFF |
FORCE_DROP |
ON, OFF |
OFF |
FORCE_NO_SCROLL_CURSORS |
ON, OFF |
OFF |
FORCE_UPDATABLE_CURSORS |
ON, OFF |
OFF |
FPL_EXPRESSION_MEMORY_KB |
0 to 20000 |
1024 |
FP_PREDICATE_WORKUNIT_PAGES |
integer |
400 |
FP_PREFETCH_SIZE |
0 to 100 |
10 |
GARRAY_FILL_FACTOR_PERCENT |
0 to 1000 |
25 |
GARRAY_INSERT_PREFETCH_SIZE |
0 to 100 |
3 |
GARRAY_RO_PREFETCH_SIZE |
0 to 100 |
10 |
HASH_PINNABLE_CACHE_PERCENT* |
0 to 100 |
20 |
HASH_THRASHING_PERCENT |
0 to 100 |
10 |
HG_DELETE_METHOD |
0 to 3 |
0 |
HG_SEARCH_RANGE |
integer |
10 |
IDENTITY_ENFORCE_UNIQUENESS |
ON, OFF |
OFF |
IDENTITY_INSERT |
= 'tablename' |
= ' ' |
INDEX_ADVISOR |
ON, OFF |
OFF |
INDEX_PREFERENCE |
-10 to 10 |
0 |
INFER_SUBQUERY_PREDICATES |
ON, OFF |
OFF |
IN_SUBQUERY_PREFERENCE |
-3 to 3 |
0 |
IQGOVERN_MAX_PRIORITY |
1 to 3 |
2 |
IQGOVERN_PRIORITY |
1 to 3 |
2 |
IQGOVERN_PRIORITY_TIME |
1 to 1,000,000 seconds |
0 (disabled) |
IQMSG_LENGTH_MB |
0 to 2047 |
0 |
ISOLATION_LEVEL |
0, 1, 2, 3 |
0 |
JAVA_HEAP_SIZE |
integer |
1000000 |
JAVA_NAMESPACE_SIZE |
integer |
4000000 |
JOIN_EXPANSION_FACTOR |
0 to 100 |
30 |
JOIN_OPTIMIZATION |
ON, OFF |
ON |
JOIN_PREFERENCE |
-7 to 7 |
0 |
JOIN_SIMPLIFICATION_THRESHOLD |
1 to 64 |
15 |
LARGE_DOUBLES_ACCUMULATOR |
ON, OFF |
OFF |
LF_BITMAP_CACHE_KB |
1 to 8 |
4 |
LOAD_MEMORY_MB |
0 to 2000 |
0 |
LOCAL_RESERVED_DBSPACE_MB |
integer > 0 in MB |
200 |
LOG_CONNECT |
ON, OFF |
ON |
LOG_CURSOR_OPERATIONS |
ON, OFF |
OFF |
LOGIN_MODE |
STANDARD, MIXED, INTEGRATED |
STANDARD |
LOGIN_PROCEDURE |
string |
sp_iq_process_login |
MAIN_CACHE_MEMORY_MB |
1 to 4194303 |
16 |
MAIN_KB_PER_STRIPE |
integer > 0 in KB |
1 |
MAIN_RESERVED_DBSPACE_MB |
integer > 0 in MB |
200 |
MAX_CARTESIAN_RESULT |
integer |
10000000 |
MAX_CLIENT_NUMERIC_PRECISION |
0 to 126 |
0 |
MAX_CLIENT_NUMERIC_SCALE |
0 to 126 |
0 |
MAX_CUBE_RESULT |
0 to 250000000 |
10000000 |
MAX_CURSOR_COUNT |
integer |
50 |
MAX_HASH_ROWS |
integer to 250000000 |
2500000 |
MAX_IQ_THREADS_PER_CONNECTION |
2 to 1000 |
72 |
MAX_IQ_THREADS_PER_TEAM |
1 to 1000 |
48 |
MAX_JOIN_ENUMERATION |
1 to 64 |
15 |
MAX_QUERY_PARALLELISM |
integer <= # CPUs |
24 |
MAX_QUERY_TIME |
0 to 232 - 1 |
0 (disabled) |
MAX_STATEMENT_COUNT |
integer |
100 |
MAX_WARNINGS |
integer |
264 - 1 |
MINIMIZE_STORAGE |
ON, OFF |
OFF |
MIN_NLPDJ_TABLE_SIZE |
1 to 4294967295 |
10000 |
MIN_PASSWORD_LENGTH |
integer >= 0 |
0 characters |
MIN_SMPDJ_OR_HPDJ_FILTERED_SIZE |
1 to 4294967295 |
25000 |
MIN_SMPDJ_OR_HPDJ_INDIRECT_SIZE |
1 to 4294967295 |
500000 |
MIN_SMPDJ_OR_HPDJ_TABLE_SIZE |
1 to 4294967295 |
100000 |
MONITOR_OUTPUT_DIRECTORY |
string |
database directory |
NOEXEC |
ON, OFF |
OFF |
NON_ANSI_NULL_VARCHAR |
ON, OFF |
OFF |
NOTIFY_MODULUS |
integer |
100000 |
ODBC_DISTINGUISH_CHAR_AND_VARCHAR |
ON, OFF |
OFF |
ON_CHARSET_CONVERSION_FAILURE |
string |
IGNORE |
OS_FILE_CACHE_BUFFERING |
ON, OFF |
OFF |
OUT_OF_DISK_MESSAGE_REPEAT |
integer |
120 |
OUT_OF_DISK_WAIT_TIME |
integer |
30 |
PARALLEL_GBH_ENABLED |
ON, OFF |
ON |
PARALLEL_GBH_MIN_ROWS_PER_UNIT |
0 to 4294967295 |
3000000 |
PARALLEL_GBH_UNITS |
0 to 100 |
0 |
PRECISION |
126 |
126 |
PREFETCH |
ON, OFF |
ON |
PREFETCH_BUFFER_LIMIT |
integer |
0 |
PREFETCH_BUFFER_PERCENT |
0 to 100 |
40 |
PREFETCH_GARRAY_PERCENT |
0 to 100 |
60 |
PREFETCH_SORT_PERCENT |
0 to 100 |
50 |
PRESERVE_SOURCE_FORMAT |
ON, OFF |
ON |
QUERY_DETAIL |
ON, OFF |
OFF |
QUERY_NAME |
string |
empty string |
QUERY_PLAN |
ON, OFF |
ON |
QUERY_PLAN_AFTER_RUN |
ON, OFF |
OFF |
QUERY_PLAN_AS_HTML |
ON, OFF |
OFF |
QUERY_PLAN_AS_HTML_DIRECTORY |
string |
'' (empty string) |
QUERY_ROWS_RETURNED_LIMIT |
integer |
0 |
QUERY_TEMP_SPACE_LIMIT |
integer |
2000 |
QUERY_TIMING |
ON, OFF |
OFF |
RECOVERY_TIME |
number of minutes |
2 |
RETURN_DATE_TIME_AS_STRING |
ON, OFF |
OFF |
ROW_COUNT |
integer |
0 |
SCALE |
0 to 126 |
38 |
SIGNIFICANTDIGITSFORDOUBLEEQUALITY |
0 to 15 |
0 |
SORT_PHASE1_HELPERS |
integer |
3 |
SORT_PINNABLE_CACHE_PERCENT* |
0 to 100 |
20 |
SUBQUERY_PLACEMENT_PREFERENCE option |
-1 to 1 |
0 |
SUPPRESS_TDS_DEBUGGING |
ON, OFF |
OFF |
SWEEPER_THREADS_PERCENT |
1 to 40 |
10 |
TDS_EMPTY_STRING_IS_NULL |
ON, OFF |
OFF |
TEMP_CACHE_MEMORY_MB |
1 to 4194303 |
12 |
TEMP_DISK_PER_STRIPE |
integer > 0 in KB |
1 |
TEMP_EXTRACT_APPEND |
ON, OFF |
OFF |
TEMP_EXTRACT_BINARY |
ON, OFF |
OFF |
TEMP_EXTRACT_COLUMN_DELIMITER |
string |
',' |
TEMP_EXTRACT_DIRECTORY |
string |
'' (empty string) |
TEMP_EXTRACT_NAME1 - TEMP_EXTRACT_NAME8 |
string |
'' (empty string) |
TEMP_EXTRACT_NULL_AS_EMPTY |
ON, OFF |
OFF |
TEMP_EXTRACT_NULL_AS_ZERO |
ON, OFF |
OFF |
TEMP_EXTRACT_QUOTE |
string |
'' (empty string) |
TEMP_EXTRACT_QUOTES |
ON, OFF |
OFF |
TEMP_EXTRACT_QUOTES_ALL |
ON, OFF |
OFF |
TEMP_EXTRACT_ROW_DELIMITER |
string |
'' (empty string) |
TEMP_EXTRACT_SIZE1 - TEMP_EXTRACT_SIZE8 |
AIX & HP-UX: 0 - 64GB Sun Solaris: 0 - 512GB Windows: 0 - 128GB |
0 |
TEMP_EXTRACT_SWAP |
ON, OFF |
OFF |
TEMP_KB_PER_STRIPE |
integer > 0 in KB |
1 |
TEMP_RESERVED_DBSPACE_MB |
integer > 0 in MB |
200 |
TEMP_SPACE_LIMIT_CHECK |
ON, OFF |
OFF |
TIME_FORMAT |
string |
'HH:NN:ss.SSS' |
TIMESTAMP_FORMAT |
string |
'YYYY- MM-DD HH:NN:ss.SSS' |
TRIM_PARTIAL_MBC |
ON, OFF |
OFF |
TRUNCATE_WITH_AUTO_COMMIT |
ON, OFF |
ON |
USER_RESOURCE_RESERVATION |
integer |
1 |
WASH_AREA_BUFFERS_PERCENT |
1 to 100 |
20 |
WAIT_FOR_COMMIT |
ON, OFF |
OFF |
The data extraction facility allows you to extract data from a database by redirecting the output of a SELECT statement from the standard interface to one or more disk files or named pipes. Several database options listed in the table above (TEMP_EXTRACT_...) are used to control this feature. For details on the use of these options, see the section “Data extraction options” in Chapter 7, “Moving Data In and Out of Databases” in the Sybase IQ System Administration Guide.
