Scalable Database Server

HiRDB Version 8

UAP Development Guide

3020-6-356(E)

Contents

Notices
Preface

1. Overview
1.1 UAP development flow
1.2 UAP characteristics
1.2.1 UAP format
1.2.2 List of SQL statements usable in HiRDB
1.3 Interface with HiRDB
1.4 UAP operation environment

2. Database Operations
2.1 Database data expressions
2.1.1 Relational database tables
2.1.2 Object relational database tables
2.2 Cursor usage
2.3 Data retrieval
2.3.1 Retrieval from a single table
2.3.2 Retrieval from multiple tables
2.3.3 Retrieval of a table with FIX attribute
2.4 Data updating
2.4.1 Updating using a cursor
2.4.2 Updating with a condition specified
2.4.3 Updating a table with the FIX attribute
2.4.4 Updating a table with repetition columns
2.5 Data deletion
2.5.1 Deletion using a cursor
2.5.2 Deletion with a condition specified
2.5.3 Deleting all rows in a table
2.6 Data insertion
2.6.1 Inserting rows on a column basis
2.6.2 Inserting rows on a row basis (to a table with the FIX attribute)
2.6.3 Inserting rows into a table with repetition columns
2.7 Specific data search
2.7.1 Searching for data within a specified range of values
2.7.2 Searching for a specific character pattern
2.7.3 Searching for non-NULL data
2.7.4 Searching for data that satisfies multiple conditions
2.7.5 Searching for data using a Boolean predicate
2.7.6 Searching for data using a structured repetition predicate
2.7.7 Searching for data using a subquery
2.8 Data operations
2.8.1 Arithmetic operations on numeric data
2.8.2 Date and time data operations
2.9 Data processing
2.9.1 Data grouping
2.9.2 Data sorting
2.9.3 Duplicated data elimination
2.10 Outer joining of tables
2.11 Defining and manipulating a view table
2.12 Manipulating data in a table with abstract data types
2.12.1 Abstract data types provided by the HiRDB Text Search Plug-in
2.12.2 User-defined abstract data types

3. UAP Design
3.1 Basic SQL configuration in a UAP
3.2 Overview of UAPs
3.2.1 UAP descriptive languages
3.2.2 Interface areas
3.2.3 Integrity constraints
3.2.4 Retrieval methods using SQL statements
3.2.5 Static and dynamic SQLs
3.3 Transaction control
3.3.1 Connection to and disconnection from a HiRDB system
3.3.2 Transaction startup and termination
3.3.3 Synchronization point setting and rollback
3.3.4 UAP transaction management in an OLTP environment
3.3.5 Moving a transaction
3.4 Locking
3.4.1 Units of locking
3.4.2 Lock modes
3.4.3 Lock period
3.4.4 Deadlocks and corrective measures
3.4.5 Unlocked conditional search
3.4.6 Non-locking of index key values
3.4.7 Lock and suppression implementable with a UAP
3.4.8 Lock sequence based on SQL statement and index types
3.4.9 Creating locked resources for index key values
3.5 Use of a cursor
3.5.1 Notes on table operations when a cursor is used
3.5.2 FOR UPDATE and FOR READ ONLY clauses
3.5.3 Cursor declarations and locks
3.5.4 Holdable cursor
3.5.5 Examples of cursor use
3.6 SQL error identification and corrective measures
3.6.1 Error identification
3.6.2 Automatic error identification

4. UAP Design for Improving Performance and Handling
4.1 Using indexes
4.1.1 Indexes and processing time
4.1.2 Index priority
4.1.3 Changing indexes during retrieval
4.2 Manipulation of tables with the FIX attribute
4.3 Stored procedures and stored functions
4.3.1 Defining a stored procedure
4.3.2 Defining a stored function
4.3.3 Defining and deleting stored functions
4.4 Triggers
4.5 SQL optimization
4.5.1 SQL optimizing modes
4.5.2 Optimization method types
4.5.3 Specifying SQL optimization
4.5.4 Allocating floatable servers (HiRDB/Parallel Server only)
4.5.5 Grouping processing methods (HiRDB/Parallel Server only)
4.5.6 Join methods
4.5.7 Search Methods
4.5.8 Execution of subqueries with no external references
4.5.9 Execution of subqueries with external references
4.5.10 Preparing for application of hash join and subquery hash execution
4.5.11 Deriving high-speed search conditions
4.6 Data guarantee levels
4.6.1 Specifying the data guarantee level
4.6.2 Data guarantee level types
4.6.3 Example of search results when a data guarantee level is specified
4.7 Block transfer facility
4.8 Facilities using arrays
4.8.1 FETCH facility using arrays
4.8.2 INSERT facility using arrays
4.8.3 UPDATE facility using arrays
4.8.4 DELETE facility using arrays
4.9 Rapid grouping facility
4.9.1 Overview
4.9.2 Application criteria
4.9.3 Specification method
4.9.4 Tuning method
4.10 Multi-connection facility
4.11 Using tables for managing numbers
4.12 Narrowed search
4.12.1 What is a narrowed search?
4.12.2 Preparations for executing a narrowed search
4.12.3 Search using lists
4.12.4 Action if a rollback occurs for a transaction that uses a list
4.12.5 Automatic list deletion at HiRDB startup and termination
4.12.6 Notes about using lists
4.13 File output facility for BLOB data
4.13.1 What is the file output facility for BLOB data?
4.13.2 Application criteria
4.13.3 Specification method
4.13.4 Notes about using the file output facility for BLOB data
4.13.5 Examples of using the file output facility for BLOB data
4.14 Addition update and partial extraction facility for BLOB and BINARY data
4.14.1 What is the addition update and partial extraction facility for BLOB and BINARY data?
4.14.2 Examples of using the addition update and partial extraction facility for BLOB data
4.14.3 Notes about using the addition update and partial extraction facility for BLOB and BINARY data
4.15 Retrieve first n records facility
4.15.1 Overview
4.15.2 Notes
4.15.3 Checking the access path
4.16 Automatic reconnect facility
4.16.1 Application criteria
4.16.2 Reconnect timings
4.16.3 CONNECT processing during automatic reconnect
4.16.4 Notes about using the automatic reconnect facility
4.17 Locator facility
4.17.1 What is the locator facility?
4.17.2 Application standard
4.17.3 Usage method
4.17.4 Usage example
4.18 Facility for returning the total number of hits
4.18.1 Overview
4.18.2 Usage examples
4.18.3 Note

5. Notes about Creating UAPs that Access Object Relational Databases
5.1 Using abstract data types and user-defined functions
5.2 Restrictions on functions provided by plug-ins

6. Client Environment Setup
6.1 Types of HiRDB clients
6.2 Environment setup procedure for HiRDB clients
6.3 HiRDB client installation
6.3.1 Installing a HiRDB client on a UNIX client
6.3.2 Installing a HiRDB client on a Windows client
6.4 Organization of directories and files for a HiRDB client
6.4.1 Directories and files for UNIX clients
6.4.2 Directories and files for Windows clients
6.5 Setting the hosts file
6.6 Client environment definitions (setting environment variables)
6.6.1 Environment setup format
6.6.2 Specifications for using a UAP under OLTP as the client
6.6.3 Client environment definitions
6.6.4 Environment definition information
6.6.5 Environment variables and connection types for HiRDB servers
6.6.6 Specifying client environment definitions for foreign table access
6.7 Registering an environment variable group
6.7.1 Registering an environment variable group in a UNIX environment
6.7.2 Registering an environment variable group in a Windows environment (registry registration)
6.7.3 Registering an environment variable group in a Windows environment (file registration)

7. UAP Creation
7.1 Overview
7.1.1 UAP basic configuration
7.1.2 UAP configuration elements
7.2 Writing a UAP in C
7.2.1 Coding rules
7.2.2 Program example
7.3 Writing a UAP in COBOL
7.3.1 Coding rules
7.3.2 Program example
7.4 Writing a UAP in C++
7.4.1 Coding rules
7.5 Writing a UAP in OOCOBOL
7.5.1 Coding rules

8. Preparation for UAP Execution
8.1 UAP execution procedure
8.1.1 Executing a UAP written in C
8.1.2 Executing a UAP written in COBOL
8.2 Preprocessing
8.2.1 Overview
8.2.2 Preprocessing in UNIX
8.2.3 Preprocessing in Windows
8.2.4 Validating preprocessor declaration statements
8.2.5 Dispensing with the embedded SQL declare section
8.2.6 Specifying pointers as environment variables
8.2.7 Referencing structures
8.2.8 Use of pointers, structures, and structure qualifiers when the -E2 or -E3 option of the preprocessor is specified
8.3 Compiling and linking
8.3.1 Libraries for compiling and linking
8.3.2 Compiling and linking in UNIX
8.3.3 Compiling and linking in Windows
8.3.4 Compiling and linking when the multi-connection facility is used
8.4 Notes on UAP execution
8.4.1 Executing UAPs that use an X/Open-based API (TX_function)
8.4.2 Creating UAPs that support the 64-bit mode
8.4.3 Converting UAPs created with XDM/RD or UNIFY2000
8.4.4 Notes on UAP execution

9. Java Stored Procedures and Java Stored Functions
9.1 Overview
9.2 Procedure from Java stored routine creation to execution
9.2.1 Coding a Java stored routine
9.2.2 Registering the JAR file in HiRDB
9.2.3 Defining the Java stored routine
9.2.4 Executing the Java stored routine
9.3 Sample programs of Java stored routine
9.3.1 Sample program
9.3.2 Sample Java stored routines provided with HiRDB
9.4 Notes about Java program creation
9.4.1 Unsupported methods
9.4.2 Package, class, and method definitions
9.4.3 Parameter input/output mode mapping (Java stored procedures only)
9.4.4 Results-set return facility (Java stored procedures only)
9.4.5 Connection in a Java stored procedure
9.4.6 Releasing the result sets
9.5 Notes about testing and debugging
9.5.1 Java program for a Java stored procedure
9.5.2 Java program for a Java stored function
9.6 Notes about JAR file creation
9.6.1 Integrating Class files
9.6.2 Integrating Java files

10. UAP Troubleshooting
10.1 Gathering error information
10.1.1 SQL tracing
10.1.2 Error logging
10.1.3 Facility for output of extended SQL error information
10.1.4 UAP statistical report facility
10.1.5 Command trace facility
10.1.6 SQL trace dynamic acquisition facility
10.1.7 Reconnect trace facility
10.1.8 HiRDB SQL Tuning Advisor access path information file
10.2 UAP error recovery

11. Using a Distributed Database (Limited to HP-UX and AIX 5L)
11.1 Format of a distributed database
11.1.1 Accessing a distributed database and its relationship to RD-nodes
11.1.2 Relationship between a connection between RD-nodes and an SQL connection
11.1.3 Generating and terminating an SQL connection
11.1.4 Current SQL connection and database access
11.1.5 SQL connection and transaction control
11.2 Creating a UAP that accesses a remote database
11.2.1 Rules governing distributed clients and servers
11.2.2 Using the default SQL connection
11.2.3 Using an SQL connection to a distributed RD-node
11.3 Available SQL statements
11.3.1 SQL statements usable for remote database access
11.3.2 Details about available SQL statements
11.4 Available data types
11.4.1 Data types of variables usable in remote database access
11.4.2 Correspondence between distributed server data types and HiRDB data types
11.5 Handling distributed server errors
11.5.1 Return codes set by the distributed client
11.5.2 Obtaining and using detailed error information
11.6 Notes about using a distributed database
11.6.1 Notes about using a distributed client
11.6.2 Notes about using a distributed server

12. Command Execution from UAPs
12.1 Overview
12.2 Preparations for executing commands from a UAP
12.3 Command executability

13. HiRDB Access from ODBC Application Programs
13.1 ODBC application programs
13.2 Installing the ODBC2.0 driver
13.3 Installing the ODBC3.0 driver and setting the environment variables
13.3.1 Installation
13.3.2 Setting the environment variables
13.3.3 Determining the version number of the ODBC3.0 driver
13.4 ODBC functions provided by HiRDB
13.5 ODBC function data types and HiRDB data types
13.6 Asynchronous execution of ODBC functions
13.7 Setting cursor libraries
13.8 File DSNs
13.9 Executing a UAP in Unicode
13.10 Tuning and troubleshooting
13.11 Facilities that cannot be used when HiRDB is accessed with ODBC

14. HiRDB Access from OLE DB Application Programs
14.1 Overview
14.2 Connection interface
14.2.1 Registry information
14.2.2 Connection properties
14.3 Schema information
14.4 Data type correspondences
14.5 Error handling procedures
14.5.1 Troubleshooting facility
14.6 Notes

15. HiRDB Access from ADO.NET-compatible Application Programs
15.1 Overview
15.1.1 HiRDB.NET Data Provider
15.1.2 Prerequisite programs for HiRDB.NET Data Provider
15.2 Installing HiRDB.NET Data Provider
15.2.1 Installation procedure
15.2.2 Files that are installed
15.2.3 Checking the version information
15.3 List of classes provided by HiRDB.NET Data Provider
15.4 List of members provided by HiRDB.NET Data Provider
15.4.1 List of HiRDBCommand members
15.4.2 List of HiRDBCommandBuilder members
15.4.3 List of HiRDBConnection members
15.4.4 List of HiRDBDataAdapter members
15.4.5 List of HiRDBDataReader members
15.4.6 List of HiRDBException members
15.4.7 List of HiRDBParameter members
15.4.8 List of HiRDBParameterCollection members
15.4.9 List of HiRDBRowUpdatedEventArgs members
15.4.10 List of HiRDBRowUpdatingEventArgs members
15.4.11 List of HiRDBTransaction members
15.5 Interfaces of HiRDB.NET Data Provider
15.5.1 HiRDBCommand
15.5.2 HiRDBCommandBuilder
15.5.3 HiRDBConnection
15.5.4 HiRDBDataAdapter
15.5.5 HiRDBDataReader
15.5.6 HiRDBException
15.5.7 HiRDBParameter
15.5.8 HiRDBParameterCollection
15.5.9 HiRDBRowUpdatedEventArgs
15.5.10 HiRDBRowUpdatingEventArgs
15.5.11 HiRDBTransaction
15.6 Notes about HiRDB.NET Data Provider
15.7 Data types of HiRDB.NET Data Provider
15.7.1 DbType and HiRDBType properties
15.7.2 Data types and accessories used by a UAP
15.7.3 Type conversion by HiRDB.NET Data Provider
15.8 Example of a UAP using HiRDB.NET Data Provider
15.8.1 Connecting to the database
15.8.2 Executing the SQL statement
15.8.3 Executing a transaction
15.8.4 Executing a search statement
15.8.5 Executing the INSERT facility using arrays
15.8.6 Executing a repetition column

16. Type2 JDBC Driver
16.1 Installation and environment setup
16.1.1 Installing
16.1.2 Environment setup
16.1.3 Abbreviation of methods
16.2 JDBC1.0 facility
16.2.1 Driver class
16.2.2 Connection class
16.2.3 Statement class
16.2.4 PreparedStatement class
16.2.5 CallableStatement class
16.2.6 ResultSet class
16.2.7 ResultSetMetaData class
16.2.8 DatabaseMetaData class
16.2.9 SQLWarning class
16.3 JDBC2.0 basic facility
16.3.1 Result set enhancements
16.3.2 Batch updating
16.3.3 Added data types
16.4 JDBC2.0 Optional Package
16.4.1 Database connection using DataSource and JNDI
16.4.2 Connection pooling
16.4.3 Distributed transactions
16.5 JAR file access facility
16.5.1 Class name
16.5.2 Method name
16.6 Array class
16.7 Specifying a value when using a repetition column as the ? parameter
16.8 Functions provided by the HiRDB JDBC driver
16.8.1 Provided class
16.8.2 setBlockUpdate
16.8.3 getBlockUpdate
16.9 Notes on using the BLOB type
16.10 Setting system properties
16.10.1 Setting the array facility
16.10.2 Setting the maximum number of SQL search items or ? parameters
16.11 Connection information setup/acquisition interface
16.11.1 setDescription
16.11.2 getDescription
16.11.3 setDBHostName
16.11.4 getDBHostName
16.11.5 setEncodeLang
16.11.6 getEncodeLang
16.11.7 setUser
16.11.8 getUser
16.11.9 setPassword
16.11.10 getPassword
16.11.11 setXAOpenString
16.11.12 getXAOpenString
16.11.13 setXACloseString
16.11.14 getXACloseString
16.11.15 setRMID
16.11.16 getRMID
16.11.17 setXAThreadMode
16.11.18 getXAThreadMode
16.11.19 setCommit_Behavior
16.11.20 getCommit_Behavior
16.11.21 setBlockUpdate
16.11.22 getBlockUpdate
16.11.23 setLONGVARBINARY_Access
16.11.24 getLONGVARBINARY_Access
16.11.25 setSQLInNum
16.11.26 getSQLInNum
16.11.27 setSQLOutNum
16.11.28 getSQLOutNum
16.11.29 setSQLWarningLevel
16.11.30 getSQLWarningLevel
16.11.31 setClear_Env
16.11.32 getClear_Env
16.12 Data types and character codes
16.12.1 Data types
16.12.2 Character code conversion facility
16.13 Classes and methods with limitations
16.13.1 Driver class
16.13.2 Connection class
16.13.3 Statement class
16.13.4 PreparedStatement class
16.13.5 CallableStatement class
16.13.6 ResultSet class
16.13.7 ResultSetMetaData class
16.13.8 DatabaseMetaData class
16.13.9 Blob class
16.13.10 Array class

17. Type4 JDBC Driver
17.1 Installation and environment setup
17.1.1 Installation
17.1.2 Environment setup
17.1.3 Abbreviation of methods
17.2 Database connection using the DriverManager class
17.2.1 Registering the Driver class
17.2.2 Connecting to HiRDB with the getConnection method
17.3 Database connection using a DataSource object and JNDI
17.4 JDBC1.2 core API
17.4.1 Driver interface
17.4.2 Connection interface
17.4.3 Statement interface
17.4.4 PreparedStatement interface
17.4.5 ResultSet interface
17.4.6 DatabaseMetaData interface
17.4.7 ResultSetMetaData interface
17.4.8 Blob interface
17.4.9 SQLException interface
17.4.10 SQLWarning interface
17.4.11 Unsupported interfaces
17.5 JDBC2.1 Core API
17.5.1 Expansion of the result set
17.5.2 Batch update
17.5.3 Added data types
17.5.4 Unsupported interfaces
17.6 JDBC2.0 Optional Package
17.6.1 JNDI support
17.6.2 Connection pool
17.6.3 Distributed transactions
17.6.4 Unsupported interfaces
17.7 Connection information setup and acquisition interface
17.7.1 setDescription
17.7.2 getDescription
17.7.3 setDBHostName
17.7.4 getDBHostName
17.7.5 setJDBC_IF_TRC
17.7.6 getJDBC_IF_TRC
17.7.7 setTRC_NO
17.7.8 getTRC_NO
17.7.9 setUapName
17.7.10 getUapName
17.7.11 setUser
17.7.12 getUser
17.7.13 setPassword
17.7.14 getPassword
17.7.15 setXAOpenString
17.7.16 getXAOpenString
17.7.17 setXACloseString
17.7.18 getXACloseString
17.7.19 setLONGVARBINARY_Access
17.7.20 getLONGVARBINARY_Access
17.7.21 setSQLInNum
17.7.22 getSQLInNum
17.7.23 setSQLOutNum
17.7.24 getSQLOutNum
17.7.25 setSQLWarningLevel
17.7.26 getSQLWarningLevel
17.7.27 setXALocalCommitMode
17.7.28 getXALocalCommitMode
17.7.29 setSQLWarningIgnore
17.7.30 getSQLWarningIgnore
17.7.31 setHiRDBCursorMode
17.7.32 getHiRDBCursorMode
17.7.33 setNotErrorOccurred
17.7.34 getNotErrorOccurred
17.7.35 setEnvironmentVariables
17.7.36 getEnvironmentVariables
17.7.37 setEncodeLang
17.7.38 getEncodeLang
17.7.39 setMaxBinarySize
17.7.40 getMaxBinarySize
17.7.41 setStatementCommitBehavior
17.7.42 getStatementCommitBehavior
17.7.43 setLONGVARBINARY_AccessSize
17.7.44 getLONGVARBINARY_AccessSize
17.7.45 setLONGVARBINARY_TruncError
17.7.46 getLONGVARBINARY_TruncError
17.8 Data types
17.8.1 Mapping SQL data types
17.8.2 Mapping during retrieval data acquisition
17.8.3 Mapping when a ? parameter is set
17.8.4 Data conversion of TIME, DATE, and TIMESTAMP columns
17.8.5 Overflow handling
17.9 Character conversion facility
17.10 Supported client environment definitions
17.11 Connection information priorities
17.12 JDBC interface method trace
17.12.1 Setup for trace acquisition
17.12.2 Acquisition rules
17.12.3 Output example
17.13 Exception trace log
17.13.1 Methods to be acquired and setup for log acquisition
17.13.2 Output formats
17.13.3 Output example and analysis method
17.13.4 Required memory size and file size
17.13.5 Notes

18. SQLJ
18.1 Overview
18.1.1 What is SQLJ?
18.1.2 Environment settings
18.2 SQLJ Translator
18.3 UAP coding rule
18.3.1 Labeling rule
18.3.2 SQL coding rule
18.3.3 SQL statements that can be used in SQLJ
18.3.4 Correspondence between HiRDB data types and SQLJ data types
18.3.5 Output variable settings (limited to the native interface version)
18.3.6 Using data types when a cursor is declared (limited to the native interface version)
18.3.7 Description of connection to and disconnection from a HiRDB server
18.3.8 Description of cursor-based retrieval
18.3.9 Receiving a dynamic result set
18.3.10 Using JDBC and SQLJ together
18.3.11 Creating and executing a UAP
18.3.12 Migrating an SQLJ source from the standard interface version to the native interface version
18.3.13 Notes about UAP development
18.4 Native Runtime
18.4.1 Package configuration
18.4.2 Public classes of Native Runtime
18.4.3 Cluster specifications
18.4.4 Coding examples using the native interface

Appendixes
A. SQL Communications Area
A.1 Organization and contents of the SQL Communications Area
A.2 Expanding the SQL Communications Area
B. SQL Descriptor Area
B.1 Organization and contents of the SQL Descriptor Area
B.2 Expanding the SQL Descriptor Area
C. Column Name Descriptor Area
C.1 Organization and contents of the Column Name Descriptor Area
C.2 Expanding the Column Name Descriptor Area
D. Type Name Descriptor Area
D.1 Organization of the Type Name Descriptor Area
D.2 Contents of the Type Name Descriptor Area
D.3 Expanding the Type Name Descriptor Area
E. SQL Data Types and Data Descriptions
E.1 SQL data types and C data descriptions
E.2 SQL data types and COBOL data descriptions
F. Data Dictionary Table Retrieval
F.1 Examples of SQL statements for retrieval
F.2 Data dictionary table details
G. Functions provided by HiRDB
G.1 Hash function for table partitioning
G.2 Space conversion function
G.3 Function for conversion to a DECIMAL signed normalized number
G.4 Character code type specification function
H. Maximum and Minimum HiRDB Values

Index

[Trademarks]

All Rights Reserved. Copyright (C) 2007, Hitachi, Ltd.