DB2 data type compatibility and host mappings

Data type mechanics

Every value in SQL has a data type with attributes: length, precision, scale, CCSID, nullability, datetime precision, and (for distinct types) a source type. When two values meet—in assignment, comparison, concatenation, arithmetic, or a function call—Db2 applies a checklist:

• Are the types in a compatible family?
• If they differ, can one be promoted along a precedence list?
• Is an implicit cast allowed, or must you write CAST?
• Will the result fit (length, precision, scale) or truncate / overflow?
• For strings, what CCSID conversion happens?

Host variables add a second mapping: the precompiler or driver translates a COBOL PIC, a C type, or a java.sql.Types code into an SQL type. If that mapping is wrong, you get truncation, decimal-point disasters, or SQLCODE -301 / -312 style mismatches even when the SQL looks fine.

Data type compatibility

Compatible types may be assigned or compared (with conversion if needed). Incompatible types are errors unless you CAST to a type that is compatible with the other operand.

• Numbers — SMALLINT, INTEGER, BIGINT, DECIMAL/NUMERIC, REAL, DOUBLE, DECFLOAT are generally compatible with each other; watch overflow and scale
• Character strings — CHAR, VARCHAR, CLOB (and mixed/SBCS subtypes). Fixed and varying lengths convert to each other when necessary
• Graphic strings — GRAPHIC, VARGRAPHIC, DBCLOB
• Binary strings — BINARY, VARBINARY, BLOB. Binary is not the same family as CHAR FOR BIT DATA in new designs; BX literals belong here, X literals belong to character
• Datetime — DATE, TIME, TIMESTAMP, TIMESTAMP WITH TIME ZONE. They are not freely interchangeable with CHAR even though they display as strings; assignment from a correctly formatted character value is a conversion
• XML — compatible with XML host types and, with conversion, character or binary strings; prefer XML host types
• Distinct types — compatible with the same distinct type; mix with the source type only through generated casts or sourced functions
• Partial compatibility with locators — CHAR/VARCHAR with CLOB locators, GRAPHIC/VARGRAPHIC with DBCLOB locators, BINARY/VARBINARY with BLOB locators, in the assignment statements IBM documents (SELECT INTO, SET, VALUES INTO, and some procedure parameters)

Concatenation requires compatible strings in the same grouping. You cannot CONCAT a binary string with a character string, including FOR BIT DATA character strings.

Data type precedence and promotion

Within a family, types are ordered from best (same type) to worse (more general). Promotion means treating a value as a type later in that list. Promotion is used in function resolution, some distinct-type casts, and assigning user-defined types to built-in types.

Examples: CHAR can be promoted to VARCHAR; INTEGER can be promoted toward BIGINT or decimal or float; CLOB cannot be promoted “down” to VARCHAR. DATE does not promote to TIMESTAMP automatically in the numeric-style list—datetime types stay in their own bucket unless you CAST. The best match in function resolution is always the same type before a promoted type.

Casting

CAST(expression AS type) (and cast functions such as INTEGER, VARCHAR, DATE) convert explicitly. Use CAST when:

• Families differ but a supported conversion exists (string to DATE)
• You must change length, precision, or TIMESTAMP fractional seconds
• A distinct type must meet a built-in parameter
• You want the conversion visible to the next reader and to EXPLAIN

1
2
3
4
5
SELECT CAST(HIREDATE AS VARCHAR(10)) AS HIRE_CHAR,
       CAST('2024-01-15' AS DATE) AS D,
       INTEGER('42') AS N
  FROM HR.EMPLOYEE
 FETCH FIRST 1 ROW ONLY;

Implicit casts still happen (fixed to varying string, some numeric widenings). Do not rely on them for distinct types or for character-to-datetime in portable SQL. Details live on the casting-and-data-conversion page; here the rule is: if the compiler complains, CAST is the first tool, not a random host-variable PIC change.

Assignment compatibility

Assignment (INSERT, UPDATE, SET, SELECT INTO, FETCH) is stricter about length and precision than comparison. A VARCHAR(10) value assigned to CHAR(5) truncates (and may warn). A DECIMAL(9,2) assigned to SMALLINT must be a whole number in range or you get overflow. Null requires an indicator on the host side. Datetime assignment from a string uses the datetime string format rules, not “any PIC X.”

Host variable compatibility means the SQL type Db2 infers from the host declaration must be compatible with the column or parameter. Truncation examples IBM highlights: fetching CHAR(80) into PIC X(70) drops 10 characters; fetching DOUBLE into PIC S9(8) COMP drops the fraction. Match lengths on purpose.

SQL-to-COBOL mapping

Prefer DCLGEN so pictures match the catalog. Modern DCLGEN can emit COMP-5 for integers so TRUNC(OPT) in Enterprise COBOL is safe.

1
2
3
4
5
       01  HV-EMPNO           PIC X(6).
       01  HV-SALARY          PIC S9(7)V9(2) COMP-3.
       01  HV-SALARY-IND      PIC S9(4) COMP-5.
       01  HV-HIREDATE        PIC X(10).
       01  HV-RESUME          USAGE IS SQL TYPE IS CLOB-LOCATOR.

• Indicators — PIC S9(4) COMP-5; negative means null
• COMP vs COMP-5 — COMP-5 is native binary; COMP/COMP-4/BINARY can truncate to the PICTURE if TRUNC(STD) is in effect
• VARCHAR — two 49-level items: a halfword length and the character data
• BINARY / VARBINARY — SQL TYPE IS; COBOL has no native equivalent

SQL-to-C mapping

In C (and C++) embedded SQL, short/int/long (and long long for BIGINT) map to the integer family; double to DOUBLE; decimal types often need SQL TYPE IS DECIMAL or packed structures; NUL-terminated char arrays map to CHAR/VARCHAR with length care (the NUL is not stored in Db2). LOBs use SQL TYPE IS CLOB(n) and locator forms. Graphic data uses wchar-oriented or SQL TYPE IS GRAPHIC declarations depending on the precompiler. Always include NUL terminator space in the C array that you do not count in the SQL length.

SQL-to-PL/I mapping

PL/I FIXED BIN(15) aligns with SMALLINT, FIXED BIN(31) with INTEGER, FIXED BIN(63) with BIGINT, FIXED DEC(p,s) with DECIMAL, FLOAT BIN with floating-point, CHAR(n) and CHAR(n) VAR with CHAR/VARCHAR, GRAPHIC with graphic types. LOB locators are DECLARE ... SQL TYPE IS CLOB_LOCATOR (underscores). PL/I VARYING strings carry a length prefix the precompiler understands—do not confuse them with COBOL 49-level groups.

SQL-to-REXX mapping

REXX variables are strings from the language’s point of view. Db2 infers SQL types from how you pass data and from explicit typing functions in the REXX SQL interface. There is no PIC clause. Numeric-looking strings can be sent as DECIMAL or INTEGER depending on format. LOBs map toward strings. For production data movement, prefer COBOL/C/Java where types are explicit; use REXX for tooling and check SQLCODE after every statement.

ODBC mappings

ODBC applications bind with SQLBindParameter / SQLBindCol using C types (SQL_C_CHAR, SQL_C_LONG, SQL_C_TYPE_TIMESTAMP, SQL_C_BINARY, …) and SQL types (SQL_VARCHAR, SQL_INTEGER, SQL_TIMESTAMP, SQL_BLOB, …). The driver converts between them. Timestamp structs include fraction fields that must match TIMESTAMP(p). Wide-character C types pair with graphic/Unicode columns. Check the IBM ODBC type table for DECFLOAT and XML, which need driver-specific type codes.

JDBC and SQLJ mappings

JDBC and SQLJ share the Java-to-SQL table. Use the recommended first Java type on setXXX / getXXX and on SQLJ host expressions.

SQLJ uses the same Java types in :host expressions. Stored procedure Java methods must match CREATE PROCEDURE parameter types; if you use a non-default Java type on z/OS you may need a method signature in the EXTERNAL clause. DBCLOB often surfaces as Clob. ROWID uses java.sql.Types.ROWID. DECFLOAT commonly uses BigDecimal plus a DB2Types DECFLOAT code.

Graphic conversion

Graphic (DBCS / Unicode graphic) data is not CHAR. Conversion between SBCS, mixed, and graphic uses CCSID pairing. Host GRAPHIC variables (COBOL PIC G ... DISPLAY-1, PL/I GRAPHIC) must match the column’s encoding scheme. UX'...' literals are UTF-16 graphic constants. Implicit conversion can occur, but “looks like garbage in the terminal” is usually a CCSID or host-type mismatch, not a bad predicate. Do not store graphic data in PIC X and hope.

Binary data

BINARY, VARBINARY, and BLOB are binary strings. Host mappings are SQL TYPE IS in COBOL and PL/I, byte[] or Blob in Java, SQL_C_BINARY in ODBC. Character FOR BIT DATA is a legacy overlap: it is still a character type for many SQL rules. New work should use true binary types and BX'...' literals. Concatenation and comparison stay inside the binary family.

TIMESTAMP precision, time zones, and current registers

TIMESTAMP(p) stores p fractional second digits (0 through 12). A host PIC X(26) matches the older TIMESTAMP(6) external form (19 characters of datetime plus a dot plus 6 digits). Higher precision needs a longer character host variable or a typed timestamp structure. JDBC java.sql.Timestamp nanos do not cover all 12 digits; know the driver’s rounding.

TIMESTAMP WITH TIME ZONE adds an offset. Assignment between WITH TIME ZONE and without it is a conversion, not a silent ignore of the zone. Session time zone special registers affect how some values are interpreted.

1
2
3
4
VALUES CURRENT DATE,
       CURRENT TIME,
       CURRENT TIMESTAMP,
       CURRENT TIMESTAMP WITH TIME ZONE;

• CURRENT DATE — DATE special register; assign to DATE columns or CHAR(10) after CAST
• CURRENT TIME — TIME special register
• CURRENT TIMESTAMP — TIMESTAMP with the precision implied by context or explicit CURRENT TIMESTAMP(p)

All three are evaluated in a way that is consistent for the statement (you do not get a different DATE in the SELECT list and the WHERE clause of the same statement). Host variables that receive them must be assignment-compatible: PIC X(10) for DATE, PIC X(8) for TIME, and a timestamp-length string or SQL TYPE for TIMESTAMP. Java uses java.sql.Date, Time, and Timestamp respectively.

Other compatibility notes

• ROWID — its own type; host SQL TYPE IS ROWID; not a CHAR you invent
• XML — XML AS CLOB/BLOB host forms or SQLXML; serialise to strings only when you mean to
• Parameter markers — untyped ? inherit type from context; wrong context yields -418 / -313 families of errors
• DECFLOAT — not the same as DECIMAL; C/PL/I DCLGEN support arrived later; Java uses BigDecimal plus driver type codes

Explain It Like I'm Five

Imagine LEGO bricks that only snap to the same shape of brick. Numbers snap to numbers, letters snap to letters, pictures-of-clocks snap to clocks. Promotion is using a small brick in a hole that also fits a bigger brick of the same colour. CAST is melting a brick and recasting it as another shape when the rules allow. Host mappings are the instruction sheet that says “this COBOL box is the INTEGER brick.” If you put an INTEGER brick in a tiny COBOL box, extra studs snap off (truncation).

Exercises

1. Write the COBOL picture you would expect DCLGEN to produce for SMALLINT, INTEGER, and DECIMAL(9,2).
2. List one type SMALLINT can promote to and one type CLOB cannot promote to.
3. Choose java.sql.Types and a Java class for a VARBINARY column.
4. Explain why fetching TIMESTAMP(12) into PIC X(26) is a bad idea.
5. Assign CURRENT DATE to a DATE column in an INSERT and name the host type you would use in COBOL to FETCH it back.

Frequently asked questions