Casting and data conversion in DB2

Why conversion happens

SQL is strongly typed but pragmatic. Comparing an INTEGER column to a DECIMAL literal, assigning a CHAR value into VARCHAR, or calling a function that expects DOUBLE all require a well-defined type change. Db2 documents which pairs are compatible, which are castable, and which promotions are allowed. When a conversion is not allowed, you get an error instead of a silent wrong answer—usually what you want.

Three ideas work together:

• Implicit conversion — Db2 converts without an explicit CAST when rules allow
• Promotion — treating a type as a later type on a precedence list
• Explicit casting — you write CAST or a cast function to force the type

Implicit data conversion

Implicit conversion appears during assignment, comparison of compatible types, and some numeric↔string situations documented for your Db2 version. Examples of the spirit of the rules:

• Numeric types are broadly compatible with each other for comparison
• Character strings may convert to numeric when the string is a valid numeric representation—in contexts that allow it
• Numeric values may convert to strings with result lengths that depend on the source type (for example SMALLINT toward a short VARCHAR length in documented tables)

1
2
3
4
5
-- Compatible numerics compare after appropriate conversion/promotion
WHERE SMALLINT_COL > 1000
 
-- Explicit is clearer when types look “stringly”
WHERE INTEGER_COL = CAST('42' AS INTEGER);

Do not rely on implicit string↔numeric conversion in application SQL if you can avoid it. Explicit CAST documents intent, fails loudly on bad data, and makes reviews easier. Distinct types rarely convert implicitly to their source type—you usually must cast.

Data type promotion

Promotion follows a best-to-worst precedence list within related groups. The “best” choice is the same type; worse choices are wider or more general related types. Db2 consults promotion when resolving functions, casting distinct types in some cases, and assigning built-in types to distinct types.

Important nuance: DECFLOAT is not promoted “down” to binary floating-point for function resolution in the way smaller floats promote up—DECFLOAT’s precision and exponent range exceed classic float. Distinct types promote only to the same distinct type.

1
2
3
-- SMALLINT argument can promote when resolving a function
-- that expects INTEGER or DECIMAL
VALUES POWER(SMALLINT_COL, 2);

Casting (explicit)

Use the CAST specification when you want a definite target type:

1
2
3
4
SELECT CAST(SALARY AS DECIMAL(10,2)) AS SAL_DEC,
       CAST(HIREDATE AS CHAR(10)) AS HIRE_CHAR,
       CAST(NULL AS INTEGER) AS NULL_INT
  FROM HR.EMPLOYEE;

You can also call cast-oriented scalar functions (INTEGER, DECIMAL, CHAR, DATE, and many others). XML data uses XMLCAST in XML contexts. Truncating non-blank characters when casting to a shorter string typically raises a warning; truncating binary casts can raise an error.

Distinct types

When you CREATE TYPE for a distinct type, Db2 generates cast functions between the distinct type and its source type. You generally cannot compare a distinct-typed column directly to a built-in literal without casting one side so the types match.

1
2
3
4
-- Conceptual pattern
WHERE PRICE > US_DOLLAR(100.00)
-- or
WHERE CAST(PRICE AS DECIMAL(9,2)) > 100.00;

Function resolution

Function resolution is Db2’s process for choosing which function to run when you write a name and argument list. It considers:

• Unqualified versus schema-qualified function names
• Number and types of arguments
• Promotion of argument types to candidate parameter types
• The ordered schemas on the SQL path

If two candidates fit equally well, you can get an ambiguous function error—qualify the name or cast arguments to steer resolution. Built-in functions in SYSIBM usually win when system schemas sit first on the path.

SQL path

The SQL path is an ordered list of schemas. For dynamic SQL, CURRENT PATH supplies it (SET PATH / SET CURRENT PATH). For static SQL, the bind-time PATH option does. If you omit SYSIBM, SYSFUN, SYSPROC, and SYSIBMADM, Db2 still assumes them at the front in a defined order.

1
2
SET PATH = SYSTEM PATH, HR, PAYROLL;
VALUES MYFUNC(1);   -- search path for unqualified MYFUNC

Changing CURRENT SCHEMA does not automatically put that schema on the path. After SET CURRENT SCHEMA = 'HR', still include HR on the path if you need unqualified UDFs in HR to resolve.

Practical guidance

• Prefer explicit CAST at API boundaries and in teaching examples
• Match host variables to column types to avoid surprise conversions in COBOL/C/Java
• Cast distinct types before comparing to built-in literals
• Keep PATH intentional in packages that call UDFs
• Watch truncation warnings when casting to shorter strings

Explain It Like I'm Five

Imagine toy blocks of different shapes: small number blocks, bigger number blocks, and letter blocks. Sometimes Db2 quietly swaps a small number block for a bigger one so it fits a game (promotion / implicit conversion). Sometimes you say out loud “pretend this letter card is a number” with CAST. Function resolution is picking which board game to play when several games share a name. The SQL path is the shelf order Db2 searches when looking for that game.

Exercises

1. Write CAST to turn the string '2024-01-15' into a DATE.
2. List two types SMALLINT can promote toward.
3. Explain why SET CURRENT SCHEMA alone might not fix “function not found.”
4. Why might comparing a distinct type MONEY to 10.00 fail without a cast?
5. Give one reason to prefer CAST('42' AS INTEGER) over relying on implicit conversion.