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.

Supported Data Types for DB2 under UNIX and PC Hosts

Here are the data types that are supported for DB2 under UNIX and PC Hosts:

• Character data:

  CHAR(n)	LONG VARGRAPHIC
  CLOB (character large object)	VARCHAR(n)
  GRAPHIC(n)	VARGRAPHIC(n)
  LONG VARCHAR

• Numeric data:

  BIGINT	INTEGER
  DECIMAL | DEC | NUMERIC | NUM	SMALLINT
  FLOAT | DOUBLE | DOUBLE PRECISION

  Note: When performing calculations on numeric values and when storing numeric values, SAS maintains up to 15 digits of precision. When you read values that contain more than 15 decimal digits of precision from a database into SAS, the values that SAS reads are rounded to meet this condition. When you use a large numeric value in a WHERE clause, this rounding can cause unexpected results, such as not selecting desired rows. For noncomputational purposes, such as storing ID values or credit card numbers, you can read the data in as character data. For more information, see Your Options When Choosing Your Needed Degree of Precision.
• Date and time data:

  DATE	TIMESTAMP
  TIME

  Note: SQL date and time data types are collectively called datetime values. Be aware that columns of these data types can contain data values that are out of range for SAS.
• Binary data: BLOB (binary large object)

For more information about DB2 data types, see your DB2 documentation.

Handling Increased Precision with TIMESTAMP Values

With the upgrade from DB2 9.5 to DB2 9.7, support was added for greater precision with TIMESTAMP values, increasing from 6 decimal values for a second up to 12 decimal values for a second. This value can be larger than the current SAS limit on the precision of numeric values, which is how datetime values are stored. This increase in precision for DB2, combined with the current SAS limit on precision, can result in a SAS formatted value that incorrectly represents the last few decimal digits of a SAS datetime value.

To avoid this issue, you can use the SAS_DB2_TS_REDUCE_SCALE environment variable. When you specify SAS_DB2_TS_REDUCE_SCALE=YES, SAS/ACCESS Interface to DB2 defaults to a format length of 26 and a six decimal values for a second.

For more information, see Choosing Your Degree of Numeric Precision.

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 specify 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 allow a row to be added to a table only 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 specified 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 provides 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 specified 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.

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.

DBLOAD Procedure Data Conversions

This table shows the default DB2 data types that SAS/ACCESS assigns to SAS variable formats when you use the DBLOAD procedure.