DB2 Numeric, String, Date, Time Data types

Here is a discussion on DB2 supported data types. Explained each for ready reference.


Numeric Data types:

SMALLINT: As the name suggests, the smallest numeric data type that DB2 supports. These values use 2 bytes of storage (16 bits) and are interpreted as 2s-complement signed numbers, providing a range between -32768 and 32767.

INTEGER: A 32-bit signed number. The range of permissible values is -2147483648 to 2147483647.

BIGINT: The biggest form of integer supported by DB2. These values are 64 bits, providing a huge range: from -9223372036854775808 to +9223372036854775807. If your integers fall outside that range, I’d love to hear what data you’re modelling!

REAL: The first of the noninteger types; floating-point numbers with single precision. They provide an approximation to 32 bits for any number from the extremely small positive or negative 1.175E-37, to the extremely large positive or negative 3.402E+38. Zero is stored precisely.

DOUBLE/FLOAT: Using 64 bits to approximate nonintegers, these values provide for double-precision floating point numbers as well as exact zero values. The numeric range supported is ±1.175E-37 to ±3.402E+38.

DECIMAL/NUMERIC: Used for storing exact noninteger values so that no data is lost in the precision rounding that other floating point types encounter. These types are specified with a scale (the number of digits before the decimal point) and a precision (the number of decimal places). They use a packed storage notation. The scale must at least be 1 (i.e., at least the first digit in the packed storage is for the scale), and the precision must be no more than 31 digits. In practice, this means that values in the range ±1031 can be stored.

DECFLOAT: New to DB2, this is a newly introduced variant of the DECIMAL type. It does not differ in semantics or exactness. This type was created to allow explicit use of the new floating-point hardware IBM introduced in the POWER6 CPU. Unless you’re planning to use DB2 on that platform, you will probably never use this data type.

String Data Types

CHAR: This fixed-length data type allows you to store up to 254 bytes as a string, depending on the length you specify at the time of definition. CHAR data types pad any unused space in a string up to that length, so if you declare a CHAR value of 50 bytes, but only store the words “Hello World”, DB2 pads that value with 39 spaces for storage but not for comparison purposes. That comparison part sometimes catches people out because it is more friendly behavior than you might find in other databases. DB2 always ignores trailing spaces in CHAR and VARCHAR columns. Your application will still fetch these trailing spaces when retrieving the data, so it’s best to be mindful of this when working with that data in development languages such as Java or C#.

VARCHAR: This variable-length data type allows you to store up to 32672 bytes as a string, depending on the length you specify when defining the column or variable. However, the size you specify must be able to fit on one database page when being stored by DB2, and page sizes range from 4KB to 32KB. but there’s a small overhead to each page, so in practice your VARCHAR types are limited to 4005 bytes on 4KB pages, 8101 bytes on 8KB pages, and so forth—up to the 32672-byte size limit on 32KB pages. Unlike a CHAR, VARCHAR doesn’t pad your data with spaces to fill unused bytes.

LONG VARCHAR: This variable-length data type allows you to store up to 32700 bytes as a string, depending on the length you specify when defining the column or variable. It is only slightly longer than a VARCHAR, so you might wonder why the effort of an entirely separate data type. The short explanation is that a standard VARCHAR has historically been able to hold much less data. It is also stored on disk in a way not affected by the page size limitation of VARCHAR, so you might use up to the full length regardless of your page size. A limitation of a LONG VARCHAR is its incapability to be used with predicates such as equals, greater than, and so on. You are limited to simple tests for nullability.

CLOB: The largest of the string types, this type can store up to 2 gigabytes of textual data. There are some functional limitations on what CLOB can achieve, including the comparison limitations listed previously for LONG VARHAR.

Double Bytes and Binary Data types:

The Double-Byte (or Graphical) Strings
Double-byte string data types exist to support ideographic scripts such as Kanji and Hangol. The native data types for double-byte strings are GRAPHIC, VARGRAPHIC, LONG VARGRAPHIC, and DBCLOB, and they each can store approximately half as many double-byte characters as their single-byte equivalents (so a GRAPHIC can store up to 127 double-byte characters in comparison with a 254 single-byte characters of a CHAR).

The Binary Data Type
In a category of its own, but closely related to the other large object types, is the BLOB. No, it’s not a character in a science fiction film; it’s a binary large object that can store up to 2 gigabytes of information that will be treated as a binary stream of ones and zeros.

DATE and TIME Data types

DATE: Stores year, month, and day data. Possible values range from 01-01-0001 to 31-12-9999. DB2 also enforces strict and accurate date validation, meaning that you’ll never suffer the flaws found in other databases that allow February 30 and January 0 as dates.

TIME: Similar to the DATE data type, this data type stores hour, minute, and second data. Possible values range from 0:00:00 to 23:59:59. Special allowance is made for 24:00:00, which equates to 0:00:00. This special handling is quite sensitive, so ensure that you are dealing with the TIME data type when expecting this behavior, not DATE or TIMESTAMP.

TIMESTAMP: Designed to support International Organization for Standardization (ISO) standardized temporal information. A TIMESTAMP stores year, month, day, hour, minute, second, and microsecond. Optional support exists for time zone information as well.

Esoteric Data Types:

DATALINK: Provides special semantics for storing links to information that is housed outside of the database. Can include items such as URLs pointing to HTTP-based information on an external web server or files on a file system stored either locally to the database server or on another server.

XML: Not to be confused with the pureXML features discussed later, it is a limited data type that exists primarily for historic reasons. It allows XML data to be input into a CLOB data field by use of a special function: XML2CLOB.

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.