ReportCaster and JDBC

By Joe Trelin

There seems to be no shortage of questions and concerns regarding connecting to the ReportCaster Repository via the JDBC, particularly with MVS and UNIX configurations.

This article addresses these questions. Included is a brief explanation of what the JDBC is; how and why we need to certify databases and JDBC drivers; an explanation of the different types of JDBC drivers; a list of JDBC driver configurations; and what to consider if you need to install a new, nontested driver.

The JDBC Interface

The JDBC is a layer of abstraction that allows Java™ programs to uniformly connect to single or disparate data sources. All you need is the proper JDBC driver classes installed on the machine(s) requiring access to the database, and the location of the database server. For ReportCaster, both the distribution server and the application server speak with the database. Because of this, the JDBC driver classes need to reside on both machines, and both machines need to know how to find this class information. This is done by setting a proper Classpath.

Regarding the context information that tells the JDBC driver where the database is and how to gain access – this is configured in ReportCaster by either using the distribution server configuration tool, or editing an encrypted file called dserver.xmls directly. Even though both the application server and the distribution server access the database, the configuration is done on the distribution server, which then passes this "context" information to the Servlets running in the application server container upon authentication.

In general, to configure ReportCaster to speak with the repository you need to do a few things:

	Make sure the JDBC classes are in the proper classpath(s), so both the application server and distribution server can find them.
	If you are using a Type 2 driver the native code needs to be in the path or shared library path.
	You need to be aware of the name of the Classname and the URL. The DBName is only necessary for DB2 on MVS.
	•	Classname – e.g., com.microsoft.jdbc.sqlserver.SQLServerDriver. This is the actual name and location of the driver class within the .jar file or .zip file pointed to by the Classpath.
	•	URL – e.g., jdbc:microsoft:sqlserver://JTRELIN1:1433;DatabaseName=testCaster. This will vary from driver to driver and it will contain the information that the specific JDBC driver needs to find and access the database server. You can discover the format of the URL you need to use from the driver documentation.

Theoretically, if you use "standard" SQL (if there is such a thing), the JDBC should allow your application to utilize any database that has a JDBC driver. Of course, theory is often not reality. There are a number of considerations before a database can be used as a ReportCaster repository. Each database presents variables that are outside the scope of the JDBC driver or the JDBC specification. Because of these, some databases don't necessarily work out of the box, or shouldn't be used, which is why we require certification. Below are a few of the things that need to be considered for each certified database:

	Each database has strengths and weaknesses that go beyond the capabilities of the driver. For example, although we could connect to an MS Access database, we don't certify it. Microsoft does not regard Access as a production-level database like SQL Server. Under enterprise load conditions, it might have problems managing multiple connections, as well as with response time, security, and production level volumes of data.
	Not all JDBC drivers are created equally. Your access to the DB is as good as your driver. For example, the SQL Server Driver Microsoft distributes doesn't offer IWA support, but a third-party driver from Merant (who also builds the Mircrosoft driver) does.
	Standard SQL is a concept, not a reality. The set of uniform SQL calls is small. Each database has unique ways of handling certain data types and complex SQL calls such as Unions. All databases require certification to see if they respond to our SQL calls correctly.

Complicating matters even further is that even after we certify a database, changing the JDBC driver can affect performance. With such a large combination of environments and driver types, testing them all becomes a full time job. For example:

	Oracle and DB2 offer both JDBC Type 2 and Type 4 drivers
	Oracle drivers differ for versions of the JVM
	New database versions often include new, recommended drivers
	Drivers might differ per platform because of changes in JVMs

We do our best to point users to a driver we have tested, and we recommend users connect via tested configurations. However, with so many quickly changing combinations, there are many drivers that have yet to be tested. For those situations, our products are written to JDBC standards and should seamlessly work with these changes.

However, changes in drivers are largely the domain of the DB providers. Users of new drivers need to review the vendors' various JDBC driver Web sites for specific information. (We have listed the Web sites below.) As a commitment to customers, Information Builders will continue to test these changes made by other vendors and report issues, bugs, and configuration information immediately.

Using Noncertified JDBC Drivers

Here are some tips for when you're in a situation requiring the use of a new driver:

Determining the Driver Type

Regarding finding the proper driver, the major database manufacturers have Web sites devoted to explaining and downloading drivers. Here are the Oracle, Microsoft, and DB2 sites:

	Microsoft: click here
	Oracle: click here, here, or here
	DB2: click here

Additionally, DB2 configurations are often troublesome. The charts below outline the DB2 JDBC drivers and their usage.

DB2 JDBC Driver Types and Their Usage
JDBC Driver Name	Associated DB2 UDB
DB2 Universal JDBC Driver (used for 8.x)	DB2 UDB for Linux, UNIX, and Windows or DB2 UDB for z/OS (see installation notes below)
JDBC/SQLJ 2.0 Driver for OS/390	DB2 UDB for z/OS (click here for installation instructions)
DB2 JDBC Type 2 Driver for Linux, UNIX, and Windows (DB2 JDBC Type 2 Driver) (used for DB2 7.x)	DB2 UDB for Linux, UNIX, and Windows (the driver is COM.ibm.db2.jdbc.app.DB2Driver)

DB2 UDB JDBC Driver for 8.x Installation Notes (Combination Type 2/Type 4 Driver)
JDBC Driver Name	Installation Notes
DB2 UDB Version 8 for Windows products	Install the db2jcc.jar and sqlj.zip files and add them to the system CLASSPATH Install file db2jcct2.dll, which is required for Universal Type 2 driver, in the sqllib\bin directory
DB2 UDB Version 8 for UNIX	Install the db2jcc.jar and sqlj.zip files Add the db2jcc.jar to the CLASSPATH statement in the db2profile (for Bourne or Korn shell) or db2cshrc (for C shell) script Install file libdb2jcct2.so (libdb2jcct2.sl for HP-UX), which is required for Universal Type 2 driver, in the sqllib\lib directory

The Four Types of JDBC Drivers

After you've read through the Web sites, you'll probably have a choice between driver types, usually Type 2 or 4. Below is an explanation of the four types of JDBC drivers.

Type 1: The ODBC-JDBC bridge. This is a Windows-only configuration. We use this for SQL Server 6 or 7. It allows the Java application to connect to the database through a mapping between a Microsoft ODBC DSN and Sun's ODBC:JDBC bridge (which comes with Java.) This is considered an unstable, semi-supported configuration and should be used only as a last resort.

Type 2: This allows the Java application to connect to the database by using both pure Java classes and native code. For this type of connection, generally you need to include the Java code in the Classpath and the native code in the path or shared library path. While type 2 drivers aren't as portable as all Java drivers, they often do have certain performance advantages in high transaction environments and should be considered as an option.

Type 3: This is, in effect, a JDBC server. You connect once, via your driver, to a server, and it makes connections, independent of your code, to the databases you need and then returns the information you request. These are used mainly for connecting disparate data sources, or unique databases. There would probably be little reason to use this type of driver with ReportCaster. But iWay is a Type 3 driver.

Type 4: Also called a "thin driver," this in an all-Java driver with no native code component. Its general advantage is portability. Some Type 4 drivers don't offer the same or as robust functionality as Type 2 drivers.

Testing the Driver

It is very easy to swap out drivers for testing. Reconfiguring ReportCaster involves changing dserver.xmls, adding the new driver to the Classpath and making sure the application server has the driver in its Classpath. If you are using a batch file to start the distribution server you might want to make sure the Classpath in the batch file is correct as well.

If you simply want to test the driver prior to installing ReportCaster, Information Builders also has a small program that will allow you to easily change configurations and check database connectivity. Please contact me if you'd like a copy.