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 Star User Guide.
The SET statement has the following format:
[EXEC SQL] SET [ACTUAL | ESTIMATED] MAXROW n
[EXEC SQL] SET AGGR_TIMESTAMPTZ ON|OFF
[EXEC SQL] SET AUTOCOMMIT ON | OFF | 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 | NONE ]
[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]FLSQL
[EXEC SQL] SET [NO]HASH
[EXEC SQL] SET [NO]HASHAGG
[EXEC SQL] SET [NO]HASHJOIN
[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 NOMAXROWSTEP value
[EXEC SQL] SET MONEY_FORMAT [value]
[EXEC SQL] SET MONEY_PREC [value]
[EXEC SQL] SET MONEY_TEXTALIGNMENT LEFT | RIGHT
[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 [NO]REUSE
[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 = NOROLLBACK | ROLLBACK TRANSACTION
| ON_LOGFULL = ABORT | COMMIT | NOTIFY
| DESCRIPTION = 'session description'
| NODESCRIPTION
| PRIORITY = INITIAL | MINIMUM | MAXIMUM | priority
| PRIVILEGES = (priv {,priv}) | ALL | DEFAULT
| NOPRIVILEGES]
SET SESSION READ ONLY | READ WRITE
[, ISOLATION LEVEL SERIALIZABLE | REPEATED READ |
READ COMMITTED | READ UNCOMMITTED]
SET SESSION AUTHORIZATION username | USER | CURRENT_USER
SESSION_USER | SYSTEM_USER | INITIAL_USER
SET SESSION [NO]CACHE_DYNAMIC
SET SESSION COLLATION collation_name
SET SESSION [NO]OLD_COPY_DN
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 TERMINAL 'terminalname'
[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
GRANT (privilege)).