Enables you to bring an engine online or offline.
sp_engine {“online” | [offline | can_offline] [, engine_id] |
[“shutdown”, engine_id]}
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.
bring an engine offline. You can also use the engine_id parameter to specify a specific engine to bring offline.
returns information on whether an engine can be brought offline. can_offline returns the Adaptive Server 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.
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.
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.
Brings engine 1 online. Messages are platform specific (in this example, Sun Solaris was used):
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
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, Adaptive 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
Determines whether engine 1 can be brought offline:
sp_engine can_offline, 1
Takes engine 1 offline:
sp_engine offline, 1
Adaptive 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
Shuts down engine 1 :
sp_engine shutdown, 1
You cannot 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 then the current number of engines with an online status, and if enough CPU is available to support the additional engine.
An engine offline may fail or may not immediately take effect if there are server processes with an affinity to that engine.
Sometimes when you use sp_engine “offline", the engine does not immediately go offline, and instead appears to be in “dormant” state in the engine table. This is caused by processes that are attached to your engine that cannot be migrated to other engines. When this happens, the engine does not take new work, and consumes minimal CPU cycles. When the process preventing the completion of engine offline either end or become available for migration, the engine moves from dormant to fully offline, and disappears from the engine table.
sp_engine "shutdown" is a more aggressive version of the offline command. The sp_engine "shutdown" procedure actively kills any processes that are preventing the engine from going offline, forcing it to shut down.
However, if you use sp_engine "shutdown" on an engine that has ct-lib or java connections, you get the following error message:
Engine has outstanding ct-lib/java connections and cannot be offlined.
When this happens, repeat the command again every few minutes until the connections are no longer there, and the engine can shut down.
You must be a System Administrator to bring engines online or offline.
Values in event and extrainfo columns from the sysaudits table are:
Event |
Audit option |
Command or access audited |
Information in extrainfo |
|---|---|---|---|
38 |
exec_procedure |
Execution of a procedure |
|
