SET Options

SET AUTOCOMMIT OFF, the default, means an explicit COMMIT or ROLLBACK statement or terminating the session is required to terminate a transaction.

SET AUTOCOMMIT READ autocommits a transaction that was started in read-only mode.

Transactions are started read only when no transaction is currently started, and:

It is possible to treat Vector cursors as read-only statements because Vector cursors are not updatable and cannot nest; they are like Ingres SELECT loops. Ingres cursors can nest and overlap, so the open of an Ingres cursor cannot imply a read-only transaction even if it is opened FOR READONLY.)

Since many ODBC and JDBC programs use cursors for querying, and since autocommit ON is the default for ODBC and JDBC, then SET AUTOCOMMIT READ will alleviate some out-of-memory conditions caused by uncommitted transactions; a long SELECT cursor query will run read-only for its entirety without having to wait for the commit.

If AUTOCOMMIT READ is in effect and an updating statement is issued, a multi-statement transaction will be started. You must explicitly end such a transaction with COMMIT or ROLLBACK.

SET AUTOCOMMIT READ can be helpful for Vector query sessions. In Vector, all transactions must be committed as soon as possible to prevent unnecessary memory usage.

The SET AUTOCOMMIT statement cannot be issued in an open transaction. In ODBC and JDBC, autocommit is ON by default.

Enables or disables copy optimization when executing batched inserts. This statement overrides the default set on the DBMS Server batch_copy_optim configuration parameter.

Enables or disables the caching of query plans for cursors defined with dynamic SELECT statements. It overrides the server level setting defined by the cache_dynamic configuration parameter. To check the current setting, use SELECT DBMSINFO ('CACHE_DYNAMIC'). The SET [NO]CACHE_DYNAMIC setting persists until the DBMS Server is shut down. See also SET SESSION [NO]CACHE_DYNAMIC.

Switches the current session to a connection previously established using the CONNECT statement. SET CONNECTION NONE results in no current session.

Valid in Embedded SQL only.

Specifies the format for date values. This option corresponds to the Vector environment variable II_DATE_FORMAT and is assigned the same values. If set, DATE_FORMAT replaces the currently configured format with an alternate format. The default format setting is US. See the Vector User Guide for a list of valid settings.

Specifies the character to be used as the decimal point in numeric literals. This option corresponds to the Vector environment variable II_DECIMAL and is assigned the same values. Valid characters are the period (.) (as in 12.34) and the comma (,) (as in 12,34). The default is the period.

Sets floating point output to the specified format. Allows a portable copydb operation to automatically generate SET statements to use the specified format (for example: -f4F79.38 and -f8F79.38) so floating point values do not lose precision if an appropriate floating point output format is not specified on the command line.

Specifies the mode to use for inserts and merges. This statement can be used to force appends to go through the PDTs, to allow concurrent inserts. Modes are as follows:

For more information, see the Vector User Guide.

Changes the timeout point of the query optimizer. When the query optimizer is checking query execution plans, it stops when it believes that the best plan that it has found takes less time to execute than the amount of time already spent searching for a plan or when it has evaluated all possible query plans, whichever is reached first.

SET JOINOP TIMEOUT nnn tells the query optimizer to stop looking for query execution plans after the specified number of milliseconds and to use the best plan found to that point.

If 0 is specified, the timeout occurs when the optimizer finds that the best plan found so far will take less time to execute than the amount of time already spent evaluating plans (the default).

SET JOINOP NOTIMEOUT tells the optimizer to search ALL possible query plans. SET JOINOP TIMEOUT restores the default TIMEOUT.

This option has no effect if the GREEDY option is in effect.

Specifies the time in milliseconds after which the optimizer stops considering query plans and uses the best plan found to that point. If no plan is found within the specified time, an error is generated.

If 0 (the default) is specified, the timeout is disabled.

Like SET JOINOP TIMEOUT nnn, SET JOINOP TIMEOUTABORT nnn instructs the optimizer to time out after nnn milliseconds of processing.

The TIMEOUTABORT option, however, also applies to the time prior to obtaining the first query plan. If the time expires and no plan is found, the query is aborted with an error. If at least one plan is found, the best plan is used and the query is executed.

Contrast this with SET JOINOP TIMEOUT nnn, where the time can expire only after the first plan is found.

As with SET JOINOP TIMEOUT, the default value of 0 for SET JOINOP TIMEOUTABORT effectively disables this feature.

If both TIMEOUT and TIMEOUTABORT have been assigned non-zero values, the following rules apply:

• If TIMEOUT is greater than or equal to TIMEOUTABORT, this is equivalent to TIMEOUT being set to 0, that is, its value is ignored and the TIMEOUTABORT mechanism is in effect.

As with SET JOINOP TIMEOUT nnn, this new timing feature does not affect the default SET JOINOP TIMEOUT behavior, which is to time out when as much time has been used for optimization as the estimated execution time of the best plan found so far.

Enables or disables the complex query enumeration heuristic of the query optimizer. The greedy heuristic enables the optimizer to produce a query plan much faster than with its default technique of exhaustive searching for query execution plans when the query references a large number of tables. When SET JOINOP GREEDY is specified, the SET JOINOP TIMEOUT statement has no effect.

The default for journaling for a DBMS Server is established by the DEFAULT_JOURNALING option in CBF. Default journaling can be overridden by the SET [NO]JOURNALING statement, which then defines the default journaling state for tables created by the session after the statement is issued.

If a table is created with the WITH [NO]JOURNALING clause, that becomes the default journaling state for that table.

Important! Regardless of whether journaling is enabled for a table, journaling only occurs when journaling is enabled for the database. Journaling for the entire database is turned on or off using the ckpdb command.

If the current journaling status of the table is OFF, and you want to enable journaling for the table after the next checkpoint, use the SET JOURNALING ON tablename statement.

Note: Journaling status can be enabled only when table is first created or after a checkpoint in which case the checkpoint has a consistent version of the table against which subsequent journals can be applied.

To disable journaling against a table, use the SET NOJOURNALING ON tablename statement.

The HELP TABLE tablename statement shows the journaling status of a table. The infodb command shows the journaling status of a database. Journaling can be stopped for a database by using the -disable_journaling option of the alterdb command or ckpdb with the -i flag.

Specifies the format for monetary output. This option corresponds to the Vector environment variable II_MONEY_FORMAT and is assigned the same values. The default is L:$. The symbol to the left of the colon indicates the location of the currency symbol—L for a leading currency symbol or a T for a trailing currency symbol. The symbol to the right of the colon is the currency symbol you want displayed. Currency symbols can contain up to four physical characters. If no currency symbol is required, set the value to NONE (case insensitive). For example:

Specifies the number of decimal places to be displayed for money values. This option corresponds to the Vector environment variable II_MONEY_PREC and is assigned the same values. Valid values are 0, 1, and 2. The default is 2 (for decimal currency).

Tells the query optimizer to use (SET OJFLATTEN) or not to use (SET NOOJFLATTEN) the transformation that converts a NOT EXISTS/NOT IN subselect to an outer join with the containing query. The default is SET OJFLATTEN.

Specifies whether query execution should halt after the completion of query optimization. Use SET OPTIMIZEONLY with SET QEP when there is a requirement to view query execution plans without executing a query.

Displays or does not display each query and its parameters as it is passed to the DBMS Server for processing.

Displays or does not display a graphical representation of the query execution plan (QEP) chosen for the query by the optimizer. SET QEP CONCISE displays the QEP in a format that is suitable for parsing by a program. SET QEP SEGMENTED displays QEP as a series of segments that are easier to read when parsing complex SQL statements. For more information on query execution plans, see the User Guide.

Sets the beginning value for the random functions. The global seed value is used until you issue SET RANDOM_SEED, which changes the value of the local seed. Once changed, the local seed is used for the entire session. If you are using the global seed value, the seed is changed whenever a random function executes. This means that other users issuing random calls enhance the "randomness" of the returned value. The seed value can be any integer. If you omit the value, the value is a multiple of the process ID and the number of seconds since 1/1/1970.

Forces WITH STRUCTURE=VECTORWISE, VECTORWISE_ROW, or HEAP on all CREATE TABLE, CREATE TABLE AS SELECT, and DECLARE GLOBAL TEMPORARY TABLE statements for the session. The default for the installation is taken from the result_structure parameter in config.dat.

Disables the "reuse" heuristic of the query optimizer. For more information, see the Vector User Guide.

The SET SCHEMA schema_name statement sets the schema for the session. If schema is not the same as the current user then all further unqualified object references are treated as if the schema value had been explicitly provided as a qualification.

For example, SET SCHEMA john applies "john." to all unqualified object references. So SELECT * FROM mytab becomes SELECT * FROM from john.mytab.

The current schema_name can be retrieved using:

SET SCHEMA (with no parameters) or SET NOSCHEMA resets schema_name to the current session user.

SET SCHEMA is ignored for all system catalogs.

SET SERVER_TRACE enables or disables query tracing for all sessions on the local server.

SERVER_TRACE overrides all SESSION_TRACE options in effect.

Requires trace point privilege or equivalent.

Sets the output directory to the specified path. A complete (absolute) path is recommended, although not required. This directory must be writable by the installation owner. The trace files created in this directory have names of the form qtrace_dbname_ppp_nnn.log where dbname is the database name, ppp is the process ID of the DBMS server, and nnn is a hex number describing the session. If a trace file does not already exist, it is created, owned by the installation owner. If a trace file already exists for some session, it is appended to. Trace files cannot be symbolic links.

Sets the number of segments of blob parameter values that are printed. Each segment is up to 2KB of blob text. Valid values: non-negative integers; 0 means none; large integer value means all.

Timing starts when query enters PSF and stops at the EQY record, that is, all results have been generated and sent. It includes parse, optimize, execution, query result send, lock-wait time, blocks, rule/DBP time, and so on.

WITH options with ONLY, EXCEPT, NO are mutually exclusive.

If tracing is ON, and another statement is issued at the same (server or session) level to turn on tracing, the statement operates as an "alter trace" whose WITH-options start with the existing settings. For example:

leaves QEP on and turns off X100ALGEBRA.

If the trace is altered, the trace is, in effect, turned off and back on. So, for example, you can alter the directory path while tracing is on.

If duplicate options are entered, the last one entered is used. For example, if DETAIL, NODETAIL is entered, NODETAIL is used.

Lets a user with security administrator and operator privilege or who is the DBA of the database to set the effective user for the current session. The statement must be issued before a transaction commences and the settings apply until that session is completed. The SET SESSION AUTHORIZATION options are as follows:

The Vector user ID that started the session

Controls the access mode for the current session. The SET SESSION statement must be issued before a transaction commences and the settings apply until that session is completed.

SET SESSION READ ONLY means that insert, update, delete, copy, and DDL operations are disallowed, and an SQLSTATE of 25000 (invalid session state) is returned. Temporary tables are the exception and are always writable. When a READ ONLY session is begun, it registers itself with the logging system and is allowed to proceed even when a ckpdb is pending against the database for the session. Use it for applications that can be read only most of the time.

SET SESSION READ ONLY prevents the session from being part of conflict resolution. Not being part of conflict resolution means that transactions executed as READ ONLY do not cause committed transaction memory to be used.

SET SESSION may be overridden with SET TRANSACTION.

SET SESSION_TRACE enables or disables query tracing for the session.

SESSION_TRACE is not allowed if SERVER_TRACE is in effect.

Requires trace point privilege or equivalent.

For valid options, see SET SERVER_TRACE ON|OFF [with-clause].

Specifies how transaction errors are handled in the current session.

ROLLBACK TRANSACTION rolls back the current transaction if an error occurs. ROLLBACK rolls back only the current statement. This is the default.

To determine the current status of transaction error handling, issue the SELECT DBMSINFO('ON_ERROR_STATE') statement.

ROLLBACK TRANSACTION reduces logging overhead and can help performance. The performance gain is offset by the fact that, if an error occurs, the entire transaction is rolled back. The following errors always roll back the current transaction regardless of the current transaction error-handling setting:

To determine if a transaction was aborted as the result of a database statement error, issue the SELECT DBMSINFO('TRANSACTION_STATE') statement. If the error aborted the transaction, this statement returns 0, indicating that the application is currently not in a transaction.

Note: SQL syntax errors (including most messages beginning with E_US) do not cause a rollback. Only errors that occur during execution of the SQL statement cause a rollback.

Specifies how user errors are handled in the current session. ROLLBACK TRANSACTION rolls back the effects of the entire transaction if a user error occurs. To revert back to default behavior, specify NOROLLBACK.

Tells the query optimizer to use or ignore any statistics found in IIHISTOGRAMS or IISTATISTICS for the specified table. The default is for the query optimizer to use statistics.

SET [NO]STATISTICS permanently modifies the table definition and affects queries in future sessions, so its use is restricted to the table owner and DBA.

Sets the current DBMS trace log file as filename. If a file has already been defined for tracing, the newly set file name is used for subsequent tracing. If no file is currently defined for tracing (because the II_DBMS_LOG variable is not set or SET NOTRACE OUTPUT has been executed), filename will be used for subsequent tracing.

The filename option must be a quoted string containing a file name with an extension of ".log". It can be an absolute file name, or a relative file name based on the directory from which the ingstart command was used to start the DBMS Server. If filename already exists, it will be overwritten by the new tracing entries; otherwise, it will be created when the SET TRACE OUTPUT statement is executed.

SET NOTRACE OUTPUT closes the trace file. No trace information is written until a subsequent SET TRACE OUTPUT is executed to define a new trace file.

SET TRACE POINT activates specified trace points for the current session. Trace points are not officially supported; Actian reserves the right to change their effect and output without notification. Trace points relevant to Vector include OP150 and QE82. For more information, see the Vector User Guide.

The SET TRANSACTION statement is used to override the SET SESSION setting. SET TRANSACTION must be issued before a transaction commences and the settings last only until that transaction is completed.

The access mode options are as follows:

When a transaction access mode of READ ONLY is specified, insert, update, delete, copy, and DDL operations are disallowed, and an SQLSTATE of 25000 (invalid session state) is returned. Temporary tables are the exception and are always writable.

When a READ ONLY transaction is begun, it registers itself with the logging system and is allowed to proceed even when a ckpdb is pending against the database for the session.

Turns union flattening on or off for the session.

Union flattening is an optimization technique used to increase the potential for compiling efficient query plans. If a query involves joining the result of a union view (or derived table or common table element) to other tables or views, the query optimizer will expand the union view to produce a query that is the union of each SELECT in the union view joined to the other tables. For example, if a view is defined as "create view uv as select * from u1 union all select * from u2", and a query is executed as "select * from uv, a, b where uv.x = a.y and a.p = b.q", the optimizer will transform it to "select * from u1, a, b where u1.x = a.y and a.p = b.q union all select * from u2, a, b where u2.x = a.y and a.p = b.q".