Result Set is closed error when referencing table via JPA (Websphere and DB2)

Question

I'm using the IBM implentation of Open JPA on WebSphere 7 and I'm having an issue when I'm trying to reference an object that is @ManyToOne and keep getting the following error from DB2:

com.ibm.db2.jcc.b.SqlException: [jcc][t4][10120][10898][3.50.152] Invalid operation: result set is closed. ERRORCODE=-4470, SQLSTATE=null

I'm pulling my hair out as to why this doesn't work and hope that somebody can help.

Here is a simplified view the database schemas:

Table Report

record_id - integer - (primary key - generated by DB2)

agency - integer not null (foreign key to Dropdown table)

Table Dropdown

record_id - integer - (primary key - generated by DB2)

Here is the JPA entity for the Report which references the agency

@ManyToOne(fetch=FetchType.EAGER)
@JoinColumn(name="AGENCY")
private Dropdown agency;

Here is the code where I'm running a named query to get the data and then just iterating over the result set to print out the report id and the agnency. Whenever report.getAgency() is called, I get the "result set is closed" error from DB2:

@SuppressWarnings("unchecked")
public List<Report> getOpenIncidentsForUser(String aceId) throws Exception
{
    List<Report> results = null;
    EntityManager em = getEntityManager();

    try
    {
        Query query = em.createNamedQuery("getOpenIncidentsForUser");
        query.setParameter(1, aceId);
        results = (List<Report>) query.getResultList();

        Iterator<Report> it = results.iterator();
        while(it.hasNext())
        {
            Report report = it.next();
            System.out.println("Report [" + report.getRecordId() + "] Agency: [" + report.getAgency() + "]");
        }
    }
    catch (Exception e)
    {
        log.fatal("Fatal error getting incidents for user", e);
        throw e;
    }
    finally
    {
        em.close();
    }

    return (List<Report>) results;
}

if I don't ever refer to the getAgency method, I can print out anything else about the report with no problems. It only seems to be with the reference to the 2nd table. Any ideas?

Answer 1

I had answered this in responses to my original comment, but realized that I never marked the question as answered, so I wanted to do that officially.

The fix is documented here: https://www.ibm.com/support/knowledgecenter/SSEQTP_8.5.5/com.ibm.websphere.base.doc/ae/tejb_jpatroubleshoot.html

The fix ended up being the resulSetHoldability setting needed to be 1 instead of 2

For XA data sources you have to set downgradeHoldCursorsUnderXa to true, otherwise you could get a persistence exception with this message:

An SQL OPEN for a held cursor was issued on a XA connection