Data Types for Netezza

Overview

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

For more information about Netezza data types and to determine which data types are available for your version of Netezza, see your Netezza Database User's Guide.

SAS/ACCESS Interface to Netezza does not directly support TIMETZ or INTERVAL types. Any columns using these types are read into SAS as character strings.

Supported Netezza Data Types

Here are the data types that are supported by Netezza:

• Character data:

  CHAR(n)	NVARCHAR(n)
  NCHAR(n)	VARCHAR(n)

• Numeric data:

  BIGINT	REAL
  BYTEINT	SMALLINT
  DECIMAL | DEC | NUMERIC | NUM	ST_GEOMETRY
  DOUBLE | DOUBLE PRECISION	VARBINARY
  INTEGER

  You might observe errors in the SAS log when you load large numeric values with more than 15 digits of precision. For more information, see Loading Large Numeric Values.

  Columns that use the binary data types, such as ST_GEOMETRY and VARBINARY, do not support some of the common query processing operations. For example, binary data type columns cannot be used in ordering, grouping, or in magnitude comparisons. They cannot be used in aggregates such as sum, avg, distinct, min, or max comparisons. The binary data cannot be implicitly or explicitly cast to other data types.

• Date and time data:

  DATE

  TIME

  DATETIME

  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.

Loading Large Numeric Values

Be aware that 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 are read are rounded to meet this condition. 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.

By default, when SAS/ACCESS loads numeric data with more than 15 digits of precision, errors are written to the log and the data fails to load. You can choose to load the data and suppress the errors by setting the TRUNCATE_BIGINT environment variable to YES before you start SAS.

Default SAS Formats for Netezza NUMERIC Data Types

This table shows the correlation between the Netezza NUMERIC data types and the default SAS formats that are created from that data type.

The general form of a Netezza number is NUMERIC(p,s) where p is the precision and s is the scale of the number. Netezza specifies precision as the total number of digits, with a valid range of –84 to 127. However, a negative scale means that the number is rounded to the specified number of places to the left of the decimal. For example, if the number 1,234.56 is specified as data type NUMERIC(8,–2), it is rounded to the nearest hundred and stored as 1,200.

Netezza Null Values

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

You can specify a column in a Netezza table so that it requires data. To do this in SQL, you specify a column as NOT NULL. This tells SQL to allow only a row to be added to a table if a value exists for the field. For example, NOT NULL assigned to the CUSTOMER field in the SASDEMO.CUSTOMER table does not allow a row to be added unless there is a value for CUSTOMER. When creating a Netezza 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 specify Netezza columns as NOT NULL DEFAULT. For more information about using the NOT NULL DEFAULT value, see your Netezza Database User's Guide.

Once you know whether a Netezza column enables NULLs or the host system provides a default value for a column that is specified 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 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 Netezza assigns to SAS variables when using the LIBNAME statement to read from a Netezza table. These default formats are based on Netezza column attributes.

This table shows the default Netezza data types that SAS/ACCESS assigns to SAS variable formats during output operations when you use the LIBNAME statement.