| Predicate Type | Indexable | Stage 1 |
| COL = value | Y | Y |
| COL = noncol expr | Y | Y |
| COL IS NULL | Y | Y |
| COL op value | Y | Y |
| COL op noncol expr | Y | Y |
| COL BETWEEN value1 AND value2 | Y | Y |
| COL BETWEEN noncol expr1 AND noncol expr2 | Y | Y |
| value BETWEEN COL1 AND COL2 | Y | Y |
| COL BETWEEN COL1 AND COL2 | N | N |
| COL BETWEEN expression1 AND expression2 | Y | Y |
| COL LIKE ‘pattern’ | Y | Y |
| COL IN (list) | Y | Y |
| COL <> value | N | Y |
| COL <> noncol expr | N | Y |
| COL IS NOT NULL | Y | Y |
| COL NOT BETWEEN value1 AND value2 | N | Y |
| COL NOT BETWEEN noncol expr1 AND noncol expr2 | N | Y |
| value NOT BETWEEN COL1 AND COL2 | N | N |
| COL NOT IN(list) | N | Y |
| COL NOT LIKE ‘char’ | N | Y |
| COL LIKE ‘%char’ | N | Y |
| COL LIKE ‘_char’ | N | Y |
| COL LIKE host variable | Y | Y |
| COL LIKE UPPER(‘pattern’) | Y | Y |
| COL LIKE UPPER(host-variable) | Y | Y |
| COL LIKE UPPER(global-variable) | Y | Y |
| COL LIKE UPPER(CAST(host-variable AS data-type) | Y | Y |
| COL LIKE UPPER(CAST(SQL-variable AS data-type) | Y | Y |
| COL LIKE UPPER(CAST(global-variable AS data-type) | Y | Y |
| T1.COL = T2 col expr | Y | Y |
| T1.COL op T2 col expr | Y | Y |
| T1.COL <> TC col expr | N | Y |
| T1.COL1 = T1.COL2 | Y | Y |
| T1.COL1 op T1.COL2 | Y | Y |
| T1.COL1 <> T1.COL2 | N | N |
| COL=(noncor subq) | Y | Y |
| COL = ANY(noncor subq) | N | Y |
| COL = ALL(noncor subq) | N | N |
| COL op (noncor subq) | Y | Y |
| COL op ANY (noncor subq) | Y | Y |
| COL op ALL (noncor subq) | Y | Y |
| COL <> (noncor subq) | N | Y |
| COL <> ANY (noncor subq) | N | N |
| COL <> ALL (noncor subq) | N | N |
| COL IN (noncor subq) | Y | Y |
| (COL1,…COLn) IN (noncor subq) | Y | Y |
| COL NOT IN (noncor subq) | N | N |
| (COL1,…COLn) NOT IN (noncor subq) | N | N |
| COL = (cor subq) | N | N |
| COL = ANY(cor subq) | Y | Y |
| COL = ALL (cor subq) | N | N |
| COL op (cor subq) | N | N |
| COL op ANY (cor subq) | N | N |
| COL op ALL (cor subq) | N | N |
| COL <> (cor subq) | N | N |
| COL <> ANY (cor subq) | N | N |
| COL <> ALL (cor subq) | N | N |
| COL IN (cor subq) | Y | Y |
| (COL1,…COLn)IN(cor subq) | N | N |
| COL NOT IN (cor subq) | N | N |
| (COL1,…COLn) NOT IN (cor subq) | N | N |
| COL IS DISTINCT FROM value | N | Y |
| COL IS NOT DISTINCT FROM value | Y | Y |
| COL IS DISTINCT FROM noncol expr | N | Y |
| COL IS NOT DISTINCT FROM noncol expr | Y | Y |
| T1.COL1 IS DISTINCT FROM T2.COL2 | N | N |
| T1.COL1 IS NOT DISTINCT FROM T2.COL2 | Y | Y |
| T1.COL1 IS DISTINCT FROM T2 col expr | N | Y |
| T1.COL1 IS NOT DISTINCT FROM T2 col expr | Y | Y |
| COL IS DISTINCT FROM (noncor subq) | N | Y |
| COL IS NOT DISTINCT FROM (noncor subq) | Y | Y |
| COL IS NOT DISTINCT FROM (cor subq) | N | N |
| SUBSTR(COL, 1, n)=value | Y | Y |
| SUBSTR(COL, 1, n) op value | Y | Y |
| DATE(COL) = value | Y | Y |
| DATE(COL) op value | Y | Y |
| YEAR(COL) = value | Y | Y |
| YEAR(COL) op value | Y | Y |
| EXISTS (subq) | N | N |
| NOT EXISTS (subq) | N | N |
| expression = value | N | N |
| expression <> value | N | N |
| expression op value | N | N |
| expression op (subq) | N | N |
| XMLEXISTS | Y | N |
| NOT XMLEXISTS | N | N |
Disclaimer: This Db2® 12 for z/OS Reference Guide was developed to help users in their daily activities in administrating and programming in Db2 for z/OS. There are no guarantees expressed or implied with the contents in this guide. We want to provide a quality and useful reference for users. Please notify us of any mistakes or errors in this reference guide at blogs@bmc.com. Db2 is a registered trademark of the IBM Corporation.
