sp_iqcheckoptions procedure

Function

For the connected user, sp_iqcheckoptions displays a list of the current value and the default value of database and server start-up options that have been changed from the default.

Syntax

sp_iqcheckoptions

Usage

Requires no parameters. Returns one row for each option that has been changed from the default value. The output is sorted by option name, then by user name.

Permissions

None. The DBA sees all options set on a permanent basis for all groups and users and sees temporary options set for DBA. Users who are not DBAs see their own temporary options. All users see nondefault server start-up options.

Description

For the connected user, the sp_iqcheckoptions stored procedure displays a list of the current value and the default value of database and server startup options that have been changed from the default. sp_iqcheckoptions considers all Sybase IQ and ASA database options. Sybase IQ modifies some ASA option defaults, and these modified values become the new default values. Unless the new Sybase IQ default value is changed again, sp_iqcheckoptions does not list the option.

When sp_iqcheckoptions is run, the DBA sees all options set on a permanent basis for all groups and users and sees temporary options set for DBA. Users who are not DBAs see their own temporary options. All users see nondefault server startup options.

Table 10-3: sp_iqcheckoptions columns

Column name

Description

User_name

The name of the user or group for whom the option has been set. At database creation, all options are set for the public group. Any option that has been set for a group or user other than public is displayed.

Option_name

The name of the option.

Current_value

The current value of the option.

Default_value

The default value of the option.

Option_type

“Temporary” for a TEMPORARY option, else “Permanent”.

Examples

In these examples, the temporary option APPEND_LOAD is set to ON and the group mygroup has the option MAX_WARNINGS set to 9. The user joel has a temporary value of 55 set for MAX_WARNINGS.

In the first example, sp_iqcheckoptions is run by the DBA.

User_name Option_name          Current_value   Default_value   Option_type
DBA       Ansi_update_constr   CURSORS         Off             Permanent
PUBLIC    Ansi_update_constr   Cursors         Off             Permanent
DBA       Append_Load          ON              OFF             Temporary
DBA       Checkpoint_time      20              60              Temporary
DBA       Connection_authent   Company=MyComp;                 Temporary
                               Application=DBTools;Signa 
DBA       Login_procedure      DBA.sp_iq_proce sp_login_envir  Permanent
PUBLIC    Login_procedure      DBA.sp_iq_proce sp_login_envir  Permanent
mygroup   Max_Warnings         9               281474976710655 Permanent
DBA       Min_NLPDJ_Table_Size 1000000         10000           Permanent
PUBLIC    Min_NLPDJ_Table_Size 1000000         10000           Permanent
DBA       Thread_count         25              0               Temporary

In the second example, sp_iqcheckoptions is run by the user joel.

User_name Option_name          Current_value   Default_value   Option_type
joel      Ansi_update_constr   CURSORS         Off             Permanent
PUBLIC    Ansi_update_constr   Cursors         Off             Permanent
joel      Checkpoint_time      20              60              Temporary
joel      Connection_authent   Company=MyComp;                 Temporary
                               Application=DBTools;Signa 
joel      Login_procedure      DBA.sp_iq_proce sp_login_envir  Permanent
PUBLIC    Login_procedure      DBA.sp_iq_proce sp_login_envir  Permanent
joel      Max_Warnings         55              281474976710655 Temporary
joel      Min_NLPDJ_Table_Size 1000000         10000           Permanent
PUBLIC    Min_NLPDJ_Table_Size 1000000         10000           Permanent
joel      Thread_count         25              0               Temporary