TABLE_PARTITIONS table function

The TABLE_PARTITIONS table function allows to query a ‘virtual’ table containing information for a given iceberg table regarding its partitions and the according files.

Authorization

Any user can execute this function.

Syntax

The schema is SYSHADOOP.

Description

schema_name

An input argument of type VARCHAR(128) containing the name of the schema as stored in the Db2 catalog. This is a required parameter which identifies the schema name.

table_name

An input argument of type VARCHAR(128) containing the name of the table as stored in the Db2 catalog. This is a required parameter which identifies the table name.

Output

Returns a table with one row per table partition and information on the partitioned data and delete files. Each row contains the following columns:

Table 1.
Column Name	Data type	Description
PARTITION	VARCHAR (1024)	String representing the partition id, e.g. {101,Berlin}
SPEC_ID	INT	Version number of the table’s schema spec. (The ID changes when the schema is changed, e.g. a column is added)
DATA_RECORD_COUNT	BIGINT	Number of data records in the partition
DATA_FILE_COUNT	INT	Number of data files in the partition
POS_DELETE_RECORD_COUNT	BIGINT	Number of records in the partition that are marked as ‘deleted’ via positional delete files. Note: This is currently not implemented, therefore the output is always ‘null’.
POS_DELETE_FILE_COUNT	INT	Number of positional delete files for the partition
EQ_DELETE_RECORD_COUNT	BIGINT	Number of records in the partition that are marked as ‘deleted’ via equality delete files. Note: This is currently not implemented, therefore the output is always ‘null’.
EQ_DELETE_FILE_COUNT	INT	Number of equality delete files for the partition.

Examples

SELECT * FROM TABLE (TABLE_PARTITIONS('SAMPLE', 'EMPLOYEE'))

PARTITION                                                                                                                        SPEC_ID     DATA_RECORD_COUNT    DATA_FILE_COUNT POS_DELETE_RECORD_COUNT POS_DELETE_FILE_COUNT EQ_DELETE_RECORD_COUNT EQ_DELETE_FILE_COUNT
-------------------------------------------------------------------------------------------------------------------------------- ----------- -------------------- --------------- ----------------------- --------------------- ---------------------- --------------------
{202}                                                                                                                                      1                    2               2                       -                     0                      -                    0
{}                                                                                                                                         1                    9               1                       -                     0                      -                    0
{100}                                                                                                                                      1                    2               2                       -                     0                      -                    0

  3 record(s) selected.