| What's New |
| Overview |
SAS/ACCESS 9.2 for Relational Databases has these new features and enhancements:
In the second maintenance release for SAS 9.2, SAS/ACCESS Interface to Greenplum and SAS/ACCESS Interface to Sybase IQ are new. In the December 2009 release, SAS/ACCESS Interface to Aster nCluster is new.
Pass-through support is available for database management systems (DBMSs) for new or additional SAS functions. This support includes new or enhanced function for the SQL_FUNCTIONS= LIBNAME option, a new SQL_FUNCTIONS_COPY= LIBNAME option for specific DBMSs, and new or enhanced hyperbolic, trigonometric, and dynamic SQL dictionary functions.
You can create temporary tables using DBMS-specific syntax with the new DBMSTEMP= LIBNAME option for most DBMSs.
SAS/ACCESS supports additional hosts for existing DBMSs.
You can use the new SAS In-Database technology to generate a SAS_PUT() function that lets you execute PUT function calls inside the Teradata Enterprise Data Warehouse (EDW). You can also reference the custom formats that you create by using PROC FORMAT and most formats that SAS supplies.
In the second maintenance release for SAS 9.2, you can use the new SAS In-Database technology to run some Base SAS and SAS/STAT procedures inside the Teradata Enterprise Data Warehouse (EDW).
In the third maintenance release for SAS 9.2, three additional Base SAS procedures have been enhanced to run inside the database: REPORT, SORT, and TABULATE. In addition, the Base SAS procedures can be run now inside Oracle and DB2 UNIX and PC Hosts.
The second maintenance release for SAS 9.2 contains Documentation Enhancements.
| All Supported SAS/ACCESS Interfaces to Relational Databases |
These options are new.
AUTHDOMAIN= LIBNAME option
DBIDIRECTEXEC= system option, including DELETE statements
brief trace capability (',,,db' flag) on the SASTRACE= system option
To boost performance when reading large tables, you can set the OBS= option to limit the number of rows that the DBMS returns to SAS across the network.
Implicit pass-through tries to reconstruct the textual representation of a SAS SQL query in database SQL syntax. In the second maintenance release for SAS 9.2, implicit pass-through is significantly improved so that you can pass more SQL code down to the database. These textualization improvements have been made.
aliases for:
inline views
SQL views
tables
aliased expressions
expressions that use the CALCULATED keyword
SELECT, WHERE, HAVING, ON, GROUP BY, and ORDER BY clauses
more deeply nested queries or queries involving multiple data sources
PROC SQL and ANSI SQL syntax
| DBMS-Specific New Function and Enhancements |
| SAS/ACCESS Interface to Aster nCluster |
In the December 2009 release for SAS 9.2, SAS/ACCESS Interface to Aster nCluster is a new database engine that runs on specific UNIX and Windows platforms. SAS/ACCESS Interface to Aster nCluster provides direct, transparent access to Aster nCluster databases through LIBNAME statements and the SQL pass-through facility. You can use various LIBNAME statement options and data set options that the LIBNAME engine supports to control the data that is returned to SAS.
In the third maintenance release for SAS 9.2, this option is new or enhanced:
PARTITION_KEY= LIBNAME and data set option
| SAS/ACCESS Interface to DB2 under UNIX and PC Hosts |
These options are new or enhanced.
FETCH_IDENTITY= LIBNAME and data set options
automatically calculated INSERTBUFF= and READBUFF= LIBNAME options for use with pass-through
SQLGENERATION= LIBNAME and system option (in the third maintenance release for SAS 9.2)
These bulk-load data set options are new:
BL_ALLOW_READ_ACCESS=
BL_ALLOW_WRITE_ACCESS=
BL_CPU_PARALLELISM=
BL_DATA_BUFFER_SIZE=
BL_DELETE_DATAFILE=
BL_DISK_PARALLELISM=
BL_EXCEPTION=
BL_PORT_MAX=
BL_PORT_MIN=
BLOB and CLOB data types are new.
In the third maintenance release for SAS 9.2, this new feature is available.
You can use the new SAS In-Database technology to run these Base SAS procedures inside DB2 under UNIX and PC Hosts:
FREQ
RANK
REPORT
SORT
SUMMARY/MEANS
TABULATE
These procedures dynamically generate SQL queries that reference DB2 SQL functions. Queries are processed and only the result set is returned to SAS for the remaining analysis.
| SAS/ACCESS Interface to DB2 under z/OS |
These options are new or enhanced.
DB2CATALOG= system option
support for multivolume SMS-managed and non-SMS-managed data sets through BL_DB2DATACLAS=, BL_DB2MGMTCLAS=, BL_DB2STORCLAS=, and BL_DB2UNITCOUNT= data set options
DB2 parallelism through the DEGREE= data set option
LOCATION= connection, LIBNAME, and data set options
The BLOB and CLOB data types are new.
In the third maintenance release for SAS 9.2, SAS/ACCESS Interface to DB2 under z/OS included many important overall enhancements, such as:
significant performance improvements
reduced overall memory consumption
improved buffered reads
improved bulk-loading capability
improved error management, including more extensive tracing and the ability to retrieve multiple error messages for a single statement at once
extended SQL function support
dynamic SQL dictionary
EXPLAIN functionality
database read-only access support
IBM z/OS is the successor to the IBM OS/390 (formerly MVS) operating system. SAS/ACCESS 9.1 and later for z/OS is supported on both OS/390 and z/OS operating systems. Throughout this document, any reference to z/OS also applies to OS/390 unless otherwise stated.
| SAS/ACCESS Interface to Greenplum |
In the October 2009 release for SAS 9.2, SAS/ACCESS Interface to Greenplum is a new database engine that runs on specific UNIX and Windows platforms. SAS/ACCESS Interface to Greenplum provides direct, transparent access to Greenplum databases through LIBNAME statements and the SQL pass-through facility. You can use various LIBNAME statement options and data set options that the LIBNAME engine supports to control the data that is returned to SAS.
| SAS/ACCESS Interface to HP Neoview |
You can use the new BULKEXTRACT= LIBNAME and data set options, as well as these new data set options for bulk loading and extracting:
BL_BADDATA_FILE=
BL_DATAFILE=
BL_DELIMITER=
BL_DISCARDS=
BL_ERRORS=
BL_DELETE_DATAFILE=
BL_FAILEDDATA=
BL_HOSTNAME=
BL_NUM_ROW_SEPS= LIBNAME and data set options (in the third maintenance release for SAS 9.2)
BL_PORT=
BL_RETRIES=
BL_ROWSETSIZE=
BL_STREAMS=
BL_SYNCHRONOUS=
BL_SYSTEM=
BL_TENACITY=
BL_TRIGGER=
BL_TRUNCATE=
BL_USE_PIPE=
BULKEXTRACT=
BULKLOAD=
| SAS/ACCESS Interface to Informix |
These items are new.
AUTOCOMMIT= LIBNAME option
GLOBAL and SHARED options for the CONNECTION= LIBNAME option
DBSASTYPE= data set option
DBDATASRC environmental variable
DATEPART and TIMEPART SAS functions
support for special characters in naming conventions
| SAS/ACCESS Interface to MySQL |
The ESCAPE_BACKSLASH= LIBNAME and data set options are new.
| SAS/ACCESS Interface to Netezza |
The BULKUNLOAD= LIBNAME option is new.
In the third maintenance release for SAS 9.2, you can specify a database other than SASLIB in which to publish the SAS_COMPILEUDF function. If you publish the SAS_COMPILEUDF function to a database other than SASLIB, you must specify that database in the new COMPILEDB argument for the %INDNZ_PUBLISH_FORMATS macro.
In the third maintenance release for SAS 9.2, the SAS_PUT() function supports UNICODE (UTF8) encoding.
| SAS/ACCESS Interface to ODBC |
These items are new.
LOGIN_TIMEOUT= LIBNAME option
READBUFF= data set option, LIBNAME option, and pass-through support for improved performance
| SAS/ACCESS Interface to OLE DB |
These items are new.
GLOBAL and SHARED options for the CONNECTION= LIBNAME option
BULKLOAD= data set option
DBTYPE_GUID and DBTYPE_VARIANT input data types
| SAS/ACCESS Interface to Oracle |
These items are new.
ADJUST_BYTE_SEMANTIC_COLUMN_LENGTHS=, ADJUST_NCHAR_COLUMN_LENGTHS=, DB_LENGTH_SEMANTICS_BYTE=, DBCLIENT_MAX_BYTES=, and DBSERVER_MAX_BYTES= LIBNAME options for more flexible adjustment of column lengths with CHAR, NCHAR, VARCHAR, and NVARCHAR data types to match encoding on both database and client servers
BL_DELETE_ONLY_DATAFILE= data set option
GLOBAL and SHARED options for the CONNECTION= LIBNAME option
OR_ENABLE_INTERRUPT= LIBNAME option
BL_DEFAULT_DIR= data set option
BL_USE_PIPE= data set option
function and default value for SHOW_SYNONYMS= LIBNAME option
SQLGENERATION= LIBNAME and system option (in the third maintenance release for SAS 9.2)
In the third maintenance release for SAS 9.2, this new feature is available:
You can use the new SAS In-Database technology to run these Base SAS procedures inside Oracle:
FREQ
RANK
REPORT
SORT
SUMMARY/MEANS
TABULATE
These procedures dynamically generate SQL queries that reference Oracle SQL functions. Queries are processed and only the result set is returned to SAS for the remaining analysis.
| SAS/ACCESS Interface to Sybase |
These LIBNAME options are new or enhanced.
GLOBAL and SHARED options for CONNECTION=
SQL_FUNCTIONS= and SQL_FUNCTIONS_COPY=
SQL_OJ_ANSI=
Pass-through support is available for new or additional SAS functions, including hyperbolic, trigonometric, and dynamic SQL dictionary functions.
| SAS/ACCESS Interface to Sybase IQ |
In the December 2009 release for SAS 9.2, SAS/ACCESS Interface to Sybase IQ is a new database engine that runs on specific UNIX and Windows platforms. SAS/ACCESS Interface to Sybase IQ provides direct, transparent access to Sybase IQ databases through LIBNAME statements and the SQL pass-through facility. You can use various LIBNAME statement options and data set options that the LIBNAME engine supports to control the data that is returned to SAS.
| SAS/ACCESS Interface to Teradata |
These options are new or enhanced.
BL_CONTROL= and BL_DATAFILE= data set options
GLOBAL and SHARED options for the CONNECTION= LIBNAME option
DBFMTIGNORE= system option for bypassing Teradata data type hints based on numeric formats for output processing (in the second maintenance release for SAS 9.2)
DBSASTYPE= data set option
FASTEXPORT= LIBNAME options
MODE= LIBNAME option (in the second maintenance release for SAS 9.2)
MULTISTMT= LIBNAME and data set option
QUERY_BAND= LIBNAME and data set options
SQLGENERATION= LIBNAME and system option (in the second maintenance release for SAS 9.2)
The Teradata Parallel Transporter (TPT) application programming interface (API) is now supported for loading and reading data using Teradata load, update, stream, and export drivers. This support includes these new options:
TPT= LIBNAME and data set options
TPT_APPL_PHASE= data set option
TPT_BUFFER_SIZE= data set option
TPT_CHECKPOINT_DATA= data set option
TPT_DATA_ENCRYPTION= data set option
TPT_ERROR_TABLE_1= data set option
TPT_ERROR_TABLE_2= data set option
TPT_LOG_TABLE= data set option
TPT_MAX_SESSIONS= data set option
TPT_MIN_SESSIONS= data set option
TPT_PACK= data set option
TPT_PACKMAXIMUM= data set option
TPT_RESTART= data set option
TPT_TRACE_LEVEL= data set option
TPT_TRACE_LEVEL_INF= data set option
TPT_TRACE_OUTPUT= data set option
TPT_WORK_TABLE= data set option
LDAP function for the USER= and PASSWORD= connection options in the LIBNAME statement
You can use a new SAS formats publishing macro, %INDTD_PUBLISH_FORMATS, and a new system option, SQLMAPPUTTO, to generate a SAS_PUT() function that enables you to execute PUT function calls inside the Teradata EDW. You can also reference the custom formats that you create by using PROC FORMAT and most of the formats that SAS supplies.
In the second maintenance release for SAS 9.2, this new feature is available.
You can use the new SAS In-Database technology to run these Base SAS and SAS/STAT procedures inside the Teradata Enterprise Data Warehouse (EDW):
FREQ
PRINCOMP
RANK
REG
SCORE
SUMMARY/MEANS
VARCLUS
These procedures dynamically generate SQL queries that reference Teradata SQL functions and, in some cases, SAS functions that are deployed inside Teradata. Queries are processed and only the result set is returned to SAS for the remaining analysis.
In the third maintenance release for SAS 9.2, these procedures have been enhanced to run inside the Teradata EDW:
REPORT
SORT
TABULATE
In the third maintenance release for SAS 9.2, the SAS_PUT() function supports UNICODE (UCS2) encoding.
| Documentation Enhancements |
In addition to information about new and updated features, this edition of SAS/ACCESS for Relational Databases: Reference includes information about these items:
BL_RETURN_WARNINGS_AS_ERRORS= data set option
DB_ONE_CONNECT_PER_THREAD data set option for Oracle (in the third maintenance release for SAS 9.2)
DBSERVER_MAX_BYTES= LIBNAME option for Oracle and Sybase
SESSIONS= LIBNAME and data set options for Teradata
special queries for data sources and DBMS info for DB2 under UNIX and PC Hosts and ODBC
significant performance improvement when you work with large tables by using the OBS= option to transmit a limited number of rows across the network
the importance of choosing the degree of numeric precision that best suits your business needs
Copyright © 2010 by SAS Institute Inc., Cary, NC, USA. All rights reserved.