REWRITE_TABLE_FILES stored procedure

The REWRITE_TABLE_FILES stored procedure is used to execute a rewrite operation on the data and delete files of an Iceberg table.

It breaks the files down into so-called file groups, based on table partitioning and within partitions based on size. Whether the files in a file group are rewritten or not, is determined by a number of aspects, of which some can be set via the options parameter. If a file group contains at least one delete file, the files of this groups will always be rewritten.

Authorization

EXECUTE is granted to the DASHDB_ENTERPRISE_ADMIN role.

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 references the table which will be impacted by the procedure.

partition_filter

An optional argument of type VARCHAR (32672) containing the predicate condition that can be used to filter the data being processed. Only columns included in the partitioning key are used in the partition_filter. With this option, only the file groups of the matching partitions are processed. The default value is NULL.

order_by

An optional argument of type VARCHAR(32672) which can be used to optimize data filtering by sorting the data by the columns that are used most in predicates. This argument applies only to data file compaction. The default value is NULL.

options

An optional input parameter of type VARCHAR(32672) which allows for the specification additional options for the operation. Valid options are:

min-input-files(Default is ‘5’)

This parameter specifies the minimum size (i.e. number of files) of a file group in order to qualify for a rewrite. In that case, the files of the group will be rewritten.
rewrite-all (Default is ‘false’)

When this option is set to true, all files will qualify for a rewrite, no matter how many files are found, which size they have, and if a delete file is among them or not.
degree-of-parallelism (Default is ‘4’)

This parameter specifies the number of processes that are run in parallel to execute the file rewrite task.
num-partitions-per-commit

If this parameter is specified, the rewrite tasks will be divided into several commits, with each commit not touching more that the specified number of partitions. This can the be used to reduce the likelihood of commit conflicts.
rewrite-delete-files-only (Default is 'false')

When this option is set to true, only the delete files will be rewritten. Read performance rewrites many small delete files into bigger delete files. Rewriting delete files is quicker than rewriting data files.

Usage Notes

Not all expressions supported by a Db2 WHERE clause are supported in the partition_filter parameter. For example, the operator IN is not supported by the partition_filter parameter and should be replaced by an ORed list of equal operators. The following is an example of a partition_filter parameter which can be used to filter the data for a table that has partitioning columns defined as CHAR and VARCHAR:

partition_filter => 'workdept is not null and (not(workdept = ''A00'' or workdept = ''D11''))'

For more detailed information about the effect of the rewrite operation, users are encouraged to run compaction reports to compare the table fragmentation state before and after the rewrite.

Output

Two result sets:

The first result set contains two columns named STATUS_CODE and STATUS_MESSAGE. These columns contain values indicating the success or failure of the procedure. If the procedure succeeds, the STATUS_CODE is set to 0 and the STATUS_MESSAGE includes information related to the procedure results. If the procedure failed, the STATUS_CODE is set to -1 and the STATUS_MESSAGE includes an explanation indicating why it failed.

A second result containing a single row is returned upon successful completion on the procedure. This row includes columns that detail the number of files added, deleted, and rewritten, as well as the number of records written by the procedure. The columns returned are: Number of deleted delete files.

Table 1.
Column Name	Data Type	Description
ADDED_DATA_FILES_COUNT	BIGINT	Number of added data files.
REWRITTEN_DATA_FILES_COUNT	BIGINT	Number of rewritten data files.
DELETED_DELETE_FILES_COUNT	BIGINT	Number of deleted delete files.
WRITTEN_DATA_RECORDS_COUNT	BIGINT	Number of data records written.

Examples

Example 1

This example shows a simple invocation of REWRITE_TABLE_FILES.

CALL REWRITE_TABLE_FILES('SAMPLE', 'EMPLOYEE')


  Result set 1
  --------------

  STATUS_CODE STATUS_MESSAGE                       
  ----------- -------------------------------------
            0 File rewrite finished for SAMPLE.EMPLOYEE

  1 record(s) selected.


  Result set 2
  --------------

  ADDED_DATA_FILES_COUNT REWRITTEN_DATA_FILES_COUNT DELETED_DELETE_FILES_COUNT WRITTEN_DATA_RECORDS_COUNT
  ---------------------- -------------------------- -------------------------- --------------------------
                      21                         32                          6                        207

  1 record(s) selected.

  Return Status = 0

Example 2

This example shows an invocation of REWRITE_TABLE_FILES using Db2's named parameters. This invocation will rewrite the table files for the EMPLOYEE table as per the parameters specified:

call REWRITE_TABLE_FILES('SAMPLE', 'EMPLOYEE', PARTITION_FILTER=>'workdept <> ''A00''', OPTIONS=>'num-partitions-per-commit=2', ORDER_BY=>'"LASTNAME" asc, edlevel desc')


  Result set 1
  --------------

  STATUS_CODE STATUS_MESSAGE                           
  ----------- -----------------------------------------
            0 File rewrite finished for SAMPLE.EMPLOYEE

  1 record(s) selected.


  Result set 2
  --------------

  ADDED_DATA_FILES_COUNT REWRITTEN_DATA_FILES_COUNT ADDED_DELETE_FILES_COUNT DELETED_DELETE_FILES_COUNT WRITTEN_DATA_RECORDS_COUNT WRITTEN_DELETE_RECORDS_COUNT
  ---------------------- -------------------------- ------------------------ -------------------------- -------------------------- ----------------------------
                       1                          2                        0                          1                          7                            0

  1 record(s) selected.

  Return Status = 0

Example 3

The following are examples of partition_filter.

partition_filter with character partition columns:

partition_filter => 'partcol_char is null or (not(partcol_char = ''a'' and partcol_varchar <> ''val2''))'

partition_filter with numeric partition columns:

partition_filter => 'partcol_decimal=89.012 or partcol_double<3.4E+0 or partcol_float>=+5.6 or partcol_integer==07'

partition_filter with date/time partition columns

partition_filter => '(partcol_date==''2025-04-02'' or partcol_time==''10:49:04.21'') or partcol_timestamp=''2025-04-02 10:49:04'''

partition_filter with case-sensitive partition column name and WHERE keyword [ ]
```
partition_filter => 'where "partcol_Boolean"=true'
```

Example 4

This following is an example of order_by:

order_by => '"COL1" asc, col2 desc'

Example 5

The following example shows how to call the stored procedure with named parameters:

call REWRITE_TABLE_FILES('SCHEMA', 'TABLE',
     PARTITION_FILTER=>'tabschema <> ''SYSHADOOP''',
     OPTIONS=>'num-partitions-per-commit=2',
     ORDER_BY=>'"TABNAME" asc, colname desc');