What's New in SAS/ACCESS 9.2 for Relational Databases

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.

• 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 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. For more information, see Deploying and Using SAS Formats in Teradata.

• 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). For more information, see In-Database Procedures in Teradata.

• The second maintenance release for SAS 9.2 contains Documentation Enhancements.

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

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

These bulk-load data set options are new:

• BL_ALLOW_READ_ACCESS=

• BL_ALLOW_WRITE_ACCESS=

• BL_CPU_PARALLELISM=

• BL_DATA_BUFFER_SIZE=

• BL_DISK_PARALLELISM=

• BL_EXCEPTION=

• BL_PORT_MAX=

• BL_PORT_MIN=

BLOB and CLOB data types are new.

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.

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 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 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.

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_PORT=

• BL_RETRIES=

• BL_ROWSETSIZE=

• BL_STREAMS=

• BL_SYNCHRONOUS=

• BL_SYSTEM=

• BL_TENACITY=

• BL_TRIGGER=

• BL_TRUNCATE=

• BL_USE_PIPE=

• BULKEXTRACT=

• BULKLOAD=

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

The ESCAPE_BACKSLASH= LIBNAME and data set options are new.

The BULKUNLOAD= LIBNAME option is new.

These items are new.

• LOGIN_TIMEOUT= LIBNAME option

• READBUFF= data set option, LIBNAME option, and pass-through support for improved performance

These items are new.

• GLOBAL and SHARED options for the CONNECTION= LIBNAME option

• BULKLOAD= data set option

• DATATYPE=GUID input data type

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_USE_PIPE= data set option

• function and default value for SHOW_SYNONYMS= LIBNAME option

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 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 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.

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

• DBSASTYPE= data set option

• FASTEXPORT= LIBNAME options

• MODE= LIBNAME option

• MULTISTMT= LIBNAME and data set option

• QUERY_BAND= LIBNAME and data set options

• SQLGENERATION= LIBNAME and system option

• 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, these new features are 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 In-Database Procedures in Teradata and the specific procedure in either the Base SAS Procedures Guide or the SAS/STAT User's Guide.

In addition to information about new and updated features, this edition of SAS/ACCESS for Relational Databases: Reference now also includes information about these items:

• DBSERVER_MAX_BYTES= LIBNAME option for 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 © 2009 by SAS Institute Inc., Cary, NC, USA. All rights reserved.