Built-in functions are those that come with DB2 of any version. Array functions are top functions added newly in DB2 v11.
How an Array Can be Defined
Array 1 
Array 2 
Array 3 
And so on…
In DB2 the Array index starts with 1, and then 2 like so on.
my_array = [1,2,3,4,5,6,7]
The size of the array you can say as ’10’. Since the number of elements it can hold maximum as ’10’. Currently there are ‘7’ elements present in my_array.
The above function returns single value. Which is ‘7’, why means ‘the cardinality function’ finds the number of elements in an array.
CARDINALITY is a scalar function and it is not aggregate function. You can read my previous post differences between Aggregate vs Scalar functions.
Around an array there are many new functions added. Those are:
The MAX_CARDINILITY function tells the size of an array. So, it gives capacity of an array about how many elements it can hold.
How an Array Filled With Data
By default all the array elements filled with NULL values. Suppose your array size is 100. Then, 1 to 100 will initialize with NULL values.
Details of an Array Functions
It gives first ‘index’ element value.
It gives last ‘index’ element vale
It gives next ‘index’ element value.
It gives prior ‘index’ element value.
TRIM_ARRAY function, trims elements in the end of an array.
This function deletes elements from an array.
ARRAY Data Type
Array data type is not supported on Table column, Views and Triggers. You have to use CREATE TYPE statement to create an array data type.
CREATE TYPE my_array AS INTEGER ARRAY;
The above array you can say as Ordinary array. The maximum size of ‘my_array’ is ‘100’.
The newly added scalar functions in DB 211 are related to array data type.
- String Functions Useful for smart SQL Queries
- The Order of SQL Query Execution Read Today
- Sample SQL ALTER queries useful for your project