SET Options
SET [ACTUAL | ESTIMATED] MAXROW n
Limits the number of actual or estimated rows returned in a query to n. ESTIMATED is the default and does not need to be specified.
SET AGGR_TIMESTAMPZ ON|OFF
Timestamp with timezone values are normalized for grouping or not normalized for grouping during aggregation, when used in the group by-clause.
Normalization here means, that two timestamps, which denote the same UTC point in time, compare as equal for grouping purposes. Valid values are:
ON-(Default) Values are normalized during aggregation
OFF-Values are not normalized during aggregation
The values can be set at the session level as:
• set aggr_timestamptz on
• set aggr_timestamptz off
Note: Even if the aggr_timestamptz is off, two different values which denote the same UTC point in time compare as equal anywhere else, except for grouping purposes.
Note: For X100 tables, normalization is enabled or disabled for the UNION operator. For non-X100 tables, the parameter will not impact the UNION operator as the output is always normalized.
SET AUTOCOMMIT ON|OFF|READ
SET AUTOCOMMIT ON treats each query as a single-query transaction.
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:
• The SET [SESSION or TRANSACTION] READ ONLY statement is in effect, or:
• The AUTOCOMMIT state is ON or READ, and one of the following query types is issued:
– A SELECT statement (including SELECT loop in Embedded SQL)
– A COPY INTO statement
– A CREATE or DROP STATISTICS statement*
– An OPEN cursor statement, whether FOR READONLY or not, if the cursor query references Vector tables*
*The transaction is started read write on the Ingres side but read only on the Vector side.
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.
SET [NO]BATCH_COPY_OPTIM
Enables or disables copy optimization when executing batched inserts. This statement overrides the default set on the DBMS Server batch_copy_optim configuration parameter.
SET [NO]CACHE_DYNAMIC
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.
SET CONNECTION connection_name | NONE
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.
SET DATE_FORMAT [value]
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. For valid settings, see the System Administrator Guide.
SET DECIMAL [value]
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.
SET [NO]FLSQL
Overrides the setting of the DBMS configuration parameter opf_flsql in CBF. The default is ON, which means the optimizer attempts to flatten more correlated subqueries than previously. Use this parameter only if a flattened query (which was previously not flattened) is seen to degrade performance.
SET FLOAT_FORMAT 'format'
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.
SET INSERTMODE DEFAULT | ROW | BULK
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:
DEFAULT
Uses default behavior: Single row inserts go through the PDT, INSERT...AS SELECT does not.
ROW
Inserts through the PDT (allows concurrent inserts)
BULK
Appends directly to disk (does not allow concurrent inserts)
For more information, see the Vector User Guide.
SET JOINOP NOTIMEOUT | TIMEOUT | TIMEOUT nnn
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.
SET JOINOP TIMEOUTABORT nnn
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.
• If TIMEOUTABORT is greater than TIMEOUT, then both timing strategies are in effect.
• If the TIMEOUT time expires, the search for a better plan stops if any plan is found.
• Otherwise, optimization continues until either a plan is found or the TIMEOUTABORT timer expires.
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.
SET JOINOP [NO]GREEDY
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.
SET NOJOURNALING | JOURNALING [ON table_name]
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.
SET MAXROWSTEP n
Specifies the number of rows that an individual query step cannot exceed before the query is canceled.
SET MONEY_FORMAT [value]
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:
Definition Result
L:$ $100
T:DM 100DM
T:F 100F
SET MONEY_PREC [value]
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).
SET [NO]OJFLATTEN
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.
SET [NO]OPTIMIZEONLY
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.
SET PARTITION_PARTS n
SET NOPARTITION_PARTS
Sets the number of partitions that should be created when a default partitioning scheme is used. SET PARTITION_PARTS overrides the default_npartitions config.dat parameter for the session. SET NOPARTITION_PARTS removes the session setting and uses the system default. The supported range of values for the number of partitions in a partition table are 1-923.
SET PARTITION_SCHEME {AUTO | NONE | HASH}
SET NOPARTITION_SCHEME
Automatically assigns a partition scheme when an X100 table is created during a CREATE TABLE statement. Valid values are:
NONE
No partitioning.
HASH
Hash partitioning on the first column of the table.
AUTO
Automatic partitioning.
SET PARTITION_SCHEME overrides the x100_partition_scheme config.dat parameter for the session. SET NOPARITION_SCHEME removes the session setting and uses the system default.
SET [NO]PRINTQRY
Displays or does not display each query and its parameters as it is passed to the DBMS Server for processing.
SET [NO]QEP [CONCISE | SEGMENTED]
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.
SET QUERY_RESULT_CACHE {ON | OFF | X100 | INGRES}
Enables or disables query result caching. Valid values are:
ON—(Default) Enables query result caching.
OFF—Disables query result caching.
X100 or VECTOR—Enables query result caching for X100 tables only.
INGRES—Enables query result caching for Ingres tables only.
SET RANDOM_SEED [value]
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.
SET RESULT_STRUCTURE
Forces WITH STRUCTURE=X100, X100_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.
SET [NO]REUSE
Disables the "reuse" heuristic of the query optimizer. For more information, see the Vector User Guide.
SET [NO]SCHEMA
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:
SELECT DBMSINFO('SESSION_SCHEMA')
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 ON|OFF [with-clause]
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.
Valid options are:
ON | OFF
Turns tracing on or off.
with-clause
Consists of the keyword WITH followed by one or more of the following options separated by a comma.
Default: NODBCHECK, NOUSERCHECK, NOERROR, BLOBSEGS=16, NOQEP, NODETAIL, X100ALGEBRA, NOX100PROFILE, MINTIME=0
DIRECTORY='path'
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.
Required for SESSION_TRACE ON.
Optional for SERVER_TRACE if there is a "SET TRACE RECORD 'path'" in effect.
Example: SET SERVER_TRACE ON WITH DIRECTORY='/temp/servertrace/'
DBONLY=(dbname,dbname,...)
Traces only sessions that are accessing the specified databases. Limit 20 databases.
Invalid for SESSION_TRACE.
DBEXCEPT=(dblist)
Traces only sessions that are not accessing the specified databases. Limit 20 databases.
Invalid for SESSION_TRACE.
NODBCHECK
Turns off database checks.
USERONLY=(user,user,...)
Traces only sessions that are accessed by the specified users (real user, not effective user). Limit 20 users.
Invalid for SESSION_TRACE.
USEREXCEPT=(userlist)
Traces only sessions that are not accessed by the specified users (real user, not effective user). Limit 20 users.
Invalid for SESSION_TRACE.
NOUSERCHECK
Turns off user checks.
ERROR=('ffxxxx',...)
Traces only the queries that result in the listed errors. For example, 'US0083', 'E_US0083'.
ERROR (no parameters) traces any query that results in any error or warning.
NOERROR
Excludes error messages.
BLOBSEGS=n
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.
QEP[=(list)] | NOQEP
Provides or does not provide QEPs for each query. List items affect the QEP style or display. Valid list items are:
empty list - Normal style
SEGMENTED - Segmented style
CONCISE - Concise style (all on one line)
NOCOST - Suppresses disk/CPU cost display in the QEP
FLOAT - Displays all disk/CPU costs as floating point in the QEP
Default is NOQEP.
[NO]DETAIL
Excludes or includes certain information pertaining to recording and playback, such as TDESCR, COL, all but first FETCH of cursor.
[NO]X100ALGEBRA
Prints the X100 algebra.
[NO]X100PROFILE
Prints the X100 profile data. X100PROFILE implies NOX100ALGEBRA.
MINTIME=n, MAXTIME=n
Traces only queries that meet the specified timing filters, plus session begin and end time, and x100 connect time.
Valid values: a non-negative integer, 0 for NO time filtering.
MAXTIME is invalid without a MINTIME.
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:
SET SESSION_TRACE ON WITH QEP, X100ALGEBRA;
SET SESSION_TRACE ON WITH NOX100ALGEBRA;
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.
SET SESSION AUTHORIZATION
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:
username
The user name specified
USER | CURRENT USER | SESSION USER
The current user of the session (Vector user)
SYSTEM_USER
The operating system user who started the session
INITIAL_USER
The Vector user ID that started the session
SET SESSION [NO]CACHE_DYNAMIC
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'). See also SET [NO]CACHE_DYNAMIC.
SET SESSION READ ONLY | READ WRITE
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 begins, 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 ON|OFF [with-clause]
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.
SET SESSION WITH ON_ERROR
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:
• Deadlock
• Forced abort
• Lock quota exceeded
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.
SET SESSION WITH ON_USER_ERROR
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.
SET [NO]STATISTICS tablename
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.
SET STRING_TRUNCATION IGNORE | WARN | FAIL
Sets error handling mode for string truncation errors. Such an error occurs if an attempt is made to insert a string into a table column that is too short to contain the value. Valid values are:
IGNORE—Truncates and inserts the string without issuing an error message.
WARN—Truncates and inserts the string and issues a warning message.
Note: X100 operations treat WARN as FAIL.
FAIL—(Default) Aborts the statement and issues an error message. The FAIL option is supported only for singleton INSERTs and COPY. It is supported only in cases where the literal string value can be validated, so it is not supported for CREATE TABLE AS SELECT, INSERT...SELECT, nor for updates where the string value originates from an expression from another table.
SET [NO]TRACE OUTPUT ‘filename’
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 [NO]TRACE POINT
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.
[NO]TRACE TERMINAL
SET TRACE TERMINAL 'terminalname' sets the terminal the DBMS trace logs are written to. If a terminalname is currently defined for tracing, then Vector uses the newly defined terminalname for subsequent tracing. If no terminal is currently defined for tracing (either SET TRACE TERMINAL has not been executed once or the latest trace terminal command executed has been SET NOTRACE TERMINAL), then terminalname will be used for subsequent tracing.
The 'terminalname' option must be a quoted string and the complete path of the terminal device in the OS file system. For example, to set terminal pts/6 on a Linux machine as the trace terminal, use the command SET TRACE TERMINAL '/dev/pts/6'.
SET NOTRACE TERMINAL closes the trace terminal. No trace information is written to any terminal until a subsequent SET TRACE TERMINAL statement is executed.
SET TRANSACTION READ ONLY | READ WRITE
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:
READ ONLY
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 begins, it registers itself with the logging system and is allowed to proceed even when a ckpdb is pending against the database for the session.
READ WRITE
When you need to write to the database, use SET TRANSACTION READ WRITE for that transaction.
Note: The access mode of a transaction has no effect on the locking mode of the transaction.
SET [NO]UNION_FLATTENING
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".
This statement overrides the default set on the CBF opf_union_flattening configuration parameter. The default is OFF.