The main consideration is whether DB2 will even use such an index. DB2 will use an index if it can reduce the number of pages (typically 4K in size) that need to be accessed.
If the values for ORDER_ST are randomly distributed throughout the table, and the row size is 400 bytes or less, then on average there is at least one qualifying row on each 4K page (since you said pending and in progress orders are 10% of the total). DB2 will not likely use an index on ORDER_ST in this situation. This scenario is more likely if the table is clustered by customer number, or some other similar column.
However, if the pending and in progress orders tend to be physically at the end of the table (cluster ratio of index on ORDER_ST is relatively high), then DB2 would be more inclined to use the index since it can reduce the number of 4K pages accessed. If the table was physical clustered by an index on ORDER, and ORDER was based on a next available number (or append was used to insert new rows), the cluster ratio for ORDER_ST might be coincidently high even though it is not the clustering index because more recent orders tend to be the ones not shipped yet. You can check the cluster ratio of the index in the catalog after running runstats.
For queries, I would probably try ORDER_ST < 3 as the predicate instead of ORDER_ST in (1,2).
Make sure you capture detail runstats on column distribution (otherwise DB2 will assume each of the three values occupies 33.3% of the table). For example:
RUNSTATS INDEX (index-qualifier.index-name)
FREQVAL NUMCOLS 1 COUNT 3
Using the explain will tell you whether the index on ORDER_ST will be used for the SQL statements in your application. But if in spite of all your attempts to make adjustments, the index is never used, you would be better off just dropping the index.
M. A. Feldman
IBM Certified DBA on DB2 for Linux, UNIX, and Windows
IBM Certified DBA on DB2 for z/OS and OS/390