DB2 conversion and casting functions: CAST, INTEGER, DECIMAL, XMLCAST

CAST specification

CAST(expression AS data-type) returns the expression converted to data-type. You can also CAST a NULL or a parameter marker when the target type supplies the type of a null or an untyped parameter. Attributes that belong on the type — length, precision, scale, CCSID — go on the type, not as extra function arguments.

1
2
3
4
5
6
SELECT CAST(SALARY AS INTEGER) AS SAL_INT,
       CAST(SALARY AS DECIMAL(9,0)) AS SAL_WHOLE,
       CAST(EMPNO AS INTEGER) AS EMP_INT,
       CAST(NULL AS DECIMAL(7,2)) AS NULL_DEC
  FROM DSN8C10.EMP
 FETCH FIRST 5 ROWS ONLY;

• expression — the source value. Distinct types usually need a CAST to a built-in type before further math.
• data-type — built-in type (or a distinct type resolved via the SQL path). CHAR defaults to length 1 if you write CHAR without a length. DECIMAL defaults to precision 5 scale 0 if you write DECIMAL with no numbers — which is almost never what you wanted.
• Unsupported pair — not every source/target pair is legal. The SQL Reference “casting between data types” table is the authority. CHAR to DATE works when the string is a valid date; INTEGER to DATE does not mean “day number” unless you go through DATE() rules.

Implicit casts still happen (string to number in some numeric functions, numeric promotion in mixed arithmetic). CAST is for when you want the conversion visible, when you need a specific precision, or when implicit conversion is not allowed (distinct types, XML, many assignments).

INTEGER or INT

INTEGER(numeric-expression) or INT(...) returns a 32-bit integer. INTEGER(string- expression) parses a character or graphic string (not CLOB/DBCLOB, length attribute ≤ 255) that must be a valid integer or numeric constant after blanks are stripped.

1
2
3
4
5
6
SELECT INTEGER(1.9) AS TRUNC_UP,
       INTEGER(-1.9) AS TRUNC_NEG,
       INTEGER('  42  ') AS FROM_STR,
       INT(SALARY) AS SAL_AS_INT
  FROM DSN8C10.EMP
 FETCH FIRST 1 ROW ONLY;

Fractional parts are truncated toward zero: 1.9 becomes 1, not 2. Out-of-range whole parts (outside −2147483648 through 2147483647) raise an error. INTEGER is the usual bridge from DECIMAL money to a whole-dollar host PIC S9(9) COMP, after you have decided truncation is acceptable.

BIGINT

BIGINT(numeric-expression) and BIGINT(string-expression) return a big integer (64-bit). Truncation of the fractional part is the same as INTEGER. Range errors occur if the whole part does not fit in BIGINT. IBM’s note is the same as for other converters: for portability prefer CAST(x AS BIGINT).

1
2
3
SELECT BIGINT(1234567890123) AS BIG,
       BIGINT('0000000000000000001') AS FROM_STR
  FROM SYSIBM.SYSDUMMY1;

Use BIGINT when INTEGER overflows — row counts, IDENTITY values, and COUNT_BIG results already live in that world. Casting COUNT(*) (INTEGER) to BIGINT is unnecessary if you use COUNT_BIG from the start for large tables.

DECIMAL or DEC

DECIMAL is the packed-decimal converter and the one COBOL shops use constantly.

1
2
3
4
5
6
7
DECIMAL(numeric-expression)
DECIMAL(numeric-expression, precision)
DECIMAL(numeric-expression, precision, scale)
DECIMAL(string-expression)
DECIMAL(string-expression, precision)
DECIMAL(string-expression, precision, scale)
DECIMAL(string-expression, precision, scale, decimal-character)

Precision is the total number of digits (1–31 for DECIMAL). Scale is digits to the right of the decimal point (0 through precision). If you omit them, defaults depend on the source:

For string input, decimal-character says which character is the decimal point (period or comma). That matters for European numeric strings. The string must be a valid numeric representation.

1
2
3
4
5
SELECT DECIMAL(SALARY, 9, 2) AS D92,
       DECIMAL(COMM, 7, 2) AS COMM_D,
       DECIMAL('123,45', 5, 2, ',') AS EU_STYLE
  FROM DSN8C10.EMP
 FETCH FIRST 3 ROWS ONLY;

Shrinking scale truncates extra fractional digits. DECIMAL(1.239, 3, 2) is 1.23, not 1.24. Expanding precision is safe if the value fits. Overflow of the whole-digit portion is an error. NUMERIC is a synonym of DECIMAL as a data type; the DECIMAL/DEC function is the converter you call.

DOUBLE, DOUBLE_PRECISION, FLOAT

DOUBLE(numeric-expression) or DOUBLE_PRECISION(...) returns 64-bit binary floating- point. DOUBLE(string-expression) parses a numeric string (≤ 255 bytes, not CLOB). FLOAT is a synonym for DOUBLE in this function. REAL exists as a 32-bit function for the shorter float.

1
2
3
4
SELECT EMPNO,
       DOUBLE(SALARY) / COMM AS RATIO
  FROM DSN8C10.EMP
 WHERE COMM > 0;

IBM’s sample applies DOUBLE to SALARY so the division runs in floating-point and is less likely to overflow DECIMAL precision. Binary float cannot represent all decimal fractions exactly (0.1 is repeating in binary). For money, stay in DECIMAL. For scientific ratios and very large magnitudes, DOUBLE is appropriate.

DECFLOAT

DECFLOAT(numeric-expression) or DECFLOAT(string-expression) returns decimal floating-point, commonly DECFLOAT(34) unless you specify DECFLOAT(16) via CAST. DECFLOAT keeps decimal fractions exactly within its precision and supports special values (NaN, sNaN, infinities) that DECIMAL does not.

1
2
3
SELECT CAST(SALARY AS DECFLOAT(34)) / CAST(7 AS DECFLOAT(34)) AS SHARE
  FROM DSN8C10.EMP
 FETCH FIRST 3 ROWS ONLY;

Rounding of DECFLOAT operations follows CURRENT DECFLOAT ROUNDING MODE. CAST and the DECFLOAT function are how you enter that world from DECIMAL or strings. Do not mix DECFLOAT special values with DECIMAL columns without handling NaN; assignment may fail.

XMLCAST

XMLCAST(expression AS data-type) converts between XML and SQL types. Either the source or the target must be XML. You cannot XMLCAST INTEGER to DECIMAL — that is ordinary CAST. You can XMLCAST an XMLQUERY result to INTEGER when the XQuery item is an xs:integer-compatible value.

1
2
3
4
5
SELECT XMLCAST(
         XMLQUERY('/PRODUCT/QUANTITY' PASSING xmlcol)
         AS INTEGER
       ) AS QTY
  FROM MYXML.T;

• XML to SQL — supported targets include SMALLINT, INTEGER, BIGINT, DECIMAL, DECFLOAT, FLOAT/REAL/DOUBLE, character and graphic strings, DATE, TIME, TIMESTAMP. BINARY/BLOB/ROWID are not supported from XML in the documented table.
• SQL to XML — builds an XML value from a non-XML operand. String results default to Unicode encoding; CCSID can be specified on the target type.
• NULL AS XML — a null XML value. Parameter markers take their type from the AS clause.
• TIMESTAMP to XML — trailing zeros in fractional seconds are not kept in the XML lexical form.

Distinct types are not valid XMLCAST targets. XMLTABLE column types often use the same XMLCAST rules under the covers when an XQuery item is stored in a typed SQL column.

Choosing CAST versus a named function

• New, portable SQL — CAST(x AS DECIMAL(9,2)), CAST(x AS BIGINT)
• Existing z/OS style — INTEGER(x), DEC(x, 9, 2), still correct
• XML involved — XMLCAST, not CAST
• Need rounding, not type change — ROUND/FLOOR first; conversion is a separate step
• Need a string picture — CHAR/VARCHAR/DIGITS, which are conversion-to- string, covered with other string functions

Function resolution applies to INTEGER() because the name is overloaded. CAST does not search the SQL path for a function named CAST; it uses the cast rules table. That is another reason CAST is easier to reason about next to user-defined functions named INT.

Errors you will actually see

Invalid string representations, overflow, and unsupported cast pairs raise negative SQLCODEs (for example data exceptions on invalid numeric strings, and assignment / cast errors on overflow). COBOL programs should check SQLCODE after a SELECT that casts host-unfriendly columns. Do not catch conversion errors by wrapping everything in COALESCE to zero unless zero is a true business default — you will hide bad data.

Explain It Like I'm Five

CAST is pouring juice from one cup into another cup that has a different shape. INTEGER is a cup that only holds whole ice cubes — leftover slush (the fraction) is left behind, not rounded into another cube. DECIMAL is an ice-cube tray with a fixed number of slots before and after the decimal line; if you pour too many drips after the line, the extra drips spill (truncate). DOUBLE is a stretchy science cup that can hold huge or tiny amounts but might not line up exactly with pocket-change pennies. XMLCAST is a special funnel that only connects the XML bucket to SQL cups. Using the wrong funnel is how juice ends up on the floor (an error), not in a mystery zero.

Exercises

1. Compare INTEGER(SALARY), CAST(SALARY AS INTEGER), and ROUND(SALARY, 0) on a salary that is not a whole number. Which two match, and which one rounds?
2. Write DECIMAL conversions of INTEGER 12 with no precision, with (5,0), and with (5,2). Describe each result.
3. Attempt BIGINT of a string that contains a comma thousands separator. What happens, and how would you clean the string first?
4. Explain when DOUBLE(SALARY)/COMM is preferable to SALARY/COMM in DECIMAL, and when it is a bad idea for money.
5. Given an XMLQUERY that returns a quantity element, write XMLCAST to DECIMAL(9,2) and list one target type that XMLCAST will not allow.