All About DB2 Backward Index Scan

The reverse scan in DB2 also called backward index scan.

Reverse Scan

The ordering of an index has implications for how DB2 will elect to use that index when satisfying a query. When you create an index, you either choose the default ascending ordering, or explicitly choose ascending or descending ordering to suit your needs.
Another default behavior kicks in at the same time with DB2 9.5, where DB2 will permit itself to traverse your index in reverse order to help it with queries.
If you’re still on DB2 9, the database is a little retentive and won’t allow reverse scans by default. This default had its foundation in the overhead in storage and computation to allow walking an index in both directions.

How to Override

You can override this aversion to reverse scans prior to version 9.5 by adding the allow reverse scans option to the index definition: create index nom_empno on nomination (empno asc) allow reverse scans

This still creates the index in the order indicated (in this case, ascending), but adds additional pointer information to the structure to allow the index to be traversed in the opposite (descending) order.

This additional metadata is very small, and the benefit of reverse scans far outweighs any impost of additional storage. You can also explicitly disallow such reverse scans by using the disallow reverse scans option.