HP NonStop JDBC Type 4 Driver Programmer's Reference for SQL/MX Release 3.2 HP Part Number: 691128-001 Published: August 2012 Edition: J06.14 and subsequent J-series RVUs and H06.
© Copyright 2012 Hewlett-Packard Development Company, L.P. Legal Notice Confidential computer software. Valid license from HP required for possession, use or copying. Consistent with FAR 12.211 and 12.212, Commercial Computer Software, Computer Software Documentation, and Technical Data for Commercial Items are licensed to the U.S. Government under vendor's standard commercial license. The information contained herein is subject to change without notice.
Contents About this document......................................................................................7 Product Version........................................................................................................................7 Supported Release Version Updates (RVUs)..................................................................................7 Audience...............................................................................................................................
Specifying JDBC Type 4 Properties............................................................................................37 Setting Properties...............................................................................................................37 Creating and Using a Properties File....................................................................................37 Setting Properties in the Command Line................................................................................
Writing Unicode data to a CLOB column..............................................................................57 Reading CLOB Data...............................................................................................................57 Reading ASCII Data from a CLOB Column............................................................................57 Reading Unicode data from a CLOB Column.........................................................................58 Updating CLOB Data..............
Unsupported NonStop SQL Features.........................................................................................78 Unsupported SQL/MX Features............................................................................................78 Unsupported SQL/MP Features............................................................................................79 Other Unsupported Features....................................................................................................79 Restrictions...
About this document This document describes how to use the HP NonStop™ JDBC Type 4 driver for SQL/MX Release 3.2. This driver provides Java applications running on a foreign platform with JDBC access to an HP NonStop SQL/MX database running on the HP NonStop platform. Where applicable, the Type 4 driver conforms to the standard JDBC 3.0 API from Oracle. Product Version HP NonStop JDBC Type 4 Driver for SQL/MX Release 3.2 Supported Release Version Updates (RVUs) This publication supports J06.
Table 1 Summary of Contents (continued) Section Description “Managing the SQL/MX Tables for BLOB and CLOB Data” Describes the database management (administrative) tasks for adding and managing the tables for BLOB and CLOB (page 63) data. The Type 4 driver uses SQL/MX tables in implementing support for BLOB and CLOB data access. “Module File Caching (MFC)” (page 69) Explains the Module File Caching (MFC) feature.
• JDBC Data Access API, FAQs for JDBC 3.2 • JDBC API Comments ◦ Core JDBCore JDBC 3.2 API in the java.sql package ◦ Optional JDBC 3.2 API in the javax.sql package Type 4 Driver 3.
HP NonStop JDBC Type 2 Describes the NonStop JDBC Type 2 Driver functionality, which allows Java Driver Programmer's Reference programmers to remotely develop applications deployed on client workstations to access NonStop SQL/MX databases. HP NonStop MXDM User Guide Describes how to use the NonStop SQL/MX Database Manager (MXDM) to for SQL/MX Release 3.2 monitor and manage the SQL/MX database.
Notation Conventions Hypertext Links Blue underline is used to indicate a hypertext link within text. By clicking a passage of text with a blue underline, you are taken to the location described. For example: This requirement is described under Backup DAM Volumes and Physical Disk Drives. General Syntax Notation This list summarizes the notation conventions for syntax presentation in this manual. UPPERCASE LETTERS. Uppercase letters indicate keywords and reserved words. Type these items exactly as shown.
… Ellipsis. An ellipsis immediately following a pair of brackets or braces indicates that you can repeat the enclosed sequence of syntax items any number of times. For example: M address [ , new-value ]… [ - ] {0|1|2|3|4|5|6|7|8|9}… An ellipsis immediately following a single syntax item indicates that you can repeat that syntax item any number of times. For example: "s-char…" Punctuation. Parentheses, commas, semicolons, and other symbols not previously described must be typed as shown.
1 Introduction to HP NonStop JDBC Type 4 Driver • “Type 4 Driver API Package” (page 13) • “Type 4 Driver Architecture” (page 13) • ◦ “Client Platforms Supported” (page 13) ◦ “Accessing a Database with the Type 4 Driver” (page 13) “Samples” (page 14) Type 4 Driver API Package The Type 4 driver package, com.tandem.t4jdbc, is shipped with the driver software.
Figure 1 Type 4 driver, through the MXCS Service, Accesses an SQL/MX Database Samples The following samples are provided with the Type 4 driver. 14 Sample Description CallableStatementSample Demonstrates the invocation of a stored procedure in Java (SPJ).(1) DBMetaSample Gets metadata from a table by using the Connection interface and ResultSetMetaDatainterface.
Sample Description WLS_MedRecSample Demonstrates that a J2EE application built to work with a database like Oracle can work with NonStop SQL/MX by just reconfiguring the application in BEA WebLogic Server. No coding changes or recompilation are required to switch to NonStop SQL/MX from a different relational database management system (RDBMS).
2 Installing and Verifying the Type 4 Driver • “Prerequisites” (page 16) • “Product Files” (page 17) • “Setting CLASSPATH” (page 17) • “Verifying the Type 4 Driver” (page 18) Prerequisites Hardware and software requirements for the JDBC Type 4 driver are described in the Softdoc file delivered with the product. Read that document before installing the product. You must install and configure the following before installing the JDBC Type 4 driver: • JDK 1.
9. To set up the client environment, configure one of the following paths: On the Windows system: • set JAVA_HOME= • set PATH=%PATH%;%JAVA_HOME%\bin • set CLASSPATH=%CLASSPATH%;\lib\t4sqlmx.jar; On HP-UX, Solaris, NonStop, or Linux system: • export JAVA_HOME= • export PATH=$PATH:$JAVA_HOME/bin • export CLASSPATH=$CLASSPATH:/lib/t4sqlmx.jar: Product Files The product contains the following files and directories.
Verifying the Type 4 Driver To verify the version of the Type 4 driver, use the command: java -jar t4sqlmx.
3 Accessing SQL Databases with SQL/MX • “Communication Overview” (page 19) • “Data Sources” (page 20) ◦ “JDBC Data Source (client-side)” (page 20) ◦ “MXCS Data Source (server-side)” (page 20) • “Security” (page 21) • “Connecting to SQL/MX” (page 21) • “Connecting with the DataSource Interface” (page 21) • ◦ “Overview of Deploying DataSource Objects” (page 21) ◦ “DataSource Object Properties” (page 22) ◦ “Programmatically Creating an Instance of the DataSource Class” (page 22) ◦ “Progra
Figure 2 Type 4 driver—the API layer, IDL layer, and the Transport layer— to the MXCS association server Figure 2 (page 20) illustrates the three layers of the Type 4 driver—the API layer, IDL layer, and the Transport layer— to the MXCS association server. Data Sources • “JDBC Data Source (client-side)” (page 20) • “MXCS Data Source (server-side)” (page 20) The term data source logically refers to a database or other data storage entity.
For information on configuring MXCS XA data source, see the HP NonStop Connectivity Service Manual for SQL/MX Release 3.2. Security Clients connect to the MXCS server with a valid Guardian user ID or alias and password, using standard JDBC 3.0 APIs. An application can make multiple connections to MXCS using different user IDs, and creating different connection objects. The Type 4 driver provides for user name and password encryption before it is sent to MXCS.
An instance of the DataSource class and the DataSource object properties are usually set by an application developer or system administer using a GUI tool as part of the installation of the data source. If you are using an installed data source, skip to topic Programmatically Creating an Instance of a DataSource Object. The subsequent topics show an example of performing these tasks programmatically.
Programmatically Registering the DataSource Object In the following example, the code shows how to register, programmatically, the SQLMXDataSource object ds that was created using the preceding code with JDNI. java.util.Hashtable env = new java.util.Hashtable(); env.put(Context.INITIAL_CONTEXT_FACTORY, "Factory class name here"); javax.naming.Context ctx = new javax.naming.InitialContext(env); ctx.
Connecting using the DriverManager Class “Loading and Registering the Driver” (page 24) “Establishing the Connection” (page 24) “Guidelines for connecting with the Driver Manager” (page 25) The java.sql.DriverManager class is widely used to get a connection, but is less portable than the DataSource class. The DriverManager class works with the Driver interface to manage the set of drivers loaded. When an application issues a request for a connection using the DriverManager.
Guidelines for connecting with the Driver Manager • The Type 4 driver defines a set of properties that you can use to configure the driver. For more information on these properties, see “Type 4 Driver Properties” (page 34). • Java applications can specify the properties in the following ways (listed in the order of precedence): 1. Using the java.util.Properties parameter in the getConnection method of DriverManager class. 2. Using the database URL in the DriverManager.
pool size is determined by the maxPoolSize property value and minPoolSize property value. • By default, connection pooling is disabled. To enable connection pooling, set the maxPoolSize property to an integer value greater than 0 (zero).
Guidelines for Statement Pooling • To enable statement pooling, set the maxStatements property to an integer value greater than 0 and enable connection pooling. See Connection Pooling for more information. • Enabling statement pooling for your JDBC applications might dramatically improve the performance. • Explicitly close a PreparedStatement by using the Statement.close method because PreparedStatement objects that are not in scope are also not reused unless the application explicitly closes them.
Thread-safe SQL/MX Access In the Type 4 driver, API layer classes are implemented as instance-specific objects to ensure thread safety: • SQLMXDataSource.getConnection() is implemented as a synchronized method to ensure thread safety in getting a connection. • After a connection is made, the connection object is instance-specific. • If multiple statements are run on different threads in a single connection, statement objects are serialized to prevent data corruption. "Update ...
information about column data types and character sets, see the HP NonStop SQL/MX Release 3.2 Reference Manual. Controlling String Literal Conversion by Using the Character-Set Properties The Type 4 driver provides character-set mapping properties. These properties allow you to explicitly define the translation of internal SQL/MX database character-set formats to and from the Java string Unicode (UnicodeBigUnmarked) encoding.
The java.sql.ResultSet class contains the methods getString() and getCharacterStream(). These methods return a String parameter and Reader parameter, respectively. Retrieving a Column When you retrieve a column as a String (for example, call the getString() or getCharacterStream methods), the Type 4 driver uses the character-set mapping property key to instantiate a String object (where that key corresponds to the character set of the column).
Trimming Padding for Fixed-Length Character Columns Retrieved values might be longer than inserted values for fixed-length character columns that use the KANJI character set or KSC5601 character set in SQL/MP tables.
A problem can occur when selecting the inserted row. If you were to select the same row as inserted, and call the getString() method, the resulting string would not match the original string. For example: rs = pStmt.executeQuery(“Select c1 from t1”); rs.
4. 5. Save the file, giving it a file name that meets the naming requirements described under File-Name Format for Localized Messages. Put the file in a directory anywhere in the class path for running the JDBC application. The new messages file can be anywhere in the class path for running the user application. At run time, if driver cannot read the messages property file, the driver uses the message portion of the property as the text of the message.
4 Type 4 Driver Properties • • • 34 “Overview of the Type 4 Driver Properties” (page 35) ◦ “Client-Side Properties” (page 35) ◦ “Server-Side Properties” (page 37) “Specifying JDBC Type 4 Properties” (page 37) ◦ “Setting Properties” (page 37) ◦ “Creating and Using a Properties File” (page 37) ◦ “Setting Properties in the Command Line” (page 38) ◦ “Precedence of Property Specifications” (page 38) “Type 4 Driver Property Descriptions” (page 38) ◦ “blobTableName” (page 39) ◦ “catalog” (pag
◦ “queryExecuteTime” (page 46) ◦ “reserveDataLocators” (page 47) ◦ “roundingMode” (page 47) ◦ “schema” (page 48) ◦ “serverDataSource” (page 48) ◦ “T4LogFile” (page 49) ◦ “T4LogLevel” (page 49) ◦ “T4QueryExecuteLogFile” (page 50) ◦ “translationVerification” (page 50) ◦ “url” (page 51) ◦ “useArrayBinding” (page 51) ◦ “useExternalTransaction” (page 52) ◦ “user” (page 52) Overview of the Type 4 Driver Properties “Client-Side Properties” (page 35) “Server-Side Properties” (page 37) Type
Table 4 Pooling Management Properties (continued) Property Name Description Default Value maxPoolSize Sets the maximum number of physical connections that the pool can contain. -1 (Disables connection pooling.) maxStatements Sets the total number of PreparedStatement objects that the connection pool should cache. 0 (Disables statement pooling.) minPoolSize Limits the number of physical connections that can be in the free connection pool. -1 (The minPoolSize value is ignored.
Server-Side Properties Type 4 driver properties that effect server-side operations are summarized in the following tables. Unless otherwise noted in the description, the particular property applies to the DataSource object, DriverManager object, and ConnectionPoolDataSource object. Table 9 Type 4 Driver Server-Side Properties Property Name Description Default Value catalog Sets the default catalog used to access SQL objects referenced None in SQL statements if the SQL objects are not fully qualified.
-Dt4sqlmx.properties=location_of_the_properties_file_on_disk For example, -Dt4sqlmx.properties=C:\temp\t4props To create the file, use the editor of your choice on your workstation to type in the property values.
• “clobTableName” (page 40) • “connectionTimeout” (page 40) • “dataSourceName” (page 41) • “description” (page 41) • “executeBatchWithRowsAffected” (page 41) • “initialPoolSize” (page 41) • “ISO88591” (page 42) • “KANJI” (page 42) • “KSC5601” (page 42) • “language” (page 43) • “LOB Table Name Properties” (page 43) • “loginTimeout” (page 43) • “maxIdleTime” (page 44) • “maxPoolSize” (page 44) • “maxStatements” (page 44) • “mfuStatementCache” (page 45) • “minPoolSize” (page 45
catalog The catalog property sets the default catalog used to access SQL objects referenced in SQL statements if the SQL objects are not fully qualified. Set this property on a DataSource object, ConnectionPoolDataSource object, or DriverManager object. Data type: String Default: none For example, specifying the catalog samdcat: catalog=samdcat clobTableName See “LOB Table Name Properties” (page 43).
Range: -1, 0 to 2147483647 • Zero (0) specifies infinity as the timeout value. • A non-zero positive value overrides the value set on the MXCS server data source, if allowed by the MXCS settings. For more information, see the HP NonStop Connectivity Server Manual for SQL/MX Release 3.2. • A negative value is treated as -1. For an example, consider this scenario. Even if a connection is not being used, it takes up resources.
Set this property on a DataSource object or DriverManager object. This property is ignored for connections made through the ConnectionPoolDataSource object. The driver creates n connections (where n is initialPoolSize) for each connection pool when the first connection is requested. For example, if initialPoolSize is set to 5 for a data source, the driver attempts to create and pool five connections the first time the application calls the data source’s getConnection() method.
For example: java –Dt4sqlmx.KSC5601=ECU_KR For more information, see “Internationalization (I18N) Support” (page 28). language The language property sets the language used for the error messages. For more information about using this property, see Localizing Error and Status Messages. Set this property on a DataSource object, ConnectionPoolDataSource object, or DriverManager object. Data type: String Default: none The value can be any valid Java Canonical Name as listed in the "Canonical Name for java.
If set to 0 (zero), no login timeout is specified. If set to a value more than the specified range such as 330000, the following error message is displayed: Invalid connection property setting: Provided LoginTimeout property value 33000 is invalid and should be less than Short.MAX_VALUE(32767. maxIdleTime NOTE: The maxIdleTime property is available in the V11^AAC and subsequent Software Product Releases (SPRs).
MFU-When the number of PreparedStatements in the cache exceeds the specified maxStatements property, the least used PreparedStatement is closed and removed from the statement cache to allow a new statement. MRU-When the number of PreparedStatements in the cache exceeds the specified maxStatements property value, the recently used PreparedStatements are retained and the earliest PreparedStatement is removed from the statement cache.
Any negative value is treated like -1. Any value greater than maxPoolSize is changed to the maxPoolSize value. The value of minPoolSize is set to -1 when maxPoolSize is -1. The value determines connection pool use as follows: • When the number of physical connections in the free pool reaches the minPoolSize value, the Type 4 driver closes subsequent connections by physically closing them—and not adding them to the free pool.
created. The default log file name is t4sqlmxQueryExecuteTime.log and will be created in the user.home directory. If you want to explicitly specify a log file, see the “T4QueryExecuteLogFile” (page 50). Set this property on a DataSource object, ConnectionPoolDataSource object, or a DriverManager object. Data type: int Units: Number of milliseconds Default: None Range: 1 to 9,223,372,036,854,775,807 (2**63-1) For example: java -Dt4sqlmx.
ROUND_CEILING ROUND_DOWN ROUND_FLOOR ROUND_HALF_DOWN ROUND_HALF_EVEN ROUND_HALF_UP ROUND_UNNECESSARY ROUND_UP • For the definition of rounding mode values, see the java.math.BigDecimal documentation in http://download.oracle.com/javase/1.5.0/docs/api/index.html pages. • If the application sets erroneous values for the roundingMode property, no error is thrown by the Type 4 driver. The driver uses ROUND_HALF_UP value instead.
T4LogFile The T4LogFile property sets the name of the logging file for the Type 4 driver. Set this property on a DataSource object, ConnectionPoolDataSource object, or DriverManager object. Data type: String Default file name is defined by the following pattern: %h/t4sqlmx%u.log where / represents the local pathname separator. %h represents the value of the user.home system property. %u represents a unique number to resolve conflicts. Any valid file name for your system is allowed.
INFO provides informational messages, typically about connection pooling, statement pooling, and resource usage. This information can help in tuning application performance. CONFIG provides static configuration messages that can include property values and other Type 4 driver configuration information. FINE provides tracing information from the Type 4 driver methods described in the Type 4 driver API.
Set this property on a DataSource object, ConnectionPoolDataSource object, or DriverManager object. The value can be TRUE or FALSE. Data type: String Default: FALSE If the translationVerification property’s value is FALSE, and the driver is unable to translate all or part of an SQL statement; the translation is unspecified. In most cases, the characters that are untranslatable are encoded as ISO88591 single-byte question marks (? or 0x3F). No exception or warning is thrown.
useArrayBinding property has no influence on UPDATE and DELETE statements. This property accepts only the following values: true false Data Type: String Default: false For example, specify the value true as useArrayBinding = true useExternalTransaction This property is used to inherit the external applications transaction running on the NonStop system. If the transaction exists, the database operation is performed within the specified transaction.
5 Working with BLOB and CLOB Data • “Architecture for LOB Support” (page 54) • “Setting Properties for the LOB Table” (page 55) • • • ◦ “Specifying the LOB Table” (page 55) ◦ “Reserving Data Locators” (page 55) “Storing CLOB Data” (page 55) ◦ “Inserting CLOB Columns by Using the Clob Interface” (page 56) ◦ “Writing ASCII or MBCS Data to a CLOB Column” (page 56) ◦ “Inserting CLOB Data by Using the PreparedStatement Interface” (page 56) ◦ “Inserting a Clob Object by Using the setClob Method
This chapter describes working with BLOB and CLOB data in JDBC applications. You can use the standard interface described in the JDBC 3.0 API specification to access BLOB and CLOB data in NonStop SQL/MX tables with support provided by the Type 4 driver. BLOB and CLOB are not native data types in an SQL/MX database.
The Unicode LOB table structure for ASCII data is: table_name CHAR(128), data_locator LARGEINT, chunk_no INTEGER, lob_data VARCHAR(1955) CHARACTER SET UCS2 Setting Properties for the LOB Table • “Specifying the LOB Table” (page 55) • “Reserving Data Locators” (page 55) Before running the JDBC application that uses BLOB and CLOB data through the JDBC API, the database administrator must create the LOB tables. For information on creating LOB tables, see Managing LOB Data with the Lob Admin Utility.
Inserting CLOB Columns by Using the Clob Interface When you insert a row containing a CLOB data type, and before the column can be updated with real CLOB data, you can insert a row that has an empty CLOB value. To insert an empty CLOB value in a NonStop SQL/MX database, specify the EMPTY_CLOB() function for the CLOB column in the insert statement. NOTE: • The EMPTY_CLOB() function is an HP NonStop specific function and might not work on other databases.
InputStream inputAsciiStream; PreparedStatement ps = conn.prepareStatement("insert into myTable (myClobColumn) values (?)"); ps.setAsciiStream(1, inputAsciiStream, length_of_data); ps.executeUpdate(); The Type 4 driver reads the data from InputStream and writes the data to the LOB table. The Type 4 driver substitutes the next-available data locator for the parameter of the CLOB column in the table.
// read Clob data using the InputStream byte[] myClobData; myClobData = new byte[length]; is.read(myClobData, offset, length); To read ASCII or MBCS data from the CLOB column by using InputStream: // obtain the InputStream from ResultSet InputStream is = rs.getAsciiStream("myClobColumn"); // read Clob data using the InputStream byte[] myClobData; myClobData = new byte[length]; is.
Replacing Clob Objects You can replace Clob objects in the following ways: • Use the EMPTY_CLOB() function to replace the Clob object with the empty Clob object, then insert new data as described under Inserting CLOB Columns by Using the Clob Interface. • Use the PreparedStatement.setAsciiStream() or setCharacterStream() method to replace the existing Clob object with new CLOB data.
Blob myBlob = null; Statement s = conn.createStatement(); ResultSet rs = s.executeQuery("Select myBlobColumn from myTable where …For update"); if (rs.next()) myBlob = rs.getBlob(1); You can now write data to the BLOB column. See Writing Binary Data to a BLOB Column. NOTE: Limitation: Do not rename the BLOB column in the select query. Writing Binary Data to a BLOB Column To write data to the BLOB column, use the Blob interface.
// read Blob data using the InputStream byte[] myBlobData; myBlobData = new byte[length]; is.read(myBlobData, offset, length); Using InputStream: // obtain the InputStream from ResultSet InputStream is = rs.getBinaryStream("myBlobColumn"); // read Blob data using the InputStream byte[] myBlobData; myBlobData = new byte[length]; is.read(myBlobData, offset, length); Updating BLOB Data To update BLOB data, use the methods in the Blob interface or use the updateBlob method of the ResultSet interface.
NULL and Empty BLOB or Empty CLOB Value The data locator can have a NULL value if the BLOB or CLOB column is omitted in the insert statement. The Type 4 driver returns NULL when the application retrieves the value for such a column. When the application uses the EMPTY_BLOB() function or the EMPTY_CLOB() function to insert empty BLOB or CLOB data into the BLOB or CLOB column, the Type 4 driver returns the Blob or Clob object with no data.
6 Managing the SQL/MX Tables for BLOB and CLOB Data This chapter describes the creation and management of the tables required to support LOB data for database administrators.
Using MXCI To Create Base Tables that Have LOB Columns Before using the procedure to create the tables, note that when using MXCI to create base tables, you must enter the following special command in the MXCI session to enable the base-table creation of tables that have LOB (BLOB or CLOB) columns: CONTROL QUERY DEFAULT JDBC_PROCESS 'TRUE' Follow these steps to create a base table that has LOB columns: 1. At the OSS prompt, enter the following to invoke the SQL/MX utility MXCI: mxci 2.
NOTE: If you are creating triggers, ensure that the base table that contains the CLOB column or BLOB column has already been created. Information about using the Lob Admin Utility is described under these topics. • “Running the Lob Admin Utility” (page 65) • “Help Listing from the Type 4 Lob Admin Utility” (page 66) • “Creating LOB Tables” (page 66) Running the Lob Admin Utility Run the T4LobAdmin utility in the OSS environment. The format of the command is: java [java_options] com.tandem.t4jdbc.
table_name The table_name represents a base table that contains LOB columns. The table_name is of the form: [catalogName.][schemaName.]baseTableName For information about catalog, schema, and table names, see the HP NonStop SQL/MX Release 3.2 Reference Manual. Help Listing from the Type 4 Lob Admin Utility To display help for the Type 4 Lob Admin Utility, type: java -Dt4sqlmx.properties=t4lob.prop com.tandem.t4jdbc.T4LobAdmin -help Example 1 Example 1.
-drop Generates SQL statements to drop triggers. -exec Executes the SQL statements that are generated. For example, the following command generates the SQL statements to create the triggers for the base table sales.paris.pictures, which contains a BLOB column, and executes those statements. Note: This command must be typed on one line. java -Dt4sqlmx.blobTableName=sales.paris.lobTable4pictures com.tandem.t4jdbc.T4LobAdmin -trigger -exec sales.paris.
NOTE: Adding a trigger can affect up to three schemas. For each schema, you must either own the schema or be the super ID. 68 • The schema where the trigger is created. • The schema where the subject table (LOB table) exists. • The schema where the referenced table (base table) exists.
7 Module File Caching (MFC) • “MFC Overview” (page 69) • “MFC Design” (page 69) • “Configuring MFC” (page 69) • “Enabling MFC” (page 69) • “MFC Usage Scenarios” (page 70) • “MFC Tuning Recommendations” (page 70) • “MFC Limitations” (page 71) MFC Overview Applications that use the JDBC Type 4 driver might encounter repeated SQL compiles while using statements and PreparedStatements. To overcome this issue, a driver side Most Frequently Used (MFU) cache is used.
For more information on MFC, see: • Module File Cache for NonStop SQL/MX at http://h20195.www2.hp.com/V2/ GetDocument.aspx?docname=4AA3-8922ENW&cc=us&lc=en • Distributed transactions (XA) on HP NonStop systems at http://h20195.www2.hp.com/V2/ GetPDF.aspx/4AA3-8405ENW.pdf MFC Usage Scenarios The benefits of lower processor utilization and the reduction in subsequent compile times mentioned in this section are applicable on the NonStop Server. • JDBC T4 applications using java.sql.Connection.
For example, if the application has 1000 unique queries, configure DSC (t4sqlmx.maxStatements) to 600. The applications with more number of queries configured in the DSC, cause memory swap. Therefore, reduce the DSC number and increase the queries in the MFC. The JDBC/T4 driver, in memory, caches the most frequently used statements. MFC Limitations MFC is not a replacement for the JDBC T4 Driver Side Cache (DSC); the JDBC T4 driver side cache has a better response time than MFC.
8 Type 4 Driver Compliance • “Compliance Overview” (page 72) • “Unsupported Features” (page 72) • “Deviations” (page 74) • “HP Extensions” (page 75) ◦ “Internationalization of Messages” (page 75) • “Conformance of DatabaseMetaData Methods' Handling of Null Parameters” (page 76) • “Type 4 Driver Conformance to SQL Data Types” (page 76) ◦ “JDBC Data Types” (page 76) • “Floating-Point Support” (page 78) • “JDBC Type 4 Driver Features” (page 78) • “Unsupported NonStop SQL Features” (page 78
Method Comments PreparedStatement.setArray(int parameterIndex, Array x) The particular PreparedStatement PreparedStatement.setRef(int parameterIndex, Ref x) method is not supported. PreparedStatement.setURL(int parameterIndex, URL x) ResultSet.getArray(int columnIndex) ResultSet.getArray(String columnName) ResultSet.getObject(int columnIndex, Map map) ResultSet.getObject(String columnName, Map map) ResultSet.getRef(int columnIndex) ResultSet.getRef(String columnName) ResultSet.
The following interfaces in the java.sql package are not implemented in the Type 4 driver: Interrface Comments java.sql.Array java.sql.Ref java.sql.Savepoint java.sql.SQLData java.sql.SQLInput java.sql.SQLOutput java.sql.Struct The underlying data types are not supported by SQL/MX. The following interfaces in the javax.sql package are not implemented in the Type 4 driver: Method Comments javax.sql.RowSet javax.sql.RowSetInternal javax.sql.RowSetListener javax.sql.RowSetMetaData javax.sql.
Method Comments java.sql.DatabaseMetaData.getUDTs(String catalog, String schemaPattern, String tableNamePattern, int[] types) BASE_TYPE is added to the column data, but its value is set to NULL because SQL/MXdoes not support the base type. java.sql.DatabaseMetaData.getVersionColumns() Mimics the DatabaseMetaData.
separate property files based on the language and country. This extension does not apply to all messages that can occur when running JDBC applications. For details, see Localizing Error and Status Messages. Conformance of DatabaseMetaData Methods' Handling of Null Parameters This topic describes how the Type 4 driver determines the value of null parameters passed as a parameter value on DatabaseMetaData methods.
JDBC Data Type Support by JDBC Type 4 Driver for NonStop SQL/MX SQL/MX Data Type Types.CLOB Yes LARGEINT Types.DATE Yes DATE Types.DECIMAL Yes DECIMAL(p,s) Types.DISTINCT No N.A. Types.DOUBLE Yes DOUBLE PRECISION Types.FLOAT Yes FLOAT(p) Types.INTEGER Yes INTEGER Types.JAVA_OBJECT No N.A. Types.LONGVARBINARY Data type is mapped by SQL/MX . Data type varies from that used for table creation. VARCHAR(n)¹ Types.LONGVARCHAR Yes Maximum length is 4018 VARCHAR[(n)] Types.
SQL/MX Data Type SQL/MP Data Type INTERVAL DAY(p) TO SECOND DATETIME MONTH TO HOUR INTERVAL HOUR(p) DATETIME MONTH TO SECOND INTERVAL HOUR(p) TO MINUTE DATETIME DAY INTERVAL HOUR(p) TO SECOND DATETIME DAY TO HOUR INTERVAL MINUTE(p) DATETIME DAY TO MINUTE INTERVAL MINUTE(p) TO SECOND DATETIME DAY TO SECOND INTERVAL SECOND(p) DATETIME HOUR DATETIME HOUR TO MINUTE DATETIME MINUTE DATETIME MINUTE TO SECOND DATETIME SECOND DATETIME FRACTION Floating-Point Support The Type 4 driver supports only I
Unsupported SQL/MP Features • SQL/MP DDL support • Columns described with the character sets ISO8859/2 through ISO8859/9 • Utility commands • Embedded-only SQL/MP statements • Transaction Control statements • Stored procedures Refer to the NonStop SQL/MP documentation set for a complete list of supported features. Other Unsupported Features These features are not required for JDBC 3.0 compliance, and they are not supported by the NonStop JDBC Type 4 Driver.
9 Tracing and Logging Facilities The Type 4 driver provides two tracing and logging facilities: • “Standard JDBC Tracing and Logging Facility” (page 80) • “The Type 4 Driver Logging Facility” (page 80) ◦ “Accessing the Type 4 Driver Logging Facility” (page 81) ◦ “Controlling Type 4 Driver Logging Output” (page 81) ◦ “Message Format” (page 81) ◦ “Examples of Logging Output” (page 82) ◦ “Controlling the QueryExecuteTime Logging Facility” (page 83) ◦ “QueryExecuteTime logging Message Format” (pa
• “Controlling the QueryExecuteTime Logging Facility” (page 83) • “QueryExecuteTime logging Message Format” (page 83) • “Examples of QueryExecuteTime Logging Output” (page 83) The Type 4 driver Logging facility allows you to retrieve internal tracing information, which you can use in debugging the driver. It also allows you to capture error and warning messages.
server-id If applicable, information about the MXCS server associated with the message. The server-id is of the form: TCP:node-name.server-name/port-number:NonStopODBC where node-name The name of the NonStop server node. server-name The Guardian name of the server. port-number The port to which the server is connected. For example: TCP:\banshee-tcp.$Z0133/46003:NonStopODBC dialogue-id If applicable, the dialogue-id used for the MXCS connection.
Controlling the QueryExecuteTime Logging Facility The Type 4 driver provides two properties that you can use to control the QueryExecute logging output: • queryExecuteTime: Specifies the time limit to log information about statements which are taking more than the specified time for execution. For information on using this property, see “queryExecuteTime” (page 46). • T4QueryExecuteLogFile: Specifies the file in which the driver must log information.
10 Migration and Compatibility • “JDBC Drivers” (page 84) • “Third-Party Databases” (page 84) • “Operating Systems” (page 84) • “Compatibility” (page 84) JDBC Drivers Driver You Are Migrating From Code Changes JDBC Type 3 Driver No JDBC Driver for SQL/MX No (JDBC/MX), a Type 2 driver Configuration Changes Considerations url, catalog, schema, logging facilities N.A.
11 Messages • “About the Message Format” (page 85) • “Type 4 Driver Error Messages” (page 85) About the Message Format Messages are listed in numerical SQLCODE order. Descriptions include the following: SQLCODE SQLSTATE message-text Cause [ What occurred to trigger the message.] Effect [ What is the result when this occurs. ] Recovery [ How to diagnose and fix the problem.
29006 HY000 Invalid Result Set concurrency Cause: An attempt was made to set an invalid result-set concurrency value. Effect: The SQLMXStatement call with resultSetConcurrency fails. Recovery: Valid resultSetConcurrency values are: CONCUR_READ_ONLY and CONCUR_UPDATABLE. nl 29007 07009 Invalid descriptor index Cause: A ResultSetMetadata column parameter or a ParameterMetaData param parameter is outside of the descriptor range.
29013 HY024 Fetch size is less than 0 Cause: The size set for ResultSet.setFetchSize rows to fetch is less than zero. Effect: The number of rows that need to be fetched from the database when more rows are needed for a ResultSet object is not set. Recovery: Set the setFetchSize() method rows parameter to a value greater than zero. nl 29015 HY024 Invalid fetch direction Cause: The setFetchDirection() method direction parameter is set to an invalid value.
29022 HY010 Function sequence error Cause: The PreparedStatement.execute() method does not support the use of the PreparedStatement.addBatch() method. Effect: An exception is reported; the operation is not completed. Recovery: Use the PreparedStatement.executeBatch() method. nl 29026 HY000 Transaction can't be committed or rolled back when AutoCommit mode is on Cause: An attempt was made to commit a transaction while AutoCommit mode is enabled. Effect: The transaction is not committed.
Effect: The designated setter or updater method does not modify the ASCII or Binary or Character stream. Recovery: Informational message only; no corrective action is needed. nl 29036 HY000 Unsupported encoding {0} Cause: The character encoding is not supported. Effect: An exception is thrown when the requested character encoding is not supported. Recovery: ASCII (ISO88591), KANJI, KSC5601, and UCS2 are the only supported character encodings. SQL/MP tables do not support UCS2 character encoding.
Effect: The number of rows that are fetched from the database when more rows are needed is not set. Recovery: For the setFetchSize() method, supply a valid row value that is greater than or equal to 0. nl 29043 HY000 Max rows value is not valid Cause: An attempt was made to set a limit of less than 0 for the maximum number of rows that any ResultSet object can contain. Effect: The limit for the maximum number of rows is not set.
29050 HY107 Row value out of range Cause: A call to getCurrentRow retrieved is outside the first and last row range. Effect: The current row is not retrieved. Recovery: It is an informational message only; no recovery is needed. Report the entire message to your service provider. nl 29051 01S02 ResultSet type changed to TYPE_SCROLL_INSENSITIVE Cause: The Result Set Type was changed. Effect: None. Recovery: This message is reported as an SQL Warning.
29059 HY000 'blobTableName' property is not set or set to null value or set to invalid value Cause: Attempted to access a BLOB column without setting the property t4sqlmx.blobTableName, or the property is set to an invalid value. Effect: The application cannot access BLOB columns. Recovery: Set the t4sqlmx.blobTableName property to a valid LOB table name. The LOB table name is of format catalog.schema.lobTableName. nl 29060 HY000 't4sqlmx.
29100 HY000 An internal error occurred. Cause: Internal error. Effect: Operation fails. Recovery: None. Report the entire message to your service provider. nl 29101 HY000 Contact your HP service provider. Cause: Internal error. Effect: Operation fails. Recovery: None. Report the entire message to your service provider. nl 29102 HY000 Error while parsing address address Cause: The address format was not recognized. Effect: Operation fails. Recovery: Refer to url Property for the valid address format.
Effect: Operation fails. Recovery: The address returned by the MXCS association server was not in the expected format. Report the entire message to your service provider. nl 29109 HY000 //<{IP Address|Machine Name}[:port]/database name> Cause: Informational message. Effect: N.A. Recovery: N.A. nl 29110 HY000 Address is missing an IP address or machine name. Cause: An IP address or machine name is required, but missing. Effect: The operation fails. Recovery: Include a valid IP address or machine name.
Recovery: Use the getCause method on the Exception to determine the appropriate recovery action. nl 29116 HY000 Socket is closed. Cause: Socket close error. Effect: The operation fails. Recovery: Evaluate the returned value from the getCause method on the Exception to determine the appropriate recovery action. nl 29117 HY000 Error while closing session. Cause: cause Cause: An error was encountered while closing a session. Effect: The operation fails.
Effect: Operation fails. Recovery: Evaluate the returned value from the getCause method on the Exception to determine the appropriate recovery action. nl 29124 HY000 An execute direct message error occurred. Cause: cause Cause: Unable to perform this operation. Effect: Operation fails. Recovery: Evaluate the returned value from the getCause method on the Exception to determine the appropriate recovery action. nl 29125 HY000 An execute direct rowset message error occurred.
29131 HY000 An initialize dialogue message error occurred. Cause: cause Cause: Unable to perform this operation. Effect: Operation fails. Recovery: Evaluate the returned value from the getCause method on the Exception to determine the appropriate recovery action. nl 29132 HY000 A prepare message error occurred. Cause: cause Cause: Unable to perform this operation. Effect: Operation fails.
29138 HY000 An end transaction reply error occurred. Cause: Unable to perform this operation. Effect: Operation fails. Recovery: Evaluate the returned value from the getCause method on the Exception to determine the appropriate recovery action. nl 29139 HY000 An execute call reply error occurred. Cause: Unable to perform this operation. Effect: Operation fails. Recovery: Evaluate the returned value from the getCause method on the Exception to determine the appropriate recovery action.
Recovery: Evaluate the returned value from the getCause method on the Exception to determine the appropriate recovery action. nl 29146 HY000 A get sql catalogs reply error occurred. Cause: Unable to perform this operation. Effect: Operation fails. Recovery: Evaluate the returned value from the getCause method on the Exception to determine the appropriate recovery action. nl 29147 HY000 An initialize dialogue reply error occurred. Cause: Unable to perform this operation. Effect: Operation fails.
29153 HY000 Invalid authorization specification Cause: Incorrect user name and/or password. Effect: Operation fails. Recovery: Retry with correct user name and/or password. nl 29154 HY000 Timeout expired Cause: Unable to perform this operation. Effect: Operation fails. Recovery: Retry and/or change the timeout value for the operation. nl 29155 HY000 Unknown message type Cause: Internal error. Effect: Operation fails. Recovery: Check the EMS event log for server errors.
29160 HY000 The message header was not long enough. Cause: The message returned by the server was too short to be a valid message. Effect: Operation fails. Recovery: None. Report the entire message to your service provider. nl 29161 S1000 Unable to authenticate the user because of an NT error: {0} Cause: A message returned by the server. Effect: Operation fails. Recovery: None. Report the entire message to your service provider. nl 29162 S1000 Unexpected programming exception has been found: exception.
Recovery: None. Report the entire message to your service provider. nl 29168 HY000 Unknown reply message error: error error detail: error_detail Cause: Server returned an error. Effect: Operation fails. Recovery: Evaluate any error or error detail information accompanying the message. Check the EMS event log for server errors. See the Operator Messages Manual for server errors. See the HP NonStop Connectivity Service Manual for SQL/MX Release 3.2 for corrective action.
29177 HY000 Data cannot be null. Cause: The data structure returned by the MXCS server contains an unexpected null value. Effect: Operation fails. Recovery: Evaluate the EMS message log on the MXCS server, if any, and refer to the SQL/MX Connectivity Service Manual for corrective action. nl 29178 HY000 No column value has been inserted. Cause: The value for a required column was not specified. Effect: Operation fails. Recovery: Ensure that all required column values are specified, and retry the operation.
Recovery: None. nl 29181 HY000 XABROKER returned error. Message: {0} Cause: Unable to perform the operation. Effect: Operation fails. Recovery: None. nl 29182 HY000 XABROKER max connection limit reached. Broker Message: {0} Cause: The application attempted to create more connections than the allowed limit. Effect: The application cannot create more connections to the data source. Recovery: Evaluate any error or error detail information; check the maximum number of connections that are allowed.
nl 29188 HY000 Invalid Tx connection. Connection closed.- {0} Cause: An action was attempted when the connection to the database was closed. Effect: The database is inaccessible. Recovery: Retry the action after the connection to the database is established. nl 29189 HY000 Invalid Tx operation on XA connection.- {0} Cause: The feature listed is not supported by the JDBC driver. Effect: An attempt was made to perform an invalid Tx operation.
29194 HY000 SQL Server and connection could not be aborted successfully, SERVER NOT FOUND ERROR (closeConnectionUponQueryTimeout Enabled). Cause: The long running query timed out after specified number of seconds. Effect: The MXOSRVR on NSK could did not stop successfully. Recovery: None. The corresponding MXOSRVR to be stopped is unavailable. nl 29195 HY000 SQL Server and connection could not be aborted successfully, SERVER IN USE BY ANOTHER CLIENT ERROR (closeConnectionUponQueryTimeout Enabled).
12 Providing a secure JDBC connection using NonStop SSL This chapter discusses JDBC connection encryption by using NonStop SSL.
3. To run the NonStop SSL SETUP, enter the following command at the TACL prompt: $SYSTEM ZNSSSL 10> run $SYSTEM.ZNSSSL.SETUP 4. 5. 6. 7. 8. Enter [7] to select ODBC/MX SERVER as run mode. Enter the home terminal. The default value is $YMIOP.#CLCI. Enter the CPU on which you want to run SSL. The default value is CPU 3. Enter the SSL process name. The default value is $ODBS3. Enter the TCP/IP process name for the subnet on which the ODBC/MX MXCS Service runs. The default value is $ZTC0. 9.
To install and configure the Remote Proxy Client on Windows, complete the following steps on the Windows workstation: 1. Download the $SYSTEM.ZNSSSL.PROXYEXE file in binary format to your ODBC/MX client workstation, and rename it to PROXY.EXE. 2. On the ODBC/MX client workstation, run PROXY.EXE to start the RemoteProxy installation program, and follow the installation instructions in the wizards. 3. Double-click the NonStop SSL RemoteProxy icon in your system tray.
A Sample Programs Accessing CLOB and BLOB Data • “Sample Program Accessing CLOB Data” (page 110) • “Sample Program Accessing BLOB Data” (page 112) This appendix shows two working programs. Sample Program Accessing CLOB Data This sample program shows operations that can be performed through the Clob interface or through the PreparedStatement interface. The sample program shows examples of both interfaces taking a variable and putting the variable's value into a base table that has a CLOB column.
long end; Connection conn1 = null; // Set t4sqlmx.clobTableName System Property. This property // can also be added to the command line through // "-Dt4sqlmx.clobTableName=...", or a // java.util.Properties object can be used and passed to // getConnection. System.setProperty( "t4sqlmx.clobTableName","cat.sch.clobdatatbl" ); if (args.length < 2) { System.out.println("arg[0]=; arg[1]=file; arg[2]="); return; } String k = "K"; for (int i=0; i<5000; i++) k = k + "K"; System.out.
do { System.out.println("Messge : " + e.getMessage()); System.out.println("Error Code : " + e.getErrorCode()); System.out.println("SQLState : " + e.getSQLState()); } while ((next = next.getNextException()) != null); } // Clob interface example - This technique is suitable when // the LOB data is already in the app, such as having been // transferred in a msgbuf. try { // insert a second base table row with an empty LOB column System.out.println("CLOB interface EMPTY LOB insert...
// // The LOB table for this example is created through the // T4LobAdmin utility as: // >> create table cat.sch.blobdatatbl // (table_name char(128) not null not droppable, // data_locator largeint not null not droppable, // chunk_no int not null not droppable, // lob_data varchar(3880), // primary key(table_name, data_locator, chunk_no)) // attributes extent(1024), maxextents 768 ; // // ***** The following is the blob interface...
numBytes = iXstream.available(); if (args.length == 3) numBytes = Integer.parseInt(args[2]); recKey = Integer.parseInt(args[0]); System.out.println("Key: " + recKey +"; Using " + numBytes + " of file " + args[1]); try { Class.forName("com.tandem.t4jdbc.SQLMXDriver"); start = System.currentTimeMillis(); //url should be of the form: // jdbc:t4sqlmx://ip_address|machine_name:port_number/: String url = "jdbc:t4sqlmx://mymachine:port/:"; String user ="UserName"; String password="password"; conn1 = DriverManager.
System.out.println("Obtaining BLOB data to update (EMPTY in this case)..."); PreparedStatement stmt3 = conn1.prepareStatement("select tiff from blobtiff where col1 = ? for update"); stmt3.setInt(1,recKey+1); ResultSet rs = stmt3.executeQuery(); if (rs.next()) tiff = rs.getBlob(1); // has to be there else the base table insert failed System.out.println("Writing data to previously empty BLOB..."); OutputStream os = tiff.setBinaryStream(1); byte[] bData = k.getBytes(); os.write(bData); os.close(); conn1.
Glossary A abstract class In Java, a class designed only as a parent from which subclasses can be derived, which is not itself suitable for instantiation. An abstract class is often used to "abstract out" incomplete sets of features, which can then be shared by a group of sibling subclasses that add different variations of the missing pieces.
CLOB Short for Character Large OBject, text data stored as a single entity in a database management system. A Java Clob object (Java type, java.sql.Clob) corresponds to the SQL CLOB data type. command The operation demanded by an operator or program; a demand for action by, or information from, a subsystem. A command is typically conveyed as an interprocess message from a program to a subsystem.
H Hotspot virtual machine See Java Hotspot virtual machine. HP JDBC Driver for SQL/MP (JDBC/MP) The product that provides access to SQL/MP and conforms to the JDBC API. HP JDBC Driver for SQL/MX (JDBC/MX) The product that provides access to SQL/MX and conforms to the JDBC API. HP NonStop ODBC Server The HP implementation of ODBC for NonStop systems. HP NonStop operating system The operating system for NonStop systems.
I IEC See International Electrotechnical Commission (IEC). IEEE Institute for Electrical and Electronic Engineers (IEEE). inlining Replacing a method call with the code for the called method, eliminating the call. interactive Question-and-answer exchange between a user and a computer system. interface In general, the point of communication or interconnection between one person, program, or device and another, or a set of rules for that interaction. See also API.
Java Database Connectivity (JDBC) An industry standard for database-independent connectivity between the Java platform and relational databases such as NonStop SQL/MP or NonStop SQL/MX. JDBC provides a call-level API for SQL-based database access. Java HotSpot virtual machine The Java virtual machine implementation designed to produce maximum program-execution speed for applications running in a server environment.
javap The Java class file disassembler, which disassembles compiled Java files and prints a representation of the Java bytecode. Also, the command to run the Java class file disassembler. JCK See Java Conformance Kit (JCK). jdb The Java Debugger, which helps you find and fix errors in Java programs. Also, the command to run the Java Debugger .jdb uses the Java Debugger API. JDBC See Java Database Connectivity (JDBC). JDBC/MP See HP JDBC Driver for SQL/MP (JDBC/MP).
NonStop Technical Library The browser-based interface to NonStop computing technical information. NonStop Technical Library replaces HP Total Information Manager (TIM). NSK See HP NonStop operating system. NSKCOM A program management tool for swap space. O Object Management Group (OMG) The standards body that defined CORBA.
Pathway CGI An extension to iTP Secure WebServer that provides CGI -like access to Pathway server classes. Extended in the NonStop Server for Java so that Java servlets can be invoked from a ServletServerClass, a special Pathway CGI server. Pathway/TS An HP product that provides tools for developing and interpreting screen programs to support OLTP programs in the Guardian environment on NonStop servers. Pathway/TS screen programs communicate with terminals and intelligent devices.
serialized object An object that has undergone object serialization. serialver The Serial Version Command, which returns the serialVersionUID of one or more classes. Also, the command to run the Serial Version Command. server 1. 2. 3. servlet An implementation of a system used as a stand-alone system or as a node in an Expand network. The hardware component of a computer system designed to provide services in response to requests received from clients across a network.
thread A task that is separately dispatched and that represents a sequential flow of control within a process. threads The nonnative thread package that is shipped with Sun Microsystems Java SE 6.0. throw Java keyword used to raise an exception. throws Java keyword used to define the exceptions that a method can raise.
