SET

SET — change a runtime parameter

Syntax

SET SYSTEM PROPERTY name=value
SET AUTOCOMMIT { ON | OFF }
SET DELIMITER value
SET ISOLATION LEVEL transaction_isolation_level
SET LOCK_WAIT_TIMEOUT sec
SET OUTPUT { VERTICAL | HORIZONTAL }
SET PAGER command
SET QUERY TIMEOUT sec
SET ROLE rolename [ ACTIVE | INACTIVE ]
SET SEMICOLON { ON | OFF }
SET TIME ZONE tzname
SET TIMING { ON | OFF | FULL }

Description

Invoke the SET command to change parameters that affect runtime behavior.

Parameters

A parameter that you specify with the SET statement affects the runtime behavior of a database or the current connection to a database. Some parameters apply only when you are using the nuosql command line utility.

Setting Parameters for the Database

The following parameter lets you specify system properties that apply to all connections to the database:

SYSTEM PROPERTY name=value

Use the SET command to set system properties defined by NuoDB. See SQL System Properties for a list of all NuoDB system properties and a description of each. Only users that have been granted the "ADMINISTRATOR" ROLE can modify system property values.

Changes to database properties have immediate effect on the cluster and can’t be rolled back.

Setting Parameters for the Connection

The following parameters apply to the current connection to a database:

AUTOCOMMIT { ON | OFF }

Toggles AUTOCOMMIT on or off. This controls whether NuoDB automatically commits after every statement execution.

The default behavior is that autocommit is enabled (ON). When autocommit is enabled then each statement causes NuoDB to start a new transaction and automatically commit that transaction after executing that statement. There is no ability to roll back database changes.

If your application requires that several changes succeed or fail as a group, do one of the following:

  • Specify START TRANSACTION. NuoDB suspends autocommit behavior during the transaction. The START TRANSACTION statement lets you override About Default Transaction Behavior. After you specify a COMMIT or ROLLBACK statement, the autocommit setting is again in effect.

  • Specify SET AUTOCOMMIT OFF. Execution of the next statement implicitly starts a transaction. Such a transaction always uses the default transaction behaviors. Specify a COMMIT or ROLLBACK statement to end the transaction.

    In a transaction that you open with START TRANSACTION, you can specify a SET AUTOCOMMIT statement but it does not affect the current transaction. It takes effect after you end the transaction.

    For example, suppose an application requires that a deposit in one account matches a withdrawal from another account. The application specifies START TRANSACTION to suspend autocommit behavior, which is enabled. The database changes that occur as a result of these DML statements are committed or rolled back as a group.

    Specification of SET AUTOCOMMIT OFF can improve performance because there is a cost to starting and committing transactions.

DELIMITER character_string

Configures the character or characters that delimit SQL statements or blocks. The default is a semicolon (;). character_string can be one or more characters representing the new delimiter. For example, any of the following are valid: (/),(//),(\),(\\),(|),(||),(@), etc. A character value should be used that will not be contained in subsequent sql statements.

SET DELIMITER sets the end-of-statement signifier to something other than the default semicolon. Changing the delimiter can be valuable for executing store procedures and complex triggers. See Example 4 below.

ISOLATION LEVEL transaction_isolation_level

ISOLATION LEVEL defines how this transaction can view updates performed by other, concurrent transactions. The default is CONSISTENT READ. The ISOLATION LEVEL setting is in effect with the start of the next transaction and stays in effect until the level is reset with another SET command or the connection closes. See Supported Transaction Isolation Levels for a description of each transaction isolation level that NuoDB supports.

LOCK_WAIT_TIMEOUT sec

Dynamically sets the lock wait timeout (in seconds) for an existing connection.

  • The default value is 0, which means lock wait timeout is disabled.

  • If the lock wait timeout is > 0, then a statement that is blocked waiting on a row lock, unique constraint check, or table lock for longer than the timeout is rolled back with an exception, "Error 58000: Timed out after <time_in_ms> milliseconds. Waited <time_in_ms> milliseconds for transaction <transaction_id> to unlock record id <record_id> of table id <table_id>."

  • If there is an active transaction in the connection when LOCK_WAIT_TIMEOUT is set, LOCK_WAIT_TIMEOUT for the active transaction is reset to the new value.

  • Both QUERY TIMEOUT and LOCK_WAIT_TIMEOUT can be enabled at the same time.

  • Changing the DEFAULT_LOCK_WAIT_TIMEOUT will not change LOCK_WAIT_TIMEOUT for established connections. For more information, see SQL System Properties.
QUERY TIMEOUT sec

Invoke SET QUERY TIMEOUT to specify the maximum length of time (in seconds) before a query is terminated for taking too long. QUERY TIMEOUT is a per-connection property which is disabled by default. It can be enabled by running the SET QUERY TIMEOUT command with a non-zero value. A value of 0 is used to disable the timeout. The specified value can be a decimal fraction for granularity smaller than a second.

ROLE rolename [ ACTIVE | INACTIVE ]

A role can be active or inactive. The default is that a role is inactive. See the GRANT statement for information about creating and assigning roles. If you specify SET ROLE rolename without specifying ACTIVE or INACTIVE then the specified role becomes active.

A role that has been granted to a user is active when the privileges associated with that role are currently available to that user. An inactive role is a role granted to a user but whose privileges are not currently available to that user, but may be used in the future by issuing the SET ROLE command. Each time the user starts a new session, the role will be inactive and the user must invoke SET ROLE to make it active again.

Although SQL standards require this statement to be executed outside of a transaction, the same restriction does not apply in a NuoDB database
TIME ZONE tzname

Sets the local time zone to tzname. tzname must be a valid time zone ID, wrapped in single quotes.

The local time zone is the default time zone of Date/Time types and it has the following effects:

  • Data types with no time zone (DATE, TIMESTAMP WITHOUT TIME ZONE, STRING) are associated with the local time zone when they are converted to or from data types with time zone (TIMESTAMP, TIME).

  • The results of functions and operations with TIMESTAMP data type are affected by the local time zone offset rules.

Setting Parameters when using nuosql

When you are using the nuosql command line utility then you can set the following parameters:

OUTPUT { VERTICAL | HORIZONTAL }

Configures output display orientation (see Example 5). The default is HORIZONTAL. Set the OUTPUT to VERTICAL when you want nuosql to print table rows as one column per line. This is useful for when your query returns many columns or when data in the column contains formatting such as new lines.

PAGER command

Pipes result from SQL queries in nuosql through the specified command. The default is that results go to stdout. The specified command can be one command, for example:

SET PAGER more

The specified command can be a command string with arguments, for example:

SET PAGER "less --QUIT-AT-EOF --no-init"

If you specify less, it is recommended that you specify both --QUIT-AT-EOF and --no-init. This prevents the need to explicitly quit at the end of the results. Also, the results do not disappear, which they do when you explicitly quit.

The specified command must be on the user’s PATH.

The SET PAGER command is not supported on Windows.

SEMICOLON [ ON | OFF ]

This is specific to the nuosql command line tool. The default is ON. Set the SEMICOLON variable to OFF when you do not want nuosql to require semicolons at the end of commands. If you specify SET SEMICOLON without specifying ON or OFF then the setting is ON.

TIMING { ON | OFF | FULL }

Turn on or off reporting of elapsed time for each SQL statement to execute. The OFF mode is default. The ON mode reports the elapsed time taken by the nuosql client. The FULL mode reports the elapsed time taken by the nuosql client and additionally for non-DDL SQL statements, reports the elapsed time spent by the server (TE) to process the command.

Examples

Example 1: SET AUTOCOMMIT
# The first set of statements are committed because AUTOCOMMIT is enabled.

SHOW AUTOCOMMIT;
    Autocommit is on

CREATE TABLE t (N int);
INSERT INTO t VALUES (1);
SELECT * FROM t;
 N
 --
 1
Example 2: Set AUTOCOMMIT OFF and ROLLBACK.
# Setting AUTOCOMMIT OFF after the first set of statements means that the
# second set of statements are not committed. They can be rolled back.

SET AUTOCOMMIT OFF;
INSERT INTO t VALUES (2);
SELECT * FROM t;
 N
 --
 1
 2

# ROLLBACK discards the second insertion.
# There is only one row in the table after ROLLBACK.

ROLLBACK;
SELECT * FROM t;
 N
 --
 1
Example 3: Explicit COMMIT with AUTOCOMMIT OFF
# You can explicitly commit execution of a statement when autocommit is
# not enabled. After the COMMIT statement, there is nothing to roll back.

INSERT INTO t VALUES (2);
COMMIT;
SELECT * FROM t;
 N
 --
 1
 2

ROLLBACK;
SELECT * FROM t;
 N
 --
 1
 2
Example 4: SET DELIMITER temporarily to define a function.
DROP FUNCTION IF EXISTS func_is_date;
SET DELIMITER @
CREATE FUNCTION func_is_date (i_date string, i_format string )
       RETURNS BOOLEAN
       DETERMINISTIC
       LANGUAGE SQL
       SECURITY INVOKER
AS
   VAR l_out BOOLEAN = 'TRUE';
   VAR l_timestamp TIMESTAMP;
   try
       l_timestamp = (SELECT date_from_str(i_date, i_format) FROM DUAL);
   catch(error)
       l_out = 'FALSE';
   end_try;
   RETURN l_out;
END_FUNCTION;
@
SET DELIMITER ;
Example 5: SET OUTPUT to change the display direction of the output.
CREATE TABLE tbl_output_display (column1 INTEGER, column2 STRING);
INSERT INTO tbl_output_display VALUES (1,'This is row 1'),(2,'This is row 2');
SET OUTPUT HORIZONTAL;
SELECT * FROM tbl_output_display;

  COLUMN1     COLUMN2
 -------- -------------
    1     This is row 1
    2     This is row 2


SET OUTPUT VERTICAL;
SELECT * FROM tbl_output_display;

==================================== Row #1 ====================================
COLUMN1: 1
COLUMN2: This is row 1
==================================== Row #2 ====================================
COLUMN1: 2
COLUMN2: This is row 2
Example 6: SET TIMING to FULL so that elapsed time will be reported for each statement executed.
SET TIMING FULL;
SELECT * FROM hockey.scoring LIMIT 3000;

 PLAYERID  YEAR  STINT  TEAMID  POSITION  GAMESPLAYED  GOALS  ASSISTS  PENALTYMINUTES
 --------- ----- ------ ------- --------- ------------ ------ -------- ---------------

 aaltoan01 1997    1      ANA      C            3         0       0            0
 aaltoan01 1998    1      ANA      C           73         3       5           24
...
 bladoto01 1972    1      PHI      D           78        11      31           26
 bladoto01 1973    1      PHI      D           70        12      22           37

Elapsed time 36ms
Server execution time 7ms
Example 7: SET TIME ZONE to change the local time zone.
SET TIME ZONE 'UTC';
CREATE TABLE tbl_timestamp(ts TIMESTAMP);
INSERT INTO tbl_timestamp VALUES ('2010-01-01 10:00:00');
SELECT * FROM tbl_timestamp;

         TS
 -------------------

 2010-01-01 10:00:00


SET TIME ZONE 'America/New_York';
SELECT * FROM tbl_timestamp;

         TS
 -------------------

 2010-01-01 05:00:00