ALTER PUMP

ALTER PUMP is used to start/restart or stop/pause the operation of one or more pumps. If you start multiple pumps, these will be started in topological order*. That is, pumps that depend on other pumps for their data will be started after the pumps they require.

You can use a wildcard character to indicate pumps by schema (such as myschema.*), or list them individually, (pump1, pump2, pump3). Wildcarding for referencing catalog objects uses a standard SQL syntax. Statements that use incorrect syntax (such as *schema_*.) will fail.

Topological order is automatically calculated by s-Server. As a result, you can list pumps in the ALTER statement in any order.

Starting Pumps in Topological Order

In order for an application or application module to function correctly, you should start all the pumps in a single ALTER PUMP statement. This ensures no data loss.

It is also best practice to stop all pumps with a single ALTER PUMP statement to undeploy an application or application module.

Pump Behavior on s-Server Shutdown or Restart

When s-Server is shutdown gracefully, all active pumps are suspended.

When s-Server is restarted, any pumps that were suspended at shutdown time are restarted (resumed). Pumps that have been stopped by a user SQL script will not be restarted automatically, and must therefore be restarted by a SQL script.

Syntax

<alterpump> ::= ALTER PUMP <qualified-pump-name> | <pumps-list> <pumpaction>
<pumps-list> ::= [pump-expr[, pump-expr ] * ]
<pumpaction> ::= START | STOP | SET  '<variable> = '<value>'

Chart

Examples of Starting and Stopping Pumps

-- start pump "mypump" in current schema
ALTER PUMP "mypump" START;

-- start pump "mypump" in schema "myschema"
ALTER PUMP "myschema"."mypump" START;

-- start all pumps in myschema in topological order
ALTER PUMP myschema.* START;

-- stop all pumps in “myschema” in reverse topological order
ALTER PUMP myschema.* STOP;

-- start a given list of pumps; the execution order is calculated in topological order 
-- by the execution engine, not necessarily in the order the pumps are listed in this statement 
ALTER PUMP pump1, pump2, pump21, pump11 START;
-- or
ALTER PUMP newschema.pump2, oldschema.* START;

-- start all pumps in the catalog
-- Pumps without start/stop privileges for the user will be skipped
ALTER PUMP *.* START;

Examples of Setting Session Variables for Pumps

In each ALTER PUMP SET statement, just one session variable may be set for one or more pumps:

-- set a single variable
ALTER PUMP "myschema"."mypump" SET myvariable = 'a value';

-- set the same variable for several pumps
ALTER PUMP "myschema1"."mypump1", "myschema2"."mypump2" SET myvar1 = 'a value';
ALTER PUMP "myschema1".* SET myvar1 = 'a value';

To list the values of session variables associated with pumps, please use the PUMP_OPTIONS view.

For more information please see User Defined Session Variables.