SybasePagingQueryProvider's windowing approach does not work for Sybase ASE [BATCH-1580]

[spring-projects-issues]

Morten Andersen-Gott opened BATCH-1580 and commented

The windowing approach to paging in SybasePagingQueryProvider does not work for Sybase Adaptive Server Enterprise (ASE), only for Sybase Anywhere. Sybase ASE the product for larger enterprise system, while Sybase Anywhere is for smaller systems. Not sure if one should differentiate between ASE and Anywhere or create a PagingQueryProvider that works for both.

I came over this issue when using Spring Batch Admin and browsing the next 20 job executions. Stacktrace attached.

---

Affects: 2.1.1

Attachments:

• sybasepagingquery_stacktrace.txt (6.39 kB)

[spring-projects-issues]

Dave Syer commented

I don't have access to ASE. I could try and get it from somewhere, but it's not a high priority. Patches, and offers to run the integration tests against your platform would be appreciated.

[spring-projects-issues]

Morten Andersen-Gott commented

no problem assisting with the integration tests. as for patches, I'm trying to see if there is a good way to do paging queries on Sybase, haven't come up with one yet. So if you have any ideas, let me know, I can run them in the db directly before it is integrated into java code.

[spring-projects-issues]

Morten Andersen-Gott commented

Found one way to solve this, but I'm afraid it's not acceptable as a generic framework code and it might not be a high performer either.

A query provider that returns the following query for the SearchableJobExectionDao would work:

SELECT E.JOB_EXECUTION_ID AS SORT_KEY FROM BATCH_JOB_EXECUTION E, BATCH_JOB_INSTANCE I WHERE E.JOB_INSTANCE_ID=I.JOB_INSTANCE_ID AND(SELECT COUNT(*) FROM BATCH_JOB_EXECUTION E2 WHERE E.JOB_EXECUTION_ID > E2.JOB_EXECUTION_ID) BETWEEN :minRow AND :maxRow ORDER BY SORT_KEY

However, as a generic solution it is not good enough, as the SORT_KEY must be unique in the table for this strategy to always return the same resultset. Also, when it comes to performance I'm pretty sure it will be pretty awful on large datasets...

[spring-projects-issues]

Dave Syer commented

Agree that looks horrible, but I can't believe there isn't a native idiom for paging in ASE. Can't you find something in the docs?

[spring-projects-issues]

Morten Andersen-Gott commented

So far it doesn't look like it. Have asked the DBA and he didn't know of a way, neither did hours of googling. Reaching out to some Sybase experts now to see if they have some alternatives.

[spring-projects-issues]

Dave Syer commented

Maybe we can use that query to write a generic PagingQueryProvider as a fallback (and with the condition that the sort key is unique)?

[spring-projects-issues]

Morten Andersen-Gott commented

That's doable. Although I'm not comfortable with an implicit requirement for the sort key. I'll wait and see what the Sybase experts come up with, if they don't come up with anything I'll start working on a patch with that provided query as a template.

[cppwfs]

Do you have any updates on your research?