SQL Getting Started

Data Types

Data types define acceptable values for constants, columns, host variables, functions, expressions and special registers. This section describes the data types referred to in the examples. For a full list and complete description of other data types refer to the SQL Reference.

Character String

A character string is a sequence of bytes. The length of the string is the number of bytes in the sequence. If the length is zero, the value is called the empty string.

Fixed-Length Character String

CHAR(x) is a fixed length string. The length attribute x must be between 1 and 254, inclusive.

Varying-Length Character String

Varying-length character strings are of three types: VARCHAR, LONG VARCHAR, and CLOB. VARCHAR(x) types are varying-length strings, so a string of length 9 can be inserted into VARCHAR(15) but will still have a string length of 9. See Large Objects (LOBs) for details on CLOB.

Graphic String

A graphic string is a sequence of double-byte character data.

Fixed-Length Graphic String

GRAPHIC(x) is a fixed length string. The length attribute x must be between 1 and 127, inclusive.

Varying-Length Graphic String

Varying-length graphic strings are of three types: VARGRAPHIC, LONG VARGRAPHIC, and DBCLOB. See Large Objects (LOBs) for details on DBCLOB.

Binary String

A binary string is a sequence of bytes. It is used to hold nontraditional data such as pictures. Binary Large OBject (BLOB) is a binary string. See Large Objects (LOBs) for more information.

Numbers

All numbers have a sign and a precision. The precision is the number of bits or digits excluding the sign.

SMALLINT

A SMALLINT (small integer) is a two byte integer with a precision of 5 digits.

INTEGER

An INTEGER (large integer) is a four byte integer with a precision of 10 digits.

REAL

A REAL (single-precision floating-point number) is a 32 bit approximation of a real number.

DOUBLE

A DOUBLE (double-precision floating-point number) is a 64 bit approximation of a real number. DOUBLE is also referred to as FLOAT.

DECIMAL(p,s)

A DECIMAL is a decimal number. The position of the decimal point is determined by the precision (p) and the scale (s) of the number. Precision is the total number of digits and has to be less than 32. Scale is the number of digits in the fractional part and is always smaller than or equal to the value of precision. The decimal value defaults to precision of 5 and scale of 0 if precision and scale are not specified.

Datetime Values

Datetime values are representations of dates, times, and timestamps. Datetime values can be used in certain arithmetic and string operations and are compatible with certain strings, however they are neither strings nor numbers. ¹

Date

A date is a three-part value (year, month, and day).

Time

A time is a three-part value (hour, minute, and second) designating a time of day using a 24-hour clock.

Timestamp

A timestamp is a seven-part value (year, month, day, hour, minute, second, and microsecond) designating a date and time.

The null value is a special value that is distinct from all non-null values. It means the absence of any other value for that column in the row. The null value exists for all data types.

The following table highlights characteristics of data types used in the examples. All numeric data types are defined in a certain range. The range of numeric data types is also included in this table. You can use this table as a quick reference for proper data type usage.

Data Type	Type	Characteristic	Example or Range
CHAR(15)	fixed-length character string	Maximum length of 254	'Sunny day      '
VARCHAR(15)	varying-length character string	Maximum length of 4000	'Sunny day'
SMALLINT	number	2 bytes in length precision of 5 digits	range is -32768 to 32767
INTEGER	number	4 bytes in length precision of 10 digits	range is -2147483648 to 2147483647
REAL	number	single-precision floating point 32 bit approximation	range is -3.402E+38 to -1.175E-37 or 1.175E-37 to -3.402E+38 or zero
DOUBLE	number	double-precision floating point 64 bit approximation	range is -1.79769E+308 to -2.225E-307 or 2.225E-307 to 1.79769E+308 or zero
DECIMAL(5,2)	number	precision is 5 scale is 2	range is -10**31+1 to 10**31-1
DATE	datetime	three-part value	1991-10-27
TIME	datetime	three-part value	13.30.05
TIMESTAMP	datetime	seven-part value	1991-10-27-13.30.05.000000

See the data type compatibility table in the SQL Reference for more information.

¹

In this book we refer to ISO representations of datetime values.

[ DB2 List of Books | Search the DB2 Books ]