We are in JDE 9.1 on i-series. We also have a Oracle DB where all the JDE tables are replicated and the data from i-series gets copied onto Oracle DB everyday. Keep in mind that the tables in Oracle DB were manually created and not created from JDE.

What we are trying to do here is to make our specific set of applications and UBEs access the Oracle DB directly. So we have got a separate Windows server to run the applications and UBEs and the OCMs were mapped to the Oracle DB for the required tables.

When we are trying to access the Oracle DB using the application/UBE running on the windows server, it's taking long execution times. Almost 15 times of what the same application/UBE accessing the i-series. We started analyzing and noticed that the optimizer is either not using the correct index/going for FULL table scan when accessing the tables on Oracle resulting in long execution times. When we looked at the SQL Query and the SQL binds, we have date values in the WHERE clause of the query and they are showing as NVARCHAR2(32) on the SQL binds. These corresponding date columns in Oracle DB are defined as NUMBER fields. We are of the assumption that because of the data types mismatch, optimizer is not able to pick the optimized index resulting in either a FULL table scan or a less optimized index.

Question here is when date values in the WHERE clause of the query coming from JDE are in Julian date format, why are they showing up as NVARCAHR2(32) on SQL binds?
How to resolve this situation so that performance improves and we see a better execution times.

Thank You!