Improvement of database application porting in DB2 V9.5 version

Improved application portability in version 9.5 increases compatibility with existing code and no longer requires migrating applications that use databases from other vendors. Version 9.5 includes the following enhancements:

Supports the use of array data types in applications that process and invoke procedures.

Supports global variables. A global variable is a named memory variable that you can access and modify through an SQL statement. Version 9.5 supports session global variables that have been created that are associated with a specific session and contain values unique to that session.

Supports the SQL dialect of other vendors. Version 9.5 contains support for the DECODE, NVL, least, and greatest functions.

Array data type

In previous versions of DB2, it was cumbersome to have a program transplant that was processed by a data collection, and we could only handle it through a temporary table or cursor.

Version 9.5 supports the ARRAY collection data type. You can handle them in stored procedures and applications, which makes it easier to migrate applications and stored procedures that have supported arrays from other database vendors.

You can use arrays to more efficiently pass data between applications and stored procedures, and to store and process transient data collections in SQL procedures without having to use relational tables. The array operators available in SQL procedures allow for more efficient storage and retrieval of data.

Support for the array data types in version 9.5 allows you to do the following:

Creates a user-defined type based on an array; For example, the Create type INT10 as INTEGER array[10] defines the type of an array with up to 10 integer values;

Declaring the variables and parameters of an array type in stored procedures and applications;

To create and process array values, the basic parts of array processing include array construction method, setting sub subscript, element counting and sorting;

Passing arrays back and forth between JDBC and CLI applications and SQL and Java stored procedures;

Convert an array to a table (one array element per table row) and convert a clustered column to an array to make it easier to connect between arrays and SQL through an interface;

Invokes a procedure from a command-line processor using input and output array parameters.

ARRAY data type definition:

Create TYPE Type-name as Data-type ARRAY [Integer-constant]

Where: Data-type does not support long VARchar, long vargraphic, LOB, XML type, integer-constant maximum value 2147483647

Array-related functions:

Array_agg gathers rows of data into an integrated array

Unnest the array data into rows

Cardinality returns the element number in the array, type BIGINT

Max_cardinality returns the maximum element number contained in the array, type BIGINT

Note that these functions are used only for SQL procedure

select T.PHONE, T.ID, T.INDEX FROM UNNEST(PHONES, IDS)
WITH ORDINALITY AS T(PHONE, ID, INDEX)
ORDER BY T.INDEX
Example 1

This example contains 2 stored procedures, sum and main. The stored procedure main creates an array containing 6 elements (integers). Pass this array to the stored procedure sum, compute all the values of the elements and return it to the calling stored procedure main. Stored procedure sum describes the use of the array subindexing and cardinality functions.

create type intArray as integer array[100] @
create procedure sum(in numList intArray, out total integer)
begin
declare i, n integer;
set n = CARDINALITY(numList);
set i = 1;
set total = 0;
while (i < n) do
set total = total + numList[i];
set i = i + 1;
end while;
end @
create procedure main(out total integer)
begin
declare numList intArray;
set numList = ARRAY[1,2,3,4,5,6];
call sum(numList, total);
end @
Example 2