sp_configure

Syntax

sp_configure [configname [, configvalue] | group_name | 
	non_unique_parameter_fragment] 'drop instance' 
	[, instance_name] [display_nondefault_settings]

sp_configure "configuration file", 0, {"write" | "read" | "verify" | "restore"} 
	"file_name"

Parameters

configname – displays the current value, default value, most recently changed value, and amount of memory used by the setting for all parameters matching parameter.
configvalue – resets configname to configvalue and displays the current value, default value, configured value, and amount of memory used by configname.
sp_configure configname, 0, “default” resets configname to its default value and displays current value, default value, configured value, and amount of memory used by configname.
group_name – displays all configuration parameters in group_name, their current values, their default values, the value (if applicable) to which they have most recently been set, and the amount of memory used by this setting.
non_unique_parameter_fragment – displays all parameter names that match non_unique_parameter_fragment, their current values, default values, configured values, and the amount of memory used.
drop instance – allows you to drop an instance-specific configuration setting
instance_name – in cluster environments – indicates the instance for which you are setting the instance-specific options.
display_nondefault_settings – displays configuration options for which the configuration or run value is different from the default value.
write – creates file_name from the current configuration. If file_name already exists, a message is written to the error log and the existing file is renamed using the convention file_name.001, file_name.002, and so on. If you have changed a static parameter but have not restarted your server, “write” gives you the currently running value for that parameter.
read – performs validation checking on values contained in file_name and reads those values that pass validation into the server. If any parameters are missing from file_name, the current running values for those parameters are used.
verify – performs validation checking on the values in file_name.
restore – creates file_name with the values in sysconfigures. This is useful if all copies of the configuration file have been lost and you need to generate a new copy.
file_name – is the name of the file you want to use sp_configure on.

Examples

Example 1 – Displays all configuration parameters by group, their current values, their default values, the value (if applicable) to which they have most recently been set, and the amount of memory used by this setting:
```
sp_configure
```

Example 2 – Displays all configuration parameters that include the word “identity”:

sp_configure "identity"

Configuration option is not unique.

Parameter Name     Default Memory Used Config Value Run Value Unit Type
--------------     ------- ----------- ------------ --------- ------ ----
identity burning set     1         0           1        1     id    static
identity grab size       0         0           0        0     id    dyna
size of auto identit    10         0          10       10     bytes dyna
. . .

Example 3 – Sets the system recovery interval in minutes to 3 minutes:

sp_configure "recovery interval in minutes", 3

Parameter Name      Default Memory Used Config Value Run Value Unit Type
--------------       ------- ----------- ------------ --------- ------ ----
recovery interval   5        0          3            3         min   dyn

Configuration option changed. The SQL Server need not be rebooted since the option is dynamic.

Example 4 – Resets the value for number of devices to the SAP ASE default:
```
sp_configure "number of device", 0, "default"
```
Example 5 – Configures four databases to be recovered concurrently, enter:
```
sp_configure "max concurrently recovered db", 4
```
Example 6 – Starts four checkpoint tasks, enter:
```
sp_configure "number of checkpoint tasks", 4
```
Example 7 – Captures Query Processing metrics (qp metrics) at the server level:
```
sp_configure "enable metrics capture", 1 
```
Example 8 – Performs validation checking on the values in the file srv.config and reads the parameters that pass validation into the server. Current run values are substituted for values that do not pass validation checking:
```
sp_configure "configuration file", 0, "read",
    "srv.config"
```
Example 9 – Runs validation checking on the values in the file restore.config:
```
sp_configure "configuration file", 0, "restore", 
    "generic.config"
```
Example 10 – Creates the file my_server.config and writes the current configuration values the server is using to that file:
```
sp_configure "configuration file", 0, "write", 
    "my_server.config"
```
Example 11 – Performs a validation check on the values in $SYBASE/backup_config.cfg:
```
sp_configure "configuration file", 0, "verify", 
    "backup_config.cfg"
```

Usage

Any user can execute sp_configure to display information about parameters and their current values, but not to modify parameters. System administrators can execute sp_configure to change the values of most configuration parameters. Only system security officers can execute certain parameters. These are listed under “Permissions” in this section.
sp_configure allows you to specify the value for configuration paramters in unit specifiers. The unit specifiers are p or P for pages, m or M for megabytes, g or G for gigabytes, and t or T for terabytes. If you do not specify a unit, and you are configuring a parameter that controls memory, the SAP ASE server uses the logical page size for the basic unit.
Files created with sp_configure have restricted permissions.
When you execute sp_configure to modify a dynamic parameter:
- The configuration and run values are updated.
- The configuration file is updated.
- The change takes effect immediately.
When you execute sp_configure to modify a static parameter:
- The configuration value is updated.
- The configuration file is updated.
- The change takes effect only when you restart the SAP ASE server.
When issued with no parameters, sp_configure displays a report of all configuration parameters by group, their current values, their default values, the value (if applicable) to which they have most recently been set, and the amount of memory used by this setting:
- The default column in the report displays the value SAP ASE is shipped with. If you do not explicitly reconfigure a parameter, it retains its default value.
- The memory used column displays the amount of memory used by the parameter at its current value in kilobytes. Some related parameters draw from the same memory pool. For instance, the memory used for stack size and stack guard size is already accounted for in the memory used for number of user connections. If you added the memory used by each of these parameters separately, it would total more than the amount actually used. In the memory used column, parameters that “share” memory with other parameters are marked with a hash mark (#).
- The config_value column displays the most recent value to which the configuration parameter has been set with sp_configure.
- The run_value column displays the value being used by the SAP ASE server. It changes after you modify a parameter’s value with sp_configure and, for static parameters, after you restart the SAP ASE server. This is the value stored in syscurconfigs.value.
  
  Note: If the server uses a case-insensitive sort order, sp_configure with no parameters returns a list of all configuration parameters and groups in alphabetical order with no grouping displayed.
Each configuration parameter has an associated display level. There are three display levels:
- The “basic” level – displays only the most basic parameters. It is appropriate for very general server tuning.
- The “intermediate” level – displays parameters that are somewhat more complex, as well as showing you all the “basic” parameters. This level is appropriate for a moderately complex level of server tuning.
- The “comprehensive” level – default display level. Displays all parameters, including the most complex ones. This level is appropriate for users who do highly detailed server tuning.
  
  Setting one of the other display levels lets you work with a subset of the configuration parameter, shortening the amount of information displayed by sp_configure.
The syntax for showing your current display level is:
```
sp_displaylevel
```
sp_configure can run in sessions using chained transaction mode if there are no open transactions.
For information on the individual configuration parameters, see the System Administration Guide.

Permissions

The permission checks for sp_configure differ based on your granular permissions settings. Any user can display information about parameters and their current values.

Setting	Description
Enabled	With granular permissions enabled: Only a user with `manage security configuration` privilege can execute sp_configure to modify values for parameters in table <`table number`>. You must have the manage server configuration privilege to execute sp_configure to modify values for other configuration parameters.
Disabled	With granular permissions disabled: Only user with sso_role can execute sp_configure to modify values for parameters in table <`table number`>. You must have sa_role to execute sp_configure to modify values for other configuration parameters: With granular permissions disabled, you must have sa_role to execute sp_configure to modify values for other configuration parameters: allow procedure grouping allow remote access allow sendmsg allow updates to system tables audit queue size auditing automatic master key access check password for digit curread change w/ open cursors current audit table enable encrypted columns enable granular permissions enable ldap user auth enable logins during recovery enable pam user auth enable predicated privileges enable ssl FIPS login password encryption log audit logon failure log audit logon success maximum failed logins minimum password length msg confidentiality reqd msg integrity reqd net password encryption reqd restricted decrypt permission secure default login select on syscomments.text SQL Perfmon Integration suspend auditing when device full syb_sendmsg port number systemwide password expiration unified login required use security services

Setting

Description

Enabled

With granular permissions enabled:

Only a user with manage security configuration privilege can execute sp_configure to modify values for parameters in table <table number>.
You must have the manage server configuration privilege to execute sp_configure to modify values for other configuration parameters.

Disabled

With granular permissions disabled:

Only user with sso_role can execute sp_configure to modify values for parameters in table <table number>.
You must have sa_role to execute sp_configure to modify values for other configuration parameters:

With granular permissions disabled, you must have sa_role to execute sp_configure to modify values for other configuration parameters:

allow procedure grouping
allow remote access
allow sendmsg
allow updates to system tables
audit queue size
auditing
automatic master key access
check password for digit
curread change w/ open cursors
current audit table
enable encrypted columns
enable granular permissions
enable ldap user auth
enable logins during recovery
enable pam user auth
enable predicated privileges
enable ssl
FIPS login password encryption
log audit logon failure
log audit logon success
maximum failed logins
minimum password length
msg confidentiality reqd
msg integrity reqd
net password encryption reqd
restricted decrypt permission
secure default login
select on syscomments.text
SQL Perfmon Integration
suspend auditing when device full
syb_sendmsg port number
systemwide password expiration
unified login required
use security services

Auditing

Values in event and extrainfo columns from the sysaudits table are:

Information	Values
Event	38
Audit option	exec_procedure
Command or access audited	Execution of a procedure
Information in `extrainfo`	Roles – Current active roles Keywords or options – NULL Previous value – NULL Current value – NULL Other information – All input parameters Proxy information – Original login name, if set proxy in effect

Information	Values
Event	73
Audit option	Automatically audited event nto controlled by an option.
Command or access audited	Turning the auditing parameter on with sp_configure
Information in `extrainfo`	Roles – Current active roles Keywords or options – NULL Previous value – NULL Current value – NULL Other information – NULL Proxy information – Original login name, if set proxy in effect

Information	Values
Event	74
Audit option	Automatically audited event nto controlled by an option.
Command or access audited	Turning the auditing parameter off with sp_configure
Information in `extrainfo`	Roles – Current active roles Keywords or options – NULL Previous value – NULL Current value – NULL Other information – NULL Proxy information – Original login name, if set proxy in effect

Information	Values
Event	82
Audit option	security
Command or access audited	sp_configure
Information in `extrainfo`	Roles – Current active roles Keywords or options – Name of the configuration parameter Previous value – Old parameter value if command is setting a new value Current value – New parameter value if command is setting a new value Other information – Number of configuration parameter, if a parameter is being set; name of configuration file, if a configuration file is being used to set parameters Proxy information – Original login name, if set proxy in effect