DB2 users...

gavin · **Posted:** Sat Sep 27, 2003 6:14 pm

I've just implemented query paging using rownumber() over() in DB2. I've tested this in DB2 8.1. Would someone please test this feature on DB2 6/7 for me? You need to call both setMaxResults()/setFirstResult() to make use of it.

bbonner · **Joined:** Fri Sep 12, 2003 12:40 pm **Posts:** 65

If you use use distinct in the query that has maxResult set, it blows up, it puts the code as:

Code:

select * from ( select rownumber() over() as row_, distinct ... ) as temp_ where row_ <= ?

I think distinct should come *before* the rownumber() over() as row_.[/code]

bbonner · **Joined:** Fri Sep 12, 2003 12:40 pm **Posts:** 65

rownumber() over() as row_ makes every row distinct. That being said, I don't think I can use FirstResults for queries with distinct. Since I need only the first x rows of the report, which doesn't translate into x rows of the result set. Dammit.

Anyway, I'd just put a bugaboo in the docs to let people know that this may not be the solution they need for paging.

Thanks,

gavin · **Posted:** Tue Nov 11, 2003 10:06 pm

Thanks. This will be fixed:

http://opensource.atlassian.com/project ... key=HB-464

loverde · **Posted:** Thu Dec 11, 2003 6:13 pm

This functionality is not working with the mainframe version of DB2 that we are using on my project (allegedly DB2 V7, but I'll try to dig up the exact version info). I am using the 2.1final release.

In the mean time, I just created a custom copy of DB2Dialect that returns false for "supportsLimit".

gavin · **Posted:** Thu Dec 11, 2003 7:06 pm

OK, we should at least mention that in the docs, if true.

loverde · **Posted:** Thu Dec 11, 2003 9:46 pm

This is the information I can glean so far. Still trying to get the exact version on the mainframe. (The select statement was improvised based on what Hibernate was generating, so please correct it if it was wrong.)

Here is the test code:

Code:

   public void testSelectRowNumberOver() throws Exception {
      DataSource ds = (DataSource) _lookup("jdbc/FssMidDataSource");
      assertNotNull("DataSource can't be null", ds);

      Connection conn = ds.getConnection();
      Statement stmt = conn.createStatement();

      DatabaseMetaData metadata = conn.getMetaData();
      _LOGGER.info(
         "databaseProductName: " + metadata.getDatabaseProductName());
      _LOGGER.info(
         "databaseProductVersion: " + metadata.getDatabaseProductVersion());
      _LOGGER.info("driverName: " + metadata.getDriverName());
      _LOGGER.info("driverVersion: " + metadata.getDriverVersion());
      _LOGGER.info("driverMajorVersion: " + metadata.getDriverMajorVersion());
      _LOGGER.info("driverMinorVersion: " + metadata.getDriverMinorVersion());

      String q1 = "select * from TFSDB01.SESN_STA_REF";
      String q2 =
         "select * from"
            + " ( select rownumber() over() as row_, this.*"
            + "   from TFSDB01.SESN_STA_REF this )"
            + " as temp_ where row_ <= 20";

      _LOGGER.info("executing first query [" + q1 + "]...");
      ResultSet rs1 = stmt.executeQuery(q1);
      rs1.close();

      _LOGGER.info("executing second query [" + q2 + "]...");
      ResultSet rs2 = stmt.executeQuery(q2);

      _LOGGER.info("closing resources...");
      rs2.close();
      stmt.close();
      conn.close();
   }

And the result:

Code:

INFO  common.DatabaseConnectivityTest - databaseProductName: DB2 
INFO  common.DatabaseConnectivityTest - databaseProductVersion: 07.01.0001 
INFO  common.DatabaseConnectivityTest - driverName: IBM DB2 JDBC 2.0 Type 2 
INFO  common.DatabaseConnectivityTest - driverVersion: 07.02.0000 
INFO  common.DatabaseConnectivityTest - driverMajorVersion: 7 
INFO  common.DatabaseConnectivityTest - driverMinorVersion: 1 
INFO  common.DatabaseConnectivityTest - executing first query [select * from TFSDB01.SESN_STA_REF]... 
INFO  common.DatabaseConnectivityTest - executing second query [select * from ( select rownumber() over() as row_, this.*   from TFSDB01.SESN_STA_REF this ) as temp_ where row_ <= 20]... 
DEBUG util.StringUtil - >exceptionToString = [COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2] SQL0104N  An unexpected token "(" was found following "".  Expected tokens may include:  ", FROM INTO ".  SQLSTATE=42601

   at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_SQLException(SQLExceptionGenerator.java:260)
   at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_SQLException(SQLExceptionGenerator.java:197)
   at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.check_return_code(SQLExceptionGenerator.java:448)
   at COM.ibm.db2.jdbc.app.DB2Statement.execute2(DB2Statement.java:449)
   at COM.ibm.db2.jdbc.app.DB2Statement.executeQuery(DB2Statement.java:369)
   at com.ibm.ejs.cm.proxy.StatementProxy.executeQueryCommon(StatementProxy.java:368)
   at com.ibm.ejs.cm.proxy.StatementProxy.executeQuery(StatementProxy.java:331)
   at ....

[/code]