Database column data types considerations

Several column data types are available for you to use when we are creating new database table or columns. We can use any of the following data types for our custom database columns when you need to create a new database table or column. IBM recommends to use only the following types of column data types when we are creating a custom table. Other types of columns are not supported.Notes:

• The descriptions of the following data types use DB2 terminology.

• All negative primary key values of a table, including 0, are reserved for IBM internal use.

BIGINT

Columns with this data type can include a 64-bit signed integer that has a range from -9223372036854775807 to 9223372036854775807. In comparison, columns with the INTEGER data type can include integer values that are only half the size.

INTEGER

Columns with this data type can include a 32-bit signed integer that has a range from -2147483647 to 2147483647. In general, use the INTEGER data type instead of the BIGINT data type as the default finite numeric data type unless you have a strong business reason to use the BIGINT data type. For example, as the data type for columns that include a system generated key. By using the INTEGER data type instead of the BIGINT data type, the database performance can be improved.

Note: The use of the SMALLINT or SHORT data types is discouraged since these data types map to a non-object Java data type. The use of these data types can cause problems in some enterprise bean object instantiation.

TIMESTAMP

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

CHAR

Columns with this data type include a fixed-length character string of length INTEGER, which can range 1 - 254 characters. If the length specification is omitted, a length of one character is assumed. Since CHAR is a fixed-length database column, any unused trailing character spaces are changed into white spaces.

Note: Unless you need to the CHAR data type for performance reasons, do not use this data type since it is not flexible and the length cannot be changed later. In general, use the CHAR data type for improved performance when the string values to be stored in the column are less that 64 characters long and are regularly retrieved or updated.

CHAR() FOR BIT DATA

This data type indicates that the contents of the column are to be treated as bit (binary) data. During data exchange with other systems, code page conversions are not performed. Comparisons are done in binary, irrespective of the database collating sequence.

DATE

Columns with this data type include a three-part value (year, month, and day) that designates a date. For example, 2016-01-01 represents January 1, 2016.

(Oracle) Columns with this data type include a six-part value that includes the time (year, month, day, hour, minute, and seconds). For example, 2016-01-01 12.00.00 represents January 1, 2016, 12:00 PM.

FLOAT

Columns with this data type include a double-precision floating-point number. FLOAT is a synonym for DOUBLE.

DOUBLE

Columns with this data type include a double-precision floating-point number. A double-precision floating-point number is a 64-bit approximation of a real number. The number can be zero or can range from -1.79769E+308 to -2.225E-307, or from 2.225E-307 to 1.79769E+308.

SMALLINT

Columns with this data type include a 2-byte integer. The range of small integers is -32 768 to 32 767.

VARCHAR

Columns with this data type include a variable-length character string of the maximum length integer. The length of the string can range 1 - 32672. Unlike columns with the data type CHAR, the column data is not stored along with the table. Instead, the value of columns with the data type VARCHAR is internally represented as a reference pointer. The length of a VARCHAR column can be changed at any time.

CLOB

Columns with this data type include a variable-length character string that we can use when the column length needs to exceed the 32 KB limit of the VARCHAR data type. The length of a CLOB object can reach 1 GB without modifying the database configuration. Text data stored as CLOB is converted when the data is moved among different systems.

BLOB

Columns with this data type include a variable-length binary character string that stores unstructured data in the database. BLOB objects can store up to 4 GB of binary data. For performance reasons, avoid the use of the BLOB data type.

DECIMAL(20,5)

Columns with this data type include most fixed decimal point numbers, such as currency units. For other floating point decimal numbers, use the FLOAT data type instead.

Data type differences between database types

The following table indicates the equivalent column data types for the different database types that are supported by WebSphere Commerce.

UDB DB2	Oracle	Size limit for WebSphere Commerce
BLOB()	BLOB	4 GB
TIMESTAMP	TIMESTAMP	-
INTEGER	INTEGER	2,147,483,647
DECIMAL(,)	DECIMAL(,)	-
BIGINT	NUMBER	9,223,372 x 1012
FLOAT	NUMBER	-
CHAR()	VARCHAR2()	254
CHAR() FOR BIT DATA	RAW()	32,673
VARCHAR()	VARCHAR2()	32,672
CLOB()	CLOB	(DB2) 1 GB (Oracle) 4 GB
DATE	DATE
DOUBLE	NUMBER
SMALLINT	SMALLINT

Related concepts
WebSphere Commerce database schema