08
Tue, Oct
2 New Articles

Putting Java Inside Your Database

Java
Typography
  • Smaller Small Medium Big Bigger
  • Default Helvetica Segoe Georgia Times

If your business is like most, your vital business data is stored in a relational database. Since that information is in a database, you can use SQL to analyze and manipulate it. And because your database is on a server system, you can use your workstation to access the data, using the client/server paradigm. At some point, however, you may discover that SQL can’t do everything. You then want to create programs to analyze your data, but you don’t want to tie those programs to any particular architecture. Most importantly, you want to be able to get at the information from your workstation.

One approach is to write a Java program that runs on your workstation and uses Java Database Connectivity (JDBC) or ODBC to access the data in your database. However, if that program accesses a large amount of data, the transfer of data from server to client may slow the program’s performance. This forces you to consider another option: putting your program on the server. But if it is a stand-alone program, you have to figure out how to get the results back to the workstation. Fortunately, most databases have a solution to this problem. That solution is stored procedures, which are programs that you can call using the SQL CALL statement. Using a stored procedure puts your code inside the database system.

When writing a stored procedure, a variety of languages can be used. However, if you are concerned with portability, you will probably end up using one of two languages: SQL or Java. If the program you are creating is fairly simple, you can write the function using SQL and then define an SQL stored procedure. However, there are times when the function is more complex, and writing it in SQL is either impossible or requires an SQL genius. In this case, you probably want to write in a procedural language. And, if you want something that is portable, you want to use Java.

Support for Java stored procedures was added to DB2 UDB for AS/400 in V4R5. This article presents examples of how stored procedures can be created, deployed, and used on the AS/400. Since Java is used as the implementation language, these stored procedures can also be used on other platforms.

Java Stored Procedures for DB2

When running a Java stored procedure, the database engine must create a Java Virtual Machine, or JVM, to run the Java program. On the AS/400, the database engine creates a


JVM that uses the latest version of Java installed on the system. Before the database can run a Java program, it must first be able to find the program. With the AS/400, all user- defined Java programs must be stored in the AS/400 Integrated File System (AS/400 IFS), in the /QIBM/UserData/OS400/SQLLib/Function directory.

Interfacing Between Java and the Database

When a stored procedure is called, the database must convert the type of the parameters from a database type to a type understood by the language being used. When the language used is Java, the database must determine which method should be called for the stored procedure. When writing stored procedures for Java on DB2 UDB, the parameter style determines how parameters are passed and how a Java method is structured. Two parameters styles are provided by DB2: DB2General and Java.

The DB2General parameter style was introduced with DB2 UDB, so it is available only on DB2 platforms. In this parameter style, a Java stored procedure is an instance method of a Java class. This class must extend the com.ibm.db. app.StoredProc class supplied by DB2. In this parameter style, parameters are passed (and converted to) Java types. These types are either a Java class or a Java primitive type. Figure 1 shows the mappings for some common SQL types. (The complete set of mappings can be found in the DB2 UDB for AS/400 SQL Reference manual.) The IN, INOUT, and OUT parameters are all passed to the Java method in the same manner. However, the Java method must explicitly set the output parameters, using Set methods provided by the com.ibm.db.app. StoredProc class.

If you are used to programming in Java or C++, using a Set method to set the return value may seem awkward. A more natural approach is provided by the Java parameter style. The Java parameter style is the parameter style specified by the SQLj: SQL Routines standard (latest draft is available at ftp://ftp.calweb.com/business/ sqlj/Pt1—99-
284.pdf). In this parameter style, a Java stored procedure is a “static” method. Like the DB2General parameter style, database types are converted to Java types. Unlike the DB2General parameter style, INOUT and OUT parameters are passed as one-element Java arrays. Setting the first element of the array sets an output parameter. Because the SQLj standard supports this parameter style, it is available on several database systems.

A Stored Procedure Example

Although SQL has some functions that provide statistical information, many functions are missing. This missing functionality may be found by using Java stored procedures. As an example, I will implement a stored procedure that calculates the geometric mean of the column of a table. My stored procedure example is designed to be flexible enough to work with a variety of numeric types as well as with data from a variety of tables. (Note that, although the example is mathematical, stored procedures are commonly used to optimize business database queries.) Any frequently used complex query should be optimized to a stored procedure. Using Java stored procedures to implement your complex business logic will permit you to run that logic on a variety of database platforms.

A geometric mean for n values is calculated by multiplying the values together and then taking the nth root of the product. In its simplest form, this algorithm is impractical because multiplying many values together soon produces a number that is so large that it cannot be represented by a 64-bit double. However, it is possible to iteratively calculate the mean.

Figure 2 (page 89) presents a Java method for calculating the geometric mean of a column of numbers. This method, called calculate, is a static method of the com.xxx.GeometricMean class. It is flexible in that it uses an SQL query to select the column from which the values are to be calculated. The method first executes the SQL query using a statement created from a JDBC connection. It then loops through each value


returned by the query and calculates the current geometric mean, using the previous geometric mean as input.

I then want to use this method within a stored procedure. Figure 3 (page 89) shows an example of a method used as a Java stored procedure that uses the DB2General parameter style. Because the DB2General parameter style is used, the GMeanDB2 class extends the com.ibm.db2.app.StoredProc class. The method, gmean, has two parameters. The first parameter, Answer, is an output parameter and is used to return (Java) double. The second parameter, query, is a String, which contains the SQL statement that selects the column of values. This procedure first uses the getConnection method of the StoredProc class to get the JDBC connection to the current database. It then passes this connection, as well as the query, to the GeometricMean.calculate method. This method returns a double, which is used by the Set method to return the value to the database.

Figure 4 (page 89) contains an example of a method to be used as Java stored procedure, which uses the Java parameter style. In this example, the GMeanJava class is merely a container. It does not extend another class. The method, gmean, is a static method. The output parameter is passed as a single-element array of double. As in the DB2General parameter style, the input parameter is passed as a String. This procedure obtains a connection to the database by using the URL jdbc.default.connection, as specified by the SQLj standard. It passes the connection and the query to GeometricMean.calculate, which returns the value. The return value is then returned to the database by setting it as the value of the first element in the array.

Compiling and Placing Code for Java Stored Procedures

Once the stored procedures are written, they need to be compiled to Java byte code. Then they need to placed on the system on which they will be run, in a location where the database can access them.

Since a class containing a Java stored procedure that uses the DB2General parameter style extends the StoredProc class, the StoredProc class must be available when the stored procedure class is compiled. On the AS/400, the StoredProc class resides in the db2routines_classes.jar file, which is found in the /QIBM/ProdData /Java400/ext directory. To compile my Java program, I first copied the db2routines_classes.jar file to my Microsoft Windows NT workstation. I then added the JAR file to my CLASSPATH and compiled using the following commands: Compiling the class for the Java parameter style, I just use the Java compiler: Then these files must be placed on the AS/400. Since they are part of the com.xxx packages, the class files must be copied to the /QIBM/UserData/OS400/SQLLib/Function/ com/xxx directory. In my case, I used FTP (in binary mode) to move the files to the AS/400.

Registering the Stored Procedure with the Database

Before you are able to use a Java method as a stored procedure, you must create a stored procedure. The Create Procedure SQL statement creates a stored procedure and allows you to associate that stored procedure with an external program. When creating a Java stored procedure, there are three phrases that must be used. First, a Language Java phrase must be used to indicate Java. Then a parameter style phrase must be used. This phrase is either parameter style Java or parameter style DB2General. Finally, the external name phrase is


set CLASSPATH=db2routines_classes.jar;%CLASSPATH%
javac comxxxGMeanDB2.java

javac comxxxGMeanJava.java

used to specify the Java method to be called. The external name phrase includes the full name of the class, followed by the method name. Figure 5 (page 89) shows the SQL statements used to create stored procedures that use both parameter style Java and parameter style DB2General.

Running the Stored Procedure

Now that the stored procedure is created, how do you run it? Given that the stored procedure calculates a mean, it needs to have some data to operate on. Using the SQL statements in Figure 6 (page 89), I create the table numbers and populate the table with data. You may notice that integer values are stored in the table. Because the stored procedure uses JDBC to read the data, the JDBC call getDouble(), within the stored procedure, automatically converts the SQL INTEGER into a Java double.

One way to run the stored procedure is to create a client program that calls the stored procedure and obtains the return value. A JDBC client program to call the stored procedure is shown in Figure 7. This program uses the AS/400 Toolbox for Java JDBC driver to connect to the AS/400. You execute Client by qualifying the IP address (or host name), profile, password, and the name of the file, as follows: The output of the program is shown below.

For more on how to debug your Java stored procedures, read the Web sidebar to this article, “Debugging Tips for Java Stored Procedures.”

The use of Java stored procedures permits Java code to run inside your database. This is especially useful if you want to write business logic that is portable to a wide range of database systems. This article has only briefly covered Java stored procedures and their use on the AS/400. More information about Java stored procedures is available in the AS/400 DB2 manuals and in the Redbook DB2 UDB for AS/400 Advanced Database Functions.

REFERENCES AND RELATED MATERIALS

• DB2 UDB for AS/400 Advanced Database Functions, Redbook (SG24-4249-02)

• DB2 UDB for AS/400 SQL Programming Concepts: http://publib.boulder.ibm.com/pubs/html/as400/v4r5/ic2924/info/db2/rbafymst02.htm

• DB2 UDB for AS/400 SQL Reference: http://publib.boulder.ibm.com/pubs/html/ as400/v4r5/ic2924/info/db2/rbafzmst02.htm

• SQLj.org Web site: www.sqlj.org

java Client hostName profile password numbers

The answer using gmeandb is 3.936283427035352
The answer using gmeanjava is 3.936283427035352


Debugging Tips for Java Stored Procedures

One of the difficulties associated with using Java stored procedures is the problem of debugging a stored procedure when something goes wrong. There are two different options I use when debugging a stored procedure: manual tracing and the AS/400 debugger.

One way to debug a Java stored procedure is to manually add tracing code to your stored procedure. Within a stored procedure, you can open a file and then write trace information to that file using typical Java file output methods. One thing to remember is that, within a Java stored procedure, you cannot write to System.out or Syster.err. Doing so may cause the job to fail with an MCH3402 exception.

Another option for debugging a stored procedure is to use the AS/400 debugger. To do this, you first need to compile your code in debuggable mode using the -g option. Second, you need to move your Java source file, as well as your Java class file, to the /QIBM/UserData/OS400/SQLLib/Function directory on the AS/400. Third, you need to figure out what job on the AS/400 is running the stored procedure. If you are using the toolbox JDBC driver or Operations Navigator, the job will be a QZDASOINIT job in the QSERVER subsystem. A tedious way to do this is to look at all the QZDASOINIT jobs on the system and find the job with the user profile used to connect to the system.

A simpler way to determine the job is to write a small CL program that is called each time someone connects to a QZDASOINIT job. Figure A shows a small CL program that sends a message to my message queue (EBERHARD) whenever I connect to the AS/400. The message indicates which QZDASOINIT job is being used. This program is registered as an exit program using the following command:

ADDEXITPGM EXITPNT(QIBM_QZDA_INIT) FORMAT(ZDAI0100) PGMNBR(1) PGM(ZDAEXIT)

The exit program can later be removed using the following command:

RMVEXITPGM EXITPNT(QIBM_QZDA_INIT) FORMAT(ZDAI0100) PGMNBR(1)

Once the job is found, I start a service job on the AS/400 using the Start Server Job (STRSRVJOB) command. After that, I can start the debugger using the Start Debugger (STRDBG) command. Before I can add a Java program to the debugger, the JVM must be started. I usually do this by letting my Java stored procedure run once. Once it has run, I am able to add my Java program to the debugger using the Display Module Source (DSPMODSRC) command. From there, I can debug the program like any other AS/400 Java program.

—John Eberhard

PGM PARM(&OKAY &REQUEST)

dcl &Okay *char 1

dcl &Request *char 34

dcl &User *char 10

dcl &JOB *CHAR 10

dcl &JOBUSER *CHAR 10

dcl &JOBNBR *CHAR 6

chgvar &User %sst(&Request 1 10)

chgvar &Okay ‘1’

IF COND(&USER *NE ‘EBERHARD ‘) THEN(GOTO +

CMDLBL(NOCHG))

SNDPGMMSG MSG(‘SENDING MESSAGE’)

RTVJOBA JOB(&JOB) USER(&JOBUSER) NBR(&JOBNBR)

SNDMSG MSG(‘THE JOB IS ‘ *CAT &JOBNBR *CAT ‘/’ *CAT +

&JOBUSER *CAT ‘/’ *CAT &JOB) TOUSR(&USER)

NOCHG: ENDPGM

Figure A: This CL program notifies a user that a connection has been accepted.


SQL Data Type Java Data Type

INTEGER int DOUBLE float CHARACTER String DECIMAL BigDecimal

Figure 1: SQL datatypes have a corresponding Java class or primitive type.

package com.xxx;

import java.sql.*;

public class GeometricMean {

public static double calculate(Connection con, String query)

throws java.sql.SQLException {

double answer= 1.0;

double count = 0.0;

Statement stmt = con.createStatement();

ResultSet rs = stmt.executeQuery (query);

while (rs.next()) {
double value = rs.getDouble(1);
if (value != 0.0) {

count += 1.0;

answer=Math.pow(answer,(count-1)/count)*

Math.pow(value,1.0/count); }

}

rs.close();

stmt.close();

return answer;

}

}

Figure 2: This Java program calculates the geometric mean of a table’s column.

package com.xxx;
import java.sql.*;
import com.ibm.db2.app.StoredProc;
// Example of parameter style db2general stored procedure
public class GMeanDB2 extends StoredProc {

public void gmean(double answer, String query) throws Exception {

Connection con = getConnection();

set(1,GeometricMean.calculate(con, query));

}

public void gmean2(String resultTable, String query) throws Exception {

Connection con = getConnection();

double answer = GeometricMean.calculate(con, query);

Statement stmt = con.createStatement ();

stmt.executeUpdate("create table "+resultTable+"(mean double)");

stmt.executeUpdate("insert into "+resultTable+" values("+answer+")");

stmt.close();

}

}

Figure 3: A Java stored procedure can use the DB2General parameter style.


package com.xxx;
import java.sql.*;
// Example of parameter style java stored procedure
public class GMeanJava { public static void gmean(double[] answer, String query) throws Exception {

Connection con = DriverManager.getConnection(“jdbc:default:connection”);

answer[0]=GeometricMean.calculate(con, query);

}

public static void gmean2(String resultTable, String query) throws Exception {

Connection con = DriverManager.getConnection(“jdbc:default:connection”);

double answer = GeometricMean.calculate(con, query);

Statement stmt = con.createStatement ();

stmt.executeUpdate(“create table “+resultTable+”(mean double)”);

stmt.executeUpdate(“insert into “+resultTable+” values(“+answer+”)”);

stmt.close();

}

}

Figure 4: A Java stored procedure can use Java parameter style.

create procedure gmeanjava(inout double, in VARCHAR(30))

language java parameter style java

external name 'com.xxx.GMeanJava.gmean';

create procedure gmeanjava2(in VARCHAR(30), in VARCHAR(30))

language java parameter style java

external name 'com.xxx.GMeanJava.gmean2';

create procedure gmeandb(inout double, in VARCHAR(30))

language java parameter style db2general

external name 'com.xxx.GMeanDB2.gmean';

create procedure gmeandb2(in VARCHAR(30), in VARCHAR(30))

language java parameter style db2general

external name 'com.xxx.GMeanDB2.gmean2';

Figure 5: The SQL Create Procedure statements can qualify a Java class.

create table numbers (number integer)
insert into numbers values(1)
insert into numbers values(3)
insert into numbers values(5)
insert into numbers values(7)
insert into numbers values(9)

Figure 6: SQL insert statements can be used to populate a table.

import java.sql.*;

public class Client {

public static void main(String args[]) throws Exception {

Class.forName(“com.ibm.as400.access.AS400JDBCDriver”);
String url = “jdbc:as400://”+args[0];
Connection con = DriverManager.getConnection (url, args[1], args[2]);
CallableStatement cstmt;

cstmt = con.prepareCall(“call gmeandb(?, ?)”);

cstmt.setDouble(1, 0.0);

cstmt.setString(2, args[3]);

cstmt.registerOutParameter(1,java.sql.Types.DOUBLE);

cstmt.execute();

System.out.println(“The answer using gmeandb is “+cstmt.getDouble(1));

cstmt = con.prepareCall(“call gmeanjava(?, ?)”);

cstmt.setDouble(1, 0.0);

cstmt.setString(2, args[3]);

cstmt.registerOutParameter(1,java.sql.Types.DOUBLE);

cstmt.execute();

System.out.println(“The answer using gmeanjava is “+cstmt.getDouble(1));

}

}

Figure 7: JDBC clients can easily invoke a stored procedure.


BLOG COMMENTS POWERED BY DISQUS

LATEST COMMENTS

Support MC Press Online

$

Book Reviews

Resource Center

  • SB Profound WC 5536 Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application. You can find Part 1 here. In Part 2 of our free Node.js Webinar Series, Brian May teaches you the different tooling options available for writing code, debugging, and using Git for version control. Brian will briefly discuss the different tools available, and demonstrate his preferred setup for Node development on IBM i or any platform. Attend this webinar to learn:

  • SB Profound WP 5539More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.

  • SB HelpSystems ROBOT Generic IBM announced the E1080 servers using the latest Power10 processor in September 2021. The most powerful processor from IBM to date, Power10 is designed to handle the demands of doing business in today’s high-tech atmosphere, including running cloud applications, supporting big data, and managing AI workloads. But what does Power10 mean for your data center? In this recorded webinar, IBMers Dan Sundt and Dylan Boday join IBM Power Champion Tom Huntington for a discussion on why Power10 technology is the right strategic investment if you run IBM i, AIX, or Linux. In this action-packed hour, Tom will share trends from the IBM i and AIX user communities while Dan and Dylan dive into the tech specs for key hardware, including:

  • Magic MarkTRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now!  Request Now.

  • SB HelpSystems ROBOT GenericForms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.

  • SB HelpSystems ROBOT GenericIT security is a top priority for businesses around the world, but most IBM i pros don’t know where to begin—and most cybersecurity experts don’t know IBM i. In this session, Robin Tatam explores the business impact of lax IBM i security, the top vulnerabilities putting IBM i at risk, and the steps you can take to protect your organization. If you’re looking to avoid unexpected downtime or corrupted data, you don’t want to miss this session.

  • SB HelpSystems ROBOT GenericCan you trust all of your users all of the time? A typical end user receives 16 malicious emails each month, but only 17 percent of these phishing campaigns are reported to IT. Once an attack is underway, most organizations won’t discover the breach until six months later. A staggering amount of damage can occur in that time. Despite these risks, 93 percent of organizations are leaving their IBM i systems vulnerable to cybercrime. In this on-demand webinar, IBM i security experts Robin Tatam and Sandi Moore will reveal:

  • FORTRA Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:

  • FORTRAManaging messages on your IBM i can be more than a full-time job if you have to do it manually. Messages need a response and resources must be monitored—often over multiple systems and across platforms. How can you be sure you won’t miss important system events? Automate your message center with the Robot Message Management Solution. Key features include:

  • FORTRAThe thought of printing, distributing, and storing iSeries reports manually may reduce you to tears. Paper and labor costs associated with report generation can spiral out of control. Mountains of paper threaten to swamp your files. Robot automates report bursting, distribution, bundling, and archiving, and offers secure, selective online report viewing. Manage your reports with the Robot Report Management Solution. Key features include:

  • FORTRAFor over 30 years, Robot has been a leader in systems management for IBM i. With batch job creation and scheduling at its core, the Robot Job Scheduling Solution reduces the opportunity for human error and helps you maintain service levels, automating even the biggest, most complex runbooks. Manage your job schedule with the Robot Job Scheduling Solution. Key features include:

  • LANSA Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.

  • LANSAWhen it comes to creating your business applications, there are hundreds of coding platforms and programming languages to choose from. These options range from very complex traditional programming languages to Low-Code platforms where sometimes no traditional coding experience is needed. Download our whitepaper, The Power of Writing Code in a Low-Code Solution, and:

  • LANSASupply Chain is becoming increasingly complex and unpredictable. From raw materials for manufacturing to food supply chains, the journey from source to production to delivery to consumers is marred with inefficiencies, manual processes, shortages, recalls, counterfeits, and scandals. In this webinar, we discuss how:

  • The MC Resource Centers bring you the widest selection of white papers, trial software, and on-demand webcasts for you to choose from. >> Review the list of White Papers, Trial Software or On-Demand Webcast at the MC Press Resource Center. >> Add the items to yru Cart and complet he checkout process and submit

  • Profound Logic Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application.

  • SB Profound WC 5536Join us for this hour-long webcast that will explore:

  • Fortra IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn: