Learn how to write DB2 JDBC tools in Jython

Do you want to cut development and maintenance costs for tools written in DB2 UDB JDBC? In this article Victor Yang explains how Jython can help.

Victor Yang (jythonrocks@yahoo.com), Software developer, IBM, Software Group

Victor Yongwei Yang is working for IBM's Toronto Lab as a consultant. He holds a MSCS from Georgia Tech. Since 1994 he has worked for numerous companies including IBM, Sun Microsystems, SunLife, CIBC, S1 and AT&T. He is a Sun certified programmer and web component developer. He can be reached at jythonrocks@yahoo.com.



01 April 2004

Introduction

DB2® Universal Database™ (UDB) JDBC driver has been a solid and proven technology supported by IBM. Jython is the 100% pure Java implementation of Python programming language (see resources).

First we will tell you why Jython is powerful, handy and fun in writing DB2 JDBC tools. Then we will show you how to set up Jython with DB2 JDBC driver. After that we will demonstrate several examples for querying and a QA tool using batch inserting in Jython.


Why Jython?

Here is a real story of Neo. Neo is a full-time server-side Java™ developer. He uses Python to write utility programs such as parsing XML or pinging web service over HTTP such as credit card gateway and order fulfillment gateway. Since Python is dynamic typed and has concise syntax for data structures such as list and dictionary, Neo found it usually results in fewer lines of code.

Neo was working on a stock trading application built with WebSphere® Application Server V5.x and DB2 V8.x deployed on AIX®. And on the live system there is a bulk feed of 10,000 rows in XML into a table STOCKQUOTE to update stock quotes every few minutes. The code for bulk feed is done by another team as part of another system and source code is not available to him. Additionally there is a trigger on STOCKQUOTE that will send alerts once the price of a stock drops below a predefined threshold such as $10. Neo was given the task by the project manager to measure the performance impact of the trigger under bulk feed with automated load testing tools. Hence Neo wanted to write a bulk feed simulator to simulate the load of 10,000 rows into STOCKQUOTE table.

At first Neo wanted to write it in pure Java JDBC but the tedious type declaration, edit/compile/test cycle would have taken quite a chunk of his precious development time. However as Neo has been impressed by Python's productivity gain before, he wondered if he could write code in Python to use DB2 JDBC driver. If it was possible he could write it within a few hours with Python's neat data structures such as list and dictionary, neat functional programming features such as list comprehension. After he did some research, he found Jython was handy and right for the job. Being pretty familiar with DB2 JDBC programming and keeping a Jython reference at hand, Neo wrote and tested the bulk insert simulator within a few hours. He was amazed at the power of Jython which brings the best of two worlds, huge Java libraries and neat Python features. As a result the final code of bulk feed simulator has a lot less lines of source code than if it is written in pure Java JDBC. The amount of source code matters since it can reduce the amount of detail a programmer needs to handle in his/her mind when he/she is programming. Therefore the complexity of software has been reduced and this is where cost-saving kicks in.


Jython JDBC Setup

Install Jython 2.1

To run the examples in this article, you need to have both Jython 2.1 and a JVM installed. You can get Jython from the Jython home page (see resources). And it is convenient if you add the Jython installation path to your PATH environment variable on your platform.

Charming Jython (see resources) also has a section on Jython installation.

Enable sample database in DB2 UDB V8

The examples shown in this article use the SAMPLE database. SAMPLE database can be created by either running db2sampl program shipped with DB2 or from the FirstSteps of DB2 Set-up Tools Menu. And a DB2 id/password has to be created. In this article, we use vyang/jythonrocks.

How to connect to the SAMPLE database in Jython

Here we will show how to connect to the SAMPLE database in Jython with the popular type 2 and type 4 drivers. For a detailed overview of all JDBC drivers, please see resources.

Type 2 JDBC Driver(The CLI JDBC driver)
 from java.lang import * 
 from java.sql import * 
 Class.forName("COM.ibm.db2.jdbc.app.DB2Driver").newInstance() 
 con = DriverManager.getConnection( 'jdbc:db2:sample','vyang','jythonrocks')
Type 4 JDBC Driver (new in DB2 UDB V8)
from java.lang import * 
from java.sql import * 
Class.forName("com.ibm.db2.jcc.DB2Driver").newInstance()
con = DriverManager.getConnection( 'jdbc:db2://db2host:50000/sample','vyang','jythonrocks')

The above code to connect to the SAMPLE database looks very similar to JDBC java code except:

  • Jython needs to import every Java package including java.lang
  • Jython string can use either single or double quotes.
  • Jython is dynamic typed thus no need to declare the type for con as java.sql.Connection

Query Examples

Now we know how to setup Jython and connect to DB2. It is time for us to rock'n roll with examples. The following examples use the SAMPLE database and type 2 CLI JDBC driver on Windows 2000. You can download the examples from the download section at the bottom of this page.

A simple dynamic query (select1.jy)

select1.jy is to select all employees from the EMPLOYEE table (from sample database) whose salary is over $20,000 in an ascending order.

Run select1.jy from a cmd console "jython select1.jy".

Figure 1. output of "jython select1.jy"
select1.jpg

Here is the Jython code.

Listing 1. select1.jy
from java.lang import *
from java.sql import *

# load DB2 JDBC type 2 driver (app driver)
Class.forName("COM.ibm.db2.jdbc.app.DB2Driver").newInstance()
con = DriverManager.getConnection( 'jdbc:db2:sample','vyang','jythonrocks')

stmt = con.createStatement()

sql='select firstname,lastname,salary from employee where salary > 20000 order by salary' 
rs = stmt.executeQuery(sql)

employeeList=[]
while (rs.next()):
    row={}
    row['firstname']=rs.getString(1)
    row['lastname']=rs.getString(2)
    row['salary']=rs.getDouble(3)
    employeeList.append(row)
    
rs.close()
stmt.close()
con.close()

print 'employee salary over $20,000'
print '============================'
print 'firstname lastname salary'
print '============================'

# print the result 
for e in employeeList:
    print e['firstname'],e['lastname'],'$'+ str(e['salary'])

The above code is very similar to pure Java JDBC programming conceptually. However, if you look closely there are fewer line of code written in Jython.

Note:

  • No need to declare the type for Connection, Statement, Result as which is necessary for pure Java JDBC programming. Their types are dynamically resolved.
  • For ResultSet Processing, it is a lot easier to use employeeList(a Jython List) and row (a Jython dictionary) to hold java.sql.ResultSet comparing to Vector/ArrayList from java.util.
  • To print the contents of employeeList, a 2 liner at the bottom does the job pretty well.

SQL String formatting (select2.jy)

Run "jython select2.jy". It is functionally equivalent to select1.jy. The only difference is the use a Jython dictionary to hold name,value pairs within a String template while use % operator to evaluate this template into a String of meaningful SQL.

With Jython's built in dictionary based String formatting which is as powerful as printf in C, we can associate a dictionary using the % operator. Imagine if we did this in Java.

The result of the following code is "select firstname,lastname,salary from employee where salary > 20000 order by salary", exactly the same as in select1.jy.

Listing 2. (select2.jy) dictionary based String formatting for SQL
sqlTemplate = 'SELECT %(columns)s FROM %(tables)s WHERE %(constraints)s' 
dict = {'columns':      'firstname,lastname,salary',
        'constraints' : 'salary>20000',
        'tables' :      'employee'}

sql = sqlTemplate % dict


print sql

ResultSet processing in functional flavor (select3.jy)

We want to write the result into a file for further analysis. To illustrate how functional programming features of Jython can be used here, lambda function and list compression are used in the following code though it can be done in imperative programming style as well.

Listing 3. (select3.jy) uses lamda and list comprehension
# lambda function
toStr=lambda e: e['firstname']+','+e['lastname']+',$'+ str(e['salary'])

# an example of list comprehension
# write the result into a file called "salaries.txt"

print '======================================'
print 'the results are saved in salaries.txt'
print '======================================'

try:
    f=open('salaries.txt','w')

    f.writelines([toStr(e)+'\n' for e in employeeList])

finally:
    f.close()

Note:

  • In general, a lambda function is a function that takes any number of arguments and returns the value of a single expression. It is similar to anonymous class in Java except it is a first class object which can be passed around as argument if needed. Lambda function toStr()is to convert the values of an employee (as a dictionary) for output.
  • ([toStr(e)+'\n' for e in employeeList] uses Jython's list comprehension to turn each result into a line written in slaries.txt with the lambda function toStr. Compare this with writing a loop to do the equivalent.
  • Text File processing in Jython is as simple as 3 liners of open(),readlines/writelines (taking a list) and close(). Again imagine if you write it in java.io.

Getting Graphical (select4.jy)

Let us try to display the result in HTML. We can also highlight those employees with salary over $40,000 in red.

In this example, we use SWT. SWT is the software component that delivers native widget functionality for the Eclipse platform in an operating system independent manner. And this is the secret that makes IBM WSAD (Websphere Studio Application Developer) run in the speed of native code.

Figure 2. output of "select4.bat"
swt.jpg

Listing 4 shows what packages needed to imported from SWT.

Listing 4. (select4.jy) import SWT pacakges
from org.eclipse.swt import *
from org.eclipse.swt.graphics import *
from org.eclipse.swt.layout import *
from org.eclipse.swt.widgets import *
from org.eclipse.swt.browser import *

Listing 5 shows how to use SWT to display results in HTML

Listing 5. (select4.jy) display results in HTML
# display in browser with HTML
display = Display()
shell = Shell (display)
shell.setLayout(FillLayout())

browser = Browser(shell, SWT.NONE)
browser.setText(toHtml(employeeList))
browser.setSize(400,500)

shell.pack()
shell.open()
while not shell.isDisposed():
    if not display.readAndDispatch():
        display.sleep

display.dispose()

Note:

  • This code snippet is almost identical for SWT applications.
  • For formatting HTML we use tuple based String formating in Jython function toHtml(employeeList) which turns the employeeList into a string in HTML.

Insert with fun

Now we will see how to insert in Jython.

Suppose Joe is a QA and he wants a way to create new employees in the SAMPLE database for functional testing. With Jython, we can have Joe write his own script with little effort.

For instance, Joe can create three new employees in the SAMPLE database by running "jython batchinsert.jy joescript.txt".

Listing 6. joescript.txt
createEmployee(firstName='JOHN',midinit='M',lastName='DOE',salary=32888.55,sex='M',workdept='A01',
job='DESIGNER')
createEmployee(firstName='MANAGER',midinit='M',lastName='DOE',salary=50000,sex='M',workdept='A01',
job='MANAGER')
createEmployee(firstName='SARA',midinit='M',lastName='DOE',salary=40000,sex='F',workdept='C01',
job='DESIGNER')

All Joe has to do if he wants to add another Employee is to use edit "joescript.txt" with any text editor he likes.

The magic behind the script is simple. Jython has a buit in function called execfile(filename). If we implement createEmployee() as a Jython function to insert a row into the EMPLOYEE table, then execfile('joescript.txt') will insert as many employees as Joe wants.

In other words, we take advantage of Jython's interpreter and can derive a little testing language like this for Joe. The solution is extensible as well. If Joe wants to remove an employee or update an employee in his script, all he needs to do is add another function. The coolest thing is the short learning curve for Joe. He can write scripts like this in a snap without knowing he is making Jython function calls.

Listing 7. (batchinsert.jy) createEmployee function
from java.lang import *
from java.sql import *
import sys

def createEmployee(**args):

    global stmt

    sqlTemplate=
    "INSERT into EMPLOYEE (EMPNO,FIRSTNAME,MIDINIT,LASTNAME,SALARY,SEX,EDLEVEL, HIREDATE, WORKDEPT,JOB) 
    values ((select RTRIM(CHAR(MAX(INT(EMPNO))+1)) from EMPLOYEE), 
    '%(firstName)s', '%(midinit)s','%(lastName)s', 
    %(salary).2f,'%(sex)s' ,18, CURRENT DATE,'%(workdept)s','%(job)s')" 

    # dictionary based SQL string formatting
    sql=sqlTemplate % args
    
    stmt.addBatch(sql)
    
# load DB2 JDBC type 2 driver
Class.forName("COM.ibm.db2.jdbc.app.DB2Driver").newInstance()
con = DriverManager.getConnection( 'jdbc:db2:sample', 'vyang','jythonrocks');
con.setAutoCommit(0)
stmt = con.createStatement()
execfile(sys.argv[1])
stmt.executeBatch()
stmt.close()
# commit
con.commit()
con.close()

There are quite a few new things here.

  • con.setAutoCommit(0) is used to set auto commit off just like con.setAutoCommit(false) in JDBC call.
  • stmt.addBatch(sql), stmt.executeBatch() for batch inserting is efficient to speed up bulk insertion. This technique is also used in Neo's load simulator for large volume insert.
  • global stmt will make stmt become a shared global variable which is convenient here.
  • def createEmployee(**args) takes argument as dictionary argsand shows how flexible Jython function declaration can be.
  • sql=sqlTemplate % args uses dictionary based SQL string formatting that we learned in select2.jy.

Conclusion

In this article we have shown some simple examples to serve as a starting point for developers to explore. We are sure there are more benefits and issues as we move forward.

On the one hand, Jython reaps of the benefits of seamless integration with huge proven Java libraries and API such as DB2 JDBC driver from IBM. Thanks to its language Python is well suited for tasks such as automated testing and load testing where rapid application development is needed. As it is well phrased in Charming Jython(see resources), "Jython by no means requires that you ditch the Java language. Jython can be a very handy supplement, useful for quick inspection and prototyping, testing, and for handling a selection of coding tasks for which its approach is better suited."


Download

DescriptionNameSize
Code samplejydb2.zip  ( HTTP | FTP )1128KB

Resources

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=14585
ArticleTitle=Learn how to write DB2 JDBC tools in Jython
publish-date=04012004