Connect-Star for JDBC

Overview

Connect★ for JDBC supports Connect★ functionality to access Model 204. It includes all SQL and RCL statement syntax.

See Model 204 documentation for related Model 204 documentation.

Connect^★ for JDBC environment setup

Limitations and specifications

Limitations

A complete listing of the Connect★ for JDBC limitations is provided in the Connect Star for Model 204 program group under the JDBC > J204 API.

JDBC API specifications

JDBC API incorporates JDBC 2.5 with no extended functionality.

Platforms tested

The following platforms have been tested successfully for connectivity with Model 204 using Connect★ for JDBC:

• z/OS 1.5
• Red Hat Linux 8.0 (Kernel 2.4)
• Mandrake 9.2 & 10.0 (Kernel 2.6)
• Sun Solaris 2.8
• Windows NT, 2000, XP

These platforms are supported by Rocket Software.

Environment requirements

Before you install Connect★ for JDBC you must have the following installed in your working environment:

• Java2 Software Development Kit (J2SDK) Version 1.5 or greater.
• Model 204 Version 6.1.0 or greater with TCP/IP and SQL.

Removing a previous version of Connect^★ for JDBC

To remove a previous version of Connect★ for JDBC, do the following:

1. Go to the Control Panel > Add/Remove Programs.
2. Click on Connect Star for JDBC and remove.
3. Go to the installation folder for the previous version of J204 and delete the j204.jar file.

Installing Connect^★ for JDBC

1. Ensure that you have fulfilled the preinstallation requirements as described in Preparing to install.
2. Navigate to the FTP server site: ftp.rocketsoftware.com
3. Enter the userid and password provided by Rocket.
4. Navigate to the M204 > version > ConnectStar folder (where version is 7.5, for example).
5. Open the JDBC folder.
6. Choose the folder for the driver that you want: 32bit or 64bit.
7. Download the files to your PC, in binary format.
8. Run the .exe file to install the application.

Connection parameters

To connect a J204 client application to a Model 204 server, the application must specify connection parameters in the URL and/or java.util.Properties object supplied as parameters to DriverManager.getConnection().

See J204 API for DriverManager and J204Driver classes. If a parameter is specified in both the URL and the properties object, the value in the properties object takes precedence.

The URL includes both keyword and positional parameters. Positional parameters must be entered in the specified order, while keyword parameters may appear in any order. All keyword parameters are optional.

Note: If you specify parameters in the properties object, you must enter the key entirely in uppercase or lowercase. Otherwise, the property will not be found. The case of parameters in the URL can be upper, lower, or mixed; it does not matter.

The URL has the following format:

Syntax

JDBC:J204:// {hostname} : {port} {/connection_type} [/user/password] [/eoc=eoc_character] [/cursors=commit_option] [/dirty data=dirty_data_option] [/isolation level=isolation_level_option] [/trace=trace_option]

Where

• hostname specifies the server's TCP/IP name, entered in dotted decimal or string format.

  Required; in the URL

• port specifies the port entered for the SERVPORT parameter in the DEFINE LINK command for the Model 204 server.

  Required; in the URL

• connection_type is either SQL or RCL.

  Required; in either the URL or properties list
  Properties key: connection type

• user and password specify a valid Model 204 user name and password.

  Optional; no defaults
  Properties keys: user, password

• The eoc_character for the eoc parameter specifies the end-of-line character for RCL connection.

  Default character is a semicolon (;)
  Properties key: eoc

• The commit_option for the cursors parameter determines the effect of commit on cursors. Choose one of the following options:
  • DROP specifies to close the cursor and drop the statement.
  • PRESERVE specifies to preserve open cursors and prepared statements.

  Default is DROP
  Properties key: cursors

• The dirty_data_option for the dirty data parameter specifies the treatment of dirty data by a Fetch statement. Choose one of the following options:
• DEFAULT uses the server default specified by the SQLCNVER parameter in the CCAIN stream; this is the default.
• SKIP specifies to bypass a record containing dirty data and continue on to the next.
• ERROR specifies to return an error message whenever dirty data is encountered.

Default is DEFAULT
Properties key: dirty data

• The isolation_level_option for the isolation level parameter is one of the following:

Option	Specifies
RC, the default	Read Committed (cursor stability)
RU	Read Uncommitted (dirty read)
SR	Serializable

Properties key: isolation level

• The trace parameter controls logging. The trace_option is one of the following:

  Option	Specifies
  ALL	Write all trace messages to the log file
  NONE	Write no trace messages

  No log file is created if this parameter is omitted. For more information, see Data source default parameters.

Usage

• You must specify the host name and port in the URL. You can specify the other parameters in the URL or in the properties object. If you specify a parameter in both places, the value specified in the properties list takes precedence.
• Positional parameters must be entered in the specified order. Hostname, port, and connection type must be specified in the URL before other options.
• All keyword parameters are optional.
• When specifying parameters in a properties list, you must enter the key (property name) in either uppercase or lowercase characters, but not mixed case. If the key is in mixed case, the property cannot be found. When specifying parameters in the URL, you can enter uppercase, lowercase, or mixed case.

Verifying your JDBC connection

To verify your JDBC connection:

1. Navigate to Connect Star for Model 204 program group > JDBC > DatabaseConnectivity.
2. Complete the Connection Information screen and click the Test Connection button.

<IMG SRC="04 JDBC-2.gif">

Connect^★ for JDBC Compilation and Execution

Execution prerequisite

Before executing an SQL statement, check with your Connect★ administrator to make sure that either the demonstration database is installed and has been defined to the SQL catalog file, CCACAT, or you have other available tables defined in CCACAT. (See SQL catalog population.)

Online help

Connect★ for JDBC includes HTML help files that provide complete documentation of the J204 JDBC driver. For detailed documentation, browse the index.HTML file in the \doc directory where the driver is installed.

RCL and JDBC

Java CallableStatements provide a means of calling stored procedures. Model 204 SQL does not support stored procedures; therefore, Connect★ for JDBC does not implement the CallableStatement interface.

Calling any CallableStatement method will result in an Unsupported Method SQLException.

An alternative to CallableStatement is the RCLStatement class, which allows execution of SOUL statements and procedures. You can use the following methods for RCL processing:

close() createStatement() execute() executeQuery() getResultSet() getResultSetType() getTransactionIsolation() nativeSQL()

BLOB and CLOB support

Connect★ for JDBC supports Model 204 BLOB and CLOB data types in SQL update and retrieval statements.

Use the following ResultSet methods to retrieve large object column data after SELECT statement execution.

BLOB byte[] getBytes() Object getObject() InputStream getBinaryStream() CLOB byte[] getBytes() Object getObject() String getString() InputStream getAsciiStream() InputStream getBinaryStream() java.io.Reader getCharacterStream() InputStream getUnicodeStream()

In INSERT and UPDATE statements, use parameters for large object columns. First, create a prepared statement, then set the values of the parameters with the following PreparedStatement methods.

BLOB setBytes() setObject() setBinaryStream() CLOB setBytes() setObject() setAsciiStream() setBinaryStream() setCharacterStream() setUnicodeStream()

j204.jar file

You must include the j204.jar file in the classpath for both compilation and execution. See your Java documentation for a complete list of options.

Note that the j204.jar file can be placed anywhere as long as the classpath points to its directory.

Compilation example

javac -classpath c:\jdbc\yourClasses;c:\jdbc\j204.jar yourProgram.java

Execution example

java -cp .;c:\jdbc\yourClasses;c:\jdbc\j204.jar yourProgram

Debug tracing for JDBC

To create a trace log file, use the TRACE=ALL parameter in the connection string. See Connection parameters.

J204Driver log file location

For applications run under Windows 7, the driver places the log file in the user's home directory.

• Under Windows 7, the directory would be:
  C:\Users\user_name
• Under Windows XP, the directory would be:
  C\Documents and Settings\user_name

For other operating systems, the log file is placed in the same directory as the j204.jar file.

Connection pooling

Connect★ version 7.5 and higher for JDBC offers a "connection pooling" class to interface with other pooling infrastructures provided by pooling packages and web application servers. This new feature allows for a pool of connections that remain active and open during execution of an SQL and RCL (SOUL) statement.

Using connection pooling helps eliminate the overhead of creating initial connections from scratch or trying to manage each connection using the JDBC API.

Each Connection Pool can create a pool of active connections maintainable throughout the connection cycle of the pooling service.

Connect★ version 7.5 and higher for JDBC has been tested with the JNDI, Apache DBCP^TM, BoneCP, and C3PO pooling packages.

Connection pooling example

Below is an example of pooling connections using the Apache Commons DBCP^TM component.
There are other examples provided in the install directory of Connect★ for JDBC:

/** * Example: Pooling Connection program using Apache DBCP? (Database Connection * Pooling) Package */ package com.cca.j204.tests; import javax.sql.DataSource; import java.sql.Connection; import java.sql.Statement; import java.sql.ResultSet; import java.sql.SQLException; // Using the Apache DBCP BasicDataSource. // The example is using the following libraries: // commons-dbcp-1.x.jar // commons-pool-1.x.jar import org.apache.commons.dbcp.BasicDataSource; public class SimpleDBCPExample { public static void main(String[] args) { BasicDataSource basicDatasource = new BasicDataSource(); basicDatasource.setDriverClassName("com.cca.j204.J204Driver"); basicDatasource.setUsername("user"); basicDatasource.setPassword("password"); basicDatasource.setUrl("jdbc:j204://mainframe.domain.com:2403/rcl"); basicDatasource.setInitialSize(5); // Set up 5 connections at initialization DataSource dataSource = basicDatasource; String statement = "VIEW ALL;"; // SOUL Statement // Show statistics before the connections are established System.out.println("Statistics before the connections are established."); showStatistics(dataSource); // Now, we can use JDBC DataSource as we normally would. Connection connectionHandle = null; Statement statementHandle = null; ResultSet results = null; try { System.out.println("Creating connection."); connectionHandle = dataSource.getConnection();//Start all 5 connections at once. // Show statistics while the connections are established System.out.println("Statistics after initializing the connections."); showStatistics(dataSource); System.out.println("Creating statement."); statementHandle = connectionHandle.createStatement(); System.out.println("Executing statement."); results = statementHandle.executeQuery(statement); System.out.println("Show Results:"); int numcols = results.getMetaData().getColumnCount(); while(results.next()) { for(int i=1;i<=numcols;i++) { System.out.print("\t" + results.getString(i)); } System.out.println(""); } // Show statistics while the connections are established System.out.println("Statistics while the connections are established."); showStatistics(dataSource); results.close(); statementHandle.close(); connectionHandle.close(); // Show statistics after the connections have been closed System.out.println("Statistics after the connections have been closed."); showStatistics(dataSource); // Clean up and close all of the pooled connections basicDatasource.close(); } catch(SQLException ex1) { System.out.println("SQLException in main(): " + ex1.getMessage()); } } // Print out the statistics from the BasicDataSource class public static void showStatistics(DataSource ds) { BasicDataSource bds = (BasicDataSource) ds; System.out.println("Number of Active Connections: " + bds.getNumActive()); System.out.println("Number of Idle Connections: " + bds.getNumIdle()); } }

The sample code generates this output:

Statistics before the connections are established. Number of Active Connections: 0 Number of Idle Connections: 0 Creating connection. Statistics after initializing the connections. Number of Active Connections: 0 Number of Idle Connections: 5 Creating statement. Executing statement. Show Results: VERSION 7.4.0K RELEASE OF MODEL 204 CMSVERSN VERSION OF THE CMS INTERFACE SYSOPT X'AB' SYSTEM OPTIONS SYSOPT2 X'00' SYSTEM OPTIONS OPSYS X'D209E8' OPERATING SYSTEM SYSID RS26 SMF SYSTEM IDENTIFICATION JOBNM M204MWD JOB NAME STEPNM ONLINE STEP NAME JOBSTEP MPONLINE JOB STEP NAME JESID J0919076 JES JOB ID LOCATION D204 LOCATION LSERVPD 8176 LENGTH OF SCHEDULER PDL NUSERS 432 NUMBER OF USERS NSERVS 15 NUMBER OF SERVERS SERVNSA X'00000000' NON SWAPPABLE SERVER AREAS SERVNSSZ 0 NON SWAPPABLE SERVER AREA SIZE NJBUFF 36 NUMBER OF JOURNAL BUFFERS .... (continue output) Statistics while the connections are established. Number of Active Connections: 1 Number of Idle Connections: 4 Statistics after the connections have been closed. Number of Active Connections: 0 Number of Idle Connections: 5