Global variables are system-defined variables that are updated by the SAP ASE server while the system is running.
Some global variables are session-specific, while others are server instance-specific. For example, @@error contains the last error number generated by the system for a given user connection.
To specify application context variables, use get_appcontext and set_appcontext.
select variable_name
select @@char_convert
Many global variables report on system activity occurring from the last time the SAP ASE server was started. sp_monitor displays the current values of some of the global variables.
Global Variable | Definition |
---|---|
@@active_instances | Returns the number of active instances in the cluster |
@@authmech | A read-only variable that indicates the mechanism used to authenticate the user. |
@@bootcount | Returns the number of times an SAP ASE server installation has been started. |
@@boottime | Returns the date and time the SAP ASE server was last started. |
@@bulkarraysize | Returns the number of rows to be buffered in local server memory before being transferred using the bulk copy interface Used only with Component Integration Services for transferring rows to a remote server using select into. See the Component Integration Services User’s Guide. |
@@bulkbatchsize | Returns the number of rows transferred to a remote server via select into proxy_table using the bulk interface. Used only with Component Integration Services for transferring rows to a remote server using select into. See the Component Integration Services User’s Guide. |
@@char_convert | Returns 0 if character set conversion is not in effect. Returns 1 if character set conversion is in effect. |
@@cis_rpc_handling | Returns 0 if cis rpc handling is off. Returns 1 if cis rpc handling is on. See the Component Integration Services User’s Guide. |
@@cis_version | Returns the date and version of Component Integration Services. |
@@client_csexpansion | Returns the expansion factor used when converting from the server character set to the client character set. For example, if it contains a value of 2, a character in the server character set could take up to twice the number of bytes after translation to the client character set. |
@@client_csid | Returns -1 if the client character set has never been initialized; returns the client character set ID from syscharsets for the connection if the client character set has been initialized. |
@@client_csname | Returns NULL if client character set has never been initialized; returns the name of the character set for the connection if the client character set has been initialized. |
@@clusterboottime | Returns the date and time the cluster was first started, even if the instance that originally started the cluster start has shut down. |
@@clustercoordid | Returns the instance id of the current cluster coordinator. |
@@clustermode | Returns the string: “shared-disk cluster”. |
@@clustername | Returns the name of the cluster. |
@@cmpstate | Returns the current mode of the SAP ASE server in a high availability environment. Not used in a non-high availability environment. |
@@connections | Returns the number of user logins attempted. |
@@cpu_busy | Returns the amount of time, in ticks, that the CPU has spent doing SAP ASE work since the last time the SAP ASE server was started. The value of @@user_busy + @@system_busy should equal the value of @@cpu_busy. |
@@cursor_rows | A global variable designed specifically for scrollable cursors. Displays the total
number of rows in the cursor result set. Returns:
|
@@curloid | Returns the curent session’s lock owner ID. |
@@datefirst | Set using set datefirst n where n is a value between 1 and 7. Returns the current value of @@datefirst, indicating the specified first day of each week, expressed as tinyint. The default value in the SAP ASE server is Sunday (based on the us_language default), which you set by specifying set datefirst 7. See the datefirst option of the set command for more information on settings and values. |
@@dbts | Returns the timestamp of the current database. Timestamp columns always display values in big-endian byte order, but on
little-endian platforms, @@dbts is displayed in little-endian
byte order. To convert a little-endian @@dbts value to a
big-endian value that can be compared with timestamp column values,
use:
reverse(substring(@@dbts,1,2)) + 0x0000 + reverse(substring(@@dbts,5,4)) |
@@error | Returns the error number most recently generated by the system. The @@error global variable is commonly used to check the error status of the most recently executed batch in the current user session. @@error contains 0 if the last transaction succeeded; otherwise, @@error contains the last error number generated by the system. @@error is not set for severity level 10 messages. |
@@errorlog | Returns the full path to the directory in which the SAP ASE server error log is kept, relative to $SYBASE directory (%SYBASE% on Windows). |
@@failedoverconn | Returns a value greater than 0 if the connection to the primary companion has failed over and is executing on the secondary companion server. Used only in a high availability environment, and is session-specific. |
@@fetch_status | Returns:
|
@@guestuserid | Returns the ID of the guest user. |
@@hacmpservername | Returns the name of the companion server in a high availability setup. |
@@haconnection | Returns a value greater than 0 if the connection has the failover property enabled. This is a session-specific property. |
@@heapmemsize | Returns the size of the heap memory pool, in bytes. See the System Administration Guide for more information on heap memory. |
@@identity | Returns the most recently generated IDENTITY column value. |
@@idle | Returns the amount of time, in ticks, that the SAP ASE server has been idle since it was last started. |
@@instanceid | Returns the ID of the instance from which it was executed. |
@@instancename | Returns the name of the instance from which it was executed. |
@@invaliduserid | Returns a value of -1 for an invalid user ID. |
@@io_busy | Returns the amount of time, in ticks, that the SAP ASE server has spent doing input and output operations. |
@@isolation | Returns the value of the session-specific isolation level (0, 1, or 3) of the current Transact-SQL program. |
@@jsinstanceid | ID of the instance on which the Job Scheduler is running, or run once enabled. |
@@kernel_addr | Returns the starting address of the first shared memory region that contains the kernel region. The result is in the form of 0xaddress pointer value. |
@@kernel_size | Returns the size of the kernel region that is part of the first shared memory region. |
@@kernelmode | Returns the mode (threaded or process) for which the SAP ASE server is configured. |
@@langid | Returns the server-wide language ID of the language in use, as specified in syslanguages.langid. |
@@language | Returns the name of the language in use, as specified in syslanguages.name. |
@@lastkpgendate | Returns the date and time of when the last key pair was generated as set by sp_passwordpolicy’s “keypair regeneration period” policy option. |
@@lastlogindate | Available to each user login session, @@lastlogindate includes a datetime datatype, its value is the lastlogindate column for the login account before the current session was established. This variable is specific to each login session and can be used by that session to determine the previous login to the account. If the account has not been used previously or “sp_passwordpolicy 'set', enable last login updates” is 0, then the value of @@lastlogindate is NULL. |
@@lock_timeout | Set using set lock wait n. Returns the current lock_timeout setting, in milliseconds. @@lock_timeout returns the value of n. The default value is no timeout. If no set lock wait n is executed at the beginning of the session, @@lock_timeout returns -1. |
@@lwpid | Returns the object ID of the next most recently run lightweight procedure. |
@@max_connections | Returns the maximum number of simultaneous connections that can be made with the SAP ASE server in the current computer environment. You can configure the SAP ASE server for any number of connections less than or equal to the value of @@max_connections with the number of user connections configuration parameter. |
@@max_precision | Returns the precision level used by decimal and numeric datatypes set by the server. This value is a fixed constant of 38. |
@@maxcharlen | Returns the maximum length, in bytes, of a character in the SAP ASE server's default character set. |
@@maxgroupid | Returns the highest group user ID. The highest value is 1048576. |
@@maxpagesize | Returns the server’s logical page size. |
@@maxspid | Returns maximum valid value for the spid. |
@@maxsuid | Returns the highest server user ID. The default value is 2147483647. |
@@maxuserid | Returns the highest user ID. The highest value is 2147483647. |
@@maxvarlen | Returns the maximum possible variable length allowed for a user-defined datatype. |
@@mempool_addr | Returns the global memory pool table address. The result is in the form 0xaddress pointer value. This variable is for internal use. |
@@min_poolsize | Returns the minimum size of a named cache pool, in kilobytes. It is calculated based on the DEFAULT_POOL_SIZE, which is 256, and the current value of max database page size. |
@@mingroupid | Returns the lowest group user ID. The lowest value is 16384. |
@@minspid | Returns 1, which is the lowest value for spid. |
@@minsuid | Returns the minimum server user ID. The lowest value is -32768. |
@@minuserid | Returns the lowest user ID. The lowest value is -32768. |
@@monitors_active | Reduces the number of messages shown by sp_sysmon. |
@@ncharsize | Returns the maximum length, in bytes, of a character set in the current server default character set. |
@@nestlevel | Returns the current nesting level. |
@@nextkpgendate | Returns the date and time of when the next key pair scheduled to be generated, as set by sp_passwordpolicy’s “keypair regeneration period” policy option. |
@@nodeid | Returns the current installation's 48-bit node identifier. The SAP ASE server generates a nodeid the first time the master device is first used, and uniquely identifies an SAP ASE installation. |
@@optgoal | Returns the current optimization goal setting for query optimization. |
@@optoptions | Returns a bitmap of active options. |
@@options | Returns a hexadecimal representation of the session’s set options. |
@@optlevel | Returns the currently optimization level setting. |
@@opttimeoutlimit | Returns the current optimization timeout limit setting for query optimization |
@@ospid | (Threaded mode only) Returns the operating system ID for the server. |
@@pack_received | Returns the number of input packets read by the SAP ASE server. |
@@pack_sent | Returns the nmber of output packets written by the SAP ASE server. |
@@packet_errors | Returns the number of errors detected by the SAP ASE server while reading and writing packets. |
@@pagesize | Returns the server’s virtual page size. |
@@parallel_degree | Returns the current maximum parallel degree setting. |
@@plwpid | Returns the object ID of the most recently prepared lightweight procedure. |
@@probesuid | Returns a value of 2 for the probe user ID. |
@@procid | Returns the stored procedure ID of the currently executing procedure. |
@@quorum_physname | Returns the physical path for the quorum device |
@@recovery_state | Indicates whether the SAP ASE server is in recovery based on these returns:
|
@@remotestate | Returns the current mode of the primary companion in a high availability environment. For values returned, see Using Failover in a High Availability Environment. |
@@repartition_degree | Returns the current dynamic repartitioning degree setting. |
@@resource_granularity | Returns the maximum resource usage hint setting for query optimization. |
@@rowcount | Returns the number of rows affected by the last query. The value of @@rowcount is affected by whether the specified cursor is forward-only or scrollable. If the cursor is the default, non-scrollable cursor, the value of @@rowcount increments one by one, in the forward direction only, until the number of rows in the result set are fetched.These rows are fetched from the underlying tables to the client. The maximum value for @@ rowcount is the number of rows in the result set. In the default cursor, @@rowcount is set to 0 by any command that does not return or affect rows, such as an if or set command, or an update or delete statement that does not affect any rows. If the cursor is scrollable, there is no maximum value for @@rowcount. The value continues to increment with each fetch, regardless of direction, and there is no maximum value. The @@rowcount value in scrollable cursors reflects the number of rows fetched from the result set, not from the underlying tables, to the client. |
@@scan_parallel_degree | Returns the current maximum parallel degree setting for nonclustered index scans. |
@@servername | Returns the name of the SAP ASE server. |
@@setrowcount | Returns the current value for set rowcount. |
@@shmem_flags | Returns the shared memory region properties. This variable is for internal use. There are a total of 13 different properties values corresponding to 13 bits in the integer. The valid values represented from low to high bit are: MR_SHARED, MR_SPECIAL, MR_PRIVATE, MR_READABLE, MR_WRITABLE, MR_EXECUTABLE, MR_HWCOHERENCY, MR_SWCOHERENC, MR_EXACT, MR_BEST, MR_NAIL, MR_PSUEDO, MR_ZERO. |
@@spid | Returns the server process ID of the current process. |
@@sqlstatus | Returns status information (warning exceptions) resulting from the execution of a fetch statement. |
@@ssl_ciphersuite | Returns NULL if SSL is not used on the current connection; otherwise, it returns the name of the cipher suite you chose during the SSL handshake on the current connection. |
@@stringsize | Returns the amount of character data returned from a toString() method. The default is 50. Max values may be up to 2GB. A value of zero specifies the default value. See the Component Integration Services User’s Guide for more information. |
@@sys_tempdbid | Returns the database ID of the executing instance’s effective local system temporary databas.e |
@@system_busy | Number of ticks during which the SAP ASE server was running a system task. The value of @@user_busy + @@system_busy should equal the value of @@cpu_busy. |
@@system_view | Returns the session-specific system view setting, either “instance” or “cluster.” |
@@tempdbid | Returns a valid temporary database ID (dbid) of the session’s assigned temporary database. |
@@textcolid | Returns the column ID of the column referenced by @@textptr. |
@@textdataptnid | Returns the partition ID of a text partition containing the column referenced by @@textptr. |
@@textdbid | Returns the database ID of a database containing an object with the column referenced by @@textptr. |
@@textobjid | Returns the object ID of an object containing the column referenced by @@textptr. |
@@textptnid | Returns the partition ID of a data partition containing the column referenced by @@textptr. |
@@textptr | Returns the text pointer of the last text, unitext, or image column inserted or updated by a process (Not the same as the textptr function). |
@@textptr_parameters | Returns 0 if the current status of the textptr_parameters configuration parameter is off. Returns 1 if the current status of the textptr_parameters if on. See the Component Integration Services User’s Guide for more information. |
@@textsize | Returns the limit on the number of bytes of text, unitext, or image data a select returns. Default limit is 32K bytes for isql; the default depends on the client software. Can be changed for a session with set textsize. |
@@textts | Returns the text timestamp of the column referenced by @@textptr. |
@@thresh_hysteresis | Returns the decrease in free space required to activate a threshold. This amount, also known as the hysteresis value, is measured in 2K database pages. It determines how closely thresholds can be placed on a database segment. |
@@timeticks | Returns the number of microseconds per tick. The amount of time per tick is machine-dependent. |
@@total_errors | Returns the number of errors detected by the SAP ASE server while reading and writing. |
@@total_read | Returns the number of disk reads by the SAP ASE server. |
@@total_write | Returns the number of disk writes by the SAP ASE server. |
@@tranchained | Returns 0 if the current transaction mode of the Transact-SQL program is unchained. Returns 1 if the current transaction mode of the Transact-SQL program is chained. |
@@trancount | Returns the nesting level of transactions in the current user session. |
@@tranrollback | Returns the type of rollback encountered, if any. If the return value is:
SAP ASE does not change a negative value for @@tranrollback
until the next rollback tran or commit tran is
issued, indicating that the session has encountered an implicit transaction rollback.
SAP ASE resets the value for @@tranrollback to 0 once it
successfully applies the next rollback tran or commit
tran. The value for @@tranrollback is 0 at the end of
this
example:
set chained on go <... Execute a DML statement ...> if (@@error != 0) and (@@tranrollback < 0) begin rollback tran end go |
@@transactional_rpc | Returns 0 if RPCs to remote servers are transactional. Returns 1 if RPCs to remote servers are not transactional. See enable xact coordination and set option transactional_rpc in the Reference Manual. Also, see the Component Integration Services User’s Guide. |
@@transtate | Returns the current state of a transaction after a statement executes in the current user session. |
@@trigger_name | Returns the name of the trigger currently executing. |
@@unicharsize | Returns 2, the size of a character in unichar. |
@@user_busy | Number of ticks during which the SAP ASE server was running a user task The value of @@user_busy + @@system_busy should equal the value of @@cpu_busy |
@@version | Returns the date, version string, and so on of the current release of the SAP ASE server. |
@@version_as_integer | Returns the number of the last upgrade version of the current release of the SAP ASE server as an integer. For example, @@version_as_integer returns 12500 if you are running SAP ASE version 12.5, 12.5.0.3, or 12.5.1. |
@@version_number | Returns the entire version of the current release of the SAP ASE server as an integer. |