SAP SDI - Filter push down from Virtual Table to Oracle is not working

[srinivasareddym]

Hello All,

We are using SAP SDI 2.5.2, Oraclelogreader adapter to connect to Oracle. Product - SAP Commissions.

There is a table with more than 3 billion records. Table has a time field EXTRACT_DATE where filters can be applied to fetch data incremental way.

Observation:

Working scenario, where filter push down is working when checking M_REMOTE_QUERY_STATISTICS table REMOTE_STATEMENT_STRING

1. SELECT * FROM “VT_TABLES" where TRX_ID = 1443034440977; Filter push down is OK

2. SELECT TOP 100 * from "_TABLES" where "CREATION_DATE" = '2020-07-30'; Working fine, TOP removed not working.

Not Working Scenario: EXTRACT_DATE of type TIMESTAMP

2. SELECT TOP 10 * FROM VT_TABLES where EXTRACT_DATE = TO_TIMESTAMP('2023-06-01 01:19:22','YYYY-MM-DD HH24:MI:SS'); —WITH TOP 10 AND =

3. SELECT * FROM VT_TABLES where EXTRACT_DATE >= TO_TIMESTAMP('2023-06-01 01:19:22','YYYY-MM-DD HH24:MI:SS'); —WITH >=

When checked REMOTE_STATEMENT_STRING it is NULL and REMOTE_STATEMENT_STATUS is EXECUTING, but data transfer is not happening. When checked remote source capability CAP_BI_TO_TIMESTAMP is available too.

Question is filter on TRX_ID is working, but filter on TIMESTAMP fields is not going fine - this case not only filter push down but data fetch also not going fine.

Thank you in advance!

[srinivasareddym · Accepted solution]

I did some experiments and finally achieved with variable. Assign the timestamp value to the variable and pass the variable in WHERE condition. Though REMOTE STATEMENT STRING shows ? at M_REMOTE_STATEMENTS, it works.

[Yogananda]

Thank you for reading all my SDI blogs munagalasrinivasareddy ! 🙂

In below link, you will find list of functions. you can try to pick relevant to Date & time and query. TO_TIMESTAMP will not work.

https://help.sap.com/docs/HANA_SERVICE_CF/7c78579ce9b14a669c1f3295b0d8ca16/f12b86a6284c4aeeb449e57eb...

[srinivasareddym]

Hello Yogananda,

I tried with different combinations. For example

SELECT * FROM "VT_TABLES" WHERE "EXTRACT_DATE" = '2020-09-24'; --Converting as TIMESTAMP '2020-09-24 00:00:00.0000000'

But the performance is too bad when compared with no filter (no filter is actually giving better performance at least FETCHING point of view). Sometimes getting error too - Error: (dberror) [403]: internal error: Error opening the cursor for the remote database Prefetch timed out. for query

I tried with different settings dpagent.ini - -Xmx98304m, -Xms256m and -Xmx16384m, -Xms256m

If you have combination of dpagent.ini and dpagentconfig.ini can you please provide or suggest a way to overcome issues?

[Yogananda]

munagalasrinivasareddy

can you create a ticket for this and assign to component : HAN-DP-SDI... ?

[srinivasareddym]

Sure, Thank you.

[Yogananda]

munagalasrinivasareddy

could you check directly in Oracle db sql explorer on that table with extract_date once ? there could be difference between HANA and oracle date time format. could you also check that. Also can check the different hana functions for date time to filter to your need.,

[srinivasareddym]

Thank you for your reply Yogananda, I watch out for your blogs on SAP Commissions, helps a lot.

More Scenarios Tried:

Working Oracle - SELECT * FROM TABLES where EXTRACT_DATE = TO_TIMESTAMP('2020-09-04 14:30:59','YYYY-MM-DD HH24:MI:SS')

Working in SAP - SELECT top 10 * FROM "TABLES" where EXTRACTED_DATE = TO_TIMESTAMP('2020-09-04 14:30:59','YYYY-MM-DD HH24:MI:SS'); -- with TOP

Not working in SAP - SELECT * FROM "TABLES" where EXTRACTED_DATE = TO_TIMESTAMP('2020-09-04 14:30:59','YYYY-MM-DD HH24:MI:SS'); -- without TOP - here status in REMOTE_STATEMENT_STRING is NULL and status is EXECUTING, it is not changing to FETCHING.