Bugzilla – Full Text Bug Listing
| Summary: | setMaxRows generates incorrect SQL on DB2MainframePlatform, ROWNUMBER() OVER() | ||
|---|---|---|---|
| Product: | z_Archived | Reporter: | Oliver Michels <omichels> |
| Component: | Eclipselink | Assignee: | Nobody - feel free to take it <nobody> |
| Status: | NEW --- | QA Contact: | |
| Severity: | major | ||
| Priority: | P3 | CC: | creation_49221, gordon.yorke, martin.grebac, tom.ware |
| Version: | unspecified | ||
| Target Milestone: | --- | ||
| Hardware: | Other | ||
| OS: | other | ||
| Whiteboard: | db2 | ||
I am having the same problem, but it has worked before. (Meaning it used to use the SYSDUMMY1 file) It was even a bug in version 1.1: https://bugs.eclipse.org/bugs/show_bug.cgi?id=270881 All of a sudden all of the services using setMaxRows are no longer usable... and the log shows it trying to use ROWNUMBER() OVER(). Is there a workaround???? I would really like to get past this problem. I tested this, and just as I thought I have one query using setMaxRows and it completes fine, this other insists on using the ROWNUMBER() OVER() I have seen in the debug logs the SYSIBM.SYSDUMMY1 has been used before, but I can't figure out what change could have caused this issue. The error is below. javax.servlet.ServletException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.3.0.v20110604-r9504): org.eclipse.persistence.exceptions.DatabaseException Internal Exception: java.sql.SQLSyntaxErrorException: [SQL0255] Function not supported for query. Error Code: -255 Call: SELECT * FROM (SELECT * FROM (SELECT EL_TEMP.*, ROWNUMBER() OVER() AS EL_ROWNM FROM (SELECT IMITM# AS a1, IMACPG AS a2, IMADUFCT AS a3, IMADUFLD AS a4, IMBOLC AS a5, IMCAT# AS a6, IMCDSC AS a7, IMCDTE AS a8, IMCMDT AS a9, IMCNXT AS a10, IMCPAG AS a11, IMCQTY AS a12, IMCRLV AS a13, IMCSDT AS a14, IMCURL AS a15, IMCURM AS a16, IMCURO AS a17, IMCURS AS a18, IMCWO# AS a19, IMDESC AS a20, IMEOQ AS a21, IMFAHR AS a22, IMFLHR AS a23, IMGUAG AS a24, IMHGRV AS a25, IMHGT AS a26, IMHGTU AS a27, IMINSQ AS a28, IMINVT AS a29, IMIPRD AS a30, IMISG1 AS a31, IMISG2 AS a32, IMISG3 AS a33, IMISG4 AS a34, IMISG5 AS a35, IMLEVL AS a36, IMLGTH AS a37, IMLGTU AS a38, IMLIFL AS a39, IMLIFM AS a40, IMLIFO AS a41, IMLIFS AS a42, IMLOCN AS a43, IMLWDT AS a44, IMLWO# AS a45, IMLWQT AS a46, IMMASK AS a47, IMMGTY AS a48, IMMGVL AS a49, IMMTRL AS a50, IMOBS AS a51, IMOHND AS a52, IMORDD AS a53, IMORDI AS a54, IMPACT AS a55, IMPADD AS a56, IMPAVG AS a57, IMPCGA AS a58, IMPCLS AS a59, IMPCON AS a60, IMPDS# AS a61, IMPGLA AS a62, IMPGRP AS a63, IMPIDT AS a64, IMPIQT AS a65, IMPISL AS a66, IMPLIF AS a67, IMPLVL AS a68, IMPMIN AS a69, IMPNTN AS a70, IMPNTS AS a71, IMPNXT AS a72, IMPO# AS a73, IMPOID AS a74, IMPOLT AS a75, IMPORD AS a76, IMPOSL AS a77, IMPOUM AS a78, IMPSTD AS a79, IMPTAG AS a80, IMPUOM AS a81, IMPYTD AS a82, IMREV AS a83, IMROP AS a84, IMRSDT AS a85, IMSACT AS a86, IMSCGA AS a87, IMSCON AS a88, IMSDES AS a89, IMSGLA AS a90, IMSGMT AS a91, IMSGRP AS a92, IMSHMN AS a93, IMSHRS AS a94, IMSISL AS a95, IMSLIF AS a96, IMSMIN AS a97, IMSMTD AS a98, IMSOLT AS a99, IMSOSL AS a100, IMSOUM AS a101, IMSPLH AS a102, IMSRLV AS a103, IMSSTD AS a104, IMSTCD AS a105, IMSTDL AS a106, IMSTDM AS a107, IMSTDO AS a108, IMSTDS AS a109, IMSUBI AS a110, IMSUBW AS a111, IMSUOM AS a112, IMSYTD AS a113, IMUSED AS a114, IMVGRV AS a115, IMWDTH AS a116, IMWDTU AS a117, IMWGT AS a118, IMWGTU AS a119, IMYTDP AS a120 FROM IMF.IM10) AS EL_TEMP) AS EL_TEMP2 WHERE EL_ROWNM <= ?) AS EL_TEMP3 WHERE EL_ROWNM > ? bind => [2 parameters bound] Query: ReadAllQuery(referenceClass=Im sql="SELECT * FROM (SELECT * FROM (SELECT EL_TEMP.*, ROWNUMBER() OVER() AS EL_ROWNM FROM (SELECT IMITM# AS a1, IMACPG AS a2, IMADUFCT AS a3, IMADUFLD AS a4, IMBOLC AS a5, IMCAT# AS a6, IMCDSC AS a7, IMCDTE AS a8, IMCMDT AS a9, IMCNXT AS a10, IMCPAG AS a11, IMCQTY AS a12, IMCRLV AS a13, IMCSDT AS a14, IMCURL AS a15, IMCURM AS a16, IMCURO AS a17, IMCURS AS a18, IMCWO# AS a19, IMDESC AS a20, IMEOQ AS a21, IMFAHR AS a22, IMFLHR AS a23, IMGUAG AS a24, IMHGRV AS a25, IMHGT AS a26, IMHGTU AS a27, IMINSQ AS a28, IMINVT AS a29, IMIPRD AS a30, IMISG1 AS a31, IMISG2 AS a32, IMISG3 AS a33, IMISG4 AS a34, IMISG5 AS a35, IMLEVL AS a36, IMLGTH AS a37, IMLGTU AS a38, IMLIFL AS a39, IMLIFM AS a40, IMLIFO AS a41, IMLIFS AS a42, IMLOCN AS a43, IMLWDT AS a44, IMLWO# AS a45, IMLWQT AS a46, IMMASK AS a47, IMMGTY AS a48, IMMGVL AS a49, IMMTRL AS a50, IMOBS AS a51, IMOHND AS a52, IMORDD AS a53, IMORDI AS a54, IMPACT AS a55, IMPADD AS a56, IMPAVG AS a57, IMPCGA AS a58, IMPCLS AS a59, IMPCON AS a60, IMPDS# AS a61, IMPGLA AS a62, IMPGRP AS a63, IMPIDT AS a64, IMPIQT AS a65, IMPISL AS a66, IMPLIF AS a67, IMPLVL AS a68, IMPMIN AS a69, IMPNTN AS a70, IMPNTS AS a71, IMPNXT AS a72, IMPO# AS a73, IMPOID AS a74, IMPOLT AS a75, IMPORD AS a76, IMPOSL AS a77, IMPOUM AS a78, IMPSTD AS a79, IMPTAG AS a80, IMPUOM AS a81, IMPYTD AS a82, IMREV AS a83, IMROP AS a84, IMRSDT AS a85, IMSACT AS a86, IMSCGA AS a87, IMSCON AS a88, IMSDES AS a89, IMSGLA AS a90, IMSGMT AS a91, IMSGRP AS a92, IMSHMN AS a93, IMSHRS AS a94, IMSISL AS a95, IMSLIF AS a96, IMSMIN AS a97, IMSMTD AS a98, IMSOLT AS a99, IMSOSL AS a100, IMSOUM AS a101, IMSPLH AS a102, IMSRLV AS a103, IMSSTD AS a104, IMSTCD AS a105, IMSTDL AS a106, IMSTDM AS a107, IMSTDO AS a108, IMSTDS AS a109, IMSUBI AS a110, IMSUBW AS a111, IMSUOM AS a112, IMSYTD AS a113, IMUSED AS a114, IMVGRV AS a115, IMWDTH AS a116, IMWDTU AS a117, IMWGT AS a118, IMWGTU AS a119, IMYTDP AS a120 FROM IMF.IM10) AS EL_TEMP) AS EL_TEMP2 WHERE EL_ROWNM <= ?) AS EL_TEMP3 WHERE EL_ROWNM > ?") r Two possible workarounds: 1. Get the database platform and setShouldUseRownumFiltering(false) - Roughly: JpaHelper.getServerSession(entityManagerFactory).getPlatform().setShouldUseRowNumberFilters(false) 2. Subclass DB2MainframePlatform add add a call to setShouldUseRownumFiltering(false) in the constructor, then enable it by specifying the classname of the new platform with the persistence unit property "eclipselink.target-database" The fix is likely a combination of disallowing the row number filtering setter as suggested above and including setting it to false in the constructor The Eclipselink project has moved to Github: https://github.com/eclipse-ee4j/eclipselink The Eclipselink project has moved to Github: https://github.com/eclipse-ee4j/eclipselink |
Build Identifier: 2.0.0.v20091127-r5931 using setMaxRows generates SQL using ROWNUMBER() OVER(). This is not supported on the mainframe version of DB2. Tested on v8 on z/Os. I would suggest to change the implementation of DB2MainframePlatform to the following: public class DB2MainframePlatform extends DB2Platform { public DB2MainframePlatform() { super(); this.pingSQL = "SELECT COUNT(*) from SYSIBM.SYSDUMMY1 WHERE 1 = 0"; useRownumFiltering = false; } @Override public void setShouldUseRownumFiltering(boolean useRownumFiltering) { if (useRownumFiltering) { throw new IllegalArgumentException("unsupported on DB2MainframePlatform!") } } ... Reproducible: Always