Table 2-1 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 might 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 – 100 |
10 |
CACHE_PARTITIONS |
power of 2, 0 to 64 |
0 |
CHECKPOINT_TIME |
number of minutes |
60 |
CIS_ROWSET_SIZE |
integer |
50 |
CONVERSION_MODE |
0, 1 |
0 |
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 – 100000 |
200 |
DATE_FIRST_DAY_OF_WEEK |
0 – 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 – 100 |
50 |
DDL_OPTIONS2 |
0 – 3 |
0 |
DEBUG_MESSAGES |
ON, OFF |
OFF |
DEDICATED_TASK |
ON, OFF |
OFF |
DEFAULT_HAVING_SELECTIVITY |
0 – 100 |
0 |
DEFAULT_LIKE_MATCH_SELECTIVITY |
0 – 100 |
15 |
DEFAULT_LIKE_RANGE_SELECTIVITY |
0 – 100 |
15 |
DELAYED_COMMIT_TIMEOUT |
integer |
500 |
DELAYED_COMMITS |
OFF |
OFF |
DISABLE_RI_CHECK |
ON, OFF |
OFF |
DISK_STRIPING |
ON, OFF |
ON |
DISK_STRIPING_PACKED |
ON, OFF |
ON |
EARLY_PREDICATE_EXECUTION |
ON, OFF |
ON |
ESCAPE_CHARACTER |
ON, OFF |
ON |
ENABLED_ORDERED_PUSHDOWN_INSERTION |
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 – 20000 |
1024 |
FP_PREDICATE_WORKUNIT_PAGES |
integer |
400 |
FP_PREFETCH_SIZE |
0 – 100 |
10 |
GARRAY_FILL_FACTOR_PERCENT |
0 – 1000 |
25 |
GARRAY_INSERT_PREFETCH_SIZE |
0 – 100 |
3 |
GARRAY_RO_PREFETCH_SIZE |
0 – 100 |
10 |
HASH_PINNABLE_CACHE_PERCENT* |
0 – 100 |
20 |
HASH_THRASHING_PERCENT |
0 – 100 |
10 |
HG_DELETE_METHOD |
0 – 3 |
0 |
HG_SEARCH_RANGE |
integer |
10 |
IDENTITY_ENFORCE_UNIQUENESS |
ON, OFF |
OFF |
IDENTITY_INSERT |
string' |
'' (empty string) |
INDEX_ADVISOR |
ON, OFF |
OFF |
INDEX_PREFERENCE |
-10 – 10 |
0 |
INFER_SUBQUERY_PREDICATES |
ON, OFF |
OFF |
IN_SUBQUERY_PREFERENCE |
-3 – 3 |
0 |
IQGOVERN_MAX_PRIORITY |
1 – 3 |
2 |
IQGOVERN_PRIORITY |
1 – 3 |
2 |
IQGOVERN_PRIORITY_TIME |
1 – 1,000,000 seconds |
0 (disabled) |
IQMSG_LENGTH_MB |
0 – 2047 |
0 |
ISOLATION_LEVEL |
0, 1, 2, 3 |
0 |
JAVA_HEAP_SIZE |
integer |
1000000 |
JAVA_NAMESPACE_SIZE |
integer |
4000000 |
JOIN_EXPANSION_FACTOR |
0 – 100 |
30 |
JOIN_OPTIMIZATION |
ON, OFF |
ON |
JOIN_PREFERENCE |
-7 – 7 |
0 |
JOIN_SIMPLIFICATION_THRESHOLD |
1 – 64 |
15 |
LARGE_DOUBLES_ACCUMULATOR |
ON, OFF |
OFF |
LF_BITMAP_CACHE_KB |
1 – 8 |
4 |
LOAD_MEMORY_MB |
0 – 2000 |
0 |
LOCAL_KB_PER_STRIPE |
integer > 0 in KB |
1 |
LOAD_ZEROLENGTH_ASNULL |
ON, OFF |
OFF |
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 – 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 – 126 |
0 |
MAX_CLIENT_NUMERIC_SCALE |
0 – 126 |
0 |
MAX_CUBE_RESULT |
0 – 250000000 |
10000000 |
MAX_CURSOR_COUNT |
integer |
50 |
MAX_HASH_ROWS |
integer to 250000000 |
2500000 |
MAX_IQ_THREADS_PER_CONNECTION |
3 – 1000 |
72 |
MAX_IQ_THREADS_PER_TEAM |
1 – 1000 |
48 |
MAX_JOIN_ENUMERATION |
1 – 64 |
15 |
MAX_QUERY_PARALLELISM |
integer <= # CPUs |
24 |
MAX_QUERY_TIME |
0 – 232 - 1 |
0 (disabled) |
MAX_STATEMENT_COUNT |
integer |
100 |
MAX_WARNINGS |
integer |
264 - 1 |
MINIMIZE_STORAGE |
ON, OFF |
OFF |
MIN_NLPDJ_FILTERED_PPM |
1 – 1000000 |
2500 |
MIN_NLPDJ_TABLE_SIZE |
1 – 4294967295 |
10000 |
MIN_PASSWORD_LENGTH |
integer >= 0 |
0 characters |
MIN_SMPDJ_OR_HPDJ_FILTERED_PPM |
1 – 1000000 |
2500 |
MIN_SMPDJ_OR_HPDJ_FILTERED_SIZE |
1 – 4294967295 |
25000 |
MIN_SMPDJ_OR_HPDJ_INDIRECT_SIZE |
1 – 4294967295 |
500000 |
MIN_SMPDJ_OR_HPDJ_TABLE_SIZE |
1 – 4294967295 |
100000 |
MONITOR_OUTPUT_DIRECTORY |
string |
database directory |
MPX_GLOBAL_TABLE_PRIV |
ON, OFF |
OFF |
MPX_LOCAL_SPEC_PRIV |
0 to 63 |
0 |
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 – 4294967295 |
3000000 |
PARALLEL_GBH_UNITS |
0 – 100 |
0 |
PRECISION |
126 |
126 |
PREFETCH |
ON, OFF |
ON |
PREFETCH_BUFFER_LIMIT |
integer |
0 |
PREFETCH_BUFFER_PERCENT |
0 – 100 |
40 |
PREFETCH_GARRAY_PERCENT |
0 – 100 |
60 |
PREFETCH_SORT_PERCENT |
0 – 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 – 126 |
38 |
SIGNIFICANTDIGITSFORDOUBLEEQUALITY |
0 – 15 |
0 |
SORT_PHASE1_HELPERS |
integer |
3 |
SORT_PINNABLE_CACHE_PERCENT* |
0 – 100 |
20 |
SUBQUERY_PLACEMENT_PREFERENCE |
-1 – 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 – 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: & Linux 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 |
VERIFY_PASSWORD_FUNCTION |
string |
'' (empty string) |
WASH_AREA_BUFFERS_PERCENT |
1 – 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 Table 2-1 (TEMP_EXTRACT_...) are used to control this feature. For details on the use of these options, see Data extraction options in Chapter 7, “Moving Data In and Out of Databases” in the Sybase IQ System Administration Guide.
