DB2 Data Types

Every column in a DB2 table must be declared with a data type. The data type decides what kind of values the column can hold, how much space it takes, and what operations are allowed on it.

Why data types matter

• The data type is fixed when the table is created. You cannot store text in a numeric column later.
• Choosing the right type saves disk space and makes the program run faster.
• DB2 checks every value against the data type. A wrong value gives an SQL error instead of corrupt data.
• Common families are character, numeric, and datetime.

Character data types

• CHAR(n) - fixed-length character string. CHAR(30) always uses 30 bytes, padded with blanks.
• VARCHAR(n) - variable-length character string. Uses only as many bytes as the value needs.
• GRAPHIC and VARGRAPHIC - same idea as CHAR and VARCHAR, but for double-byte (DBCS) data.
• Use CHAR for short fixed codes like SEX CHAR(1). Use VARCHAR for names and addresses of unknown length.

Numeric data types

• SMALLINT - whole numbers from -32768 to +32767. Uses 2 bytes.
• INTEGER (INT) - whole numbers up to about 2 billion. Uses 4 bytes.
• BIGINT - very large whole numbers. Uses 8 bytes.
• DECIMAL(p,s) - packed decimal numbers. p is total digits, s is digits after the decimal point. DECIMAL(9,2) is ideal for money.
• FLOAT / REAL - approximate floating-point numbers. Used for scientific calculations, not for money.

Datetime data types

• DATE - stores a calendar date (year, month, day).
• TIME - stores time of day (hour, minute, second).
• TIMESTAMP - stores date and time together, down to microseconds. Used for audit columns.
• DB2 checks datetime values. Feb 30 gives SQLCODE -181 because the value is invalid.

Example

• Creating an employee table with the common data types:-