Other Issues

Welcome to Developer's Desktop

Welcome to the first edition of the IIUG Developer's Desktop Newsletter. It is hopefully the beginning of a very long and useful newsletter. This monthly rag is aimed at application developers who work with IDS ), and in helping them do more with the product. To that end, you will see a lot more about the tools used to develop apps, including the new developments at IBM like Data Studio 1.1) and the Java Common Client. You will also see tips and tricks for writing code with 4GL, ESQL, PHP, RUBY, as well as the JCC, and JDBC. We also intend to have a regular column entitled "Ask the Developers," where questions you ask get tackled by the Product Development team. In order for this newsletter to be a success though, I need your help. I need to know what subjects that you, as application developers, would like to see discussed. I also would love to hear from you about tips and tricks you use to get the drivers, or SQL, to do what you want, and I would really love to see you submit articles. Finally in order for the "Ask the Developers" articles to work I need to hear from you about what you would like the Developers to answer. So as we begin this newsletter I would ask that you mail me questions, tips and tricks, articles, and comments to devnews@iiug.org

This month's edition we are focusing primarily on Java, with a tip of the hat to the Eclipse framework. This will likely be our only article on Eclipse proper, as we will be using IBM Data Studio ), for continued articles.

Thanks so much, and Enjoy the Newsletter.

Mark Jamison
Editor: IIUG Developer's Desktop

 

Table of Contents

I. Welcome to Developer's Desktop

II. From the President

III. Eclipse and IDS

IV. Using Password Encryption with JDBC

V. An Introduction to the JCC

VI, Differences between JDBC and JCC.

 

 

From the President

A few months back Mark Jamison approached me with this wonderful idea of asking the IBM Informix Development Staff for some great articles and create a monthly Developers Newsletter available to the IIUG Membership at large. Personally I loved the idea but to be honest I was not sure Mark could get this going as the developers are all quite busy, but thru Mark's persistence and with the special help of Cathy Elliot of IBM, this idea has come true. Mark has done an amazing job and with all that hard work from the authors, Cathy and Mark, the first issue is hereby launched in what we hope to be a monthly publication apart from the IIUG Insider.

 

The Developers Newsletter will be emailed out to all IIUG members on a monthly basis in the middle of each month and can be viewed online at www.iiug.org/devnews

Once again thank you to Mark Jamison, Cathy Elliot, Jerry Keesee and all the IBM Informix Developers worldwide who have contributed to this and the future issues. If you are not a developer and would also like to write in the Developers Newsletter, you can contact Mark at devnews@iiug.org and I am sure he would love to have some articles from users as well as developers. So please share your knowledge as what may seem trivial to you technically can always help others.

Stuart Litel

President

International Informix Users Group

www.iiug.org

 

Eclipse and IDS

By: S.Ramesh Gopal

(Editors Note, beginning in our second newletter, we will be discussing the use of Eclipse though our Data Studio articles)

Eclipse is a free open-source Java enviroment which can be got from http://eclipse.org. Eclipse framework is written primarily in Java. It is a Java IDE, consisting of the Java Development Tools and compiler. However, to use Eclipse one must have a Java Runtime Environment (JRE). Eclipse requires one to install the Java SDK first before installing Eclipse. The SDK can be either IBM SDK or Sun SDK.

This topic broadly covers:

 

Getting Started With Eclipse:

When you start Eclipse, a startup screen appears, and the program spends some time loading various modules.

 

When Eclipse has finished loading, you see a screen similar to the following:

One can learn more by clicking on the Icons.

On clicking the X button on the Welcome tab, we see a screen similar to the following:

 

Setting preferences

It is a generally a good idea to set preferences for the Java editor. Select Window->Preferences from the menu. Select Java->Code Style-> Formatter from the tree in the left panel. On clicking the Show button in the Formatter window on the right panel, we can provide our own options that make the code screen presentable.

Loading a new program

If you write a program from scratch, then you can start your work in Eclipse. It is always best to place each of your programs into a separate directory.

Select File->New->Project from the menu. You will get the following screen.

Select the Java Project option and click on the Next> button. We see the following screen:

 

In the following dialog, give a name to the project. In the Contents section, select the option Create new project in workspace (instead of using the option Create project from existing source). We also have the option of specifying the JDK version.

Click on Next> button. we see a screen similar to the following:

 

The screen shows a hierarchy of the source codes under our Project. If one wants to add any other projects, it can be done by selecting the Projects tab. The libraries tab lists the files that we will need for our source compilations.

NOTE : We can click Finish button in this screen or in the previous screen.

On clicking the Finish button, we get the following screen:

 


One can add a new package (for easily distinguishing the source folders) or add a new source code under the Project created as above.

 

If we want to create a Package under our Project, right click on our Project and select File->New->Package or from File->New->Project. In both the options, the following screen is displayed:

 

 


Click Finish button. We see the screen as below:

 

NOTE: Under our Project, we can see JRE System library which corresponds to the runtime which Eclipse uses. We can the default JRE, or provide our own libraries.

If we want to add external libraries, select Project->Properties->Java Build Path in the left window panel. On the right panel under Libraries tab, we can add any other libraries that our project uses.

To write Java code, select File->New->Class. The following screen is displayed.

NOTE : This class can be under a package or under a project or a standalone Java code.

 

Under the Name label, provide a suitable name to the Java Class.

 

Click on Finish. The following screen is displayed.

 

NOTE: Eclipse generates an outline body for the code based on the Java Class Name that we provided. One can modify this source based on the requirements.

Compiling a program

To compile a program in Eclipse, select the project in the pane on the left hand side. Then select Project->Build Automatically from the menu.

Compilation errors are displayed in a window at the bottom of the Eclipse frame.

Click on an error message, and the cursor moves to the offending line in the edit window:

 

Running a program

To run a program, select the Run->Run as...->Java Application menu option. The program runs. Any console output is directed to a window at the bottom of the screen.

Similarly, we can also load existing programs using Eclipse.

Debugging a program

Before debugging a program, you should set a breakpoint at the start of the main method.

Double-click on the gray bar to the left of the edit window, next to the first source line after the line public static void main(String[] args). A blue dot appears, indicating the breakpoint.

 

Then select the menu option Run->Debug as->Java Application. The debugger starts and switches the Eclipse display to the debugger perspective. It pauses at the breakpoint that you set.

 

Based on our requirements, we can choose Run->and our options (based on if we want to go into the function, or come out of it, or stop debugging).

 

In the bottom-most window, the Console tab shows the output, the Debug tab shows the flow of our code, the Variables tab allows us to see the values on the variables.

Using IDS and Eclipse

As has been already said, Eclipse needs a Java RunTime Environment. In addition to use Eclipse against IDS (Informix Dynamic Server), we need some more libraries, namely Ifxjdbc.jar and ifxjdbcx.jar which are the libraries that are mandatory for using Eclipse against IDS. These libraries provide for Informix implementation, without the use of which we will run into errors.

The following screen shows a list of Informix specific libraries.

 

NOTE : ifxjdbc.jar and ifxjdbcx.jar are the requisite ones. To add these libraries, click on Add External Jars, and then select the jars from the directory where available.

 

 

Sample Java Application

The sample application tests for the connectivity against IDS.

package Test;

 

import java.sql.Connection;

import java.sql.DriverManager;

 

public class Example {

public static void main(String args[]) {

String url="jdbc:informix-sqli://9.25.150.54:1526/"+"sysmaster"+":INFORMIXSERVER=atmol1;user=informix;password=Ifx4Ever;";

Connection con = null;

 

try {

String driver = "com.informix.jdbc.IfxDriver";

Class.forName(driver).newInstance();

System.out.println("Obtained Informix driver.");

}

catch( Exception e ) {

System.out.println("Failed to load Informix driver.");

return;

}

try {

System.out.println("Using url: "+url);

con = DriverManager.getConnection(url);

System.out.println("Connected");

}

catch( Exception e ) {

e.printStackTrace();

}

finally {

if( con != null ) {

try { con.close(); }

catch( Exception e ) { e.printStackTrace();

}

}

}

}

}

The Package and Class Name is the same that we had created, The two import statements are required for initiating Connection. The url provides the details of the database (sysmaster in this case), INFORMIXSERVER (which is the name of the Informix Dynamic Server), user and the password for establishing connection. The "com.informix.jdbc.IfxDriver" is Informix implementation that establishes connection to the database. The message is displayed based on whether connection is established or not.

 

Executing the sample application on eclipse

 

We can view the output on the Console tab at the bottom of the panel. This concludes our first intro to using the Eclipse environment with IDS.

Using Password Encryption with JDBC

By: S.Ramesh Gopal & Thamizhchelvan A Anbalagan

What is Encryption? Encryption is the transformation of data into an unreadable form using a key. This makes it impossible to convert the data back to intelligible form without access to the key. Most of the time when we try to connect to server from the client application we are exchanging the password over the network. This data gets stored somewhere. You might wonder if the password you provide will be kept well-protected (read encrypted). In case you are the person designing such backend registration component, why not give your users peace of mind by encrypting their passwords? In this article we first introduce how to setup the password encryption on server side, sqlhosts file entry and configuring concsm.cfg file later we will discuss about how password encryption achieved in a simple java/JDBC application.

You can use communication support modules (CSMs) to enable password encryption. The simple password CSM (SPWDCSM) provides password encryption. This encryption protects a password when it must be sent between the client and the database server for authentication. SPWDCSM is available on all platforms. You cannot use password encryption with encryption CSM (ENCCSM). For example, if you are using the SPWDCSM and decide to encrypt your network data, you must remove the entries for the SPWDCSM in your concsm.cfg and sqlhosts files.

concsm.cfg file:

The following two examples illustrate the two alternatives for parameters that you must enter in the concsm.cfg file to define the Simple Password Communication Support Module.

Concsm.cfg:

SPWDCSM("/usr/informix/lib/client/csm/libixspw.so, server=/usr/informix/lib/csm/libixspw.so", "", "")

SPWDCSM("/usr/informix/lib/csm/libixspw.so", "", "")

The following example shows the conn_options field set to 0, so no password is necessary:

SPWDCSM("/work/informix/csm/libixspw.so","","p=0")

You cannot use a simple password CSM over a multiplexed connection.

SQLHOSTS file:

When a client application sends a password to the database server for authentication the password is not encrypted unless you request password encryption through the simple password communications support module (SPWDCSM). You activate password encryption by specifying it on the configuration for the database server name, or an alias, in the sqlhosts file or registry. To activate password encryption, specify the following value in the Options field of the sqlhosts entry

SQLHOSTS entry at server side:

servername ontlitcp machinename servicename csm=(SPWDCSM)

Example:

Informix_on1110 ontlitcp idc1ul14 9001 csm=(SPWDCSM)

How to configure the JRE for JAVA/JDBC Password encryption connection?

JDBC internally uses JCE(Java Crypto Encryption) modules. If you write a JAVA client application the you need to make the following configurations to

Configuring IBM/SUN JCE

To use the encrypted user ID and password security mechanism, you need IBM JCE (Java Cryptography Extension) 1.2.1 or later. You can use it with any version of IBM or Sun's Java??? 2 Platform, Standard Edition, Version 1.2 (J2SE).

IBM Developer Kit for the Java Platform 1.4 or later comes with IBM JCE, so you do not need to install IBM JCE separately. If you have an earlier version of IBM Developer Kit for the Java Platform or other Software Development Kits, complete the following steps:

Copy the following IBM JCE jar files to the jre/lib/ext directory of the IBM SDK's installation home:

ibmjcefips.jar

ibmjceprovider.jar

ibmjcefw.jar

ibmpkderby.jar

ibmpkcs11.jar

Modify the java.security file in the jre/lib/security directory. In the section that lists providers (and preference order), replace the text with:

security.provider.1=com.ibm.crypto.provider.IBMJCE

security.provider.2=com.ibm.crypto.fips.provider.IBMJSSEFIPSProvider

security.provider.3=com.ibm.jsse.IBMJSSEProvider

To make use of IBMJCEFIPS provider:

You must specify the IBMJCEFIPS provider at a higher preference order than any non-FIPS security providers in the java.security file. The order is 1-based, meaning that 1 is the most preferred, followed by 2, and so on.

For example:

security.provider.1=com.ibm.crypto.fips.provider.IBMJCEFIPS

security.provider.2=com.ibm.crypto.fips.provider.IBMJCE

Make sure that the IBMJCEFIPS has a higher preference order than the IBMJCE provider.

security.provider.1=sun.security.provider.Sun

security.provider.2=com.ibm.crypto.provider.IBMJCE

 

Note: If you are installing the IBM JCE on a Sun Java Development Kit, you must specify both of these lines in the order shown.

To use the encrypted user id and password security mechanism during JDBC connection using the network client, specify the securityMechanism in the connection property.

Client or Simple JAVA application to connect the password encryption enabled server:

String URL = "jdbc:informixsqli://hostip:port:user=myname;password=mypassord;

INFORMIXSERVER=myserver;SECURITY=PASSWORD";

PASSWORD is case insensitive. You can type it in upper or lowercase letters.

import java.io.*;

import java.sql.*;

import javax.sql.*;

import java.util.*;

import com.informix.jdbc.*;

 

class ConTest {

 

public static void main(String[] args) {

 

String url=null;

Connection con;

System.out.println("\nGoing To Test Connectivity...\n");

 

// Load the driver

try

{

Class.forName("com.informix.jdbc.IfxDriver");

}

catch (Exception e)

{

System.out.println("Error: failed to load Informix JDBC driver.");

e.printStackTrace();

return;

} // End Driver Loading Block

System.out.println("Informix JDBC driver loaded successfully.");

url="jdbc:informix-sqli:idc1ul14.lenexa.ibm.com:9001/test:user=i

nformix;password=Pswd4QAT;informixserver=manojm_950ucx;SECURITY=PASSWORD";

// Connect to database

System.out.println("The url is:-"+url);

try

{

con=DriverManager.getConnection(url);

}

catch(SQLException e)

{

System.out.println("ERROR: Failed to connect!");

System.out.println("ERROR:" + e.getMessage());

e.printStackTrace();

return;

} // End of Connect Database block

System.out.println("\nConnection established using password encryption\n");

}

}

 

If the SECURITY=PASSWORD setting is specified in the IBM Informix JDBC client, the SPWDCSM csm option must be enabled on the Informix database server. Otherwise, an error is returned during connection.

To use the SPWDCSM csm server option, which supports password encryption on the database server, you must configure the server's sqlhosts servername option. After this option is set on the server, only clients using the SECURITY=PASSWORD setting can connect to that server name.

JDBC password encryption mechanism allows exchanging the encrypted password over the network, in this way storing the password as such is avoided and we are providing the password security. Started about what is encryption, how to configure password encryption at server side, configuring various attribute in client and server side then we had seen simple java/jdbc connection application enabling JDBC password encryption.

 

An Introduction to the JCC

By: Dhanashri C Kudgavkar & Prasanna Mathada

Many new features and enhancements have been made for newer versions of IBM Informix Dynamic Server. One of these changes is the new JDBC driver architecture referred to as IBM DB2 Universal Driver for JDBC and SQLJ which is independent of driver-type connectivity or target platform. This new driver is based on the Distributed Relational Database Architecture protocol and with this driver, users can write client applications that can use both IDS and DB2 data servers. The key goal for the common client is to replace legacy clients with the common client and to minimize the changes required to migrate from the legacy client to the common client. Understanding the differences between the Informix legacy driver and IBM Data Server Driver for JDBC and SQLJ is recommended before migrating current applications using the legacy client to IBM Data Server Driver for JDBC and SQLJ. For more information on these differences refer the Resources section of this article.

The new Universal Driver is com.ibm.db2.jcc.DB2Driver and is contained in db2jcc.jar File db2jcc.jar contains the JDBC driver. IBM Informix Dynamic Server does not support SQLJ at this time. This is planned in future releases of IDS. This Universal driver i.e. the db2jcc.jar file is not branded by any particular IBM data server product. The same common db2jcc.jar build may be bundled with multiple database products.

Why JCC

*         JCC provides java connectivity to IDS , all DB2 server, Cloudscape/Derby.

*         Enables easier addition of backends and removed need for legacy drivers

*         Can connect distributed and local

*         A common driver reduces behavioral differences when switching between the various connectivity types though IDS supports only type 4 connections.

*         Provides JDBC or SQLJ access to data

*         Provides both, type 4 and type 2 connectivity . IBM Informix Dynamic Server, only supports type 4 connections which use pure Java and allows the client applications to connect directly to the database servers

*         Very compact and does not require install

*         High performance

*         Business value of JCC is very impressive

Components of JCC

JCC Internal Universal Architecture

Current restrictions with IDS and IBM Data Server Driver for JDBC and SQLJ

*         Connections must be DRDA( Distributed Relational Database Architecture ); the Informix proprietary protocol is not supported

*         Can be used only with IBM IDS version 11.10

*         There is no SQLJ support in the above mentioned release

*         Type 2 connections are not supported; only type 4 connections are supported

*         Certain Informix data types are not supported: INTERVAL, opaque data types, uder-defined data types and collection data types

Application programming using IBM Data Server Driver for JDBC and SQLJ

*         Connecting to a data source: There are two ways of connecting to the data source, using DriverManager interface or using DataSource interface. While connecting to the data source using the JDBC DriverManager interface which is part of the java.sql package, first step is to load the JDBC driver by invoking the Class.forName method as follows:

 

      try {
  // Load the IBM Data Server Driver for JDBC and SQLJ with DriverManager
  Class.forName("com.ibm.db2.jcc.DB2Driver");
} catch (ClassNotFoundException e) {
     e.printStackTrace();
}

 

Second step is to connect to a data source by invoking the

DriverManager.getConnection method as follows :

getConnection(String url);
getConnection(String url, user, password);
getConnection(String url, java.util.Properties info); 

 

For IBM Data Server Driver for JDBC and SQLJ type 4 connectivity, the

getConnection methos must specify a user ID and password, through parameters or

through property vaues.

The url argument represents a data source, and indicates what type of

JDBC connectivity is being used.

The info argument is an object of type java.util.Properties that contains a

set of driver properties for the connection.

 

To make the application portable among data source, use of DataSource

interface is recommended in which case DataSource.getConnection

method is used to obtain the connection. For more details on this, refer

the Resources section of this article.

 

*         Java packages for JDBC support : Before invoking JDBC methods, user should be able to access all or parts of various Java packages that contain those methods.

This can be done either by importing the packages or specific classes, or by using the fully-qualified class names. Following packages or classes may be required for a JDBC program:

java.sql : Contains the core JDBC API.

javax.naming : Contains classes and interfaces for Java Naming and Directory Interface (JNDI), which is often used for implementing a DataSource.

javax.sql : Contains methods for producing server-side applications using Java

javax.transaction : Contains JDBC support for distributed transactions for the DB2 JDBC Type 2 Driver for Linux., UNIX. and Windows. (DB2 JDBC Type 2 Driver).

com.ibm.db2.jcc : Contains the implementation of JDBC for the IBM. Data Server Driver for JDBC and SQLJ.

COM.ibm.db2.jdbc : Contains the implementation of the JDBC for the DB2 JDBC Type 2 Driver.

*         Handling database objects : Statement.executeUpdate method is used to do the tasks like executing data definition statements such as CREATE, ALTER, DROP, GRANT, REVOKE , to execute INSERT, UPDATE, DELETE and MERGE statements that do not contain parameter markers and with IBM Data Server Driver for JDBC and SQLJ, to execute the CALL statement to call stored procedures that have no parameters and that return no result sets. If the updates to the tables involve passing values in variables to IDS table, PreparedStatement.executeUpdate method should be used. With the IBM Data Server Driver for JDBC and SQLJ, PreparedStatement.executeUpdate method can be used to call stored procedures that have input parameters and no output parameters and that return no result sets.

 

*         Data retrieval : To retrieve data from a table using a SELECT statement with no parameter markers, Statement.executeQuery method is used. This returns a result table in a ResultSet object which can be accessed using ResultSer methods. With the IBM Data Server Driver for JDBC and SQLJ, Statement.executeQuery method can be used to retrieve a result set from a stored procedure call, if that stored procedure returns only one result set. Statement.execute method should be used if the procedure returns multiple result sets.

PreparedStatement.executeQuery method should be used for a SELECT statement with parameter markers. With the IBM Data Server Driver for JDBC and SQLJ, PreparedStatement.executeQuery method can be used to retrieve a result set from a stored procedure call, if that stored procedure returns only one result set and has only input parameters. To retrieve result set from a stored procedure which returns multiple result sets, Statement.execute method should be used.

The IBM Data Server Driver for JDBC and SQLJ provides support for scrollable, updatable, and holdable cursors. IBM Informix Dynamic Server data sources, cursors, can not be updatable. It also supports multi-row INSERT, UPDATE, and FETCH for connections to data sources that support these operations. When user executes multiple INSERT statements in a batch, and the data source supports multi-row INSERT, the IBM Data Server Driver for JDBC and SQLJ uses multi-row INSERT to insert the rows. When you use a scrollable cursor to perform a FETCH in a JDBC application, and the useRowsetCursor property value is set to true or not set, the IBM Data Server Driver for JDBC and SQLJ uses multi-row FETCH to retrieve the rows, if the data source supports multi-row FETCH. The IBM Data Server Driver for JDBC and SQLJ supports a technique for performing positioned update or delete operations that follows the JDBC 1 standard. That technique involves using the ResultSet.getCursorName method to obtain the name of the cursor for the ResultSet, and defining a positioned UPDATE or positioned DELETE statement of the following form:

   UPDATE table SET col1=value1,.coln=valueN WHERE CURRENT OF ursorname
    DELETE FROM table WHERE CURRENT OF cursorname

*         IBM Data Server Driver for JDBC and SQLJ isolation levels : The IBM Data Server Driver for JDBC and SQLJ supports a number of isolation levels which correspond to database server isolation levels. Connection.setTransactionIsolation method is used to set isolation level for a unit of work within a JDBC program and the default isolation level can be set with the dafaultIsolationLevel property.

The following table shows the values of level that user can specify in the Connection.setTransactionIsolation method and their database server equivalents.

Equivalent JDBC and IDS isolation levels

JDBC value

IBM Informix. Dynamic Server (IDS) isolation level

java.sql.Connection.TRANSACTION_SERIALIZABLE

Repeatable read

java.sql.Connection.TRANSACTION_REPEATABLE_READ

Repeatable read

java.sql.Connection.TRANSACTION_READ_COMMITTED

Committed read

java.sql.Connection.TRANSACTION_READ_UNCOMMITTED

Dirty read

com.ibm.db2.jcc.DB2Connection.TRANSACTION_IDS_CURSOR_STABILITY

IDS cursor stability

com.ibm.db2.jcc.DB2Connection.TRANSACTION_IDS_LAST_COMMITTED

 

*         Exceptions and Warnings under the IBM Data Server Driver for JDBC and SQLJ : The IBM Data Server Driver for JDBC and SLQJ provides SQLException class and DB2Diagnosable interface for handling errors. An SQLException object contains an int value which is the error code and can be retrieved using SQLException.getErrorCode , a String object which is the SQLSTATE or null and can be retrieved using SQLException.getSQLState , a String object that contains the description of the error which can be retrieved using SLQExecption.getMessage and a pointer to the next SQLException or null which can be retrieved using SQLException.getNextException.

The IBM Data Server Driver for JDBC and SQLJ-only interface com.ibm.db2.jcc.DB2Diagnosable extends the SQLException class. The DB2Diagnosable interface gives user more information about errors that occur when the data source is accessed. If the JDBC driver detects an error, DB2Diagnosable gives you the same information as the standard SQLException class. However, if the database server detects the error, DB2Diagnosable adds the following methods, which give user additional information about the error:

getSqlca returns a DB2Sqlca object with the following information

*         An SQL error code

*         The SQLERRMC values

*         The SQLERRP value

*         The SQLERRD values

*         The SQLWARN values

*         The SQLSTATE

getThrowable returns a java.lang.Throwable object that caused the SQLException, or null, if no such object exists.

printTrace prints diagnostic information.

Security under the IBM Data Server Driver for JDBC and SQLJ

securityMechanism property is used to specify the required security method when using the IBM Data Server Driver for JDBC and SQLJ. This property can be set in one of the following two ways :

*         If DriverManager interface is being used, then set securityMechanism in a java.util.Properties before invoking the form of the getConnection method that includes the java.util.Properties parameter

*         If DataSource interface is being used and user is creating a DataSource object, then invoke the DataSource.setSecurityMechanism method after creating a DataSource object

 

The security mechanism that is in effect for a connection can be determined by calling DB2Connection.getDB2SecurityMechanism method.

The following table lists the security mechanisms that the IBM Data Server Driver for JDBC and SQLJ supports and the data sources that support those security mechanisms.

Security mechanism

Supported by

 

DB2. Database for Linux., UNIX., and Windows.

DB2 for z/OS.

IBM Informix. Dynamic Server

User ID and password

Yes

Yes

Yes

User ID only

Yes

Yes

Yes

User ID and encrypted password

Yes

Yes

Yes

Encrypted user ID

Yes

Yes

No

Encrypted user ID and encrypted password

Yes

Yes

Yes

Encrypted user ID and encrypted security-sensitive data

No

Yes

No

Encrypted user ID, encrypted password, and encrypted security-sensitive data

Yes

Yes

No

Kerberos1

Yes

Yes

No

Plugin1

Yes

No

No

Note:

  1. Available for IBM Data Server Driver for JDBC and SQLJ type 4 connectivity only.

 

Following are some of the examples explaining how to set the security mechanism with DriverManager interface :

(1) Setting the security mechanism in the DriverManager get.Connection invocation :

import java.sql.*;         // JDBC base
.
String id = "dbadm";       // Set user ID
String pw = "dbadm";       // Set password
String url = "jdbc:ids://mvs1.sj.ibm.com:5021/san_jose";
                           // Set URL for the data source
Connection con = DriverManager.getConnection(url, id, pw); 
                           // Create connection

 

(2) Setting the userid and password directly in the URL :

import java.sql.*;         // JDBC base
.
String url = 
  "jdbc:ids://mvs1.sj.ibm.com:5021/san_jose:user=dbadm;password=dbadm;";
                           // Set URL for the data source
Connection con = DriverManager.getConnection(url); 
                           // Create connection

 

Following are some of the examples explaining how to set the security mechanism with DataSource interface:

(1) Specifying the userid and password directly in the DataSource.getConnection invocation:

import java.sql.*;                        // JDBC base
import com.ibm.db2.jcc.*;                 // IBM Data Server Driver for JDBC
                                          // and SQLJ implementation of JDBC
.
Context ctx=new InitialContext();         // Create context for JNDI
DataSource ds=(DataSource)ctx.lookup("jdbc/sampledb"); 
                                          // Get DataSource object
String id = "dbadm";                      // Set user ID
String pw = "dbadm";                      // Set password
Connection con = ds.getConnection(id, pw); 
                                          // Create connection

 

(2) Using the DataSource.setSecurityMechanism methos :

.
com.ibm.db2.jcc.DB2SimpleDataSource ds =       // Create DB2SimpleDataSource object
  new com.ibm.db2.jcc.DB2SimpleDataSource();
ds.setDriverType(4);                           // Set driver type
ds.setDatabaseName("san_jose");                // Set location
ds.setServerName("mvs1.sj.ibm.com");           // Set server name
ds.setPortNumber(5021);                        // Set port number
ds.setUser("dbadm");                           // Set user ID
ds.setPassword("dbadm");                       // Set password
ds.setSecurityMechanism(
  com.ibm.db2.jcc.DB2BaseDataSource.CLEAR_TEXT_PASSWORD_SECURITY); 
                                               // Set security mechanism to

 

                                               // user ID and password

 

CLEAR_TEXT_PASSWORD_SECURITY is the default security mechanism. If the database server does not support it then , the driver updates the security mechanism to ENCRYPTED_USER_AND_PASSWORD_SECURITY and tries to connect to the server.

For more examples on various security mechanisms and ways to set those, refer the Resources section of this article.

 

Problem diagnosis with the IBM Data Server Driver for JDBC and SQLJ

There are several different ways to diagnose JDBC problem with the IBM Data Server Driver for JDBC and SQLJ, some specific to DriverManager interface and some for DataSource interface.

e.g. For type 4 connectivity recommended method is to start the trace by setting the db2.jcc.override.traceFile property or the db2.jcc.override.traceDirectory property in the IBM Data Server Driver for JDBC and SQLJ configuration properties file.

If DataSource interface is being used to connect to data source, DB2BaseDataSource.setTraceLevel method should be invoked to set the tracing type( default is TRACE_ALL). DB2BaseDataSource.setJccLogWriter method should be invoked to specify the trace destination and trace should be turned on.

If DriverManager interface is being used, the traceFile and traceLevel properties can be specified as part of the URL when the driver is loaded as follows:

 

String url = "jdbc:ids://sysmvs1.stl.ibm.com:5021/san_jose" +
 ":traceFile=/u/db2p/jcctrace;" +
 "traceLevel=" + com.ibm.db2.jcc.DB2BaseDataSource.TRACE_DRDA_FLOWS + ";";

 

Following example explains how to enable JDBC trace using configuration properties which helps tracing JDBC applications without modifying the applications:

Test.java does no tracing by itself and uses type 4 connection. If the program is modified to enable tracing in future, the settings within the program will take precedence over the settings in the configuration properties. Trace output is expected to have the following characteristics:

*         Trace information for each connection on the same DataSource is written to a separate trace file. Output goes into a directory named /Trace

*         Each trace file name begins with jccTrace1

*         If trace files with the same names already exists, the trace data is appended to them

 

The configuration property settings look like this :

*         db2jcc.traceDirectory=/Trace

*         db2jcc.traceFile=jccTrace1

*         db2jcc.traceFileAppend=true

 

 

If these settings should be applied only to the stand-alone program Test1.java, then a file with these settings should be created to refer when the Java program is invoked by specifying the .Ddb2.jcc.propertiesFile option. Suppose that the file containing the settings is /Test/jcc.properties , to enable tracing when Test1.java is run, command issued will be like this :

java -Ddb2.jcc.propertiesFile=/Test/jcc.properties Test1

 

For further details on tracing and also to understand how to enable tracing within a program, refer the Resources section of this article.

 

IBM Data Server Driver for JDBC and SQLJ properties for IBM Informix Dynamic Server

Properties define how the connection to a particular data source should be made. Most properties can be set for a DataSource object or for a Connection object. Properties can be set in one of the following ways :

*         Using setXXX methods where XXX is the unqualified property name with the first character capitalized

*         In a java.util.Properties value in the info parameter of a DriverManager.getConnection call

*         In a java.lang.String value in the url parameter of a DriverManager.getConnection call

 

Most of the IBM Data Server Driver for JDBC and SQLJ properties apply to all database products that the driver supports, e.g. databaseName, serverName, defaultIsolationLevel, deferPrepares, driverType, loginTimeout, logWrite, password, portNumber, resultSetHoldability, securityMechanism, traceDirectory, traceFile, traceLevel, xaNetworkOptimization, etc. Some of the IBM Data Server Driver for JDBC and SQLJ properties apply only to IBM Informix Dynamic Server databases which correspond to IDS environment variables. IDS specific properties must be specified in uppercase. GetXXX and setXXX methods for IDS-specific properties are formed by pre-pending the uppercase property name with get or set. For example:

boolean dbDate = DB2BaseDateSource.getDBDATE();

Some of the IDS specific properties are DBDATE, DBPATH, DBSPACETEMP, DBTEMP, DBUPSPACE, DELIMIDENT, IFX_DIRECTIVES, IFX_UPDDESC, INFORMIXOPCACHE, INFORMIXSTACKSIZE, PDQPRIORITY, STMT_CACHE, etc.

For the complete list of IBM Informix Dynamic Server specific properties and their detailed description refer the Resources section of this article.

 

IBM Data Server Driver for JDBC and SQLJ extensions to JDBC

The IBM Data Server Driver for JDBC and SQLJ provides a set of extensions to the support that is provided by the JDBC specification.

 

To use IBM Data Server Driver for JDBC and SQLJ-only methods in classes that have corresponding, standard classes, cast an instance of the related, standard JDBC class to an instance of the IBM Data Server Driver for JDBC and SQLJ-only class.

For example:

javax.sql.DataSource ds =  new com.ibm.db2.jcc.DB2SimpleDataSource();
((com.ibm.db2.jcc.DB2BaseDataSource) ds).setServerName("sysmvs1.stl.ibm.com");

Summary of IBM Data Server Driver for JDBC and SQLJ-only interfaces provided by the IBM Data Server Driver for JDBC and SQLJfor IBM Informix Dynamic Server data source

 

Interface name

Purpose

DB2Connection

Extends the java.sql.Connection interface.

DB2DatabaseMetaData

Extends the java.sql.DatabaseMetaData interface.

DB2Diagnosable

Provides a mechanism for getting DB2 diagnostics from a DB2 SQLException.

DB2PreparedStatement

Extends the com.ibm.db2.jcc.DB2Statement and java.sql.PreparedStatement interfaces.

DB2Statement

Extends the java.sql.Statement interface.

DB2SystemMonitor

Used for collecting system monitoring data for a connection.

 

Summary of IBM Data Server Driver for JDBC and SQLJ-only classes provided by the IBM Data Server Driver for JDBC and SQLJ for IBM Informix Dynamic Server data source

Class name

Purpose

DB2BaseDataSource

The abstract data source parent class for all IBM Data Server Driver for JDBC and SQLJ-specific implementations of javax.sql.DataSource, javax.sql.ConnectionPoolDataSource, and javax.sql.XADataSource.

DB2ConnectionPoolDataSource

A factory for PooledConnection objects.

DB2ExceptionFormatter

Contains methods for printing diagnostic information to a stream.

DB2PooledConnection

Provides methods that an application server can use to switch users on a preexisting trusted connection.

DB2SimpleDataSource

Extends the DataBaseDataSource class. Does not support connection pooling or distributed transactions.

DB2Sqlca

An encapsulation of the DB2 SQLCA.

DB2TraceManager

Controls the global log writer.

DB2XADataSource

A factory for XADataSource objects. An object that implements this interface is registered with a naming service that is based on the Java Naming and Directory Interface (JNDI).

 

Authors: Dhanashree Kudgavkar , Prasanna Mathada

Differences between JDBC and JCC

By: Dhanashri C Kudgavkar & Prasanna Mathada

 

The JDBC API defines the Java interfaces and classes that programmers use to connect to databases and send queries. A JDBC driver implements these interfaces and classes for a particular database management system vendor. A java program that uses the JDBC APIs loads the specified driver specific to the DBMS to be used by the application before connecting to a database.

IBM Informix JDBC Driver is a native-protocol, pure-Java driver. It is a Type 4 driver which works without a middle layer i.e. the client applications can directly connect to the database. Since, it is a vendor specific driver, client application can connect only to IBM Informix Dynamic Server using this driver. IBM Data Server Driver for JDBC and SQLJ , the driver based on the Distributed Relational Database Architecture protocol, is also integrated with DB2. This driver provides connectivity to all DB2 servers, IBM Informix Dynamic Server and Cloudscape/Derby. It supports both Type 2 and Type 4 connections, though IBM Informix Dynamic Server supports only Type 4 connections.

This article makes an attempt to explain the major differences between these two drivers. For more details on these differences and for examples, refer the document links provided in the Resources section of this article.

Topics covered in this article with respect to the differences between JDBC and IBM Data Server Driver for JDBC and SQLJ, include

*         Establishing the Connection

*         Security mechanisms and Encryption

*         Environment Variables and their mapping

*         Data types and Data conversion differences

*         SQL Commands

*         Cursors

*         Prepared Statements

*         Parameter order

*         Large Object behavior

*         Extended data types and Extended methods

*         Error codes

*         High availability solutions

 

*         Establishing the Connection

Initial class

When the initial class is specified in the application, user needs to make the following changes:

 

JDBC

JCC

Com.informix.jdbc.IfxDriver

com.ibm.db2.jcc.DB2Driver

Com.informix.jdbcx.IfxDataSource

com.ibm.db2.jcc.DB2SimpleDataSource

Com.informix.jdbcx.IfxXADataSource

com.ibm.db2.jcc.DB2XADataSource

 

URL protocol

Where the connection URL protocol name is specified in the application, user needs to make the following change:

 

jdbc:informix-sqli:

jdbc:ids:

 

Properties of the connection

Both Informix JDBC and the IBM Data Server Driver for JDBC and SQLJ allow users to build a list of properties that include environment variables by using the

java.util.Properties class. Alternatively, user can specify property=value; strings in the URL. However, the IBM Data Server Driver for JDBC and SQLJ ignores the Informix data server-specific property:

INFORMIXSERVER=instance_name;

Getting and setting connection properties

The DataSource property setters or getters specified in the application have a different syntax for the IBM Data Server Driver for JDBC and SQLJ. Properties are formed by prepending the uppercase property name with get or set. For example, to migrate from Informix JDBC to the IBM Data Server Driver for JDBC and SQLJ, user would need to make the following changes:

 

setIfxPDQPRIORITY(String value)

setPDQPRIORITY (String value)

setIfxDBANSIWARN(Boolean value)

setDBANSIWARN (Boolean value)

 

*         Security mechanisms and Encryption

 

This section describes differences in security mechanisms between Informix JDBC Driver and IBM Data Server Driver for JDBC and SQLJ.

Security mechanism differences

 

Functionality

Informix JDBC Driver

IBM Data Server Driver for JDBC and SQLJ

Userid and password encryption

Password encryption is supported, using environment variable

 

SECURITY=PASSWORD.

Userid encryption not supported.

Multiple types of encryption implementations are supported:

*         DB2BaseDataSource.USER_ONLY_SECURITY

*         DB2BaseDataSource.ENCRYPTED_USER
_AND_PASSWORD_SECURITY

Data encryption

Uses the communication support
module (CSM), environment variable:
csm=(classname=com.informix.jdbc.Crypto, config=test.cfg)

Not supported.

Authentication

Pluggable authentication modules; applications use com.informix.jdbc.IfmxPAM for Challenge-Response.

Not supported.

 

Encryption differences

With the Informix JDBC driver, password encryption is supported using environment variable SECURITY=PASSWORD. Userid encryption is not supported. The IBM Data Server Driver for JDBC and SQLJ allows the following types of encryption:

*         DB2BaseDataSource.USER_ONLY_SECURITY

*         DB2BaseDataSource.ENCRYPTED_USER _AND_PASSWORD_SECURITY

 

*         Environment Variables and their mapping

The following IDS environment variables that were supported for the Informix JDBC driver are not supported for the IBM Data Server Driver for JDBC and SQLJ.

  • ALLOWREGISTEROUTFORINPARAM
  • BIG_FET_BUF_SIZE
  • CSM
  • ENABLE_HDRSWITCH
  • IFX_BATCHUPDATE_PER_SPEC
  • IFX_CODESETLOB
  • IFX_SET_FLOAT_AS_SMFLOAT
  • IFX_TRIMTRAILINGSPACES
  • JDBCTEMP
  • LDAP_IFXBASE
  • LDAP_PASSWD
  • LDAP_URL
  • LDAP_USER
  • LOBCACHE
  • NEWCODESET
  • NEWLOCALE
  • IFXHOST_SECONDARY
  • IFXHOST INFORMIXCONRETRY
  • IFXPORTNO_SECONDARY
  • INFORMIXCONTIME
  • INFORMIXOPCACHE
  • INFORMIXSERVER_SECONDARY
  • INFORMIXSTACKSIZE
  • NEWNLSMAP
  • PROXY
  • SECURITY
  • SQLH_FILE
  • SQLH_LOC
  • SQLH_TYPE
  • SQLIDEBUG

The following table shows the variables for IBM Data Server Driver for JDBC and SQLJ that replace those from Informix JDBC driver that are not supported.

 

Environment variables used by the Informix JDBC driver

Equivalent environment variables for IBM Data Server Driver for JDBC and SQLJ

TRACEFILE, PROTOCOLTRACEFILE

traceFile

TRACE, PROTOCOLTRACE

traceLevel

FET_BUF_SIZE

queryBlockSize

IFX_AUTOFREE

queryCloseImplicit

INFORMIXCONTIME

loginTimeout, blockingReadConnectionTimeout

IFX_LOCK_MODE_WAIT

currentLockTimeout

 

*         Data Types and Data Conversion Differences

 

This section describes differences in data type and data conversion between Informix JDBC Driver and IBM Data Server Driver for JDBC and SQLJ.

Data type differences

 

Data Type

Informix JDBC Driver

IBM Data Server Driver for JDBC and SQLJ

Informix JDBC extensions classes for smart large objects (BLOB and CLOB) and simple large objects (BYTE and TEXT) and Statement type

Extension classes as supported by the Informix JDBC driver:

  • IfxBblob
  • IfxCblob
  • IfxSmartBlob
  • IfxStatementTypes
  • IfxTypes
  • IfxLoStat IfxLobDescriptor

Not Supported.

Floating point conversion to integer/byte

JDBC returns incorrect value.

Throws an SQLException if conversion is not possible.

INTERVAL data type

Supported.

Not supported.

IDS collection : LIST, SET, MULTISET, and ROW

Supported.

Not supported.

Smart large object functions like filetoblob, filetoclob, lotofile and locopy support

Supported.

Not supported.

getString() on BYTE column semantics

JDBC converts BYTE to STRING

Not supported.

Semantics of BLOB updates on client side being automatically reflected on the server

Changes to BLOB data on client side can be reflected on the server.

Does not automatically update BLOB unless the user issues an update.

BYTE, BLOB, TEXT, and CLOB need casting to resolve ambiguity when passed as parameters to functions or procedures

JDBC identifies BYTE as BINARY and smart large objects as BLOB.

Not supported.

IDS BOOLEAN is identified as SMALLINT

JDBC driver identifies BOOLEAN as BOOLEAN.

Does not differentiate BOOLEAN from SMALLINT.

 

Data conversion differences

 

Functionality

Informix JDBC Driver

IBM Data Server Driver for JDBC and SQLJ

DataConversion from DECIMAL to SHORT/BYTE with out-of-range results

  • getByte conversion: (-1)
  • getShort conversion: (-1)
  • getInt conversion: (-323855360)
  • getByte conversion: (0)
  • getShort conversion: (23552)
  • getInt conversion: (-323855360)

DECIMAL PRECISION with PreparedStatement.setDouble()

A packed decimal can be up to 32.

A packed decimal can be up to 31.

REAL to DECIMAL conversion

Returns a REAL to DECIMAL conversion. For example: 12345.67871 is returned as 12345.67871.

Limits the scale to give consistent results for DECIMAL irrespective of the precision and scale. For example: 12345.67871 is returned as 12345.67800.

DECIMAL to BYTE/SHORT conversion

Not supported.

Throws an SQLException.

 

*         SQL commands

 

The following SQL commands are not supported by IBM Data Server Driver for JDBC and SQLJ, when changing frequently used connection attributes:

 

o        CREATE DATABASE

Although the pass-through SQL that changes the transaction state is allowed with the IBM Data Server Driver for JDBC and SQLJ (e.g. COMMIT, COMMIT WORK, ROLLBACK, ROLLBACK WORK), it is preferable to use Connection.commit() and Connection.rollback() methods for better performance. Also, executing a pass-through SQL command that is comprised of multiple SQL commands separated by semicolons(;) is allowed, but it is not recommended.

 

*         Cursors

 

This section describes differences in cursor functionality between Informix JDBC Driver and IBM Data Server Driver for JDBC and SQLJ.

Cursor differences

 

Functionality

Informix JDBC Driver

IBM Data Server Driver for JDBC and SQLJ

Forward-only ResultSet

The Forward-only ResultSet stays open after the last row is accessed; it remains open until an explicit call to ResultSet.close();

The Forward-only ResultSet automatically closes after the last row is accessed. Throws an error if an application attempts to retrieve ResultSetMetaData after reading all rows. *

ResultSet.wasNull(): ResultSet has no data

  • wasNull() returns FALSE if query returns no data (ResultSet not empty but does not contain data).
  • wasNull() returns FALSE if resultset is moved beyond ResetSet.last().

Throws an SQLException (client side) with client-specific error numbers.

Product name and version strings have semantic changes

dmd.getDatabaseProduct
Version()="11.10.UC1"

dmd.getDatabaseProductVersion()="IFX11100"

dmd.getDatabaseProductName()="Informix Dynamic Server"

dmd.getDatabaseProductName()="IDS/UNIX32"

ResultSet.relative(): For illegal operations

Throws SQLException -79739 error "No current row".

Throws SQLException -4476 error "Cursor is not on a valid row."

ResultSet.next(): IFX does not have any data

Returns FALSE.

Throws an SQLException with following SELECT failed error: com.ibm.db2.jcc.am.SqlException: [ibm][db2][jcc][10120][10898] Invalid operation: result set is closed.

ResultSet.next(): if getByte() and getShort() on DECIMAL column result in overflow

Returns -1 for both methods to indicate an error.

Throws SQLException [ibm][db2][jcc][10177][11611] Invalid data conversion: Requested conversion would result in a loss of precision of nnnnnn.

ResultSet.updateTime()

ResultSet.updateTime() can be called on the DATETIME YEAR TO FRACTION column.

Not supported; use updateTimestamp().

Statement.setCursorName(""): Setting cursor name to an empty string

Checks for this condition and throws an illegal cursor name SQLException.

Passes the empty string to the server without returning an error.

Updatable Scroll cursors

Supported for files on the server.

Use Forward-Only, Updatable cursor

 

* For SELECT statements, the statement is complete when the associated result set is closed. The result set is closed as soon as one of the following conditions occurs:

*         Prepared statements

 

This section describes differences in the prepared statement functionality between Informix JDBC Driver and IBM Data Server Driver for JDBC and SQLJ.

Prepared statement differences

 

Functionality

Informix JDBC Driver

IBM Data Server Driver for JDBC and SQLJ

PreparedStatement.executeBatch() error handling

Throws an SQLException, only if there is an error in the batch execution.

Throws the SQLException:com.ibm.
db2.jcc.am.BatchUpdateException';
applications that do not handle the
exception fail at runtime.

PreparedStatement.setCharacter Stream(): characters in reader given do not match the length

Reads n characters as given in length from the start; characters exceeding the length are ignored.

Throws an SQLException

 

*         Parameter Order

There are some differences in parameter order between Informix JDBC Driver and IBM Data Server Driver for JDBC and SQLJ.

According to the JDBC specifications, when the stored procedure returns a result parameter, a form of OUT parameter, it is treated just like any other OUT parameter. Its data type must be registered with the method registerOutParameter, and its value is retrieved with the appropriate getXXX method. Because a result parameter comes first in a call to a stored procedure, its ordinal position is always 1.

When the stored procedure returns the result parameter, it returns a result set and its value is retrieved with the appropriate result set getXXX method.

For IBM Data Server Driver for JDBC and SQLJ, the result of a stored procedure is treated as an OUT parameter. Its data type must be registered (like other OUT parameters) with the method registeroutparameter and its value is retrieved with the appropriate callable statement getXXX method.

To migrate the applications, user needs to remove the question mark (?) = from the {? = call (.......} statement.

*         Large object behavior

 

This section describes differences in large object behavior between Informix JDBC Driver and IBM Data Server Driver for JDBC and SQLJ.

Large object behavior differences

 

Functionality

Informix JDBC Driver

IBM Data Server Driver for JDBC and SQLJ

getString on a BYTE column

Supported.

Returns a HEX representation of the inserted data inserted.

getBlob on a CLOB column

Supported.

Not supported.

Inserting LOB data using filetoblob or filetoclob

Supported.

Not supported.

BLOB and CLOB object updates with setBinaryStream() or setCharacterStream()

Objects updated automatically.

Objects not automatically updated. After calling setXXXStream(), you must issue an SQL UPDATE.

 

*         Extended data types and Extended methods

 

Extended data types are classes provided by the Informix JDBC driver implementation. Presently, there are no replacements for Informix-SQLI extended data types for the IBM Data Server Driver for JDBC and SQLJ. Examples of extended data types include:

*         (com.informix.) Interval, IntervalDY, IntervalYM

*         IfxSmartBlob, IfxCblob, IfxBblob, IfxLocator, IfxLobDescriptor

 

Differences apply to the use of an IDS extended data type in a CallableStatement. With the IBM Data Server Driver for JDBC and SQLJ, an overloaded stored routine will fail when it attempts to resolve the call to a single procedure name if the arguments contain an IDS extended data type.

Extended methods are additional methods provided by the Informix JDBC driver for existing classes. Presently, the IBM Data Server Driver for JDBC and SQLJ has alternate methods for the following JDBC driver methods. If you use these methods, you would have to change them in your application:

 

com.informix.jdbc.IfxStatement.getSerial()

com.ibm.db2.jcc.DB2Statement.setIDSSerial()

com.informix.jdbc.IfxStatement.getSerial8()

com.ibm.db2.jcc.DB2Statement.setIDSSerial8()

com.informix.jdbc.IfxConnection.IfxOffset()

com.ibm.db2.jcc.DB2Connection.getIDSOffset()

 

The ifxType

 

The ifxType does not have a corresponding class in the IBM Data Server Driver for JDBC and SQLJ. Methods that include the ifxType are not supported.

The Informix JDBC driver can use extended methods that specify an Informix type to be transmitted. The DRDA protocol does not support transmitting Informix metadata Informix for Informix data. Following are examples of methods that are not supported:

*         Com.informix.jdbc.IfmxPreparedStatement.setBindColType

*         Com.informix.jdbc.IfmxPreparedStatement.setBindColIfxType

 

*         Error codes

 

Error codes in the ranges +4200 to +4299, +4450 to +4499, -4200 to -4299, and -4450 to -4499 are reserved for the IBM Data Server Driver for JDBC and SQLJ.

When user calls the SQLException.getMessage method after a IBM Data Server Driver for JDBC and SQLJ error occurs, a string is returned that includes:

 

 

The SQLSTATEFor example:

[jcc][t4][20128][12071][3.50.54] Invalid queryBlockSize specified: 1,048,576,012.

Using default query block size of 32,767. ERRORCODE=0, SQLSTATE=

This section describes differences in error codes between Informix JDBC Driver and IBM Data Server Driver for JDBC and SQLJ.

Error code differences

 

Functionality

Informix JDBC Driver

IBM Data Server Driver for JDBC and SQLJ

SQLException.getNextException and getErrorCode

Return an ISAM code and detailed message on SQLException.getNextException and getErrorCode.

An ISAM error cannot be returned through SQLException or getNextException().

Some IDS errors have an SQL error code and an RSAM error code. The driver does not support RSAM error codes returned as nextException or as SQLException.getCause().

Client error messages

Uses -79xxx.

Some messages come from the server and have a different format and description. Client error messages are in the format: -4nnn.

Access to ISAM error codes

Gives ISAM error codes to applications.

Applications need to use diagnostics extensions to get ISAM error code. Applications can get detailed error message for ISAM error codes.

 

*         High availability solutions

High availability solutions are not supported for IBM Data Server Driver for JDBC and SQLJ. These solutions include:

*         High-Availability Data Replication

*         Remote Standalone secondary servers

*         Shared Disk secondary servers

 

Resources

http://www.iiug.org/url/idsv111_help.html

http://jccpedia.svl.ibm.com/index.php/Main_Page