set

Sets SAP ASE query-processing options for the duration of the user’s work session; sets some options inside a trigger or stored procedure.

Syntax

set advanced_aggregation on/off
set @variable = expression [, @variable = expression...]
set ansinull {on | off}
set ansi_permissions {on | off}
set arithabort [arith_overflow | numeric_truncation] {on | off}
set arithignore [arith_overflow] {on | off}
set bulk array size number
set bulk batch size number
set builtin_date_strings number
set {chained, close on endtran, nocount, noexec, parseonly, 
	self_recursion, showplan, sort_resources} {on | off}
set char_convert {off | on [with {error | no_error}] |
	charset [with {error | no_error}]}
set cis_rpc_handling {on | off}
set [clientname client_name | clienthostname host_name
	| clientapplname application_name]
set compression {on | off | default}
set cursor rows number for cursor_name
set {datefirst number, dateformat format, language language}
set delayed_commit {on | off | default}
set deferred_name_resolution { on | off }
set dml_logging {minimal | default}
set encryption passwd 'password_phrase'
	for {key | column} {keyname | column_name}
set erase_residual_data {on | off}
set export_options {on | off}
set fipsflagger {on | off}
set flushmessage {on | off}
set fmtonly {on | off}
set forceplan {on | off}
set identity_insert [database.[owner.]]table_name {on | off}
set identity_update table_name {on | off}
set index_union {on | off}
set ins_by_bulk {on | off}
set join_bloom_filter {on | off}
set literal_autoparam {on | off}
set lock {wait [numsecs] | nowait | default}
set logbulkcopy {on | off }
set materialized_view_optimization {disable | fresh | stale}
set metrics_capture {on | off}
set mon_stateful_history {on | off}
set nodata
set offsets {select, from, order, compute, table,
	procedure, statement, param, execute} {on | off}
set option show
set opttimeoutlimit
set parallel_degree number 
set plan {dump | load} [group_name] {on | off}
set plan exists check {on | off}
set plan for show
set plan optgoal {allrows_oltp | allrows_mix | allrows_dss |
	user_defined_goal_identifier}
set plan optlevel value
set plan opttimeoutlimit number
set plan replace {on | off}
set prefetch [on|off]
set proc_output_params {on | off}
set proc_return_status {on | off}
set process_limit_action {abort | quiet | warning}
set proxy login_name
set quoted_identifier {on | off}
set repartition_degree number
set repthreshold number
set resource_granularity number
set role {"sa_role" | "sso_role" | "oper_role" | 
	role_name [with passwd "password"]} {on | off}
set {rowcount number, textsize number}
set scan_parallel_degree number 
set send_locator {on | off }
set session authorization login_name
set switch [serverwide] {on | off} print_minlogged_mode_override
set switch [serverwide] {on | off} trace_flag ,[trace_flag,] [with option [, option]
set show_exec_info [“on” | “off”]
set show_permission_source ["on" | "off" ]
set show_permission_source, {on|off}
set show_sqltext {on | off}
set show_transformed_sql, {on|off}
set spinlock_aggregation {on | off}
set statement_cache on | off
set statistics {io, subquerycache, time, plancost} {on | off}
set statistics plan_detail_html {on | off}
set statistics plan_directory_html {dir_name | on | off}
set statistics plan_html {on | off}
set statistics parallel_plan_detail_html {on | off}
set statistics query_name_html {queryname | on | off}
set statistics simulate {on | off}
set strict_dtm_enforcement {on | off}
set string_rtruncation {on | off}
set system_view {instance | cluster | clear}
set textsize {number}
set statistics timing_html  {on | off}
set tracefile [filename] [off] [for spid]
set transaction isolation level {
	[read uncommitted | 0] | 
	[read committed | 1] |
	[repeatable read | 2] | 
	[serializable | 3]} 
set transactional_rpc {on | off}

Parameters

Examples

Usage

See also:
  • convert in Reference Manual: Building Blocks

  • isql, optdiag in the Utility Guide

  • sp_setrepdbmode, sp_setrepdefmode in Reference Manual: Procedures

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

The ANSI SQL standard specifies behavior that differs from Transact-SQL behavior in versions of SAP ASE earlier than 15.7. Compliant behavior is enabled, by default, for all Embedded-SQL precompiler applications. Other applications needing to match this standard of behavior can use these set options:

Options to set for Entry-Level ANSI SQL Compliance

Option

Setting

ansi_permissions

on

ansinull

on

arithabort

off

arithabort numeric_truncation

on

arithignore

off

chained

on

close on endtran

on

fipsflagger

on

quoted_identifier

on

string_rtruncation

on

transaction isolation level

3

Permissions

Permission checks may differ based on your granular permission settings. In general, set permission defaults to all users and no special permissions are required to use it. Exceptions include set identity_insert, set identity_update, set option show_option, set plan for show, set proxy, set repthreshold, set role, set session authorization, set tracefile, and set switch. See command description above for permission requirements for each exception.

Auditing

Values in event and extrainfo columns of sysaudits are:

InformationValues
Event

88

Audit option

security

Command or access audited

set proxy or set session authorization

Information in extrainfo
  • Roles – current active roles

  • Keywords or options – NULL

  • Previous value – Previous suid

  • Current value – New suid

  • Other information – NULL

  • Proxy information – original login name, if set proxy or set session authorization had no parameters; otherwise, NULL.

Related reference
Aggregate Behavior
Using Proxies
Delimited Identifiers
create trigger
fetch
grant
insert
lock table
revoke