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