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 [1]
Array 2 [2]
Array 3 [3]
And so on…
In DB2 the Array index starts with 1, and then 2 like so on.
my_array[10] = [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.
CARDINALITY Function
CARDINALITY(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:
- ARRAY_FIRST
- ARRAY_LAST
- ARRAY_NEXT
- ARRAY_PRIOR
- ARRAY_DELETE
MAX_CARDINALITY Function
The MAX_CARDINILITY function tells the size of an array. So, it gives capacity of an array about how many elements it can hold.
Related Posts
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
ARRAY_FIRST
It gives first ‘index’ element value.
ARRAY_LAST
It gives last ‘index’ element vale
ARRAY_NEXT
It gives next ‘index’ element value.
ARRAY_PRIOR
It gives prior ‘index’ element value.
TRIM_ARRAY
TRIM_ARRAY function, trims elements in the end of an array.
ARRAY_DELETE
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[100];
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.
Also Read
- How to Delete a Row: Sub-query Best Example
- 8 Top SQL Queries Where it Shows the Usage of String Functions
- DB2 SQL Query Performance Tuning (1 of 3)
Srinimf 