4.3.1 Specification format and rules for the PURGE CHUNK statement

The PURGE CHUNK statement deletes all of the rows in a chunk.

The PURGE CHUNK statement can be run for only multi-chunk tables.

Organization of this subsection

(1) Specification format
(2) Explanation of specification format
(3) Privileges required at execution
(4) Rules
(5) Examples

(1) Specification format

PURGE CHUNK-statement ::= PURGE CHUNK table-name [[AS] correlation-name]
                       WHERE search-condition

To Page Top

(2) Explanation of specification format

table-name

Specifies the name of the multi-chunk table to be processed (the chunk deletion target table). For rules on specifying a table name, see (2) Table name specification format in 6.1.5 Qualifying a name.

Note that you cannot specify a viewed table.

[AS] correlation-name

Specifies the correlation name of the chunk deletion target table. For details about correlation names, see (4) Table specification format in 6.1.5 Qualifying a name.

WHERE search-condition

Specifies the ID of the chunk to be processed.

Specify search conditions in which CHUNKID is specified. For details about search conditions, see 7.18 Search conditions.

You must specify either a comparison predicate, IN predicate, or quantified predicate in search-condition.

comparison predicate:

For details about comparison predicates, see 7.19.7 Comparison predicate.

The following rules apply to the PURGE CHUNK statement specifically:

The only comparison operator that can be specified is =.
CHUNKID must be specified for either comparison operand 1 or comparison operand 2.
The comparison operand to be compared to CHUNKID must be either an integer literal, dynamic parameter, or scalar subquery. For details about scalar subqueries, see 7.3.1 Specification format and rules for subqueries.
If you specify a dynamic parameter for a comparison operand, the assumed data type of the dynamic parameter is INTEGER type.

The following are examples of the specification format:

WHERE CHUNKID=integer-literal
WHERE CHUNKID=?
WHERE CHUNKID=(scalar-subquery)

Important

When using a comparison predicate to specify the chunk ID, only one chunk ID can be specified. Therefore, to delete rows from multiple chunks using a comparison predicate, you must execute multiple PURGE CHUNK statements.

However, when using an IN predicate or quantified predicate, you can delete rows from multiple chunks that match the conditions in a single PURGE CHUNK statement.

IN predicate:

For details about IN predicates, see 7.19.3 IN predicate.

The following rules apply to the PURGE CHUNK statement specifically:

The IN predicate must use a table subquery.
CHUNKID must be the first value expression in the IN predicate.
The IN predicate must not use NOT.

The following is an example of the specification format:

WHERE CHUNKID IN (table-subquery)

quantified predicate:

For details about quantified predicates, see 7.19.8 Quantified predicate.

The following rules apply to the PURGE CHUNK statement specifically:

The only comparison operator that can be specified is =.
Only ANY or SOME can be specified. ALL cannot be specified.
You must specify CHUNKID as the value expression inside the quantified predicate.

The following are examples of the specification format:

WHERE CHUNKID=ANY(table-subquery)
WHERE CHUNKID=SOME(table-subquery)

To Page Top

(3) Privileges required at execution

To execute the PURGE CHUNK statement, both of the following privileges are required:

The CONNECT privilege
The TRUNCATE privilege on the chunk deletion target table

If a subquery is specified, the SELECT privilege is required on all of the tables specified in the FROM clause.

To Page Top

(4) Rules

Make sure the result of the search condition will have a data type of INTEGER or SMALLINT.
Logical operations (AND, OR, NOT) cannot be specified on the predicate.

Examples that produce an error:
```
WHERE CHUNKID=1 OR CHUNKID=5
WHERE NOT(CHUNKID=1)
```
CHUNKID cannot be specified in a subquery.

If you specify a subquery, the selection expression to be compared to CHUNKID must have a data type of INTEGER or SMALLINT.

Example:

PURGE CHUNK "SALESLIST"
        WHERE CHUNKID=(
                       SELECT "CHUNK_ID"
                         FROM "MASTER"."STATUS_CHUNKS"
                           WHERE "TABLE_SCHEMA" = 'ADBUSER01'
                             AND "TABLE_NAME" = 'SALESLIST'
                             AND "CHUNK_COMMENT" = '2015/01/24 additional data')

The underlined portion is the selection expression to be compared to CHUNKID.

You cannot specify a column from the chunk deletion target table in the search condition.
You cannot specify the chunk deletion target table in the FROM clause of a subquery specified in the search condition.
If the chunk ID of the current chunk is specified, the PURGE CHUNK statement will result in an error.
If a non-existent chunk ID is specified, the specified chunk ID will be ignored and processing will continue.
The total number of tables, derived tables, and table function derived tables specified in the PURGE CHUNK statement cannot exceed 2,048. For rules and examples about how to count the number of tables, derived tables, and table function derived tables specified in an SQL statement, see (4) Rules in 4.4.1 Specification format and rules for the SELECT statement.
If all of the set operations specified in the PURGE CHUNK statement are UNION, a maximum of 1,023 set operations can be specified. However, if the specified set operations include EXCEPT or INTERSECT, no more than 63 set operations can be specified.
A maximum of 63 outer joins (FULL OUTER JOIN) can be specified in the PURGE CHUNK statement.
During the execution of the PURGE CHUNK statement, the DB area is locked in exclusive mode. Therefore, you cannot execute the PURGE CHUNK statement while performing operations on another table or index stored in the DB area that holds the chunk deletion target table, or the index of the chunk deletion target table.
If the PURGE CHUNK statement terminates successfully, a COMMIT statement is automatically executed before the transaction terminates. Therefore, there is no need to execute a COMMIT statement after the execution of the PURGE CHUNK statement.
If the PURGE CHUNK statement terminates successfully, the chunk to be processed (the chunk specified in the chunk ID) is deleted.
If both of the following conditions are met, the PURGE CHUNK statement will result in an error.
- There is a cursor performing retrieval of the chunk deletion target table in the connection where the PURGE CHUNK statement is executing
- The cursor is open
If the PURGE CHUNK statement is executed using the JDBC driver, and there is a Statement object or a PreparedStatement object performing retrieval from the chunk deletion target table within the same connection, the PURGE CHUNK statement will result in an error.

To Page Top

(5) Examples

Example 1

In the sales history table (SALESLIST), delete all the rows in the chunk whose ID is 1.

PURGE CHUNK "SALESLIST" WHERE CHUNKID=1

The above example uses an integer literal to specify the ID of the chunk to be deleted.

Example 2

In the sales history table (SALESLIST), delete all the rows in the chunk whose ID is specified in the dynamic parameter.

PURGE CHUNK "SALESLIST" WHERE CHUNKID=?

The above example uses a dynamic parameter to specify the ID of the chunk to be deleted.

Example 3

In the sales history table (SALESLIST), delete all the rows in the chunk meeting the following condition:

The chunk's comment is set to 2015/01/24 additional data

PURGE CHUNK "SALESLIST"
        WHERE CHUNKID=(
                       SELECT "CHUNK_ID"
                         FROM "MASTER"."STATUS_CHUNKS"
                           WHERE "TABLE_SCHEMA" = 'ADBUSER01'
                             AND "TABLE_NAME" = 'SALESLIST'
                             AND "CHUNK_COMMENT" = '2015/01/24 additional data')

The above example uses a scalar subquery to specify the ID of the chunk to be deleted.

Important: The above PURGE CHUNK statement is executed under the assumption that there is only one chunk whose comment is set to 2015/01/24 additional data. If there are multiple such chunks, use an IN predicate or a quantified predicate.

Example 4

In the sales history table (SALESLIST), delete all the rows in the chunks meeting the following condition:

The chunk's comment is set to 2015XXXX additional data

where XXXX denotes a month and day.

PURGE CHUNK "SALESLIST"
        WHERE CHUNKID IN (
                          SELECT "CHUNK_ID"
                            FROM "MASTER"."STATUS_CHUNKS"
                              WHERE "TABLE_SCHEMA" = 'ADBUSER01'
                                AND "TABLE_NAME" = 'SALESLIST'
                                AND "CHUNK_COMMENT" LIKE '2015% additional data')

The above example uses an IN predicate to specify the IDs of the chunks to be deleted.

Example 5

In the sales history table (SALESLIST), delete all the rows in chunks that are in wait status.

PURGE CHUNK "SALESLIST"
        WHERE CHUNKID=ANY(
                          SELECT "CHUNK_ID"
                            FROM "MASTER"."STATUS_CHUNKS"
                              WHERE "TABLE_SCHEMA" = 'ADBUSER01'
                                AND "TABLE_NAME" = 'SALESLIST'
                                AND "CHUNK_STATUS" = 'Wait')

The above example uses a quantified predicate to specify the IDs of the chunks to be deleted.

To Page Top