 |
|
| SAS/ACCESS Interface to DB2 Under UNIX and PC Hosts |
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.
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 about how SAS handles NULL values, see Potential Result Set Differences When Processing Null Data.
To control how DB2 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.
| DB2 Data Type | SAS Data Type | Default SAS Format |
|---|---|---|
| BLOB | character | $HEXn. |
| CLOB | character | $n. |
|
CHAR(n)
VARCHAR(n LONG VARCHAR |
character | $n. |
|
GRAPHIC(n)
VARGRAPHIC(n) 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 |
| * n in DB2 data types is equivalent to w in SAS formats. | ||
The following table shows the default DB2 data types that SAS/ACCESS assigns to SAS variable formats during output operations when you use the LIBNAME statement.
| SAS Variable Format | DB2 Data Type |
|---|---|
| m.n | DECIMAL (m,n) |
| other numerics | DOUBLE |
| $n. | 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. | |
| 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.
| SAS Variable Format | DB2 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. |
||
|
|
Copyright © 2010 by SAS Institute Inc., Cary, NC, USA. All rights reserved.