Set default mode for a session.
The default mode specified is set for the current connection and remains until the connection is closed.
SET SESSION READ
SET SESSION READoption allows the default
SET TRANSACTION READsetting to be defined.
SET TRANSACTION READstatement only affects the single next transaction to be started after it has been used.
SET TRANSACTION READsetting is normally
READ WRITE, however,
SET SESSION READcan be used to set whichever default is desired for the current session.
SET SESSION ISOLATION LEVEL
SET SESSION ISOLATION LEVELoption allows the default
SET TRANSACTION ISOLATION LEVELsetting to be defined.
SET TRANSACTION ISOLATION LEVELstatement only affects the single next transaction to be started after it has been used.
SET TRANSACTION ISOLATION LEVELsetting is normally
REPEATABLE READ, however,
SET SESSION ISOLATION LEVELcan be used to set whichever default is desired for the current session.
SET SESSION ISOLATION LEVEL READ UNCOMMITTEDis specified, then a default transaction access mode of
READ ONLYis implicit. I.e. transactions performing updates are not allowed unless a
SET TRANSACTIONstatement changing this default is specified before doing such a transaction.
SET SESSION DIAGNOSTICS SIZE
SET SESSION DIAGNOSTICS SIZEoption allows the default size of the diagnostics area to be defined. The
unsigned-integervalue specifies how many exceptions can be stacked in the diagnostics area, and examined by
GET DIAGNOSTICS, in situations where repeated
RESIGNALoperations have effectively been performed. The default size is 50.
SET SESSION FETCH SIZE
SET SESSION FETCH SIZEoption 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.
SET SESSIONstatement may not be issued within a transaction.
SET SESSION READsetting or a
SET SESSION ISOLATION LEVELsetting may not be changed if there are any holdable cursors remaining open from the previous transaction.
Set the default transaction isolation level to repeatable read:SET SESSION 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.
Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40