DB2 - Data Types

Db2 - Data Types

This chapter provides a comprehensive overview of the modern data types available in Db2. Choosing the correct data type is fundamental for data integrity, storage efficiency, and query performance.

Introduction

In a Db2 database, every column in a table is assigned a specific data type. This data type defines the kind of data the column can hold (e.g., numbers, text, dates), its range of possible values, and its storage format. Db2 offers a rich set of built-in data types to cover virtually any requirement.

Built-in Data Types

Numeric Types

• SMALLINT: A 16-bit integer. Range: -32,768 to +32,767. Ideal for counters or flags with a small, known range.
• INTEGER: A 32-bit integer. Range: -2,147,483,648 to +2,147,483,647. A very common choice for primary keys or general-purpose whole numbers.
• BIGINT: A 64-bit integer. Used for very large whole numbers, often for IDs in large-scale systems or for financial calculations where precision is key.
• DECIMAL(p, s) or NUMERIC(p, s): A packed decimal number with exact precision. p is the total number of digits (precision) and s is the number of digits to the right of the decimal point (scale). Perfect for financial data like currency, where rounding errors are unacceptable.
• REAL: A 32-bit single-precision floating-point number. Use for scientific calculations where absolute precision is not as critical as range and performance.
• DOUBLE: A 64-bit double-precision floating-point number. Offers greater precision and range than REAL.
• DECFLOAT: A decimal floating-point number. Combines the precision of DECIMAL with the range of floating-point numbers, useful for modern financial and scientific applications.

String Types

• CHAR(n): A fixed-length character string. If the stored string is shorter than n, it is padded with spaces. Use for data with a consistent length, like country codes (‘US’, ‘CA’).
• VARCHAR(n): A varying-length character string with a maximum length of n. This is the most common string type as it saves space by not storing padding characters.
• CLOB(n): Character Large Object. For storing very large text documents that exceed the maximum length of VARCHAR (up to 2 GB).
• GRAPHIC(n), VARGRAPHIC(n), DBCLOB(n): These are the double-byte character equivalents of CHAR, VARCHAR, and CLOB, respectively. They are used for storing character sets like UTF-16.

Datetime Types

• DATE: Stores a date value (year, month, day).
• TIME: Stores a time value (hour, minute, second).
• TIMESTAMP(p): Stores a combination of date and time, with fractional second precision up to p digits (0 to 12). TIMESTAMP(6) is common for microsecond precision. Ideal for logging events.

Boolean Type

• BOOLEAN: Stores a TRUE or FALSE value. Internally, it can be represented by 1 (true) and 0 (false). Much more semantic than using a SMALLINT for flags.

Binary and Semi-Structured Types

• BLOB(n): Binary Large Object. For storing binary data such as images, audio files, or serialized objects up to 2 GB.
• XML: Stores well-formed XML documents. Db2 provides powerful functions for querying and manipulating data directly within the XML documents using XQuery and XPath.
• JSON: A modern addition for storing data in JavaScript Object Notation (JSON) format. Db2 provides SQL/JSON functions to query and work with JSON documents, making it ideal for web applications and API integrations. Note: Stored as BLOB, CLOB, or VARCHAR with specific functions to interact with it as JSON.

Best Practice: Choosing the Right Data Type

Selecting the most appropriate data type is a critical design decision:

• Be Specific: Always choose the smallest data type that can reliably accommodate all possible values for a column. Using BIGINT for a column that will never exceed 1000 is inefficient.
• Prefer VARCHAR over CHAR: Unless your data is always a fixed length, VARCHAR is generally more space-efficient.
• Use DECIMAL for Money: Never use floating-point types (REAL, DOUBLE) for currency or financial calculations to avoid potential rounding errors.
• Use TIMESTAMP for Events: For recording when something happened, TIMESTAMP is superior to DATE as it includes the time of day with high precision.