SAS/ACCESS Software for Relational Databases: Reference

DB2 Data Types

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 are stored. DB2 uses IBM SQL data types. The data types fall into three categories: types for string data, types for numeric data, and types for datetime values. Each of these types is described in the following sections. For more information about DB2 data types, see your DB2 SQL reference manual.

Note: The SAS/ACCESS interface does not support the following DB2 data types: BLOB, CLOB, and DBCLOB. [cautionend]

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 forth. 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

SMALLINT: specifies a small integer. Values in a column of this type can range from -32768 through +32767.
INTEGER: specifies a large integer. Values in a column of this type can range from -2147483648 through +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 the SAS System stores its numeric data type; therefore, numeric columns of this type require the least processing when they are being accessed by the SAS System.)
DECIMAL | DEC | NUMERIC | NUM: specifies a mainframe packed decimal number with an implicit decimal point. The position of the decimal point is determined by the precision and scale of the number. The scale, which is the numbers to the right of the decimal point, cannot be negative or greater than the precision. The maximum precision is 31 digits. Note that numbers that require decimal precision greater than 15 digits may be subject to rounding and conversion errors.

Dates, Times, and Timestamps

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 may contain data values that are out of range for the SAS System.

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.mmm.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 input 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 input 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.

Null Values

DB2 has a special value called NULL. NULL means that a value in a row is not known or is missing; it does not mean the value is blank or zero. It is analogous to the SAS System's missing value.

You can define a column in a 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.

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 manual.

Knowing whether a DB2 column allows NULLs, or whether the host system supplies a default value for a column that is defined as NOT NULL WITH DEFAULT, can assist you in writing selection criteria and in entering 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, see DBNULL= and NULLCHAR= .

ACCESS Procedure Data Conversions

The following table shows the default SAS System variable formats that the ACCESS procedure assigns to each DB2 data type.

Operating Environment Information: PROC ACCESS is valid only for DB2 running under OS/2. It is not valid under any other operating environment. [cautionend]

Default SAS System Variable Formats for DB2 Data Types
DB2 for Common Servers Data Type SAS Variable Format

Character (fixed length) $w.(n<32,767)*
$32767. (n>32,767)

Character (varying length) $w.(n<32,767)*
$32767. (n>32,767)

INTEGER 11.0

SMALLINT 6.0

DECIMAL p+2.s.
for example, DEC(6,4)=8.4

FLOAT none

TIME TIME11.2.

DATE DATE9.

TIMESTAMP DATETIME25.6

*n in DB2 data types is equivalent to w in SAS formats.

***Default SAS System Variable Formats for DB2 Data Types***
DB2 for Common Servers Data Type	SAS Variable Format
Character (fixed length)	$w.(n<32,767)* $32767. (n>32,767)
Character (varying length)	$w.(n<32,767)* $32767. (n>32,767)
INTEGER	11.0
SMALLINT	6.0
DECIMAL	p+2.s. for example, DEC(6,4)=8.4
FLOAT	none
TIME	TIME11.2.
DATE	DATE9.
TIMESTAMP	DATETIME25.6
*n in DB2 data types is equivalent to w in SAS formats.

If DB2 data fall outside of the valid SAS data ranges, you get an error message in the SAS log when you try to read the data.

DBLOAD Procedure Data Conversions

The following table shows the default DB2 data types that the DBLOAD procedure assigns to SAS variable formats.

PROC DBLOAD: Default DB2 Data Types for SAS System Variable Formats
SAS Variable Format DB2 for Common Servers Data Type

$w.
CHAR(n)

w. DECIMAL(p)

w.d DECIMAL(p,s)

IBw.d, PIBw.d INTEGER

all other numerics* DOUBLE

datetimew.d
TIMESTAMP

datew.
DATE

time.** TIME

*Includes all SAS numeric formats, such as BINARY8 and E10.0.
**Includes all SAS time formats, such as TODw.d and HHMMw.d.

***PROC DBLOAD: Default DB2 Data Types for SAS System Variable Formats***
SAS Variable Format	DB2 for Common Servers Data Type
$w.	CHAR(n)
w.	DECIMAL(p)
w.d	DECIMAL(p,s)
IBw.d, PIBw.d	INTEGER
all other numerics*	DOUBLE
datetimew.d	TIMESTAMP
datew.	DATE
time.**	TIME
Includes all SAS numeric formats, such as BINARY8 and E10.0. *Includes all SAS time formats, such as TODw.d and HHMMw.d.

LIBNAME Statement Data Conversions

The following table shows the default SAS System variable formats that the LIBNAME statement assigns to DB2 data types during input operations.

LIBNAME Statement: Default SAS Formats for DB2 Data Types
DB2 for Common Servers Data Type SAS Data Type Default SAS Format

CHAR(n) character $n.

VARCHAR(n) character $n.

LONG VARCHAR character $n.

GRAPHIC(n), VARGRAPHIC(n), LONG VARGRAPHIC character $n.

INTEGER numeric 11.

SMALLINT numeric 6.

DECIMAL numeric m.n

NUMERIC numeric m.n

FLOAT numeric none

DOUBLE numeric none

TIME numeric TIME8.

DATE numeric DATE9.

TIMESTAMP numeric DATETIMEm.n

*n in DB2 data types is equivalent to w in SAS formats.

***LIBNAME Statement: Default SAS Formats for DB2 Data Types***
DB2 for Common Servers Data Type	SAS Data Type	Default SAS Format
CHAR(n)	character	$n.
VARCHAR(n)	character	$n.
LONG VARCHAR	character	$n.
GRAPHIC(n), VARGRAPHIC(n), LONG VARGRAPHIC	character	$n.
INTEGER	numeric	11.
SMALLINT	numeric	6.
DECIMAL	numeric	m.n
NUMERIC	numeric	m.n
FLOAT	numeric	none
DOUBLE	numeric	none
TIME	numeric	TIME8.
DATE	numeric	DATE9.
TIMESTAMP	numeric	DATETIMEm.n
*n in DB2 data types is equivalent to w in SAS formats.

The following table shows the default DB2 data types that the LIBNAME statement assigns to SAS variable formats during output operations.

LIBNAME Statement: Default DB2 Data Types for SAS Variable Formats
SAS Variable Format DB2 for Common Servers Data Type

m.n DECIMAL (m,n)

other numerics DOUBLE

$n. VARCHAR(n) (n<=4000)
LONG VARCHAR(n) (n>4000)

datetime formats TIMESTAMP

date formats DATE

time formats TIME

*n in DB2 data types is equivalent to w in SAS formats.

***LIBNAME Statement: Default DB2 Data Types for SAS Variable Formats***
SAS Variable Format	DB2 for Common Servers Data Type
m.n	DECIMAL (m,n)
other numerics	DOUBLE
$n.	VARCHAR(n) (n<=4000) LONG VARCHAR(n) (n>4000)
datetime formats	TIMESTAMP
date formats	DATE
time formats	TIME
*n in DB2 data types is equivalent to w in SAS formats.

Chapter Contents
Previous
Next
Top of Page