SET CURRENT SQL_CCFLAGS statement

The SET CURRENT SQL_CCFLAGS statement changes the value of the CURRENT SQL_CCFLAGS special register.

Invocation

This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared.

None required.

variable

Specifies a variable that contains one or more name and value pairs that are separated by commas.

The variable must have the following characteristics (SQLSTATE 42815):

The data type must be CHAR or VARCHAR. The actual length of the contents of the variable must not exceed the maximum length of the special register.
It must be a string of blanks, an empty string, or include one or more name and value pairs where the name is separated from the value by the colon character. The name must be a valid ordinary identifier. The value associated with a name must be a BOOLEAN constant, an INTEGER constant, or the keyword NULL.
It must be padded on the right with blanks if using a fixed-length character variable.
It can include extra blanks at the beginning or ending of the string, around the comma character, or around the colon character. The blanks are ignored.
It must not be the null value.

string-constant

Specifies a character string constant that contains one or more name and value pairs that are separated by commas.

The string constant must have the following characteristics (SQLSTATE 42815):

It must be a character string constant. The length of the constant must not exceed the maximum length of the special register.
It must be a string of blanks, an empty string or include one or more name and value pairs where the name is separated from the value by the colon character. The name must be a valid ordinary identifier. The value associated with a name must be a BOOLEAN constant, an INTEGER constant, or the keyword NULL.
It can include extra blanks at the beginning or ending of the string, around the comma character, or around the colon character. The blanks are ignored.

If a duplicate name appears in the content for the CURRENT SQL_FLAGS special register, then only the last (furthest to the right) value is used. The special register value will include only a single occurrence of the duplicated name with the value that is used. Concatenating a duplicated name with a different value to the CURRENT SQL_CCFLAGS value can be used to override some conditional compilation values while retaining other values.
When the CURRENT SQL_CCFLAGS is retrieved, the returned string includes the unique name and value pairs in uppercase characters with multiple pairs separated by a comma and a blank. The pairs are in the order they were specified, with a duplicate name appearing only where it first occurred, but reflecting the value from where it last occurred.
The CURRENT SQL_CCFLAGS special register can be set to the default defined for the database by retrieving the VALUE column from SYSIBMADM.DBCFG where NAME='sql_ccflags' into a variable and then assigning that variable to the special register.
Transaction considerations: The SET SQL_CCFLAGS statement is not a committable operation. ROLLBACK has no effect on CURRENT SQL_CCFLAGS.

Example 1: Define a conditional compilation value for the session to indicate that the server is Db2® 9.7 and that debug is false.
```
  SET CURRENT SQL_CCFLAGS 'db2v97:true, debug:false'
```
Example 2: Extend the existing CURRENT SQL_CCFLAGS to set debug to true and define the tracing level.
```
  BEGIN
    DECLARE LIST VARCHAR(1024);
    SET LIST = CASE WHEN (CURRENT SQL_CCFLAGS = ' ')
                THEN 'tracelvl:3,debug:true'
                ELSE CURRENT SQL_CCFLAGS
                     concat ',tracelvl:3,debug:true'
                END;
    SET CURRENT SQL_CCFLAGS = LIST;
  END
```
A CASE expression is used in the assignment to handle the possibility that the CURRENT SQL_CCFLAGS special register does not include any conditional compilation values, resulting in a leading comma in the value of the variable LIST.
A query of the CURRENT SQL_CCFLAGS special register after the execution of the statement in Example 1 and the compound statement in this example would return:
```
DB2V97:TRUE, DEBUG:TRUE, TRACELVL:3
```
Even though the conditional compilation value for DEBUG appeared twice in the variable LIST, it appears only once in the special register value where it would have first appeared.