sp_engine

Syntax

sp_engine {“online” | [offline | can_offline] [, engine_id] | 
[“shutdown”, engine_id]}

Parameters

“online” – bring an engine online. The value of sp_configure “max online engines” must be greater than the current number of engines online. Because “online” is a reserved keyword, you must use quotes.
In threaded mode, online increases the thread count for syb_default_pool by 1.
offline – bring an engine offline. You can also use the engine_id parameter to specify a specific engine to bring offline.
In threaded mode, offline decreases the thread count for syb_default_pool by 1.
can_offline – returns information on whether an engine can be brought offline. can_offline returns the SAP ASE tasks with an affinity to this engine (for example, during Omni or java.net tasks) if its state is online. If you do not specify an engine_id, the command describes the status of the engine in sysengines with the highest engine_id.
In threaded mode, can_offline succeeds only if the total number of engines is less than the total number of threads in syb_default_pool and the total number of threads in syb_default_pool is greater than or equal to 2.
engine_id – the ID of the engine. The engine_id parameter is optional. If you do not specify an engine_id, sp_engine uses the incremented or decremented value for engine_id for the value of engine found within sysengines. That is, if your system uses engines 0, 1, 2, and 3, and you do not specify an engine ID, sp_engine takes engine ID 3 offline, then engine ID 2, and so on.
This parameter is ignored in threaded mode.
“shutdown” – Forces an engine offline. If there are any tasks with an affinity to this engine, they are killed after a five-minute wait. You must use quotes, as shutdown is a reserved keyword.

Examples

Example 1 – Brings engine 1 online. Messages are platform specific (this example uses Sun Solaris):

sp_engine "online", 1

02:00000:00000:2001/10/26 08:53:40.61 kernel  Network and device connection 
limit is 3042.
02:00000:00000:2001/10/26 08:53:40.61 kernel  SSL Plus security modules 
loaded successfully.
02:00000:00000:2001/10/26 08:53:40.67 kernel  engine 2, os pid 8624  online
02:00000:00000:2001/10/26 08:53:40.67 kernel  Enabling Sun Kernel 
asynchronous disk I/O strategy
00:00000:00000:2001/10/26 08:53:40.70 kernel  ncheck: Network fc0330c8 
online

Example 2 – Describes the steps in taking an engine offline that is currently running tasks with an affinity for this engine:

select engine, status from sysengines

engine    status
------    ------
0         online
1         online
2         online
3         online

If you bring engine 1 offline:

sp_engine offline, 1

The following task(s) will affect the offline process:
spid: 19 has outstanding ct-lib connections.

And then run the same query as above, it now shows that engine 1 is in an offline state:

select engine, status from sysengines

engine    status
------    ------
0         online
1         in offline
2         online
3         online

As soon as the task that has an affinity to engine 1 finishes, the SAP ASE server issues a message similar to the following to the error log:

02:00000:00000:2001/10/26 09:02:09.05 kernel  engine 1, os pid
8623  offline

Example 3 – Determines whether engine 1 can be brought offline:
```
sp_engine can_offline, 1
```

Example 4 – Takes engine 1 offline:

sp_engine offline, 1

The SAP ASE server eventually returns a message similar to the following:

01:00000:00000:2001/11/09 16:11:11.85 kernel  Engine 1 waiting for 
affinitated process(es) before going offline
01:00000:00000:2001/11/09 16:11:11.85 kernel  Process 917518 is preventing 
engine 1 going offline
00:00000:00000:2001/11/09 16:16:01.90 kernel  engine 1, os pid 
21127  offline

Example 5 – Shuts down engine 1 :
```
sp_engine shutdown, 1
```

Usage

As sp_engine works only in process mode, the SAP ASE server issues an error message if you run sp_engine in threaded mode. Use alter thread pool in threaded mode.
You cannot take offline or shut down engine 0.
You can determine the status of an engine, and which engines are currently online with the following query:
```
select engine, status from sysengines
where status = "online"
```
online and shutdown are keywords and must be enclosed in quotes.
Engines can be brought online only if max online engines is greater than the current number of engines with an online status, and if enough CPU is available to support the additional engine.
sp_engine can run in sessions using chained transaction mode if there are no open transactions.
An engine offline command may fail or may not immediately take effect if there are server processes with an affinity to that engine.

Permissions

The permission checks for sp_engine differ based on your granular permissions settings.

Setting	Description
Enabled	With granular permissions enabled, you must be a user with`manage server` privilege.
Disabled	With granular permissions disabled, you must be a user with sa_role.

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