Manual for the i-net ENTWO DB2 Server JDBC driver

Version: 1.10
Last Modified: 14. Mar 2007

Table of Content

1. Installation

1.1. Using the Driver in an Java Application

Add the file Entwo.jar to your classpath or extract the jar file in the directory of the application.

1.2. Using the Driver in an Applet

Add the file Entwo.jar to the Archive attribute of the applet tag, e.g.:

<APPLET CODE="..." Codebase="..." ARCHIVE="Entwo.jar" WIDTH="100%" HEIGHT="100%">
...
</APPLET>

or extract the jar in the directory of the applet.

2. Getting Started

2.1. DB2 Server, Java and JDBC Versions

Java Versions: 1.2.x or higher
JDBC Version: 2.0

DB2 Server Version:

  • DRDA SQLAM Level 6 is minimum for most features, 7 or better is recommended for full functionality
  • UDB 7.x (AS/400, Linux, some features may not work) (NOTE: The versions for Windows, SUN, AIX may not work)
  • UDB 8.x (all OS*)

Tested with:

  • UDB 7.1 (AS/400, client information)
  • UDB 7.2 (Linux)
  • UDB 8.1 (Windows 2000)

*as far as we know

2.2. Implemented Features

  • JDBC 2.0 core API

(also see Known Problems)

2.3. Check Host Name and Port Number of Your Server

This driver works with DB2 Servers that are configured to use the TCP/IP networking protocol. Please verify that your server is currently listening on a TCP/IP port.
If you know that your DB2 Server is listening on a TCP/IP port and you know the host name of your DB2 Server and the port number you can go to the next chapter.

The default port number for the DB2 Server is usually 50000. However servers can be configured to listen on any port number.

To make sure that the RDBMS server is listening on the machine name and port number you specified use:

  telnet <hostname or ip address> <port number>

If the connection is refused then the hostname or the port number is incorrect.

2.4. Driver Class Name

The class name of the driver is: com.inet.drda.DRDADriver

2.5. JDBC URL Subprotocols

jdbc:inetdb2: ….

2.6. JDBC URL Syntax of the driver

jdbc:inetdb2:hostname:portnumber

2.7. Implemented Driver Properties

Name Values Description
bind 0 - do not bind any packages
1 - autodetect if binding is neccessary (default)
2 - force (re)bind all packages
set bind mode
cursorTimeout time in milliseconds The time the thread will wait for a free cursor. A garbage collection is suggested to the VM at this time to recover unclosed resources.
database database to use (mandatory)
host overide the value in the JDBC URL
level dump - dump all send/received data packets
debug - show debug data
info - show informational data (default)
warn - show warnings only
error - show errors only
fatal - show fatal errors only
each level automatically includes all more severe ones
set log level (only for this connection)
log out - enable logging to console (stdout)
err - enable logging to console (stderr)
off - disable logging
filename - enable logging to this file
set logging (only for this connection)
port overide the value in the JDBC URL
safe true - disable optimizations
false - enable optimizations
switch off optimizations
sqlam maximum sqlam level allowed (see drda spec)

There are three ways to put the properties to the driver:

  • append the properties to the URL like this
	jdbc:inetdb2:hostname:portnumber?database=MyDb&language=deutsch
  • call from method getConnection(String url, Properties info) from the driver manager.
  • create a DataSource and set the properties with the set methods.

2.8. Monitored Failover

With the interface FailoverListener it is possible to manage the reconnection process. The FailoverListener receives an event if the Connection was broken, if the reconnection process was successful and if an error occurred be the try of reconnect. In depend of the value returned from FailoverListener, a new try of reconnect will be started or not.
The Connection tryes to reconnect to the same server with the same properties. If reconnection was successful, all Statements, PreparedStatements and CallableStatements will be restored. The ResultSets won't be restored. For restore that, you have to executed the Statement again.
For more information see com.inet.pool.PoolManager.addFailoverListener(), com.inet.pool.FailoverListener, com.inet.pool.FailoverEvent and sample.MonitoredFailover.

2.8.1. Configuration via Connection properties

There are multiple possibilities about how to configure the Monitored Failover. It is possible to use the default implementation of the FailoverListener or to use your own implementation. The default FailoverListener is similar to the TimedFailoverListener in the samples package. To every created Connection automatically an instance of the configured FailoverListener will be added.

Set a FailoverListener via Connection properties:

Name Default Description
useDefaultFailoverListener false if set on true, a default FailoverListener will be added automatically to every Connection
waitTimeToNextAttempt 500 property for the default FailoverListener. Sets the wait time in millis, that will be wait to the next reconnect attempt.
totalTimeOfAttempts 30 property for the default FailoverListener. Sets the total time in seconds, in that the reconnection process must be finished.
failoverListener - the class name of your own FailoverListener implentation. This class has to implement the FailoverListener interface AND the default public constructor. This is important to create a new instance of this class via reflection.

Example URLs:

  • jdbc:inetpool:inetora:localhost?database=orcl&useDefaultFailoverListener=true&waitTimeToNextAttempt=100&totalTimeOfAttempts=10
  • jdbc:inetpool:inetora:localhost?database=orcl&failoverListener=test.MyFirtsFailoverListener

2.8.2. Configuration in Java

For more flexibility it is also possible to manage the registered FailoverListener of a Connection in Java. A FailoverListener can be only added to a Connection, that implements the interface TAFConnection. Those Connection can be received from:

  com.inet.pool.PoolDriver,
  com.inet.ora.PDataSource,
  com.inet.tds.PDataSource,
  com.inet.syb.PDataSource,
  com.inet.drda.PDataSource.

The interface TAFConnection allows you to add and remove a FailoverListener to/from the Connection.

Example:

  //receive a Connection 
  Class.forName("com.inet.pool.PoolDriver");
  Connection con = DriverManager.getConnection("jdbc:inetpool:inetora:localhost?database=orcl", "scott", "tiger");
  //add a FailoverListener
  FailoverListener fl = new samples..MonitoredFailover.CounteredFailoverListener(99);
  ((TAFConnection)con).addFailoverListener(fl);
 
  //remove a FailoverListener
  ((TAFConnection)con).removeFailoverListener(fl);
 
  //remove all FailoverListener
  ((TAFConnection)con).removeAllFailoverListener();
 
  //get all registered FailoverListener
  FailoverListener[] fls = getFailoverListener();

3. Escape Clauses

The driver implements escape clauses as follows:

3.1. Date and Time

{d 'yyyy-mm-dd'}
??? {t 'hh:mm:ss[.fff]'}
??? {ts 'yyyy-mm-dd hh:mm:ss[.fff]'}

3.2. Stored Procedures

{call storedProcedures('Param1'[,'Param2'][,?][…])}
{? = call storedProcedures('Param1'[,'Param2'][,?][…])}

Example:

  st = con.createStatement();
  st.execute("{call MyProc('value')}");
 
  pr = con.prepareStatement("{call MyProc('value')}");
  pr.execute();
 
  pr = con.prepareStatement("{call MyProc( ? )}");
  pr.setString("value");
  pr.execute();

3.3. Functions

The following functions are supported. You can find more info at http://java.sun.com/products/jdbc/driverdevs.html.

Example:

  st = con.createStatement();
  rs = st.executeQuery( "select * from all_users where username like {fn ucase( 'scott' )}" );
  ...
  rs.close();
  st.close();

3.3.1. Numeric Functions

{fn ABS(number)}
{fn ACOS(float)}
{fn ASIN(float)}
{fn ATAN(float)}
{fn ATAN2(float1,float2)}
{fn CEILING(number)}
{fn COS(float)}
{fn COT(float)}
{fn DEGREES(number)}
{fn EXP(float)}
{fn FLOOR(number)}
{fn LOG(float)}
{fn LOG10(float)}
{fn PI()}
{fn POWER(number, power)}
{fn RADIANS(number)}
{fn RAND(integer)}
{fn ROUND(number, places)}
{fn SIGN(number)}
{fn SIN(float)}
{fn SQRT(float)}
{fn TAN(float)}

3.3.2. Time and Date Functions

{fn now()}
{fn curdate()}
{fn curtime()}
{fn DAYNAME(datetime)}
{fn DAYOFMONTH(datetime)}
{fn DAYOFWEEK(datetime)}
{fn DAYOFYEAR(datetime)}
{fn HOUR(datetime)}
{fn MINUTE(datetime)}
{fn MONTH(datetime)}
{fn MONTHNAME(datetime)}
{fn QUARTER(datetime)}
{fn SECOND(datetime)}
{fn WEEK(datetime)}
{fn YEAR(datetime)}
{fn TIMESTAMPDIFF(interval, count, datetime)}
{fn TIMESTAMPADD(interval, datetime, datetime2)}

interval may be one of the following:

SQL_TSI_FRAC_SECOND
SQL_TSI_SECOND
SQL_TSI_MINUTE,
SQL_TSI_HOUR
SQL_TSI_DAY
SQL_TSI_WEEK
SQL_TSI_MONTH,
SQL_TSI_QUARTER
SQL_TSI_YEAR

3.4 Outer Join

{oj table1 LEFT OUTER JOIN table2 ON table1.id = table2.id}
{oj table1 RIGHT OUTER JOIN table2 ON table1.id = table2.id}

4. Debugging and Logging

If you have problems with the driver then you can enable the logging of the driver with:

  DriverManager.setLogStream( System.out );

or

  DriverManager.setLogStream( System.err );

You can also use any other PrintStream to make this output. This can be used if you can not see the default PrintStreams
If you use the driver in an servlet, .jsp script or in an Application Server then you need to set an PrintStream on an log file:

  PrintStream ps = new PrintStream( new FileOutputStream( "c:\driver.log" ) );
  DriverManager.setLogStream( ps );

Now the driver prints the logging output in the file that you have specified.
Please note: Enable the logging of the driver only when you need it to find a problem because the driver performance will be better without the logging.

5. Known problems

5.1. Packages are not bound

If you get error 51002 this means you have not bound the packages this driver needs. These are the packages INETCRSR and INETHOLD.
The driver will try to bind the packages automatically, but if it does not have the permissions to do so we have provided a small tool for you.

Simply execute:

"java -cp ENTWO.jar com.inet.drda.BindUtil jdbc-url username password"

where the username you provide has to have the privilege to create packages in the database.

5.2. Unsupported Features

  • CLOB / BLOBS not fully supported currently.
  • Callable Statements returning ResultSets are not supported currently.
  • Updatable Result Sets not fully supported currently.

The following methods are not supported by this release:

  • PrepareStatement.setArray (int i, Array x) (Arrays are not supported by DB2)
  • PrepareStatement.setRef (int i, Ref x)
  • CallableStatement.getObject (int i, java.util.Map map)
  • CallableStatement.getRef (int i)
  • CallableStatement.getArray (int i) (Arrays are not supported by DB2)
  • DatabaseMetaData.getUDTs(String catalog, String schemaPattern, String typeNamePattern, int[] types)
  • ResultSet.getObject (int i, java.util.Map map)
  • ResultSet.getRef (int i)
  • ResultSet.getArray (int i) (Arrays are not supported by DB2)
  • Connection.getTypeMap ()
  • Connection.setTypeMap (java.util.Map map)

5.3. Prepared Statements and Alter Table

If you prepare a statement and alter the table later, the prepared statement will not work any longer as expected and will return the old meta data. As almost all applications do not alter the tables in a production environment and a fix would result in a slowdown we decided not to fix this now. If you have urgent need for this you can force the SQLDA level to 6 or send an email and we can see what is appropriate for you.

6. Documentation

7. Support

Please read this file and our FAQ at: http://www.inetsoftware.de/products/jdbc/db2/faq.htm

If you have not found your problem in the FAQ then post your problem to our helpdesk system as much information of what occurred or happened as possible. You can find more info about our support at: http://www.inetsoftware.de/support

Please include at least:

  • Name and version of the database you are using.
  • Name and version of the database servers operating system.
  • Description of the problem.
  • The drivers log output with dump enabled.
  • URL parameters: log=yourLogFile.log&level=dump

If possible/applicable:

  • Test case to reproduce the problem.
  • Additional configuration information (e.g. application server)

8. Changes

8.1. Changes from 1.00

  • initial release

8.2. Changes in Version 1.01

  • Fixed codepage handling on non-intel machines (e.g. AS/400)
  • Standalone Bind Utility

8.3. Changes in Version 1.02

  • Fixed problems with spaces around select statements
  • Fixed problems with value statements
  • log url parameters
  • Updatable ResultSets now return changed values.
  • Support for DataSources

8.4. Changes in Version 1.03

  • Fixed problems with unicode characters and clobs not using the correct codepage
  • Pool for prepared statements if SQLAM >= 7 (e.g. DB2 UDB 8.x)

8.5. Changes in Version 1.04

  • The default codepage for data types is now ASCII, because the IBM jdk doesn't know the codepage 0.
  • A bug that a PreparedStatement tried to open a ResultSet cursor that was already open has been fixed. It concerns PreparedStatements with the same sql statement.
  • A bug that Clob and Blob data will be ignored from db2 servers is fixed. The data will no longer be sent as externalized data.
  • Support for setting Clob objects with PreparedStatement.setObject().
  • Support for setting InputStream with PreparedStatement.setObject().
  • A bug that utf-8 encoded Strings were not saved completely in the database has been fixed.
  • A bug that utf-8 encoded Strings were not read completely in the database has been fixed.
  • The ResultSet methods isBeforeFirst(), isFirst(), isLast(), isAfterLast(), next(), getRow() now return in the correct value in each case.

8.6. Changes in Version 1.05

  • A bug that connecting failed because of codepage 1114 has been fixed. The codepage will be mapped to big5.

8.7. Changes in Version 1.06

  • The com.inet.pool.TimedFailoverListener / DefaultFailoverListener calculated the end of reconnect process too early.

8.8. Changes in Version 1.07

  • The Exception “com.inet.drda.SQLException: unexpected response: TYPDEFOVR” was thrown on reading ResultSet was fixed.
  • DatabaseMetaData.supportsSavepoints will now return false as SavePoints are not supported. (In previous versions true was returned by mistake.)

Copyright by i-net software
More info and updates can be found at http://www.inetsoftware.de/

© 2003-2007 i-net software

 

© Copyright 1996 - 2017, i-net software; All Rights Reserved.