Sets SAP ASE query-processing options for the duration of the user’s work session; sets some options inside a trigger or stored procedure.
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}
expression includes constant, function, any combination of constants, and functions connected by arithmetic or bitwise operators, or a subquery.
Command |
||
|---|---|---|
Off |
On |
|
update |
|
|
delete |
|
|
arithabort arith_overflow – specifies the SAP ASE server behavior following a divide-by-zero error, range overflow during an explicit or implicit datatype converson, or a domain error. This type of error is serious. The default setting, arithabort arith_overflow on, rolls back the entire transaction in which the error occurs. If the error occurs in a batch that does not contain a transaction, arithabort arith_overflow on does not roll back earlier commands in the batch; however, the SAP ASE server does not execute any statements in the batch that follow the error-generating statement.
Setting arith_overflow to on refers to the execution time, not to the level of normalization to which the SAP ASE server is set.
If you set arithabort arith_overflow off, the SAP ASE server aborts the statement that causes the error, but continues to process other statements in the transaction or batch.
arithabort numeric_truncation – specifies the SAP ASE behavior following a loss of scale by an exact numeric type during an implicit datatype conversion. (When an explicit conversion results in a loss of scale, the results are truncated without warning.) The default setting, arithabort numeric_truncation on, aborts the statement that causes the error, but the SAP ASE server continues to process other statements in the transaction or batch. If you set arithabort numeric_truncation off, the SAP ASE server truncates the query results and continues processing.
Use this option only with CIS for transferring rows to a remote server using select into.
View your current setting using the @@bulkarraysize global variable.
number – indicates the number of rows to buffer. If the rows being transferred contain text, unitext , image or java ADTs, then the bulk copy interface ignores the current setting for array size and uses a value of 1. Also, the array size actually used never exceeds the value of @@bulkbatchzise. If @@bulkbatchsize is smaller than array size, then the smaller value is used.
The initial value of the array size is inherited by new connections from the current setting of the configuration property cis bulk insert array size, which defaults to 50. Setting this value to 0 resets the value to the default.
Use this option only with CIS for transferring rows to a remote server using select into.
View your current setting using the @@bulkbatchsize global variable.
The bulk interface allows a commit after a specified number of rows. This allows the remote server to free any log space being consumed by the bulk transfer operation, and enables the transfer of large data sets from one server to another without filling the transaction log.
The initial value of the batch size is inherited by new connections from the current setting of the configuration property cis bulk insert batch size, which by default is 0. A value of 0 indicates that no rows should be committed until after the last row is transferred.
Changing the builtin_date_strings value to 1 makes the server interpret the argument strings as bigdatetimes. This affects the result of chronological builtins.
chained – begins a transaction just before the first data retrieval or data modification statement at the beginning of a session and after a transaction ends. In chained mode, the SAP ASE server implicitly executes a begin transaction command before the following statements: delete, fetch, insert, lock table, open, select, and update. You cannot execute set chained within a transaction.
close on endtran – causes the SAP ASE server to close all cursors opened within a transaction at the end of that transaction. A transaction ends by the use of either the commit or rollback statement. However, only cursors declared within the scope that sets this option (stored procedure, trigger, and so on) are affected. For more information about cursor scopes, see the Transact-SQL User’s Guide.
For more information about the evaluated configuration, see the System Administration Guide.
nocount – controls the display of rows affected by a statement. set nocount on disables the display of rows; set nocount off reenables the count of rows.
noexec – compiles each query but does not execute it. noexec is often used with showplan. After you set noexec on, no subsequent commands are executed (including other set commands) until you set noexec off.
parseonly – checks the syntax of each query and returns any error messages without compiling or executing the query. Do not use parseonly inside a stored procedure or trigger.
self_recursion – determines whether the SAP ASE server allows triggers to cause themselves to fire again (this is called self recursion). By default, the SAP ASE server does not allow self recursion in triggers. You can turn this option on only for the duration of a current client session; its effect is limited by the scope of the trigger that sets it. For example, if the trigger that sets self_recursion on returns or causes another trigger to fire, this option reverts to off. This option works only within a trigger and has no effect on user sessions.
showplan – generates a description of the processing plan for the query. The results of showplan are of use in performance diagnostics. showplan does not print results when it is used inside a stored procedure or trigger. For parallel queries, showplan output also includes the adjusted query plan at runtime, if applicable. See the Performance and Tuning Guide.
sort_resources – generates a description of the sorting plan for a create index statement. The results of sort_resources are of use in determining whether a sort operation is done serially or in parallel. When sort_resouces is on, the SAP ASE server prints the sorting plan but does not execute the create index statement. See Parallel Sorting in the Performance and Tuning Guide.
charset can be either the character set’s ID or a name from syscharsets with a type value of less than 2000.
set char_convert off turns conversion off so that characters are sent and received unchanged. set char_convert on turns conversion on if it is turned off. If character set conversion was not turned on during the login process or by the set char_convert command, set char_convert on generates an error message.
If you request character set conversion with set char_convert charset, and the SAP ASE server cannot perform the requested conversion, the conversion state remains the same as it was before the request. For example, if conversion is set to off prior to the set char_convert charset command, conversion remains turned off if the request fails.
When the with no_error option is included, the SAP ASE server does not notify an application when characters from the SAP ASE server cannot be converted to the client’s character set. Error reporting is initially turned on when a client connects with the SAP ASE server: if you do not want error reporting, you must turn it off for each session with set char_convert {on | charset} with no_error. To turn error reporting back on within a session, use set char_convert {on | charset} with error.
Whether or not error reporting is turned on, the bytes that cannot be converted are replaced with ASCII question marks (?).
See the System Administration Guide for more about error handling in character set conversion.
clientname client_name – assigns a client an individual name. This is useful for differentiating among clients in a system where many clients connect to the SAP ASE server using the same client name. After you assign a new name to a user, they appear in the sysprocesses table under the new name.
client_name is the new name you assign to the user.
clienthostname host_name – assigns a host an individual name. This is useful for differentiating among clients in a system where many clients connect to the SAP ASE server using the same host name. After you assign a new name to a host, it appears in the sysprocesses table under the new name.
host_name is the new name you assign to the host.
clientapplname application_name – assigns an application an individual name. This is useful for differentiating among clients in a system where many clients connect to the SAP ASE server using the same application name. After you assign a new name to an application, it appears in the sysprocesses table under the new name.
application_name is the new name you assign to the application.
on – enables data compression on new data for tables and partitions on which compression is configured. The SAP ASE server compresses all rows that qualify.
off – inserts and updates all new data as uncompressed. Inserts that trigger a page compression ignore the uncompressed rows. Updated rows remain uncompressed. Uncompressed rows that are compressed during an update (rows inserted as uncompressed remain uncompressed until explicitly compressed).
default – resets the compression level to the default setting (inserts and updates are compressed according to the table or partition setting).
datefirst number – uses numeric settings to specify the first day of the week. The us_english language default is Sunday.
|
To Set the First Day of the Week as |
Use |
|---|---|
|
Monday |
1 |
|
Tuesday |
2 |
|
Wednesday |
3 |
|
Thursday |
4 |
|
Friday |
5 |
|
Saturday |
6 |
|
Sunday (us_english language default) |
7 |
dateformat format – sets the order of the date parts month/day/year for entering datetime, smalldatetime, date or time data. Valid arguments are mdy, dmy, ymd, ydm, myd, and dym. The us_english language default is mdy.
language language – is the official name of the language that displays system messages. The language must be installed on the SAP ASE server. The default is us_english.
This option works only at the session level if you set the server level deferred_name_resolution option off. If server level deferred_name_resolution is OFF, set deferred_name_resolution on takes effect at the session level and can be switched off again at session level. If, however, deferred_name_resolution is set on at server level, set deferred_name_resolution off does not take effect on the session, because it cannot override the server setting.
The session-level setting overrides any existing the database-level setting. Change delayed_commit to its default to revert back to the database-level setting.
minimal – the SAP ASE server attempts to log no changes for the DML statements. In most cases, the SAP ASE server generates little or no logging to syslogs.
default – the SAP ASE server disables session-specific minimal logging and uses the logging mode you enabled for individual tables based on table-specific and database-wide logging levels.
Any user can execute set dml_logging for minimal logging and for returning to the default mode of logging. Once this set succeeds, DML on all tables owned by the user executing the statement in any database is minimally logged for the current session, until set dml_logging default is executed.
DML logging defaults to the database- and table-level settings when minimal logging is enabled for the session but the DML operates on tables not owned by the session’s user.
The DML logging setting in a session or procedure is inherited by procedures, however, only the tables owned by the user running the session are affected.
password_phrase – is the explicit password specified with the create encryption key or alter encryption key command to protect the key.
key – indicates that the SAP ASE server uses this password to decrypt the key when accessing any column encrypted by the named key
[[database.][owner].]keyname
column – specifies that the SAP ASE server use this password only in the context of encrypting or decrypting the named column. End users do not necessarily know the name of the key that encrypts a given column.
column_name – name of the column on which you are setting an encryption password. Supply column_name as:
[[ database.][ owner ]. ]table_name.column_name
When you enable the option at a session level, residual data is removed from all the page deallocations that occur during that session. This includes page deallocations of tables that have the "erase residual data" option turned OFF explicitly.
This option can be set by any user for the particular session; no special permissions are required.
set export_options on
set export_options off
1> set forceplan on 2> select * from table1, table2 where table2.id *= table1.id
After setting identity_insert on for the table, the table owner or users with insert permission on the column can manually insert any legal value greater than 5. For example, inserting a value of 55 would create a large gap in IDENTITY column values:
insert stores_cal (syb_identity, stor_id, stor_name) values (55, "5025", "Good Reads") select syb_identity from stores_cal
id_col ------- 1 5 55 (3 rows affected)
If identity_insert is then set to off, the SAP ASE server assigns an IDENTITY column value of 55 + 1, or 56, for the next insertion. If the transaction that contains the insert statement is rolled back, the SAP ASE server discards the value 56 and uses a value of 57 for the next insertion.
Unless you have created a unique index on the IDENTITY column, the SAP ASE server does not verify the uniqueness of the inserted value; you can insert any positive integer.
Setting identity_insert table_name off restores the default behavior by prohibiting explicit inserts to IDENTITY columns. At any time, you can use set identity_insert table_name on for a single database table within a session.
The permission checks for set identity_insert table_name on|off differ based on your granular permissions settings.
| Setting | Description |
|---|---|
Enabled | With granular permissions enabled, you must be the table owner or a user with identity_insert permission on the table. Table owner or user with manage any object permission privilege can grant the permission to others. |
Disabled | With granular permissions disabled, you must the the table owner, database owner, or a user with sa_role. identity_insert permission is not transferable. |
update table_name set syb_identity = value where clause
The SAP ASE server does not check for duplicates entries or verify that entries are unique. You can update an existing value to any positive integer within the range allowed by the column's declared precision. You can check for duplicate entries by creating a unique index on the identity column.
The permission checks for set identity_update table_name on|off differ based on your granular permissions settings.
| Setting | Description |
|---|---|
Enabled | With granular permissions enabled, you must be the table owner or a user with identity_update permission on the table. Table owner or user with manage any object permission privilege can grant the permission to others. |
Disabled | With granular permissions disabled, you must the the table owner, database owner, or a user with sa_role. identity_update permission is not transferable. |
select * from titleauthor where au_id = "409-56-7008" or title_id = "PC8888"
Enabled – this example uses an index on au_id to find the row IDs (RIDs) of all titleauthor tuples with au_id = "409-56-7008", and uses an index on title_id to find the RIDs of all titleauthor tuples with title_id = "PC8888". The SAP ASE server then performs a union on all RIDs to eliminate duplicates. The resulting RIDs are joined with a RidJoin to access the data tuples.
Disabled – the SAP ASE server does not use an index union strategy in a query to limit the table scan. Instead, it uses other access paths on the table (in the example above, it would use a table scan for table titleauthor), and applies the or clause as a filter in the scan operator.
wait – specifies the length of time that a command waits to acquire locks before aborting and returning an error.
numsecs – specifies the number of seconds a command is to wait to acquire a lock. Valid values are from 0 to 2147483647, the maximum value for an integer.
lock nowait – specifies that if a command cannot acquire a lock immediately, it returns an error and fails. set lock nowait is equivalent to set lock wait 0.
disabled – (the default) the SAP ASE server does not use any precomputed result sets in query optimization.
fresh – the SAP ASE server considers only the precomputed result sets with an immediate refresh policy.
stale – the SAP ASE server considers all enabled precomputed result sets for query optimization, even if they are stale.
When enabled, queries to the historical monitoring tables return only rows that were added to the tables since mon_stateful_history was disabled.
This option is used in Open Client DB-Library only.
show – shows the basic syntax common to all modules
show_lop – shows the logical operators (scans, joins, etc.) used
show_managers – shows data structure managers used during optimization.
show_log_props – shows the logical properties (row count, selectivity, etc.) evaluated.
show_parallel – shows details of parallel query optimization
show_histograms – shows the processing of histograms associated with SARG/Join columns
show_abstract_plan – shows the details of an abstract plan
show_search_engine – shows the details of the join ordering algorithm
show_counters – shows the optimization counters
show_best_plan – shows the details of the best query plan selected by the optimizer
show_pio_costing – shows estimates of physical input/output (reads/writes from/to the disk)
show_lio_costing – shows estimates of logical input/output (reads/writes from/to memory)
show_elimination – shows partition elimination
brief – the SAP ASE server creates minimal statistics for each query, and show_missing_stats does not report warnings on user created temporary tables.
long – the SAP ASE server creates full statistics for each query, including user-created temporary tables.
See Displaying Query Optimization Strategies and Estimates in Query Optimizer for more information.
The permission checks for set option show_option differ based on your granular permissions settings.
| Setting | Description |
|---|---|
Enabled | With granular permissions enabled, you must be a user with either set tracing privilege or monitor qp performance privilege. |
Disabled | With granular permissions disabled, you must be a user with set tracing privilege or a user with sa_role. |
dump – enables or disables capturing abstract plans for the current connection. If a group_name is not specified, the plans are stored in the default group, ap_stdout.
load – enables or disables loading abstract plans for the current connection. If a group_name is not specified, the plans are loaded from the default group, ap_stdin.
group_name – is the name of the abstract plan group to use for loading or storing plans.
See Creating and Using Abstract Plans in the Performance and Tuning Guide.
show_exec_xml – gets the compiled plan output in XML, showing each of the query plan operators.
show_execio_xml – gets the plan output along with estimated and actual IOs. This also includes the query text.
show_opt_xml – gets optimizer diagnostic output, which shows all of the different components like logical operators, output from the managers, some of the search engine diagnostics, and the best query plan.
show_lop_xml – gets the output logical operator tree in XML.
show_managers_xml – shows the output of the different component managers during the preparation phase of the query optimizer.
show_log_props_xml – shows the logical properties for a given equivalence class (one or more group of relations in the query).
show_parallel_xml – shows the diagnostics related to the optimizer while generating parallel query plans.
show_histograms_xml – shows diagnostics related to histograms and the merging of histograms.
show_abstract_plan_xml – shows the AP generation/application.
show_search_engine_xml – shows the search engine related diagnostics.
show_counters_xml – shows plan object construction/destruction counters.
show_best_plan_xml – shows the best plan in XML.
show_pio_costing_xml – shows actual PIO costing in XML.
show_lio_costing_xml – shows actual LIO costing in XML.
show_elimination_xml – shows partition elimination in XML.
client – when specified, output goes to the client.
message – when specified, output goes to an internal message buffer.
See Displaying Query Optimization Strategies and Estimates in Performance and Tuning Series: Query Processing and Abstract Plans for more information.
The permission checks for set plan for show differ based on your granular permissions settings.
| Setting | Description |
|---|---|
Enabled | With granular permissions enabled, you must be a user with either set tracing privilege or monitor qp performance privilege. |
Disabled | With granular permissions disabled, you must be a user with set tracing privilege or a user with sa_role. |
allrows_mix – is the default optmization goal, and the most useful goal in a mixed-query environment. It balances the needs of OLTP and DSS query environments.
allrows_dss – is the most useful goal for operational DSS queries of medium to high complexity. Currently, this goal is provided on an experimental basis.
See Understanding Query Processing in SAP ASE in Performance and Tuning Series: Query Processing and Abstract Plans for more information about optimization plans.
ase_current – enables all optimizer changes through the current release.
ase_default – disables all optimizer changes since version 15.0.3 ESD #1.
ase1503esd2 – enables all optimizer changes through version 15.0.3 ESD #2.
ase1503esd3 – enables all optimizer changes through version 15.0.3 ESD #3.
See Controlling Optimization in Performance and Tuning Series: Query Processing and Abstract Plans.
process_limit_action is set to quiet, the SAP ASE server silently adjusts the plan to use a degree of parallelism that does not exceed the number of available processes.
process_limit_action is set to warning when an insufficient number of worker processes are available, the SAP ASE server issues a warning message when adjusting the plan
process_limit_action is set to abort, the SAP ASE server aborts the query and issues an explanatory message an insufficient number of worker processes are available.
Conform to the rules for valid identifiers.
Be enclosed in brackets.
If you use set quoted_identifier on, double quotation marks behave the way brackets do, and you can use table, view, and column names that begin with a nonalphabetic character, including characters that would not otherwise be allowed, or are reserved words, by enclosing the identifiers within double quotation marks. Delimited identifiers cannot exceed 28 bytes, may not be recognized by all front-end products, and may produce unexpected results when used as parameters to system procedures.
When quoted_identifier is on, all character strings enclosed within double quotes are treated as identifiers. Use single quotes around character or binary strings.
A user can alter the scope of the threshold and set the session threshold using a login trigger, in which case you need not explicitly set the session threshold at login.
For example,
create proc myproc as set repthreshold 777 --------------------- alter login sa modify login script 'myproc' ---------------------- option changed.
(Return status = 0
The procedure ’myproc’ is invoked each time the user sa logs in, and ensures that the replication threshold is set at 777 for the whole session.
Another way to alter the scope of the threshold is to use set export_options:
create proc p2 as set repthreshold222 set export_options on ---------------------------
After p2 is executed the threshold remains set at 222.The hierarchy of thresholds is Session > Table > Database
When you set the session threshold to 0, the only replication thresholds that still matter are table and database thresholds, if they have been specified, in that order. If you set no threshold, the threshold defaults to 50 rows.
To set the table level threshold, see sp_setrepdbmode; to set the database level threshold, see sp_setrepdefmode, both in the Reference Manual: Procedures.
The session threshold is exportable; a stored procedure can set the threshold and turn the export options setting ON. The SAP ASE server enforces the new threshold in the invoking procedure or session.
The permission checks for set repthreshold differ based on your granular permissions settings.
| Setting | Description |
|---|---|
Enabled | With granular permissions enabled, you must be a user with manage replication privilege. |
Disabled | With granular permissions disabled, you must be a user with replication_role. |
role_name can be the name of a system role or a user-defined role. When you log in, all system roles that have been granted to you are automatically activated. If you have been granted sa_role or sso_role, you cannot set these roles off unless you are a named or aliased user in the database or there exists a "guest" user.
role_name – is the name of any user-defined role created by the system security officer. User-defined roles are not turned on by default. To set user-defined roles to activate at login, system security officer must specify the role as a default or auto activated role when creating or altering a login or a login profile.
with passwd – specifies the password to activate the role. If a user-defined role has an attached password, you must specify the password to activate the role.
To use set role, the role must have been granted to you. If you gain entry to a database only because you have a certain role, you cannot turn that role off while you are using the database.
If you were granted a role using an activation predicate, the SAP ASE server evaluates the predicate when you execute set role on. If the predicate evaluates to false, the SAP ASE server returns an error and does not activate the role.
set rowcount 0
select @@setrowcount ____________ 37
on – generates extra diagnostics about the logging mode for a DML statement. Displays the selected logging mode for the current statement and session, and the user executing the DML.
off – disables show_exec_info.
If you grant permission to a role that is part of a hierarchy the permission is not granted directly to the user. For example, if you granted permissions for role1 to role2, which in turn is granted to role3, and that permission is granted to a user. set show_permission_source displays “role1” in the grantee column and not role3 because role1 was granted the permission and not role3. Use sp_displayroles ... expand_up to see information about role3.
If more than one grantor grants permission for a particular action, set show_permission_source displays the permission associated with the grantor with the highest user ID.
set show_permission_source displays information at the object level and not the column level because there may be more than one row for the same object from different grantors on different columns.
If more than one permission exists with different predicates on an object for a combination of actions and grantors, set show_permission_source displays all the predicate names, with a separate row for each predicate.
You do not need to enable the set show_sqltext before you execute the query (as you do with commands like set showplan on) to collect diagnostic information for a SQL session. Instead you can enable it while the commands are running to help determine which query is performing poorly and diagnose their problems.
dbcc traceon(3604)The syntax for set show_sqltext is:
set show_sqltext {on | off}
set show_sqltext on
Once set show_sqltext is enabled, the SAP ASE server prints all SQL text to standard out for each command or system procedure you enter. Depending on the command or system procedure you run, this output can be extensive.
To disable show_sqltext, enter:
set show_sqltext off
By default, SAP ASE aggregates the values for Grabs, Waits, and Spins for all spinlocks with the same value. Set set spinlock_aggregation to off to configure SAP ASE to return a separate row for each spinlock instance in the monSpinlockActivity table.
The number of times the table is accessed (scan count)
The number of logical reads (pages accessed in memory)
The number of physical reads (database device accesses)
For each command, statistics io displays the number of buffers written.
If the SAP ASE server has been configured to enforce resource limits, statistics io also displays the total I/O cost.
subquerycache – displays the number of cache hits, misses, and the number of rows in the subquery cache for each subquery.
time – displays the amount of time the SAP ASE server used to parse and compile for each command. For each step of the command, statistics time displays the amount of time the SAP ASE server used to execute the command. Times are given in milliseconds and timeticks, the exact value of which is machine-dependent.
plancost – displays the query statistics in a tree format.
simulate – specifies that the optimizer should use simulated statistics to optimize the query.
See Using the set statistics Command in Performance and Tuning Series: Improving Performance with Statistical Analysis.
plan_detail_html {on | off} – generates a graphical query plan in HTML format containing information about details per thread and plan fragments for query plans that are executed in parallel using several worked threads. Use this option to diagnose the behavior of specific threads and plan fragments in comparison with the global execution of the query.
parallel_plan_detail_html {on | off} – generates a graphical query plan in HTML format containing information details of plan operators, such as the name, different timestamps captured during the execution, number of rows affected, number of estimated rows, elapsed time, and so on.
plan_directory_html "dirName" {on | off} – specifies the directory path name into which to write the HTML query plans. The file name is identified by a combination of user name, spid, and timestamp. When set to off, the dumping of the HTML data to an external file is stopped.When set to on, the dumping of HTML data to an external file in a directory previously indicated is resumed. No output is generated if a directory name was not previously provided.
plan_html {on | off} – generates a graphical query plan in HTML format containing information about the number of rows and number of threads per operator.
timing_html {on | off} – generates a graphical query plan in HTML format containing execution statistics related to the timing spent in each operator per execution phase. CPU usage and Wait distribution is generated for queries executed in parallel.
See Using the set statistics Command in Performance and Tuning Series: Improving Performance with Statistical Analysis.
instance – sets the system view for the current instance.
cluster – sets the system view for the cluster.
clear – clears any session-level setting, reverting to the system_view setting for the logical cluster hosting that spid. Enter select @@system_view to check the current value.
serverwide – (optional) sets a switch serverwide ON or OFF. The default is session-specific.
on – trace flags are switched on.
off – trace flags are switched off.
trace_flag – a sequence of numbers (the old traceflag numbers) and/or switch names.
option – (optional) a sequence of switch options. Valid values are:
override – this option is necessary to enable a non-documented switch names or trace flags
no_info – this option is used to surpress any informational warnings
The permission checks for set switch differ based on your granular permissions settings.
| Setting | Description |
|---|---|
Enabled | With granular permissions enabled:
For all other traceflags, you must have set switch privilege. |
Disabled | With granular permissions disabled, you must have sa_role. |
set textsize 0
set tracefile file_name [off] [for spid
set tracefile off [for spid]
file_name – is the full path to the file in which you are saving the SQL text. If you do not specify a directory path, the SAP ASE server creates the file in $SYBASE.
set tracefile '/tmp/mytracefile.txt' for 25
If file_name does not contain special characters and you want to save it to $SYBASE, it does not require quotes. For example, this file_name does not need to be in quotes:
set tracefile mytracefile.txt
off – disables the tracing for this session or spid.
spid – server process ID whose SQL text you want saved to a trace file. Only the users with the SA or systems security officer role can enable tracing for other spids. You cannot save the SQL text for system tasks (such as the housekeeper or the port manager).
Make sure to switch off all the diagnostic commands you turned on before issuing set tracefile off, or else the output that should go to the tracefile instead goes to the client.
The permission checks for set tracefile differ based on your granular permissions settings.
| Setting | Description |
|---|---|
Enabled | With granular permissions enabled, setting tracefile for your own session requires either set tracing privilege or monitor qp performance privilege. To set tracefile for other user's session you must have either set tracing any process privilege or monitor qp performance privilege. |
Disabled | With granular permissions disabled, setting tracefile for your own session requires set tracing privilege; to set tracefile for other user's session you must have either sa_role or sso_role. |
read uncommitted | 0 – scans at isolation level 0 do not acquire any locks. Therefore, the result set of a level 0 scan may change while the scan is in progress. If the scan position is lost due to changes in the underlying table, a unique index is required to restart the scan. In the absence of a unique index, the scan may be aborted.
select * from table_name (index table_name)
Activity on the underlying table may cause the scan to be aborted before completion.
read committed | 1 – by default, the SAP ASE transaction isolation level is read committed or 1, which allows shared read locks on data.
repeatable read | 2 – prevents nonrepeatable reads.
serializable | 3 – specifies isolation level 3, the SAP ASE server applies a holdlock to all select and readtext operations in a transaction, which holds the queries’ read locks until the end of that transaction. If you also set chained mode, that isolation level remains in effect for any data retrieval or modification statement that implicitly begins a transaction.
set ansinull on
Warning - null value eliminated in set function
select * from titles where price = null
If you use set ansinull off, the same query returns rows in which price is NULL.
set char_convert on with error
set cis_rpc_handling on
set clientname 'alison' set clienthostname 'money1' set clientapplname 'webserver2'
set cursor rows 5 for test_cursor
begin tran set dml_logging minimal
insert into tab1 values(1)
set dml_logging default
insert into tab1 values(1)
The error log includes reasons the logging mode choice was overridden.
set export_options onTo disable set export_options and return the SAP ASE server to the default behavior, use:
set export_options off
You can export these optimization settings using set export_options on.
set fipsflagger on
use pubs2 go
SQL statement on line number 1 contains Non-ANSI text. The error is caused due to the use of use database.
set identity_insert stores_south on go insert stores_south (syb_identity) values (100) go set identity_insert stores_south off go
set identity_update t1 on update t1 set c2 = 10 where c1 =1 select * from t1 c1 c2 -------- ------- 1 10
set identity_update t1 off
set lock nowait
set lock wait
set lock wait 5
set plan dump dev_plans on
set plan load dev_plans on
1> create procedure sp_pout (@x int output) as select @x = @x + 1 2> go 1> set proc_output_params off 2> go 1> declare @x int 2> select @x = 1 3> exec sp_pout @x output 4> print "Value of @x returned from sproc is: %1!", @x 5> go (1 row affected) (return status = 0) Value of @x returned from sproc is: 1
Return parameters: ----------- 2
set proc_output_params off go set proc_return_status OFF go declare @x int select @x = 2 exec sp_pout @x output print "Value of @x returned from sproc is: %1!", @x go (1 row affected) Value of @x returned from sproc is: 2 (1 row affected)
In addition, you can also suppress the lines reporting the number of rows affected to generate output with no extra messages using the set nocount on option before running this batch.
set proxy "mary"
select title_id, price from titles title_id price -------- ---------- BU1032 19.99 BU1111 11.95 BU2075 2.99 BU7832 19.99 (4 rows affected)
set rowcount 4
set quoted_identifier on
go
create table "!*&strange_table"
("emp's_name" char (10), age int)
go
set quoted_identifier off
go
set quoted_identifier off go create table [!*&strange_table] ([emp’s_name] char (10), age int) go
set role doctor_role on
set role "sa_role" off
set role doctor_role with passwd "physician" on
set scan_parallel_degree 4
set session authorization "mary"
set showplan, noexec on go select * from publishers go
1> set spinlock_aggregation off
2> go
1> select * from monSpinlockActivity
2> where SpinlockName like "default data cache%"
3> order by Contention
4> go
Grabs Spins Waits OwnerPID
LastOwnerPID Contention InstanceID SpinlockSlotID
SpinlockName
-------------------- -------------------- -------------------- -----------
------------ --------------------------- ---------- --------------
----------------------------------------------------------------------
37697 978 1 0
1638413 0.000027 0 2338
default data cache 16396 15
2 0
1638413 0.000122 0 2340
default data cache
17317 24 3 0
1638413 0.000173 0 2339
default data cache
27533 629 5 0
1638413 0.000182 0 2341
default data cache
select count(*) from monSpinlockActivity
-----------
2384
1> set spinlock_aggregation on
2> go
1> select * from monSpinlockActivity
2> where SpinlockName like "default data cache%"
3> order by Contention
4> go
Grabs Spins Waits OwnerPID
LastOwnerPID Contention InstanceID SpinlockSlotID
SpinlockName
-------------------- -------------------- -------------------- -----------
------------ --------------------------- ---------- --------------
-------------------------------------------------------------------
99235 1646 11 0
1769486 0.000111 0 2338
default data cache
select count(*) from monSpinlockActivity
-----------
324
set statistics plancost on
select * from authors au_id au_lname au_fname phone address city state country postalcode ----------- ----------- ----------- ------------ --------------------- ------------ ----- ---------- ------------------------------------172-32-1176 White Johnson 408 496-7223 10932 Bigge Rd. Menlo Park CA USA 94025 213-46-8915 Green Marjorie 415 986-7020 309 63rd St. #411 Oakland CA USA 94618 . . . 998-72-3567 Ringer Albert 801 826-0752 67 Seventh Av. Salt Lake City UT USA 84152 ==================== Lava Operator Tree ==================== Emit (VA = 1) 23 rows est: 23 cpu: 0 / TableScan authors (VA = 0) 23 rows est: 23 lio: 1 est: 2 pio: 0 est: 2 ============================================================ (23 rows affected)
set statistics plan_directory_html "/usr/myDir/HTML" go set statistics plan_directory_html on go
set string_rtruncation on
string data, right truncation
set textsize 100
set switch on print_minlogged_mode_override go
Switch 'print_minlogged_mode_override' is turned on. All supplied switches are successfully turned on.
set switch serverwide on 110 with override, no_info
set tracefile '/var/sybase/REL1502/text_dir/sql_text_file'
Subsequent outputs from set showplan, set statistics io, and dbcc traceon(100) are saved in sql_text_file.
set tracefile 'sql_text_file' for 11
Any SQL run on spid 11 is saved to this tracefile.
set tracefile '/var/sybase/REL1502/text_dir/sql_text_file' for 86
set transactional_rpc on
set transaction isolation level 2
set transaction isolation level 3
isql -Ubob -Pbob123 use myimdb
create table tab1(col1 int)
set show_exec_info on set dml_login minimal
insert into tab1 values(1)
Operating on the table 'tab1', database 'myimdb' (owner ID 3) in 'minimal' logging mode by user ID 3.
set dml_logging default
insert into tab1 values(1)
Operating on the table 'tab1', database 'myimdb' (owner ID 3) in 'full' logging mode by user ID 3.
fipsflagger, string_rtruncation, ansinull, ansi_permissions, arithabort, and arithignore affect aspects of SAP ASE error handling and compliance to SQL standards.
You can use the cis_rpc_handling and transactional_rpc options only when CIS is enabled.
The async log service option and delayed_commit are mutually exclusive. delayed_commit does not work if async log service is set to “true.”
parallel_degree and scan_parallel_degree limit the degree of parallelism for queries, if the SAP ASE server is configured for parallelism. When you use these options, you give the optimizer a hint to limit parallel queries to use fewer worker processes than allowed by the configuration parameters. Setting these parameters to 0 restores the server-wide configuration values.
If you specify a number that is greater than the numbers allowed by the configuration parameters, the SAP ASE server issues a warning message and uses the value set by the configuration parameter.
If you use the set command inside a trigger or stored procedure, most set options revert to their former settings after the trigger or procedure executes.
datefirst
dateformat
identity_insert
language
quoted_identifier
If you specify more than one set option, the first syntax error causes all following options to be ignored. However, the options specified before the error are executed, and the new option values are set.
If you assign a user a client name, host name, or application name, these assignments are only active for the current session. You must reassign these the next time the user logs in. Although the new names appear in sysprocesses, they are not used for permission checks, and sp_who still shows the client connection as belonging to the original login. For more information about setting user processes, see the System Administration Guide.
set showplan on select * from publishers go pub_id pub_name city state ------- --------------------- ----------- --- 0736 New Age Books Boston MA 0877 Binnet & Hardley Washington DC 1389 Algodata Infosystems Berkeley CA (3 rows affected)
set showplan on go select * from publishers go
QUERY PLAN FOR STATEMENT 1 (at line 1). STEP 1 The type of query is SELECT FROM TABLE publishers Nested iteration Table Scan Ascending Scan. Positioning at start of table. pub_id pub_name city state ------ -------------------- ---------- ---- 0736 New Age Books Boston MA 0877 Binnet & Hardley Washington DC 1389 Algodata Infosystems Berkeley CA (3 rows affected)
The SAP ASE server automatically stores one or more spaces in clientname, clienthostname, and clientapplname columns. For this reason, a query using any of these three columns that includes “is null” does not return an expected result set.
SQL statement on line number 1 contains Non-ANSI text. The error is caused due to the use of DBCC.
If you use a login trigger to set current execution properties, any exportable set option that you enable or disable inside a login trigger takes affect in the current process.
convert in Reference Manual: Building Blocks
isql, optdiag in the Utility Guide
sp_setrepdbmode, sp_setrepdefmode in Reference Manual: Procedures
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:
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.
Values in event and extrainfo columns of sysaudits are:
| Information | Values |
|---|---|
Event | 88 |
Audit option | security |
Command or access audited | set proxy or set session authorization |
Information in extrainfo |
|