Enables you to bring an engine online or offline. In threaded mode, use alter thread pool to bring engines online.
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.
In threaded mode, online increases the thread count for syb_default_pool by 1.
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.
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.
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.
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.
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 (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
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
As sp_engine works only in process mode, Adaptive 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.
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. sp_engine "shutdown" 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 Client Library™ or Java connections, you see:
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.
The permission checks for sp_engine differ based on your granular permissions settings.
Granular permissions enabled |
With granular permissions enabled, you must be a user with manage server privilege. |
Granular permissions disabled |
With granular permissions disabled, you must be a user with sa_role. |
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 |
|
