CONTENTS Title Page Copyright Page Send Us Your Comments Preface 1 Introduction 1.1 Product Family Overview 1.2 DEC DB Integrator Overview 1.2.1 How DBI Works 1.2.2 Overview of DBI Configuration Design 1.2.3 Benefits of Using DBI 1.3 DBI Gateway Overview 1.3.1 Relational Gateway Product Overview 1.3.2 Nonrelational Gateway Product Overview 1.4 Using the DBI Product Family to Solve Problems 1.5 DBI Product Family Documentation Overview 1.5.1 Installation and Configuration Information 1.5.2 User Guides 1.5.3 Reference Information 1.5.4 Release Notes 1.5.5 Online Help 1.5.6 Error Messages 2 Attaching to Databases 2.1 Purpose of an Attach Specification 2.2 Format of an Attach Specification 2.2.1 Sources of Additional Attach Information 2.2.2 Abbreviating Attach Qualifiers 2.2.3 Duplicating Attach Qualifiers 2.2.4 Length of Attach Specifications 2.3 Common Attach Specification Qualifiers 2.3.1 Specifying a Database Key Emulation Mode 2.3.1.1 Specifying a Default Database Key Mode 2.3.1.2 Specifying a Database Key Mode for a Table 2.3.2 Specifying That Transaction Options Be Enforced 2.3.3 Specifying the Transaction Mode 3 DBI Attach Specification Qualifiers 3.1 Specifying the Type of Database 3.2 Specifying the DBI Physical Database Type 3.3 Accessing a DBI Logical Database Created Using DEC Rdb 3.4 Accessing a DBI Logical Database Created Using the Gateway for ORACLE 3.5 Accessing a DBI Logical Database Created Using the Gateway for SYBASE 4 Gateway for DB2 Attach Specification Qualifiers 4.1 Using a Configuration File 4.2 Relationship of DB2 DATABASE, TABLES, and USER Qualifiers 4.3 Specifying a DB2 User Name and Password 4.4 Specifying the DB2 Database 4.5 Specifying a List of DB2 Tables 4.6 Specifying the Database Key Emulation Mode 4.7 Defining the Communication Between the IBM and Digital Systems 4.7.1 Specifying the DEC SNA Gateway Access Name 4.7.2 Specifying the VTAM Application ID 4.7.3 Specifying the Logon Mode 4.7.4 Specifying the DEC SNA Gateway Logical Unit Name 4.7.5 Specifying the DEC SNA Gateway Node Name 4.7.6 Specifying the DEC SNA Gateway Physical Unit Identifier 4.7.7 Specifying the DEC SNA Gateway Session Address 4.7.8 Specifying the CICS Transaction Identifier Name 4.8 DB2 Security Qualifiers 4.8.1 Specifying the DEC SNA Gateway Password 4.8.2 Specifying the Logical Unit Password 4.9 DB2 Character Sets 4.9.1 Specifying the Character Translation Set 4.9.2 Specifying the Collating Sequence 4.10 Enabling Gateway for DB2 Server Logging 4.11 Committing a DDL Transaction Automatically 4.12 Specifying Transaction Mode and Enforcing Transactions 5 Gateway for EDA/SQL Attach Specification Qualifiers 5.1 Specifying a Collection of Tables 5.2 Using a Configuration File 5.3 Specifying an EDA/SQL Stored Procedure 5.4 Specifying an EDA/SQL Password 5.5 Using an EDA/SQL Server 5.6 Specifying a List of EDA/SQL Tables 5.7 Identifying a Data Source Database Type 5.8 Identifying a Gateway Database User Name 6 Gateway for ORACLE Attach Specification Qualifiers 6.1 Using a Configuration File 6.2 Specifying an ORACLE User Name 6.3 Specifying an ORACLE Password 6.4 Specifying a List of ORACLE Tables 6.5 Specifying an ORACLE Connect String 6.6 Specifying Transaction Mode and Enforcing Transactions 6.7 Specifying the Maximum Length of ORACLE LONG Data 7 Gateway for SequeLink Attach Specification Qualifiers 7.1 Using the TYPE Qualifier 7.2 Using a Configuration File 7.3 Connecting to a Target Database 7.3.1 Specifying Communication Information 7.3.2 Logging on to the Database System 7.3.3 Directing the Gateway for SequeLinkto Build a Connection to Any Target Data Source 7.3.4 Examples of Attach Statements for the DBI Gateway for SequeLink 7.4 Specifying a Connection Timeout 7.5 Specifying the Database Key Emulation Mode 7.6 Enforcing Transactions and Specifying Transaction Mode 7.7 Specifying the Collating Sequence 7.8 Specifying a List of Tables 7.9 Specifying the Maximum Length of Text Data 8 Gateway for SYBASE Attach Specification Qualifiers 8.1 Using a Configuration File 8.2 Specifying a SYBASE Server Login 8.3 Specifying the SYBASE Database 8.4 Specifying a List of SYBASE Tables 8.5 Specifying the Database Key Emulation Mode 8.6 Specifying a SYBASE Database Server 8.7 Specifying a SYBASE INTERFACES File 8.8 Specifying a SYBASE Client Node 8.9 Specifying a SYBASE Connection Timeout 8.10 Specifying a SYBASE Query Timeout 8.11 Specifying the Maximum Length of SYBASE Text Data 8.12 Specifying the Application Requesting a Connection 8.13 Specifying Transaction Mode and Enforcing Transactions 9 Simplifying Attach Specifications 9.1 Specifying a Default Database 9.1.1 Using the SQL$DATABASE Logical Name 9.1.2 Using the SQL Configuration File 9.2 Specifying a Relational Gateway Configuration File 9.3 Using Relational Gateway Configuration Files 9.3.1 Specifying a Configuration File 9.3.2 Sample Configuration Files 9.3.3 Rules for Creating Configuration Files 10 System Relations and Data Translation 10.1 System Relations 10.1.1 System Relations Built by DBI and the Relational Gateway Products 10.1.2 DBI-Specific System Relations 10.1.3 System Relation Constructs That Cannot Reflect Gateway Database Definitions 10.2 Conversion of Data Names 10.3 Obtaining Information About System Relations 10.4 Obtaining Information About User Relations 10.5 Conversion of Data Types 10.5.1 Conversion of VMSDATE and ABSOLUTE DATE-TIME Data Types 10.5.2 Conversion of EDA/SQL Data Types 10.5.3 Conversion of SYBASE Data Types 10.5.4 Conversion of ORACLE Data Types 10.5.4.1 Translation of ORACLE Alphanumeric Data 10.5.5 Conversion of DB2 Data Types 10.5.6 Conversion of SequeLink Data Types 11 Database Keys 11.1 Dbkeys in Gateway and Link Databases 11.2 Dbkey Support Available Through the Relational Gateway Products 11.2.1 Dbkey Mode 11.2.2 Native Dbkeys 11.2.3 Read-Only Dbkeys 11.2.3.1 Circumstances Under Which Read-Only Dbkeys Are Not Used 11.2.4 Primary Keys as Dbkeys 11.2.5 User-Designated Dbkeys 11.3 Application Considerations for Dbkeys 11.3.1 Dbkey Lengths 11.3.2 INSERT RETURNING DBKEY Statement 11.3.3 Positioned Update and Delete Operations 12 Transaction Management 12.1 Specifying SET TRANSACTION Options 12.1.1 Using ENFORCE_ALL_TXN in Links 12.1.2 Setting the Access Mode 12.1.3 Using Read/Write Transactions with a Read-Only DB2 Server 12.2 Exiting SQL on DEC OSF/1 Systems 12.3 Committing Automatically, Using the Gateways for ORACLE, SequeLink, and SYBASE 12.4 Avoiding Catalog Locking Using the Gateway for DB2 12.5 Losing Transactions at the Gateway Database 12.5.1 Losing SNA Sessions with the Gateway for DB2 12.5.2 Encountering Deadlock Conditions in DB2 12.5.3 Implicit Rollbacks in SYBASE 12.6 Managing Transactions for Multistatement Procedures 12.7 Using Distributed Transactions on OpenVMS Systems 12.7.1 Using Distributed Transactions Involving DBI 12.7.2 Disabling Implicit Distributed Transactions 12.7.3 Using Distributed Transactions Involving EDA/SQL, DB2, ORACLE, or SequeLink 12.7.4 Using Distributed Transactions Involving SYBASE 12.7.5 Using Distributed Transactions Involving Nonrelational Gateways 12.8 Using Distributed Transactions on DEC OSF/1 Systems 13 Common Language Elements 13.1 Data Type Considerations 13.1.1 CAST Function Support 13.1.2 Long Character String Data Type Restriction 13.2 Value Expressions Considerations 13.2.1 Comparison Operators with the Relational Gateway Products 13.2.1.1 Comparison of Approximate Numbers 13.2.1.2 Comparison of Dbkey Values 13.2.2 Collating Sequences with the Gateway for DB2 13.2.2.1 Which Collating Sequence to Use 13.2.2.2 Sample Collating Sequences 13.2.3 Collating Sequences with the Other Gateways 13.2.4 Built-in Functions 13.2.4.1 Evaluation of CURRENT_DATE, CURRENT_TIME, and CURRENT_TIMESTAMP Functions 13.2.5 Keywords That Refer to Literals 13.2.5.1 Evaluation of the SQL USER Keyword 13.2.6 External Functions 13.2.7 Stored Procedures 13.3 Compound Statements 13.4 Select Expressions 13.5 Cursors 13.5.1 Default Mode for SQL Cursors 13.5.2 List Cursors Not Supported 13.5.3 Accessing ORACLE Views with Cursors 14 General SQL Usage Information 14.1 Using Data Manipulation Language 14.1.1 Updating and Deleting Rows Using Dbkeys 14.1.2 Truncating DB2 Input Values 14.1.3 Using DB2 Input Parameters in Select Lists 14.1.4 Using Views 14.1.4.1 Updating Views with the Relational Gateway Products 14.1.5 Accessing DB2 Nested Views 14.2 Controlling Query Processing 14.2.1 Limiting the Number of Rows Returned 14.2.2 Limiting the Execution and Compilation Elapsed Time 14.2.3 Limiting the Execution and Compilation CPU Time 14.3 Statement Atomicity 14.3.1 Query Decomposition in the Relational Gateway Products 14.3.2 Query Decomposition Limitation 14.3.3 Using Markpoints 14.3.4 DBI Support for Compound Statement Atomicity 14.4 Using Data Definition Language 14.4.1 Creating Tables 14.4.1.1 Table Names and Column Names 14.4.1.2 Column Data Types 14.4.1.3 Default Values for Columns 14.4.1.4 Location of Created Table in DB2 Database 14.4.2 Deleting Tables 14.4.3 Granting Privileges 14.4.4 Creating and Deleting Indexes 14.4.5 Creating Domains 14.5 Ending a Session with COMMIT or ROLLBACK 14.6 Programming Considerations 14.6.1 Avoiding Error Message Parsing 14.6.2 Enabling and Disabling ASTs with the Gateway for DB2 14.6.3 Reentrancy 15 Using DBI and the Relational Gateway Products with Other Tools 15.1 DEC RALLY 15.1.1 Accessing ORACLE Databases 15.1.2 Accessing SYBASE Databases 15.1.3 Changing Database Type at Run Time 15.1.4 Using Uppercase Characters in an Attach Specification 15.1.5 Using the ENFORCE Attach Specification Qualifier 15.1.6 Using Dbkeys 15.1.7 Transaction Management 15.1.7.1 RALLY Transaction Options 15.1.7.2 Data Source Definitions 15.1.8 Defining a Logical Name for the File Name 15.1.9 Using a Relational Gateway Configuration File 15.2 DEC DATATRIEVE 15.2.1 Setting Up DATATRIEVE 15.2.2 Creating a CDD/Repository Path Name Using a Logical Name 15.2.3 Updating Data with DATATRIEVE 15.2.4 DATATRIEVE Example 15.3 DEC Data Distributor 15.3.1 DEC Data Distributor Terminology 15.3.2 Sample Extraction of a Gateway Database 15.3.3 Sample Extraction of a DBI Database 15.4 RDO Utility 15.4.1 Using RDO 15.4.2 Unsupported RDO Statements 15.5 SmartStar 15.5.1 Setting Up SmartStar 15.6 SQL/Services 15.6.1 Executors 15.6.2 SQL/Services Database Class Executor Definition 15.6.3 Using SQL/Services and the USER Keyword 15.6.4 Gateway for DB2 Information 15.6.4.1 Proxy Implications with SQL/Services Executors 15.6.4.2 CICS Timeouts with SQL/Services 15.7 DEC ODBC Driver Software for MS Windows Operating System 15.8 Microsoft ACCESS 15.8.1 Setting Up Microsoft ACCESS 15.8.2 Using Microsoft ACCESS with DBI 15.8.2.1 Indexes on Tables 15.8.2.2 Sorting 15.8.2.3 Committing Data 15.8.2.4 Null Values 15.8.2.5 Subqueries 15.8.3 Performance 15.9 PowerBuilder 15.9.1 Setting Up PowerBuilder 15.9.2 Using the PowerBuilder Extended Catalog 15.9.2.1 DBI and the Extended Catalog 15.9.2.2 Relational Gateway Products and the Extended Catalog 15.9.3 Updating Data 15.9.4 Performing Queries 15.9.5 Creating a Pass-Through Table 15.9.6 Creating an Imported Table 16 DBI and Relational Gateway Tracing 16.1 Enabling Tracing 16.2 Tracing Options 16.2.1 Defining the Tracing Logical Name 16.2.2 Defining the Tracing Flags Using the SQL Configuration File 16.3 Directing the Tracing Output 16.3.1 Directing Trace Output for OpenVMS Systems 16.3.2 Directing Trace Output for DEC OSF/1 Systems 16.4 Tracing on a Remote Node 16.5 Enabling Tracing for DB2 Server 16.6 Summary of DBI and Relational Gateway Tracing Options 16.7 Summary of Relational Gateway Tracing Options 16.8 Tracing Recommendations 16.9 Entry Options for DBI and the Relational Gateway Products 16.9.1 Tracing the Binary Representation Language Requests 16.9.2 Tracing the Metadata Binary Representation Language Requests 16.9.3 Tracing Messages 16.9.4 Tracing Relational Call Interface Requests 16.10 Interface Options for DBI and the Relational Gateway Products 16.10.1 Tracing Operations Between DBI and Other Entities 16.10.2 Single-Line Tracing of Operations Between DBI and Other Entities 16.10.3 Tracing Query Plans 16.11 Behavioral Options for DBI and the Relational Gateway Products 16.11.1 Tracing Multistatement Procedures 16.11.2 Tracing DBI or Relational Gateway Product Errors 16.11.3 Tracing the Loading of Shareable Images 16.11.4 Suppressing the Timestamp in Trace Events 16.12 Statistical Options for DBI and the Relational Gateway Products 16.12.1 Tracing Statistics for All Events 16.12.2 Tracing Statistics for Attach Events 16.12.3 Tracing Statistics in a Request 16.12.4 Tracing Statistics in a Request Instance or Request Execution 16.12.5 Tracing Statistics in a Transaction 16.13 Relational Gateway Trace Options 16.13.1 Turning on All the Relational Gateway Trace Options 16.13.2 Tracing Interface Requests 16.13.2.1 Database-Specific Interface Calls 16.13.2.2 SDI Requests 16.13.3 Tracing Context Changes 16.13.3.1 Attach Context 16.13.3.2 Cursor Context 16.13.3.3 Request Context 16.13.3.4 Transaction Context 16.13.4 Tracing Messages 16.13.5 Tracing Rows 16.13.6 Writing Request Information to the Trace Log 16.13.6.1 Attach Specification Information 16.13.6.2 Metadata Information 16.13.6.3 Execution Request Plans 16.13.6.4 SQL Requests Sent to the Gateway Database 16.14 Reading a Trace File 16.14.1 Sample Trace File 16.14.2 Interpreting the Trace File 17 Troubleshooting DBI 17.1 Problems Creating DBI Logical Databases 17.1.1 DBI-F-CAT_ACC_FAIL, DBI-E-EXCLOADDRV, DBI-F-NOIMGSYM 17.1.2 DBI-F-CAT_ACC_FAIL, SQL-F-REL_EXISTS 17.1.3 DBI-F-CAT_ACC_FAIL, SQL-F-SCHNOTDEF 17.1.4 DBI-F-NOT_ANSI_CAT 17.1.5 DBI-F-NOT_ANSI_DB 17.1.6 RDB-E-BAD_DB_FORMAT, DBI-F-UNSUP_STORE 17.1.7 SQL-F-NODEFDB 17.2 Problems Attaching to DBI Logical Databases 17.2.1 DBI-F-CAT_ACC_FAIL, RDB-E-OBSOLETE_METADA 17.2.2 RDB-E-BAD_DB_FORMAT, RMS-F-SYN 17.2.3 RDB-E-BAD_DB_FORMAT, DBI-E-MISDBNAM 17.2.4 RDB-E-BAD_DB_FORMAT, DBI-F-CAT_ACC_FAIL, RDB-E-BAD_DB_FORMAT, RMS-E-FNF 17.2.5 RDB-E-UNAVAILABLE2, LIB-E-ACTIMAGE 17.2.6 RDB-E-DECDTMERR, SYSTEM-F-ABORT 17.3 Problems Accessing Link Databases 17.3.1 DBI-E-ATTLNKERR, DBI-E-DDRV_OLDPRODVER 17.3.2 DBI-E-ATTLNKERR, DBI-E-EXCLOADDRV, LIB-E-ACTIMAGE 17.3.3 DBI-E-ATTLNKERR, RDB-E-BAD_DB_FORMAT,DBI-F-CAT_ACC_FAIL 17.3.4 DBI-E-ATTLNKERR, RDB-F-IO_ERROR, SYSTEM-F-NOSUCHOBJ 17.3.5 DBI-E-ATTLNKERR, RDB-F-IO_ERROR, SYSTEM-F-INVLOGIN 17.3.6 DBI-E-ATTLNKERR, RDB-E-NOPRIV, DBI-F-CAT_ACC_FAIL, RDB-E-NO_PRIV 17.3.7 DBI-E-ATTLNKERR, RDB-F-IO_ERROR, SYSTEM-F-LINKEXIT 17.3.8 DBI-E-ATTLNKERR, RDB-F-WRONG_ODS, RDMS-F-ROOTMAJVER 17.3.9 DBI-E-LINK_CMP_ERR, DBI-I-LINK_INFO, RDB-E-OBSOLETE_METADA, RDMS-F-BAD_SYM 17.3.10 DBI-E-ERRCREWSFIL 17.3.11 DBI-E-LINK_CMP_ERR, DBI-I-LINK_INFO, RDB-E-OBSOLETE_METADA, RDMS-F-TABNOTDEF 17.3.12 DBI-E-LINK_NO_CAP 17.3.13 RDB-E-BAD_DB_FORMAT, RMS-E-FNF 17.3.14 RDB-E-NO_META_UPDATE, DBI-F-IMPORT_FAIL, RDB-E-BAD_DB_FORMAT 17.3.15 SYSTEM-F-CONNECFAIL 17.3.16 RDMS-I-BUGCHKDMP Followed by Process Being Logged Off 17.4 Problems Using Gateways to Access Data Sources 17.4.1 DBI-E-UDBTXN_START, DBI-I-LINK_INFO, RDB-E-STRANSNAL 17.4.2 DDAL-E-ERRACCSRCDB, RDB-E-PORT_LEN, DBI-E-REQSENDMSGLEN 17.4.3 RDB-E-BAD_DB_FORMAT, DBI_F_IMPORT_FAIL, RDB-E-NO_META_UPDATE 17.4.4 SQL-F-ERRATTDEC, DBI-E-ATTACHERR 18 Improving Performance 18.1 General Performance Improvements 18.1.1 Improving Attach Performance 18.1.2 Managing Large Result Sets 18.1.3 Using Indexes 18.1.4 Formulating Queries Efficiently 18.1.4.1 Using the Equal Operator 18.1.4.2 Specifying Rows and Columns Precisely 18.1.5 Using Tracing Appropriately 18.1.6 Using Read-Only Cursors 18.2 Improving Relational Gateway Performance 18.2.1 Reducing the Number of Queries Per Transaction 18.2.2 Using Read-Only Access to DB2 18.3 Improving DBI Performance 18.3.1 Installing Rdb/Dispatch Version 6.0 or Higher 18.3.2 Importing Tables Instead of Views 18.3.3 Manually Setting Link Table Statistics 18.3.4 Executing DBI Close to Data 18.3.5 Executing DBI on the Same Node as an Application 18.3.6 Using Parallel Processing 18.3.6.1 Enabling Parallel Processing 18.3.6.2 Optimizing Parallel Processing 18.3.6.3 Monitoring Parallel Processing 18.3.7 Controlling Join Selection 18.3.8 Controlling Index Join Degree 18.3.9 Controlling Ordering in Views 18.3.10 Controlling Query Unnesting 18.3.11 Controlling Optimization Level A SQL Support B Error Messages B.1 Error Message Structure B.2 Error Message Sources B.3 Bugcheck Output Files B.4 Where to Find Help on Error Messages