image_pdfimage_print
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.

Order Now! Get your free Db2 collateral from BMC!

Get your free Reference Guide and Catalog Tables Poster for z/OS from BMC.
Order Now ›
Last updated: 04/01/2019

These postings are my own and do not necessarily represent BMC's position, strategies, or opinion.

See an error or have a suggestion? Please let us know by emailing blogs@bmc.com.

About the author

Stephen Watts

Stephen Watts

Stephen is based in Birmingham, AL and began working at BMC Software in 2012. Stephen holds a degree in Philosophy from Auburn University and is currently enrolled in the MS in Information Systems - Enterprise Technology Management program at University of Colorado Denver.

Stephen contributes to a variety of publications including CIO.com, Search Engine Journal, ITSM.Tools, IT Chronicles, DZone, and CompTIA.