General database options

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.

NoteThere are additional internal options not listed in this table that Sybase Technical Support might ask you to use.

Table 2-1: General database options

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

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

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

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

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_QUERY_PARALLELISM

integer <= # CPUs

24

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

sp_iq_process_post_login

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_NAME1TEMP_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

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

Data extraction options

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.