Using DB2 in XPages Part 9: Access DB2 from a Java Agent

Unless I think of something else worthy of writing about, this will be my final post in this blog series on DB2 and XPages. I am grateful and honored by the compliments that have come my way with regards to this series. One of the main reasons I blog is so that I can remember the work that I have done, and so that I will be able to do it quicker and more effective in future. The other main reason is that I also enjoy sharing information with others, especially since I have greatly benefited from other community bloggers.

I also wanted to give another plug for the MWLUG conference this August 19-21, 2015 in Atlanta. There some amazing sessions being presents by the top names in our community. I am humbled and honored to be included on that list. Along with my co-worker Dwain Weurfel, I will be presenting a session titled "Real World Experience: Integrating XPages and DB2". Come hear us present on topics that are included in this blog series and much more. 

In this post, my topic is accessing DB2 data using a Java agent.

How are Java Agents Different

Java agents are different and those differences can be very frustrating. I remember seeing this tweet and it reminded me that I needed to get busy and write this blog post.

@ptcalhoun Same here this week - Java agents suck. DO NOT USE them

— Oliver Busse ✫ (@zeromancer1972) July 21, 2015

In our case, we had to use them for scheduled processes that needed to run on the server. It was use either Java or LotusScript.

To access DB2, it is necessary to access the drivers provided by IBM or your DBA. To have your agent code reference the drivers and any other jars is is necessary to understand the restrictions that apply to java agents. The list below is not meant to be exhaustive:

Java Agents Can:

1. Access Jar files stored in the servers jvm\lib\ext folder
2. Access Script Libraries containing supporting classes

Java Agents Cannot:  

1. Access Jars stored in the NSF's Build Path
2. Access any Jar using the new 'Jar' design element
3. Access any java code stored in the Code\Java section

Using External Jars and Script Libraries

Java agents can access the jars on the server but you MUST also have a copy of the jars on your local jvm\lib\ext folder in order to compile your code.

To use a supporting script library in your java agent, you first create a new library of type 'Java'. You then put whatever supporting classes inside that you need. Once created, you then can import the library into your java agent using the 'Import' button. In this case in the graphic, there are only two classes; I could have easily just imported those two classes individually into my agent.

Script Library

Use Import to use the Script Library in your agent

Finally, the last thing you need to do in order to use the classes is to add an import statement in your agent.  

import org.sample.javaLib.XLog; 
import org.sample.javaLib.AgentUtils;

Code Usage

The code in this example runs on a scheduled basis to update a table column based on a condition.

Code Example using footnotes

package org.sample.agent; 

import java.sql.Connection; 
import java.sql.DriverManager; 
import java.sql.PreparedStatement; 
import java.sql.ResultSet; 
import java.sql.SQLException; 
import java.text.DateFormat; 
import java.text.SimpleDateFormat; 

import lotus.domino.AgentBase; 
import lotus.domino.Database; 
import lotus.domino.Document; 
import lotus.domino.Session; 
import lotus.domino.View; 

1 import org.sample.javaLib.XLog; 
import org.sample.javaLib.AgentUtils; 

public class JavaAgent extends AgentBase { 

@Override 
public void NotesMain() { 
   Database db_cred = null; 
   Connection conn = null; 
   PreparedStatement ps1 = null, ps2 = null; 
   ResultSet rs = null; 
   XLog log = null; 
   Session session = getSession(); 

   try { 
     //get values from keyword documents 
     2 String schema = session.getCurrentDatabase().getView("Keywords").getDocumentByKey("SCHEMA").getItemValue("KeywordValues").elementAt(0).toString().trim();

    ...Rest of code to pull keywords removed...                    

     //Initialize log 
     log = new XLog("My App", agentDebugServer, agentLogDB, agentDebugLevel); 
     log.info("Agent processPendingSettlementAmount: Starting execution of agent"); 

     db_cred = session.getDatabase(agentDebugServer, dbpath); 

     if (3 db_cred.isOpen()) { 

        View v = db_pass.getView(dbview); 
        Document doc = v.getDocumentByKey(dbkey, true); 
        String url = doc.getItemValueString("PI_JDBCURL").trim(); 
        String user = doc.getItemValueString("PI_Username").trim(); 
        String password = doc.getItemValueString("PI_Password").trim(); 
        4 doc.recycle(); 

        v.recycle(); 
     }
     Class.forName(driver); 
     5 conn = DriverManager.getConnection(url, user, password); 

     if (conn != null) { 
        conn.setAutoCommit(false); 
        java.util.Date currentDate = new java.util.Date(); 
        6 java.sql.Date emptyDate = java.sql.Date.valueOf("9999-12-31"); 

     // Format Posting Date 
     DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
     currentDate = formatter.parse(formatter.format(currentDate));                                                              

     java.sql.Date currentDateSQL = new

     java.sql.Date(currentDate.getTime()); // convert to SQL date 

     7 String sqlSelect = "SELECT USER_ID, USER_TAX_PAID, USER_TAX_DUE_AMT, " + 

"USER_TAX_DUE_AMT FROM " + schema + ".USER_DETAIL" + 

"WHERE USER_TAX_DUE_AMT > 0 AND USER_TAX_DUE_DT <= ?"; 

    String sqlUpdate = "UPDATE USER_DETAIL SET USER_TAX_PAID = ?, USER_TAX_DUE_AMT = ?, USER_TAX_DUE_DT = ? WHERE USER_ID = ?"; 

    ps1 = conn.prepareStatement(sqlSelect); 
    ps1.setDate(1, currentDateSQL); 
    rs = ps1.executeQuery(); 

    while (rs.next()) { 
       ps2 = conn.prepareStatement(sqlUpdate); 
       8 ps2.setString(1, rs.getString("USER_TAX_DUE_AMT").trim()); 

       ps2.setInt(2, 0); 
       ps2.setDate(3, emptyDate); 
       ps2.setString(4, rs.getString("USER_ID").trim()); 
       ps2.executeUpdate();

       9 conn.commit();

     }
                                                     

      log.info("Agent processTax: Process completed successfully"); 
     } else { 
        log.error("Agent processTax: Connection to DB2 could not be obtained"); 
     } 
    } else { 
     log.error("Agent processTax: Unable to open DBCRED document"); 
    } 

   } 

   catch (Exception e) { 

      //log errors 
   } 
   finally { 
      //clean up connections                 
   } 
}

Code Explanation using footnotes

1  - Code imported from Script Library that we previously imported into the agent.

2  - In previous post, the example code pulled from a KeywordBean in applicationScope.  In a java agent, this bean is unavailable therefore each agent must load the keywords that are needed.

3 - Since the Credentials class (discussed in previous post) is unavailable in an agent, the agent manually retrieves the values from the Credential Notes database.

4 -  Of course, all Notes objects must be recycled

5 - Gets the connection from the standard java.sql.DriverManager class, and passes the credentials it had retrieved from the Credentials database. This agent runs on the server so credentials are never passed to or from the server, therefore this is considered secure by company standards.

6 -  Early on in the project, the DBA set all columns to "Not Null" so we had to put a dummy date of "9999-12-31" in a column to signify that it was 'empty'.  Bottom line here: Don't let your DBA get away with doing that unless you really need it. Know what you need the table to look like prior to starting coding.

7 - There are two distinct queries that need to be run within this agent

8 - Shows how the second query is build with values pulled from the first query. The first being a SELECT that returns a ResultSet object.

9 - After each iteration in the while statement, we must manually commit the changes to DB2

Note: The agent code above was heavily modified and stripped down from the original. The purpose was also changed to something more understandable. I wouldn't be surprised if all my curly braces don't line up properly.