HANA - SQL for Flashback Query

[former_member212005]

Hi Team:

We are currently performing migration from Oracle to HANA database for one of the customers.

We have identified a custom code which is basically a native SQL executed through ADBC classes.

The purpose of that code is to obtain state of table at a particular time. Syntax is as below:

SELECT * FROM ' tablename ' as of timestamp ( SYSTIMESTAMP - interval' lv_minutes_back 'minute )' INTO l_sql_statement

This works well in Oracle database. However it gives a syntax error in HANA database....as expected.

However, I am finding it difficult to obtain a corresponding syntax which can work in HANA database.

I found a document as specified in link below...which states that HANA does not have Flaashback Query feature.

http://www.oracle.com/technetwork/database/availability/sap-hana-ha-analysis-cwp-1959003.pdf

However, this document is old and not sure whether it still holds true.

Could someone please confirm that HANA does not support such a feature?

I was able to find a statement "SET HISTORY SESSION TO <when>" which seems like it could work. I am trying the same now....and will update you of result....but if someone has already done this...then it will help.

Thanks in advance for reply.

[lbreddemann · Accepted solution]

The Oracle "Flashback Query" and the "Time-Travel Query" of SAP HANA are similar but still different functions.

Both revolve around the ability to see old version of records.

And this is about where the similarities end.

To enable this feature for SAP HANA column store tables, these tables have to be specifically created as history tables. It's not possible to activate this feature later on.

While it's certainly possible to create a "show-me-the-table-content-at-a-specific-point-in-the-past"-behavior with SAP HANA history tables, it is rarely the full extend of the actual use case.

And doing more advanced things like comparing different versions of records with each other are not possible.

As the feature is rather seldom used with Oracle as well, I'd recommend to review the application logic here and implement a time/validity concept in the data model directly.

[former_member212005 · Accepted solution]

Thank you Lars for clarifying!

[Former Member]

HANA EIM Core Data Services(CDS) with the Table Comparison / History Preserving Business Function Library and here is another blog on the same topic