sp_dumpoptimize  sp_estspace

Chapter 1: System Procedures

sp_engine

Description

Enables you to bring an engine online or offline.

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.

offline

bring an engine offline. You can also use the engine_id parameter to specify a specific engine to bring offline.

can_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.

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.

“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 (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

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, 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

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 

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

Example 5

Shuts down engine 1 :

sp_engine shutdown, 1

Usage


Using sp_engine “offline” versus sp_engine “shutdown”

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.

Permissions

You must be a System Administrator to bring engines online or offline.

Auditing

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

  • 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





Copyright © 2005. Sybase Inc. All rights reserved. sp_estspace

View this book as PDF