SET_ROUTINE_OPTS procedure

The SET_ROUTINE_OPTS procedure sets the options that are to be used for the creation of SQL procedures in the current session.

Options set with the SET_ROUTINE_OPTS procedure override the instance-wide setting specified in the DB2_SQLROUTINE_PREPOPTS registry variable.

The schema is SYSPROC.

Authorization

One of the following authorities is required to execute the procedure:
  • EXECUTE privilege on the procedure
  • DATAACCESS authority
  • DBADM authority
  • SQLADM authority

Default PUBLIC privilege

In a non-restrictive database, EXECUTE privilege is granted to PUBLIC when the procedure is automatically created.

Syntax

Read syntax diagramSkip visual syntax diagramSET_ROUTINE_OPTS(character-expression )

Procedure parameter

character-expression
An input argument of type VARCHAR(1024) that specifies the options setting for the current session.

Specified options are valid for the duration of the session. If the null value is specified as the argument, the value of the DB2_SQLROUTINE_PREPOPTS registry variable is restored as the default options setting for the current session. For a list of the allowed options, see the description of the DB2_SQLROUTINE_PREPOPTS registry variable under Query compiler variables.

Example 1: Set the options setting for the current session to NULL.
   CALL SYSPROC.SET_ROUTINE_OPTS(CAST (NULL AS VARCHAR(1)))
Example 2: Set the options setting for the current session to EXPLAIN YES.
   CALL SET_ROUTINE_OPTS('EXPLAIN YES')
Example 3: Set the options setting for the current session to EXPLAIN YES and BLOCKING NO.
   CALL SET_ROUTINE_OPTS('EXPLAIN YES BLOCKING NO')