Dear Min Chai.
We have the same system layout (DataServer on z/OS and JDBC on Linux).
As the JDBC server processes the “ORDER BY”, the collation sequence for Char-Fields follows the ascii rules.
Whereas the applications running on z/OS offer the EBCDIC collation sequence.
To solve this discrepancy, we implemented a “strange” workaround, using dynamic sql.
Step-1: We describe the sql statement.
Step-2: We check whether there is a Char Field in the result set.
If “YES”, then we parse the original sql statement and analyze the ORDER BY clause (if any).
If “ORDER BY” present, then we merge the the ORDER BY fields with the fields in the result set.
If “there is a ORDER BY Char Field which is element of the result set”, then we strip of the ORDER BY Clause.
ACE then will provide a result set which is “unsorted” (in terms of the “ORDER BY”).
We fetch all records of the result set and finally we do the sort on z/OS.
I enclosed a PDF to make myself understood.
For time being the described workaround works for us.
But, if you start the access different databases on different platforms (z/OS, LUW) in one SQL-statemt then you have to find an common collation sequence.
NewControlFlow.pdf (154 KB)