IBM® DB2® 9 enables you to use the power of pureXML™ and integrate it into your application development environment. Take a look at how to integrate this powerful technology into your Java™ applications and make development easier with the new DB2 Developer Workbench.

Share:

Anson Kokkat (ansonk@ca.ibm.com), Senior Software Engineer, IBM, Software Group

Anson Kokkat photoAnson Kokkat works as a software development team lead in the DB2 UDB Advanced Support team. He has been working at IBM since 1999 after graduating from the University of Western Ontario with a degree in Electrical and Computer Engineering Science. Anson has been involved with many aspects of DB2 application development using many different platforms. Areas of specialization include WebSphere and DB2 integration, JDBC, stored procedures, and .NET. Most recently he was selected to present information on the new DB2 Universal Driver at the Information Management conference in Europe.



26 October 2006

Also available in Japanese

Jump start the development of your Java applications using the new pureXML features of DB2 9, and integrate pureXML into your application development environment.

With the introduction of DB2 9, you now have powerful integrated development environments (IDEs) such as the Developer Workbench. By the end of this article, you will be able to take an existing application you are using or develop a new Java application and exploit the features of pureXML that you want to introduce or already have in your environment. To help you if you run into problems as you try to develop JDBC applications using XML, this article discusses how to diagnose these types of problems and move on to develop concise, feature-rich programs.

Universal JDBC driver

For the purposes of this article, we will deal with the Type 4 Universal JDBC driver, which was introduced in V810. The Universal JDBC driver is the driver of choice since it provides better memory management and stability over the legacy-based cli driver, or db2java.zip.

In order to distinguish between the Type 2 legacy-based driver and the Type 4 Universal driver, you can look at the actual connection string that you use in your application.

The use of the Type 2 driver is shown in the connection string where you use the COM.ibm.db2.jdbc.app.DB2Driver

Listing 1. Type 2 JDBC Driver Connect
try {
  // Load the DB2 JDBC Type 2 Driver with DriverManager
  Class.forName("COM.ibm.db2.jdbc.app.DB2Driver");
} catch (ClassNotFoundException e) {
     e.printStackTrace();
}

The use of the type 4 driver is shown in the connection string where we use the com.ibm.db2.jcc.DB2Driver

Listing 2. Type 4 JDBC Driver Connect
try {                        
// Load the DB2 JDBC Type 4 Driver with DriverManager
  Class.forName("com.ibm.db2.jcc.DB2Driver");                             
} catch (ClassNotFoundException e) {
   e.printStackTrace();
}

Let's see what a typical error would look like when using the Type 4 driver. (How to handle these errors is covered in a later section.)

When you use the Type 4 driver and run into an error condition, you always get some type of EXCEPTION. A typical exception with the jcc driver will look like the following:

Listing 3. Typical Type 4 jcc exception
[Thread:IdleRemover][SQLException@513098] Stack trace follows            
com.ibm.db2.jcc.a.SqlException: Error executing a XAResource.commit(),   
Server returned XAER_RMFAIL                                              
 at com.ibm.db2.jcc.c.ic.a(ic.java:2981)                                 
 at com.ibm.db2.jcc.c.c.n(c.java:494)                                    
 at com.ibm.db2.jcc.a.p.m(p.java:1180)                                   
 at com.ibm.db2.jcc.a.p.w(p.java:1381)                                   
 at com.ibm.db2.jcc.c.b.w(b.java:3444)                                   
 at com.ibm.db2.jcc.a.p.v(p.java:1362)                                   
 at com.ibm.db2.jcc.c.c.v(c.java:618)                                    
 at com.ibm.db2.jcc.a.dc.close(dc.java:67)

For the most part, the above error shows some obfuscated code which may not be useful to the end user, but the text of the error message is what will help you to get to the bottom of the problem. Your best option to find out the cause of the above error message is to do a search in the DB2 Technical Support Web site to see if this is in fact a known problem.

pureXML

There are numerous articles already that show how to use the power of XML in DB2 9 and take advantage of the many features. This article walks through some of them, but the key point to keep in mind when developing JDBC applications using XML is that we are just plucking out the methods that we use either by XQuery and SQL/XML and integrating these methods into the application.

The power of DB2 9 shows that it is the only data server that is able to store SQL data and XML data in its own native format, which is called pureXML. You can see the practical use of pureXML when it is stored in DB2 9 because it is stored in parsed representation, and the efficient use of indexing makes it that much more powerful when you try to access it from within the actual engine.

Let's look at an example of how powerful DB2 9 is in integrating DB2 XML as a column on its own:

Listing 4. CREATE TABLE with XML column
CREATE TABLE Customer (Cid BIGINT NOT NULL PRIMARY KEY,
                       Info XML,
                       History XML)

You can see in the example above that you can easily create a column called 'Info,' of type XML, just as easily as it is to create any other column of DB2.

By integrating the ease of use of XML into the relational format, more and more organizations will find it useful to take advantage of storing data in its native form.

From the application development side, programmers can either use SQL or XML APIs to work with either relational or XML data whatever they see fit.

Let's look at how JDBC applications can use the above knowledge and exploit the use of XML data. Today you can just use the power of XQuery or SQL/XML and integrate these statements into your JDBC application.

With respect to XQuery, the way it works is that it is embedded within SQL, and SQL is already supported by JDBC. So all you need to do is take the SQL statements that have the already embedded XQuery in them and put this into your Java application.

DB2 Developer Workbench (DWB)

Let's now walk through how to install and set up the Developer Workbench and create your own Java application that uses XML.

Developer Workbench can be downloaded from the DB2 UDB Software Support site. From this page, you can go to the clients download. Under "Fixes By Version," you will see the link on where to download the DWB. You can also check out the Developer Workbench download site.

See the following screen shot in Figure 1 on where to download the DWB:

Figure 1. Where to download the DWB
Where to download the DWB

Once you have downloaded the DWB, you are ready to begin. Note: DWB is a separate install from DB2, so it will show up as a separate item when you try to launch it from Windows (Start > All Programs > IBM DB2 Developer Workbench > DB2 Developer Workbench 9.1 > DB2 Developer Workbench, for example).

  1. On the first screen after you have launched DB2 Developer Workbench, you are prompted to name the workspace. You can put any name. For this part of the article, name the workspace "Test."
    Figure 2. Workspace launcher
    Workspace launcher
    Select OK.
  2. Once the DWB comes up, let's get right into the Workbench, so click on the arrow in the top, right-hand corner:
    Figure 3. DWB launch window
    DWB launch window
    After you have clicked the arrow, you are in the Workbench view, which shows all the areas such as Data Project Explorer, Database Explorer, and various other tabs related to application development common to any IDE:
    Figure 4. DWB views
    DWB views
  3. Now you are ready to get started with your Java project. The first thing you need to do is switch to the Java perspective: Window > Open Perspective > Other. Select Java, and click OK.
    Figure 5. Java perspective in the DWB
    Java perspective in the DWB
  4. Start by creating a new Java project: File > New > Project. Expand the Java folder, and select Java Project, then click Next.
    Figure 6. New project view
    New project view
  5. On the next screen, give your project a name, and click Finish. In this case, we called our project "TestLab."
    Figure 7. Project name
    Project name
  6. Let's now add all the code and necessary components for the Java project. Create a new class first: File > New > Class. Select the checkbox that contains public static void main(String[] args) to include the main method. Under Package, enter lab.
    Figure 8. New Java class
    New Java class
  7. You can now see all the components of the Java application and see how intuitive it is to add source or use this developer environment to create new components in your Java application.
    Figure 9. Java code view
    Java code view
  8. Before doing anything else, you need to import the appropriate JAR files into your project. The db2jcc.jar is the physical representation of the DB2 Universal JDBC driver, and that is what you want to use in this project. Right-click on the project (in this case TestLab), and choose Properties.
    Figure 10. Properties of Java project
    Properties of Java project
  9. Select Java Build Path > Libraries tab > Add External Jars.
    Figure 11. Java Build Path
    Java Build Path
  10. Browse for the Universal JDBC driver files, JAR files, which are usually located in your $DB2PATH/sqllib/java directory.
    Figure 12. Java Build Path
    Java Build Path
  11. Select the db2jcc.jar and db2jcc_license_cu.jar files.
  12. Make sure the getConnection( ) method in the Java program contains the appropriate userid/password suited to your system.

    Now you are ready to run the Java application.
  13. On the menu bar Run, select Run.
  14. Select all the defaults, and click on the New button. You can now see all the newly created files as part of the project.

The above shows you the step-by-step approach to develop a Java application in the DWB. Now let's look at the specific code to utilize the XML data.

If you look at a Java program that utilizes XML data, all you are really doing is taking the knowledge that you have to develop queries against XML data and putting that directly into your JDBC application.

The first step of your Java application is to make sure that you import the DB2Xml methods:

Listing 4. import jcc packages
import com.ibm.db2.jcc.DB2Xml;

This is in addition to your other imports.

Let's look at the typical flow of a Java application and the basic parts of a JDBC application.

  1. Connect to the database:
    Listing 5. getConnection
    connection = DriverManager.getConnection(url, user, pass);
  2. Statement -- whatever SQL you are executing:
    Listing 6. prepareStatement
    PreparedStatement stmt = connection.prepareStatement(sql);
  3. ResultSet -- get the resultset back:
    Listing 7. resultset
    ResultSet resultSet = stmt.executeQuery();

    Within the actual application, there are a couple of different ways that you get the resultset back:

    1. You can stream an XML value and use something like getBinrarySteam( ):
      Listing 8. resultset.getBinaryStream( )
      InputStream inputStream = resultSet.getBinaryStream(1);
    2. You can use something like DB2XML or SQLXML to get the value back:
      Listing 9. resultset.getObject( )
      DB2Xml db2xml = (DB2Xml) resultSet.getObject(1);

Now let's have a look at two different scenarios that you may encounter when dealing with XML data:

  • Select
  • Insert

Select: Listing 10 depicts a typical part of an application, showing how to select from an XMLProduct table:

Listing 10. Select an XML value
String sql = "SELECT PID, DESCRIPTION from XMLPRODUCT where PID = ?";
PreparedStatement stmt = connection.prepareStatement(sql);
stmt.setString(1, "100-105-09");
ResultSet resultSet = stmt.executeQuery();
String xml = resultSet.getString("DESCRIPTION"); // or
InputStream inputStream = resultSet.getBinaryStream("DESCRIPTION"); // or
Reader reader = resultSet.getCharacterStream("DESCRIPTION"); // or
DB2Xml db2xml = (DB2Xml) resultSet.getObject("DESCRIPTION");

So as you can see from the application, there are a number of different Java methods that you can use to get to the XML DESCRIPTION values. Let 's look at some of the methods that are used in the above program:

  • getString( )
  • getBinaryStream( )
  • getCharacterStrem( )
  • getObject( )

Insert: Listing 11 depicts a typical part of an application, showing how to insert into the XMLProduct table:

Listing 11. Insert an XML file
String sql = "INSERT INTO xmlproduct VALUES(?, ?)";
PreparedStatement stmt = connection.prepareStatement(sql);
stmt.setString(1, "100-105-09");
File binFile = new File("productBinIn.xml");
InputStream inBin = new FileInputStream(xmlFile);
stmt.setBinaryStream(2, inBin, (int) binFile.getLength());
stmt.execute();

You can see above that we are inserting an XML file called productBinIn.xml. So this would be your typical code that you would insert into the DWB and run as shown above.

The point of the example above is that the code shown lets you utilize XML data, whether you want to select or insert it into a table. The actual code can then be ported into DWB and run as you would run any Java program. You also have other features available to you, such as debuggers that come with the DWB to make your application development easier.

Within the DB2 application development environment, you have the capability to use the Type 4 pure Java driver for your Java application needs. pureXML is now a powerful part of DB2 9, and by exploiting the use of XML in your Java applications, you can use tools like the Developer Workbench to easily create standalone Java applications. The development of more feature-rich enhancements to exploit the use of XML methods in Java are yet to come and make this an exciting time for Java developers who want to exploit the power of pureXML.

Resources

Learn

Get products and technologies

  • Download a free trial version of DB2 Enterprise 9.
  • Now you can use DB2 for free. Download DB2 Express-C, a no-charge version of DB2 Express Edition for the community that offers the same core data features as DB2 Express Edition and provides a solid base to build and deploy applications.
  • Download the DB2 Developer Workbench.
  • Download IBM product evaluation versions and get your hands on application development tools and middleware products from DB2®, Lotus®, Rational®, Tivoli®, and WebSphere®.

Discuss

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, Java technology
ArticleID=170584
ArticleTitle=Using DB2 XML and Java
publish-date=10262006