SAS/ACCESS 9.2 for Relational Databases: …

1 SAS/ACCESS Relational DatabasesReferenceFourth edition Here is the correct bibliographic citation for this document: SAS Institute Inc. for Relational Databases: reference , fourth edition . Cary, NC: SASI nstitute for Relational Databases: reference , fourth EditionCopyright 2010, SAS Institute Inc., Cary, NC, USAISBN 978-1-60764-619-8 All rights reserved. Produced in the United States of a hard-copy book:No part of this publication may be reproduced, stored in aretrieval system, or transmitted, in any form or by any means, electronic, mechanical,photocopying, or otherwise, without the prior written permission of the publisher, SASI nstitute a Web download or e-book:Your use of this publication shall be governed by theterms established by the vendor at the time you acquire this Government Restricted Rights , duplication, or disclosure of thissoftware and related documentation by the government is subject to the Agreementwith SAS Institute and the restrictions set forth in FAR Commercial ComputerSoftware-Restricted Rights (June 1987).

2 SAS Institute Inc., SAS Campus Drive, Cary, North Carolina electronic book, November 20101st printing, November 2010 SAS Publishing provides a complete selection of books and electronic products to helpcustomers use SAS software to its fullest potential. For more information about oure-books, e-learning products, CDs, and hard-copy books, visit the SAS Publishing Web call and all other SAS Institute Inc. product or service names are registered trademarksor trademarks of SAS Institute Inc. in the USA and other countries. indicates brand and product names are registered trademarks or trademarks of theirrespective s NewxiiiOverviewxiiiAll Supported SAS/ACCESS Interfaces to Relational DatabasesxivSAS/ACCESS Interface to AsternClusterxivSAS/ACCESS Interface to DB2 under UNIX and PC HostsxivSAS/ACCESS Interface to DB2 under z/OSxvSAS/ACCESS Interface to GreenplumxviSAS/ACCESS Interface to HP NeoviewxviSAS/ACCESS Interface to InformixxviiSAS/ACCESS Interface to MySQLxviiSAS/ACCESS Interface to NetezzaxviiSAS/ACCESS Interface to ODBC xviiSAS/ACCESS Interface to OLE DBxviiSAS/ACCESS Interface to OraclexviiiSAS/ACCESS Interface to SybasexviiiSAS/ACCESS Interface to Sybase IQxviiiSAS/ACCESS Interface to TeradataxixDocumentation EnhancementsxxPART1 Concepts 1 Chapter 1 Overview of SAS/ACCESS Interface to Relational Databases 3 About This Document3 Methods for

3 Accessing Relational database Data4 Selecting a SAS/ACCESS Method4 SAS Views of DBMS Data6 Choosing Your Degree of Numeric Precision7 Chapter 2 SAS Names and Support for DBMS Names 11 Introduction to SAS/ACCESS Naming11 SAS Naming Conventions12 SAS/ACCESS Default Naming Behaviors13 Renaming DBMS Data14 Options That Affect SAS/ACCESS Naming Behavior15 Naming Behavior When Retrieving DBMS Data15 Naming Behavior When Creating DBMS Objects16 SAS/ACCESS Naming Examples17 Chapter 3 Data Integrity and Security 25 Introduction to Data Integrity and Security25 DBMS Security25 SAS Security26ivPotential Result Set Differences When Processing Null Data31 Chapter 4 Performance Considerations 35 Increasing Throughput of the SAS Server35 Limiting Retrieval35 Repeatedly Accessing Data37 Sorting DBMS Data37 Temporary Table Support for SAS/ACCESS38 Chapter 5 Optimizing Your SQL Usage 41 Overview of Optimizing Your SQL Usage41 Passing Functions to the DBMS Using PROC SQL42 Passing Joins to the DBMS43 Passing the DELETE Statement to Empty a Table45 When Passing Joins to the DBMS Will Fail45 Passing DISTINCT and UNION Processing to the DBMS46 Optimizing the Passing of WHERE Clauses to the DBMS47 Using the DBINDEX=, DBKEY=.

4 And MULTI_DATASRC_OPT= Options48 Chapter 6 Threaded Reads 51 Overview of Threaded Reads in SAS/ACCESS51 Underlying Technology of Threaded Reads51 SAS/ACCESS Interfaces and Threaded Reads52 Scope of Threaded Reads52 Options That Affect Threaded Reads53 Generating Trace Information for Threaded Reads54 Performance Impact of Threaded Reads57 Autopartitioning Techniques in SAS/ACCESS57 Data Ordering in SAS/ACCESS58 Two-Pass Processing for SAS Threaded Applications58 When Threaded Reads Do Not Occur59 Summary of Threaded Reads59 Chapter 7 How SAS/ACCESS Works 61 Introduction to How SAS/ACCESS Works61 How the SAS/ACCESS LIBNAME Statement Works62 How the SQL Pass-Through Facility Works63 How the ACCESS Procedure Works64 How the DBLOAD Procedure Works65 Chapter 8 Overview of In- database Procedures 67 Introduction to In- database Procedures67 Running In- database Procedures69In- database Procedure Considerations and Limitations70 Using MSGLEVEL Option to Control Messaging72 PART2 General reference 73vChapter 9 SAS/ACCESS Features by Host 75 Introduction75 SAS/ACCESS Interface to AsternCluster: Supported Features75 SAS/ACCESS Interface to DB2 Under UNIX and PC Hosts: Supported Features76 SAS/ACCESS Interface to DB2 Under z/OS: Supported Features77 SAS/ACCESS Interface to Greenplum: Supported Features77 SAS/ACCESS Interface to HP Neoview: Supported Features78 SAS/ACCESS Interface to Informix: Supported Features78 SAS/ACCESS Interface to Microsoft SQL Server: Supported Features79 SAS/ACCESS Interface to MySQL: Supported Features79 SAS/ACCESS Interface to Netezza: Supported Features80 SAS/ACCESS Interface to ODBC: Supported Features81 SAS/ACCESS Interface to OLE DB.

5 Supported Features82 SAS/ACCESS Interface to Oracle: Supported Features82 SAS/ACCESS Interface to Sybase: Supported Features83 SAS/ACCESS Interface to Sybase IQ: Supported Features84 SAS/ACCESS Interface to Teradata: Supported Features85 Chapter 10 The LIBNAME Statement for Relational Databases 87 Overview of the LIBNAME Statement for Relational Databases87 Assigning a Libref Interactively88 LIBNAME Options for Relational Databases92 Chapter 11 Data Set Options for Relational Databases 203 About the Data Set Options for Relational Databases207 Chapter 12 Macro Variables and System Options for Relational Databases 401 Introduction to Macro Variables and System Options401 Macro Variables for Relational Databases401 System Options for Relational Databases403 Chapter 13 The SQL Pass-Through Facility for Relational Databases 425 About SQL Procedure Interactions425 Syntax for the SQL Pass-Through Facility for Relational Databases426 PART3 DBMS-Specific reference 437 Chapter 14 SAS/ACCESS Interface to AsternCluster 439 Introduction to SAS/ACCESS

6 Interface to AsternCluster439 LIBNAME Statement Specifics for AsternCluster440 Data Set Options for AsternCluster443 SQL Pass-Through Facility Specifics for AsternCluster445 Autopartitioning Scheme for AsternCluster446 Passing SAS Functions to AsternCluster448 Passing Joins to AsternCluster449 Bulk Loading for AsternCluster450viNaming Conventions for AsternCluster451 Data Types for AsternCluster452 Chapter 15 SAS/ACCESS Interface to DB2 Under UNIX and PC Hosts 455 Introduction to SAS/ACCESS Interface to DB2 Under UNIX and PC Hosts456 LIBNAME Statement Specifics for DB2 Under UNIX and PC Hosts456 Data Set Options for DB2 Under UNIX and PC Hosts460 SQL Pass-Through Facility Specifics for DB2 Under UNIX and PC Hosts462 Autopartitioning Scheme for DB2 Under UNIX and PC Hosts464 Temporary Table Support for DB2 Under UNIX and PC Hosts467 DBLOAD Procedure Specifics for DB2 Under UNIX and PC Hosts468 Passing SAS Functions to DB2 Under UNIX and PC Hosts470 Passing Joins to DB2 Under UNIX and PC Hosts472 Bulk Loading for DB2 Under UNIX and PC Hosts472In- database Procedures in DB2 under UNIX and PC Hosts475 Locking in the DB2 Under UNIX and PC Hosts Interface475 Naming Conventions for DB2 Under UNIX and PC Hosts477 Data Types for DB2 Under UNIX and PC Hosts477 Chapter 16 SAS/ACCESS Interface to DB2 Under z/OS 483 Introduction to SAS/ACCESS Interface to DB2 Under z/OS485 LIBNAME Statement Specifics for DB2 Under z/OS485 Data Set Options for DB2 Under z/OS487 SQL Pass-Through Facility Specifics for DB2 Under z/OS489 Autopartitioning Scheme for DB2 Under z/OS491 Temporary Table Support for DB2 Under z/OS492 Calling Stored Procedures in DB2 Under z/OS494 ACCESS Procedure Specifics for DB2

7 Under z/OS496 DBLOAD Procedure Specifics for DB2 Under z/OS498 The DB2 EXT Procedure500 The DB2 UTIL Procedure502 Maximizing DB2 Under z/OS Performance507 Passing SAS Functions to DB2 Under z/OS510 Passing Joins to DB2 Under z/OS511 SAS System Options, Settings, and Macros for DB2 Under z/OS512 Bulk Loading for DB2 Under z/OS515 Locking in the DB2 Under z/OS Interface520 Naming Conventions for DB2 Under z/OS521 Data Types for DB2 Under z/OS521 Understanding DB2 Under z/OS Client/Server Authorization527DB2 Under z/OS Information for the database Administrator529 Chapter 17 SAS/ACCESS Interface to Greenplum 533 Introduction to SAS/ACCESS Interface to Greenplum534 LIBNAME Statement Specifics for Greenplum534 Data Set Options for Greenplum537viiSQL Pass-Through Facility Specifics for Greenplum539 Autopartitioning Scheme for Greenplum540 Passing SAS Functions to Greenplum542 Passing Joins to Greenplum544 Bulk Loading for Greenplum544 Naming Conventions for Greenplum547 Data Types for Greenplum548 Chapter 18 SAS/ACCESS Interface to HP Neoview 553 Introduction to SAS/ACCESS Interface to HP Neoview554

8 LIBNAME Statement Specifics for HP Neoview554 Data Set Options for HP Neoview557 SQL Pass-Through Facility Specifics for HP Neoview559 Autopartitioning Scheme for HP Neoview561 Temporary Table Support for HP Neoview562 Passing SAS Functions to HP Neoview564 Passing Joins to HP Neoview565 Bulk Loading and Extracting for HP Neoview565 Naming Conventions for HP Neoview568 Data Types for HP Neoview568 Chapter 19 SAS/ACCESS Interface for Informix 573 Introduction to SAS/ACCESS Interface to Informix574 LIBNAME Statement Specifics for Informix574 Data Set Options for Informix576 SQL Pass-Through Facility Specifics for Informix577 Autopartitioning Scheme for Informix580 Temporary Table Support for Informix581 Passing SAS Functions to Informix582 Passing Joins to Informix583 Locking in the Informix Interface584 Naming Conventions for Informix585 Data Types for Informix585 Overview of Informix Servers588 Chapter 20 SAS/ACCESS Interface to Microsoft SQL Server 591 Introduction to SAS/ACCESS Interface to Microsoft SQL Server591 LIBNAME Statement Specifics for Microsoft SQL Server592 Data Set Options for Microsoft SQL Server595 SQL Pass-Through Facility Specifics for Microsoft SQL Server597 DBLOAD Procedure Specifics for Microsoft SQL Server598 Passing SAS Functions to Microsoft SQL Server600 Locking in the Microsoft SQL Server Interface600 Naming Conventions for Microsoft SQL Server601 Data Types for Microsoft SQL Server602viiiChapter 21 SAS/ACCESS Interface for MySQL 605 Introduction to SAS/ACCESS Interface to MySQL605 LIBNAME Statement Specifics for MySQL605 Data Set Options for MySQL608 SQL Pass-Through Facility Specifics for MySQL609 Autocommit and Table Types610 Understanding MySQL

9 Update and Delete Rules611 Passing SAS Functions to MySQL612 Passing Joins to MySQL613 Naming Conventions for MySQL614 Data Types for MySQL615 Case Sensitivity for MySQL619 Chapter 22 SAS/ACCESS Interface to Netezza 621 Introduction to SAS/ACCESS Interface to Netezza622 LIBNAME Statement Specifics for Netezza622 Data Set Options for Netezza625 SQL Pass-Through Facility Specifics for Netezza626 Temporary Table Support for Netezza628 Passing SAS Functions to Netezza630 Passing Joins to Netezza631 Bulk Loading and Unloading for Netezza632 Deploying and Using SAS Formats in Netezza634 Naming Conventions for Netezza648 Data Types for Netezza648 Chapter 23 SAS/ACCESS Interface to ODBC 653 Introduction to SAS/ACCESS Interface to ODBC654 LIBNAME Statement Specifics for ODBC656 Data Set Options for ODBC660 SQL Pass-Through Facility Specifics for ODBC662 Autopartitioning Scheme for ODBC666 DBLOAD Procedure Specifics for ODBC670 Temporary Table Support for ODBC672 Passing SAS Functions to ODBC674 Passing Joins to ODBC675 Bulk Loading for ODBC676 Locking in the ODBC Interface676 Naming Conventions for ODBC677 Data Types for ODBC678 Chapter 24 SAS/ACCESS Interface to OLE DB 681 Introduction to SAS/ACCESS Interface to OLE DB681 LIBNAME Statement Specifics for OLE DB682 Data Set Options for OLE DB689 SQL Pass-Through Facility Specifics for OLE DB690ixTemporary Table Support for OLE DB695 Passing SAS Functions to OLE DB697 Passing Joins to OLE DB698 Bulk Loading for OLE DB699 Locking in the OLE DB Interface699 Accessing OLE DB for OLAP Data700 Naming Conventions for OLE DB703 Data Types for OLE DB704 Chapter 25 SAS/ACCESS Interface to Oracle 707 Introduction to SAS/ACCESS Interface to

10 Oracle708 LIBNAME Statement Specifics for Oracle708 Data Set Options for Oracle711 SQL Pass-Through Facility Specifics for Oracle713 Autopartitioning Scheme