Learn how to write DB2 JDBC tools with Jython

Brief introduction

The Db2®universal database™ (UDB) JDBC driver has proven to be a powerful and IBM-supported technology. Jython is a pure Java implementation of the Python programming language.

First, we'll tell you why Jython is powerful, easy to use, and fun to write DB2 JDBC tools. We will then show you how to install the Jython with the DB2 JDBC driver. Finally, we'll demonstrate several query examples and a QA tool that uses bulk inserts in Jython.

Why use Jython?

Here is a true story of Neo. Neo is a full-time server-side Java developer. He uses Python to write utilities, such as parsing XML or connecting to a WEB service that acts as a credit card gateway and an order fulfillment gateway through an HTTP protocol. Because Python's types are dynamic and have concise data structure syntax such as lists and dictionaries, Neo finds that it usually reduces lines of code.

Neo is working on a stock-trading application built with WebSphere application Server 5.x and DB2 8.x deployed on AIX. In a real-time system, 10,000 XML-formatted records are entered into the table STOCKQUOTE every few minutes to update the stock quotes. The code that executes the bulk input is completed by another team as part of another system, so he cannot get the source code. In addition, there is a trigger on the STOCKQUOTE that triggers an alert if the stock price falls to a predefined threshold such as $. The project manager assigned Neo the task of using automated load testing tools such as Mercury Interactive's LoadRunner or segue silkperformermeasure to measure the performance impact of triggers in the case of batch input. Neo, therefore, needs to write a batch-input emulator (simulator) to simulate loading 10,000 of rows of records into the STOCKQUOTE table.

At first, Neo wanted to write the emulator with pure Java JDBC, but the lengthy type declaration, edit/compile/test cycle would take up a lot of valuable development time. And because Python's efficiency gains have impressed Neo in the past, he wondered if he could write code in Python to use the DB2 JDBC driver. If it works, he can write this code in a matter of hours, using data structures like Python's neat lists and dictionaries, and neat functional programming features such as List comprehension. After doing some research, he found the Jython very convenient and suitable for the job. Because of the familiarity of DB2 JDBC programming and the Jython reference, Neo wrote and tested the bulk INSERT emulator within a few hours. He was amazed at the powerful features of Jython, which included two of the best areas of Jython: a huge Java library and neat Python features. As a result, the final code for the batch input emulator is much less than that written with pure Java JDBC. The total amount of source code has a significant impact because it reduces the details that programmers need to deal with when programming. Therefore, reducing the amount of source code will reduce the complexity of the software, thereby saving costs.

Jython JDBC Installation

Install Jython 2.1

In order to run the examples in this article, you need to install Jython 2.1 and the JVM. You can get Jython from the Jython home page. Installation is convenient if you add the Jython installation path to the PATH environment variable on the platform.

The charming Jython also contains chapters for installing Jython.

Enable the SAMPLE database in DB2 UDB V8

The example shown in this article uses the sample database. You can create a SAMPLE database either by running the Db2sampl program that is published with DB2, or from firststeps in the DB2 Setup Tools menu. In addition, you must create a DB2 id/password. In this article, we use Vyang/jythonrocks.