As you all should know by now, version 10 of DB2 for z/OS greatly expands support for indicator variables. Those of you who write programs that must handle results that could be null should know what an indicator variable is. Basically, DB2 represents null in a special variable known as an indicator. An indicator is defined to DB2 for each column that can accept nulls. The indicator variable is transparent to the end user, but must be provided for when programming in a host language (such as COBOL or PL/I). If the indicator variable is less than zero, then the column to which it applies has returned NULL.

This traditional indicator variable is set when data is output to the program. DB2 10 for z/OS enhanced and extended the concept of an indicator variable so they can be used on input to DB2.

Consider the scenario where a program is being written to modify data. There are multiple combinations of columns that may need to be modified based on conditional programmatic processing. Maybe the program is for editing customer data. The customer has multiple columns that could be modified: name, address, telephone number, credit rating, etc. So the programmer codes up an online screen (e.g. CICS) with all of the data that can then be changed by the end user.

But what happens when the end user cracks the <ENTER> key to submit the changes? What actually changed and what stayed the same? Does the program check every value on the screen (perhaps hundreds) and build every UPDATE statement iteration for data that might have been changed? That approach is unlikely, since it would require x! statements (where x is the total number of columns). The ! symbol stands for factorial. For non-mathematicians a discussion of factorial can be found here (http://en.wikipedia.org/wiki/Factorial).

Yes, there are CICS options to help the programmer determine which values have changed (or simply save and compare). But CICS is not the only option for online applications these days. And until recently, dealing with all the potential SQL statements could be problematic.

Now you can use indicator variables to inform DB2 whether the value for an associated host variable has been supplied or not… and to specify how DB2 should handle the missing value.

This is referred to as an extended indicator variable. And it can be applied to host variables and parameter markers.

Whether you will use extended indicator variables can be enabled at the package level, by using the EXTENDEDINDICATOR option of the BIND PACKAGE command. You can also enable extended indicator variables on a statement level for dynamic SQL by using the WITH EXTENDED INDICATORS attribute on the PREPARE statement. You can use indicator variable arrays and indicator structures to perform these same actions for individual items in host data arrays and structures.

How does this all work? Well, extended indicator variables can be specified only for host variables that appear in the following situations:

  • The set assignment list of an UPDATE operation in UPDATE or MERGE statements
  • The values list of an INSERT operation in INSERT or MERGE statements
  • The select list of an INSERT statement in the FROM clause

OK, then, let’s take a look at how we can use an extended indicator variable by setting its value to tell DB2 how to proceed. The following values are available:

0 (zero) or a positive integer: This indicates the first host identifier provides the value of this host variable reference and it is not null.

 

-1, -2, -3, -4, or -6: This indicates a null.

 

-5: If extended indicator variables are not enabled, this indicates a null; otherwise, a value of -5 indicates that the DEFAULT value is to be used for the target column for this host variable.

 

-7: If extended indicator variables are not enabled, this indicates a null; otherwise, a value of -7 indicates that the UNASSIGNED value is to be used for the target column for this host variable (in other words, treat it as if it were not specified in this statement).

For an INSERT, -5 and -7 settings for an extended indicator variable will end up with the same result. This is so because the INSERT statement works by inserting a default value for any column that is missing. On the other hand, for UPDATE and the UPDATE portion of a MERGE, setting the extended indicator variable to -5 leads to the column being update to the default value, but -7 leads to the update of the column not being applied.

With extended indicator variables then, there is no need for the application to re-send a column’s current value, or to know a column’s DEFAULT value. Which should make things easier for developers.