What's New Table of Contents |
What's New in SAS/ACCESS 9.0, 9.1, 9.1.2, and 9.1.3 Interfaces to Relational Databases
Overview
SAS/ACCESS Interfaces for Relational Databases has the following changes and enhancements:
- Beginning with SAS 9.1.3 Service Pack 4, SAS/ACCESS Interface for HP Neoview and SAS/ACCESS Interface for Netezza are new, including documentation enhancements.
- Beginning with SAS 9.1.3, SAS/ACCESS supports additional hosts for existing DBMSs.
- Beginning with SAS 9.1.2, SAS/ACCESS Interface for MySQL databases is new.
- Beginning with SAS 9.0, you can use threaded reads to complete jobs in substantially less time than if each task is handled sequentially.
- The new CV2VIEW procedure converts SAS/ACCESS view descriptors into SQL views.
All Supported SAS/ACCESS Interfaces to Relational Databases
- You can use the CHANGE statement to rename SAS/ACCESS tables.
- The CV2VIEW procedure converts SAS/ACCESS view and access descriptors to the SAS 9.0 format. It can also convert a view descriptor to a SAS 9.0 SQL view. As SAS/ACCESS moves forward with LIBNAME enhancements and tighter integration with the SAS Open Metadata Repository, SAS/ACCESS views no longer is the method of choice.
- The DBMASTER= data set option designates which table is the master table when you are processing a join that involves tables from two different types of databases.
- DBMS metadata can now be accurately maintained within the SAS Open Metadata Repository.
- You can now encode the DBMS password that appears in SAS source code so that it does not appear as text in SAS programs.
- You can use the DIRECT_EXE= LIBNAME option to pass an SQL statement directly to a database by using explicit pass-through when you use PROC SQL with a libref.
- The new SQL MULTI_DATASRC_OPT= LIBNAME option and the DBMASTER= data set option optimize the performance of the SQL procedure. For more detailed information, see the passing joins to the DBMS, determining when joins will fail, and optimizing WHERE clauses topics.
- You can use the MULTI_DATASRC_OPT= LIBNAME option instead of the DBKEY= data set option to improve performance when you process a join between two data sources.
- The SASTRACE= system option now provides improved debugging capabilities.
- With temporary table support, DBMS temporary tables can persist from one SAS step to the next. It involves establishing a SAS connection to the DBMS that persists across SAS procedures and DATA steps.
- Threaded reads divide resource-intensive tasks into multiple independent units of work and execute those units in parallel.
- SAS/ACCESS supports these hosts:
-
Beginning with SAS 9.0, support for these items
is discontinued:
OS/2, OpenVMS VAX, MIPS ABI, Intel ABI, UNIX MP-RAS, and CMS operating environments
SAS/ACCESS Interface to DB2 under UNIX and PC Hosts
The following options are new:
- The DBSLICE= data set option specifies user-supplied WHERE clauses to partition a DBMS query into component queries for threaded reads.
- The DBSLICEPARM= LIBNAME option and data set option control the scope of DBMS-threaded reads and the number of threads.
- The IGNORE_READ_ONLY_COLUMNS= LIBNAME option and data set option specify whether to ignore or include columns whose data types are read-only when generating an SQL statement for inserts or updates.
- The SQL_FUNCTIONS= LIBNAME option specifies that the SQL functions that match the functions that SAS supports are passed to the DBMS for processing.
SAS/ACCESS Interface to DB2 under z/OS
Note: z/OS is the successor to the OS/390 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 DB2 under z/OS features stored procedure support that includes passing input parameters, retrieving output parameters into SAS macro variables, and retrieving result sets into SAS tables.
The following options are new:
- The BL_DB2CURSOR= data set option specifies a string that contains a valid DB2 SELECT statement that points to either local or remote objects (tables or views). After your database administrator populates the communication database with the appropriate entries, you can select data from a remote location to load DB2 tables directly from other DB2 and non-DB2 objects.
- The BL_DB2LDCT3= data set option specifies a string in the LOAD utility control statement, following the field specification.
- The DBSLICE= data set option specifies user-supplied WHERE clauses to partition a DBMS query into component queries for threaded reads.
- The DBSLICEPARM= LIBNAME option and data set option control the scope of DBMS threaded reads and the number of threads.
- The DEGREE= LIBNAME option determines whether DB2 uses parallelism.
- The REMOTE_DBTYPE= LIBNAME option ensures that the SQL that some SAS procedures use to access the DB2 catalog tables is generated properly, based on the database server type.
- The TRAP151= data set option removes columns that cannot be updated from a FOR UPDATE OF clause so that column update can continue.
SAS/ACCESS Interface to HP Neoview
SAS/ACCESS Interface to HP Neoview is a new database engine that runs on specific UNIX and Windows platforms. SAS/ACCESS Interface to HP Neoview provides direct, transparent access to HP Neoview databases through LIBNAME statements or 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.
SAS/ACCESS Interface to Informix
The following options are new:
- The DBSLICE= data set option specifies user-supplied WHERE clauses to partition a DBMS query into component queries for threaded reads.
- The DBSLICEPARM= LIBNAME option and data set option control the scope of DBMS threaded reads and the number of threads.
SAS/ACCESS Interface to Microsoft SQL Server
The following options are new:
- The DBSLICE= data set option specifies user-supplied WHERE clauses to partition a DBMS query into component queries for threaded reads.
- The DBSLICEPARM= LIBNAME option and data set option control the scope of DBMS threaded reads and the number of threads.
- The ERRLIMIT= LIBNAME option specifies the number of errors that are allowed while using the Fastload utility before SAS stops loading data to Teradata.
- The IGNORE_READ_ONLY_COLUMNS= LIBNAME option and data set option specify whether to ignore or include columns where data types are read-only when generating an SQL statement for inserts or updates.
SAS/ACCESS Interface to MySQL
Beginning with SAS 9.1.3 Service Pack 4, you can use the INSERTBUFF= LIBNAME option and data set option to specify the number of rows in a single insert operation.
Beginning with SAS 9.1.2, SAS/ACCESS Interface to MySQL is new. MySQL software is an open-source SQL database server that runs on 32-bit Windows systems, 64-bit HP systems, and 64-bit Solaris systems. SAS/ACCESS Interface to MySQL provides direct transparent access to MySQL databases through LIBNAME statements or the Pass-Through Facility. To control data that is returned to SAS, you can use various LIBNAME and data set options that the LIBNAME engine supports.
SAS/ACCESS Interface to Netezza
Beginning with SAS 9.1.3 Service Pack 4, SAS/ACCESS Interface to Netezza is a new database engine that runs on specific UNIX and Windows platforms. SAS/ACCESS Interface to Netezza provides direct, transparent access to Netezza databases through LIBNAME statements or the Pass-Through Facility. To control data that is returned to SAS, you can use various LIBNAME and data set options that the LIBNAME engine supports.
SAS/ACCESS Interface to ODBC
The following feature and options are new:
- ODBC 3.x standard API is supported.
- The DBSLICE= data set option specifies user-supplied WHERE clauses to partition a DBMS query into component queries for threaded reads.
- The DBSLICEPARM= LIBNAME option and data set option control the scope of DBMS threaded reads and the number of threads.
- The IGNORE_READ_ONLY_COLUMNS= LIBNAME option and data set option specify whether to ignore or include columns whose data types are read-only when generating an SQL statement for inserts or updates.
- The SQL_FUNCTIONS= LIBNAME option specifies that the SQL functions that match the functions that SAS supports by SAS are passed to the DBMS.
SAS/ACCESS Interface to OLE DB
The following options are new:
- The IGNORE_READ_ONLY_COLUMNS= LIBNAME option and data set option specify whether to ignore or include columns whose data types are read-only when generating an SQL statement for inserts or updates.
- The INSERTBUFF= LIBNAME option specifies the number of rows in a single insert operation.
- The SQL_FUNCTIONS= LIBNAME option specifies that the SQL functions that match the functions that SAS supports are passed to the DBMS.
SAS/ACCESS Interface to Oracle
The following options are new:
- You can use the BL_INDEX_OPTIONS= data set option to specify SQL*Loader Index options with bulk loading. You can boost performance by specifying the SORTED INDEXES index option.
- The BL_PARFILE= data set option creates a file that contains the SQL*Loader command line options.
- You can use the BL_RECOVERABLE= data set option to specify whether the load process is recoverable. It enhances the performance of the bulk load.
- To improve performance, you can use the BL_SUPPRESS_NULLIF= data set option to specify whether to suppress the NULLIF clause for specified columns when a table is created.
-
The DBSLICE= data set option specifies user-supplied WHERE clauses to partition a DBMS query into component queries for threaded reads.
- The DBSLICEPARM= LIBNAME option and data set option control the scope of DBMS threaded reads and the number of threads.
- You can use the OR_UPD_PARTITION= data set option to read, update, and delete data from a specific partition in a partitioned table, as well as insert and bulk-load data into a specific partition in a partitioned table. It also boosts performance.
- In addition to being a LIBNAME option, OR_UPD_NOWHERE= is now also a data set option.
SAS/ACCESS Interface to Sybase
The following options are new:
- The DBSLICE= data set option specifies user-supplied WHERE clauses to partition a DBMS query into component queries for threaded reads.
- The DBSLICEPARM= LIBNAME option and data set option control the scope of DBMS threaded reads and the number of threads.
SAS/ACCESS Interface to Teradata
The following features and options are new:
- The DBSLICE= data set option specifies user-supplied WHERE clauses to partition a DBMS query into component queries for threaded reads.
- The DBSLICEPARM= LIBNAME option and data set option control the scope of DBMS threaded reads and the number of threads.
- The ERRLIMIT= LIBNAME option specifies the number of errors that are allowed while using the Fastload utility before SAS stops loading data to Teradata.
- The LOGDB= LIBNAME option specifies the name of an alternate database where the restart log tables from the Teradata FastExport utility should be created.
- You can extract data faster with the Teradata FastExport utility.
- SAS/ACCESS Interface to Teradata supports the MultiLoad bulk-load facility. MultiLoad greatly accelerates insertion of data into both empty and existing Teradata tables. The SAS/ACCESS MultiLoad facility is similar to the native Teradata MultiLoad. You invoke the MultiLoad facility with the MULTILOAD= data set option. MULTILOAD= works together with several other new MultiLoad data set options.
- Beginning with SAS 9.1.3 Service Pack 4, when processing WHERE statements that contain literal values for TIME or TIMESTAMP, the SAS engine passes the values to Teradata exactly as they were entered, without rounding or truncation.
- Beginning with SAS 9.1, specifying OBS=n causes SAS/ACCESS Interface to Teradata to append SAMPLE n to the SQL that Teradata generates.
Documentation Enhancements
Two new supplements are available:
- SAS/ACCESS Supplement for Netezza (SAS/ACCESS for Relational Databases)
- SAS/ACCESS Supplement for HP Neoview (SAS/ACCESS for Relational Databases)
These supplements explain how SAS/ACCESS software is customized to accommodate the particular requirements and capabilities of each respective DBMS. When you use them with the separately available SAS/ACCESS for Relational Databases: Reference, they provide comprehensive guides for using SAS/ACCESS features and options for the DBMSs. These titles are available from SAS OnlineDoc 9.1.3 on the Web and are available for purchase from the SAS Publications Catalog.
In addition, the DISTRIBUTE_ON= data set option is new in SAS/ACCESS Supplement for Netezza (SAS/ACCESS for Relational Databases) and SAS/ACCESS for Relational Databases: Reference.