Global variables - How to create your own

Quite often developers or DBA's ask if it is possible to set your own global variables in Sybase. In short, the answer to this question is no and yes, depending on the way you look at the solution to this problem. No, you cannot set your own global variable like @@fiscal_year, but yes, there is a reasonable alternative.

This document describes a method to implement your own global variables using application context functions.

Application context functions

The Application context functions are not a commonly known feature in Sybase ASE, they were introduced in version 12.5. These functions allow you to store a limited amount of data in memory and you can access the data with SQL. This feature was primarily designed to support row-based security but it can also be used for global variables.

Overview of the functions

The application context functions allow you to store data per context and attribute name combination. The following functions can be used:

To set a value

set_appcontext("<context name>", "<attribute name>", "<value>")

Sets a value for the combination of context and attribute name.

To retrieve a value

get_appcontext("<context name>", "<attribute name>")

Retrieves a value for the combination of context and attribute name. When the value has not previously been set a null value is returned.

To remove a value

rm_appcontext("<context name>", "<attribute name>")

Removes the value for the combination of context and attribute name.

List values

list_appcontext(["<context name>"]), list the values set. Context name is an optional parameter.

Both context and attribute name are of a char(30) datatype. Sybase documents a datatype char(2048) for the value parameter, however even with 12.5.2 you can only use 30 bytes. The functions set_appcontext and rm_appcontext return 0 upon successful execution or -1 when an error occurs.

A reserved context name is "sys_session", this value is used by Sybase.

Example

An example of a global variable could be "fiscal year". We associate this variable with the application "Accounting". A value for fiscal year can then be set with:

select set_appcontext("Accounting","fiscal year","2004")  

The retrieval of it can be done with

select get_appcontext("Accounting","fiscal year")  

The function get_appcontext returns the fiscal year in a datatype char(30), so you should specify an explicit convert to another datatype (e.g. int) when needed.

Population of the initial values can be done with, for instance, a login-trigger or with a stored procedure that is a part of the application. An advantage of a login-trigger is the immediate availability of the required values of the global variables at the start of the session.

Once the data is set, it is available throughout the session, also in commands started with the execute command. An application context is only accessible per server process (spid) so each spid should populate its own set of global variables. This a major distinction from real global variables (the ones starting with @@).

Cross-spid synchronisation

Since application context values are only accessible for the spid itself, the synchronisation of a change in a value across different spids is difficult to accomplish. For instance, when at some point in time the fiscal year changes from 2004 into 2005, all spids should be notified of this change at the same time. One possibility is to force a reconnect for all sessions so the new values will be stored in the global variables. However, this is a rather inconvenient method especially when a variable changes often in value. This problem can be resolved with the implementation of a Java class within ASE that should be able to handle the application context values and an embedded mechanism that handles changes to the value.

The need to grant

By default, the application context functions are not granted to a login so at least the functions set_appcontext (to set a variable) and get_appcontext (to retrieve a variable) should explicitly be granted to a login. When you grant these functions this must be done in the default database for that particular login. This can be cumbersome since the default database of a login can change at some point in time and the permissions become inaccessible when a change is made to the default database. A preferred method is to grant these functions to a user defined role and grant the role to the login.

Some technical notes

Application context data are stored in the procedure cache. When there is not enough space in the procedure cache you will see error 701 in the errorlog.

Application context data is stored in memory and accessed sequentially. When you store very large amounts of data as application context you will see some degraded performance at the end of the heap. According to Sybase documentation the value for an application context can be a varchar(2048). However, my own tests only worked with values up to 30 bytes in length, even with 12.5.2. No additional licensing is needed.