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 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. 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 blogs@bmc.com.

BMC Brings the A-Game

BMC works with 86% of the Forbes Global 50 and customers and partners around the world to create their future. With our history of innovation, industry-leading automation, operations, and service management solutions, combined with unmatched flexibility, we help organizations free up time and space to become an Autonomous Digital Enterprise that conquers the opportunities ahead.
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.