DB2 provides a rich and flexible assortment of data types. DB2 comes with basic data types such as INTEGER, CHAR, and DATE. It also includes facilities to create user-defined data types (UDTs) so that you can create complex, nontraditional data types suited to today's complex programming environments, or more precisely defined types based on built-in data types. Choosing which data type to use in a given situation depends on the type and range of information that is stored in the column.
There are four categories of built-in data types: numeric, string, datetime, and XML. Note: on z/OS there is a ROWID data type, and on Linux, UNIX, and Windows there is BOOLEAN.
The user-defined data types are categorized as: distinct, structured, and reference.
There are four categories of numeric data types: integer, decimal, floating point, and decfloat. These types vary in the range and precision of numeric data they can store.
- Integer: SMALLINT, INTEGER, and BIGINT are used to store integer numbers. For example, an inventory count could be defined as INTEGER. SMALLINT can store integers from -32,768 to 32,767 in 2 bytes. INTEGER can store integers from -2,147,483,648 to 2,147,483,647 in 4 bytes. BIGINT can store integers from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 in 8 bytes.
- Decimal: DECIMAL is used to store numbers with fractional parts up to 31 digits. To define this data type, specify a precision(p), which indicates the total number of digits, and a scale(s), which indicates the number of digits to the right of the decimal place. A column defined by DECIMAL(10,2) that held currency values could hold values up to 99999999.99 dollars. The amount of storage required in the database depends on the precision and is calculated by the formula p/2 +1. So, DECIMAL(10,2) would require 10/2 + 1 or 6 bytes.
- Floating point: REAL and DOUBLE are used to store approximations of numbers. For example, very small or very large scientific measurements can be defined as REAL. REAL can be defined with a precision up to 24 digits and requires 4 bytes of storage. DOUBLE can be defined with a precision up to 53 digits and requires 8 bytes of storage. FLOAT can be used as a synonym for REAL or DOUBLE depending upon the specified precision.
- Decfloat: DECFLOAT implements the IEEE standard 754r for floating point, with a precision of 16 or 34 digits. DECFLOAT can occupy either 8 or 16 bytes, depending upon the precision.
DB2 provides several data types for storing character data or strings. Choose a data type based on the size of the string you are going to store and what data will be in the string.
The following data types are used to store single-byte character strings.
- CHAR or CHARACTER is used to store fixed-length character strings up to 254 bytes (Note: z/OS, 255 bytes). For example, a manufacturer may assign an identifier to a part with a specific length of eight characters, and therefore store that identifier in the database as a column of CHAR(8).
- VARCHAR is used to store variable-length character strings. For example, a manufacturer may deal with a number of parts with identifiers of different lengths, and thus store those identifiers as a column of VARCHAR(100). The maximum length of a VARCHAR column is approximately 32K bytes. In the database, VARCHAR data only takes as much space as required, plus 2 bytes for the length.
The following data types are used to store strings made of multi-byte characters.
- GRAPHIC is used to store fixed-length multi-byte character strings. The maximum length of a GRAPHIC column is 127 characters.
- VARGRAPHIC is used to store variable-length multi-byte character strings. The maximum length of a VARGRAPHIC column is approximately 16K characters.
DB2 also provides data types to store very long strings of data. All long string data types have similar characteristics. First, the data is not stored physically with the row data in the database, which means that additional processing is required to access this data. Long data types can be defined up to 2GB-1 in length. However, only the space required is actually used. The long data types are as follows.
- CLOB (character large object).
- DBCLOB (double-byte character large object).
- BLOB (binary large object) a LOB for which no character code conversion should be applied, for example, a picture.
DB2 provides the following three data types to store dates and times.
The values of these data types are stored in the database in an internal format; however, applications can manipulate them as strings. When one of these data types is retrieved, it is represented as a character string. Enclose the value in quotation marks when updating these data types.
DB2 provides functions to manipulate datetime values. For example, you can determine the day of the week of a date value using the DAYOFWEEK or DAYNAME functions. Use the DAYS function to calculate how many days lie between two dates. DB2 also provides special registers to generate the current date, time, or timestamp based on the time-of-day clock. For example, CURRENT DATE returns a string representing the current date on the system.
The format of the date and time values depends on the country code of the database, which is specified when the database is created. There are several formats available: ISO, USA, EUR, and JIS. For example, if you are using the USA format, the format of date values would be mm/dd/yyyy. You can change the format by using the DATETIME option of the BIND command when creating your application.
The string representation of a timestamp is yyyy-mm-dd-hh.mm.ss.nnnnnn.
DB2 provides the XML data type to store well-formed XML documents. Values in XML columns are stored in an internal representation different from string data types. To store XML data in an XML data type column, transform the data using the XMLPARSE function. An XML data type value can be transformed into a serialized string value representing of the XML document using the XMLSERIALIZE function. DB2 provides many other built-in functions to manipulate XML data types.
DB2 allows you to define data types that suit your application.
User-defined distinct: Define a new data type based on a built-in type.
This new type has the same features of the built-in type, but you can
use it to ensure that only values of the same type are compared. For
example, you can define a Canadian dollar type (CANDOL) and a US
dollar type (USADOL), both based on DECIMAL(10,2). Both types are
based on the same built-in type, but you won't be able to compare them
unless a conversion function is applied. The following
CREATE TYPE statements create the CANDOL
and USADOL UDTs, as shown in Listing 20.
Listing 20. CREATE TYPE statements
CREATE DISTINCT TYPE CANDOL AS DECIMAL(10,2) WITH COMPARISONS; CREATE DISTINCT TYPE USADOL AS DECIMAL(10,2) WITH COMPARISONS;
DB2 automatically generates functions to perform casting between the base type and the distinct type, and comparison operators for comparing instances of the distinct type. The following statements show how to create a table with a column of CANDOL type and then insert data into the table using the CANDOL casting function, as shown in Listing 21.
Listing 21. CANDOL casting function
CREATE TABLE ITEMS (ITEMID CHAR(5), PRICE CANDOL); INSERT INTO ITEMS VALUES('ABC11',CANDOL(30.50) );
In addition, on the Linux, UNIX, and Windows platform you can create the following.
- User-defined array: An array type is defined as an array with elements of another data type.
- User-defined cursor: A cursor type represents a reference to an underlying cursor.
- User defined row: A row type is a structure that can be used by SQL PL as a data type for variables and parameters to represent a row for simpler manipulation. It can also be used as the data type of an array.
- User-defined structured: A structured type has a structure that contains a sequence of attributes each of which has a data type. A structured type may be used as the type of a table, view or column. For example, you can create a structured type named ADDRESS that contains data for street number, street name, city, and so forth. Then you can use this type when defining other tables, such as employees or suppliers, because the same data is required for both. Also, structured types can have subtypes in a hierarchical structure. This lets you store objects that belong to a hierarchy.
- User-defined reference: When using structured types, you can define references to rows in another table using reference types. These references appear similar to referential constraints; however, they do not enforce relationships between the tables. References in tables let you specify queries in a different way.
DB2 Extenders provide support for complex, nontraditional data types. They are packaged separately from the DB2 server code and installed in each DB2 instance that uses the data types and methods provided by the extenders.
DB2 Extenders available from IBM and from independent software vendors. The DB2 Spatial Extender can be used for storing and analyzing spatial data. The DB2 Net Search Extender offers users and application programmers a way to search full-text documents stored in DB2 databases, other databases, and file systems using SQL queries.
DB2 Extenders are implemented using the features of user-defined types and user-defined functions (UDFs). Each extender comes with one or more UDTs, UDFs for operating on the UDT, specific application programming interfaces (APIs), and perhaps other tools.