Data types of attributes

String data types

Data that contains a combination of letters, numbers, and special characters. String data types are listed below:

• CHARACTER: Fixed-length character strings. The common short name for this data type is CHAR.
• VARCHAR: Varying-length character strings.
• CLOB: Varying-length character large object strings, typically used when a character string might exceed the limits of the VARCHAR data type.
• GRAPHIC: Fixed-length graphic strings that contain double-byte characters.
• VARGRAPHIC: Varying-length graphic strings that contain double-byte characters.
• DBCLOB: Varying-length strings of double-byte characters in a large object.
• BINARY: A sequence of bytes that is not associated with a code page.
• VARBINARY: Varying-length binary strings.
• BLOB: Varying-length binary strings in a large object.
• XML: Varying-length string that is an internal representation of XML.

Numerical data types

Data that contains digits. Numerical data types are listed below:

• SMALLINT: for small integers.
• INTEGER: for large integers.
• BIGINT: for bigger values.
• DECIMAL(p,s) or NUMERIC(p,s), where p is precision and s is scale: for packed decimal numbers with precision p and scale s. Precision is the total number of digits, and scale is the number of digits to the right of the decimal point.
• DECFLOAT: for decimal floating-point numbers.
• REAL: for single-precision floating-point numbers.
• DOUBLE: for double-precision floating-point numbers.

Datetime data types

Data values that represent dates, times, or timestamps. Datetime data types are listed below:

• DATE: Dates with a three-part value that represents a year, month, and day.
• TIME: Times with a three-part value that represents a time of day in hours, minutes, and seconds.
• TIMESTAMP: Timestamps with a seven-part value that represents a date and time by year, month, day, hour, minute, second, and microsecond

Examples

You might use the following data types for attributes of the EMPLOYEE entity:

• EMPLOYEE_NUMBER: CHAR(6)
• EMPLOYEE_LAST_NAME: VARCHAR(15)
• EMPLOYEE_HIRE_DATE: DATE
• EMPLOYEE_SALARY_AMOUNT: DECIMAL(9,2)

The data types that you choose are business definitions of the data type. During physical database design, you might need to change data type definitions or use a subset of these data types. The database or the host language might not support all of these definitions, or you might make a different choice for performance reasons.

For example, you might need to represent monetary amounts, but Db2 and many host languages do not have a data type MONEY. In the United States, a natural choice for the SQL data type in this situation is DECIMAL(10,2) to represent dollars. But you might also consider the INTEGER data type for fast, efficient performance.