DB2 Data Type

DB2 tables have data types for every column. Depending on the data type, the column can have a wide range of values and can be subject to a variety of operators and DB2 functions. In this article, you’ll learn the basics of data types in DB2. In addition, we'll discuss what are the different categories of DB2 data types and their sizes followed by an example.

Agenda.

• Introduction.
• What is Data Types in DB2?
• DB2 data types and COBOL equivalents.
• Conclusion.

Introduction.

DB2 tables have data types for every column. When you create a DB2 table, you specify the data type for each column. A table column can also be changed to a different type of data. Whenever a table is reorganized, all data in the associated table is reclassified according to the new data type definition.

Depending on the data type, operators and functions may be further defined as parameters. IBM's DB2 supports both IBM-supplied data types as well as user-defined data types. IBM supplied data types are sometimes called built-in data types.

What is Data Types in DB2?

In programming language, a data type is used to classify the type of value a variable has and what type of operation can be applied on these variables. An integer, for example, is a type of data type that is used to represent whole numbers, while a string represents text. The following are the DB2 Data Types. 

Data	Data Type	DB2 Data Type
Signed numeric types: Exact	Binary integer: 16-bit	SMALLINT
	Binary integer: 32-bit	INTEGER
Signed numeric types: Decimal	Packed	DECIMAL
Signed numeric types: Approximate	Floating-point single-precision	REAL
	Floating-point double-precision	DOUBLE
Date/time types	Time	TIME
	Timestamp	TIMESTAMP
	Date	DATE
String types: Character	Fixed length	CHAR
	Varying length	VARCHAR CLOB
String types: Graphic	Fixed length	GRAPHIC
	Varying length	VARGRAPHIC DBCLOB
	Varying-length binary	BLOB
String Types: Binary	Varying length	BLOB
Row identifier		ROWID

Special Data Types

Some special data types in DB2 can be used to support special processing, such as random-number and sequential-number generation and the ability to create data types.

ROWID

A ROWID is a value that uniquely identifies a row in a table. A ROWID column enables writing queries that navigate directly to a row in the table. ROWID column values are generated by DB2 unless supplied, but they must be unique. If a value is supplied, it must be a valid DB2-generated value. The internal format is 17 bytes and contains bit data. The external length is 40 bytes.

If a ROWID column is used in a query, the access path is referred to as 'Direct Row Access' because the row is directly accessed without a scan of the index or the table space.

Data Type Selection

Usage	Data Type
Is the data variable in length?	VARCHAR
If the data is variable in length, what is the maximum length?	VARCHAR
Do you need to sort, or order, the data?	CHAR, VARCHAR, NUMERIC
Is the data going to be used in arithmetic operations?	DECIMAL, NUMERIC, REAL, DOUBLE, FLOAT, 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?	DISTINCT TYPE
Is the data larger than what a character string can store? Do you need to store nontraditional data?	CLOB, BLOB, DBCLOB

Created with Artisteer