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.
Authorization
None required.
Syntax
.-=-.
>>-SET--CURRENT SQL_CCFLAGS--+---+--+-variable--------+--------><
'-string-constant-'
Description
- 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.
Notes
- 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.
Examples
- 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.