DB2 UDB 5.2 JDBC application driver failing on getBinaryStream() 
Author Message
 DB2 UDB 5.2 JDBC application driver failing on getBinaryStream()

Greetings,

I have been working on a Java servlet that needs to fetch some data from
(B)LOB fields.  I have written my servlet to keep a pool of PreparedStatement
objects (themselves contained in connection pools) around so I don't have
to constantly prepare statements over and over.  All queries are parameterized
SELECT statements; there are no updates, inserts or deletes done from the
servlet (a separate system is used to update the database content).

When only one or two LOB requests are open at the same time on the same
connection, things work just fine.  However, if the web server gets a bunch
of requests for LOB objects concurrently, DB2 throws an exception on some
of them.  The exception I'm getting is:

COM.ibm.db2.jdbc.app.DB2Exception: [IBM][CLI Driver][DB2/SUN] SQL0423N  LOB locator variable "1" does not currently represent any value.  SQLSTATE=0F001

The code I'm using to fetch the LOB data is really very simple:

    boolean haveData = true;
    String contentType = "";

    if (id != null) {
      if (id.thumbnail) {
        ps = amdsd.getPreparedPoolStatement(GETTHUMBNAILIMAGE);
      } else {
        ps = amdsd.getPreparedPoolStatement(GETFULLSIZEIMAGE);
      }

      if (ps == null) {
        response.sendError(HttpServletResponse.SC_INTERNAL_SERVER_ERROR);
        return;
      } else {
        try {
          try {
            ps.setLong(1, id.imageNumber);
            rs = ps.executeQuery();

            try {
              if (rs.next()) {
                contentType = rs.getString(1);
                if (rs.wasNull()) {
                  haveData = false;
                } else {
                  contentType = contentType.trim();
                }

                imageLen = rs.getInt(3);
                if (rs.wasNull() || imageLen < 1) {
                  haveData = false;
                }

                in = rs.getBinaryStream(2);
                if (in == null) {
                  haveData = false;
                }
              } else {
                haveData = false;
              }

              if (haveData) {
                /* First set header data! */
                response.setContentType(contentType);
                response.setContentLength(imageLen);

                /* Send the image data. */
                buffer = new byte[32768];

                out = response.getOutputStream();
                while ((lengthRead = in.read(buffer)) != -1) {
                  out.write(buffer, 0, lengthRead);
                  writtenLen += lengthRead;
                }
                out.flush();

                if (writtenLen == imageLen) {
                  /* Finally return OK. */
                  return;
                } else {
                  response.sendError(HttpServletResponse.SC_INTERNAL_SERVER_ERROR, "Datastream byte length mismatch (expected " + imageLen + ", had " + writtenLen + ")");
                }
              }
            } finally {
              in = null;
              rs.close();
            }
          } catch (SQLException sqle) {
            /* If we get a SQLException, indicate to the client something
               wrong happened. */
            response.sendError(HttpServletResponse.SC_INTERNAL_SERVER_ERROR, sqle.toString());
            return;
          }
        } finally {
          ps.release();
        }
      }
    }

I have also noticed that the JDBC driver appears to transfer the entire
LOB into memory when getBinaryStream() is called, at least for LOBs that
are a few hundred kilobytes in size.  I'd have expected it to read chunks
as necessary when one calls read() on the stream it creates.  While these
images are not huge, I will have some video streams in the future that
will be many many megabytes, and transfering the entire stream into
memory first would require the JVM to be started with an artifically high
maximum memory requirement.

Has anyone else really tried to use LOB data in the JDBC application driver
for DB2?  Experiences?  Comments?

My plan right now is to force LOB statements to be multually exclusive within
the connection they are allocated from and see if that eliminates the
exception.  However, that means my servlet is going to start opening up a
LOT more connections to the database server under even only moderate load...

I have seen this behavior both running the servlet in its target production
production environment (Solaris 2.6/SPARC with SunSoft Java kit, 1.1.6_04,
native threads with the JIT enabled) and in the development environment
(Borland JBuilder 2.01 on a Windows NT 4SP4 Workstation). The production server
runs the servlet via Apache JServ 1.0b1, the development environment is using
Sun's servletrunner.  Both production and development are using 5.2 level
drivers (e.g., 9084cae downloaded and installed clean on NT, while the
Solaris box is a UDB 5.0 upgraded to FP6 and then FP7).

As an aside, does anyone know details about how DB2 multiplexes statements
over a connection?  Assuming that this bug with multithreaded access to
LOBs on a single connection is fixed, would I still want to isolate LOB
access onto separate connections anyway because the transfer of the LOB
will stream until the entire LOB is done, locking out other statements from
fetching their result sets?  Or does it efficiently multiplex the statements
such that they each get a chance to send some data in chunks, and can't
monopolize the connection?  I have verified that the CAE only opens one
TCP connection per connection, unlike other vendors (like Oracle) that open
multiple TCP connections per logical connection to handle their multiplexing.

Thanks,
Chris Wichura



Thu, 21 Jun 2001 03:00:00 GMT
 
 [ 1 post ] 

 Relevant Pages 

1. Error in Java Program, when it accesses DB2 UDB 7.2 from DB2 UDB 5.2 Client

2. JDBC 2.0 with DB2/Solaris UDB 5.2

3. Restoring DB2 UDB 5.2 to UDB 7.2 question.

4. DB2 UDB 5.2 and Decimal Fields

5. Restoring DB2 UDB 5.2 to 7.2

6. Restore question under DB2 UDB 5.2 for Windows NT

7. DECIMAL columns with DB2 UDB 5.2 and MDAC 2.6

8. Anyone running DB2/UDB 5.2 CAE on RS/6000 P660 Model 6H1

9. DB2 UDB Version 5.2 FixPak 13 is available

10. DB2 UDB 5.2 MF Cobol usercall.c

11. DB2 UDB 5.2 and Decimal Fields

12. UDB 5.2 JDBC timestamp format different from java.sql.timestamp


 
Powered by phpBB® Forum Software