DB2 Data Types


A data type tells you what kind of data can be saved in a column or in a variable, and how large the value may be. There are two categories of data types in DB2:

  • Built-in data types

  • User-defined data types

Valid DB2 Built-In Data Types and Their Value Ranges

The built-in data types are provided with DB2. DB2 supports a wide range of data types for your business need. A summary of DB2 built-in data types are shown in Figure 3.1.

Figure 3.1. DB2 built-in data types.


Note

LONG VARCHAR and LONG VARGRAPHIC data types are supported in DB2 for LUW for backward compatibility only. They are being deprecated, which means that these data types will not be supported in the future. Use VARCHAR and VARGRAPHIC instead.


DB2 for iSeries and zSeries supports the ROWID data type. A ROWID data type is one that uniquely identifies a row. A query that uses ROWID navigates directly to the row because the column implicitly contains the location of the row. When a row is inserted into a table, DB2 generates a value for the ROWID column, unless one is supplied. If it is supplied, it must be a value that was previously generated. The value of ROWID cannot be updated and does not change, even after table space reorganizations. There can only be one ROWID column in a table.

There are six numeric data types in DB2. Their precisions and value ranges are listed in Table 3.1.

Table 3.1. DB2 Built-In Numeric Data Types

Data Type

Precision (Digits)

Data Value Range

SMALLINT

5

32,768 to 32,767

INTEGER

10

2,147,483,648 to 2,147,483,647

BIGINT

19

9,223,372,036,854,775,808 to 9,223,372,036,854,775,807

DECIMAL/NUMERIC

LUW, zSeries: 31
iSeries: 63

LUW, zSeries: Any value with 31 digits or less.
iSeries: Any value with 63 digits or less.

REAL

LUW, iSeries: 24 zSeries: 21

LUW:
Smallest REAL value 3.402E+38
Largest REAL value +3.402E+38
Smallest positive REAL value +1.175E-37
Largest negative REAL value 1.175E-37
iSeries:
Smallest REAL value 3.4E+38
Largest REAL value +3.4E+38
Smallest positive REAL value +1.18E-38
Largest negative REAL value 1.18E-38
zSeries:
Smallest REAL value 7.2E+75
Largest REAL value +7.2E+75
Smallest positive REAL value +5.4E79
Largest negative REAL value 5.4E79

DOUBLE

53

LUW:
Smallest DOUBLE value 1.79769E+308
Largest DOUBLE value +1.79769E+308
Smallest positive DOUBLE value +2.225E307
Largest negative DOUBLE value 2.225E307
iSeries:
Smallest DOUBLE value 1.79E+308
Largest DOUBLE value +1.79E+308
Smallest positive DOUBLE value +2.23E308
Largest negative DOUBLE value 2.23E308
zSeries:
Smallest REAL value 7.2E+75
Largest REAL value +7.2E+75
Smallest positive REAL value +5.4E79
Largest negative REAL value 5.4E79


DB2 supports both single-byte and double-byte character strings. DB2 uses 2 bytes to represent each character in double-byte strings. Their maximum lengths are listed in Table 3.2.

Table 3.2. DB2 Built-In String Data Types

Data Type

Maximum Length

CHAR

LUW: 254 bytes
iSeries: 32,766 bytes
zSeries: 255 bytes

VARCHAR

LUW: 32,672 bytes
iSeries: 32,740 bytes
zSeries: 32,704 bytes

LONG VARCHAR (LUW only)

32,700 bytes

CLOB

2,147,483,647 bytes

GRAPHIC

LUW: 127 characters
iSeries: 16,383 characters
zSeries: 127 characters

VARGRAPHIC

LUW: 16,336 characters
iSeries: 16,370 characters
zSeries: 16,352 characters

DBCLOB

1,073,741,823 characters

BINARY (iSeries only)

32,766 bytes

VARBINARY (iSeries only)

32,740 bytes

BLOB

2,147,483,647 bytes


You can also specify a subtype for string data types. For example, CHAR and VARCHAR columns can be defined as FOR BIT DATA to store binary data. On iSeries, other subtypes can be specified such as FOR SBCS DATA, FOR DBCS DATA, and CCSID. On zSeries, other subtypes that can be specified are FOR SBCS DATA and FOR MIXED DATA.

DB2 date and time data types include DATE, TIME, and TIMESTAMP. The TIMESTAMP data type consists of both the date part and the time part, while DATE and TIME data types only deal with the date and the time component, respectively. Their limits are listed in Table 3.3.

Table 3.3. DB2 Built-In Date Time Data Types

Description

Limits

Smallest DATE value

0001-01-01

Largest DATE value

9999-12-31

Smallest TIME value

00:00:00

Largest TIME value

24:00:00

Smallest TIMESTAMP value

0001-01-01-00.00.00.000000

Largest TIMESTAMP value

9999-12-31-24.00.00.000000


The last data type in Figure 3.1, DATALINK, is used to work with files stored outside the database. It is not covered in this book.



    DB2 SQL PL. Deployment and Advanced Configuration Essential Guide for DB2 UDB on Linux., UNIX, Windows, i5. OS, z. OS
    Intelligent Enterprises of the 21st Century
    ISBN: N/A
    EAN: 2147483647
    Year: 2003
    Pages: 205

    flylib.com © 2008-2017.
    If you may any questions please contact us: flylib@qtcs.net