Reference > Database > WebSphere Commerce database tables database schemacolumn data type considerations
Database column data type considerations
You can use several column data types 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 the string column is less then 64 characters in length and is regularly retrieved or updated, use CHAR instead for better performance.
- CHAR() FOR BIT DATA
- Specifies 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
- A three-part value (year, month, and day), i.e. 2005-01-01 represents January 1, 2005. In Oracle DATE has not only date but also time (Year, month, day, hour, minute, and seconds), i.e. 2005-01-01 12.00.00 represents January 1, 2005, 12:00 PM.
- FLOAT
- FLOAT is double-precision floating-point by default. FLOAT is a synonym for DOUBLE.
- DOUBLE
- A double-precision float-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
- A two-byte integer with a precision of 5 digits. The range of small integers is -32 768 to 32 767.
- VARCHAR
- 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.