Mainframe Blog

Predicates in Db2 12

3 minute read
Stephen Watts

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

Order now! Get your free Db2 collateral from BMC!

Get your free Reference Guide and Catalog Tables Poster for z/OS from BMC. This collateral helps Db2 for z/OS users with daily activities in administering and programming for Db2 on z/OS.


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 [email protected].

Business, Faster than Humanly Possible

BMC empowers 86% of the Forbes Global 50 to accelerate business value faster than humanly possible. Our industry-leading portfolio unlocks human and machine potential to drive business growth, innovation, and sustainable success. BMC does this in a simple and optimized way by connecting people, systems, and data that power the world’s largest organizations so they can seize a competitive advantage.
Learn more about BMC ›

About the author

Stephen Watts

Stephen Watts (Birmingham, AL) contributes to a variety of publications including, Search Engine Journal, ITSM.Tools, IT Chronicles, DZone, and CompTIA.