Conditional compilation in SQL

Conditional compilation allows SQL to include compiler directives which are used to determine the actual SQL that gets compiled.

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.

Selection directive

The selection directive is very similar to the IF statement except there are prefixes on the keywords to indicate use of conditional compilation and the terminating keyword is _END.

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

Inquiry directive

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

The only compilation environment variable defined by the system is __SQL_LINE, which provides the line number of SQL that is currently being compiled.

A user-defined compilation value can be defined at the database level using the sql_ccflags database configuration parameter or at a session level by assigning it to the CURRENT SQL_CCFLAGS special register.

If an inquiry directive is referenced but is not defined, processing continues assuming that the value for the inquiry directive is the null value.

Notes

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.

Example

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.