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:

How to get started with Eclipse
Usage Of Eclipse
Develop A Sample Java Application on IDS With Eclipse

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)

<servername> - Name of the IBM Informix Server (as present in the onconfig file)
<service> - What service to use: ontlitcp, onsoctcp, onipcshm, etc.
<machinename> - Name of the machine where the server resides
<portno> - Number of the port used for communication between client & server
csm=(SPWDCSM) enabling the password encryption on server side.

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
Kerberos¹	Yes	Yes	No
Plugin¹	Yes	No	No
Note: 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<