DB2 10.5 for Linux, UNIX, and Windows

Data types that map to database data types in Java applications

To write efficient JDBC and SQLJ programs, you need to use the best mappings between Java™ data types and table column data types.

The following tables summarize the mappings of Java data types to JDBC and database data types for a DB2® for Linux, UNIX, and Windows, DB2 for z/OS®, or IBM® Informix® system.

Data types for updating table columns

The following table summarizes the mappings of Java data types to database data types for PreparedStatement.setXXX or ResultSet.updateXXX methods in JDBC programs, and for input host expressions in SQLJ programs. When more than one Java data type is listed, the first data type is the recommended data type.

Table 1. Mappings of Java data types to database server data types for updating database tables
Java data type Database data type
short, java.lang.Short SMALLINT
boolean1, byte1, java.lang.Boolean, java.lang.Byte SMALLINT
int, java.lang.Integer INTEGER
long, java.lang.Long BIGINT12
java.math.BigInteger BIGINT11
java.math.BigInteger CHAR(n)11,5
float, java.lang.Float REAL
double, java.lang.Double DOUBLE
java.math.BigDecimal DECIMAL(p,s)2
java.math.BigDecimal DECFLOAT(n)3,4
java.lang.String CHAR(n)5
java.lang.String GRAPHIC(m)6
java.lang.String VARCHAR(n)7
java.lang.String VARGRAPHIC(m)8
java.lang.String CLOB9
java.lang.String XML10
byte[] CHAR(n) FOR BIT DATA5
byte[] VARCHAR(n) FOR BIT DATA7
byte[] BINARY(n)5, 13
byte[] VARBINARY(n)7, 13
byte[] BLOB9
byte[] ROWID
byte[] XML10
java.sql.Blob BLOB
java.sql.Blob XML10
java.sql.Clob CLOB
java.sql.Clob DBCLOB9
java.sql.Clob XML10
java.sql.Date DATE
java.sql.Time TIME
java.sql.Timestamp TIMESTAMP, TIMESTAMP(p), TIMESTAMP WITH TIME ZONE, TIMESTAMP(p) WITH TIME ZONE14,15
java.io.ByteArrayInputStream BLOB
java.io.StringReader CLOB
java.io.ByteArrayInputStream CLOB
java.io.InputStream XML10
com.ibm.db2.jcc.DB2RowID (deprecated) ROWID
java.sql.RowId ROWID
com.ibm.db2.jcc.DB2Xml (deprecated) XML10
java.sql.SQLXML XML10
java.util.Date CHAR(n)11,5
java.util.Date VARCHAR(n)11,5
java.util.Date DATE11
java.util.Date TIME11
java.util.Date TIMESTAMP, TIMESTAMP(p), TIMESTAMP WITH TIME ZONE, TIMESTAMP(p) WITH TIME ZONE11,14,15
java.util.Calendar CHAR(n)11,5
java.util.Calendar VARCHAR(n)11,5
java.util.Calendar DATE11
java.util.Calendar TIME11
java.util.Calendar TIMESTAMP, TIMESTAMP(p), TIMESTAMP WITH TIME ZONE, TIMESTAMP(p) WITH TIME ZONE11,14,15
Notes:
  1. For column updates, the data server has no exact equivalent for the Java boolean or byte data types, but the best fit is SMALLINT.
  2. p is the decimal precision and s is the scale of the table column.

    You should design financial applications so that java.math.BigDecimal columns map to DECIMAL columns. If you know the precision and scale of a DECIMAL column, updating data in the DECIMAL column with data in a java.math.BigDecimal variable results in better performance than using other combinations of data types.

  3. n=16 or n=34.
  4. DECFLOAT is valid for connections to DB2 Version 9.1 for z/OS, DB2 V9.5 for Linux, UNIX, and Windows, or DB2 for i V6R1, or later database servers. Use of DECFLOAT requires the SDK for Java Version 5 (1.5) or later.
  5. n<=254.
  6. m<=127.
  7. n<=32672.
  8. m<=16336.
  9. This mapping is valid only if the database server can determine the data type of the column.
  10. XML is valid for connections to DB2 Version 9.1 for z/OS or later database servers or DB2 V9.1 for Linux, UNIX, and Windows or later database servers.
  11. This mapping is valid only for IBM Data Server Driver for JDBC and SQLJ version 4.13 or later.
  12. BIGINT is valid for connections to DB2 Version 9.1 for z/OS or later database servers, DB2 V9.1 for Linux, UNIX, and Windows or later database servers, and all supported DB2 for i database servers.
  13. BINARY and VARBINARY are valid for connections to DB2 Version 9.1 for z/OS or later database servers or DB2 for i5/OS™ V5R3 and later database servers.
  14. p indicates the timestamp precision, which is the number of digits in the fractional part of the timestamp. 0<=p<=12. The default is 6. TIMESTAMP(p) is supported for connections to DB2 for Linux, UNIX, and Windows V9.7 and later and DB2 for z/OS V10 and later only.
  15. The WITH TIME ZONE clause is supported for connections to DB2 for z/OS V10 and later only.

Data types for retrieval from table columns

The following table summarizes the mappings of DB2 or IBM Informix data types to Java data types for ResultSet.getXXX methods in JDBC programs, and for iterators in SQLJ programs. This table does not list Java numeric wrapper object types, which are retrieved using ResultSet.getObject.

Table 2. Mappings of database server data types to Java data types for retrieving data from database server tables
SQL data type Recommended Java data type or Java object type Other supported Java data types
SMALLINT short byte, int, long, float, double, java.math.BigDecimal, boolean, java.lang.String
INTEGER int short, byte, long, float, double, java.math.BigDecimal, boolean, java.lang.String
BIGINT5 long int, short, byte, float, double, java.math.BigDecimal, boolean, java.lang.String
DECIMAL(p,s) or NUMERIC(p,s) java.math.BigDecimal long, int, short, byte, float, double, boolean, java.lang.String
DECFLOAT(n)1,2 java.math.BigDecimal long, int, short, byte, float, double, java.math.BigDecimal, boolean, java.lang.String
REAL float long, int, short, byte, double, java.math.BigDecimal, boolean, java.lang.String
DOUBLE double long, int, short, byte, float, java.math.BigDecimal, boolean, java.lang.String
CHAR(n) java.lang.String long, int, short, byte, float, double, java.math.BigDecimal, boolean, java.sql.Date, java.sql.Time, java.sql.Timestamp, java.io.InputStream, java.io.Reader
VARCHAR(n) java.lang.String long, int, short, byte, float, double, java.math.BigDecimal, boolean, java.sql.Date, java.sql.Time, java.sql.Timestamp, java.io.InputStream, java.io.Reader
CHAR(n) FOR BIT DATA byte[] java.lang.String, java.io.InputStream, java.io.Reader
VARCHAR(n) FOR BIT DATA byte[] java.lang.String, java.io.InputStream, java.io.Reader
BINARY(n)6 byte[] None
VARBINARY(n)6 byte[] None
GRAPHIC(m) java.lang.String long, int, short, byte, float, double, java.math.BigDecimal, boolean, java.sql.Date, java.sql.Time, java.sql.Timestamp, java.io.InputStream, java.io.Reader
VARGRAPHIC(m) java.lang.String long, int, short, byte, float, double, java.math.BigDecimal, boolean, java.sql.Date, java.sql.Time, java.sql.Timestamp, java.io.InputStream, java.io.Reader
CLOB(n) java.sql.Clob java.lang.String
BLOB(n) java.sql.Blob byte[]3
DBCLOB(m) No exact equivalent. Use java.sql.Clob.  
ROWID java.sql.RowId byte[], com.ibm.db2.jcc.DB2RowID (deprecated)
XML4 java.sql.SQLXML byte[], java.lang.String, java.io.InputStream, java.io.Reader
DATE java.sql.Date java.sql.String, java.sql.Timestamp
TIME java.sql.Time java.sql.String, java.sql.Timestamp
TIMESTAMP, TIMESTAMP(p), TIMESTAMP WITH TIME ZONE, TIMESTAMP(p) WITH TIME ZONE7,8 java.sql.Timestamp java.sql.String, java.sql.Date, java.sql.Time, java.sql.Timestamp
Notes:
  1. n=16 or n=34.
  2. DECFLOAT is valid for connections to DB2 Version 9.1 for z/OS, DB2 V9.5 for Linux, UNIX, and Windows, or DB2 for i V6R1, or later database servers. Use of DECFLOAT requires the SDK for Java Version 5 (1.5) or later.
  3. This mapping is valid only if the database server can determine the data type of the column.
  4. XML is valid for connections to DB2 Version 9.1 for z/OS or later database servers or DB2 V9.1 for Linux, UNIX, and Windows or later database servers.
  5. BIGINT is valid for connections to DB2 Version 9.1 for z/OS or later database servers, DB2 V9.1 for Linux, UNIX, and Windows or later database servers, and all supported DB2 for i database servers.
  6. BINARY and VARBINARY are valid for connections to DB2 Version 9.1 for z/OS or later database servers or DB2 for i5/OS V5R3 and later database servers.
  7. p indicates the timestamp precision, which is the number of digits in the fractional part of the timestamp. 0<=p<=12. The default is 6. TIMESTAMP(p) is supported for connections to DB2 for Linux, UNIX, and Windows V9.7 and later and DB2 for z/OS V10 and later only.
  8. The WITH TIME ZONE clause is supported for connections to DB2 for z/OS V10 and later only.

Data types for calling stored procedures and user-defined functions

The following table summarizes mappings of Java data types to JDBC data types and DB2 or IBM Informix data types for calling user-defined function and stored procedure parameters. The mappings of Java data types to JDBC data types are for CallableStatement.registerOutParameter methods in JDBC programs. The mappings of Java data types to database server data types are for parameters in stored procedure or user-defined function invocations.

If more than one Java data type is listed in the following table, the first data type is the recommended data type.

Table 3. Mappings of Java, JDBC, and SQL data types for calling stored procedures and user-defined functions
Java data type JDBC data type SQL data type
boolean3, java.lang.Boolean BOOLEAN BOOLEAN1,2
boolean3, java.lang.Boolean BIT SMALLINT
byte3, java.lang.Byte TINYINT SMALLINT
short, java.lang.Short SMALLINT SMALLINT
int, java.lang.Integer INTEGER INTEGER
long, java.lang.Long BIGINT BIGINT7
float, java.lang.Float REAL REAL
float, java.lang.Float FLOAT REAL
double, java.lang.Double DOUBLE DOUBLE
java.math.BigDecimal NUMERIC DECIMAL
java.math.BigDecimal DECIMAL DECIMAL
java.math.BigDecimal java.types.OTHER DECFLOATn4
java.math.BigDecimal com.ibm.db2.jcc.DB2Types.DECFLOAT DECFLOATn4
java.lang.String CHAR CHAR
java.lang.String CHAR GRAPHIC
java.lang.String VARCHAR VARCHAR
java.lang.String VARCHAR VARGRAPHIC
java.lang.String LONGVARCHAR VARCHAR
java.lang.String VARCHAR CLOB
java.lang.String LONGVARCHAR CLOB
java.lang.String CLOB CLOB
byte[] BINARY CHAR FOR BIT DATA
byte[] VARBINARY VARCHAR FOR BIT DATA
byte[] BINARY BINARY6
byte[] VARBINARY VARBINARY6
byte[] LONGVARBINARY VARCHAR FOR BIT DATA
byte[] VARBINARY BLOB5
byte[] LONGVARBINARY BLOB5
java.sql.Date DATE DATE
java.sql.Time TIME TIME
java.sql.Timestamp TIMESTAMP TIMESTAMP, TIMESTAMP(p), TIMESTAMP WITH TIME ZONE, TIMESTAMP(p) WITH TIME ZONE8,9
java.sql.Blob BLOB BLOB
java.sql.Clob CLOB CLOB
java.sql.Clob CLOB DBCLOB
java.io.ByteArrayInputStream None BLOB
java.io.StringReader None CLOB
java.io.ByteArrayInputStream None CLOB
com.ibm.db2.jcc.DB2RowID (deprecated) com.ibm.db2.jcc.DB2Types.ROWID ROWID
java.sql.RowId java.sql.Types.ROWID ROWID
com.ibm.db2.jcc.DB2Xml (deprecated) com.ibm.db2.jcc.DB2Types.XML XML AS CLOB
java.sql.SQLXML java.sql.Types.SQLXML XML
java.sql.SQLXML java.sql.Types.SQLXML XML AS CLOB
java.sql.Array java.sql.Types.ARRAY ARRAY2
java.sql.Struct java.sql.Types.STRUCT ROW1,2
java.sql.ResultSet com.ibm.db2.jcc.DB2Types.CURSOR CURSOR type2
Notes:
  1. This parameter data type is supported for IBM Data Server Driver for JDBC and SQLJ type 4 connectivity to DB2 for Linux, UNIX, and Windows only.
  2. This parameter data type is supported for stored procedure calls only.
  3. A stored procedure or user-defined function that is defined with a SMALLINT parameter can be invoked with a boolean or byte parameter. However, this is not recommended.
  4. DECFLOAT parameters in Java routines are valid only for connections to DB2 Version 9.1 for z/OS or later database servers. DECFLOAT parameters in Java routines are not supported for connections to for Linux, UNIX, and Windows or DB2 for i. Use of DECFLOAT requires the SDK for Java Version 5 (1.5) or later.
  5. This mapping is valid only if the database server can determine the data type of the column.
  6. BINARY and VARBINARY are valid for connections to DB2 Version 9.1 for z/OS or later database servers or DB2 for i5/OS V5R3 and later database servers.
  7. BIGINT is valid for connections to DB2 Version 9.1 for z/OS or later database servers, DB2 V9.1 for Linux, UNIX, and Windows or later database servers, and all supported DB2 for i database servers.
  8. p indicates the timestamp precision, which is the number of digits in the fractional part of the timestamp. 0<=p<=12. The default is 6. TIMESTAMP(p) is supported for connections to DB2 for Linux, UNIX, and Windows V9.7 and later and DB2 for z/OS V10 and later only.
  9. The WITH TIME ZONE clause is supported for connections to DB2 for z/OS V10 and later only.

Data types in Java stored procedures and user-defined functions

The following table summarizes mappings of the SQL parameter data types in a CREATE PROCEDURE or CREATE FUNCTION statement to the data types in the corresponding Java stored procedure or user-defined function method.

For DB2 for Linux, UNIX, and Windows, if more than one Java data type is listed for an SQL data type, only the first Java data type is valid.

For DB2 for z/OS, if more than one Java data type is listed, and you use a data type other than the first data type as a method parameter, you need to include a method signature in the EXTERNAL clause of your CREATE PROCEDURE or CREATE FUNCTION statement that specifies the Java data types of the method parameters.

Table 4. Mappings of SQL data types in a CREATE PROCEDURE or CREATE FUNCTION statement to data types in the corresponding Java stored procedure or user-defined function program
SQL data type in CREATE PROCEDURE or CREATE FUNCTION Data type in Java stored procedure or user-defined function method2
SMALLINT short, java.lang.Integer
BOOLEAN1 boolean
INTEGER int, java.lang.Integer
BIGINT3 long, java.lang.Long
REAL float, java.lang.Float
DOUBLE double, java.lang.Double
DECIMAL java.math.BigDecimal
DECFLOAT4 java.math.BigDecimal
CHAR java.lang.String
VARCHAR java.lang.String
CHAR FOR BIT DATA byte[]
VARCHAR FOR BIT DATA byte[]
BINARY5 byte[]
VARBINARY5 byte[]
DATE java.sql.Date
TIME java.sql.Time
TIMESTAMP, TIMESTAMP(p), TIMESTAMP WITH TIME ZONE, TIMESTAMP(p) WITH TIME ZONE6,7 java.sql.Timestamp
BLOB java.sql.Blob
CLOB java.sql.Clob
DBCLOB java.sql.Clob
ROWID java.sql.Types.ROWID
ARRAY1 java.sql.Array
ROW1 java.sql.Struct
XML AS CLOB java.sql.Types.SQLXML
Notes:
  1. This parameter data type is supported for stored procedures only.
  2. For a stored procedure or user-defined function on a DB2 for Linux, UNIX, and Windows server, only the first data type is valid.
  3. BIGINT is valid for connections to DB2 Version 9.1 for z/OS or later database servers or DB2 V9.1 for Linux, UNIX, and Windows or later database servers.
  4. DECFLOAT parameters in Java routines are valid only for connections to DB2 Version 9.1 for z/OS or later database servers. DECFLOAT parameters in Java routines are not supported for connections to for Linux, UNIX, and Windows or DB2 for i. Use of DECFLOAT requires the SDK for Java Version 5 (1.5) or later.
  5. BINARY and VARBINARY are valid for connections to DB2 Version 9.1 for z/OS or later database servers or DB2 for i5/OS V5R3 and later database servers.
  6. p indicates the timestamp precision, which is the number of digits in the fractional part of the timestamp. 0<=p<=12. The default is 6. TIMESTAMP(p) is supported for connections to DB2 for Linux, UNIX, and Windows V9.7 and later and DB2 for z/OS V10 and later only.
  7. The WITH TIME ZONE clause is supported for connections to DB2 for z/OS V10 and later only.