Database column data type considerations

This topic introduces column data types that can be used when creating new tables. The descriptions of the various data types use DB2 terminology.

BIGINT

This is a 64-bit signed integer that has a range from -9223372036854775807 to 9223372036854775807. Contrast this to INTEGER, which is only half the size of BIGINT.

INTEGER

This is a 32-bit signed integer that has a range from -2147483647 to 2147483647. In general, INTEGER should be the default finite numeric data type, instead of BIGINT. Unless there is a strong business reason for using BIGINT, for performance reasons it is better to use INTEGER as the numeric data type. A common user of the BIGINT data type is a system generated key.

The use of either SMALLINT or SHORT data types is strongly discouraged because these data types are mapped to a non-object Java data type and these non-object data types will cause problems in some enterprise bean object instantiations.

TIMESTAMP

This is a seven-part value (year, month, day, hour, minute, second, and microsecond) that designates a date and time, except that the time includes a fractional specification of microseconds. The internal representation of a timestamp is a string of 10 bytes, each of which consists of 2 packed decimal digits. The first 4 bytes represent the date, the next 3 bytes the time, and the last 3 bytes the microseconds.

CHAR

This is a fixed-length character string of length INTEGER, which may range from 1 to 254 characters. If the length specification is omitted, a length of 1 character is assumed. Since CHAR is a fixed length database column, any unused trailing character spaces are changed into white spaces. Unless for performance reasons, it is not recommended to use CHAR data type because CHAR is not flexible and length cannot be changed at a later time. As a rule of thumb, if your string column is less then 64 characters in length and is regularly retrieved or updated, use CHAR instead for better performance.

VARCHAR

This is a variable-length character string of maximum length integer, which may range from 1 to 32672. However, unlike CHAR where the column data is stored along with the table, VARCHAR is internally represented as a reference pointer inside a database page. Therefore, length of a VARCHAR column can be changed at any time after creation.

CLOB

This is another variable-length character string that can be used if the length of the column needs to exceed the 32KB limit of LONG VARCHAR. The length of a CLOB object can reach 1 GB without modifying the database configuration. Text data that is stored as CLOB is converted appropriately when moving among different systems.

BLOB

This is a variable-length binary character string that stores unstructured data in the database. BLOB objects can store up to 4 GB of binary data. In general, you should avoid using BLOB as a column data type, unless it is absolutely necessary. In terms of performance, a BLOB object is considered to be one of the most expensive objects in any database.

DECIMAL(20,5)

This data type is specially defined to be used for most fixed decimal point numbers, such as currency units. For other floating point decimal numbers, FLOAT can be used instead.
Related reference