Show TOC

Introduction to Database OptionsLocate this document in the navigation structure

Database options control many aspects of database behavior including compatibility, error handling, and concurrency.

For example, you can use database options for the purposes such as:

  • Compatibility – lets you control how much like SAP ASE your SAP IQ database operates, and whether SQL that does not conform to SQL92 generates errors.
  • Error handling – lets you control what happens when errors, such as dividing by zero or overflow errors, occur.
  • Concurrency and transactions – lets you control the degree of concurrency and details of COMMIT behavior using options.

You set options with the SET OPTION statement, which has this general syntax:

SET [ EXISTING ] [ TEMPORARY ] OPTION
... [ <userid>. | PUBLIC. ]<option-name> = [ <option-value> ]

Specify a user ID or role name to set the option only for that user or role. Every user belongs to the PUBLIC role. If no user ID or role is specified, the option change is applied to the currently logged on user ID that issued the SET OPTION statement.

For example, this statement applies a change to the PUBLIC user ID, a role to which all users belong:

SET OPTION Public.login_mode = standard
Note

When you set an option to TEMPORARY without specifying a user or role, the new option value takes effect only for the currently logged on user ID that issued the statement, and only for the duration of the connection. When you set an option to TEMPORARY for the PUBLIC role, the change remains in place for as long as the database is running—when the database shuts down, TEMPORARY options for the PUBLIC role revert back to their permanent value.

When you set an option without issuing the TEMPORARY keyword, the new option value is permanent for the user or role who issued the statement.

See Scope and Duration of Database Options, Temporary Options, and SET OPTION Statement for more information on temporary versus permanent option values.

The maximum length of <option-value>, when set to a string, is 127 bytes.

Note

For all database options that accept integer values, SAP IQ truncates any decimal <option-value> setting to an integer value. For example, the value 3.8 is truncated to 3.

Caution

Do not change option settings while fetching rows.


In this section:
    Current Option Settings
    You can obtain a list of option settings, or the values of individual options, using sp_iqcheckoptions, sa_conn_properties, the SET statement, SAP IQ Cockpit, and the SYSOPTIONS system view.
    Scope and Duration of Database Options
    You can set options at three levels of scope: public, user, and temporary.
    Temporary Options
    Adding the TEMPORARY keyword to the SET OPTION statement changes the duration of the change.
    PUBLIC Options
    PUBLIC options can be set for a user, user extended role, or the PUBLIC role. They can be set for self or for another user or role.
    SECURITY Options
    SECURITY options are special category which are relevant to security of the database. It can be set at user level or PUBLIC level depending on options.
    SYSTEM Options
    SYSTEM options are special category which are relevant to security of the database. It can be set at user level or PUBLIC level.
    Delete an Option Setting
    Omit the <option-value> to delete the option setting from the database.
    Initial Option Settings
    You can use stored procedures to configure the initial database option settings of a user.
    Deprecated Database Options
    See New Features Summary SAP IQ 16.0 for information about database options deprecated in this release.
Related Information