Fastest Data Types for SQL Flag Variables

Earlier this year, I revealed that  Db2 for i generates a C program object (or service program) behind the scenes for every SQL routine that gets created for performance reasons.  The SQL routine term here refers to SQL stored procedures, SQL user-defined functions, and SQL triggers.

 

A flag variable is one of the most common constructs found in an SQL routine. These variables are used to control the logic flow of the program based on events that have already happened or have yet to occur as the following code snippet shows. This SQL snippet uses three flag variables - invalidcustID, orders_to_process, log_trace_data - to navigate the logic associated with the orders requested from a specific customer. 

IF invalid_custID = 0 THEN 
  WHILE orders_to_process = TRUE DO 
   /* process next order logic */
    
    IF log_trace_data = 1 THEN
       /* write out trace data */
    END IF;
  END WHILE; 
ELSE 
  /* return  error for invalid customer id */
END IF;

 

You might notice that none of these flag variables are being compared against character strings. That was done on purpose because we’re trying to learn what SQL data types enable a flag variable comparison or assignment to run the fastest.  As the following variable declarations show, the orders_to_process variable is defined with the BOOLEAN type introduced with IBM i 7.5 while the invalid_custID and log_trace_data variables were defined with the SMALLINT and INTEGER types respectively.

 

DECLARE orders_to_process BOOLEAN DEFAULT FALSE NOT NULL; 
DECLARE invalid_custID SMALLINT DEFAULT 0 NOT NULL;
DECLARE log_trace_data INTEGER DEFAULT 0 NOT NULL;

These data types provide the best performance for flag variables because Db2 can always generate and use C code to perform the comparison or assignment when the SQL routine is executed.  The ability to use generated C code is not necessarily true when it comes to flag variables defined with the character types.  It depends on the combination of the source code CCSID, the runtime job CCSID, and if a CCSID value was specified for the variable. These various combinations are documented in the Improving SQL Procedure Performance white paper. 

You’re really rolling the dice in terms of performance when it comes to using the character type for a frequently referenced flag variable.  That’s because if generated C code cannot be used, then Db2 must use a  much more expensive interface to process the character assignment or comparison. This is why the BOOLEAN, SMALLINT, and INTEGER data types are a safer choice when it comes to performance.

 

The other thing to notice with the flag variable declarations is that they all specify a default value along with the NOT NULL cause. If these are not included, then a variable is automatically assumed to be null capable by SQL.  My guess is that most of your SQL routines are not using the NULL value with flag variables – if this is a true, then a small performance penalty is being paid.  That penalty exists because extra C code is generated by Db2 just in case the flag variable is NULL. Take the log_trace_data flag variable usage in the earlier example – if it’s defined as null capable, then the generated C code must first check if the value is NULL before the code can check if the value is 0.  That extra check for NULL can start to add up from a performance perspective – especially given the fact that it’s embedded within a WHILE loop that could have many iterations.

 

If your application is unable to use the newer BOOLEAN data type that leaves the INTEGER and SMALLINT data types as your options for fast flag variables.  If having application code that compares flag variables against 0 and 1 leaves you feeling like you’re coding in assembly language, don’t forget that constants can be used to make the code easier to read.  Here’s an example of defining constants that can be used with your integer-based flag variables.  Db2 for i defines YES and NO as SQL Reserved Words, so it’s a good idea to not use those for the names of your constants.

 

DECLARE YES_VAL SMALLINT CONSTANT 1 ;
DECLARE NO_VAL SMALLINT CONSTANT 0 ;

 

If you want your SQL routines to take the checkered flag in terms of performance and efficiency, the choice of data types for flag variables should now be clear.