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 |
ALLOW_READ_CLIENT_FILE |
ON, OFF |
OFF |
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 |
BTREE_PAGE_SPLIT_PAD_PERCENT |
0 - 90 |
50 |
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_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 |
DEBUG_MESSAGES |
ON, OFF |
OFF |
DEFAULT_DBSPACE |
string |
'' (empty string) |
DEFAULT_DISK_STRIPING |
ON, OFF |
ON |
DEDICATED_TASK |
ON, OFF |
OFF |
DEFAULT_HAVING_SELECTIVITY_PPM |
0 – 1000000 |
0 |
DEFAULT_KB_PER_STRIPE |
1 – max unsigned bigint |
1 |
DEFAULT_LIKE_MATCH_SELECTIVITY_PPM |
0 – 1000000 |
150000 |
DEFAULT_LIKE_RANGE_SELECTIVITY_PPM |
1 – 1000000 |
150000 |
DELAYED_COMMIT_TIMEOUT |
integer |
500 |
DELAYED_COMMITS |
OFF |
OFF |
DISABLE_RI_CHECK |
ON, OFF |
OFF |
EARLY_PREDICATE_EXECUTION |
ON, OFF |
ON |
ENABLE_LOB_VARIABLES |
ON, OFF |
OFF |
EXTENDED_JOIN_SYNTAX |
ON, OFF |
ON |
FORCE_DROP |
ON, OFF |
OFF |
FORCE_NO_SCROLL_CURSORS |
ON, OFF |
OFF |
FORCE_UPDATABLE_CURSORS |
ON, OFF |
OFF |
FP_LOOKUP_SIZE |
1 MB – 4096 MB |
16 MB |
FP_LOOKUP_SIZE_PPM |
1 – 1000000 |
2500 |
FP_PREDICATE_WORKUNIT_PAGES |
integer |
200 |
FP_PREFETCH_SIZE |
0 – 100 |
10 |
FPL_EXPRESSION_MEMORY_KB |
0 – 20000 |
1024 |
GARRAY_FILL_FACTOR_PERCENT |
0 – 1000 |
25 |
GARRAY_INSERT_PREFETCH_SIZE |
0 – 100 |
3 |
GARRAY_PAGE_SPLIT_PAD_PERCENT |
0-100 |
25 |
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 |
HTTP_SESSION_TIMEOUT |
integer (1 – 525600) |
30 |
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 |
ON |
IN_SUBQUERY_PREFERENCE |
-3 – 3 |
0 |
IQGOVERN_MAX_PRIORITY |
1 – 3 |
2 |
IQGOVERN_PRIORITY |
1 – 3 |
2 |
IQGOVERN_PRIORITY_TIME |
1 – 1000000 seconds |
0 (disabled) |
ISOLATION_LEVEL |
0, 1, 2, 3 |
0 |
JAVA_LOCATION |
string |
'' (empty string) |
JAVA_VM_OPTIONS |
string |
'' (empty string) |
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_ZEROLENGTH_ASNULL |
ON, OFF |
OFF |
LOCKED |
ON, OFF |
OFF |
LOG_CONNECT |
ON, OFF |
ON |
LOG_CURSOR_OPERATIONS |
ON, OFF |
OFF |
LOGIN_MODE |
STANDARD, MIXED, INTEGRATED |
STANDARD |
LOGIN_PROCEDURE |
string |
sp_login_environment |
MAIN_RESERVED_DBSPACE_MB |
integer >= 200 in MB |
200 |
MAX_CARTESIAN_RESULT |
integer |
100000000 |
MAX_CLIENT_NUMERIC_PRECISION |
0 – 126 |
0 |
MAX_CLIENT_NUMERIC_SCALE |
0 – 126 |
0 |
MAX_CONNECTIONS |
0 - 2147483647 |
Unlimited |
MAX_CUBE_RESULT |
0 – 4294967295 |
10000000 |
MAX_CURSOR_COUNT |
integer |
50 |
MAX_DAYS_SINCE_LOGIN |
0 - 2147483647 |
Unlimited |
MAX_FAILED_LOGIN_ATTEMPTS |
0 - 2147483647 |
Unlimited |
MAX_HASH_ROWS |
integer to 4294967295 |
2500000 |
MAX_IQ_THREADS_PER_CONNECTION |
3 – 10000 |
144 |
MAX_IQ_THREADS_PER_TEAM |
1 – 10000 |
144 |
MAX_JOIN_ENUMERATION |
1 – 64 |
15 |
MAX_NON_DBA_CONNECTIONS |
0 – 2147483647 |
Unlimited |
MAX_PREFIX_PER_CONTAINS_PHRASE |
0 – 300 |
1 |
MAX_QUERY_PARALLELISM |
integer |
64 |
MAX_QUERY_TIME |
0 – 232 - 1 |
0 (disabled) |
MAX_STATEMENT_COUNT |
integer |
100 |
MAX_TEMP_SPACE_PER_CONNECTION |
integer |
0 |
MAX_WARNINGS |
integer |
248 - 1 |
MINIMIZE_STORAGE |
ON, OFF |
OFF |
MIN_PASSWORD_LENGTH |
integer >= 0 |
0 characters |
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 |
PASSWORD_GRACE_TIME |
0 – 2147483647 |
0 |
PASSWORD_EXPIRY_ON_NEXT_LOGIN |
ON, OFF |
OFF |
PASSWORD_LIFE_TIME |
0 – 2147483647 |
Unlimited |
POST_LOGIN_PROCEDURE |
string |
dbo.sa_post_login_procedure |
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 |
20 |
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_PLAN_TEXT_ACCESS |
ON, OFF |
OFF |
QUERY_PLAN_TEXT_CACHING |
ON, OFF |
OFF |
QUERY_ROWS_RETURNED_LIMIT |
integer |
0 |
QUERY_TEMP_SPACE_LIMIT |
integer |
0 |
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_COLLATION |
Internal, collation_name, or collation_id |
Internal |
SORT_PINNABLE_CACHE_PERCENT* |
0 – 100 |
20 |
SUBQUERY_CACHING_PREFERENCE |
-3 – 3 |
0 |
SUBQUERY_FLATTENING_PERCENT |
0, 1 - 232 -1 |
100 |
SUBQUERY_FLATTENING_PREFERENCE |
-3 – 3 |
0 |
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_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_ESCAPE_QUOTES |
ON, OFF |
OFF |
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_RESERVED_DBSPACE_MB |
integer >= 200 in MB |
200 |
TEMP_SPACE_LIMIT_CHECK |
ON, OFF |
ON |
TEXT_DELETE_METHOD |
0 – 2 |
0 |
TIME_FORMAT |
string |
'HH:NN:SS.SSS' |
TIMESTAMP_FORMAT |
string |
'YYYY- MM-DD HH:NN:SS.SSS' |
TOP_NSORT_CUTOFF_PAGES |
1 – 1000 |
1 |
TRIM_PARTIAL_MBC |
ON, OFF |
OFF |
USER_RESOURCE_RESERVATION |
integer |
1 |
VERIFY_PASSWORD_FUNCTION |
string |
'' (empty string) |
WASH_AREA_BUFFERS_PERCENT |
1 – 100 |
20 |
WAIT_FOR_COMMIT |
ON, OFF |
OFF |
WD_DELETE_METHOD |
0 – 3 |
0 |
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 System Administration Guide: Volume 1.
