New Blog Series: Using a DB2 Datasource in XPages

This post is the introduction of a blog series on using a DB2 backend datasource in XPages.  I will discuss how we set up the connections and give examples of different types of queries. There should be very little here specific to DB2, but for ease of writing, I will write 'DB2' throughout this series. Feel free to substitute any relational database, such as Oracle, MySQL, SQL Server, etc.

Background

Why did we choose to use a DB2 backend?  It is complicated... but the real reason we chose to use DB2 is that someone at my company believed that Notes was not secure enough for storing sensitive data.  Most everyone who reads this knows that this is far from the truth.  Notes is extremely secure and its security model is looked at as one of it biggest strengths.

When presented with this assertion, I did my best to present arguments against using DB2. I knew the security premise was wrong, and I knew it would dramatically increase project completion time. Lastly, I knew Notes was well suited for the project at hand.

Even though I fought hard for what I felt was right, I was not very disappointed when I lost the battle. The reason being that I knew that using a relational backend would be a fun learning experience and good for the career. In my last job, I had experience retrieving Oracle data through a java agent, but I never bound an XPage to relational data real time.

Around this time, I went to the MWLUG conference in Grand Rapids and heard an excellent presentation by Toby Samples on using Hibernate in Xpages. When I came back, I did my best to sell the use of Hibernate because I felt it would be a benefit, and I now had the information to make it work. Despite this, the decision was made to go with JDBC, mainly because we had already had another database with the same functionality that we could copy, and the developers who wrote it were now on the same project.

Series Contents

With that background, I am going to discuss the following topics in this blog series:

• Setting up your database to connect to DB2.  
• Using the Extension Library Relational Controls
• Using Connection Pooling
• Storing the DB2 Credentials in a managed bean
• Coding a simple SELECT statement
• Selecting information from two tables using a JOIN
• Coding an INSERT
• Coding an UPDATE
• Populating a combobox dropdown with values from a DB2 query
• Retrieving a collection of data in JSON format for using in a data table

Note: Due to a very heavy load at work, it might take me up to two months or more to finish this blog series.  

Assumptions

Throughout I am going to make the assumption that you know the basics of SQL. For me, SQL was the one thing I learned in college that I actually still use today.  Another assumption is that you know something about java, because all the coding is done using it. You can use SSJS to access DB2, but early on, we made the decision to have as much of the business logic as possible in java.

Credits

I was busy with another project while the groundwork was setup for connecting to DB2. Credit for doing this work goes to my coworker Dwain Weurfel. Dwain, thanks for helping me document and explain the setup, and  for helping me to proof these posts, so that I get it right! I should also give credit to former coworker Eric Dyer who had a large role in creating the groundwork while working on the first DB2 backend project at my company.