 |
|
| What's New |
What's New in SAS/ACCESS 9.2 for Relational Databases
| 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. For more information, see the SQL_FUNCTIONS= LIBNAME Option, SQL_FUNCTIONS_COPY= LIBNAME Option, and Passing Functions to the DBMS Using PROC SQL.
-
You can create temporary tables using DBMS-specific syntax with the new DBMSTEMP= LIBNAME option for most DBMSs. For more information, see the DBMSTEMP= LIBNAME Option.
-
SAS/ACCESS supports additional hosts for existing DBMSs. For more information, see SAS/ACCESS Features by Host.
-
You can use the new SAS In-Database technology to generate a SAS_PUT() function that lets you execute PUT function calls inside Teradata, Netezza, and DB2 under UNIX. You can also reference the custom formats that you create by using PROC FORMAT and most formats that SAS supplies. For more information, see Deploying and Using SAS Formats in Teradata, Deploying and Using SAS Formats in Netezza, and LINK HERE.
-
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 Teradata, DB2 under UNIX and PC Hosts, and Oracle. For more information, see Overview of In-Database Procedures.
-
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. Three additional SAS/STAT procedures have been enhanced to run inside the database: CORR, CANCORR, and FACTOR. In addition, the Base SAS procedures can be run now inside Oracle and DB2 UNIX and PC Hosts. For more information, see Overview of In-Database Procedures.
-
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.
For more information, see SAS/ACCESS Interface to Aster nCluster and SAS/ACCESS Interface to Aster nCluster: Supported Features.
In the third maintenance release for SAS 9.2, these options are new or enhanced:
| SAS/ACCESS Interface to DB2 under UNIX and PC Hosts |
These options are new or enhanced.
-
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:
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.
For more information, see Overview of In-Database Procedures and the specific procedure in the Base SAS Procedures Guide.
-
| 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.
For more information, see SAS/ACCESS Interface to Greenplum and SAS/ACCESS Interface to Greenplum: Supported Features.
| 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:
| 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.
In the June 2010 release, the SAS/ACCESS Interface to Netezza supports the Netezza TwinFin system. The new Netezza TwinFin system adds supports for shared libraries. The shared library technology makes the scoring functions more efficient and robust. In addition, the use of SFTP for file transfer during the format publishing process has been replaced with the Netezza External Table Interface.
| 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, you can use the new SAS In-Database technology feature 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.
For more information, see Overview of In-Database Procedures and the specific procedure in the Base SAS Procedures Guide.
-
| SAS/ACCESS Interface to Sybase |
These LIBNAME options are new or enhanced.
-
GLOBAL and SHARED options for CONNECTION=
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.
For more information, see SAS/ACCESS Interface to Sybase IQ and SAS/ACCESS Interface to Sybase IQ: Supported Features.
| 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)
-
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_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.
For more information, see Overview of In-Database Procedures and the specific procedure in either the Base SAS Procedures Guide or the SAS Analytics Accelerator for Teradata: User's Guide.
-
In the third maintenance release for SAS 9.2, these procedures have been enhanced to run inside the Teradata EDW:
-
CORR
-
CANCORR
-
FACTOR
-
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
-
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.