Details

    • Type: Improvement Improvement
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 2.0.0
    • Fix Version/s: 1.2.3, 1.3.0, 2.0.2, 2.1.0
    • Component/s: jdbc
    • Labels:
      None

      Description

      Streaming Lob support in DB2

      1. OPENJPA-1699-DB2-1.2.x.patch
        4 kB
        Heath Thomann
      2. OPENJPA-1699-DB2-2.0.x.patch
        3 kB
        Heath Thomann
      3. OPENJPA-1699-DB2-1.3.x.patch
        3 kB
        Heath Thomann

        Issue Links

          Activity

          Hide
          Michael Dick added a comment -

          Thanks for catching the inconsistency in the docs Milosz. I've added a bullet (tried to keep it brief) to the manual. Re-closing the issue.

          Show
          Michael Dick added a comment - Thanks for catching the inconsistency in the docs Milosz. I've added a bullet (tried to keep it brief) to the manual. Re-closing the issue.
          Hide
          Milosz Tylenda added a comment -

          The fullyMaterializedLobData trick should be documented under "Known issues with DB2" section, like it is done with other database-specific LOB streaming limitations (Oracle and MySQL for example).

          Show
          Milosz Tylenda added a comment - The fullyMaterializedLobData trick should be documented under "Known issues with DB2" section, like it is done with other database-specific LOB streaming limitations (Oracle and MySQL for example).
          Hide
          Michael Dick added a comment -

          Thanks for the patches Fay and Heath.

          Show
          Michael Dick added a comment - Thanks for the patches Fay and Heath.
          Hide
          Heath Thomann added a comment -

          I'm attaching patch named OPENJPA-1699-DB2-1.3.x.patch which is for 1.3.x. This is basically a back port of the changes made in truck via OJ-130.

          Thanks,

          Heath

          Show
          Heath Thomann added a comment - I'm attaching patch named OPENJPA-1699 -DB2-1.3.x.patch which is for 1.3.x. This is basically a back port of the changes made in truck via OJ-130. Thanks, Heath
          Hide
          Heath Thomann added a comment -

          I'm attaching patch named OPENJPA-1699-DB2-2.0.x.patch which is for 2.0.x. This is basically a back port of the changes made in truck via OJ-130.

          Thanks,

          Heath

          Show
          Heath Thomann added a comment - I'm attaching patch named OPENJPA-1699 -DB2-2.0.x.patch which is for 2.0.x. This is basically a back port of the changes made in truck via OJ-130. Thanks, Heath
          Hide
          Heath Thomann added a comment -

          I'm attaching patch named OPENJPA-1699-DB2-1.2.x.patch which is for 1.2.x. This is basically a back port of the changes made in truck via OJ-130, with one additional fix to LobFieldStrategy for 1.2.x.

          Thanks,

          Heath

          Show
          Heath Thomann added a comment - I'm attaching patch named OPENJPA-1699 -DB2-1.2.x.patch which is for 1.2.x. This is basically a back port of the changes made in truck via OJ-130, with one additional fix to LobFieldStrategy for 1.2.x. Thanks, Heath
          Hide
          Fay Wang added a comment -

          In the case of very large lob, DB2 JCC driver will automatically use progressive streaming to retrieve the Lob data. With progressiveStreaming, the inputStream retrieved (see red below) must be materialized before the next iteration of call to rs.next().

          ResultSet rs = pstmt.executeQuery(...);
          while (rs.next())

          { InputStream is = rs.getBinaryInputStream(1); writeToFile(is); }

          rs.close();

          However, in OpenJPA, we iterate through the whole resultSet and set the InputStream to respective entities:
          ResultSet rs = pstmt.executeQuery(...);
          while (rs.next())

          { InputStream is = rs.getBinaryInputStream(1); entity.setInputStream(is); }

          rs.close();
          ...

          Once the entity is returned from OpenJPA, the application will get Lob Closed exception when trying to process the InputStream:

          writeToFile(entity.getInputStream()); <== Lob is closed Exception

          The work around is to force fullyMaterializedLobData to true (see below) in the url, so that the DB2 JCC driver will materialize the whole Lob data into memory before returning to the application:

          <property name="openjpa.ConnectionProperties"
          value="DriverClassName=com.ibm.db2.jcc.DB2Driver,Url=jdbc:db2://localhost:50000/demodb:fullyMaterializeLobData=true;progressiveStreaming=NO;,Username=db2user,Password=passw0rd" />

          Show
          Fay Wang added a comment - In the case of very large lob, DB2 JCC driver will automatically use progressive streaming to retrieve the Lob data. With progressiveStreaming, the inputStream retrieved (see red below) must be materialized before the next iteration of call to rs.next(). ResultSet rs = pstmt.executeQuery(...); while (rs.next()) { InputStream is = rs.getBinaryInputStream(1); writeToFile(is); } rs.close(); However, in OpenJPA, we iterate through the whole resultSet and set the InputStream to respective entities: ResultSet rs = pstmt.executeQuery(...); while (rs.next()) { InputStream is = rs.getBinaryInputStream(1); entity.setInputStream(is); } rs.close(); ... Once the entity is returned from OpenJPA, the application will get Lob Closed exception when trying to process the InputStream: writeToFile(entity.getInputStream()); <== Lob is closed Exception The work around is to force fullyMaterializedLobData to true (see below) in the url, so that the DB2 JCC driver will materialize the whole Lob data into memory before returning to the application: <property name="openjpa.ConnectionProperties" value="DriverClassName=com.ibm.db2.jcc.DB2Driver,Url=jdbc:db2://localhost:50000/demodb:fullyMaterializeLobData=true;progressiveStreaming=NO;,Username=db2user,Password=passw0rd" />
          Hide
          Milosz Tylenda added a comment -

          Now the patch looks much better to me. I am just still curious why the instanceof checks are needed, I don't remember seeing them in other dictionaries which support LOB streaming.

          Show
          Milosz Tylenda added a comment - Now the patch looks much better to me. I am just still curious why the instanceof checks are needed, I don't remember seeing them in other dictionaries which support LOB streaming.
          Hide
          Fay Wang added a comment -

          OPENJPA-130-DB2-2.patch is attached in OPENJPA-130.

          Show
          Fay Wang added a comment - OPENJPA-130 -DB2-2.patch is attached in OPENJPA-130 .

            People

            • Assignee:
              Fay Wang
              Reporter:
              Fay Wang
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development