DB2 Version 10.1 for Linux, UNIX, and Windows

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. This setting overrides the instance-wide setting specified in the DB2_SQLROUTINE_PREPOPTS registry variable.


>>-SET_ROUTINE_OPTS--(--character-expression--)----------------><

The schema is SYSPROC.

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

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.

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')