Show me the timelines, baby!

Based on revision 46 (SPS 5) of SAP HANA Studio and Database

Hello once again!

While reading through the recently released SPS 05 RKT material (here) (that is, IF you have access to this RKT material), specifically the “What’ New Admin & Monitoring” document I found that the authors mentioned that the plan visualization feature we got in SPS 04 had been improved by adding an execution timeline to the display.

Most people that have been involved with HANA performance analysis already know this feature from the unsupported (but not yet superfluous) HDBAdmin tool.

When I read about this, I immediately checked where this new feature was hidden in my rev. 46 HANA studio and I did have to look for a while.

Thus I thought you might be interested to get the bread crumbs that lead to the nice feature.

Let’s start at the beginning …

The plan visualizer is available from multiple places in HANA studio.

The two places I know of are:

  1. any SQL window
    just mark your SQL statement and right-click to get the context menu -> there it is)
  2. the SQL Plan Cache data dump grid (Administration perspective -> Performance -> SQL Plan Cache)
    Again, right click on the line you want to work with gets you to the option.

For this example I just used option 1:

Visualize it!

The next thing you’ll see is a graphical version of explain plan, which is not really what you’re looking for in most of the cases.

You want more ūüėČ

And you get it.

Take an educated guess how…

Correct!

The path to enlightment through more information involves using the context menu again.

So, pimp up your right mouse-button click skills and right-click on the graphic and select “EXECUTE” from the context menu.

Now, the query is actually run in HANA and run time statistics are gathered.

Depending on the query this could take quite some time.

After some waiting you’ll get what you asked for, looking similar to this:

If you don’t see the timeline (lower part of the scree, red box around it… got it?) window now already, you’ve to manually open it.
To do so, use

“Window” menu
¬† -> “Show View”
¬†¬†¬†¬† -> “Other”
¬†¬†¬†¬†¬†¬† -> Folder “PlanViz”
¬†¬†¬†¬†¬†¬†¬† -> “Timeline”

You’ll notice that there are fancy little triangles in those blue-ish boxes upper right corner.

Click on these to expand the boxes and see what happens “inside” of them.

This will also expand the operations on the time line display automatically.

Finally we have this functionality in HANA Studio and we don’t need to create performance traces anymore, just to get an idea on how a statement was processed by HANA.

Nice!

Thanks for that dev-Team and have a great holiday season and a happy 2013!

Cheers, Lars

SPS 5 quick note ‚Äď on NULL values

Based on revision 45 (SPS 5) of SAP HANA Studio and Database

One thing I found a bit annoying with HANA had been the fact that it was not possible to go and change table definitions later on as easily as I was used to from other DBMS.

The change I wanted to implement was to add a NOT NULL constraint to a table already containing data.

Up to Revision 41 (the last SPS 4 revision), this was what happened:

Revision 41 – you know this old, outdated stuff… ūüėČ

  drop table aaa;
    create column table aaa (cola nvarchar(30) not null ,
                             colb integer ,
                             flag varchar(1) default ' ');
    insert into aaa values ('A', NULL, NULL);
    insert into aaa values ('A', 10, NULL);
    select * from aaa;
    COLA    COLB    FLAG
    A       NULL    NULL
    A       10      NULL

We’ve got a table, we’ve got data in it and there are NULL values in our table.

Let’s do something about this!.

    alter table "AAA" alter ("COLB" INTEGER NOT null);
¬†¬†¬† Could not execute 'alter table "AAA" alter ("COLB" INTEGER NOT null)' in 25 ms 571 ¬Ķs Started: 2012-12-13 12:23:43.
    SAP DBTech JDBC: [7] (at 25):
    feature not supported: NULL value exists: COLB: line 1 col 26 (at pos 25) 

Fair enough, there are NULL values in there, so I have to get rid of them before enabling the new constraint.

Let’s do that:

    update aaa set colb=0 where colb is null;
    select * from aaa;
    COLA    COLB    FLAG
    A       0       NULL
    A       10      NULL

Now, let’s retry to enable the constraint:

    alter table "AAA" alter ("COLB" INTEGER NOT null);
¬†¬†¬† Could not execute 'alter table "AAA" alter ("COLB" INTEGER NOT null)' in 29 ms 68 ¬Ķs Started: 2012-12-13 12:24:39.
    SAP DBTech JDBC: [7]: feature not supported: table must be empty to add NOT NULL column

“Feature not supported” Bummer!

Of course, at this point I could still create a new table with the constraint in place, copy over the data and the drop the original table.

But who on earth would want that?

Not me (so I opened a support message, asking for this feature to become supported…)!

Now we’re on SPS 5 (revision 45), everything is shiny and new, right?

Let’s see what happened to my request…

    drop table aaa;
    create column table aaa (cola nvarchar(30) not null ,
                             colb integer ,
                             flag varchar(1) default ' ');
    insert into aaa values ('A', NULL, NULL);
    insert into aaa values ('A', 10, NULL);
    select * from aaa;
     COLA    COLB    FLAG
    A       NULL    NULL
    A       10      NULL
   alter table "AAA" alter ("COLB" INTEGER NOT null);
¬†¬† Could not execute 'alter table "LARS"."AAA" alter ("COLB" INTEGER not null)' in 44 ms 528 ¬Ķs Started: 2012-12-13 12:27:17.
   SAP DBTech JDBC: [7]: feature not supported: NULL value exists: COLB: line 1 col 33 (at pos 32) 

Up to here no changes (good thing!) …

   update aaa set colb=0 where colb is null;
    select * from aaa;
    COLA    COLB    FLAG
    A       10      NULL
    A       0       NULL
   alter table "LARS"."AAA" alter ("COLB" INTEGER not null);
   Statement 'alter table "AAA" alter ("COLB" INTEGER NOT null)' successfully executed in 2.268 seconds
   Started: 2012-12-13 12:28:12 (server processing time: 2.245 seconds) - Rows Affected: 0 

This is A-amazing, isn’t it?

Really nice to now and have, especially if you finally decide to clean up your data model and get all the required constraints straight, that are required to keep data as clean as possible.

One more thing on NULLs…

One thing that comes play, when you actually DO allow NULLs in your database: how do you SORT the NULL value?

If your table contains NULLs in a column and you want to sort by this column, where would you want to see these NULLs?

At the end of the list? At the top?

Up to SPS4, there was just the default sort order available:

NULLs were dealt with as if they were smaller than the smallest value of the column.

So ‘naturally’ the place of the NULLs would be the top of the list (again, default order being ASCending).

While this is usually OK-ish if you know about it, there are for sure as much reasons to put NULLs at the end of the list as there are to put them to the top.

Starting with SPS5 you have the choice with that (see the docs here):

    insert into aaa values ('C', 5, 'F');
    insert into aaa values ('C', 5, NULL);
    select * from aaa;
    COLA    COLB    FLAG
    A       10      NULL
    A       0       NULL
    B       10      NULL
    C       5       F  
    C       5       NULL

NULL first please:

    select cola, colb, flag from aaa
    order by flag NULLS FIRST;
    COLA    COLB    FLAG
    A       10      NULL
    A       0       NULL
    B       10      NULL
    C       5       NULL
    C       5       F   

The other way ’round:

    select cola, colb, flag from aaa
    order by flag NULLS LAST;
    COLA    COLB    FLAG
    C       5       F  
    A       10      NULL
    A       0       NULL
    B       10      NULL
    C       5       NULL  

Attention though when counting/summing/aggregating!

    select cola, count(flag) from aaa
    group by cola
    order by count(flag) NULLS LAST;
    COLA    COUNT(FLAG)
    A       0            <<< NULLS treated as 0's (zeros)
    B       0         
    C       1         

Once again, that’s all folks!

Cheers,
Lars

Say cheese… on taking snapshots with SAP HANA

Based on revision 45 (SPS 5) of SAP HANA Studio and Database

As projects with HANA (just like with any other technology) move on, at some point in time the requirement to perform tests and trainings comes up.

If this is not the case in your project, please go and talk to your project responsible right away! ūüėČ

Stil here? Ok, so you’re actually want to perform some testing?

One important thing when performing tests is that you repeat them in the same environment (and maybe alter one thing at a time).

So, how do you achieve this with a database?

You restore a backup taken earlier, right?

Yeah, right!

That’s one way to do it.

A disadvantage of this approach is that it takes a lot of time as the whole database backup needs to be read from the backup medium and be written to the database storage.

Another way to deal with this requirement is to take snapshots of the database and set it back to a snapshot once required.

If you’ve worked with a virtual machine (e.g. VMWare, VirtualBox or Parallels) you know how snapshots work:

the system is ‘frozen’ for a moment, a snapshot is taken and afterwards you can continue to work with the system.

From that moment on you always have the option to ‘flash back‘ to the system state when you took the snapshot.

SAP HANA is offering a pretty similar functionality.

Those of you that know SAP MaxDB snapshots will clearly recognize the concepts.

The HANA implementation is quite close to the MaxDB feature.

SNAPSHOTS in HANA

So, I can take snapshots of my HANA instance?

Great, show me the button for that in HANA studio!

Unfortunately there is no frontend UI support for HANA snapshots (yet – no idea whether this is on the backlog for development).

Therefore, this little walktrough involves a bit of typing and reading…

But, wait a minute: where is all the old data stored then?

Ok, let’s answer this obvious question first. Similar to many filesystems and in fact very similar to MaxDB HANA’s persistency employs the shadow paging concept (http://en.wikipedia.org/wiki/Shadow_paging). That means that whenever a changed data page is written from memory to the disks (this is called a savepoint in HANA) the changed page is stored at a different physical position. The old page is not immediately overwritten. Instead the old version of the page remains in the storage until one of the subsequent savepoints overwrites it with newly changed data.

To keep track of which pages are current and which ones can be overwritten, HANA uses a mapping structure called the converter (hello MaxDB again).

Having these two concepts savepoint+converter in place, a snapshot is now easy to create.

All you’ve to do is to keep a specific state of the Converter and keep all pages referenced by this converter version from being overwritten.

And that’s exactly what happens within HANA when you take a snapshot.

Since savepoints can be recovered transactionally consistent by their design, HANA can simply switch back to a saved Converter version and resume working from there.

By knowing this, the answer to the question is easy to give: the data is still in the data area and won’t be overwritten.

Obviously, if you do change a lot of the data that is part of the snapshot, the data area usage will grow quite a bit, even if you’re only doing UPDATEs or DELETEs (funny effect, isn’t it? ūüôā ).

Well, then let’s see what the snapshot is like with HANA.

And off we go – my annotated HANA snapshot walkthrough:

The commands I use I found in our documentation:

   System Administration and Maintenance Information

SAP HANA Administration Guide

13.14 SQL Syntax for Backup and Recovery.

13.14.4 SQL Statements for Data Snapshot……………………………………188

Syntax

<execute_create_snapshot>::= BACKUP DATA CREATE SNAPSHOT

  <execute_drop_snapshot>::= BACKUP DATA DROP SNAPSHOT

Just there I also found this interesting remark that I will check out later in more detail:

¬†¬† “Examples of SQL Statements for Data Snapshot

BACKUP DATA CREATE SNAPSHOT

Create a database-wide snapshot based on a transactional consistent savepoint similar to the data backup.

If a snapshot exists, no complete data backup is possible.

Every request of this kind is rejected with a notification that a data backup is still in process.

BACKUP DATA DROP SNAPSHOT

Drop a database-wide snapshot. From this point in time on, complete data backups are possible.”

To start I setup a test table and fill it with some data.

I also make sure that the table is not fully merged, since I want to know whether the snapshot works with the change log of column tables as well

    (no commits here as AUTOCOMMIT = ON)
    drop table lars.snappy;
    create column table lars.snappy (col1 varchar(20));
    insert into lars.snappy values ('LARS');
    merge delta of lars.snappy;
    
    -- table created, one record in and merged to the main store
    -- now another record for the delta store:
    insert into lars.snappy values ('NORA');
    
    -- let's check what we've got:
    -- (I cut away the columns that I'm not interested in for readabilty)
    select * from m_cs_tables where table_name='SNAPPY';
    SCHEMA_NAME TABLE_NAME  RECORD_COUNT    RAW_RECORD_COUNT_IN_MAIN    RAW_RECORD_COUNT_IN_DELTA
    LARS        SNAPPY      2               1                           1

Now, I take a snapshot of this database.

    backup data create snapshot;
    Statement 'backup data create snapshot' successfully executed in 33.449 seconds
    Started: 2012-12-06 21:43:58 (server processing time: 33.428 seconds)
    - Rows Affected: 0

That was easy.

Noteworthy is that the time to take a snapshot is *not* related to the size of the database.

Instead it’s bound to how many changed pages have to be written out for the savepoint that is triggered.

Remember? A HANA snapshot is a ‘frozen’ savepoint.

Well, while we’re at it, we could also just check on this, shall we?

    select * from m_savepoints;
    HOST             PORT           VOLUME_ID          START_TIME                       STATE          VERSION          REQUESTED_FREQUENCY          TIME_SINCE_PREVIOUS          DURATION          CRITICAL_PHASE_DURATION          TOTAL_SIZE          FLUSHED_PAGES          FLUSHED_PAGES_IN_CRITICAL_PHASE          FLUSHED_ROWSTORE_PAGES          FLUSHED_ROWSTORE_PAGES_IN_CRITICAL_PHASE          FLUSHED_SIZE          FLUSHED_SIZE_IN_CRITICAL_PHASE          FLUSHED_ROWSTORE_SIZE          FLUSHED_ROWSTORE_SIZE_IN_CRITICAL_PHASE          RTT_SIZE
    vml3012          30003          2                  2012-12-06 21:43:59.017          DONE           85775            300                          171                          5592076           15801                            973209600           14637                  9                                        0                               0                                                 972029952             1179648                                 0                              0                                                0
    vml3012          30003          2                  2012-12-06 21:41:07.241          DONE           85774            300                          114                          22376             111                              2048000             8                      0                                        3                               0                                                 1507328               0                                       540672                         0                                                0
    [...]
    (RTT =  rollback transaction table - see RTT_SIZE column description for m_savepoints view)

The first line shows our snapshot savepoint.

Don’t ask me about the mismatch of DURATION (5592076 ms) and the reported 33.449 seconds, no clue on that.

Another system view to check out is – wait for it – m_snapshots:

    select * from m_snapshots;
    HOST             PORT           VOLUME_ID          ID             TIMESTAMP                        FOR_BACKUP          ANCHOR
    vml3012          30003          2                  85775          2012-12-06 21:43:59.017          TRUE                105553116266525
    vml3012          30005          3                  24610          2012-12-06 21:43:58.106          TRUE                105553116266559
    vml3012          30007          4                  69057          2012-12-06 21:43:58.194          TRUE                105553116266504

The avid reader will have noticed, that we took ONE snapshot and now we see THREE entried in our system view.

Upton closer look we find that these snapshots belong to different volumes of different services (notice the PORT cokumn).

This is already a hint to how snapshots are handled in our distributed system (distributed over separate services with separate persistencies eventually distributed over several nodes).

Being the old support guy I am, I also peek into the current indexserver trace file and find one line:

    [...]
    [8319]{0}[0] 2012-12-06 21:44:04.608995 i Logger       SavepointImpl.cpp(02077) : Snapshot 85775->85776, snapsp=85775, RTT size=0P+0O+0D
    [...]

Noticable here is apart from the savepoint number (85775->85776) the information that there is currently a snapshot currently present (snapsp=85775).

If no snapshot would be present we would find snapsp=0 there.

Finally, the backup.log file provides more information:

    [...]
    2012-12-06T21:43:58+01:00  P07589      13b71f5e371 INFO    BACKUP   SNAPSHOT started
    2012-12-06T21:43:58+01:00  P07589      13b71f5e371 INFO    BACKUP   state of service: nameserver, vml3012:30001, volume: 1, BackupPrepareSavepointInProgress
    2012-12-06T21:43:58+01:00  P07589      13b71f5e371 INFO    BACKUP   state of service: statisticsserver, vml3012:30005, volume: 3, BackupPrepareSavepointInProgress
    2012-12-06T21:43:58+01:00  P07589      13b71f5e371 INFO    BACKUP   state of service: xsengine, vml3012:30007, volume: 4, BackupPrepareSavepointInProgress
    2012-12-06T21:43:58+01:00  P07589      13b71f5e371 INFO    BACKUP   state of service: indexserver, vml3012:30003, volume: 2, BackupPrepareSavepointInProgress
    2012-12-06T21:43:58+01:00  P07589      13b71f5e371 INFO    BACKUP   state of service: xsengine, vml3012:30007, volume: 4, BackupPrepareSavepointFinished
    2012-12-06T21:43:58+01:00  P07589      13b71f5e371 INFO    BACKUP   state of service: nameserver, vml3012:30001, volume: 1, BackupPrepareSavepointFinished
    2012-12-06T21:43:58+01:00  P07589      13b71f5e371 INFO    BACKUP   state of service: statisticsserver, vml3012:30005, volume: 3, BackupPrepareSavepointFinished
    2012-12-06T21:44:03+01:00  P07589      13b71f5e371 INFO    BACKUP   state of service: indexserver, vml3012:30003, volume: 2, BackupPrepareSavepointFinished
    2012-12-06T21:44:03+01:00  P07589      13b71f5e371 INFO    BACKUP   state of service: nameserver, vml3012:30001, volume: 1, BackupSynchronizeSavepointInProgress
    2012-12-06T21:44:03+01:00  P07589      13b71f5e371 INFO    BACKUP   state of service: statisticsserver, vml3012:30005, volume: 3, BackupSynchronizeSavepointInProgress
    2012-12-06T21:44:03+01:00  P07589      13b71f5e371 INFO    BACKUP   state of service: xsengine, vml3012:30007, volume: 4, BackupSynchronizeSavepointInProgress
    2012-12-06T21:44:03+01:00  P07589      13b71f5e371 INFO    BACKUP   state of service: indexserver, vml3012:30003, volume: 2, BackupSynchronizeSavepointInProgress
    2012-12-06T21:44:03+01:00  P07589      13b71f5e371 INFO    BACKUP   state of service: nameserver, vml3012:30001, volume: 1, BackupSynchronizeSavepointFinished
    2012-12-06T21:44:03+01:00  P07589      13b71f5e371 INFO    BACKUP   state of service: statisticsserver, vml3012:30005, volume: 3, BackupSynchronizeSavepointFinished
    2012-12-06T21:44:03+01:00  P07589      13b71f5e371 INFO    BACKUP   state of service: xsengine, vml3012:30007, volume: 4, BackupSynchronizeSavepointFinished
    2012-12-06T21:44:03+01:00  P07589      13b71f5e371 INFO    BACKUP   state of service: indexserver, vml3012:30003, volume: 2, BackupSynchronizeSavepointFinished
    2012-12-06T21:44:03+01:00  P07589      13b71f5e371 INFO    BACKUP   state of service: nameserver, vml3012:30001, volume: 1, BackupFinishSavepointInProgress
    2012-12-06T21:44:03+01:00  P07589      13b71f5e371 INFO    BACKUP   state of service: indexserver, vml3012:30003, volume: 2, BackupFinishSavepointInProgress
    2012-12-06T21:44:03+01:00  P07589      13b71f5e371 INFO    BACKUP   state of service: statisticsserver, vml3012:30005, volume: 3, BackupFinishSavepointInProgress
    2012-12-06T21:44:03+01:00  P07589      13b71f5e371 INFO    BACKUP   state of service: xsengine, vml3012:30007, volume: 4, BackupFinishSavepointInProgress
    2012-12-06T21:44:03+01:00  P07589      13b71f5e371 INFO    BACKUP   state of service: nameserver, vml3012:30001, volume: 1, BackupFinishSavepointFinished
    2012-12-06T21:44:03+01:00  P07589      13b71f5e371 INFO    BACKUP   state of service: statisticsserver, vml3012:30005, volume: 3, BackupFinishSavepointFinished
    2012-12-06T21:44:03+01:00  P07589      13b71f5e371 INFO    BACKUP   state of service: xsengine, vml3012:30007, volume: 4, BackupFinishSavepointFinished
    2012-12-06T21:44:04+01:00  P07589      13b71f5e371 INFO    BACKUP   state of service: indexserver, vml3012:30003, volume: 2, BackupFinishSavepointFinished
    2012-12-06T21:44:04+01:00  P07589      13b71f5e371 INFO    BACKUP   SNAPSHOT finished successfully
    [...]

And since the snapshot command was bundled into the BACKUP DATA command, finding this information here kind of makes sense.

Maybe we even find something in m_backup_catalog (the system view used to track data backups) for our snapshot?

NOPE. NADA. RIEN.

If you ask me this is just consequent because a snapshot is all but a backup.

It doesn’t prevent you from loss of data by media failure.

You can’t store the backed up data somewhere else, somewhere safe.

A snapshot is not a backup.

The mixing up of these technically extremely close concepts on a semantic level (by using the same command to trigger both) is not the wisest design decision, if you ask me.

Even worse, due to the overlapping of both functions, there is also a severe limitation that comes into play, when you create a snapshot.

I mentioned it already above: once there is a snapshot, it’s not possible to take new data backups anymore!

WOW – this is not nice.

And this wasn’t the case with MaxDB.

To bad it really isn’t possible to run a data backup on top of a snapshot. If you use the backup wizard to start a new data backup, the wizard will present the following message:

    Backup of system HAN failed
    Could not start backup of system 'HAN'. '
    The state 'ManagerSnapshotExists' of the BackupManager does not allow the requested operation'

The same thing happens when you try to add another snapshot (multiple snapshots are supported by MaxDB, so why not try this?):

¬†¬†¬† Could not execute 'backup data create snapshot' in 26 ms 719 ¬Ķs Started: 2012-12-06 21:59:11.
    SAP DBTech JDBC: [2]: general error:
    Backup error: The state 'ManagerSnapshotExists' of the BackupManager does not allow the requested operation

To be honest, no idea why multiple snapshots and backups are not possible.

What is easy to see however, is that the converter structure is more complex than in MaxDB:

    select * from m_converter_statistics;
    HOST             PORT           VOLUME_ID          TYPE                        MAX_LEVEL          MAX_PAGENUMBER          ALLOCATED_PAGE_COUNT          ALLOCATED_PAGE_SIZE CREATE_SNAPSHOT_COUNT          DROP_SNAPSHOT_COUNT
    vml3012          30003          2                  StaticConverter             0                  16382                   4                             1048576             10                             9
    vml3012          30003          2                  TemporaryConverter          0                  0                       0                             0                   0                              0
    vml3012          30003          2                  RowStoreConverter           1                  688127                  94208                         1543503872          10                             9
    vml3012          30003          2                  DynamicConverter            1                  212990                  28270                         1818025984          10                             9
    vml3012          30005          3                  StaticConverter             0                  0                       0                             0                   10                             9
    vml3012          30005          3                  TemporaryConverter          0                  0                       0                             0                   0                              0
    vml3012          30005          3                  RowStoreConverter           0                  8191                    4096                          67108864            10                             9
    vml3012          30005          3                  DynamicConverter            0                  16382                   10113                         675201024           10                             9
    vml3012          30007          4                  StaticConverter             0                  0                       0                             0                   10                             9
    vml3012          30007          4                  TemporaryConverter          0                  0                       0                             0                   0                              0
    vml3012          30007          4                  RowStoreConverter           0                  8191                    4096                          67108864            10                             9
    vml3012          30007          4                  DynamicConverter            0                  16382                   23                            1863680             10                             9

In order to be able to backup data again, the existing snapshot (frozen savepoint) needs to be dropped/unfrozen:

    backup data drop snapshot;

After this command, you can take new backups and snapshots again.

Sadly, the error message when you try to drop a snapshot when there is no snapshot is cryptic, to say the least:

¬†¬†¬† Could not execute 'backup data drop snapshot' in 54 ms 338 ¬Ķs Started: 2012-12-11 13:42:44.
    SAP DBTech JDBC: [2]: general error:
    Backup error: The state 'ManagerIdle' of the BackupManager does not allow the requested operation 

Seriously, why not something more appealing like, I don’t know, “no snapshot present to drop“?

BACK TO THE PAST!

Okay, okay, enough looking around in the system.

Let’s change some data and return to the snapshot afterwards (I didn’t drop the snapshot during but after my tryout ūüėČ )

    insert into lars.snappy (select * from lars.snappy);
¬†¬†¬† Statement 'insert into lars.snappy (select * from lars.snappy)' successfully executed in 44 ms 380 ¬Ķs Started: 2012-12-06 22:17:03 (server processing time: 19 ms 481 ¬Ķs) - Rows Affected: 2
¬†¬†¬† Statement 'insert into lars.snappy (select * from lars.snappy)' successfully executed in 22 ms 763 ¬Ķs Started: 2012-12-06 22:17:06 (server processing time: 4 ms 60 ¬Ķs) - Rows Affected: 4
¬†¬†¬† Statement 'insert into lars.snappy (select * from lars.snappy)' successfully executed in 21 ms 419 ¬Ķs Started: 2012-12-06 22:17:07 (server processing time: 2 ms 507 ¬Ķs) - Rows Affected: 8
¬†¬†¬† Statement 'insert into lars.snappy (select * from lars.snappy)' successfully executed in 21 ms 435 ¬Ķs Started: 2012-12-06 22:17:07 (server processing time: 2 ms 658 ¬Ķs) - Rows Affected: 16

Now there’s some more data in our table.

    select * from lars.snappy;
    COL1
    ----
    LARS
    NORA
    LARS
    NORA
    LARS
    NORA
    LARS
    NORA
    LARS
    NORA
    LARS
    NORA
    LARS
    NORA
    LARS
    NORA
    LARS
    NORA
    LARS
    NORA
    LARS
    NORA
    LARS
    NORA
    LARS
    NORA
    LARS
    NORA
    LARS
    NORA
    LARS
    NORA

And of course:

    merge delta of lars.snappy;
    select * from m_cs_tables where table_name='SNAPPY';
    SCHEMA_NAME TABLE_NAME  RECORD_COUNT    RAW_RECORD_COUNT_IN_MAIN    RAW_RECORD_COUNT_IN_DELTA
    LARS        SNAPPY      32              32                          0

To get the old version of the database content back, I first have to shutdown the instance and then use a command line tool:

    -- shutdown via HANA studio
    -- logon to HANA server via SSH:

Tell the database to use the snapshot:

    hanadm@lxxxx:/usr/sap/HAN/HDB00> hdbnsutil -useSnapshot
    nameserver lxxxx:30001 not responding.
    opening persistence ...
    run as transaction master
    done.

For some reason the database is not online now, but has to be started manually…

    hanadm@lxxxx:/usr/sap/HAN/HDB00> HDB start
    StartService
    OK
    OK
    Starting instance using: /usr/sap/HAN/SYS/exe/hdb/sapcontrol -prot NI_HTTP -nr 00 -function StartWait 2700 2
    06.12.2012 22:20:49
    Start
    OK
    06.12.2012 22:23:13
    StartWait
    OK

Finally the instance is up and running again and I can look for my old data.

    select * from lars.snappy
    COL1
    ----
    LARS
    NORA
    
    select * from m_cs_tables where table_name='SNAPPY';
    SCHEMA_NAME TABLE_NAME  RECORD_COUNT    RAW_RECORD_COUNT_IN_MAIN    RAW_RECORD_COUNT_IN_DELTA
    LARS        SNAPPY      2               1                           1                        

In fact, this is how my data (and the whole database) looked like when I created the snapshot.

Welly, welly, welly, well – not too bad.

Is the snapshot still there now?

What would be your guess?

Mine, based on past experiences with VM-machines and MaxDB, was that the snapshot stays there until I drop it explicitely.

Is it the same with HANA?

Nope.

    select * from m_snapshots;
    ==> EMPTY <==
   

This is not too problematic.

You could still use the snapshots to re-generate the same database state over and over, for testing or training system purposes.

All you have to keep in mind is to take a snapshot again, immediately after you restarted the instance when you re-activated the former snapshot.

If you do use the return to snapshot functionality and continue working from there, make sure to create a data backup immediately as well, because the indexserver trace file warns us:

    [...]
    [19308]{0}[0] 2012-12-06 22:31:54.782171 w Logger       SavepointImpl.cpp(02091) : NOTE: BACKUP DATA needed to ensure recoverability of the database
    [...]

And that’s it once again folks.

Hope there was something in here for you.

Cheers,
Lars

Here are some links to documentation, references etc. that I used…

System view references: