SET SESSION

---

Set default mode for a session.

Usage

Embedded/Interactive/Procedural.

Description

The default mode specified is set for the current connection and remains until the connection is closed.

SET SESSION READ

The SET SESSION READ option allows the default SET TRANSACTION READ setting to be defined.

The SET TRANSACTION READ statement only affects the single next transaction to be started after it has been used.

The default SET TRANSACTION READ setting is normally READ WRITE, however, SET SESSION READ can be used to set whichever default is desired for the current session.

SET SESSION ISOLATION LEVEL

The SET SESSION ISOLATION LEVEL option allows the default SET TRANSACTION ISOLATION LEVEL setting to be defined.

The SET TRANSACTION ISOLATION LEVEL statement only affects the single next transaction to be started after it has been used.

The default SET TRANSACTION ISOLATION LEVEL setting is normally REPEATABLE READ, however, SET SESSION ISOLATION LEVEL can be used to set whichever default is desired for the current session.

If SET SESSION ISOLATION LEVEL READ UNCOMMITTED is specified, then a default transaction access mode of READ ONLY is implicit. I.e. transactions performing updates are not allowed unless a SET TRANSACTION statement changing this default is specified before doing such a transaction.

SET SESSION DIAGNOSTICS SIZE

The SET SESSION DIAGNOSTICS SIZE option allows the default size of the diagnostics area to be defined. The unsigned-integer value specifies how many exceptions can be stacked in the diagnostics area, and examined by GET DIAGNOSTICS, in situations where repeated RESIGNAL operations have effectively been performed. The default size is 50.

SET SESSION FETCH SIZE

The SET SESSION FETCH SIZE option allows for Embedded SQL (ESQL) programmers to provide hints about an appropriate block cursor size. ESQL applications will now, whenever possible, fetch result rows in blocks from the server. In effect this means that ESQL, whenever the application wants to fetch more data, transfers a number of rows from the server at once and store these in an internal buffer. Future fetches will read directly from the internal buffer until it is exhausted, when a new block of rows are requested from the server.

In most cases, this has a positive effect on performance, applications will communicate less with the server and thus improving its scalability. Communication overheads are also reduced. There are, however, a few cases when this might be detrimental to performance. One situation might be when one want the first result row as fast as possible, while there can take some time for the server to complete an entire block request. In these situations ESQL programmers may change the block fetch behavior with the session attribute FETCH SIZE. This attribute will provide a hint about a suitable fetch size, that is the number of rows to fetch in each block, to ESQL. ESQL, will whenever possible try to use the specified fetch size, but it may in practice use a fetch size smaller than specified. If the value is zero, the hint is ignored. The default value is zero.

Restrictions

The SET SESSION statement may not be issued within a transaction.

A SET SESSION READ setting or a SET SESSION ISOLATION LEVEL setting may not be changed if there are any holdable cursors remaining open from the previous transaction.

Examples

Set the default transaction isolation level to repeatable read:

 SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL REPEATABLE READ;
 

Ensure that rows are transferred one at a time from the server:

 exec sql SET SESSION FETCH SIZE 1;
 

Set the fetch block size to 24:

 exec sql BEGIN DECLARE SECTION;
 long fetch_size;
 exec sql END DECLARE SECTION;
 ...
 fetch_size = 24;
 exec sql SET SESSION FETCH SIZE :fetch_size;
 

For more information, see the Mimer SQL Programmer's Manual, Transaction Handling and Database Security.

Standard Compliance

Standard	Compliance	Comments
SQL-2011	Features outside core	F761, "Session management". F111, "Isolation levels other than SERIALIZABLE".
	Mimer SQL extension	Optional CHARACTERISTICS AS TRANSACTION syntax is a Mimer SQL extension.

Mimer Information Technology AB Voice: +46 18 780 92 00 Fax: +46 18 780 92 40 info@mimer.se