Was this helpful?
SET
Valid in: SQL, ESQL, OpenAPI, ODBC, JDBC, .NET
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.
The SET statement has the following format:
[EXEC SQL] SET AUTOCOMMIT ON | OFF
[EXEC SQL] SET AUTOCOMMIT READ
[EXEC SQL] SET [NO]BATCH_COPY_OPTIM
[EXEC SQL] SET [NO]CACHE_DYNAMIC
[EXEC SQL] SET [NO]CARDINALITY_CHECK
[EXEC SQL] SET CONNECTION NONE | connection_name
[EXEC SQL] SET CPUFACTOR [value]
[EXEC SQL] SET DATE_ALIAS [INGRESDATE | ANSIDATE]
[EXEC SQL] SET DATE_FORMAT [value]
[EXEC SQL] SET DECIMAL [value]
[EXEC SQL] SET [NO]FIRSTVAL
[EXEC SQL] SET [NO]FLATTEN
[EXEC SQL] SET FLOAT_FORMAT 'format'
[EXEC SQL] SET [NO]HASH
[EXEC SQL] SET [NO]HASHAGG
[EXEC SQL] SET [NO]HASHJOIN
[EXEC SQL] SET INSERTMODE DEFAULT | ROW | BULK
[EXEC SQL] SET [NO]IO_TRACE
[EXEC SQL] SET JOINOP NOTIMEOUT | TIMEOUT | TIMEOUT nnn
[EXEC SQL] SET JOINOP TIMEOUTABORT nnn
[EXEC SQL] SET JOINOP [NO]GREEDY
[EXEC SQL] SET NOJOURNALING | JOURNALING [ON tablename]
[EXEC SQL] SET LOCKMODE SESSION|ON tablename WHERE
              [LEVEL = PAGE | TABLE | SESSION | SYSTEM | ROW | MVCC]
              [, 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]LOGDBEVENTS
[EXEC SQL] SET [NO]LOGGING
[EXEC SQL] SET [NO]LOG_TRACE
[EXEC SQL] SET NOMAXCONNECT | MAXCONNECT value
[EXEC SQL] SET NOMAXCOST | MAXCOST value
[EXEC SQL] SET NOMAXCPU | MAXCPU value
[EXEC SQL] SET NOMAXIDLE | MAXIDLE 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 MONEY_FORMAT [value]
[EXEC SQL] SET MONEY_PREC [value]
[EXED SQL] SET NUMERIC_OVERFLOW WARN | FAIL | IGNORE
[EXEC SQL] SET [NO]OJFLATTEN
[EXEC SQL] SET [NO]OPTIMIZEONLY
[EXEC SQL] SET [NO]PARALLEL [degree of parallelism]
[EXEC SQL] SET [NO]PRINTDBEVENTS
[EXEC SQL] SET [NO]PRINTQRY
[EXEC SQL] SET [NO]PRINTRULES
[EXEC SQL] SET [NO]QEP [CONCISE | SEGMENTED]
[EXEC SQL] SET RANDOM_SEED [value]
[EXEC SQL] SET RESULT_STRUCTURE
                HEAP | CHEAP | HEAPSORT | CHEAPSORT | HASH | CHASH
               | ISAM |CISAM | BTREE | CBTREE | X100 | X100_ROW
[EXEC SQL] SET NOREUSE
[EXEC SQL] SET [NO]SCHEMA
[EXEC SQL] SET SERVER_TRACE ON | OFF [with-clause]
[EXEC SQL] SET ROLE NONE | rolename [WITH PASSWORD = 'pwd'];
[EXEC SQL] SET [NO]RULES
[EXEC SQL] SET SESSION
                [ADD PRIVILEGES ( priv {,priv} )
                |DROP PRIVILEGES ( priv {,priv}) ]
                [WITH
                      ON_ERROR = ROLLBACK STATEMENT | TRANSACTION
                      ON_USER_ERROR = ROLLBACK TRANSACTION | NOROLLBACK
                      | DESCRIPTION ='session_description'
                      | NODESCRIPTION
                      | PRIORITY = INITIAL | MINIMUM | MAXIMUM | priority
                      | PRIVILEGES = ( priv {, priv}) |ALL | DEFAULT
                      | NOPRIVILEGES
                       ON_LOGFULL = COMMIT | ABORT | NOTIFY]
[EXEC SQL] SET SESSION READ ONLY | READ WRITE
              [, ISOLATION LEVEL SERIALIZABLE | REPEATABLE READ |
                   READ COMMITTED | READ UNCOMMITTED ]
[EXEC SQL] SET SESSION AUTHORIZATION username | USER | CURRENT_USER
                   SESSION_USER | SYSTEM_USER | INITIAL_USER
[EXEC SQL] SET SESSION [NO]CACHE_DYNAMIC
[EXEC SQL] SET SESSION COLLATION collation_name
[EXEC SQL] SET SESSION_TRACE ON | OFF [with-clause]
[EXEC SQL] SET [NO]STATISTICS tablename
[EXEC SQL] SET NOTRACE OUTPUT | TRACE OUTPUT filename
[EXEC SQL] SET [NO]TRACE POINT [value]
[EXEC SQL] SET TRANSACTION READ ONLY | READ WRITE
              [, ISOLATION LEVEL SERIALIZABLE | REPEATABLE READ |
               READ COMMITTED | READ UNCOMMITTED ]
[EXEC SQL] SET NOUNICODE_SUBSTITUTION | UNICODE_SUBSTITUTION [substitution character]
[EXEC SQL] SET [NO]UNION_FLATTENING
[EXEC SQL] SET UPDATE_ROWCOUNT CHANGED | QUALIFIED
[EXEC SQL] SET WORK LOCATIONS ADD | DROP | USE
              location {, location}
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.
Usage in OpenAPI, ODBC, JDBC, and .NET
In OpenAPI, ODBC, JDBC, and .NET interfaces, the following SET commands are not supported:
SET CONNECTION NONE | connection_name
SET [NO]PRINTQRY
The following SET commands are supported through interface-specific mechanisms:
SET AUTOCOMMIT ON|OFF uses IIapi_autocommit().
SET SESSION [READ ONLY | READ WRITE] uses IIapi_setConnectParam(), IIapi_modifyConnect(), and/or IIapi_setEnvParam() in OpenAPI.
SET TRANSACTION uses IIapi_setConnectParam(), IIapi_modifyConnect(), and/or IIapi_setEnvParam() in OpenAPI.
All other SET statements can be sent as SQL.
Permissions
To issue the following statements, a user must have TRACE privilege:
SET [NO]LOCK_TRACE
SET [NO]PRINTQRY
SET [NO]RULES
SET [NO]PRINTRULES
SET [NO]PRINTDBEVENTS
SET [NO]LOGDBEVENTS
SET [NO]IO_TRACE
SET [NO]LOG_TRACE
SET [NO]TRACE POINT value
To issue the SET WORK LOCATIONS statement, the effective user of the session must have MAINTAIN_LOCATIONS privilege. For more information see CREATE USER and ALTER USER.
To issue the following statements, you must be the DBA of the database to which the session is attached:
SET [NO]RULES
SET [NO]LOGGING
To issue the SET LOCKMODE statement, the effective user of the session must have LOCKMODE privilege. LOCKMODE privilege is assigned using the GRANT statement (see page GRANT (privilege)).
Last modified date: 01/30/2023