Configuring the Build Environment

Because a C/C++ compiler is no longer a requirement for DB2 LUW, no configuration is needed before being able to build SQL procedures. However, you can specify options as to how DB2 builds the SQL procedure. These options can be configured at the instance level by setting the DB2_SQLROUTINE_PREPOPTS DB2 registry variable or they can be configured at the SQL procedure level by calling the SET_ROUTINE_OPTS function.

To set the options at the instance level, use the DB2 registry variable

 db2set DB2_SQLROUTINE_PREOPTS=<options> 

where <options> lists the precompile options to be used. The following are the supported options:

 BLOCKING {UNAMBIG | ALL | NO} DATETIME {DEF | USA | EUR | ISO | JIS | LOC} DEGREE {1 | degree-of-parallelism | ANY} DYNAMICRULES {BIND | RUN} EXPLAIN {NO | YES | ALL} EXPLSNAP {NO | YES | ALL} FEDERATED {NO | YES} INSERT {DEF | BUF} ISOLATION {CS |RR |UR |RS |NC} QUERYOPT optimization-level 

For example, to set the query optimization level to 2 and isolation level to RS for all SQL procedures to be created within the instance, you would issue the following command:

 db2set DB2_SQLROUTINE_PREPOPTS="QUERYOPT 2 ISOLATION RS" db2stop db2start 

Once you change the value of DB2_SQLROUTINE_PREOPTS, you need to restart the instance.

You can also specify the precompile options to use for the next SQL procedure compiled from within your current session. The GET_ROUTINE_OPTS procedure can be used to return the value of DB2_SQLROUTINE_PREOPTS. A SET_ROUTINE_OPTS procedure is used to specify the precompile options for the next compiled SQL procedure.

The following example builds on the previous example. Suppose you want to build an SQL procedure with a query optimization level of 2, an isolation level of read stability, as well as the ISO format for all date data types. First, you must be connected to the database. Then, issue the following statements:

 db2 CONNECT to sample db2 CALL SET_ROUTINE_OPTS(GET_ROUTINE_OPTS() || ' DATETIME ISO') db2 CREATE PROCEDURE NEWPREPOPTS return 1 

To override the value of DB2_SQLROUTINE_PREOPTS and specify particular precompile options, use the SET_ROUTINE_OPTS procedure as demonstrated:

 db2 CALL SET_ROUTINE_OPTS('DATETIME ISO') 

or

 db2 CALL SET_ROUTINE_OPTS('DATETIME ISO ISOLATION UR') 

The SQL procedure created after the previous command will use only the precompile options specified.

Finally, to clear the values set in either SET_ROUTINE_OPTS or DB2_SQLROUTINE_PREOPTS, use the following technique:

 db2 CALL SET_ROUTINE_OPTS(NULL) 

or

 db2set DB2_SQLROUTINE_PREPOPTS="" db2stop db2start