Customizing precompile and bind options for compiled SQL objects

The precompile and bind options for SQL procedures, compiled functions, compiled triggers and compound SQL (compiled) statements can be customized via a Db2® registry variable or some SQL procedure routines.

About this task

To customize precompile and bind options for compiled SQL objects, set the instance-wide Db2 registry variable, DB2_SQLROUTINE_PREPOPTS. For example:

   db2set DB2_SQLROUTINE_PREPOPTS=options

The options can be changed at the procedure level with the SET_ROUTINE_OPTS stored procedure. The values of the options set for the creation of SQL procedures in the current session can be obtained with the GET_ROUTINE_OPTS function.

The options used to compile a given routine are stored in the system catalog table ROUTINES.PRECOMPILE_OPTIONS, in the row corresponding to the routine. If the routine is revalidated, those stored options are also used during the revalidation.

After a routine is created, the compile options can be altered using the SYSPROC.ALTER_ROUTINE_PACKAGE and SYSPROC.REBIND_ROUTINE_PACKAGE procedures. The altered options are reflected in the ROUTINES_PRECOMPILE_OPTIONS system catalog table.

Note: Cursor blocking is disabled in SQL procedures for cursors referenced in FETCH statements and for implicit cursors in FOR statements. Regardless of the value specified for the BLOCKING bind option, data will be retrieved one row at a time in an optimized, highly efficient manner.

Example

The SQL procedures used in this example will be defined in following CLP scripts. These scripts are not in the sqlpl samples directory, but you can easily create these files by cutting-and-pasting the CREATE procedure statements into your own files.

The examples use a table named "expenses", which you can create in the sample database as follows:

   db2 connect to sample
   db2 CREATE TABLE expenses(amount DOUBLE, date DATE)
   db2 connect reset

To begin, specify the use of ISO format for dates as an instance-wide setting:

   db2set DB2_SQLROUTINE_PREPOPTS="DATETIME ISO"
   db2stop
   db2start

Stopping and restarting the Db2 instance is necessary for the change to take affect.

Then connect to the database:

   db2 connect to sample

The first procedure is defined in CLP script maxamount.db2 as follows:

   CREATE PROCEDURE maxamount(OUT maxamnt DOUBLE)
   BEGIN
     SELECT max(amount) INTO maxamnt FROM expenses;
   END @

It will be created with options DATETIME ISO and ISOLATION UR:

   db2 "CALL SET_ROUTINE_OPTS(GET_ROUTINE_OPTS() || ' ISOLATION UR')"
   db2 -td@ -vf maxamount.db2

The next procedure is defined in CLP script fullamount.db2 as follows:

CREATE PROCEDURE fullamount(OUT fullamnt DOUBLE)
BEGIN
  SELECT sum(amount) INTO fullamnt FROM expenses;
END @

It will be created with option ISOLATION CS (note that it is not using the instance-wide DATETIME ISO setting in this case):

   CALL SET_ROUTINE_OPTS('ISOLATION CS')
   db2 -td@ -vf fullamount.db2

The last procedure in the example is defined in CLP script perday.db2 as follows:

CREATE PROCEDURE perday()
BEGIN
  DECLARE cur1 CURSOR WITH RETURN FOR
    SELECT date, sum(amount)
    FROM expenses
    GROUP BY date;

    OPEN cur1;
END @

The last SET_ROUTINE_OPTS call uses the NULL value as the argument. This restores the global setting specified in the DB2_SQLROUTINE_PREPOPTS registry, so the last procedure will be created with option DATETIME ISO:

   CALL SET_ROUTINE_OPTS(NULL)
   db2 -td@ -vf perday.db2