Compatibility of SQL and language data types

The host variable data types that are used in SQL statements must be compatible with the data types of the columns with which you intend to use them.

When deciding the data types of host variables, consider the following rules and recommendations:

  • Numeric data types are compatible with each other:

    Assembler: A SMALLINT, INTEGER, BIGINT, DECIMAL, or FLOAT column is compatible with a numeric assembler host variable.

    Fortran: An INTEGER column is compatible with any Fortran host variable that is defined as INTEGER*2, INTEGER*4, REAL, REAL*4, REAL*8, or DOUBLE PRECISION.

    PL/I: A SMALLINT, INTEGER, BIGINT, DECIMAL, or FLOAT column is compatible with a PL/I host variable of BIN FIXED(15), BIN FIXED(31), DECIMAL(s,p), or BIN FLOAT(n), where n is from 1 to 53, or DEC FLOAT(m) where m is from 1 to 16.

  • Character data types are compatible with each other:

    Assembler: A CHAR, VARCHAR, or CLOB column is compatible with a fixed-length or varying-length assembler character host variable.

    C/C++: A CHAR, VARCHAR, or CLOB column is compatible with a single-character, NUL-terminated, or VARCHAR structured form of a C character host variable.

    COBOL: A CHAR, VARCHAR, or CLOB column is compatible with a fixed-length or varying-length COBOL character host variable.

    Fortran: A CHAR, VARCHAR, or CLOB column is compatible with Fortran character host variable.

    PL/I: A CHAR, VARCHAR, or CLOB column is compatible with a fixed-length or varying-length PL/I character host variable.

  • Character data types are partially compatible with CLOB locators. You can perform the following assignments:
    • Assign a value in a CLOB locator to a CHAR or VARCHAR column
    • Use a SELECT INTO statement to assign a CHAR or VARCHAR column to a CLOB locator host variable.
    • Assign a CHAR or VARCHAR output parameter from a user-defined function or stored procedure to a CLOB locator host variable.
    • Use a SET assignment statement to assign a CHAR or VARCHAR transition variable to a CLOB locator host variable.
    • Use a VALUES INTO statement to assign a CHAR or VARCHAR function parameter to a CLOB locator host variable.
    However, you cannot use a FETCH statement to assign a value in a CHAR or VARCHAR column to a CLOB locator host variable.
  • Graphic data types are compatible with each other:

    Assembler: A GRAPHIC, VARGRAPHIC, or DBCLOB column is compatible with a fixed-length or varying-length assembler graphic character host variable.

    C/C++: A GRAPHIC, VARGRAPHIC, or DBCLOB column is compatible with a single character, NUL-terminated, or VARGRAPHIC structured form of a C graphic host variable.

    COBOL: A GRAPHIC, VARGRAPHIC, or DBCLOB column is compatible with a fixed-length or varying-length COBOL graphic string host variable.

    PL/I: A GRAPHIC, VARGRAPHIC, or DBCLOB column is compatible with a fixed-length or varying-length PL/I graphic character host variable.

  • Graphic data types are partially compatible with DBCLOB locators. You can perform the following assignments:
    • Assign a value in a DBCLOB locator to a GRAPHIC or VARGRAPHIC column
    • Use a SELECT INTO statement to assign a GRAPHIC or VARGRAPHIC column to a DBCLOB locator host variable.
    • Assign a GRAPHIC or VARGRAPHIC output parameter from a user-defined function or stored procedure to a DBCLOB locator host variable.
    • Use a SET assignment statement to assign a GRAPHIC or VARGRAPHIC transition variable to a DBCLOB locator host variable.
    • Use a VALUES INTO statement to assign a GRAPHIC or VARGRAPHIC function parameter to a DBCLOB locator host variable.
    However, you cannot use a FETCH statement to assign a value in a GRAPHIC or VARGRAPHIC column to a DBCLOB locator host variable.
  • Binary data types are compatible with each other.
  • Binary data types are partially compatible with BLOB locators. You can perform the following assignments:
    • Assign a value in a BLOB locator to a BINARY or VARBINARY column.
    • Use a SELECT INTO statement to assign a BINARY or VARBINARY column to a BLOB locator host variable.
    • Assign a BINARY or VARBINARY output parameter from a user-defined function or stored procedure to a BLOB locator host variable.
    • Use a SET assignment statement to assign a BINARY or VARBINARY transition variable to a BLOB locator host variable.
    • Use a VALUES INTO statement to assign a BINARY or VARBINARY function parameter to a BLOB locator host variable.
    However, you cannot use a FETCH statement to assign a value in a BINARY or VARBINARY column to a BLOB locator host variable.
  • Fortran: A BINARY, VARBINARY, or BLOB column or BLOB locator is compatible only with a BLOB host variable.
  • C: For varying-length BIT data, use BINARY. Some C string manipulation functions process NUL-terminated strings and other functions process strings that are not NUL-terminated. The C string manipulation functions that process NUL-terminated strings cannot handle bit data because these functions might misinterpret a NUL character to be a NUL-terminator.
  • Datetime data types are compatible with character host variables.

    Assembler: A DATE, TIME, or TIMESTAMP column is compatible with a fixed-length or varying-length assembler character host variable.

    C/C++: A DATE, TIME, or TIMESTAMP column is compatible with a single-character, NUL-terminated, or VARCHAR structured form of a C character host variable.

    COBOL: A DATE, TIME, or TIMESTAMP column is compatible with a fixed-length or varying length COBOL character host variable.

    Fortran: A DATE, TIME, or TIMESTAMP column is compatible with a Fortran character host variable.

    PL/I: A DATE, TIME, or TIMESTAMP column is compatible with a fixed-length or varying-length PL/I character host variable.

  • The ROWID column is compatible only with a ROWID host variable.
  • A host variable is compatible with a distinct type if the host variable type is compatible with the source type of the distinct type.
  • XML columns are compatible with the XML host variable types, character types, and binary string types.
    Recommendation: Use the XML host variable types for data from XML columns.
  • Assembler:You can assign LOB data to a file reference variable (BLOB_FILE, CLOB_FILE, and DBCLOB_FILE).

When necessary, DB2® automatically converts a fixed-length string to a varying-length string, or a varying-length string to a fixed-length string.