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 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, 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.
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 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 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 |
|
| DATE | DATE | |
| DOUBLE | NUMBER | |
| SMALLINT | SMALLINT |
1 GB