SET SESSION

The SET SESSION management statement establishes SQL session characteristics. Using the SET SESSION statement, you can perform the following tasks:
idmscu19
The SET SESSION management statement establishes SQL session characteristics. Using the SET SESSION statement, you can perform the following tasks:
  • Specify whether subsequent SQL statements must comply with a particular SQL standard
  • Change the current schema in effect for the SQL session
  • Establish default transaction options
  • Control SQL dynamic statement caching
  • Specify the encoding of XML values
  • Specify the maximum number of rows processed by the database engine for each SQL statement
These session characteristics apply only to SQL submitted through the Command Facility or for dynamic compilation during the execution of an application program.
A SET SESSION statement must include at least one parameter and is a CA IDMS extension of the SQL standard.
Authorization
None required.
 
Syntax
►►─── SET SESSION ────────────────────────────────────────────────────────────► ┌──────────────────────┐ ►─────▼─
session-attribute
──┴───────────────────────────────────────────────►◄
 
Expansion of session-attribute
 
►──┬─ CHECK SYNTAX ─┬─ SQL89 ────────┬────────────────────────────────┬──────►◄ │ ├─ FIPS ─────────┤ │ │ └─ EXTENDED ─────┘ │ │ │ ├─ CURRENT SCHEMA ─┬─
schema-name
──┬──────────────────────────────┤ │ └─ NULL ─────────┘ │ │ │ ├─┬─ CURSOR STABILITY ─┬───────────────────────────────────────────┤ │ └─ TRANSIENT READ ───┘ │ │ │ ├─┬─ READ ONLY ──┬─────────────────────────────────────────────────┤ │ └─ READ WRITE ─┘ │ │ │ ├─ SQL CACHING ─┬─ ON ────────┬─────────────────────────────────┤ │ ├─ OFF ─────────┤ │ │ └─ DEFAULT ◄─────┘ │ │ │ ├─SQL ROW LIMIT ───
count
──
──
─────────────────────────────────────
│ │ │ └─ XML ENCODING ─┬─ UTF8 ───────┬──────────────────────────────────┘ ├─ UTF16BE ────┤ ├─ UTF16LE ────┤ └─ EBCDIC ◄────┘
Parameters
Parameters for Expansion of session-attribute
 
  • CHECK SYNTAX
    Specifies whether CA IDMS is to check subsequent SQL statements for compliance with a particular standard.
    If CHECK SYNTAX is not specified, SQL statements are checked for compliance with CA IDMS Extended SQL.
  • SQL89
    Directs CA IDMS to use ANSI X3.135-1989 (Rev), 
    Database Language SQL with integrity enhancement
    , as the standard for compliance.
  • FIPS
    Directs CA IDMS to use FIPS PUB 127-1, 
    Database Language SQL
    , as the standard for compliance.
     The FIPS standard is based on ANSI X3.135-1989 (Rev). Specifying FIPS in the CHECK SYNTAX parameter has the same effect as specifying SQL89.
  • EXTENDED
    Directs CA IDMS to check subsequent SQL statements for compliance with CA IDMS Extended SQL.
  • CURRENT SCHEMA
    Changes the default schema specification for the SQL session.
  • schema-name
     
    Specifies a schema to be used as the default for the SQL session. The specified schema overrides the default in effect for the user session.
  • NULL
    Directs CA IDMS to use the default schema in effect for the user session as the default for the SQL session.
  • CURSOR STABILITY/TRANSIENT READ
    Directs CA IDMS to set the default isolation level to that specified.
  • READ ONLY/READ WRITE
    Directs CA IDMS to set the default transaction mode to that specified.
  • SQL CACHING
    Enables you to control dynamic SQL statement caching.
    • ON
      If SQL caching is globally enabled, the session will use caching until the session option is changed or until the caching is disabled at the system level.
    • OFF
      Regardless of the global setting of SQL caching, the session will not use caching until the session option is changed.
    • DEFAULT
      Same as ON.
  • SQL ROW LIMIT
    Specifies the maximum number of rows that can be read or updated by a single SQL statement. Includes rows that are processed internally, for example by a sort or an aggregate function, not only the number of rows updated or returned to the user. The calls to a table procedure or procedure are also counted toward the limit. SQL ROW LIMIT can be used to limit database I/O activity.
    Limits:
     0 to 2,147,483,647. A value of zero indicates there is no limit.
  • XML ENCODING
    Specifies the type of encoding to use for XML values.
    XML ENCODING remains valid until the end of session or until a new SET SESSION command is executed.
    • UTF8
      Specifies UTF-8 Unicode encoding.
    • UTF16BE
      Specifies UTF-16 Big Endian Unicode encoding.
    • UTF16LE
      Specifies UTF-16 Little Endian Unicode encoding.
    • EBCDIC
      Specifies EBCDIC encoding. This is the default.
Usage
Default Schema for a User Session
 
The default schema in effect for a user session is established by a user profile, a system profile, or a DCUF SET PROFILE command.
Duration of SQL Session Characteristics
 
The SQL session characteristics established by the SET SESSION statement remain in effect until the end of the SQL session or until changed by a subsequent SET SESSION statement.
Precompiled Statements
 
The SET SESSION command does not cause CA IDMS to check precompiled SQL statements. Use the corresponding precompiler option to enable standards checking for embedded SQL statements.
Establishing Default Transaction Options
 
You can establish default transaction options for an SQL session using the SET SESSION statement. You can establish the default mode in which a database is accessed (READ ONLY or READ WRITE) and specify an isolation level (CURSOR STABILITY or TRANSIENT READ).
If you do not specify either of these options, the defaults are READ WRITE and CURSOR STABILITY, or the settings specified as part of the access module definition for embedded SQL. The default options may be overridden for an individual transaction by using the SET TRANSACTION statement.
If a transaction is active at the time these options are changed, they impact only subsequent transactions.
For more information about transaction mode and isolation level, see CREATE ACCESS MODULE.
SQL Row Limit
 
The SQL Row Limit is an option to limit the number of rows that can be read or updated by a single SQL statement, which includes rows that are processed internally, for example by a sort or an aggregate function.
The following items are also counted toward the limit:
  • The calls to a table procedure passed with the following operation codes: next row, update row, delete row, or insert row
  • The call to a procedure
Any DML executed by a table procedure or procedure is not counted toward the limit.
SQL Row Limit is more powerful than standard row count options because it can be used to limit additional database I/O activity, rather than only limit the number of rows returned to the user in a result set. SQL Row Limit differs from other CA IDMS limits as it is enforced at the statement level by the SQL engine and provides a recoverable error message instead of task abend. Resource Limit Exit (Exit 20) does not get invoked. 
The SQL Row Limit is an attribute of the SQL session. It can be set on the SYSTEM statement and overridden on the TASK statement in the SYSGEN and passed down to the session. Specifying the SQL Row Limit on the SET SESSION overrides both the SYSTEM and TASK settings. Specifying the SQL Row Limit on the SET SESSION overrides both the SYSTEM and TASK settings and allows a lower limit to be used for a session. If an attempt is made to specify an SQL Row Limit that exceeds the limit for the system or task, an error message displays and the limit for the current session is not updated. 
 When using a cursor, the row limit applies to the cursor statement and is incremented for each fetch, positioned update and positioned delete. 
Examples
Checking Compliance with SQL Standard
 
The following SET SESSION statement which is embedded in an application program, directs CA IDMS to flag any subsequent statements submitted for dynamic compilation that do not comply with SQL standard 89:
EXEC SQL SET SESSION CHECK SYNTAX SQL89 END-EXEC
Setting a Default Schema
 
The following SET SESSION statement (submitted through the Command Facility) directs CA IDMS to use the SALES_SCH schema as the default schema for the remainder of the SQL session:
set session current schema sales_sch;
Encoding XML Values
The following examples illustrate EBCDIC and Unicode encoding.
Example 1 - EBCDIC encoding
 
set session XML ENCODING ebcdic ; select cast(SLICE as BIN (27)) as EBCDIC from SYSCA.XMLSLICE where SLICESIZE = 27 and XMLVALUE = XMLCOMMENT(' 0123456789ABCDEF ');
The result looks like this:
*+ EBCDIC *+ ------ *+ 4C5A60604040F0F1F2F3F4F5F6F7F8F9C1C2C3C4C5C6404060606E
 
Example 2 - UTF-8 encoding
 
set session XML ENCODING UTF8 ; *+ Status = 0 SQLSTATE = 00000 select cast(SLICE as BIN (27)) as "UTF-8" from SYSCA.XMLSLICE where SLICESIZE = 27 and XMLVALUE = XMLCOMMENT(' 0123456789ABCDEF ');
The result looks like this:
*+ UTF-8 *+ ----- *+ 3C212D2D20203031323334353637383941424344454620202D2D3E
 
Example 3 - UTF-16 Big Endian encoding
 
set session XML ENCODING UTF16BE ; *+ Status = 0 SQLSTATE = 00000 select cast(SLICE as BIN (27)) as "UTF-16 BE" from SYSCA.XMLSLICE where SLICESIZE = 27 and XMLVALUE = XMLCOMMENT(' 0123456789ABCDEF ');
The result looks like this:
*+ UTF-16 BE *+ --------- *+ 003C0021002D002D00200020003000310032003300340035003600 *+ 370038003900410042004300440045004600200020002D002D003E
 
Example 4 - UTF-16 Little Endian encoding
 
set session XML ENCODING UTF16LE ; *+ Status = 0 SQLSTATE = 00000 select cast(SLICE as BIN (27)) as "UTF-16 LE" from SYSCA.XMLSLICE where SLICESIZE = 27 and XMLVALUE = XMLCOMMENT(' 0123456789ABCDEF ');
The result looks like this:
*+ UTF-16 LE *+ --------- *+ 3C0021002D002D0020002000300031003200330034003500360037 *+ 0038003900410042004300440045004600200020002D002D003E00