Conditional compilation in SQL

There are two types of compiler directives that can be used for conditional compilation:

Selection directive

A compiler control statement used to determine the selection of a code fragment. The _IF directive can reference inquiry directives or global variables that are defined as a constant.

Inquiry directive

A reference to a compiler named constant that is assigned by the system or specified as a conditional compilation named constant in CURRENT SQL_CCFLAGS. An inquiry directive can be used directly or in a selection directive.

These directives can be used in the following contexts:

• SQL procedure definitions
• Compiled SQL function definitions
• Compiled trigger definitions
• Oracle PL/SQL package definitions

A directive can only appear after the object type (FUNCTION, PACKAGE, PACKAGE BODY, PROCEDURE, or TRIGGER) has been identified in the data definition language statement.

>>-_IF--search-condition--_THEN--code-fragment------------------>

   .-----------------------------------------------------.   
   V                                                     |   
>----+-------------------------------------------------+-+------>
     '-_ELSEIF--search-condition--_THEN--code-fragment-'     

>--+----------------------+--_END------------------------------><
   '-_ELSE--code-fragment-'         

search-condition

Specifies the condition that is evaluated to determine what code-fragment, if any, is included. If the condition is unknown or false, evaluation continues with the next search condition, until a condition is true, the _ELSE clause is reached, or the end of the selection directive is reached. The search condition can include only the following elements (SQLSTATE 428HV):

• Constants of type BOOLEAN, INTEGER, or VARCHAR
• NULL constants
• Inquiry directives
• Global constants, where the defined constant value is a simple literal of type BOOLEAN, INTEGER, or VARCHAR
• Basic predicates
• NULL predicates
• Predicates that are a Boolean constant or a Boolean inquiry directive
• Logical operators (AND, OR, and NOT)

code-fragment

A portion of SQL code that can be included in the context of the SQL statement where the selection directive appears. There must not be a selection directive in code-fragment (SQLSTATE 428HV).

An inquiry directive is used to inquire about the compilation environment. An inquiry directive is specified in an SQL statement as an ordinary identifier prefixed with two underscore characters. The actual identifier can represent one of the following values:

• A compilation environment value defined by the system
• A compilation value defined by a user at the database level or at the individual session level

• References to global variables defined as constants: A reference to a global variable (which can also be a reference to a module variable published in a module) in a selection directive is used to provide a value based on a constant at the time of compilation only. The referenced global variable must meet the following requirements:
  • Exist at the current server (SQLSTATE 42704)
  • Have a data type of BOOLEAN, INTEGER, or VARCHAR (SQLSTATE 428HV)
  • Be defined using the CONSTANT clause with a single constant value (SQLSTATE 428HV)
  Such a global variable is known as a global constant. Subsequent changes to the global constant do not have any impact on statements that are already compiled.
• Syntax alternatives: If the data server environment is enabled for PL/SQL statement execution:
  • ELSIF can be specified instead of ELSEIF
  • A dollar character ($) can be used instead of an underscore character (_) as the prefix for the keywords for conditional compilation
  • Two dollar characters ($$) can be used instead of two underscore characters (__) as the prefix for an inquiry directive
  The dollar character prefix is intended only to support existing SQL statements that use inquiry directives and is not recommended for use when writing new SQL statements.

Specify a database-wide setting for a compilation value called DBV97 that has a value of TRUE.

   UPDATE DATABASE CONFIGURATION USING SQL_CCFLAGS DB2V97:TRUE

The value is available as the default for any subsequent connection to the database.

If a particular session needed a maximum number of years compilation value for use in defining some routines in the current session, the default SQL_CCFLAGS can be extended using the SET CURRENT SQL_CCFLAGS statement.

   BEGIN
     DECLARE CCFLAGS_LIST VARCHAR(1024);
     SET CCFLAGS_LIST = CURRENT SQL_CCFLAGS CONCAT ',max_years:50';
     SET CURRENT SQL_CCFLAGS = CCFLAGS_LIST;
   END

The use of CURRENT SQL_CCFLAGS on the right side of the assignment to the CCFLAGS_LIST variable keeps the existing SQL_CCFLAGS settings, while the string constant provides the additional compilation values.

Here is an example of a CREATE PROCEDURE statement that uses the contents of the CURRENT SQL_CCFLAGS.

   CREATE PROCEDURE CHECK_YEARS (IN YEARS_WORKED INTEGER)
   BEGIN
      _IF __DB2V97 _THEN
         IF YEARS_WORKED > __MAX_YEARS THEN
         ...
         END IF;
      _END

The inquiry directive __DB2V97 is used as a Boolean value to determine if the code can be included. The inquiry directive __MAX_YEARS is replaced during compilation by the constant value 50.