HP NonStop JDBC Type 4 Driver Programmer's Reference for SQL/MX Release 3.2.1 HP Part Number: 691128-005 Published: November 2013 Edition: J06.15 and subsequent J-series RVUs and H06.
© Copyright 2013 Hewlett-Packard Development Company, L.P. 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............................................................................................36 Setting Properties...............................................................................................................37 Creating and Using a Properties File....................................................................................37 Setting Properties in the Command Line................................................................................
Inserting CLOB Data by Using the PreparedStatement Interface................................................57 Inserting a Clob Object by Using the setClob Method.............................................................58 Inserting a CLOB column with Unicode data using a Reader....................................................58 Writing Unicode data to a CLOB column..............................................................................58 Reading CLOB Data........................................
JDBC Data Types...............................................................................................................77 Floating-Point Support..............................................................................................................79 JDBC Type 4 Driver Features....................................................................................................79 Unsupported NonStop SQL Features......................................................................................
About this document This document describes how to use the HP NonStop™ JDBC Type 4 driver for SQL/MX Release 3.2.1. 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.1 Supported Release Version Updates (RVUs) This publication supports J06.
Table 1 Summary of Contents (continued) Section Description “Type 4 Driver Properties” (page 33) Describes how to set the properties that configure the driver and provides a detailed description of each property. “Working with BLOB and CLOB Data” (page 54) Describes working with BLOB and CLOB data in JDBC applications using the standard interface described in the JDBC 3.0 API specification.
• 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.1 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.
HP Encourages Your Comments HP encourages your comments concerning this document. We are committed to providing documentation that meets your needs. Send any errors found, suggestions for improvement, or compliments to docsfeedback@hp.com. Include the document title, part number, and any comment, error found, or suggestion for improvement you have concerning this document.
1 Introduction to HP NonStop JDBC Type 4 Driver • “Type 4 Driver API Package” (page 14) • “Type 4 Driver Architecture” (page 14) • ◦ “Client Platforms Supported” (page 14) ◦ “Accessing a Database with the Type 4 Driver” (page 14) “Samples” (page 15) 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. 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.
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 20) • “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.
The Type 4 driver provides for user name and password encryption before it is sent to MXCS. The password is encrypted with a proprietary algorithm provided by the MXCS product.
DataSource Object Properties A DataSource object has properties that identify and describe the actual data source that the object represents. These properties include such information as the URL for the MXCS association server, the database schema and catalog names, the location of the database server, the name of the database, and so forth. NOTE: When a JDBC application uses any server (MXCS) data source, including the default data source, the data source must have been started by MXCS.
Retrieving a DataSource Instance by using JNDI and to the Data Source Typically, the JDBC application looks up the data source JNDI name from a context object. After the application has the DataSource object, the application makes a getConnection() call on the data source and gets a connection. To connect to and use the data source associated with the SQL/MX database, the JDBC application performs the following steps are listed together with the application code to perform the operation: 1.
the set of drivers loaded. When an application issues a request for a connection using the DriverManager.getConnection method and provides a URL, the DriverManager finds a suitable driver that recognizes this URL and obtains a database connection using that driver. com.tandem.t4jdbc.SQLMXDriver is the Type 4 driver class that implements the java.sql.Driver interface.
3. Using a properties file for the JDBC driver. The properties file is passed as a command-line parameter. The format to enter the properties file in the command-line is: -Dt4sqlmx.properties=location-of-the-properties-file-on-disk For example, -Dt4sqlmx.properties=C:\temp\t4props For information about the properties file, see “Creating and Using a Properties File” (page 37) in the “Type 4 Driver Properties” (page 33). 4. Using JDBC properties with the -D option in the command line.
schema username password blobTableName clobTableName serverDataSource Therefore, connections that have the same values for the combination of a set of properties are pooled together. NOTE: The connection-pooling property values used at the first connection of a given combination are effective throughout the life of the process. An application cannot change any of these property values after the first connection for a given combination.
• The Type 4 driver assumes that any SQL CONTROL statements in effect at the time of execution or reuse are the same as those in effect at the time of SQL/MX compilation. If this condition is not true, reuse of a PreparedStatement object might result in unexpected behavior. • You should avoid SQL/MX recompilation to yield performance improvements from statement pooling. The SQL/MX executor automatically recompiles queries when certain conditions are met.
Internationalization (I18N) Support “String Literals Used in Applications” (page 28) “Controlling String Literal Conversion by Using the Character-Set Properties” (page 28) “Trimming Padding for Fixed-Length Character Columns” (page 30) “Localizing Error Messages and Status Messages” (page 31) String Literals Used in Applications Internationalization support in the driver affects the handling of string literals.
Table 2 Corresponding SQL/MX Character Sets and Java Encoding Sets (continued) SQL/MX Character Set Corresponding JavaEncoding Set—Canonical Name for java.io and java.lang API Description NOTE: KAJNI is supported in SQL/MP tables only. KSC5601 EUC_KR Double-character character set required on systems used by government and banking within Korea. KSC5601 encoding is big-endian. NOTE: KSC5601 is supported in SQL/MP tables only.
create table t1 (c1 char(20) character set ISO88591); > java –DISO88591=SJIS test1.java The following method invocation sets column one of stmt to the String “abcd” where “abcd” is encoded as SJIS. The charset parameter to the String getBytes method is SJIS. stmt.setString(1, “abcd”); Controlling an Exception You can use the translationVerification property to explicitly define the behavior of the driver if the driver cannot translate all or part of an SQL parameter.
byte b1 = new byte[4]; b1[0] = 0x83; \___ Katakana letter A b1[1] = 0x41; / b1[2] = 0x83; \___ Katakana letter small i b1[3] = 0x42; / String k1 = new String(b1, “SJIS”); Internally, the JVM stores k1 as UCS2 in 4 octets (bytes). The UCS2 encoding would be: 0x30 0xA2 0x30 0xA3 An SQL insert statement is prepared: pStmt = conn.PrearedStatement(“insert into t1 values (?)”); The statements parameter is set to the string: pStmt.
Localized-Message String Format A localized message file contains strings in the following form: message=message_text For example: driver_err_error_from_server_msg=An error was returned from the server. Error: {0} Error detail: {1} where the message is driver_err_error_from_server_msg. The message_text is: An error was returned from the server.
4 Type 4 Driver Properties • • • “Overview of the Type 4 Driver Properties” (page 34) ◦ “Client-Side Properties” (page 34) ◦ “Server-Side Properties” (page 36) “Specifying JDBC Type 4 Properties” (page 36) ◦ “Setting Properties” (page 37) ◦ “Creating and Using a Properties File” (page 37) ◦ “Setting Properties in the Command Line” (page 37) ◦ “Precedence of Property Specifications” (page 37) “Type 4 Driver Property Descriptions” (page 38) ◦ “blobTableName” (page 39) ◦ “catalog” (page 39
◦ “properties” (page 46) ◦ “queryExecuteTime” (page 46) ◦ “reserveDataLocators” (page 46) ◦ “roundingMode” (page 47) ◦ “schema” (page 47) ◦ “serverDataSource” (page 47) ◦ “T4LogFile” (page 48) ◦ “T4LogLevel” (page 49) ◦ “T4QueryExecuteLogFile” (page 50) ◦ “sslEncryption” (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 Prop
Table 4 Pooling Management Properties Property Name Description Default Value initialPoolSize Sets the initial connection pool size when connection pooling -1 (Do not create an initial is used with the Type 4 driver . (Ignored for connections made connection pool.) through the ConnectionPoolDataSource object.) maxIdleTime Set the number of seconds that a physical connection can remain unused in the pool before the connection is closed. 0 (Specifies no limit.
Table 8 Miscellaneous Client-Side Properties (continued) Property Name Description Default Value roundingMode Specifies the rounding behavior of the Type 4 driver. ROUND_HALF_UP co l seConnectionUponQueryTimeout Specifies the behavior of the JDBC driver when Statement.setQueryTimeout() is run. DEFAULT Server-Side Properties Type 4 driver properties that effect server-side operations are summarized in the following tables.
Setting Properties • For connections made through a DataSource or a ConnectionPoolDataSource, set the property on the DataSource or the ConnectionPoolDataSource object. • For the DriverManager class, set properties in either of two ways: ◦ Using the option -Dproperty_name=property_value in the command line ◦ Using the java.util.
1. 2. 3. 4. Set on the DataSource object, DriverManager object, or ConnectionPoolDataSource object. Set through the java.util.Properties parameter in the getConnection method of DriverManager class. Set the property in a properties file specified by the t4sqlmx.properties property. Set the –Dt4sqlmx.property_name=property_value in the java command line For more information, see order of precedence for properties specified in various ways for use with the Driver Manager.
• “T4LogLevel” (page 49) • “T4QueryExecuteLogFile” (page 50) • “translationVerification” (page 50) • “url” (page 51) • “useArrayBinding” (page 51) • “useExternalTransaction” (page 52) • “user” (page 52) • autoCommit The properties are listed in alphabetic order (with the exception of LOB Table Name Properties) together with their descriptions. blobTableName See “LOB Table Name Properties” (page 42).
connectionTimeout The connectionTimeout property sets the number of seconds a connection can be idle before the connection is physically closed by MXCS. Set this property on a DataSource object, ConnectionPoolDataSource object, or DriverManager object. Data type: int Units: seconds Default: -1 (Use the ConnTimeout value set on the MXCS server data source.) Range: -1, 0 to 2147483647 • Zero (0) specifies infinity as the timeout value.
Data type: String Default: OFF Values : ON or OFF For example: executeBatchWithRowsAffected=ON initialPoolSize The initialPoolSize property sets the initial connection pool size when connection pooling is used with the Type 4 driver. Set this property on a DataSource object or DriverManager object. This property is ignored for connections made through the ConnectionPoolDataSource object.
KANJI The KANJI character-set mapping property corresponds to the SQL/MX KANJI character set, which is a double-byte character set widely used on Japanese mainframes. This property is a subset of Shift JIS -- the double character portion. The encoding for this property is bigendian. Set this property on a DataSource object, ConnectionPoolDataSource object, or DriverManager object. Data type: String Default: SJIS (which is shift-JIS, Japanese) For example, java –Dt4sqlmx.
Default: none For example: blobTableName=samdbcat.sales.lobvideo Set this property on a DataSource object, ConnectionPoolDataSource object, or DriverManager object. loginTimeout The loginTimeout property sets the time limit that a connection can be attempted before the connection disconnects. When a connection is attempted for a period longer than the set value, in seconds, the connection disconnects. Set this property on a DataSource object, ConnectionPoolDataSource object, or DriverManager object.
Units: number of physical connections Default: -1 (Disables connection pooling.) Range: -1, 0 through 2147483647, but greater than minPoolSize The value determines connection-pool use as follows: Any negative value is treated like -1. 0 means no maximum pool size. A value of -1 disables connection pooling. Any positive value less than minPoolSize is changed to the minPoolSize value.
Default: YES Available values: YES and NO. For example, mfuStatementCache = NO For information on enabling caching, see “maxStatements” (page 44). minPoolSize The minPoolSize property limits the number of physical connections that can be in the free connection pool. Set this property on a DataSource object, ConnectionPoolDataSource object, or DriverManager object. Data type: int Default: -1 (The minPoolSize value is ignored.
Data type: String Default: empty string For example: password=eye0weU$s properties The properties property specifies the location of the properties file that contains keyword-value pairs that specify property values for configuring the Type 4 driver. For more information, see “Creating and Using a Properties File” (page 37). queryExecuteTime This property is used to specify the value in time (milliseconds). Any query that takes more than the specified time will be logged in the log file.
roundingMode The roundingMode property specifies the rounding behavior of the Type 4 driver. For example, if the data is 1234.127 and column definition is numeric(6, 2) and the application does setDouble() and getDouble(), the value returned is 1234.12, which is truncated as specified by the default rounding mode, ROUND_HALF_UP. Set this property on a DataSource object, ConnectionPoolDataSource object, or DriverManager object.
The serverDataSource allows the application to set SQL/MX properties (such as resource governing) for server-side data sources. For more information about server-side data sources, see "Server Data Sources" in the HP NonStop Connectivity Service Manual for SQL/MX Release 3.2.1. • If not specified, the default server data source is used. • If the data source named in serverDataSource is not available on the MXCS server, the default server data source is used.
T4LogLevel The T4LogLevel property sets the logging levels that control logging output for the Type 4 driver. The Java package java.util.logging logs error messages and traces messages in the driver. Set this property on a DataSource object, ConnectionPoolDataSource object, or DriverManager object. Data type: String Default: OFF Logging Levels OFF is a special level that turns off logging; the default setting.
T4QueryExecuteLogFile Whenever the queryExecuteTime is specified, T4QueryExecuteLogFile property is used to set the name of the logging file for the queryExecuteTime property. Set this property on a DataSource object, ConnectionPoolDataSource object, or a DriverManager object. Data type: String The default log file name is t4sqlmxQueryExecuteTime.log and will be created in the user.home directory. The system can accept any valid file name.
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.
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. If the property is set and an external transaction does not exist or is not passed, you cannot perform database-related operations. As a result, exceptions are generated.
By default, this property is set true when a JDBC connection is established. If autoCommit property is set to false, the current transaction remains active until the application explicitly commits or rolls back the transaction. If the JDBC/MX Type 4 driver property t4sqlmx.autoCommit is specified, and JDBC API method java.sql.Connection.setAutoCommit() is called from the application, the values specified in the API method take higher precedence over the property t4sqlmx.autoCommit.
5 Working with BLOB and CLOB Data • “Architecture for LOB Support” (page 55) • “Setting Properties for the LOB Table” (page 55) • • • 54 ◦ “Specifying the LOB Table” (page 56) ◦ “Reserving Data Locators” (page 56) “Storing CLOB Data” (page 56) ◦ “Inserting CLOB Columns by Using the Clob Interface” (page 56) ◦ “Writing ASCII or MBCS Data to a CLOB Column” (page 57) ◦ “Inserting CLOB Data by Using the PreparedStatement Interface” (page 57) ◦ “Inserting a Clob Object by Using the setClob Me
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.
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. The JDBC applications that access BLOB or CLOB data must specify the associated LOB table names and, optionally, configure the reserveDataLocators property.
NOTE: • The EMPTY_CLOB() function is an HP NonStop specific function and might not work on other databases. • Do not use the EMPTY_CLOB() function when using the PreparedStatement interface. The Type 4 driver scans the SQL string for the EMPTY_CLOB() function and substitutes the next-available data locator. Then, obtain the handle to the empty CLOB column by selecting the CLOB column for update.
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. Inserting a Clob Object by Using the setClob Method Your JDBC application cannot directly instantiate a Clob object. To perform an equivalent operation: 1. Obtain a Clob object by using the getClob method of the ResultSet interface. 2.
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.read(myClobData, offset, length); Reading Unicode data from a CLOB Column To read Unicode or MBCS data from a CLOB column, use the CLOB interface or Reader. Using the CLOB interface: // Obtain the Clob from ResultSetClob myClob = rs.
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 66) • “Help Listing from the Type 4 Lob Admin Utility” (page 67) • “Creating LOB Tables” (page 67) 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.1 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 4 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. • 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 70) • “MFC Design” (page 70) • “Configuring MFC” (page 70) • “Enabling MFC” (page 70) • “MFC Usage Scenarios” (page 71) • “MFC Tuning Recommendations” (page 71) • “MFC Limitations” (page 72) 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.
NOTE: There is no need to change any data source configuration settings to disable MFC. 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.
MFC. If the most frequently used statements number is not known, it is recommended that you configure a number closer to 60% of total queries in the application. 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.
8 Type 4 Driver Compliance • “Compliance Overview” (page 73) • “Unsupported Features” (page 73) • “Deviations” (page 75) • “HP Extensions” (page 76) ◦ “Internationalization of Messages” (page 76) • “Conformance of DatabaseMetaData Methods' Handling of Null Parameters” (page 77) • “Type 4 Driver Conformance to SQL Data Types” (page 77) ◦ “JDBC Data Types” (page 77) • “Floating-Point Support” (page 79) • “JDBC Type 4 Driver Features” (page 79) • “Unsupported NonStop SQL Features” (page 79
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 81) • “The Type 4 Driver Logging Facility” (page 81) ◦ “Accessing the Type 4 Driver Logging Facility” (page 82) ◦ “Controlling Type 4 Driver Logging Output” (page 82) ◦ “Message Format” (page 82) ◦ “Examples of Logging Output” (page 83) ◦ “Controlling the QueryExecuteTime Logging Facility” (page 84) ◦ “QueryExecuteTime logging Message Format” (pa
• “Controlling the QueryExecuteTime Logging Facility” (page 84) • “QueryExecuteTime logging Message Format” (page 84) • “Examples of QueryExecuteTime Logging Output” (page 84) 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 85) • “Third-Party Databases” (page 85) • “Operating Systems” (page 85) • “Compatibility” (page 85) 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 86) • “Type 4 Driver Error Messages” (page 86) 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.1 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.
10. Enter y or n to specify whether the startup and error messages must be sent to EMS. 11. Enter a name for the SCF IN file for the ODBCMXS configuration. The default value is ODBSIN3. 12. Enter a name for the SCF configuration file for the ODBCMXS configuration. The default value is ODBSCF3.
a. b. c. d. In the Protocol menu, select ODBC/MX Client. In the Target (Connecting) Host field, enter the IP address or host name where your ODBCMXS process is listening on the HP NonStop server. In the Target (Connecting) Port field, enter the port number of the SSL server. For example, 8402. In the Local (Accepting) Port field, enter the port number of the MXCS Association Server. NOTE: The MXCS Association Server port number must not be in use by any other program or service on your client workstation.
A Sample Programs Accessing CLOB and BLOB Data This appendix shows two working programs. • “Sample Program Accessing CLOB Data” (page 111) • “Sample Program Accessing BLOB Data” (page 113) 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.
