(This article is part of our Db2 Guide. Use the right-hand menu to navigate.)
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 [email protected]. Db2 is a registered trademark of the IBM Corporation.