DB2v11 top Built-in Functions newly added

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:

  1. ARRAY_FIRST
  2. ARRAY_LAST
  3. ARRAY_NEXT
  4. ARRAY_PRIOR
  5. 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.

Join 1,777 other followers

Also Read

Advertisements

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.