Database options

The following options allow you to configure the behavior of a SQL Anywhere database server.

Option Values Default
allow_nulls_by_default option On, Off

On

Off for Sybase Open Client and jConnect connections

allow_read_client_file option On, Off Off
allow_snapshot_isolation option On, Off Off
allow_write_client_file option On, Off Off
ansi_blanks option On, Off Off
ansi_close_cursors_on_rollback option On, Off Off
ansi_permissions option On, Off On
ansi_substring option On, Off On
ansi_update_constraints option Off, Cursors, Strict Cursors
ansinull option On, Off On
auditing option On, Off Off
auditing_options option Reserved Reserved
background_priority option [deprecated] On, Off Off
blocking option On, Off On
blocking_others_timeout option Integer, in milliseconds 0
blocking_timeout option Integer, in milliseconds 0
chained option On, Off

On

Off for Sybase Open Client and jConnect connections

checkpoint_time option Number of minutes 60
cis_option option 0, 7 0
cis_rowset_size option Integer 50
close_on_endtrans option On, Off

On

Off for jConnect connections

collect_statistics_on_dml_updates option On, Off On
conn_auditing option On, Off On
connection_authentication option String Empty string
continue_after_raiserror option On, Off On
conversion_error option On, Off On
cooperative_commit_timeout option Integer, in milliseconds 250
cooperative_commits option On, Off On
database_authentication String Empty string
date_format option String YYYY-MM-DD
date_order option MDY, YMD, DMY YMD
debug_messages option On, Off Off
dedicated_task option On, Off Off
default_dbspace option String Empty string (use the system dbspace)
default_timestamp_increment option Integer, representing microseconds, from 1 to 1000000 1
delayed_commit_timeout option Integer, in milliseconds 500
delayed_commits option On, Off Off
divide_by_zero_error option On, Off On
escape_character option Reserved Reserved
exclude_operators option Reserved Reserved
extended_join_syntax option On, Off On
fire_triggers option On, Off On
first_day_of_week option 1, 2, 3, 4, 5, 6, 7 7 (Sunday is the first day of the week)
for_xml_null_treatment option Empty, Omit Omit
force_view_creation option Reserved Reserved
global_database_id option Integer 2147483647
http_connection_pool_basesize option Integer (from 0 to 1000) 10
http_connection_pool_timeout option Integer, in seconds from 1 to 86400 60
http_session_timeout option Integer 30
integrated_server_name option String NULL
isolation_level option 0, 1, 2, 3, Snapshot, Statement-snapshot, Readonly-statement-snapshot 0
java_class_path option String Empty string
java_location option String Empty string
java_vm_options option String Empty string
log_deadlocks option On, Off Off
login_mode option Standard, Integrated, Kerberos, Mixed (deprecated) Standard
login_procedure option String sp_login_environment
materialized_view_optimization option Disabled, Fresh, Stale, N { Minute[s] | Hour[s] | Day[s] | Week[s] | Month[s] } Stale
max_client_statements_cached option Integer 10
max_cursor_count option Integer 50
max_plans_cached option Integer 20
max_priority option Critical, High, Above Normal, Normal, Below normal, Low, Background Normal
max_query_tasks option Integer 0
max_recursive_iterations option Integer 100
max_statement_count option Integer >=0 50
max_temp_space option Integer [k|m|g|p] 0
min_password_length option Integer >=0 0 characters
nearest_century option Integer, between 0 and 100 inclusive 50
non_keywords option String Empty string
odbc_describe_binary_as_varbinary On, Off Off
odbc_distinguish_char_and_varchar option On, Off Off
oem_string option String (up to 128 bytes) Empty string
on_charset_conversion_failure option Ignore, Warning, Error Ignore
on_tsql_error option Stop, Conditional, Continue

Conditional

Continue for jConnect connections

optimization_goal option First-row or All-rows All-rows
optimization_level option 0-15 9
optimization_workload option Mixed, OLAP Mixed
pinned_cursor_percent_of_cache option Integer, between 0-100 10
post_login_procedure option String Empty string
precision option Integer, between 1 and 127 inclusive 30
prefetch option Off, Conditional, Always Conditional
preserve_source_format option On, Off On
prevent_article_pkey_update option On, Off On
priority option Critical, High, Above Normal, Normal, Below normal, Low, Background Normal
progress_messages option Off, Formatted, Raw Off
query_mem_timeout option -1, 0, positive integer -1
quoted_identifier option On, Off On
read_past_deleted option On, Off On
recovery_time option Integer, in minutes 2
remote_idle_timeout option Integer, in seconds 15
request_timeout option Integer, 0 through 86400, in seconds 0
reserved_keywords option String Empty string
return_date_time_as_string option On, Off Off
rollback_on_deadlock On, Off On
row_counts option On, Off Off
scale option Integer, between 0 and 127, inclusive, and less than the value specified for the precision database option 6
secure_feature_key String NULL
sort_collation option Internal, collation_name, or collation_id Internal
sql_flagger_error_level option Off, SQL:1992/Entry, SQL:1992/Intermediate, SQL:1992/Full, SQL:1999/Core, SQL:1999/Package, SQL:2003/Core, SQL:2003/Package, UltraLite Off
sql_flagger_warning_level option Off, SQL:1992/Entry, SQL:1992/Intermediate, SQL:1992/Full, SQL:1999/Core, SQL:1999/Package, SQL:2003/Core, SQL:2003/Package, UltraLite Off
string_rtruncation option On, Off On
st_geometry_asbinary_format option The list of values supported for this option is identical to the list of parameters documented for the ST_AsBinary method for type ST_Geometry. See ST_AsBinary method for type ST_Geometry. WKB
st_geometry_astext_format option The list of values supported for this option is identical to the list of parameters documented for the ST_AsText method for type ST_Geometry. See ST_AsText method for type ST_Geometry. WKT
st_geometry_asxml_format option The list of values supported for this option is identical to the list of parameters documented for the ST_AsXML method for type ST_Geometry. See ST_AsXML method for type ST_Geometry. XML
st_geometry_describe_type option CHAR, NCHAR, BINARY CHAR
st_geometry_interpolation option A set of key=value pairs, formatted as: key1=value1;key2=value2;. Valid keys include: relative-tolerance-percent=DOUBLE absolute-tolerance=DOUBLE See the description of the defaulting behavior found in st_geometry_interpolation option.
st_geometry_on_invalid option
subsume_row_locks option On, Off On
suppress_tds_debugging option On, Off Off
synchronize_mirror_on_commit option On, Off Off
tds_empty_string_is_null option On, Off Off
temp_space_limit_check option On, Off On
time_format option String HH:NN:SS.SSS
time_zone_adjustment option Integer, or negative integer enclosed in quotation marks, or string, representing time in hours and minutes, preceded by + or -, enclosed in quotation marks Set by either the client's or the database server's time zone, depending on the client's connection type
timestamp_format option String YYYY-MM-DD HH:NN:SS.SSS
timestamp_with_time_zone_format option String YYYY-MM-DD HH:NN:SS.SSS+HH:NN
truncate_timestamp_values option On, Off Off
tsql_outer_joins option On, Off Off
tsql_variables option On, Off

Off

On for Sybase Open Client and jConnect connections

updatable_statement_isolation option 0, 1, 2, 3 0
update_statistics option On, Off On
user_estimates option Enabled, Disabled, Override-Magic Override-Magic
uuid_has_hyphens option On, Off Off
verify_password_function option String Empty string
wait_for_commit option On, Off Off
webservice_namespace_host option NULL, hostname-string NULL