Data Types for DB2 under UNIX and PC Hosts

Overview

Every column in a table has a name and a data type. The data type tells DB2 how much physical storage to set aside for the column and the form in which the data is stored. DB2 uses IBM SQL data types. This section includes information about DB2 data types, null and default values, and data conversions.
For more information about DB2 data types and to determine which data types are available for your version of DB2, see your DB2 SQL reference documentation.

Character Data

BLOB (binary large object)
contains varying-length binary string data with a length of up to 2 gigabytes. It can hold structured data that user-defined types and functions can exploit. Similar to FOR BIT DATA character strings, BLOB strings are not associated with a code page.
CLOB (character large object)
contains varying-length character string data with a length of up to 2 gigabytes. It can store large single-byte character set (SBCS) or mixed (SBCS and multibyte character set, or MBCS) character-based data, such as documents written with a single character set. It therefore has an SBCS or mixed code page associated with it.

String Data

CHAR(n)
specifies a fixed-length column for character string data. The maximum length is 254 characters.
VARCHAR(n)
specifies a varying-length column for character string data. The maximum length of the string is 4000 characters. If the length is greater than 254, the column is a long-string column. SQL imposes some restrictions on referencing long-string columns. For more information about these restrictions, see your IBM documentation.
LONG VARCHAR
specifies a varying-length column for character string data. The maximum length of a column of this type is 32700 characters. A LONG VARCHAR column cannot be used in certain functions, subselects, search conditions, and so on. For more information about these restrictions, see your IBM documentation.
GRAPHIC(n)
specifies a fixed-length column for graphic string data. n specifies the number of double-byte characters and can range from 1 to 127. If n is not specified, the default length is 1.
VARGRAPHIC(n)
specifies a varying-length column for graphic string data. n specifies the number of double-byte characters and can range from 1 to 2000.
LONG VARGRAPHIC
specifies a varying-length column for graphic-string data. n specifies the number of double-byte characters and can range from 1 to 16350.

Numeric Data

BIGINT
specifies a big integer. Values in a column of this type can range from –9223372036854775808 to +9223372036854775807. However, numbers that require decimal precision greater than 15 digits might be subject to rounding and conversion errors.
SMALLINT
specifies a small integer. Values in a column of this type can range from –32768 to +32767.
INTEGER
specifies a large integer. Values in a column of this type can range from –2147483648 to +2147483647.
FLOAT | DOUBLE | DOUBLE PRECISION
specifies a floating-point number that is 64 bits long. Values in a column of this type can range from –1.79769E+308 to –2.225E−307 or +2.225E−307 to +1.79769E+308, or they can be 0. This data type is stored the same way that SAS stores its numeric data type. Therefore, numeric columns of this type require the least processing when SAS accesses them.
DECIMAL | DEC | NUMERIC | NUM
specifies a mainframe-packed decimal number with an implicit decimal point. The precision and scale of the number determines the position of the decimal point. The numbers to the right of the decimal point are the scale, and the scale cannot be negative or greater than the precision. The maximum precision is 31 digits. Numbers that require decimal precision greater than 15 digits might be subject to rounding and conversion errors.

Date, Time, and Timestamp Data

SQL date and time data types are collectively called datetime values. The SQL data types for dates, times, and timestamps are listed here. Be aware that columns of these data types can contain data values that are out of range for SAS.
DATE
specifies date values in various formats, as determined by the country code of the database. For example, the default format for the United States is mm-dd-yyyy and the European standard format is dd.mm.yyyy. The range is 01-01-0001 to 12-31-9999. A date always begins with a digit, is at least eight characters long, and is represented as a character string. For example, in the U.S. default format, January 25, 1991, would be formatted as 01-25-1991.
The entry format can vary according to the edit codes that are associated with the field. For more information about edit codes, see your IBM documentation.
TIME
specifies time values in a three-part format. The values range from 0 to 24 for hours (hh) and from 0 to 59 for minutes (mm) and seconds (ss). The default form for the United States is hh:mm:ss, and the IBM European standard format for time is hh.mm[.ss]. For example, in the U.S. default format 2:25 p.m. would be formatted as 14:25:00.
The entry format can vary according to the edit codes that are associated with the field. For more information about edit codes, see your IBM documentation.
TIMESTAMP
combines a date and time and adds an optional microsecond to make a seven-part value of the format yyyy-mm-dd-hh.mm.ss[.nnnnnn]. For example, a timestamp for precisely 2:25 p.m. on January 25, 1991, would be 1991-01-25-14.25.00.000000. Values in a column of this type have the same ranges as described earlier for DATE and TIME.
For more information about SQL data types, datetime formats, and edit codes that are used in the United States and other countries, see your IBM documentation.

DB2 Null and Default Values

DB2 has a special value called NULL. A DB2 NULL value means an absence of information and is analogous to a SAS missing value. When SAS/ACCESS reads a DB2 NULL value, it interprets it as a SAS missing value.
You can define a column in a DB2 table so that it requires data. To do this in SQL, you specify a column as NOT NULL. NOT NULL tells SQL to only allow a row to be added to a table if there is a value for the field. For example, NOT NULL assigned to the field CUSTOMER in the table SASDEMO.CUSTOMER does not allow a row to be added unless there is a value for CUSTOMER. When creating a DB2 table with SAS/ACCESS, you can use the DBNULL= data set option to indicate whether NULL is a valid value for specified columns.
DB2 columns can also be defined as NOT NULL WITH DEFAULT. For more information about using the NOT NULL WITH DEFAULT value, see your DB2 SQL reference documentation.
Once you know whether a DB2 column enables NULLs or the host system supplies a default value for a column that is defined as NOT NULL WITH DEFAULT, you can write selection criteria and enter values to update a table. Unless a column is defined as NOT NULL or NOT NULL WITH DEFAULT, it allows NULL values.
For more information about how SAS handles NULL values, see Potential Result Set Differences When Processing Null Data .
To control how the DBMS handles SAS missing character values, use the NULLCHAR= and NULLCHARVAL= data set options.

LIBNAME Statement Data Conversions

This table shows the default formats that SAS/ACCESS Interface to DB2 assigns to SAS variables when using the LIBNAME statement to read from a DB2 table. These default formats are based on DB2 column attributes.
LIBNAME Statement: Default SAS Formats for DB2 Data Types
DB2 Data Type
SAS Data Type
Default SAS Format
BLOB
character
$HEXn.
CLOB
character
$n.
CHAR(n)1
VARCHAR(n)1
LONG VARCHAR
character
$n.
GRAPHIC(n)1
VARGRAPHIC(n)1
LONG VARGRAPHIC
character
$n.
INTEGER
numeric
11.
SMALLINT
numeric
6.
BIGINT
numeric
20.
DECIMAL
numeric
m.n
NUMERIC
numeric
m.n
FLOAT
numeric
none
DOUBLE
numeric
none
TIME
numeric
TIME8.
DATE
numeric
DATE9.
TIMESTAMP
numeric
DATETIMEm.n
1n in DB2 data types is equivalent to w in SAS formats.
This table shows the default DB2 data types that SAS/ACCESS assigns to SAS variable formats during output operations when you use the LIBNAME statement.
LIBNAME Statement: Default DB2 Data Types for SAS Variable Formats
SAS Variable Format
DB2 Data Type
m.n
DECIMAL (m,n)1
other numerics
DOUBLE
$n.
VARCHAR(n)1 (n<=4000)
LONG VARCHAR(n)1 (n>4000)
datetime formats
TIMESTAMP
date formats
DATE
time formats
TIME
1n in DB2 data types is equivalent to w in SAS formats.

DBLOAD Procedure Data Conversions

The following table shows the default DB2 data types that SAS/ACCESS assigns to SAS variable formats when you use the DBLOAD procedure.
PROC DBLOAD: Default DB2 Data Types for SAS Variable Formats
SAS Variable Format
DB2 Data Type
$w.
CHAR(n)
w.
DECIMAL(p)
w.d
DECIMAL(p,s)
IBw.d, PIBw.d
INTEGER
all other numerics1
DOUBLE
datetimew.d
TIMESTAMP
datew.
DATE
time.2
TIME
1Includes all SAS numeric formats, such as BINARY8 and E10.0.
2Includes all SAS time formats, such as TODw,d and HHMMw,d.