SET Examples
1. Create tables "withlog1", "withlog2", and "withlog3" with journal logging enabled and "nolog" without.
SET JOURNALING;
CREATE TABLE withlog1 ( ... );
CREATE TABLE withlog2 ( ... );
SET NOJOURNALING;
CREATE TABLE withlog3 ( ... ) WITH JOURNALING;
CREATE TABLE nolog1 ( ... );
2. Create tables "a", "b", and "d" with different structures.
CREATE TABLE a AS ...;/* HEAP - the default */
SET RESULT_STRUCTURE HASH hash;
CREATE TABLE b AS SELECT id ...;/* HASH on 'id' */
SET RESULT_STRUCTURE HEAP;
CREATE TABLE d AS SELECT id ...;/* HEAP again */
3. Set lockmode parameters for the current session. Tables accessed after executing this statement are governed by these locking behavior characteristics.
SET LOCKMODE SESSION WHERE LEVEL = PAGE,
READLOCK = NOLOCK,
MAXLOCKS = 50, TIMEOUT = 10;
4. Set the lockmode parameters explicitly for table employee.
SET LOCKMODE ON employee
WHERE LEVEL = TABLE, READLOCK = EXCLUSIVE,
MAXLOCKS = SESSION, TIMEOUT = 0;
5. Reset your session default locking characteristics to the system defaults.
SET LOCKMODE SESSION WHERE LEVEL = SYSTEM,
READLOCK = SYSTEM,
MAXLOCKS = SYSTEM, TIMEOUT = SYSTEM;
6. Switch sessions in a multi-session application.
SET SESSION CONNECTION personnel;
7. Set the session description to 'Payroll App: Generating invoices'.
SET SESSION
WITH DESCRIPTION = 'payroll app: generating invoices';
8. Set the session priority to 5 below the normal base priority.
SET SESSION WITH PRIORITY = -5
9. Restore the initial session priority.
SET SESSION WITH PRIORITY = INITIAL;
10. Changes the session role to clerk.
SET ROLE clerk WITH PASSWORD ='clerk_password';
Last modified date: 11/09/2022