Choosing Proper Data Types

Choosing the correct data type is a simple and yet important task. Specifying the wrong data type may result in not only wasted disk space but also poor performance. To choose the correct data type, you need to fully understand your data and their possible values and usage. Table 3.4 offers a checklist for data type selection.

Table 3.4. Simple Data Type Checklist

Question	Data Type
Is the string data variable in length?	VARCHAR
If the string data is variable in length, what is the maximum length?	VARCHAR
Do you need to sort (order) the data?	CHAR, VARCHAR,NUMERIC
Is the data going to be used in arithmetic operations?	DECIMAL, NUMERIC,REAL, DOUBLE,BIGINT, INTEGER,SMALLINT
Does the data element contain decimals?	DECIMAL, NUMERIC,REAL, DOUBLE
Is the data fixed in length?	CHAR
Does the data have a specific meaning (beyond DB2 base data types)?	USER DEFINED TYPE
Is the data larger than what a character string can store, or do you need to store non-traditional data?	CLOB, BLOB, DBCLOB

Tip

Unnecessary casting can cost performance. Try to define the variables in the SQL procedures with the same data types as the underlining table columns.

REAL, FLOAT, and DOUBLE are imprecise data types where rounding may occur. You should not use these data types for storing precise data, such as primary key values or currency data.