Set

Valid in: SQL, ESQL

The Set statement specifies a runtime option for the current session. The selected option remains in effect until the session is terminated or the option is changed using another set statement.

Note: This statement has additional considerations when used in a distributed environment. For more information, see the Ingres Star User Guide.

Syntax

The Set statement has the following format:

[EXEC SQL] SET AUTOCOMMIT ON|OFF

[EXEC SQL] SET CONNECTION connection_name

[EXEC SQL] SET CONNECTION NONE

[EXEC SQL] SET JOINOP [NO]TIMEOUT

[EXEC SQL] SET JOINOP [NO]GREEDY

[EXEC SQL] SET JOURNALING|NOJOURNALING [ON table_name]

[EXEC SQL] SET RESULT_STRUCTURE

              HEAP|CHEAP|HEAPSORT|CHEAPSORT|HASH|CHASH|ISAM

                            |CISAM|BTREE|CBTREE

[EXEC SQL] SET LOCKMODE SESSION|ON table_name WHERE

              [LEVEL = PAGE|TABLE|SESSION|SYSTEM|ROW]

              [, READLOCK = NOLOCK|SHARED|EXCLUSIVE

                            | SESSION|SYSTEM]

              [, MAXLOCKS = n|SESSION|SYSTEM]

              [, TIMEOUT = n|SESSION|SYSTEM|NOWAIT]

[EXEC SQL] SET [NO]LOCK_TRACE

[EXEC SQL] SET [NO]LOGGING

[EXEC SQL] SET [NO]OPTIMIZEONLY

[EXEC SQL] SET [NO]PARALLEL [degree of parallelism]

[EXEC SQL] SET [NO]PRINTQRY

[EXEC SQL] SET [NO]QEP

[EXEC SQL] SET SESSION

              [ADD PRIVILEGES ( priv {,priv} )

              |DROP PRIVILEGES ( priv {,priv}) ]

              [WITH

                            ON_ERROR = ROLLBACK STATEMENT|TRANSACTION

                            | DESCRIPTION ='session_description'

                            | NODESCRIPTION

                            | PRIORITY = INITIAL|MINIMUM|MAXIMUM|priority

                            | PRIVILEGES = ( priv {, priv})|ALL|DEFAULT

                            | NOPRIVILEGES

ON_LOGFUL = COMMIT|ABORT|NOTIFY]

[EXEC SQL] SET SESSION READ ONLY|WRITE

              [, ISOLATION LEVEL SERIALIZABLE | REPEATABLE READ | READ COMMITTED |

              READ UNCOMMITTED]

[EXEC SQL] SET UPDATE_ROWCOUNT CHANGED | QUALIFIED

[EXEC SQL] SET WORK LOCATIONS ADD|DROP|USE

              location {, location}

[EXEC SQL] SET TRANSACTION READ ONLY|READ WRITE

[EXEC SQL] SET TRANSACTION isolation level

               READ UNCOMMITTED

               | READ COMMITTED

               | REPEATABLE READ

               | SERIALIZABLE

[EXEC SQL] SET [NO]RULES

[EXEC SQL] SET [NO]PRINTRULES

[EXEC SQL] SET NOMAXCOST | MAXCOST value

[EXEC SQL] SET NOMAXCPU | MAXCPU value

[EXEC SQL] SET NOMAXIO | MAXIO value

[EXEC SQL] SET NOMAXPAGE | MAXPAGE value

[EXEC SQL] SET NOMAXQUERY | MAXQUERY value

[EXEC SQL] SET NOMAXROW | MAXROW value

[EXEC SQL] SET [NO]PRINTDBEVENTS

[EXEC SQL] SET [NO]LOGDBEVENTS

[EXEC SQL] SET RANDOM_SEED [value]

[EXEC SQL] NOMAXIDLE | SET MAXIDLE value

[EXEC SQL] NOMAXCONNECTC | SET MAXCONNECT value

[EXEC SQL] SET UNICODE_SUBSTITUTION [<substitution character>]

[EXEC SQL] SET NOUNICODE_SUBSTITUTION

[EXEC SQL] SET ROLE NONE | rolename [WITH PASSWORD = 'pwd'];

Embedded Usage

When using the set lockmode statement in an embedded Set statement, host language variables can be used to specify elements to the right of the equal sign (=) in the where clause.

Permissions

Permissions are assigned to users by a DBA. For more information on privileges, see the Database Administrator Guide.

• To issue the set [no]printqry, set [no]printrules, and set [no]rules statements, you must have trace permissions.
• To issue the set [no]rules statement, you must be the DBA of the database to which the session is attached when the statement is issued.
• To issue the set lockmode statement, the effective user of the session must have lockmode privilege. Lockmode privilege is assigned using the grant statement. For more information, see Grant (privilege).