Doubts regarding History tables

[former_member212706]

Dear Experts,

     I have been trying to explore History tables in SAP HANA. I would like to know how and where the History data is stored?

Let me give an example, Say , i have a history table 'X' defined with a key and a data in it.

I insert 2 records

Insert into X values (1,1);

Insert into X values (2,2);

and i commit it.(Say, commit ID is aaa)

Then i update one record,

Update x set data = 4 where key = 2;

and i commit again (Say, commit ID is bbb)

Now when i select the data

SELECT *,"$validfrom$", "$validto$"

FROM X ('REQUEST_FLAGS'='ALLROWS');

It gives me three rows of data

Key Data validfrom  validto

1     1      aaa          ?

2     2      aaa          bbb

2     4      bbb          ?

What i would like to know is where and how these data are stored. Because when i try to look for the data in M_CS_TABLES i see the record count in HISTORY_MAIN and HISTORY_DELTA as zero. Is this History part not related ?

Please help me here,

Thanks in advance

Regards,

Rajesh.

[lbreddemann · Accepted solution]

Hello Rajesh,

the usage of "$validfrom$", "$validto$" and the ALLROWS request flags is not at all supported for usage outside of SAP.

These are pure developer options and the way these work is neither obvious not what meant to be functionally complete.

If you query data with these options, you could get transaction wise inconsistent data sets!

As of now history tables can be used with the timet-ravel SQL syntax described in the documentation (SELECT ...AS OF UTCTIMESTAMP or SET HISTORY SESSION ).

Accessing the history storage is not supported.

Technically there are separate main and delta store containers for the historic data tuples.

HANA manages the transition of tuples from the current containers to the history containers automagically in the background - very similar to the auto merge functionality.

cheers,

Lars

[former_member212706 · Accepted solution]

Dear Lars,

     Thanks a lot for the info. Can you please explain main and delta store containers for history data. I just wanted to know when/ how a data moves to history delta and when to History main?

Thanks in advance!!!

Regards,

Rajesh.

[lbreddemann · Accepted solution]

Hello Rajesh,

the implementation/conceptual details of the history tables are not disclosed so I can't go into much detail here.

As already mentioned a lot of the internal management is done during the merge process.

To specifically merge the history delta you can use the documented MERGE HISTORY OF... command.

The movement from current main/delta to history main/delta is performed during the merge of the current column table.

But again: this whole feature is not only undisclosed concerning implementation/conceptual details it's also (just like everything in HANA) subject to fundamental changes.

So, if you you're right now looking into this feature and you're not satisfied by the (few) options currently available, then my only advice for that would be: do include time dependency information explicitly in your data model.

In it's current state (and this is of course my personal opinion) history tables are more suited for a kind of "technical" history approach and not so much for business/end user history use cases.

That's one of the reasons why BW on HANA doesn't use history tables for it's own history objects (time-dependent data), but only for in memory optimized DSOs...

cheers,

Lars

[henrique_pinto · Accepted solution]

On the other hand, these information were made fairly public in sessions ALM360 of TechEd 2012. If you check the slides, there are a couple of these that explain these merge methods and all.

[lbreddemann · Accepted solution]

Henrique,

the stuff in the slides (created by my colleague Richard) don't contain anything more than what you would figure out by reading the documentation.

Specifically they don't include the unsupported features the OP asked about.

Feel free to add your own hands on experiences with history tables and their usage in products and projects.

cheers, Lars

[henrique_pinto · Accepted solution]

Yeah I didn't mean to imply it contained any unsupported information, just a general overview what is allowed on history tables.

[Former Member]

Adding to that,

If we check the M_CS_TABLES  some memory has occupied for the HISTORY MAIN and DELTA but no records exists in the hisorty main and delta.

Please help out.