Db2® data types
- Character strings (alphanumeric)
- A sequence of bytes where the length of the string is the number
of bytes in the sequence.
- CHAR
- Fixed-length character string.
- VARCHAR
- Varying-length character string.
- GRAPHIC
- Fixed-length graphic string.
- VARGRAPHIC
- Varying-length graphic string.
- BINARY
- Fixed-length binary string.
- VARBINARY
- Varying-length binary string.
- Numeric
- Decimal, binary integer, and floating point data types containing
a sign and a precision.
- DECIMAL or NUMERIC
- A packed decimal number with an implied decimal point.
- DECFLOAT
- A decimal floating-point number.
- SMALLINT
- A binary integer with a precision of 15 bits.
- INTEGER
- A binary integer with a precision of 31 bits.
- BIGINT
- A binary integer with a precision of 63 bits.
- REAL
- A single precision floating-point number (32 bits).
- DOUBLE, DOUBLE PRECISION, or FLOAT
- A double precision floating-point number (64 bits).
- Row identifier
- Supports queries that navigate directly to a row in the table.
- ROWID
- A unique value maintained permanently by Db2®.
- Datetime
- These data types represent a point in time.
- DATE
- Internally
to Db2®, the date is represented
as a 4 byte field, with each byte containing 2 packed decimal digits
stored in the format yyyymmdd.
Externally, as returned by an SQL SELECT statement, the date is represented by a character field. The format of the string is dependant on the DATE parameter in the Db2® DSNHDECP installation module (the Db2® installation option specified on the installation panel DSNTIP4). Date and Time field formats shows valid Db2® date formats. The length of the date field is 10 bytes, except where the Db2® Installation Date format is LOCAL, in which case the length is installation-dependent and in the range 10 to 254 bytes. The date value must be in the range 0001-01-01 to 9999-12-31 (ISO format).
- TIME
- Internally
to Db2® the timestamp is represented
as a 7-15 byte field. with each byte containing 2 packed decimal digits.
The first 4 bytes represent the date, the next 3 bytes the time, the
next 0-6 bytes the microseconds, and when a TIME ZONE is present,
2 bytes containing the time zone.
Externally, as returned by an SQL select statement, the timestamp is represented by a 19-38 byte character field. The timestamp is always returned in the format yyyy-mm-dd.hh.mm.ss(.nnnnnnnnnnnn)(sHH:MM) where:
- yyyy
- An integer in the range 1 to 9999 representing the years.
- mm
- An integer in the range 1 to 12 representing the months.
- dd
- An integer in the range 1 to 31 representing the days. The value must be appropriate for the month, including leap years.
- hh
- An integer in the range 0 to 24 representing the hours.
- mm
- An integer in the range 0 to 59 representing the minutes.
- ss
- An integer in the range 0 to 59 representing the seconds.
The remaining parts of the TIMESTAMP (fractional part of the seconds value and TIME ZONE) are optional:
- nnnnnnnnnnnn
- An integer in the range 0 to 999999999999 representing the fractional part of the seconds. The number of digits can vary from 0-12 inclusive, depending on the precision of the TIMESTAMP.
- sHH
- A signed integer in the range -12 to +14 inclusive. -12 represents 12 hours behind Greenwich mean time, +14 represents 14 hours ahead.
- MM
- An integer in the range 00-59, representing the minutes component of the time zone. Internally to Db2®, the time is represented as a 3 byte field with each byte containing 2 packed decimal digits stored in the format hhmmss.
Table 1. Date and Time field formats Format Name Abbreviation Date format Time format International Standard Organization ISO yyyy-mm-dd hh.mm.ss IBM® USA standard USA mm/dd/yyyy hh.mm AM or PM IBM® European standard EUR dd.mm.yyyy hh.mm.ss Japanese industrial standard JIS yyyy-mm-dd hh:mm:ss Installation defined LOCAL Installation-defined format Installation-defined format
For a full description of Db2® data types, see the Db2 UDB for z/OS SQL Reference.