SET SESSION

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

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 ◄─────┘                                 │
   └─ 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.

Note: 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.

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.

Note: For more information about transaction mode and isolation level, see CREATE ACCESS MODULE.

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

More Information

For more information about CA IDMS compliance with SQL standard SQL, see Summary Comparison to SQL Standard.
For more information about user profiles, see the CA IDMS Security Administration Guide.
For more information about system profiles, see the CA IDMS System Tasks and Operator Commands Guide.
For more information about the DCUF SET PROFILE command, see the CA IDMS System Tasks and Operator Commands Guide.
For more information about managing SQL sessions, see the CA IDMS SQL Programming Guide.
For more information about precompiler options, see the CA IDMS SQL Programming Guide.
For more information about dynamic SQL statement caching, see SQL Cache Tables, and the CA IDMS SQL Programming Guide.