How should I do a DB update using SQLJ in a Liberty Batch (JSR 352) ItemWriter?

Question

How should I obtain/cache/close my ConnectionContext (Connection)?

Should I cache the context in ItemWriter.open() and cache across each chunk's ItemWriter.writeItems()

What if I want to use DB2-specific APIs?

Answer 1

The recommended approach is generally to use a get-use-close pattern for SQLJ in the writeItems() method run in each chunk. This is preferred over getting the connection/context in open() and caching across chunks.

You can make JDBC driver-specific calls using the Connection.unwrap() method.

So something like:

public class MyDB2SQLJItemWriter implements ItemWriter {

    DataSource myDataSource; // get via injection (not shown) or however

    // ...

    public void writeItems(List<Object items) throws Exception {

        Connection con = myDataSource.getConnection();
        DB2Connection db2Con = con.unwrap(DB2Connection.class);
        db2Con.setPackagePath(currentPackagePath);  // DB2-specific call
        SqljCtx sqljCtx = new SqljCtx(db2Con);

        // now do SQLJ

        // close SqljCtx AND DB2Connection
        sqljCtx.close();
    }

There's also no need to configure your dataSource with the enableConnectionCasting attribute, (which adds flexibility not needed here plus opens the door to more complicated issues related to connection caching).

This recommendation extends the general recommendation to follow get-use-close patterns in WebSphere when using JDBC APIs. Letting WebSphere do the connection pooling provides good performance, better resource utilization and avoids some more complicated pooling scenarios.