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 Watts (Birmingham, AL) has worked at the intersection of IT and marketing for BMC Software since 2012.

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

Run and Reinvent Your Business with BMC

BMC has unmatched experience in IT management, supporting 92 of the Forbes Global 100, and earning recognition as an ITSM Gartner Magic Quadrant Leader for six years running. Our solutions offer speed, agility, and efficiency to tackle business challenges in the areas of service management, automation, operations, and the mainframe. Learn more about BMC ›