In-Depth

Getting Started with Java and DB2 for OS/390: Using the Java Programming Language in Large-Scale Enterprise Databases

• 03/01/2000

With the availability of Java stored procedures (delivered via APAR to DB2 Version 5 and Version 6 in the first quarter of 2000) for DB2 for OS/390, the era of portable, vendor-neutral, database programming will really be with us. This article looks at how you can use the Java programming language with DB2 for OS/390.

Until very recently, writing applications that access databases had been a process of using an industry standard query language (SQL) surrounded by programming language statements and interface and definition statements. Moving these programs to other systems – to other databases – means, at best, a porting exercise, modifying programming language statements, updating definitions and so on. At worst, it means a complete rewrite of the programming statements and the definitions.

Java is changing that and truly revolutionizing the face of many businesses. The unique properties of Java-developed programs promise technology liberation and platform independence that allows programmers to concentrate on business, rather than the technology problems.

Java offers a simpler work environment where application interfaces are common across different platforms and different vendor products, not just across common systems. Java also gives programmers the ability to encapsulate business concepts and processes in individual components and groups or classes of components. This means they can build relatively complex applications from pre-constructed parts.

Of course, Java is more than just a cool technology. Recent surveys have shown that Java is being used in an ever-increasing number of business applications. A March 1999 study by GartnerGroup indicates that as much as 37 percent of all application programming in finance, banking and insurance businesses is being done with Java.

Interestingly, the next biggest exploiter of Java is in higher education. Logically, graduates in computer-related subjects will be more inclined to look for positions in which they already have skills – Java. The more Java programmers, the more Java programs. The more programs, the higher the demand for Java programmers. The higher the demand for Java programmers, the faster Java will become the de facto enterprise application language and platform.

Along with CICS and IMS, DB2 is an early exploiter of the OS/390 "Java Everywhere" strategy.

DB2 provides three complementary opportunities to exploit the Java programming language and to build and reuse Java components or Classes. The three ways you can use Java with DB2 are JDBC programs, SQLJ programs and stored procedures written in Java. Each of these program types has a different performance profile and different strengths, but all can exploit the portability and component benefits of Java. As you can see from the following examples, all three types of Java DB2 programs share common language, structure and semantics.

Please note that in addition to the DB2 Java exploitation described here, you can make extensive use of JDBC and SQLJ from client programs running on distributed systems. This article, however, only looks at the DB2 exploitation of Java from OS/390-based applications.

JDBC is a Java data interface standard that provides access to a wide range of relational databases. To deliver database-independent access in Java language, Java has included JDBC as a standard part of the Java platform. Virtually, all database vendors have adapted the JDBC specification into their database products, including DB2 for OS/390.

JDBC enables Java programs to create sessions to databases, execute SQL statements and retrieve the results. The JDBC specification delivers a call-level SQL interface for Java, based on the X/Open SQL call-level interface specification. To confine SQL syntax and semantic diversities across database platforms, JDBC also sets minimum SQL conformance to the SQL92 entry-level SQL standard. This gives wide portability for applications designed to run on many platforms.

The call-level interface limits operations to executing SQL statements and retrieving the results. A Java program can issue an SQL string to be processed by a database server at runtime. This mechanism also means dynamic compilation, plus privilege and authorization checks at runtime. It allows flexibility for the program to construct variable queries that are defined at runtime. This model is also known as the dynamic SQL model.

As illustrated in Example 1, JDBC-based Java programs are compiled into Java byte code using the Javac compiler part of the Java Development Toolkit. They can then be executed in the OS/390 Java Virtual Machine (JVM), another part of the standard Java toolkit.

To use DB2 JDBC, your program needs to include the java.sql package and invoke methods, according to the JDBC specification. On OS/390, the java.sql package is part of db2jdbcclasses.zip. The JDBC driver for DB2 for OS/390 has undergone a substantial revision to change it from a Type-1 driver, which is a bridge to ODBC calls, to a Type-2 driver, which gives substantial performance improvements to JDBC applications.

You can see how the JDBC calls are made to retrieve this piece of data from one row of DB2 table "EMP." The setString JDBC method binds the local Java variable "name" to the parameter marker "?" in the prepared statement. Once the statement has been executed using the executeQuery() method, the result set needs to be accessed, and the result then has to be placed into the local Java variable "addr," via yet another JDBC method invocation. Then, the result set must manually be closed.

SQLJ complements and extends JDBC. It enables quicker development cycles through a more concise syntax, and safer code through more extensive pre-execution checking. In the following figure, you can see that SQLJ is subject to an additional step, the SQLJ Translator. The translator generates standard Java code with JDBC calls. The SQLJ statements are generally just SQL embedded directly into the Java source code.

SQLJ was designed by a group of experts including team members from IBM, Oracle, Sun and Tandem. Like JDBC, SQLJ has been adopted by all the major database vendors. DB2 delivers SQLJ support that complies with the ANSI X.3.135 standard. DB2 SQLJ runs on top of DB2 JDBC drivers. It provides SQLJ Java classes, translator, runtime and customizing tools. The DB2 Java libraries are db2sqljclasses.zip and db2sqljruntime.zip.

Prior to Java compilation, SQLJ programs first have to be processed by the SQLJ Translator. The resulting Java program is then compiled into Java byte code for execution in the OS/390 JVM.

SQLJ contains static SQL statements; JDBC supports dynamic SQL statements. In a dynamic SQL model, an application may change any SQL statement during runtime, which offers flexibility. Note, however, that dynamic SQL statements require runtime compilation. This may catch some runtime errors. SQLJ, on the other hand, pre-compiles SQL statements before the program executes. It enforces strong type checking between Java data types and SQL data types. The pre-compiled static statements ensure that all SQL statements are verified against the database and will run. It reduces, therefore, total application maintenance costs.

SQLJ syntax also is more concise than JDBC. SQLJ can embed Java variables into SQL statements. JDBC requires separate statements to bind variables using their position or column name.

Developers often will find it more convenient to use SQLJ statements because bind processing provides data type and authorization checking. SQLJ separates the package owner from the package runner, providing better data manageability. JDBC programs can’t check user privileges until runtime.

Most applications require more static statements than dynamic statements. Yet, to exploit JDBC dynamic SQL features, you can combine SQLJ and JDBC inside one program. Since SQLJ includes JDBC support, the program can create a JDBC connection and use the connection for JDBC dynamic SQL statements. It also can use the same connection to set a connection context for SQLJ static SQL statements.

To summarize, some of the major differences between SQLJ and JDBC are:

• SQLJ follows the static SQL model, and JDBC follows the dynamic SQL model.

• SQLJ source programs are smaller than equivalent JDBC programs, because certain code that the programmer must include in JDBC programs is generated automatically by SQLJ.

• SQLJ does data type checking during the program preparation process and enforces strong typing between table columns and Java host expressions. JDBC passes values to and from SQL tables without compile-time data type checking.

As illustrated in Example 2, to retrieve the data from one row of the table takes only one statement in SQLJ. The local Java variables "addr" and "name" are bound to the statement during program preparation time, instead of with explicit method calls, as was the case in the JDBC example.

There are some additional steps required to prepare an SQLJ program. The first is to run the SQLJ translator, which is done using the sqlj command against the source program. This produces a Java source code file. The next step is to compile the Java program using the Javac command, which produces one or more class files containing the Java byte codes. The final step is the customization step, which creates what is used to bind the program to DB2 and do the data type and authorization checking. This is done using the db2profc command.

In SQLJ programs, you can embed Java host expressions in SQL statements. JDBC requires a separate call statement for each bind variable and specifies the binding by position number.

SQLJ provides the advantages of static SQL authorization checking. With SQLJ, the authorization ID under which SQL statements execute is the plan or package owner. DB2 checks table privileges at bind time. Because JDBC uses dynamic SQL, the authorization ID under which SQL statements execute is not known until runtime. That means no authorization checking of table privileges can occur until runtime.

A stored procedure is a compiled program – stored at a DB2 local or remote server – that can execute SQL statements. A typical stored procedure contains two or more SQL statements and some manipulative or logical processing in a host language. A client application program uses the SQL CALL statement to invoke the stored procedure.

Typical client programs on OS/390 can be SQLJ- and JDBC-based Java programs, WebSphere Application Server servlets and Java Server pages. Stored procedures also can be executed from distributed clients, as well as traditional OS/390 Transaction systems, like IMS and CICS.

Consider using stored procedures for a client/server application that needs the execution of many SQL statements or access to DB2 tables for which you want to guarantee security and integrity.

DB2 stored procedures are integrated with and exploit some of the key OS/390 scalability and availability features, guaranteeing highly available, high-volume database transactions. Stored procedures benefit from OS/390 Workload Manager address spaces, allowing individual stored procedures to be scheduled according to their business priority and optimized for the system workload. Multiple WLM-controlled address spaces for DB2 stored procedures also provide improved program isolation.

In addition, stored procedures can access non-SQL resources based on the caller’s RACF capabilities, which can improve security. The Recoverable Resource Manager Services attachment facility also can use OS/390 Transaction Management and Recoverable Resource Manager Services to synchronize two-phase commit processing between DB2 and non-SQL resources that are accessed by a stored procedure.

A stored procedure program is a Java program that can contain SQLJ, JDBC, or both. The Java method that is invoked as a stored procedure program must be a public static method with a void return type, and its parameters must be mappable to base SQL data types. Parameters sent to a Java stored procedure program are declared using the default JDBC mapping of SQL data types to Java types. In addition, output and input/output parameters are sent to the Java program as arrays of the Java type, so that the Java program can set them for return to the calling application.

To prepare a Java stored procedure program, the same steps must be taken in the preparation of a JDBC or SQLJ program, that is, if it contains SQLJ, the SQLJ translator must be invoked, and the result must be compiled into Java byte code and also bound to DB2 for OS/390. However, unlike JDBC- and SQLJ-based applications, stored procedures written in Java must additionally be compiled into OS/390 native executables using the VisualAge for Java Enterprise Toolkit for OS/390 byte code binder. This is because started task address spaces, like the WLM-established stored procedures address spaces, do not currently support attachment of the JVM. Compilation into native OS/390 instructions is done using the VisualAge for Java Enterprise Toolkit for OS/390 (ET/390).

ET/390 native compilation provides a number of benefits to Java programs running on OS/390, including improved performance over execution using the current OS/390 JVM. These benefits also will be available to stored procedures written in Java.

Example 3 shows a partial CREATE PROCEDURE syntax to define the stored procedure to DB2 for OS/390. The parameters declared in the catalog map directly to the Java method signature parameters, except for the JDBC result set parameter. In accordance with the SQLJ Part-1 specification, the database system is responsible for processing result sets in the Java method signature. Parameters that are input only to the Java stored procedure are represented as their corresponding Java types and the output or input/output parameters are represented as a single-element array of the Java type. This allows the Java stored procedure program to modify the parameter and have it returned to the calling application.

Now, take a closer look at the Java program itself. For the purpose of this example, the stored procedure in Example 4 (on page 38) looks like an ordinary JDBC program, with a few omissions. First, the URL that is used to obtain the JDBC connection does not have the location name portion at the end. This tells the JDBC driver to go to the currently connected database subsystem. In the stored procedures environment, this will be the subsystem where the SQL CALL statement to invoke this program is being processed.

Next, notice that the Java method does not process the result set that is returned from the executeQuery. Instead, it sets the ResultSet parameter array element to this result set object. The calling application will process this as a result set, in whatever language and protocol the calling application is written in, JDBC, ODBC or DB2 for OS/390 SQL to access result sets.

Finally, unlike a JDBC program, in a stored procedure we do not close the statement or the connection on completion. This is because we want to leave the result set open for the calling application. Once the result set has been processed, DB2 for OS/390 will cleanup the objects.

The stored procedure program can also be coded using SQLJ syntax rather than the more verbose JDBC syntax, and an SQLJ Iterator class returned from the Java method, rather than the JDBC result set. DB2 for OS/390 will correlate the parameters declared in the stored procedure definition with the leading parameters in the Java method signature, and then look for a matching method that has any combination of JDBC result set and/or SQLJ iterator array parameters, up to the maximum number of result sets specified on the procedure definition.

As you can see, core Java enabling technologies are available for use with DB2 to allow applications to exploit industry standard application programming interfaces. JDBC and SQLJ can help produce portable, component-based applications that are tightly integrated with DB2.

This is just one facet of the OS/390 "Java Everywhere" strategy, which also includes:

• 100 percent Pure Java Compatible virtual machine and development toolkit

• The ability to compile into native OS/390 executable instructions so that Java can be exploited from environments where the current JVM cannot be used

• Tracing and debugging, and performance profiling provided by VisualAge for Java Enterprise Toolkit for OS/390

• The possibility to write transactions in the CICS Transaction Server and IMS Transaction Server in Java

• Directory Server exploitation of the OS/390 LDAP Server

• Security Server exploitation

• The ability to access OS/390 native datatypes, such as sequential, partitioned and VSAM datasets

The second facet of the "Java Everywhere" strategy is fully enabling the WebSphere Application Server on OS/390, to provide Java-based servlet and Java Server page exploitation, using the scalable and extensible OS/390 HTTP server as its host.

In the future, WebSphere Application Server for OS/390 Enterprise Edition will leveraged by advanced, scalable and highly reliable Enterprise JavaBean Containers provided by:

• CICS Transaction Server for simpler, direct integration with new and existing CICS transactions

• Component-based applications (through Component Broker for OS/390) that need to coordinate with multiple back-end OS/390 applications and data

Finally, OS/390 will provide an implementation of the Java 2 Enterprise Edition, adding support for messaging (JMS) and transaction services (JTA and JTS), as well as JavaMail.

With the OS/390 "Java Everywhere" strategy, future application development will become much simpler, more standardized and able to share common languages and components across application environments. Even if you are not ready to make a big step and start writing and rewriting core applications and stored procedures in Java, there is no better time to make a start in this exciting new environment than by using SQLJ to access and process data in DB2 for OS/390 databases.

Editor’s Note:More information about DB2 is available at www.ibm. com/software/data/db2; for SQLJ, visit www.sqlj.org; for Java for OS/390, visit www.ibm.com/s390/ java; and for Java for OS/390 strategy, visit www.ibm.com/ s390/corner/ j390status.pdf.

About the Authors: Peggy Abelite is the Lead Developer at the IBM Santa Teresa Lab (San Jose, Calif.).

Mark Cathcart is a Technical Strategist for IBM (United Kingdom), specializing in Java, XML, Linux and the Open Source world. For more information, visit www.ibm.com/s390/corner.