Database column data types considerations

Several column data types are available for you to use when you are creating new database table or columns.

You can use any of the following data types for your custom database columns when you need to create a new database table or column. You are recommended to use only the following types of column data types when you 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 HCL 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, your 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.

OracleColumns 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 you 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 that is 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 HCL Commerce.
UDB DB2 Oracle Size limit for HCL 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
  • DB21 GB
  • Oracle4 GB
DATE DATE
DOUBLE NUMBER
SMALLINT SMALLINT