Data Types for Informix :: SAS/ACCESS(R) 9.3 for Relational Databases: Reference, Second Edition

Overview

Every column in a table has a name and a data type. The data type tells Informix how much physical storage to set aside for the column and the form in which the data is stored. This section includes information about Informix data types, null values, and data conversions.

Character Data

CHAR(n), NCHAR(n)

contains character string data from 1 to 32,767 characters in length and can include tabs and spaces.

VARCHAR(m,n), NVARCHAR(m,n)

contains character string data from 1 to 255 characters in length.

TEXT

contains unlimited text data, depending on memory capacity.

BYTE

contains binary data of variable length.

Numeric Data

DECIMAL, MONEY, NUMERIC

contains numeric data with definable scale and precision. The amount of storage that is allocated depends on the size of the number.

FLOAT, DOUBLE PRECISION

contains double-precision numeric data up to 8 bytes.

INTEGER

contains an integer up to 32 bits (from –2³¹ to 2³¹−1).

REAL, SMALLFLOAT

contains single-precision, floating-point numbers up to 4 bytes.

SERIAL

stores sequential integers up to 32 bits.

SMALLINT

contains integers up to 2 bytes.

INT8

contains an integer up to 64 bits (–2^(63–1) to 2^(63–1)).

SERIAL8

contains sequential integers up to 64 bits.

When the length value of INT8 or SERIAL8 is greater than 15, the last few digits currently do not display correctly due to a display limitation.

Date, Time, and Interval Data

DATE

contains a calendar date in the form of a signed integer value.

DATETIME

contains a calendar date and time of day stored in 2 to 11 bytes, depending on precision.

When the DATETIME column is in an uncommon format (for example, DATETIME MINUTE TO MINUTE or DATETIME SECOND TO SECOND), the date and time values might not be displayed correctly.

INTERVAL

contains a span of time stored in 2 to 12 bytes, depending on precision.

Informix Null Values

Informix has a special value that is called NULL. An Informix NULL value means an absence of information and is analogous to a SAS missing value. When SAS/ACCESS reads an Informix NULL value, it interprets it as a SAS missing value.

If you do not indicate a default value for an Informix column, the default value is NULL. You can specify the keywords NOT NULL after the data type of the column when you create an Informix table to prevent NULL values from being stored in the column. When creating an Informix table with SAS/ACCESS, you can use the DBNULL= data set option to indicate whether NULL is a valid value for specified columns.

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 Informix assigns to SAS variables when using the LIBNAME statement to read from an Infomix table. These default formats are based on Infomix column attributes. To override these default data types, use the DBTYPE= data set option on a specific data set.

LIBNAME Statement: Default SAS Formats for Informix Data Types

Informix Column Type	Default SAS Format
CHAR(n)	$n
DATE	DATE9.
DATETIME3	DATETIME24.5
DECIMAL	m+2.n
DOUBLE PRECISION	none
FLOAT	none
INTEGER	none
INT82	none
INTERVAL	$n
MONEY	none
NCHAR(n)	$n NLS support required
NUMERIC	none
NVARCHAR(m,n)1	$m NLS support required
REAL	none
SERIAL	none
SERIAL82	none
SMALLFLOAT	none
SMALLINT	none
TEXT1	$n
VARCHAR(m,n)1	$m
1Only supported by Informix online databases.
2The precision of an INT8 or SERIAL8 is 15 digits.
3If the Informix field qualifier specifies either HOUR, MINUTE, SECOND, or FRACTION as the largest unit, the value is converted to a SAS TIME value. All other values (such as YEAR , MONTH , or DAY) are converted to a SAS DATETIME value.

The following table shows the default Informix data types that SAS/ACCESS applies to SAS variable formats during output operations when you use the LIBNAME statement.

LIBNAME Statement: Default Informix Data Types for SAS Variable Formats

SAS Variable Format		Informix Data Type
$w.		CHAR(w).
w. with SAS format name of NULL		DOUBLE
w.d with SAS format name of NULL		DOUBLE
all other numerics		DOUBLE
datetimew.d		DATETIME YEAR TO FRACTION(5)
datew.		DATE
time.		DATETIME HOUR TO SECOND

SQL Pass-Through Facility Data Conversions

The SQL pass-through facility uses the same default conversion formats as the LIBNAME statement. For conversion tables, see LIBNAME Statement Data Conversions.