-STOP PROCEDURE command (Db2)

The -STOP PROCEDURE command prevents Db2 from accepting SQL CALL statements for one or more stored procedures.

You can qualify stored procedure names with a schema name. This command does not prevent CALL statements from running if they have already been queued or scheduled by Db2.

Db2 implicitly issues the command STOP PROCEDURE ACTION(REJECT) for any stored procedure that exceeds the maximum abend count. That count is set by the MAX ABEND COUNT field of installation panel DSNTIPX.

Abbreviation: -STO PROC

Environment for -STOP PROCEDURE

This command can be issued from a z/OS® console, a DSN session under TSO, a DB2I panel (DB2 COMMANDS), an IMS or CICS® terminal, or a program using the instrumentation facility interface (IFI).

Data sharing scope: Group or member

Authorization for -STOP PROCEDURE

To execute this command, you must use a privilege set of the process that includes one of the following authorities:
  • Ownership of the stored procedure
  • System DBADM authority
  • SYSOPR authority
  • SYSCTRL authority
  • SYSADM authority
If you specify STOP PROCEDURE *.* or schema.partial-name *, the privilege set of the process must include one of the following authorities:
  • SYSOPR authority
  • SYSCTRL authority
  • SYSADM authority

Db2 commands that are issued from a logged-on z/OS console or TSO SDSF can be checked by Db2 authorization using primary and secondary authorization IDs. Start of changeA logged-on z/OS user ID must be defined in RACF or a similar security server.End of change

Syntax for -STOP PROCEDURE

Read syntax diagramSkip visual syntax diagramSTOP PROCEDURE( *.*)(,schema.procedure-nameschema.partial-name*procedure-namepartial-name*)ACTION(QUEUEREJECT)SCOPE(LOCALGROUP)

Option descriptions for -STOP PROCEDURE

(*.*)
Stops access to all stored procedures in all schemas, including procedure definitions that have not yet been accessed by Db2 applications.
( schema.procedure-name )
Identifies the fully-qualified procedure name that is to be stopped.
( schema.partial-name *)
Stops a set of stored procedures in the specified schema. The names of all procedures in the set begin with partial-name and can end with any string, including the empty string. For example, PAYROLL.* stops all stored procedures in the PAYROLL schema.
procedure-name
Identifies one or more specific stored procedure names to be stopped. The procedure name is implicitly qualified with the SYSPROC schema name.
partial-name *
Stops a set of stored procedures within the SYSPROC schema. The names of all procedures in the set begin with partial-name and can end with any string, including the empty string. For example, ABC* stops all stored procedures with names that begin with ABC.
ACTION
Indicates what to do with a CALL statement that is received while the procedure is stopped. If STOP PROCEDURE is issued more than once for a given procedure, the action taken is determined by the ACTION option on the most recent command.
(QUEUE)
Queues the request until either:
  • The wait exceeds the installation timeout value, or
  • The stored procedure is started by the command START PROCEDURE.
(REJECT)
Rejects the request
SCOPE
Specifies the scope of the command.
( LOCAL )
Specify to stop the procedure on the local member only.
(GROUP)
Specify to stop the procedure on all members of the data sharing group.

Usage notes for -STOP PROCEDURE

Permanently disabling a stored procedure
A stopped procedure does not remain stopped if Db2 is stopped and restarted. To disable a stored procedure permanently, you can:
  • Drop the procedure using the DROP PROCEDURE statement.
  • Use an ALTER PROCEDURE statement.
  • Rename or delete the z/OS load module.
Considerations for native SQL procedures
The STOP PROCEDURE command affects all versions of the native SQL procedures that you specify in the command.
Trace information for commands issued with group scope
If this command is issued with options that specify group scope in a Db2 data sharing member, it also runs on all other active members. IFICID 090 trace records for other group members can show that the same command was issued by the SYSOPR authorization ID from the 016.TLPKN5F correlation ID, in addition to the trace records from the member where the original command was issued. See Command scope in Db2 data sharing.

Examples for -STOP PROCEDURE

Example 1: Stop access to all stored procedures, and terminate the Db2 stored procedures address space. While the STOP PROCEDURE command is in effect, attempts to execute stored procedures are queued. 
-STOP PROCEDURE ACTION(QUEUE)
DSNX947I - DSNX9SP2 STOP PROCEDURE SUCCESSFUL FOR *.*
DSN9022I - DSNX9COM '-STOP PROC' NORMAL COMPLETION
Example 2: Stop access to all stored procedures, and terminate the Db2 stored procedures address space. While the STOP PROCEDURE command is in effect, attempts to execute stored procedures are rejected. 
-STOP PROCEDURE ACTION(REJECT)
DSNX947I - DSNX9SP2 STOP PROCEDURE SUCCESSFUL FOR *.*
DSN9022I - DSNX9COM '-STOP PROC' NORMAL COMPLETION
Example 3: Stop stored procedures USERPRC1 and USERPRC3. While the STOP PROCEDURE command is in effect, attempts to execute these stored procedure are queued. 
-STOP PROCEDURE(USERPRC1,USERPRC3)
DSNX947I - DSNX9SP2 STOP PROCEDURE SUCCESSFUL FOR USERPRC1
DSNX947I - DSNX9SP2 STOP PROCEDURE SUCCESSFUL FOR USERPRC3
DSN9022I - DSNX9COM '-STOP PROC' NORMAL COMPLETION
Example 4: Stop stored procedures USERPRC1 and USERPRC3. While the STOP PROCEDURE command is in effect, attempts to execute these stored procedure are rejected. 
-STOP PROCEDURE(USERPRC1,USERPRC3) ACTION(REJECT)
DSNX947I - DSNX9SP2 STOP PROCEDURE SUCCESSFUL FOR USERPRC1
DSNX947I - DSNX9SP2 STOP PROCEDURE SUCCESSFUL FOR USERPRC3
DSN9022I - DSNX9COM '-STOP PROC' NORMAL COMPLETION