Informix has four protocols, or communication channels, which you can use to access the server, SQLI, DRDA, JSON, and RESTful. If you are familiar with one of these and want to know how to perform the same basic operations using one of the others, consider this post to be your Rosetta stone.

The sample application is written in Java. It iterates through using the Informix JDBC driver (which uses the SQLI protocol), the DB2 JCC driver (which uses DRDA), the MongoDB driver for Java (JSON), and native Java HTTP classes (REST). For each of these protocols, it goes through the steps to:

Create a user

Create a database

Create a table/collection

Create a row or document (entry)

Read an entry

Update an entry

Delete entries

Drop a table/collection

Drop a database

Drop a user

The code should be self-explanatory, but there are some differences between the APIs worth noting.

Using the SQLI and DRDA protocols, connections are stateful. There is an open channel maintained between the port listening for commands on the server and the port on the client sending commands. So, transaction states are easily maintained, and multiple commands can be executed without re-authenticating. Most operations are identical between these two, except the URLs are different. Informix JDBC requires an INFORMIXSERVER setting. You can not drop the database you are currently accessing. Authentication takes place at the server level, allowing you to access whatever database for which you have appropriate privileges.

The MongoDB clients are what I'll call semi-stateful. Client and db objects have the appearance of maintaining a connection with the database server, but really the connection information is cached on the client side and as far as the listener is concerned every operation is independent. (Exceptions to this model are beyond the scope of this write-up.) The database you are dropping must be the current database. Authentication takes place at the database level.

Fitting the REST model, there is no concept of a stateful connection in the RESTful API. However, there are two ways to authenticate: Providing a user name and password as is common, and sending a session id cookie from a previously authenticated operation. Using a session cookie is how you can, say, authenticate against the admin database, and use your userAdminAnyDatabase privilege to create a new user within another database.

Users with the ability to create other users must already exist for this demo to work. Examples:

The download file contains the source code and Eclipse project that can be used to build it. It also contains all the required drivers; it is your choice whether to use the ones included or use more modern ones you have installed. Included in the project is an ANT script for building the jar.

Run the demo with the command: java -jar basicChecks.jar connSettings.properties

Running the demo without the required properties file will give you instructions for what the file should contain.

<"element"> = data that must be filled out specifically from your setup. The word "element" can be replaced by "host", "port", etc.

JCC driver = jar and zip files necessary.

(JCC driver may be obtained on: https://www14.software.ibm.com/webapp/iwm/web/preLogin.do?lang=en_US&source=swg-informixfpd Its the file identified as "IBM Data Server Driver for JDBC and SQLJ Version 3.52 (This is platform independent)").

{JCC_JAR_FILE} = The .jar file with all the classes for the JCC driver. (Usually named db2jcc.jar or db2jcc4.jar).

Prerequisites:

*Have IDS installed with DRDA connection set up.*Have the JCC drivers.(Informix sqli driver is NOT officially supported on Mac OS X)*Install the "stores_demo" database by running the following:

"dbaccessdemo -log". Script available in the $INFORMIXDIR/bin directory.This is for testing purposes.

Steps to follow:

1-Obtain the following information about your IDS server You want to find out host, port, database , username and password:(Its going to be needed for the JDBC URL which is in the form of: jdbc:ids://<host>:<port>/<database>:user=<username>;password=<password>; )

host: IP address or name of machine running the database. This is the computer's name. You may find this in sqlhosts file. The third value of any one of the lines that have a server thats being hosted locally on your computer (or on the computer in which the server you want to connect to is in) will be the "host". Write it down.

port: The "port" on which your server is listening to. Its very important to keep in mind that the port has to be one that is using the 'drsoctcp' protocol. To verify this check the sqlhost file. Verify that the second value on one of the lines within this file is the indicated protocol. At the end of the line you will see a number. This will be the "port" that you'll need. Write it down.

database: The database is the name of the database which you will want to access. For example, if you are using the database that the script "dbaccessdemo" creates, then the "database" name would be "stores_demo".

username: The user name of the user that will be connecting to the database

password: The password of the previously mentioned user

2-Determine the appropriate version of the JCC driver to use. If you have JDK 6, you should use JCC version 4. The file related to this version is "db2jcc4.jar". If you have a previous version of the JDK you should be using JCC version 3. The related file is "db2jcc.jar". A way to determine which JCC version you are using is as follows:

"java -cp {JCC_JAR_FILE} com.ibm.db2.jcc.DB2Jcc -version"

Something to keep in mind about your version is that if you are using JCC version 4, then you don't need to use the Class.forName() method.

3-Now its time to create a sample app. To see a good few samples see:http://publib.boulder.ibm.com/infocenter/idshelp/v115/topic/com.ibm.jccids.doc/com.ibm.db2.luw.apdv.java.doc/doc/cjvjdbas.htm

To test out your installation, try out the included sample java class, which is just a slightly modified version of the sample found on: http://publib.boulder.ibm.com/infocenter/idshelp/v115/index.jsp?topic=/com.ibm.jccids.doc/com.ibm.db2.luw.apdv.java.doc/doc/cjvjdbas.htm

A few things to consider about the sample:You must give as a command line input the information to complete the URL. The full URL needed is in the form of: jdbc:ids://<host>:<port>/<database>:user=<username>;password=<password>; (to see how to obtain the information needed to fill out the URL, see step 1.)

The sample code already includes the "jdbc:ids" part of the URL, so you must pass the rest of the URL. For example, if the host is 'myhost', the port is '1234', the database is 'test_db', the username is 'informix' and the password is 'mypass', then to run the sample application (with it being named EzJava), you'd run it as follows:

java EzJava '//myhost:1234/test_db:user=informix;password=mypass;'

NOTE: This is supposing that the appropriate jar file is included in the CLASSPATH environment variable. If not, you must include it with the "-cp" flag.

Keep in mind that the query executed by the sample app will probably not work with your db, so change the query accordingly to test it out with a table of your own. For this example, you need to have set up your logging mode to be buffered or to log. Otherwise you'll get an exception when trying to commit your connection. If you want to use the "stores_demo" database, to create it run the "dbaccessdemo -log"

4-Make your own app. If you were able to compile and run the sample app without a problem, congratulations, you are now ready to develop your own app. Enjoy!

Previous version of JDBC 3.50 could not be installed on Windows 64-bit using 64-bit JRE. Installing JDBC 3.50 gave an error "Directory not writable" for all directories. Same error message was received, even after running all the process as administrator by turning UAC (User Account Control) off. Workaround for this problem was to use 32-bit JRE on Windows 64-bit to install JDBC 3.50.

This problem was solved in IDS 11.50. Now JDBC 3.50 uses newer version of Install Shield, which allows for JDBC 3.50 to be installed on 64-bit Windows using 64-bit JRE. Now, users no longer have to use 32-bit JRE to install JDBC 3.50 on Windows 64-bit. This makes users life easier, since users do not have to set up 32-bit JRE on 64-bit Windows.

The best-fit Java class for an Informix DATETIME value is java.sql.Timestamp. Java.sql.Timestamp technically inherits from java.util.Date; although, there are some semantic differences which are not typical of inheritance relationships. All the constructors, getters, and setters which are not deprecated for Date and Timestamp use values based on Greenwich Mean Time (GMT). They are offsets in milliseconds from the epoch, January 1, 1970 00:00:00 GMT. So, it would be logically consistent for Timestamps to convert to DATETIMEs using the GMT reference frame by default. However, the JDBC Tutorial and Reference specifies that, when no Calendar is provided, Timestamp values are converted to the time zone of the JVM in which the application is running. The difficulties surrounding Timestamps and time zones are not limited to the scope of the Informix JDBC driver; they are generic to all of JDBC because that is the specification.

This model creates problems in a variety of ways. For distributed applications, the Date or Timestamp values might be streaming into the database from JDBC drivers located in different time zones. With multi-tier applications, you might find yourself contending with a user interface in time zone A, the JDBC driver in time zone B, and the database server in time zone C. The driver is the library where external types are converted to internal types; so, where it is running is where the local time zone comes into play. It can be difficult for either the front end or the database to know in what time zone the driver is operating. Dealing with the conversion from Timestamp in GMT to DATETIME in the JDBC driver locale has to be done in the application layer directly interfacing with the JDBC driver.

The conversion done by the driver applies in both directions, and this can lead to complications if anything other than the JDBC Timestamp class is used in conjunction with DATETIME values. If strings representing DATETIMEs or DATETIME literals are used anywhere in your application SQL (including CURRENT), the values will not be converted on the way in, but will be converted on the way out. Similarly, different APIs will not convert in either direction. If you insert new Timestamp(86400001) (January 2, 1970 00:00:00.001 GMT) through JDBC, and select it back from within the same JVM, you will get that same value back. So, you might expect that selecting the same row-column from, say, DBAcccess, would also give you the same value, but it will not because the value was converted to JVM time zone on the way in through JDBC, but not converted on the way out through ESQL/C. If you are in the U.S., you will get some time on January 1st, not even the same day. The reverse also applies, you may find yourself with values inserted through other APIs, which perform no conversion, that are converted to some other time zone when selected through JDBC.

DATETIMEs, of course, do not contain time zone information. There are basically two ways to deal with this, record the time zone information in another column along with the DATETIME or establish a convention that all the DATETIMES in the database are in the same time zone. Establishing a convention is the simpler approach both from an application implementation perspective, and in terms of being able to easily compare values in different rows, columns, or tables. If you know that every Java application which will ever access the data will operate in its own JVM, you can code your applications to set the default time zone of the JVM, but this is a problematic solution when more than one, independent application is running in the same JVM, such as servlets in a web server. It is unclear what would happen with different threads of execution changing the time zone for all other threads, but if they were actually able to do that, it would not be pretty.

At JDK 1.1 two things happened, all the getters and setters for java.util.Date where the meaning was ambiguous with respect to time zone were deprecated and the java.util.Calendar class was introduced. Along with the Calendar class itself, methods accepting a Calendar object were added to the JDBC interface, for example PreparedStatement.setTimestamp(int parameterIndex, Timestamp x, Calendar cal) andResultSet.getTimestamp(int columnIndex, Calendar cal). In these methods, the Calendar object establishes the frame of reference used to convert from the GMT offset in the Timestamp object to or from a DATETIME value. If you create a constant Calendar object in your applications, and use it every time you read or write a Timestamp object to a DATETIME, the values DATETIME will remain consistent with each other, and the meaning of the value will not change dependent on the time zone of the JVM.

Using a GMT calendar would be efficient because it requires less operations to convert between the Java classes and the value in the database. Also, the value will not change if other APIs, like ODBC or ESQL/C, are used. Conceptually, what is stored in the database is January 1, 1970 00:00:00.000 GMT + tstamp.getTime() milliseconds.

Calendar cal = Calendar.getInstance(TimeZone.getTimeZone("GMT"));

Coordinated Universal Time (UTC) is a close relative to GMT and is just as viable.

In this model, all date and time values in the database are on the same time line, and conversion to other time lines or calendar systems are handled at the user interface level. Using UTC or GMT for all DATETIME values provides a simple model to implement in applications. It also removes all the problems related to knowing the time zone of the JDBC driver runtime or if applications written in anything other than Java will ever be used to access the data.

EstablishingJCC connections for IDS

While working with the open beta of IBMData Server Developer Workbench 9.5 , I noticed that inaddition to that product you also become open beta participants for theJava Common Client (JCC) driver. In fact, the Workbench uses the JCCdriver by default. On the one hand, this is absolutely great, because ithelps you see where IBM's long range direction is with clients. On theother hand, JCC requires some special setup on the IDS instance level.This article will detail how to setup you instance to handle JCCconnections, and then will use the Developer Workbench to show how youconnect using JCC.

Whathappened to my SQLI?

The reason why JCC connections require engine configuration changes isthe default means by which IDS instance communicate is through the SQLIprotocol, whereas JCC needs the DRDA protocol. Currently IDS does notallow for both SQLI and DRDA to connect through the same listenerthread, so this means is we need to configure a listener thread andport to accept DRDA calls.

This is done by making a simple change to the SQLHOSTS file as shownbelow:

<instance name> drsoctcp<hostname> <service/port>

or

<instance name> drsipcshm<hostname> <service/port>

As you can see the protocols look the same as our old ones, exceptinstead of the prefix ol,you use dr.

And full example is below:

cheetah_dr drsoctcp gofishsqlexec2

After changing the SQLHOST all you need to do is place this entry in youonconfig file, in the DBSERVERALIASES variable. So if you use theexample SQLHOSTS entry above and had no aliases currently you wouldhave the following in you onconfig file:

DBSERVERALIASES cheetah_dr

Now just bring your instance off-line and then back on-line, and then weare ready to use the Developer Workbench.

Setting up yourJCC Driver for 11.10 in Developer Workbench 9.5

Now that you have set your IDS instance up to accept JCC driverconnections, let's establish one using the Developer Workbench 9.5

This will bring you to the Connection Settings window. Below is anexample of the JCC driver with all field filled in:

As you can see, you do not need an INFORMIXSERVER for your connection,just the Database, Host and Port Number. Once you configure this foryour server, just click the TestConnection button. After that just click the Finish button. Younow have a JCC connection that you can use for your Applicationrunning against IDS 11.10

I'm happy to announce that Informix JDBC driver 4.10.8.1 is out on Maven Central!

Using a distribution platform such as Maven, it's now easier than ever to download, upgrade, and utilize the Informix JDBC driver in your applications. You can bypass the traditional installer and download site and get the driver you need quickly and efficiently.

Here is a link to the Maven page for the new driver. On the page they have examples for many build systems (Gradle, Maven, SBT, and more) on how to include our driver. You can also directly download the the jar file from the site.

The group we use for Maven is 'com.ibm.informix'. This will be where we push out relevant technologies (like JDBC driver) that make sense to have a home on Maven. Wondering where ifxjdbc.jar and ifxjdbcx.jar files went? We combined the functionality into a single jar file. Now you can just download and manage one file.