Real time during data integration

[Former Member]

Hi experts,

I have a need of real time on SAP IQ 16.

Here's my situation. Let's say I have two databases "HISTORY" and "REAL_TIME".

My HISTORY database is refreshed one time per day. My REAL_TIME database is refreshed every 5 minutes, with an ETL which loads bulk text files.

I got a reporting system on-top of SAP IQ which add the HISTORY to the REAL_TIME, in order to have all my information stored in a view.

Here's the main requirement :

1. My view can't be empty (So it's not possible to simply truncate my table and load the text file)

So my question is quite easy : how I can do all of this with SAP IQ, knowing the LOCK process and my requirement?

Thanks you for your advices!

Kind regards,

[Former Member · Accepted solution]

Hi

Since IQ implements snapshot isolation, deleting all rows in a table and loading it from a text file should not be a problem. I don't remember right now if TRUNCATE TABLE in IQ commits automatically, but DELETE of all rows surely not, and in IQ TRUNCATE TABLE and DELETE of all rows are very much similar performance-wise. So, you can just delete all rows from your staging table, load it with LOAD TABLE statement and only then commit the transaction. In the meanwhile, all other transactions will see the old content of the staging table. Even more, all transactions opened before yours will continue to see your old data until they commit/rollback. Anyway, SELECT queries will not be blocked by your activity on the staging table and they should not see your staging table empty at any point.

Hope it helps

Leonid Gvirtz

[Former Member · Accepted solution]

Thanks you for you answers!

My choice is to create a BEGIN TRAN / COMMIT TRAN instead of using RLV because, in my case, only one person will alter the data.

[saroj_bagai]

RLV

About In-Memory Row-Level Versioning - Administration: In-Memory Row-Level Versioning - SAP Library