Other DB2 operators and precedence: strings, ||, and bits

String operators

The string operator you will use constantly is concatenation: take two strings and make one longer string. Db2 for z/OS provides two operator spellings and a function spelling of the same idea:

1
2
3
4
5
6
7
8
SELECT FIRSTNME CONCAT ' ' CONCAT LASTNAME AS FULL_NAME
  FROM HR.EMPLOYEE;
 
SELECT FIRSTNME || ' ' || LASTNAME AS FULL_NAME
  FROM HR.EMPLOYEE;
 
SELECT CONCAT(FIRSTNME, CONCAT(' ', LASTNAME)) AS FULL_NAME
  FROM HR.EMPLOYEE;

All three produce a full name with a space in the middle, provided FIRSTNME and LASTNAME are compatible character (or graphic) strings. Prefer the CONCAT keyword in shop standards and in SQL you copy between systems.

Why CONCAT beats ||

Both CONCAT and the vertical bars || represent the concatenation operator. Vertical bars—or the characters some national EBCDIC code pages use in place of vertical bars—can cause parsing errors when a statement is passed from one DBMS to another and undergoes character conversion with certain source and target CCSID pairs. IBM documents code-point combinations such as X'4F4F' and others that Db2 may interpret as concatenation. That is a z/OS-flavored footgun: a statement that looks perfect in one emulator code page breaks after a conversion.

CONCAT is letters. Letters survive CCSID conversion. Use CONCAT in dynamic SQL, in packages that travel through DDF, and in any example you publish.

Nulls, types, and length

• NULL — if either operand is null, the concatenation result is null. An empty string is not null; concatenating 'A' with '' yields 'A'. Concatenating 'A' with NULL yields NULL.
• Compatibility — character strings concatenate with character strings. Graphic strings concatenate with graphic strings. Binary strings concatenate with binary strings. Do not mix character and binary without an explicit CAST to a common kind.
• Distinct types — you cannot concatenate distinct (user-defined) types even if they are based on VARCHAR. Create a sourced user-defined function, or CAST to the source type first.
• Result type — Db2 picks CHAR vs VARCHAR vs CLOB (and graphic/binary counterparts) and a length from documented combination rules. Long concatenations can become CLOB. Watch host-variable lengths in COBOL.
• Parameter markers — one operand may be an untyped parameter marker; its attributes are taken from the other operand (with documented length formulas for strings). Nested CONCAT needs you to think about order so those attributes are correct.

1
2
3
4
5
6
7
-- Null name fragment wipes the whole result
SELECT FIRSTNME CONCAT ' ' CONCAT LASTNAME
  FROM HR.EMPLOYEE;
 
-- Protect fragments
SELECT COALESCE(FIRSTNME, '') CONCAT ' ' CONCAT COALESCE(LASTNAME, '')
  FROM HR.EMPLOYEE;

Padding matters for CHAR columns. CHAR(10) FIRSTNME holds trailing blanks. Concatenating it with a space and a last name often looks like “JOHN SMITH”. Use STRIP or TRIM / RTRIM when you want a tidy display string.

1
2
SELECT STRIP(FIRSTNME) CONCAT ' ' CONCAT STRIP(LASTNAME) AS FULL_NAME
  FROM HR.EMPLOYEE;

The || operator

|| is not a different operation. It is the symbol form of CONCAT. Some people prefer it because other SQL dialects use it. On Db2 for z/OS it is legal and common in ad-hoc SPUFI, but it is the form most likely to break when the source is not the CCSID you thought. If a code review asks you to change || to CONCAT, that is a portability fix, not a style nitpick.

Do not confuse || with OR. OR is a word in a search condition. || is a string operator in an expression. This is valid:

1
2
3
4
SELECT STRIP(FIRSTNME) CONCAT ' ' CONCAT STRIP(LASTNAME) AS NAME
  FROM HR.EMPLOYEE
 WHERE WORKDEPT = 'A00'
    OR WORKDEPT = 'B01';

Bit manipulation

Some tables store several yes/no flags packed into one integer: bit 0 means “on sale,” bit 2 means “hazardous,” and so on. Db2 for z/OS does not use C-style &, |, and ^ operators in the same way some Db2 LUW / cloud SQL dialects do. On z/OS you call scalar functions:

The functions operate on the two’s complement representation of the integer value and return a corresponding base-10 integer. Arguments must be integer values as SMALLINT, INTEGER, BIGINT, or DECFLOAT. DECIMAL, REAL, or DOUBLE arguments are cast to DECFLOAT and truncated to a whole number. Width is 16 bits for SMALLINT, 32 for INTEGER, 64 for BIGINT, and 113 for DECFLOAT. NaN and infinity are not supported.

If the two arguments have different types, the narrower one is cast to the wider type. That cast matters for negative values: SMALLINT −1 has 16 one-bits; cast to INTEGER it has 32 one-bits.

If any argument is null, the result is null. Compare and display bit results as integer values, not as HEX dumps of internal bytes—internal representation is data-type and platform dependent.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- Third property bit (value 4) is set
SELECT ITEMID
  FROM INV.ITEM
 WHERE BITAND(PROPERTIES, 4) = 4;
 
-- Fourth (8) or sixth (32) bit: 8 + 32 = 40
SELECT ITEMID
  FROM INV.ITEM
 WHERE BITAND(PROPERTIES, 40) <> 0;
 
-- Turn on bit 5 (value 16)
UPDATE INV.ITEM
   SET PROPERTIES = BITOR(PROPERTIES, 16)
 WHERE ITEMID = 3412;
 
-- Clear bit 12 (value 2048)
UPDATE INV.ITEM
   SET PROPERTIES = BITANDNOT(PROPERTIES, 2048)
 WHERE ITEMID = 3412;
 
-- Toggle bit 11 (value 1024)
UPDATE INV.ITEM
   SET PROPERTIES = BITXOR(PROPERTIES, 1024)
 WHERE ITEMID = 3412;

IBM recommends BITANDNOT(val, pattern) to clear bits rather than BITAND(val, BITNOT(pattern)). Use BITXOR to toggle. BITNOT flips every bit of the type’s width, so BITNOT of SMALLINT 2 is −3, not “the other small flags you had in mind.” Prefer AND-masks over NOT when you only care about a few bits.

Logical operators AND/OR/NOT still wrap the predicate you build from a bit function:

1
2
3
4
SELECT ITEMID, PROPERTIES
  FROM INV.ITEM
 WHERE BITAND(PROPERTIES, 4) = 4
   AND NOT BITAND(PROPERTIES, 1) = 1;

Operator precedence in expressions

When an expression mixes operators and you omit parentheses, Db2 uses a fixed order. From the SQL Reference (expressions — precedence of operations):

• Expressions in parentheses are evaluated first
• Prefix operators (unary + and −) apply before multiplication and division
• Multiplication, division, and concatenation apply before addition and subtraction
• Operators at the same precedence level are applied left to right

1
2
3
4
5
6
7
8
9
10
-- 14, not 20
SELECT 2 + 3 * 4
  FROM SYSIBM.SYSDUMMY1;
 
-- Force addition first → 20
SELECT (2 + 3) * 4
  FROM SYSIBM.SYSDUMMY1;
 
-- CONCAT is at the * / level, so it happens before binary minus
-- DATECOL - (:YYYYMM CONCAT :DD) is a date duration pattern IBM documents

IBM’s classic example: DATECOL - :YYYYMM CONCAT :DD concatenates the host variables first (same level as * /), then subtracts that date from DATECOL. If that surprises you, you needed parentheses. Write them.

Prefix minus is not the same as subtraction. −SALARY * 2 negates SALARY first, then multiplies. 0 - SALARY * 2 multiplies SALARY * 2 first, then subtracts from 0. The numeric result matches for that example, but mixed expressions with CONCAT or several terms are easier to get wrong. Parentheses remove the debate.

Two precedence worlds

Keep two charts:

• Value expressions — unary ±, then * / CONCAT, then + −
• Search conditions — NOT, then AND, then OR (previous page)

Comparisons sit between them: they turn expressions into predicates. You do not multiply AND. You do not CONCAT a search condition. A full statement uses both charts:

1
2
3
4
5
6
7
SELECT EMPNO,
       STRIP(FIRSTNME) CONCAT ' ' CONCAT STRIP(LASTNAME) AS NAME,
       SALARY * 1.03 AS NEXT_SALARY
  FROM HR.EMPLOYEE
 WHERE WORKDEPT IN ('A00', 'B01')
   AND SALARY + COALESCE(COMM, 0) > 55000
 ORDER BY NEXT_SALARY DESC;

The SELECT list uses CONCAT and *. The WHERE clause uses IN, AND, and a comparison of an arithmetic expression. Precedence inside SALARY * 1.03 is obvious; the AND grouping is obvious because there is no OR. The moment you add OR, parenthesize the WHERE clause.

CONCAT with numbers, dates, and mixed encodings

Concatenation wants strings. If you write EMPNO CONCAT '-' CONCAT LASTNAME and EMPNO is CHAR or VARCHAR, you are fine. If EMPNO is INTEGER, Db2 must convert it to a string first. Implicit conversion works in many cases and surprises you in others (leading zeros, decimal scale, date formats). Prefer an explicit CHAR(EMPNO), DIGITS(EMPNO), or VARCHAR_FORMAT for datetimes so the glued result matches the report layout your COBOL copybook expects.

1
2
3
4
5
SELECT DIGITS(EMPNO) CONCAT '-' CONCAT STRIP(LASTNAME) AS BADGE
  FROM HR.EMPLOYEE;
 
SELECT VARCHAR_FORMAT(HIREDATE, 'YYYY-MM-DD') CONCAT ' hired'
  FROM HR.EMPLOYEE;

Mixed CCSID operands force character conversion before CONCAT. The result CCSID follows documented rules (often the “more mixed / Unicode” operand wins). If a name looks right in SPUFI but wrong in a Unicode Java client, check CCSID before rewriting CONCAT. Graphic and character strings do not casually concatenate; CAST to a common string kind first.

Do not confuse CONCAT with XMLCONCAT. XMLCONCAT combines XML values. Using the wrong one is a type error. Likewise, the scalar function CONCAT(a, b) takes two arguments only; chain it or use the operator form for three or more pieces.

Bit flags in real tables

Flag integers show up in control tables, message-attribute columns, and packages that copied C bit masks onto the mainframe. Document each bit’s value as a power of two (1, 2, 4, 8, 16, …) in a comment or a check table. Test bits with equality to the mask when you need that bit on regardless of others: BITAND(FLAGS, 8) = 8. Test “any of these bits” with BITAND(FLAGS, 40) <> 0.

Never use logical AND in place of BITAND. WHERE FLAGS AND 8 is invalid or meaningless as a bit test. Never assume LUW & / | operator syntax will bind on z/OS. Stick to the five scalar functions, CAST the width you mean (SMALLINT vs INTEGER) before BITNOT, and treat null FLAGS as “no bits known,” not as zero, unless COALESCE says otherwise.

Precedence mistakes in mixed CONCAT and arithmetic are hard to see in a trace because the statement still runs. AMT + TAX CONCAT ' USD' is not “format money then glue a label” unless parentheses say so; CONCAT sits with * /, so it may glue TAX to the label before addition. Write (AMT + TAX) CONCAT ' USD' after converting the number to a string. When a result looks like a truncated number stuck to text, draw the precedence tree before changing data types.

Shop standards often allow CONCAT, ban ||, require parentheses around mixed AND/OR, and require parentheses around mixed + and CONCAT. That is not bureaucracy. It is how teams survive CCSID conversions and 2 a.m. production fixes.

Explain It Like I'm Five

CONCAT is glue for words: “first name” plus a space plus “last name” becomes one name sticker. The || glue is the same glue with a shyer symbol that sometimes gets lost when the sticker is photocopied into another alphabet (CCSID). Bit functions are a row of light switches packed into one number: BITAND asks “are these switches on in both numbers?”, BITOR turns switches on, BITANDNOT turns chosen switches off. Precedence is the school rule that multiply happens before add, so you do not have to write every parenthesis—though writing them is still the kind thing to do for the next reader.

Exercises

1. Build FULL_NAME with CONCAT and STRIP from FIRSTNME and LASTNAME.
2. Explain in one sentence why a shop standard might ban ||.
3. Write a WHERE clause that is true when integer FLAGS has bit value 8 set, using BITAND.
4. Clear bit value 8 with BITANDNOT in an UPDATE.
5. Evaluate 10 - 2 * 3 and (10 - 2) * 3, then state which operators share precedence with CONCAT.