migue/liferay-portal — pull request #97 by laszlocsontos (2012-12-12 20:28:11+00:00)
Title: LPS-28755 Implemented DB2's native paging and support for DB2 V10

Hi Miguel,

I'm sending this pull request to you since I've found your name in the "component hierarchy" list for being responsible for DB compatibility.

Liferay Portal threw SQL0104N exception when it was used with DB2 V10, however worked normally with DB2 V9.7.

The problem was in DialectDetector: DB2's version was being checked by the equal operator (==9) and for DB2 v10 Hibernate's default dialect was chosen which is buggy.

* DialectDetector was fixed.
* Liferay's own DB2Diaclect was enhanced to use native paging SELECT clauses: FETCH FIRST n ROWS ONLY and OPTIMIZE FOR n ROWS. For more details on the two aforementioned special SQL clauses, please see DB2's official documentation here: http://pic.dhe.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.admin.perf.doc/doc/c0055223.html
* Benchmark results showed, that using FETCH FIRST n ROWS ONLY and OPTIMIZE FOR n ROWS to query the results for the first page, improves execution time by ~30%.

For more details, please see LPS-28755 in JIRA.

Thanks,
Laszlo.

PS: I'm sitting here next to Zsigmond 3 aisles further from you.