 |
|
| SAS/ACCESS Interface to DB2 Under z/OS |
Data Types for DB2 Under z/OS
| 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. This section includes information about DB2 data types, NULL and default values, and data conversions.
For more information about DB2 data types, see your DB2 SQL reference documentation.
SAS/ACCESS Interface to DB2 under z/OS supports all DB2 data types.
| 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. Like 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 of length n for character string data. The maximum for n is 255.
- VARCHAR(n)
-
specifies a varying-length column for character string data. n specifies the maximum length of the string. If n is greater than 255, 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 |
- 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 -32,768 to +32,767.
- INTEGER | INT
-
specifies a large integer. Values in a column of this type can range from -2,147,483,648 to +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]](../../../../common/63294/HTML/default/images/le.gif)
s p.If s is omitted, 0 is assigned and p might also be omitted. Omitting both s and p results in the default DEC(5,0). The maximum range of p is 1 -1031 to 1031 -1.
Even though the DB2 numeric columns have these distinct data types, the DB2 engine accesses, inserts, and loads all numerics as FLOATs.
| Date, Time, and Timestamp Data |
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 might contain data values that are out of range for SAS, 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 Null and Default Values |
DB2 has a special value that is 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.
DB2 columns can be defined so that they do not allow NULL data. For example, NOT NULL would indicate that DB2 does not allow a row to be added to the TestID.Customers table 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.
You can also define DB2 columns as NOT NULL WITH DEFAULT. The following table lists the default values that DB2 assigns to columns that you define 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.
| 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 DB2 assigns. | |
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.
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 Column Type | Default SAS Format |
|---|---|
| BLOB | $HEXn. |
| CLOB | $n. |
|
CHAR(n)
VARCHAR(n) LONG VARCHAR(n) |
$n |
|
GRAPHIC(n)
VARGRAPHIC(n) LONG VARGRAPHIC |
$n.( 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 |
This table shows the default DB2 data types that SAS/ACCESS assigns to SAS variable formats during output operations.
| SAS Variable Format | DB2 Data Type |
|---|---|
|
$w.
$CHARw. $VARYINGw. $HEXw. |
CHARACTER(w) for 1-255 |
| 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 variable formats that SAS/ACCESS assigns to DB2 data types when you use the ACCESS procedure.
| DB2 Column Type | Default SAS Format |
|---|---|
| CHAR(n) | $n. (n<=199) |
| VARCHAR(n) | $n. |
| LONG VARCHAR | $n. |
|
GRAPHIC(n)
VARGRAPHIC(n) LONG VARGRAPHIC |
$n.( n<=127) |
| INTEGER | 11.0 |
| SMALLINT | 6.0 |
| DECIMAL(m,n) | m+2.s |
| 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 |
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.
| 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.
$CHARw. $VARYINGw. $HEXw. |
CHARACTER |
| any date format | DATE |
| any time format | TIME |
| any datetime format | TIMESTAMP |
| all other numeric formats | FLOAT |
|
|
Copyright © 2010 by SAS Institute Inc., Cary, NC, USA. All rights reserved.