SAS/ACCESS Software for Relational Databases: Reference

DB2 Data Types

Every column in a table has a name and a data type. DB2 data types fall into three categories: types for string data; types for numeric data; and types for dates, times, and timestamps. The categories, followed by the data types within each category, are listed in the following sections. The SAS/ACCESS interface to DB2 handles all DB2 data types. This section describes how the DB2 engine treats each of these data types.

String Data

The DB2 string data types are listed here.

CHAR(n): specifies a fixed-length column of length n for character string data. The maximum for n is 254.
VARCHAR(n): specifies a varying-length column for character string data. n specifies the maximum length of the string. If n is greater than 254, the column is a long string column. DB2 imposes some restrictions on referencing long string columns.
LONG VARCHAR: specifies a varying-length column for character string data. DB2 determines the maximum length of this column. A column defined as LONG VARCHAR is always a long string column and, therefore, subject to referencing restrictions.
GRAPHIC(n), VARGRAPHIC(n), LONG VARGRAPHIC: specifies graphic strings and is comparable to the types for character strings. However, n specifies the number of double-byte characters, so the maximum value for n is 127. If n is greater than 127, the column is a long string column and is subject to referencing restrictions.

Numeric Data

The DB2 numeric data types are listed here.

SMALLINT

specifies a small integer. Values in a column of this type can range from -32,768 through +32,767.

INTEGER | INT

specifies a large integer. Values in a column of this type can range from -2,147,483,648 through +2,147,483,647.

REAL | FLOAT(n)

specifies a single-precision, floating-point number. If n is omitted or if n is greater than 21, the column is double-precision. Values in a column of this type can range from approximately -7.2E+75 through 7.2E+75.

FLOAT(n) | DOUBLE PRECISION | FLOAT | DOUBLE

specifies a double-precision, floating-point number. n can range from 22 through 53. If n is omitted, 53 is the default. Values in a column of this type can range from approximately -7.2E+75 through 7.2E+75.

DECIMAL(p,s) | DEC(p,s)

specifies a packed-decimal number. p is the total number of digits (precision) and s is the number of digits to the right of the decimal point (scale). The maximum precision is 31 digits. The range of s is 0 [le]

If s is omitted, 0 is assigned and p may also be omitted. Omitting both s and p results in the default DEC(5,0). The maximum range of p is 1 -10³¹ to 10³¹ -1.

Even though the DB2 numeric columns have these distinct data types, the DB2 engine accesses, inserts, and loads all numerics as FLOATs.

Dates, Times, and Timestamps

DB2 date and time data types are similar to SAS date and time values in that they are stored internally as numeric values and are displayed in a site-chosen format. The DB2 data types for dates, times, and timestamps are listed here. Note that columns of these data types may contain data values that are out of range for the SAS System, which handles dates from 1582 A.D. through 20,000 A.D.

DATE: specifies date values in the format YYYY-MM-DD. For example, January 25, 1989, is input as 1989-01-25. Values in a column of this type can range from 0001-01-01 through 9999-12-31.
TIME: specifies time values in the format HH.MM.SS. For example, 2:25 p.m. is input as 14.25.00. Values in a column of this type can range from 00.00.00 through 24.00.00.
TIMESTAMP: combines a date and time and adds a microsecond to make a seven-part value of the format YYYY-MM-DD-HH.MM.SS.MMMMMM. For example, a timestamp for precisely 2:25 p.m. on January 25, 1989, is 1989-01-25-14.25.00.000000. Values in a column of this type can range from 0001-01-01-00.00.00.000000 through 9999-12-31-24.00.00.000000.

DB2 NULLs and DB2 Default Values

DB2 has a special value that is called NULL. This value means an absence of information. It is analogous to the SAS System's missing value.

Columns can be defined so that they do not allow NULL data. NOT NULL would indicate, for example, that DB2 does not allow a row to be added to the TESTID.CUSTOMERS table unless there's a value for CUSTOMER.

Columns can also be defined as NOT NULL WITH DEFAULT. The following table lists the default values assigned by DB2 to columns that are defined as NOT NULL WITH DEFAULT. An example of such a column is STATE in TESTID.CUSTOMERS. If a column is omitted from a view descriptor, default values are assigned to the column. However, if a column is specified in a view descriptor and it has no values, no default values are assigned.

Default Values Assigned by DB2 for columns defined as NOT NULL WITH DEFAULT
DB2 Column Type DB2 Default*

CHAR(n) | GRAPHIC(n) blanks, unless the NULLCHARVAL= option is specified

VARCHAR | LONG VARCHAR | VARGRAPHIC | LONG VARGRAPHIC empty string

SMALLINT | INT | FLOAT | DECIMAL | REAL 0

DATE current date, derived from the system clock

TIME current time, derived from the system clock

TIMESTAMP current timestamp, derived from the system clock

*The default values that are listed in this table pertain to values that are assigned by DB2.

***Default Values Assigned by DB2 for columns defined as NOT NULL WITH DEFAULT***
DB2 Column Type	DB2 Default*
CHAR(n) \| GRAPHIC(n)	blanks, unless the NULLCHARVAL= option is specified
VARCHAR \| LONG VARCHAR \| VARGRAPHIC \| LONG VARGRAPHIC	empty string
SMALLINT \| INT \| FLOAT \| DECIMAL \| REAL	0
DATE	current date, derived from the system clock
TIME	current time, derived from the system clock
TIMESTAMP	current timestamp, derived from the system clock
*The default values that are listed in this table pertain to values that are assigned by DB2.

Knowing whether a DB2 column allows NULL values or whether DB2 supplies a default value 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, the column allows NULL values.

LIBNAME Statement Data Conversions

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

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

CHAR(n) $n. (n<=254)

VARCHAR(n) $n.

$255. (n>255)

LONG VARCHAR $n.

GRAPHIC(n), VARGRAPHIC(n), LONG VARGRAPHIC $n.( n<=127)
$127. (n>127)

INTEGER m.n

SMALLINT m.n

DECIMAL(m,n) m.n

FLOAT none

NUMERIC(m,n) m.n

DATE DATE9.

TIME TIME8.

DATETIME DATETIME30.6

***LIBNAME Statement: Default SAS Formats for DB2 Data Types***
DB2 Column Type	Default SAS Format
CHAR(n)	$n. (n<=254)
VARCHAR(n)	$n. $255. (n>255)
LONG VARCHAR	$n.
GRAPHIC(n), VARGRAPHIC(n), LONG VARGRAPHIC	$n.( n<=127) $127. (n>127)
INTEGER	m.n
SMALLINT	m.n
DECIMAL(m,n)	m.n
FLOAT	none
NUMERIC(m,n)	m.n
DATE	DATE9.
TIME	TIME8.
DATETIME	DATETIME30.6

The following table shows the default DB2 data types that are assigned to SAS variable formats during output operations.

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

$w., $CHARw., $VARYINGw., $HEXw. CHARACTER

any date format DATE

any time format TIME

any datetime format TIMESTAMP

all other numeric formats FLOAT

***LIBNAME Statement: Default DB2 Data Types for SAS Variable Formats***
SAS Variable Format	DB2 Data Type
$w., $CHARw., $VARYINGw., $HEXw.	CHARACTER
any date format	DATE
any time format	TIME
any datetime format	TIMESTAMP
all other numeric formats	FLOAT

ACCESS Procedure Data Conversions

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

ACCESS Procedure: Default SAS Formats for DB2 Data Types
DB2 Column Type Default SAS Format

CHAR(n) $n. (n<=199)

VARCHAR(n) $n.

$200. (n>200)

LONG VARCHAR $n.

GRAPHIC(n), VARGRAPHIC(n), LONG VARGRAPHIC $n.( n<=127)
$127. (n>127)

INTEGER 11.0

SMALLINT 6.0

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

REAL E12.6

DOUBLE PRECISION E12.6

FLOAT(n) E12.6

FLOAT E12.6

NUMERIC(m,n) m.n

DATE DATE7.

TIME TIME8.

DATETIME DATETIME30.6

***ACCESS Procedure: Default SAS Formats for DB2 Data Types***
DB2 Column Type	Default SAS Format
CHAR(n)	$n. (n<=199)
VARCHAR(n)	$n. $200. (n>200)
LONG VARCHAR	$n.
GRAPHIC(n), VARGRAPHIC(n), LONG VARGRAPHIC	$n.( n<=127) $127. (n>127)
INTEGER	11.0
SMALLINT	6.0
DECIMAL(m,n)	m+2.s for example, DEC(6,4) = 8.4
REAL	E12.6
DOUBLE PRECISION	E12.6
FLOAT(n)	E12.6
FLOAT	E12.6
NUMERIC(m,n)	m.n
DATE	DATE7.
TIME	TIME8.
DATETIME	DATETIME30.6

Note: You can use the YEARCUTOFF= option to make your DATE7. dates comply with Year 2000 standards. For more information about this SAS system option, see SAS Language Reference: Dictionary. [cautionend]

DBLOAD Procedure Data Conversions

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

DBLOAD Procedure: Default DB2 Data Types for SAS Variable Formats
SAS Variable Format DB2 Data Type

$w., $CHARw., $VARYINGw., $HEXw. CHARACTER

any date format DATE

any time format TIME

any datetime format TIMESTAMP

all other numeric formats FLOAT

***DBLOAD Procedure: Default DB2 Data Types for SAS Variable Formats***
SAS Variable Format	DB2 Data Type
$w., $CHARw., $VARYINGw., $HEXw.	CHARACTER
any date format	DATE
any time format	TIME
any datetime format	TIMESTAMP
all other numeric formats	FLOAT

Chapter Contents
Previous
Next
Top of Page