The CARDINALITY function returns a value of type BIGINT representing the number of elements of an array.

The schema is SYSIBM.

array-expression

An SQL variable, SQL parameter, or global variable of an array type, or a CAST specification of a parameter marker to an array type.

Result

For an:

• Ordinary array, the returned value is the highest array index for which the array has an assigned element.
  This includes elements that have been assigned the null value.
• Associative array, the returned value is the actual number of unique array index values defined in the array expression.

The function returns 0 if the array is empty.
The result can be null; if the argument is null, the result is the null value.

Examples

1. Return the number of calls that have been stored in the recent calls list so far:

      SET HOWMANYCALLS = CARDINALITY(RECENT_CALLS)

   The SQL variable HOWMANYCALLS contains the value 3.


2. Assume that the associative array variable CAPITALS of array type CAPITALSARRAY contains all of the capitals for the 10 provinces and 3 territories in Canada as well as the capital of the country, Ottawa.
   Return the cardinality of the array variable:

      SET NUMCAPITALS = CARDINALITY(CAPITALS)

   The SQL variable NUMCAPITALS contains the value 14.