DB2 version 10 enables insert and update of subset of data enhancing reusability of DB2 functions
With the increase in the Web 2.0 applications, there are frequent situations where the application needs to insert or update data only for a subset of columns for a given table. And it becomes all the more difficult when it is not known which columns are being inserted to or updated until application execution time. For example, the customer address change request could mean changing the entire address or just the apartment number or any combination of columns (street name, city, zip code, apartment number and others).
Prior to DB2 version 10, the options for handling different combination of insert and updates included:
- Using a dynamic SQL statement constructed at execution time for each request. Obviously this has overhead and impacts performance.
- Coding SQL statements for all expected combinations of columns – and if we assume that every single combination is possible, the number of SQL statements to be coded grows exponentially. This indeed is a tedious process and has negative impact on application maintenance.
- Have one SQL statement that included all the expected columns. While the customer request comes with data corresponding to only a subset of columns, it becomes the application’s responsibility to figure out the correct values for the other columns – say the old values in case of update, default values in case of insert and NULL values for nullable columns etc. Again, this makes application program complex – and testing difficult.
DB2 10 for z/OS addresses this difficulty of supporting changes to only a subset of columns in a table by introducing the extended indicator variable support. Preparing separate INSERT or UPDATE statements for every combination of columns that are being inserted is no longer required.
Using these extended indicator variables we can indicate to DB2 that the value for the associated host variable is not supplied and also specify how DB2 should handle the missing value. By specifying the extended indicator variable value of -5 (implying default), we can indicate that the target column of the host variable is to be set to its defined DEFAULT value. By specifying the extended indicator variable value of -7 (implying unassigned) we can indicate that the target value of the host variable is UNASSIGNED and the target column is to be treated as if it is not included in the statement.
By enabling the extended indicator variables, you do not need to resubmit the current value for a column, or know the default value for a column. Extended indicator variables can be enabled at a package level, by using the EXTENDEDINDICATOR option on the BIND PACKAGE (or REBIND PACKAGE) command. Extended indicator variables can be enabled on a statement level for dynamic SQL by using the WITH EXTENDED INDICATORS attribute on the PREPARE statement.
When the extended indicator variable is used as an input variable:
|value of -5 (default)||Specifies that the default value is to be used (extended indicator variable enabled)Specifies a null value if the extended indicator variable is not enabled|
|value of -7||Specifies that UNASSIGNED value is to be used (extended indicator variable enabled).Specifies a null value if the extended indicator variable is not enabled|
|value of -1, -2, -3, -4, or -6||Specifies a null value|
|value of 0 or a positive integer||Specifies that the first host identifier provides the value of this host variable reference.|
When the extended indicator variable is used as an output variable, the values have the same meanings as for an indicator variable (negative value indicates NULL and 0 or positive indicates specified value). As this new variable is meant is intended for allowing applications to skip sending data for any of the host variables specified on the statement, in case of output, DB2 does not set indicator variables to any of the special values.
The following restrictions apply in specifying the special extended indicator variable values. It can be specified only for host variables that appear in:
- 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 of the SELECT statement
- The source-table parameter of a MERGE statement
For the INSERT statement, setting the extended indicator variable to -5 or -7 is the same, as the result is to insert a default value for any column that is missing an input value (that is, unassigned). With multi-row insert, the extended indicator variable values of default or unassigned can be used inside the indicator array.
For UPDATE or MERGE UPDATE, setting the extended indicator variable to -5 leads to the column being updated to the default value and setting the extended indicator variable to -7 leads to the update of the column not being applied.
It is important to understand the impact of these extended variables on UPDATE statement quite thoroughly, as it could result in behaviors other than intended by the developer. IBM provides the following example to get through the point.
|memset(&hv_indicators, 0, sizeof(hv_indicators));|
|hv_indicators.hvi_name = -7; /* skip update */|
|hv_indicators.hvi_country = -5; /* use DEFAULT */|
|hv_indicators.hvi_city = -7; /* skip update */|
|hv_indicators.hvi_zip = -7; /* skip update */|
|UPDATE TRYINDVAR SET|
|NAME = :hv_name:hvi_name|
|,COUNTRY = :hv_country:hvi_country|
|,CITY = :hv_city:hvi_city|
|,ZIP = :hv_zip:hvi_zip;|
While bind the DBRM to a package, it is required to the EXTENDEDINDICATOR(YES) option to enable the extended indicator variables.
After executing the above code, if we query the table the result is: (‘Josef’, ‘Earth’, ‘Unknown’, ‘00000′). As the value of -7 is specified in the extended indicator variable, the columns NAME, CITY and ZIP are not at all updated (i.e., the value Michael doesn’t have an impact at all). As the value of -5 is specified in the extended indicator variable, the COUNTRY column was set to the DEFAULT value (‘Earth’), rather than the given value (‘Australia’).
If the extended indicator variable for this package is disabled by rebinding with EXTENDEDINDICATOR(NO) option and tested, the results would be different. Re-running the UPDATE and querying will show that all the columns were set to the value of NULL. The reason being that the disabling of extended indicator variables results in these values being treated as the “indicator variable” and hence the negative value (-5 or -7) implies a NULL value.
As can be seen in the example, the simple enabling or disabling of Extended indicator variable and setting the values of -5 or -7, the application developer can control the actual columns that would be updated – irrespective of where the function is called from and what value it gets.
Thus these extended indicator variable enable applications to use a single SQL statement – for handling different combinations of INSERTS and UPDATES – without burdening the application with the need to fill the missing columns data explicitly with the current value or default value. Thus DB2 functions exploiting these extended indicator variables can be reused across different channels accessing them and form the core services in an SOA environment.