SET

SET — change a runtime parameter

Syntax

SET SYSTEM PROPERTY name=value
SET AUTOCOMMIT { ON | OFF }
SET AUTOCOMMIT_SP_MODE { ON | OFF }
SET DELIMITER value
SET ISOLATION LEVEL transaction_isolation_level
SET OUTPUT { VERTICAL | HORIZONTAL }
SET PAGER command
SET QUERY TIMEOUT sec
SET ROLE rolename [ ACTIVE | INACTIVE ]
SET ROLLBACK MODE { PROCEDURE | TRANSACTION | OFF }
SET SEMICOLON { ON | OFF }
SET STATEMENT ANALYZE { ON | OFF }
SET TIMING { ON | OFF | FULL }
SET [ trace_level ] TRACE { ON [ opt_trace_params ] | OFF }

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.

AUTOCOMMIT_SP_MODE { ON | OFF }

Toggles the autocommit for stored procedure mode which controls the behavior of auto commit for the execution of DML SQL statements inside a stored procedure, UDF, or trigger. By default this is set to OFF.

This is also governed by the connection property autoCommitSPMode. See Connection Properties.

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.

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
ROLLBACK MODE { PROCEDURE | TRANSACTION | OFF }

Controls the rollback behavior of transactions executed inside a stored procedure.

This is also governed by the rollbackMode connection property. See Connection Properties.

STATEMENT ANALYZE { ON | OFF }

Turn STATEMENT ANALYZE on or off. The default is that STATEMENT ANALYZE is disabled. If STATEMENT ANALYZE is specified as ON, then the query plan and other statistics are reported for each query. When this information is not needed then you can invoke SET STATEMENT ANALYZE OFF. The query plan displayed is the same as the one displayed by the EXPLAIN command (See EXPLAIN).

Setting Connection or Database Level Tracing

[trace_level] TRACE { ON [ opt_trace_params ] | OFF }

Turn on or off tracing of SQL statements at the trace_level defined. Tracing means logging SQL statements to a persistent table. All non-DDL SQL statements are logged, including statements that execute inside a stored procedure.

See Using SQL Trace Facility for more information.

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 STATEMENT ANALYZE to turn STATEMENT ANALYZE on and off.
DROP TABLE t IF EXISTS;
SET STATEMENT ANALYZE ON;
CREATE TABLE T (x int);

/* Analyze Output */
Analysis:
CREATE TABLE T (x int);

INSERT INTO t VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10), (NULL);

/* Analyze Output */
Analysis:
INSERT INTO t VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10), (NULL);
  Inserts:           11

SELECT x FROM t WHERE x=5;
 X
 --
 5

/* Analyze Output */
Analysis:
SELECT x FROM t WHERE x=5;
  Exhaustive Fetches: 11
  Records Fetched:   11
  Records Returned:  1

CREATE INDEX idx ON t (x);

/* Analyze Output */
Analysis:
CREATE INDEX idx ON t (x);

SELECT x FROM t WHERE x=5;
 X
 --
 5

/* Analyze Output */
Analysis:
SELECT x FROM t WHERE x=5;
  Index Hits:        1
  Index Fetches:     1
  Records Fetched:   1
  Records Returned:  1
Example 7: 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