sp_showcontrolinfo

Displays information about thread pool assignments, bound client applications, logins, and stored procedures.

Considerations for process mode

When you configure the SAP ASE server for process mode, sp_showcontrolinfo displays information about engine group assignments, bound client applications, logins, and stored procedures.

Syntax

sp_showcontrolinfo [object_type, object_name, spid ]

Parameters

object_type – one of:
- AP for application
- LG for login
- PR for stored procedure
- EG for thread pool (threaded mode) or engine group (process mode)
- SV for service task
- PS for process
- DF for user-defined default execution class
If you do not specify an object_type or specify an object_type of null, sp_showcontrolinfo displays information about all types.
object_name – is the name of the application, login, stored procedure, or engine group. Do not specify an object_name if you specify PS or DF as the object_type. If you do not specify an object_name (or specify an object_name of null), sp_showcontrolinfo displays information about all object names.
spid – is the SAP ASE process ID. Specify a spid only if you specify PS as the object_type. If you do not specify a spid (or specify a spid of null), sp_showcontrolinfo displays information for all spids. Use sp_who to see spids.

Examples

Example 1 – Shows all user-assigned execution class-to-object bindings:
```
sp_showcontrolinfo
```
Example 2 – Displays the execution class of the isql application:
```
sp_showcontrolinfo 'AP', 'isql'
```
Example 3 – Displays the execution class for all processes assigned to thread pools:
```
sp_showcontrolinfo 'PS'
```
Example 4 – Displays the execution class for spid 7:
```
sp_showcontrolinfo 'PS', null, 7
```

Usage

There are additional considerations when using sp_showcontrolinfo:

When used with no parameters, sp_showcontrolinfo displays information about all user-assigned thread pool assignments, bound client applications, logins, and stored procedures. When used with the object_type parameter, sp_showcontrolinfo provides information on an individual basis about application, login, or stored procedure bindings to an execution class, thread pool compositions, and session-level attribute bindings. See Distributing Engine Resources in the Performance and Tuning Series: Basics.
When run in process mode, sp_showcontrolinfo replaces thread_pool with the engine_group and engine columns.
Unless object_type is PR, execute sp_showcontrolinfo from the master database. If object_type is PR, execute sp_showcontrolinfo from the database in which the procedure resides.
If object_type is:
- null – sp_showcontrolinfo displays execution class information for objects that match the other parameters.
- DF – object_name and spid should be null, and sp_showcontrolinfo shows information about the user-defined default execution class.
If object_name is null, sp_showcontrolinfo displays the binding information for all applications, logins, and stored procedures.
If spid is null, sp_showcontrolinfo displays execution class information for objects that match the other parameters.

Permissions

Any user can execute sp_showcontrolinfo. Permission checks do not differ based on the granular permissions settings.

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