Virtualization Tool to remediate old red Hat virtualization solution in OACS

GauravSingh

Hi Team,

We are being migrated from On-Prem OBIEE 12c to OACS Gen2 platform. We have a DAL (Data Access layer) layer (Redhat Jboss data Virtualization) which we connect from OBIEE RPD's connection pool using jdbc connection.

DAL layer connects to underlying databases like Oracle, API data sources and then on DAL layer it joins the datasets fetched from different databases in a sequential way.

For ex. in below example - DAL will fetch the data from DB1.Table1 basis on business dates filter and using makedep (make this as a dependent join) hint, result will pass to DB2.Table2 and only matched records will be fetched on DAL side. In this example, data from DB2 is dependent on DB1.Table1 data.

First Case - if DB1.Table1 table has 5 rows and DB2.Table2 table has 100K rows

The query planner will do the operations in sequence this time
(1) Fetch 5 rows from DB1.Table1
(2) Push the distinct equi-join values from DB1.Table1 into DB2.Table2 side using IN clause
(3) Fetch resultant rows from DB2.Table2 that matches the JOIN condition

Second Case - if DB1.Table1 table has 10k rows and DB2.Table2 table has 100K rows. DAL will automatically pass limited values to DB2.Table2 using batched inserts method. Some databases do not allow SQL statement bigger than certain size, have limits to values in the IN clause, limits to prepared bindings, etc. DAL takes care of it as well.

The query planner will do the operations in sequence this time
(1) Fetch 10k rows from DB1.Table1
(2) Insert the distinct equi-join values from DB1.Table1 into DB2.Table2 side using batched inserts (For ex. 1k values at one go)
(3) Fetch resultant rows from DB2.Table2 that matches the JOIN condition
(4) send rows back to user

from DB1.Table1 err
inner join /*+ makedep */ DB2.Table2 bk on bk.Table2.col1 = err.Table1.col1 and bk.Table2.col2 = err.Table1.col2
WHERE
(

err.Table1.col1 >= ('2024-08-21')
and err.Table1.col1 <= ('2024-08-22'))
)

Do we have any DAL equivalent solution from Oracle which we can connect from OACS RPD which has similar capability like Redhat DAL virtualization solution?