The set statement specifies a runtime option for the current session. The selected option remains in effect until the session is terminated or the option is changed using another set statement.

This statement has the following syntax:

Parameters for the various set options are detailed in the following sections.

To issue the following statements, a user must have TRACE privilege:

To issue the set work locations statement, the effective user of the session must have MAINTAIN_LOCATIONS privilege. For more information see CREATE USER and ALTER USER in the Ingres SQL Reference Guide.

To issue the following statements, you must be the DBA of the database to which the session is attached:

To issue the set lockmode statement, the effective user of the session must have LOCKMODE privilege. LOCKMODE privilege is assigned using the Grant (privilege) Statement.

The set autocommit on statement directs the DBMS Server to treat 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.

The set autocommit statement cannot be issued in an open transaction. For a description multi-statement transaction behavior, see the chapter “Working with Transactions and Handling Errors.”

Set batch_copy_optim turns on copy optimization when executing batched inserts. This statement overrides the default set on the DBMS Server batch_copy_optim configuration parameter.

Set nobatch_copy_optim turns off copy optimization.

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 Session [No]Cache_Dynamic.

Set [no]cardinality_check lets you control the where clause singleton subselect cardinality checking.

In previous releases that supported where clause subqueries, a cardinality error (that is, a result contained too many results) was not raised unless set noflatten was specified. With the introduction of scalar subqueries, cardinality errors may be reported that were ignored previously.

Set cardinality_check raises the cardinality violation exception and otherwise restores the legacy behavior. The session default setting is derived from a CBF parameter ii.nodename.dbms.*.cardinality_check, which defaults to ON.

If you are working on a machine where CPU operations are more expensive than I/O operations, the default cpufactor can be set to a lower value. This causes the Optimizer to look at less CPU-intensive operations.

The default is 100 CPU operations to one disk I/O.

For example: Setting cpufactor to 10 indicates that one disk I/O is roughly equivalent to 10 CPU units.

Set date_format specifies the format for date values. This option corresponds to the Ingres 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 Workbench User Guide for a list of valid settings.

To set the date_format from within a 4GL routine, you must pass the set date_format a variable containing the desired format with single quotes before and after the date format string. For example:

Set decimal specifies the character to be used as the decimal point in numeric literals. This option corresponds to the Ingres 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. See the Workbench User Guide for more information about setting this option.

To set the decimal character from within a 4GL routine, you must pass to the set decimal a variable containing the desired format with single quotes before and after the date format string. For example:

Set flatten specifies that query flattening be used to optimize queries, including queries involving aggregate subselects or singleton subselects.

By default, Ingres performs query flattening. (The ANSI entry SQL-92 standard specifies that query flattening is not performed.)

Set noflatten disables query flattening.

Set hash allows the query optimizer to consider using hash aggregation and hash joins.

Set nohash stops the query optimizer from considering the use of hash aggregation and hash joins.

Set hash is the default.

Set io_trace prints information about disk I/O during the life of each query in the session. Using this option requires trace privilege.

Set noio_trace disables this tracing option.

See I/O Tracing in the Ingres System Administrator Guide for operating system-specific command and output examples.

This statement 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.

If a set joinop timeout nnn is issued (where nnn is in milliseconds) the query optimizer stops looking for query execution plans after the specified number of milliseconds and uses 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 Ingres default).

If a set joinop notimeout statement is issued, the optimizer searches all possible query plans.

A set joinop timeout statement 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:

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.

This statement 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. For details on the greedy optimization heuristic, see the Ingres Database Administrator Guide.

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.

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 table_name statement.

To disable journaling against a table, use the set nojournaling on table_name statement.

The help table table_name 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. (For more information, see the Ingres Command Reference Guide.)

The set lockmode statement changes the system defaults for locking that are in effect for a session (as defined for the DBMS in CBF). These defaults can be changed for any tables and indexes accessed during the session.

Use the set lockmode statement to optimize performance, alter the level of concurrency, or set locking back to either session or system level.

The set lockmode statement cannot be issued within a transaction, except for the statement:

The following set lockmode parameters control locking for a session:

The set lock_trace statement enables the display of locking activity for the current session, including information about the locks taken and locks released. Lock tracing can be started or stopped at any time during a session. For more information about the usage and output of this statement, see the Ingres Database Administrator Guide.

The set [no]logdbevents option enables or disables logging of event trace information for the application that raises events. When logging is enabled, event trace information is written to the installation log file.

To enable logging, specify set logdbevents.

To disable logging, specify set nologdbevents.

Only events raised by the application issuing the set statement are logged. Events received by the application are not logged.

The set nologging statement allows a session to bypass the logging and recovery system. This may speed up certain types of update operations but must be used with extreme caution.

The set nologging statement is intended to be used for operations for which the reduction of logging overhead and log file space usage outweigh the benefits of having to recover from transaction abort. Do not use the set nologging statement to try to improve performance during everyday use of a production database.

Caution! If journaling is set for the database within a session where nologging was set, none of the changes will be journaled.

When transaction logging is disabled, any error that occurs when the database is being updated (including interrupts, deadlock, lock timeout, and forced abort) or any attempt to rollback a transaction causes the DBMS Server to mark the database inconsistent.

To use the set nologging option it is recommended that the DBA:

To enable transaction logging, issue the set logging statement.

This set statement can be issued only by the DBA of the database on which the session is operating and cannot be issued within a multi-statement transaction.

If set nologging was in effect on a journaled database, we recommend that a checkpoint be taken immediately after the set logging statement is issued. When a session in set nologging mode disconnects from a database, the DBMS Server executes a set logging operation to bring the database to a guaranteed consistent state before completing the disconnect.

Session disconnect is an asynchronous operation. The user should issue an explicit set logging statement if subsequent operations rely on logging being enabled before they are executed.

Set nologging is rejected if the database is set to always be logged.

Set log_trace starts tracing of logfile writes. To stop tracing of logfile writes, use the set nolog_trace statement. Using this option requires trace privilege.

When you use set log_trace during a session, you receive a list of the log records written during execution of your query, along with other information about the log, including:

For information on the use of the set log_trace statement, see the Ingres Database Administrator Guide.

The set maxconnect statement is an alias for set maxidle. For details, see [No]Maxidle.

The set maxcost statement restricts the maximum cost per query on the database in terms of disk I/O and CPU usage. The maxcost value must be less than or equal to the session's value for query_cost_limit. If no query_cost_limit is set, there is no limit on cost usage per query. To set query_cost_limit for a user, use the grant statement.

When maxcost is set, it remains in effect until another set maxcost or set nomaxcost statement is issued or the session terminates.

For more information, see query_cost_limit in the description of Grant (privilege) Statement.

The set maxcpu statement restricts the maximum CPU usage per query on the database. The maxcpu value must be less than or equal to the session's value for query_cpu_limit. If no query_cpu_limit is set, there is no limit on CPU usage per query. To set query_cpu_limit for a user, use the grant statement.

When MAXCPU is set, it remains in effect until another set maxcpu or set nomaxcpu statement is issued or the session terminates.

For more information, see query_cpu_limit in the description of Grant (privilege) Statement.

The set maxidle option specifies whether a time limit is in force, and how long it is in seconds. The value entered must be less than that defined by the idle_time_limit session privilege. If no idle_time_limit is set, there is no time limit.

When maxidle is set, it remains in effect until another set maxidle or set nomaxidle statement is issued, or the session terminates. For more information, see idle_time_limit in the description of Grant (privilege) Statement.

The set maxio statement restricts the estimated number of I/O operations that can be used by each subsequent query to the value specified. Value must be less than or equal to the session's value for query_io_limit. If no query_io_limit is set, there is no limit on the amount of I/O performed

When MAXIO is set, it remains in effect until another set maxio or set nomaxio statement is issued or the session terminates.

To set query_io_limit for a user, use the grant statement. For more information, see Grant (privilege) Statement.

The set maxpage statement restricts the maximum number of pages per query on the database. The value must be less than or equal to the session's value for query_page_limit. If no query_page_limit is set, there is no limit on max page usage per query.

When maxpage is set, it remains in effect until another set maxpage or set nomaxpage statement is issued or the session terminates.

To set query_page_limit for a user, use the grant statement. For more information, see Grant (privilege) Statement.

The set maxquery statement is an alias for set maxio. For details, see [No]Maxio.

The set maxrow statement restricts the estimated number of rows that can be returned by subsequent queries. Value must be less than or equal to the session's value for query_row_limit. If no query_row_limit is set, there is no limit on the number of rows returned.

When MAXROW is set, it remains in effect until another set maxrow or set nomaxrow statement is issued, or the session terminates.

For more information, see query_row_limit in the description of Grant (privilege) Statement.

Set money_format specifies the format for money output. This option corresponds to the Ingres 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. It must be "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 and must contain leading and trailing single quotes (for example, 'T:DM').

For example:

Set money_prec specifies the number of decimal places to be displayed for money values. This option corresponds to the Ingres 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 noojflatten tells the query optimizer not to use the transformation that converts a not exists/not in subselect to an outer join with the containing query. The default is set ojflatten.

Optimizeonly specifies whether query execution should halt after the completion of query optimization.

Use set optimizeonly in conjunction with set qep when there is a requirement to view query execution plans without executing a query.

The set parallel statement controls whether the query optimizer enables the generation of parallel query execution plans.

The optional degree of parallelism value indicates the number of exchange nodes (or points of concurrency) built into the plan. The default value is 4 and the maximum is 32.

The set noparallel statement (or setting the degree of parallelism value to 0 or 1) tells the optimizer not to consider parallel query plans.

For a discussion of parallel query plans, see the Ingres Database Administrator Guide.

The set [no]printdbevents option enables or disables display of event trace information for the application that raises events.

To enable the display of trace information, specify set printdbevents.

To disable the display of trace information, specify set noprintdbevents.

This option displays only events raised by the application issuing the set statement. Events received by the application are not displayed.

The set printqry statement displays each query and its parameters as it is passed to the DBMS Server for processing.

The set noprintqry option disables this option.

The set printrules statement causes the DBMS Server to send a trace message to the application each time a rule is fired. This message identifies the rule and the associated database procedure that is invoked as a result of the rule's firing.

Issue the set noprintrules to disable rule-related trace messages. By default, rule-related trace messages are not displayed.

The set qep statement displays a diagrammatic representation of the query execution plan chosen for the query by the optimizer. The set noqep option disables this option.

For a discussion of query execution plans, see the Ingres Database Administrator Guide.

This statement sets the beginning value for the random functions. There is a global seed value and local seed values. The global 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.

The set result_structure statement changes the default storage structure for tables created with the create table...as select statement.

This storage structure can be any of the structures described in the MODIFY statement, that is, heap, cheap, heapsort, cheapsort, hash, chash, btree, cbtree, isam, or cisam.

The default storage structure for a table created by the create table as statement is cheap. For example:

does the same as:

For btree, cbtree, hash, chash, isam, and cisam, the newly created table is automatically indexed on the first column. This results in the above table “temp” being created with a storage structure of hash, keyed on the first column “id”.

Set role allows the session role to be changed during the life of the session.

Set role has the following syntax:

If set role none is specified, the session has no role.

If set role role is specified, the role is set to the indicated role. The user must be authorized to use that role. If the role has a password, the password must be specified using the with password clause.

If the user is not authorized to use the role or the password is incorrect, the session role is unchanged.

If a role has associated subject privileges or security audit attributes, these are added to the maximum privilege set for the session when the role is activated, and removed from the privilege set when the role is deactivated. Role security audit attributes can increase auditing over the current session value but cannot decrease it.

The set norules option disables any rules (user or system created) that would have been executed during the session after this statement is executed. To re-enable rules firing, issue the set rules statement. By default, rules are enabled.

The set session with on_error option specifies how transaction errors are handled in the current session.

To direct the DBMS Server to rollback the current transaction if an error occurs, specify rollback transaction.

To direct the DBMS Server to rollback only the current statement, specify rollback statement. This is the default.

To determine the current status of transaction error handling, issue the select dbmsinfo('on_error_state') statement.

Specifying 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.

The set session with on_error statement cannot be issued from within a database procedure or multi-statement transaction.

The set session with on_user_error option enables you to specify how user errors are handled in the current session. To direct the DBMS to roll back the effects of the entire transaction if a user error occurs, specify rollback transaction. To revert back to default behavior, specify norollback.

The set session authorization statement allows a user with security or dbadmin to set the effective user for the current session.

The set session 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:

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').

The set session [no]cache_dynamic setting persists for the duration of the session.

See also [No]Cache_dynamic.

Set nostatistics tells the query optimizer to ignore any statistics found in iihistograms or iistatistics for the specified table and to use its default values.

Set statistics tells the query optimizer to use any statistics found in iihistograms or iistatistics for the specified table.

The default is for the query optimizer to use statistics.

Set trace output filename sets the current DBMS trace log file as filename. If a file has already been defined for tracing, Ingres uses the newly set filename for subsequent tracing. If no file is currently defined for tracing (because the II_DMBS_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, as Ingres reserves the right to change their effect and output without notification.

A trace point starts with a Facility Identifier followed by a number of digits. Some trace points have additional parameters. For example:

Some trace points are equivalents of set statements. For example:

Set trace point qe5 is equivalent to set norules.

Set trace point OP212 is the eqivalent of setting TABLE_AUTO_STRUCTURE to ON in CBF.

The set transaction access mode controls the access mode for the current transaction.

The set transaction statement must be issued before a transaction commences and the settings last only until that transaction is completed.

The access mode options are as follows:

The set transaction isolation level statement controls the isolation level of the transaction.

The set transaction statement must be issued before a transaction starts and the settings last only until that transaction is completed.

The following three phenomena are relevant to isolation level:

Ingres supports the ANSI/ISO SQL92 standard levels of isolation:

The behaviors described above assume that the set lockmode setting for readlock has not been changed from the default, which is readlock=SHARED. The system will attempt to take out a readlock on rows as specified above, but its precise behavior is determined by the readlock setting.

The lockmode (see Lockmode) can be changed without affecting the isolation level, and vice versa. The readlock setting should be changed only with careful consideration.

When a Unicode value is being coerced into a local character set that has no equivalent character, an error is returned.

If unicode_substitution is set for the session, instead of returning the error, unmapped characters are replaced with a designated substitution character. The substitution character is the default subchar value specified in the mapping file. To override this value, provide a character value for the substitution character with this statement.

Set nounicode_substitution in the session reverts to the default behavior.

The set update_rowcount statement specifies the nature of the value returned by the inquire_sql(rowcount) statement.

Valid options are:

For example, consider the following table called test_table:

and the following query is executed:

The DBMS Server, for reasons of efficiency, does not actually update the first row because column1 already contains "Jones". However, the row does qualify for updating by the query.

If the update_rowcount option is set to changed, inquire_sql(rowcount) returns 2 (the number of rows actually changed).

If the update_rowcount option is set to qualified, inquire_sql(rowcount) returns 3 (the number of rows that qualified to be changed).

To determine the setting for the update_rowcount option, issue the following statement:

The following are set statement examples: