DB2 and Java

The Big Picture

Kathryn Zeidenstein is a contributing author for developerWorks.

Summary: Start here for your introduction to Java support in DB2 Universal Database.

Date: 13 Feb 2003
Level: Introductory
Comments:

IBM® DB2® Universal Database™ (UDB) supports all the key Internet standards, making it an ideal database for use on the Web. It has in-memory speed to facilitate Internet searches and complex text matching combined with the scalability and availability characteristics of a relational database. DB2 UDB supports WebSphere®, Java™, and XML technology, making it easy for you to deploy your e-business applications.

DB2 Universal Database supports many types of Java programs. It provides driver support for client applications and applets written in Java using JDBC™. It also supports embedded SQL for Java (SQLJ), Java user-defined functions (UDFs), and Java stored procedures.

This big picture describes the Java application development environment provided by the DB2 UDB Universal Developer's Edition Version 8 (UDE), DB2 for z/OS™ and OS/390® (V7) and iSeries™ (V5R2).

Java platform support

This section describes the following features:

JDBC support
SQLJ support
J2EE support

JDBC support

According to the JDBC specification, there are four types of JDBC driver architectures:

Type 1 - drivers that implement the JDBC API as a mapping to another data access API, such as Open Database Connectivity (ODBC). Drivers of this type are generally dependent on a native library, which limits their portability. The JDBC-ODBC Bridge driver is an example of a Type 1 driver.
Type 2 - drivers that are written partly in the Java programming language and partly in native code. The drivers use a native client library specific to the data source to which they connect. Again, because of the native code, their portability is limited.
Type 3 - drivers that use a pure Java client and communicate with a middleware server using a database-independent protocol. The middleware server then communicates the client's requests to the data source.
Type 4 - drivers that are pure Java and implement the network protocol for a specific data source. The client connects directly to the data source.

DB2 UDB Version 8 for Linux, UNIX®, and Windows® supports Type 2, Type 3 and Type 4 JDBC drivers. The JDBC drivers in previous releases were built on top of DB2 CLI (Call Level Interface). DB2 Version 8 Type 2 and Type 3 drivers continue to use the DB2 CLI interface to communicate to DB2 UDB servers on UNIX, Windows, Linux. When applications on Linux, UNIX, and Windows require access to DB2 data on OS/390 and z/OS and iSeries a DB2 Connect server is usually required. DB2 UDB Version 8 for Linux, Unix and Windows adds a new DB2 JDBC Universal Driver (Type 4), which uses the Distributed Relational Database Architecture™ (DRDA®) protocol for client/server communications and is compatible with various DB2 server platforms with appropriate DRDA Application Server (AS) level support, and prerequisite stored procedures. This new JDBC Universal Driver can be used to access data across the DB2 family of database servers. This driver currently supports DB2 UDB v8 servers on UNIX, Windows, Linux, and DB2 UDB v7 and v8 servers on z/OS. The future direction is for this driver to also support DB2 on iSeries.

The DB2 for iSeries database server provides a Type 4 JDBC driver known as the iSeries Toolbox driver for Java. The iSeries Developer Kit for Java contains a Type 2 JDBC driver built on top of the iSeries DB2 Call Level Interface (CLI) native libraries.

DB2 for z/OS v7 provides a JDBC Type 2 driver and has announced plans to support the JDBC Universal Driver (Type 4) in the upcoming DB2 V8 for z/OS release.

The DB2 JDBC drivers support many of the latest JDBC 3.0 features.

SQLJ support

DB2 SQLJ support enables you to build and run SQLJ applets and applications. These Java programs contain embedded SQL statements that are precompiled and bound to a DB2 UDB database.

The SQLJ has three components: a translator, customizer, and a run-time environment. The translator produces Java code based on the embedded SQL statements within a source SQLJ program. A binary representation of the SQL statements is created in a separate serialized profile (.ser file). Static SQL packages are created when the profile is customized using the db2sqljcustomize command.

Figure 1. SQLJ application development using the Type 4 Java Universal Driver (process may vary depending on driver and release)

SQLJ provides:

A static package level security model
A static SQL interface (e.g., SELECT xxx INTO :hv1, :hv2)
Increased development productivity as compared to JDBC, especially if an application is being ported from an existing embedded SQL architecture (C, COBOL, etc.)

J2EE support

Java 2 Platform, Enterprise Edition (J2EE) defines the standard for developing multi-tier enterprise applications. The J2EE platform manages the infrastructure and supports the Web services to enable development of secure, robust and interoperable business applications. J2EE not only takes advantages of many features of the Java 2 Platform, Standard Edition, such as "Write Once, Run Anywhere" portability, JDBC API for database access, but also adds full support for Enterprise JavaBeans (EJBs) components, Java Servlets, JavaServer Pages (JSPs) and XML technology. With simplicity, portability, scalability and legacy integration, J2EE technology and its component-based model simplify enterprise development and deployment.

Figure 2. J2EE application model

There are two types of entity EJBs (Enterprise Javabeans): container-managed persistence (CMP) and bean-managed persistence (BMP). WebSphere Studio provides the ideal environment for creating J2EE applications including entity EJBs. Many Java developers prefer to use CMP EJBs to simply access to data stored in DB2 database servers. This flexible and portable programming model abstracts the data access layer using an object to relational mapping layer and the Enterprise Javabean Query Language (EJB-QL). The WebSphere Studio Application Developer v5 product provides a full suite of J2EE development wizards and templates to create DB2 applications quickly and efficiently. As you develop your J2EE application you can test it quickly with the built-in WebSphere Test Environment and then deploy the application to a production-ready WebSphere Application Server environment.

DB2 EJB sample
There is a DB2 UDB EJB-based sample provided with DB2 Version 8 on Linux, UNIX, and Windows. The sample application includes a Web client which accesses the DB2 sample database using a JSP/servlet interface to a Session/Entity EJB component. The sample application is contained in the AccessEmployee.ear file, which is available in the <DB2_root>\samples\java\Websphere\ directory on Windows platform and the <DB_instance_home>/sqllib/samples/java/Websphere directory on UNIX.

A full description of the EJB sample application and deployment information is provided in the README file located in the samples/Java directory.

Java UDFs and stored procedures

DB2 UDB provides a flexible Java infrastructure to link portions of your application within the DB2 server. Java modules can be converted into SQL extensions known as user-defined functions (UDFs). Another powerful option is to create database objects known as stored procedures using existing Java classes. Because Java UDFs and stored procedures involve DB2 server interaction with a Java Virtual Machine and external Java class files it is important to consider any platform differences across the DB2 family regarding the best practices for building and deploying these features. There is a distributed debugger for Java provided with DB2 UDB V8 to assist in debugging Java stored procedure logic. The DB2 Development Center can be used to help create and test Java stored procedures and UDFs on many platforms.

DB2 Java UDFs let you extend the functionality of the database server SQL language. After it is implemented, a Java UDF can be invoked by any SQL query. These Java SQL functions can return scalar values or tables (record sets). Table functions are powerful because it can make any data source appear like a DB2 table, which can be then used in normal SQL queries, join operations, grouping, and so on. For more information on creating Java based SQL functions consult the documentation for the CREATE FUNCTION SQL statement in the DB2 SQL reference.

Stored procedures are valuable for encapsulating application logic and database access on the server. A client application calls the stored procedure, which then performs the database accessing without returning unnecessary data across the network. Only the results the client application needs are returned by the stored procedure. Stored procedures offer these benefits:

Improved performance:
Grouping SQL statements together can reduce network traffic resulting in performance improvements. A typical application requires two trips across the network for each SQL statement. Grouping SQL statements into a stored procedure can reduce the number of network interactions to complete a transaction. Large result sets can be processed on the DB2 server within the stored procedure module to reduce the amount of data that must be processed within the client application.
Enforcement of business rules related to the data model:
Stored procedures can be used to define and reuse common business rules. When an application calls the stored procedure, it will process data in a consistent way according to the rules defined in the stored procedure. If you need to change the rules, you only need to make the change once in the stored procedure, not in every application that calls the stored procedure.

For more information on creating Java-based SQL procedures, consult the documentation for the CREATE PROCEDURE SQL statement in the DB2 SQL reference.

Figure 3a. Java client and commands with DB2

Figure 3b. Java client

Tools for developing Java applications

WebSphere Studio is an integrated development environment (IDE) that enables you to build, test, and deploy Java applications to WebSphere Application Server and DB2 Universal Database. The DB2 stored procedure and UDF builder component of WebSphere Studio provides wizards and tools for creating and working with Java stored procedures and user-defined functions (UDFs) for use with DB2 Universal Database. When you create an application that accesses DB2 data, you can improve your application's performance by incorporating stored procedures and UDFs that are registered with the database server.

The DB2 Development Center can be used, independently or in concert with WebSphere Studio, to create server-side Java stored procedures and UDFs for the UNIX, Windows, Linux, and z/OS platforms.

Summary

The DB2 UDB Universal Developer's Edition Version 8 product delivers all the tools you need to rapidly build and deploy applications. The package includes a full-function integrated development environment, a scalable Web application server, and DB2 UDB built-in XML features. DB2 Universal Database is a scalable, industrial-strength database that will be the data management foundation for your e-business.

Resources