Partition filtering

Use an SQL WHERE clause to select which rows to unload from a table.

BMC AMI Unload automatically skips partitions that do not satisfy the provided WHERE clause criteria. This reduces the number of partitions that BMC AMI Unload must read. You can use FILTERPART to override the partition filtering. 

Related topics

Selecting-data-to-unload

BMC-AMI-Utilities-SQL-language

IN-predicate

LIKE-predicate

FILTERPART

To be eligible for partition filtering, an unload must satisfy the following criteria:

  • For both the UNLOAD and UNLD command:
    • The unloaded table is a partitioned table space.
    • The unload command has a WHERE clause that has at least one predicate that references the first key column of a limit key.
  • For the UNLD command, the following options are not specified:
    • PART
    • COPYDSN
    • COPYDDN
    • NOFILTER

The BMC AMI Unload output shows the total number of partitions and the partition numbers that satisfy the search criteria.

BMC AMI Unload uses WHERE predicates to determine whether to skip a partition. BMC AMI Unload supports the following three types of predicates for this purpose:

Basic predicate

For BMC AMI Unload to analyze a basic predicate, it must be in one of the following formats:

  • columnName relationalOperator constant
  • constant relationalOperator columnName

The variables are defined as follows:

  • columnName is a key column of the partitioning index.
  • relationalOperator is a valid operator as described in BMC-AMI-Utilities-SQL-language.
    BMC AMI Unload analyzes all operators except ^= or <>.
  • constant is a constant or a special register that is free of any expressions.

IN predicate

For BMC AMI Unload to analyze the IN predicate, it must be in the following format:

columnName IN (constant,constant)

BETWEEN predicate

For BMC AMI Unload to analyze the BETWEEN predicate, it must be in one of the following formats:

  • columnName BETWEEN constant AND constant
  • columnName NOT BETWEEN constant AND constant

BMC AMI Unload also analyzes the use of search condition operators (such as AND and OR) to reduce or expand the partition limit range. Using the AND operator in predicates reduces the number of partitions. Using the OR operator in predicates increases the selected partition range.

If a predicate does not conform to the preceding rules, BMC AMI Unload selects all parts.

BMC AMI Unload logically uses the AND operator with first key column conditions.

The partition selection analysis is exact when the partitioning index has a single-column key. For multiple-column key indexes, the analysis might result in an additional partition per range.

Multi-column keys

In the following examples, the clustering index indexes columns C1 and C2 with the following limitkey values:

  • Part 1 value(1000)
  • Part 2 value(2000)
  • Part 3 value(3000)
  • Part 4 value(4000)
  • Part 5 value(5000)
  • Part 6 value(6000)
  • Part 7 value(7000)
  • Part 8 value(8000)
  • Part 9 value(9000)
Example 1
5 PARTS SELECTED.

The following messages appear:

PARTS 2-6

This means that BMC AMI Unload selects partition 6 (unlike in example 1 in Single column key).

Example 2
UNLOAD FROM T1 WHERE (C1 BETWEEN -100 AND 5000)

The following messages appear:

6 PARTS SELECTED.
PARTS 1-6

This means that BMC AMI Unload reads the first six partitions.

Example 3
UNLOAD FROM T1 WHERE (C1 IN (1,5000,8000,10000))

The following messages appear:

5 PARTS SELECTED.
PARTS 1,5-6,8-9

This means that BMC AMI Unload reads partitions 1, 5, 6, 8, and 9.

Single-column key

In the following examples, the table (T1) is defined in a large partitioned table space with nine partitions.

T1 has two columns, both of TYPE data types C1 and C2. A partitioning index (CX1) indexes C1 in ascending order with the following limitkeys:

  • Part 1 value(1000)
  • Part 2 value(2000)
  • Part 3 value(3000)
  • Part 4 value(4000)
  • Part 5 value(5000)
  • Part 6 value(6000)
  • Part 7 value(7000)
  • Part 8 value(8000)
  • Part 9 value(9000)
Example 1
UNLOAD FROM T1 WHERE (C1 > 1000 AND C1 <= 5000 AND C2+C1 = 6000)

The following messages appear:

4 PARTS SELECTED.
PARTS 2-5

This means that BMC AMI Unload reads partitions 2 through 5 only, and then evaluates the search criteria to select the applicable rows.

Example 2
UNLOAD FROM T1 WHERE (C1 > 1000 AND C1 < 5000 OR C2+C1=6000)

The following message appears:

9 PARTS SELECTED.

This means that BMC AMI Unload selects all partitions because the predicate C1+C2=6000 does not conform to the basic predicate rules.

Example 3
UNLOAD FROM T1 WHERE (C1 > 9000)

The following message appears:

ALL PARTS EXCLUDED.

This means that BMC AMI Unload does not select any partitions because the table space is LARGE and the last partition does not address keys less than 9000. If you attempt to insert into this table a key value greater than 9000, the SQL results in a Db2 error.

Example 4
UNLOAD FROM T1 WHERE (C1 > 1000 AND C1 < 5000 OR C1 >= 8000 AND C1 < 9000)

The following messages appear:

6 PARTS SELECTED.
PARTS 2-5, 8-9

This means that BMC AMI Unload reads partitions 2 through 5 and 8 through 9 only, and then evaluates the search criteria after reading the row data to ascertain the applicable rows.

Example 5
UNLOAD FROM T1 WHERE (C1 BETWEEN -100 AND 5000)

The following messages appear:

5 PARTS SELECTED.
PARTS 1-5

This means that BMC AMI Unload reads the first five partitions.

Example 6
UNLOAD FROM T1 WHERE (C1 NOT BETWEEN 3500 AND 7500)

The following messages appear:

6 PARTS SELECTED.
PARTS 1-4, 8-9

This means that BMC AMI Unload reads partitions 1, 2, 3, 4, 8, and 9.

Example 7
UNLOAD FROM T1 WHERE (C1 IN 1,5000,8000,10000))

The following messages appear:

3 PARTS SELECTED.
PARTS 1,5,8

This means that BMC AMI Unload reads 1, 5, and 8.

 

  • 13.1
Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*