How do I execute a 'time travel' query on HANA?

[Former Member]

First of all, I have read the SAP documentation. It simply says:

"<time_travel_clause> ::= AS OF [COMMIT ID|TIMESTAMP] [commit_id | timestamp]"

So I hoped I could manage that.

select count(*) from usr02

COUNT(*)

5675

select count(*) from usr02 as of timestamp '2012-04-23 12:00:00'

* 257: sql syntax error: incorrect syntax near "timestamp": line 1 col 34 (at pos 34) SQLSTATE: HY000

hdbsql T17=> select count(*) from usr02 as of timestamp to_timestamp('2012-04-23 12:00:00','YYYY-MM-DD HH24:MI:SS')

* 257: sql syntax error: incorrect syntax near "timestamp": line 1 col 34 (at pos 34) SQLSTATE: HY000

So what am I doing wrong? It looks like hdbsql or transaction ST04 don't like the "as of timestamp" clause. Is this feature already supported? Did anyone already manage to execute a time travel query? Any working examples would be highly welcome.

Looks like the syntax is more complicated than what the documentation says. So let's try it another way:

\a off

Auto commit mode switched OFF

hdbsql=> set history session to commit id 1695003

0 rows affected (28.776 msec)

select bname,trdat,ltime from from usr02 order by trdat,ltime

(The output indictes that no time travel has happened but current data is shown.)

The documentation says: "Non-history tables in restored sessions always show their current snapshot"

How can I find out what is a history table and what not?

Regards,

Mark

[Former Member · Accepted solution]

Hi Mark.

Try to create table as HISTORY first:

CREATE HISTORY COLUMN TABLE <table_name> etc

Regards,

-Vitaliy

[Former Member · Accepted solution]

Hi Vitaliy,

thanks for that hint! I didn't find that in the documentation. Somehow I hate to reverse engineer applications simply because the documentation is somewhat rudimentary, but looks like we have to live with that situation for a while.

My next question would be: How can I see whether a columnar table is a history table?
I elaborated this answer:

In table TABLES we see SESSION_TYPE='HISTORY'

Regards,

Mark

[rajarshi_muhuri · Accepted solution]

Hi

would I be able to mark a SLT replicated table as history table ..( from your above SQL , it does not seem so )

I have a thread open at http://scn.sap.com/thread/3216473 .. incase you care to answer in a new open thread .

[former_member198127 · Accepted solution]

Hi Mark,

While creating a table, you can choose to create it as one of the following types:

(http://help.sap.com/hana/html/sql_create_table.html#create_table_table_type)

• COLUMN
• ROW
• HISTORY COLUMN
• GLOBAL TEMPORARY
• LOCAL TEMPORARY

So your question "How can I see whether a columnar table is a history table?" goes invalid, since a table can be either columnar, or a history table. You'll be able to run the time travel queries only when the table type is 'history column'.

Hope this helps.

[lbreddemann · Accepted solution]

Mark,

spot on!

That's exactly where to check if a table supports history or not.

cheers,

Lars

[former_member198127]

Hi,

I have scribbled the details about time travel concept, with examples about running time travel queries in SAP HANA.

http://scn.sap.com/community/developer-center/hana/blog/2013/02/12/when-i-travelled-through-time-usi...

Actually this was the original discussion, where i got the idea about working something and then writing about Time Travel. So, thanks a lot, and do let me know your feedback about the same.