1 2 3 48 Previous Next

SAP HANA and In-Memory Computing

720 Posts

One of the most stupid things I've done in my life was to create SAP Note 1651055 (SAP Service Market Place login required) and the script attached to that note. I'm very pleased to say that today I've released a new version of that script, and that it is also the final version. The last one. Terminally. For good. And all eternity. After all, the world was has been going to have been deleted in order to make room for a hyperspace bypass on a Thursday.

 

The backup script was created as a workaround for missing functionality, when there were no professional backup tools supporting the SAP HANA system. With the not-new-anymore BACKINT interface that has been adopted by all major backup management solutions, the script is in fact not needed anymore except for a few scenarios such as

  • HANA systems in a landscape without a good backup solution
  • Some people seem to want to use it for scheduling log backup deletion
  • Other reasons that may or may not be good.

 

The new version (version 04) of the script comes with the following major changes:

  • There was a change in the syntax in hdbsql which made the listing of log backups fail. The script has been adapted to the new syntax.
  • The script configuratoin has been moved to an external configuration file. Through this, the script itself is now system-independent. You can create configuration files for all your HANA systems and simply run the script via ./backup.sh --config-file=<file_name> [other options]
  • The script can now make use of HANA's SQL syntax for deleting from the backup catalog: BACKUP CATALOG DELETE ... - for details of that syntax read the admin guide or Lars Breddemann's and my book. This is offered in addition to the old functionality.
    The new command-line switch -cd (for catalog delete) implicitly invokes the BACKUP CATALOG DELETE command. It has to be paired with an option to specify a data backup ID: either --backup-id=<ID> or -od -cl as for the old option -ll. With -od -cl, the script determines the ID of the oldest data backup that still exists on disk in its original file system location.
    The BACKUP CATALOG DELETE command can delete only from the catalog, or also from the file system. This behavior can be customized in the script via an option in the configuraiton file.
  • We now clean up all temporary files (I think).
  • And most importantly: there are three switches cunningly hidden within the script that will stop the script from working unless one explicitly acknowledges that the script is not SAP-supported software; that one may not create SAP support tickets related to the script; and that there will be no further development on the script. If you run into trouble using the script, this scn blog post may be a good location for help. But please do not e-mail me; and please do not create SAP support tickets related to the backup script.

 

Have a good thanksgiving or - if that doesn't mean anything to you - have fun,

Richard

This blog post is cross posted here

I’m an SAP guy—always have and most likely always will be. It’s been an adventure hacking and implementing their software across a multitude of great companies. I’ve seen SAP transform from a closed company with little innovation to a company where developers thrive and innovation is a cornerstone of future thinking. Where until a couple of years ago there was no true platform to build innovations on, today there is, and she is called HANA. After stumbling upon Estimote Beacons, I’m convinced that SAP HANA and Estimote are a match made in heaven.

image

First, what is SAP HANA? HANA (“High-Performance Analytic Appliance”) is an in-memory,column-oriented, relational database management system. HANA has a built-in web server (XS engine) which can turn any table into a RESTful service with only a few lines of code. Therefore, exposing data to the outside world in a unified way (JSON/XML) becomes a piece of cake. Also, HTML5 applications (in the SAP world referred to as UI5) can be created on the HANA platform itself and act as a thin client between the database and its (mobile) user.

It was little over a year ago when I laid eyes on Estimote Beacons for the first time. They bridge the gap between physical data-rich spaces and our day-to-day interactions within them. Beacons are the mechanism to glean this intelligence and HANA provided a place to store and process it.

Here is an example of an innovation my team and I built combining these two technologies.

HEMR – HANA Electronical Medical Record

image

The healthcare space is another area ripe for innovation. We built an app which can identify patient data when and where medical professionals need them: again with HANA working under the hood, and Estimote Beacons providing proximity features. When the patient is in vicinity of a beacon, her records are pulled from SAP HANA and doctors can add new information to the database with a push of a button. Since every patient is assigned a unique beacon, incorrect patient identification and records are no longer a problem. We can also keep track of our patients’ location by using Estimote Indoor Location software. Imagine patients wandering the hospital—now we can find them easily using the SDK’s mapping capabilities.

Estimote recently activated accelerometer and temperature sensors in their beacons. This opens up opportunities to extrapolate events in the hospital environment. With the HEMR app for example, we can measure patient movement and correlate it to ailments like bed sores. Also, the warmer the room, the less the patient is likely to move, increasing the risk of severe injuries: this is where the thermometer that’s built into beacons comes in handy. Lastly, we can also track abnormal patient movement: if a patient falls, an alarm will sound.

The following YouTube video runs through these use cases and provides a deep dive into the analytics of Estimote/HANA data.

I hop the HEMR app has showcased the innovative solutions that can come out of combining Estimote and SAP HANA. You can get started by downloading the Estimote SDK and by using the HANA Cloud Platform.

image

The NetBackup for SAP HANA Agent integrates the backint interface for SAP HANA along with the backup and the recovery management capabilities of NetBackup. The software works in single as well as multi-node environments. Below are the list of activites that you need to perfrom in the HANA Studio for netbackup configuration.

 

1. Create hdbbackint soft link from /usr/sap/<SID>/SYS/global/hdb/opt/hdbbackint to /usr/openv/NetBackup/bin/hdbbackint_script for every database instance.

2. The parameter file (initSAP.utl) must be specified for data in the SAP HANA database instance configuration. To specify the parameter file, go to

     Instance->Configuration-> global.ini > data_backup_parameter_file.

3.  The parameter file (initSAP.utl) must be specified for logs in the SAP HANA database instance configuration. To specify backup using the parameter file,

    go to Instance->Configuration->global.ini-> log_backup_parameter_file.

4. To specify backup using the backint file, go to Instance->Configuration->global.ini-> log_backup_using_backint->SYSTEM = true.

 

After setting up the above configuration . The HANA studio -> configuration ->global.ini file should look like the below

 

Capture.PNG

Performing backups and restores of SAP HANA using backint:

 

Use the SAP HANA studio to backup the SAP HANA data. The following steps help you to setup the backup. Setting up the backup using the SAP HANA studio

1 On the SAP HANA studio interface, select the HANA instance that you want to backup.

2 Right-click on the instance and select the Back Up option.

3 The Backup of System screen is displayed. Select the Backint option. Specify the Backup Destination and Backup Prefix as shown and click Next.

backint.PNG

4 Confirm the settings under Review Backup Settings and click Finish.

5 Check the Activity Monitor of the NetBackup Administration Console to get the backup information. On a successful backup the results will be displayed


Usefull Notes:

 

 

OSS 1986958 - Common issues / Configuration recommendations for Symantec NetBackup for SAP HANA

 

OSS 1913568 - Support Process for Symantec NetBackup

 

P.S: Incase of backup failure you need to analyze the backint.log &backup.log files.

 

Regards,

Pavan Gunda

I recently got into a discussion regarding the 2 Billion row 'limit' for HANA tables / partitions and when this limit might be removed.

In fact this is not a 'limit' or a 'bug' but rather its intrinsic to how HANA works. Erich suggested that I transfer the information to a blog which I've been happy to do.

 

As we know HANA is a primarily a column store, that is we expect to see the bulk of the data we hold held as large in-memory column stores.

 

At certain times we need to input or to reconstruct rows of data, and we need to keep track of which where the corresponding attributes of a row occur within the separate columns.


We define our ‘rows’ not by using keys for rows and holding all the attributes together juxtaposed physicall as a row,  but rather by their position within the column – so that we can materialise the N’th row by picking up the column attributes at the N’th position in each column.

For this we need to hold a position value – and we’ve chosen 32 bits to do this , with one bit reserved to mark ‘deleted. rows This gives us just over 2Bn values to represent a row position (the 2 Bn value will be familiar with anyone familiar with powers of two, as 2**31 = 2,147,483,648).


As my colleague Ruediger Karl pointed out we can regard this as a separate data type; the data type that defines the row position of a 'row' in our table.

 

This looks like a good tradeoff between the the number of rows we can keep in a table / partition and the space occupied.

If we were to increase the limit, say to 33 bits, then this would be pretty awkward, as we’d most likely have to take at least another 8 bits, maybe even 16 or 32 – otherwise we have values that don’t align to word boundaries. But taking just 4 bytes gives us a good range of values to play with.


Consider what happens when we are doing a query and scanning a column to find values that qualify in a WHERE clause. Most of the values will be filtered out with rows, possible scattered throughout the table being passed on in an intermediate result ready to be combined with other intermediate results. At some point we will reach the end of our query, and at that point we need to materialise our results and pass the results back to the user. At that point we will likely need to pick up other column values from our table that correspond to the positions of the filtered values.


That is, we may have scanned the ACCOUNT_TYPE column of our ACCOUNT table to find all those of type 'A, D or E' then examine the ACCOUNT_AMT column of the same table to find those with values of > 100,000 Euro, then return the account number, holders name and account name.


At each stage we need to keep track of the positions of the values we're dealing with, that is when we've found the positions in the ACCOUNT_TYPE column we need to remember these and inspect the corresponding positions in the ACCOUNT_AMT column to check their values. We'll then end up with a list of positions in the table that qualify for both criteria, and we'll then go into ACCOUNT_NUMBER, ACCOUNT_HOLDER_NAME and ACCOUNT_NAME columns to pick up the corresponding values in those columns. In order to do this we clearly need to keep track of the row positions - and that's the job of this internal data type which allows us to carry forward not just the values we found qualified, but the positions in the table where we found them.

 

This is not really a limit because we can divide any column table into partitions, as many as we like pretty much and this 2Bn limit applies to each partition, not to whole tables, so if we need more than 2Bn rows then all we need to do is partition the data into two or more partitions. Note that this applies whether or not we have a single server HANA cluster or a multi-server cluster. For a multi-server cluster we must partition the table so we can put one or more partitions on each server in order to spread the data. But here again we may discover that we need to put more than one partition on each server since we need to hold more than 2Bn rows per server. For example, a little while ago I worked on a project where we had 60 Bn rows spread over six HANA servers, therefore we needed at least 60 / 2 / 6 = 5 partitions per server. In practice we have many more partitions per server because we didn't want to have the partitions up against their limit.


As an aside this is not just about the absolute storage limit, but also about performance and operational considerations. I’ve read that for performance a recommendation is to have not more than 100m rows per partition – so to hold 2bn rows you might want 20 partitions of 100m rows each. But depending on the workload, it may not be necessary to go for 20 partitions since one of the things HANA does is dynamically subdivide partitions into sub ranges and assign different tasks to each subrange – so it can increase the parallelism even if the table hasn’t been explicitly subdivided.  If so then 2 or 3 partitions would be sufficient, say if the table was seldom updated. As ever 'it depends'.

 

Operationally we also need to consider if the tables are being updated having one big table gives problems with doing a MERGE after updates, where we merge the updates that have arrive,and are being held in the delta store with the main table. The way HANA does this is to replicate the partion (or whole table if it’s a single partition) to create a new version. If they have one 2Bn partition then we'd need to replicate all of that into the new version – ie. They have 4Bn rows ‘in flight’ during the operation. But if we partition the data into multiple partitions, say of 100m rows each then they can do these one at a time on a rolling basis – so we only need to replicate 100m rows at any one time, that is we just need to replicate 5% of the data at any one time, not 100%. This consumes less resource as well as smoothing resource consumption.


So, when we need to hold more than 2Bn rows, we simply partition the table and each partition can use a full 2Bn rows, as we said in practice we'll probably want the parititon to hold less than this in each partition.


For the mathematically inclined, another way to think about this is if we want to exceed the 2Bn limit then we'll need more than our 32-1 bits to represent all the rows that might be processed, but we can create these by adding the partition number to the front of our row position, if we like we can have hundreds of partitions if we wish and identify any row position with the 'Partition | RowId' combination.


The use of fixed position within the column store is fundamental to enabing the use of column stores, and HANA's clever delta merge process allows us to keep track of and adjust all our row positions as our partitions have rows added and deleted.


For those interested in knowing more this topic is extensively coverred in Hasso's book:

A Course in In-Memory Data Management: The Inner Mechanics of In-Memory Databases: Amazon.co.uk: Hasso Plattner: 9783642…


and also in the In-Memory Data Management Course offered by the Open HPI

https://open.hpi.de/courses

 


Cheers, H

In the first part of this blog, we modeled the logic of game of life using a calculation view and  a decision table, In this part let us expose the decision table to a front end UI using XSJS and create an interactive game as shown below.

 

UI_Cellgame.png

 

Wrapper SQL Script :

 

With just a decision table , we will not be able to generate consecutive generations of cells . To do so, we have to create a wrapper SQL script over the decision table. which will get the results from the decision table and update the source table ( cellgame table ).So, As and when the SQL Script Procedure is executed , cells for next generation will be updated to the cellgame table..

 

CREATE PROCEDURE "HANA_GARAGE"."PLAY" () LANGUAGE SQLSCRIPT AS

BEGIN

 

CREATE GLOBAL TEMPORARY TABLE temp_table AS ( SELECT X,Y,S_NEW AS S FROM "_SYS_BIC"."garage.gameoflife/GAME_RULES_DT/RV" ) WITH DATA;

 

UPDATE "HANA_GARAGE"."CELLGAME" SET s = temp_table.S FROM "HANA_GARAGE"."CELLGAME",temp_table WHERE "HANA_GARAGE"."CELLGAME".X = temp_table.X AND "HANA_GARAGE"."CELLGAME".Y = temp_table.Y;

 

DROP TABLE temp_table;

 

END;

 

In the above SQL Script procedure , we store the results of the decision table in a Global temporary table and we use the temporary table to update the values in the source table ( CELLGAME table ). So, whenever the procedure is called , the source table ( CELLGAME  table ) is updated with next generation cells.

 


XSJS


The following server side javascript calls the procedure created in the previous step and the result of the source table is returned as a JSON.

 

var procedureCallSql = 'CALL "HANA_GARAGE"."PLAY"',

sqlSelect = 'SELECT * FROM "HANA_GARAGE"."CELLGAME"',

init = $.request.parameters.get("initial");

 

 

function close(closables) {

    var closable;

    var i;

    for (i = 0; i < closables.length; i++) {

              closable = closables[i];

              if(closable) {

                        closable.close();

              }

    }

}

 

 

function getCellValue(){

    var cells = [];

    var connection = $.db.getConnection();

    var statement = null;

    var resultSet = null;

    try{

              statement = connection.prepareStatement(sqlSelect);

              resultSet = statement.executeQuery();

              while (resultSet.next()) {

                        var cell = {};

                        cell.x = resultSet.getString(1)

                        cell.y = resultSet.getString(2);

                        cell.s = resultSet.getString(3);

                        cells.push(cell);

                        }

    } finally {

              close([resultSet, statement, connection]);

    }

    return cells;

}

 

 

function doGet() {

    try{

              $.response.contentType = "application/json";

              $.response.setBody(JSON.stringify(getCellValue()));

    }

    catch(err){

              $.response.contentType = "text/plain";

              $.response.setBody("Error while executing query: [" + err.message + "]");

              $.response.returnCode = 200;

    }

}

 

function callProcedure(sqlQuery){

    var connection = $.db.getConnection();

    var statement = null;

    var resultSet = null;

    try{

              statement = connection.prepareCall(sqlQuery);

              resultSet = statement.execute();

    } finally {

              close([resultSet, statement, connection]);

    }

    doGet();

}

 

if(init==1){

  doGet();

}else{

  callProcedure(procedureCallSql);

}



UI.

 

To visualize the output of the xsjs , we create a HTML page as shown below,

 

<!DOCTYPE html>

 

<html lang="en">

  <head>

    <title>Game Of Life</title>

<!--     <link href="style.css" rel="stylesheet">

-->

<style>

body {

  background: #a0a0a0;

  padding: 20px;

}

 

table {

  margin: 0 auto;

  border-collapse: collapse;

  background: black;

}

 

td {

    width: 40px; height: 40px;

    background: white;

    border: 1px solid black;

}

 

td.live{

  background: black;

  border: 1px solid white;

}

 

button{

display:block;

margin:0 auto;

margin-top:20px;

}

</style>

<script src="http://ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js"></script>

<SCRIPT TYPE="text/javascript">

 

 

$( document ).ready(function() {

 

var result = [];

tdTag = "<td"

tdTag2 = "></td>",

tdTagBlack = '<td class="live"></td>',

htmlTag = "",

trOpen = "<tr>",

trEnd = "</tr>";

 

 

$.get("/garage/gameoflife/play.xsjs?initial=1",function(data){

  result = data;

  console.log(result);

  for(i=3;i>=1;i--){

   htmlTag+= trOpen;

   for(j=1;j<=3;j++){

     htmlTag+= tdTag + " id='"+j+""+i+"'"+tdTag2;

     console.log(j,i);

   }

   htmlTag += trEnd;

  }

 

  var tableTag = $("table");

  tableTag.append(htmlTag);

  for(n=0;n<result.length;n++){

   var x = parseInt(result[n].x),

   y = parseInt(result[n].y),

   s = parseInt(result[n].s);

   if(s>0){

     var identifier = "td#"+x+""+y;

     $(identifier).addClass("live");

   }

  }

});

 

$("button").click(function(){

   $.get("/garage/gameoflife/play.xsjs?initial=2",function(data){

   result = data;

   for(n=0;n<result.length;n++){

       var x = parseInt(result[n].x),

       y = parseInt(result[n].y),

       s = parseInt(result[n].s);

       var identifier = "td#"+x+""+y;

       $(identifier).removeClass("live");

 

     }

 

     for(n=0;n<result.length;n++){

       var x = parseInt(result[n].x),

       y = parseInt(result[n].y),

       s = parseInt(result[n].s);

       if(s>0){

         var identifier = "td#"+x+""+y;

         $(identifier).addClass("live");

       }

 

     }

 

  });

 

});

 

});

</SCRIPT>

</head>

<body>

<table>

</table>

<button type="button">Play</button>

</body>

</html>

Here we use jQuery Get function to request the JSON from play.xsjs file. The resulting JSON is displayed as cells using javascript and CSS. Whenever the play button is clicked, a request is sent to play.xsjs and procedure is called by the xsjs . then the next generation cell pattern is passed to the UI in JSON format. UI creates cells based on the JSON result from xsjs .

 

Result:

 

Whenever the Play button is clicked , next generation cells are generated and displayed in the UI as shown below.

 

UI_Cellgame_2nd_gen.png

Sometimes people think that because HANA is a columnar database, it doesn't run fast for simple OLTP operations. I was just looking at a performance problem with class /IWBEP/CL_MGW_ABS_MODEL, method GET_LAST_MODIFIED.

 

This had some screwy ABAP, which is incidentally fixed in SAP Note 2023100 (thanks Oliver Rogers for finding the note), and it generated the following SQL:

 

SELECT "PROGNAME", "CDAT" AS c ,"UDAT" AS c ,"UTIME" AS c

FROM "REPOSRC"

WHERE "PROGNAME" LIKE 'CL_ABAP_COMP_CLASS%' AND "R3STATE" = 'A'

ORDER BY "CDAT" ,"UDAT" ,"UTIME"

 

That SQL is pretty nasty, because it does a wildcard search on a big table. On the non-HANA system it was running in 20 seconds. I did the root cause analysis in the database and found that it was searching the primary clustered index, which was 98% fragmented.

 

Obviously I rebuilt the index - these are the results.

 

CPU time = 2750 ms,  elapsed time = 2746 ms.

CPU time = 2594 ms,  elapsed time = 2605 ms.

CPU time = 2750 ms,  elapsed time = 2764 ms.

 

I realized at this point this was some bad coding, so I found the fix thanks to Oli and we put the change in. That fixed the performance problem.

 

But then I thought... what happens if you run this bad query on a HANA system? This is just what custom code looks like a lot of the time...

 

Statement 'SELECT "PROGNAME", "CDAT" AS c ,"UDAT" AS c ,"UTIME" AS c FROM "REPOSRC" WHERE "PROGNAME" LIKE ...'

successfully executed in 12 ms 414 µs  (server processing time: 11 ms 613 µs)

Fetched 8 row(s) in 0 ms 68 µs (server processing time: 0 ms 0 µs)

 

Statement 'SELECT "PROGNAME", "CDAT" AS c ,"UDAT" AS c ,"UTIME" AS c FROM "REPOSRC" WHERE "PROGNAME" LIKE ...'

successfully executed in 9 ms 778 µs  (server processing time: 9 ms 136 µs)

Fetched 8 row(s) in 0 ms 64 µs (server processing time: 0 ms 0 µs)

 

Statement 'SELECT "PROGNAME", "CDAT" AS c ,"UDAT" AS c ,"UTIME" AS c FROM "REPOSRC" WHERE "PROGNAME" LIKE ...'

successfully executed in 12 ms 677 µs  (server processing time: 11 ms 830 µs)

Fetched 8 row(s) in 0 ms 56 µs (server processing time: 0 ms 0 µs)

 

So anyDB is averaging 2705ms, and HANA is averaging 10.86ms, an average speedup of 249x.

 

You may be saying... OK well that's for poorly written SQL - what about when it was optimized. Sure, let's test in that scenario. Here's the SQL:

 

SELECT "PROGNAME", "CDAT" AS c ,"UDAT" AS c ,"UTIME" AS c

FROM "REPOSRC"

WHERE

"PROGNAME" IN ('CL_ABAP_COMP_CLASS============CCDEF', 'CL_ABAP_COMP_CLASS============CCIMP', 'CL_ABAP_COMP_CLASS============CCMAC', 'CL_ABAP_COMP_CLASS============CI', 'CL_ABAP_COMP_CLASS============CO', 'CL_ABAP_COMP_CLASS============CP', 'CL_ABAP_COMP_CLASS============CT', 'CL_ABAP_COMP_CLASS============CU')

AND "R3STATE" = 'A'

ORDER BY "CDAT", "UDAT", "UTIME"

 

So ran it on anyDB, I couldn't get accurate results from the SQL console so I had to use the ABAP trace to get the numbers. They were 5.504ms, 1.484ms, 4.605ms for an average of 3.86ms. Let's see how HANA compares.

 

Statement 'SELECT "PROGNAME", "CDAT" AS c ,"UDAT" AS c ,"UTIME" AS c FROM "REPOSRC" WHERE "PROGNAME" IN ...'

successfully executed in 1 ms 977 µs  (server processing time: 1 ms 156 µs)

Fetched 8 row(s) in 0 ms 63 µs (server processing time: 0 ms 0 µs)

 

Statement 'SELECT "PROGNAME", "CDAT" AS c ,"UDAT" AS c ,"UTIME" AS c FROM "REPOSRC" WHERE "PROGNAME" IN ...'

successfully executed in 1 ms 946 µs  (server processing time: 1 ms 250 µs)

Fetched 8 row(s) in 0 ms 60 µs (server processing time: 0 ms 0 µs)

 

Statement 'SELECT "PROGNAME", "CDAT" AS c ,"UDAT" AS c ,"UTIME" AS c FROM "REPOSRC" WHERE "PROGNAME" IN ...'

successfully executed in 2 ms 230 µs  (server processing time: 1 ms 127 µs)

Fetched 8 row(s) in 0 ms 59 µs (server processing time: 0 ms 0 µs)

 

With HANA then, we get an average of 1.18ms for an average speedup of 3.27x.

 

Conclusions

 

For poorly constructed OLTP queries at the database level, we can get enormous benefits of running HANA - up to 250x or more. With optimized SQL that hits database indexes on anyDB, that drops to around 3.27x, but SAP have only ever claimed a 2-3x increase of running ERP on HANA for transactional workloads.

 

And remember if you move to the sERP suite, you'll see another 2-3x because the data structures are simpler. That's going to mean response times of 5-10x faster than on anyDB.

 

I don't know about, you, but that feels significant to me.

 

Yes, I know I didn't do concurrency, inserts, updates and all that jazz. This was just a quick test run with 15 minutes of spare time. Hope it is informative. It's also worth noting that with program changes, I was in this case able to get acceptable performance using anyDB for our timesheet app. The only area where performance is a problem is for the WBS element search, which is a wildcard search again.

 

For those searches, HANA rocks. For customer, product - anything with a free text search, HANA is going to kill anyDB.

 

P.S. This was all run on HANA Rev.81

 

P.P.S Why not run this on your DB and let us know how it runs?

Overview

 

 

This blog is part of a series of troubleshooting blogs geared towards telling you a story of how an issue got resolved. I will include the entire troubleshooting process to give you a fully transparent account of what went on. I hope you find these interesting. Please leave the feedback in the comments if you like the format or things I can improve on

 

Let's get started!

 

 

Problem Description

 

 

Trying to register the secondary site for System Replication fails with error "remoteHost does not match with any host of the source site"

 

 

Environment Details

 

 

This incident occurred on Revision 73

 

 

Symptoms

 

 

Running the following command:

 

hdbnsutil -sr_register --name=SITEB --remoteHost=<hostname primary> --remoteInstance=<inst> --mode=<sync mode>

 


Gives error:

 

adding site ..., checking for inactive nameserver ..., nameserver <hostname_secondary>:3<inst>01
not responding., collecting information ..., Error while registering new
secondary site: remoteHost does not match with any host of the source site.
please ensure that all hosts of source and target site can resolve all
hostnames of both sites correctly., See primary master nameserver tracefile for
more information at <hostname_primary>, failed. trace file nameserver_<hostname_secondary>00000.000.trc
may contain more error details.]

 

 

Studio had a similar error as well.

studio system replication error.jpg

 

 

Troubleshooting


The error message indicates that the secondary system could not be reached when performing sr_register.

 

Firstly, when dealing with System Replication, it is always good to double-check that all the prerequisites have been completed. Refer to the Administration
guide for this (http://help.sap.com/hana/SAP_HANA_Administration_Guide_en.pdf)

 

 

Let’s make sure the network connectivity is fine between the primary master nodes and the secondary master nodes.

 

 

Are the servers able to ping each other?

 

From the O/S, type “ping <hostname>”. Perform this from the primary to secondary and secondary to primary.

 

 

In this customer’s case, ping was successful.

 

 

What about firewalls? Could the ports be blocked?

 

 

From the O/S, type “telnet <hostname> <port>”. Perform this from the primary to the secondary and secondary to the primary.
The port that you will use is the one in the error message.

 

 

In this customer’s case, ping was successful.

 

 

 

Comparing the host files between the primary and secondary sites


The customer noticed that there was an error in the /etc/hosts file, the shortname was not filled in correctly. They fixed this, but the problem still occurred

 

 

 

 

Network Communication and System Replication


There is a note 1876398 - Network configuration for System Replication in HANA SP6. 

 

 

 

The symptoms of the note match what we are experiencing “When using SAP HANA Support Package 6, a
System Replication secondary system may not be able to establish a connection to the primary system.
“.

 

It is explained “Therefore, the listener hears only on the local network. System Replication also uses the infrastructure for internal network communication for exchanging data between the name servers of the primary and the secondary system.  Therefore, the name servers of the two systems can no longer communicate with each other in this case.”

 

 

It is worth noting this is very common cause of the issue, but in the customer's case, it was not the problem.

 

 

 

 

Strace

 

 

Performed an strace, here is some of the output.

 

 

sendto(13,"?\0\50\50\50\60\0\0\0\1\2\6,\0\0\0dr_gethdbversion"..., 86, 0, NULL,
0) = 86

recvfrom(13,0x7f1bd94549264, 8337, 0, 0, 0) = -1 EAGAIN (Resource temporarily unavailable)

poll([{fd=13,events=POLLIN|POLLPRI}], 1, -1) = 1 ([{fd=13, revents=POLLIN}])

recvfrom(13,"\323\346\v\333\333\333\333\333\333\333\333F\1I\nhdbversionI\0221.00."...,
8337, 0, NULL, NULL) = 52

recvfrom(13,0x7fff22c5277f, 1, 2, 0, 0) = -1 EAGAIN (Resource temporarily unavailable)

recvfrom(13,0x7fff22c528bf, 1, 2, 0, 0) = -1 EAGAIN (Resource temporarily unavailable)

gettid()                                = 35760

sendto(13,"?\0\32\33\45\33\0\0\0\1\2\0033\0\0\0dr_registerdatac"..., 413, 0,NULL, 0) = 413

recvfrom(13,0x7f1bd9745564, 8337, 0, 0, 0) = -1 EAGAIN (Resource temporarily unavailable)

poll([{fd=13,events=POLLIN|POLLPRI}], 1, -1

 

 

Seems like some sort of packet loss here.

 

 

 

Involving the Networking Team

 

 

 

We involved the customer’s networking team and found that the MTU-size was set to 9000. They set the MTU-size to 1500 and then ran the register step and it worked! The registration completed!

 

 

The networking team did not explain exactly what was going on but we suspect they performed a tcpdump to see if there was packet loss.

 

 

** This may need to be changed back later for performance optimization, see 2081065 - Troubleshooting SAP HANA Network **

 

 

 

Disclaimer

 

This blog detailed the steps that SAP and the customer worked through a problem towards a resolution. This may not be the exact resolution for every incident that has the same symptoms. If you are encountering the same issue, you can review these steps with your HANA Administrator and Networking team.

Update: You can see the recording for this session here

 

---------

SAP HANA Distinguished Engineer(HDE) is someone with significant hands-on experience with SAP HANA, and a member of the SAP technology community either as a customer, partner, or SAP employee. Find mode details about HDE program here.

 

Tamas Szirtes is a SAP HANA Distinguished Engineer.

 

Tamas will share his experience in using SAP HANA in real-world at customers as part of the HDE Webinar series.

Please register to attend the free webinar.

 

Title: SAP HANA usage scenarios in the real-world

Date/Time: November 25th, 2014 / 5 PM - 6 PM CET / 8 AM - 9 AM PT

 

The goal of this session is to show the ways SAP HANA is used successfully in the real world and share the key leanings.

 

In this webinar Tamas will explain the various SAP HANA deployment options, such as using SAP HANA as a data mart, running SAP BW powered by SAP HANA, running the SAP Business Suite powered by SAP HANA, using it as a sidecar, creating and running native SAP HANA applications, Cloud, etc. For each deployment scenario, one or more customer cases will be explain in terms of the industry context, business challenges and the solution. For each case,  the lessons learned will be explained in detail.

Amazon's annual global conference AWS re-invent  2014 is starting on Nov 11 at The Venetian, Las Vegas. And in a very short period of time, it has become the place to be for global cloud platform leaders, customers & developers to come together every year.

 

Its only fitting for SAP, as a key AWS partner, to be present at the show floor delivering to our customers, prospects & partners an understanding of our cloud strategy and how that can help them create their own cloud strategy to achieve their business goals. We will focus on how you can leverage the power of AWS to deliver the value that SAP solutions sooner especially with SAP HANA.

 

 

SAP HANA on AWS:

 

Achieve breakthroughs in speed and flexibility with SAP HANA running in the AWS cloud

 

  • Business is moving to the cloud – for a wide range of workloads.
  • SAP HANA is certified to run in the AWS public cloud – offering significant value and ability to scale.
  • You can deploy SAP HANA quickly and economically – for fast time to value.

 

Below are some of the options available to harness the power of SAP HANA with the flexibility of AWS.

 

SAP HANA One

Get the full set of SAP HANA features on a subscription basis.

This is our simplest entry point for SAP HANA and perfect for department scale projects, system integrators, independent software vendors, and innovative startups. Support is through an active community of HANA One users.

Highlights:

  • Breakthrough in-memory platform in the cloud: Real-time business with the simplicity of the cloud
  • Licensed for productive use: *64GB Memory
  • Flexible, pay as you go subscription pricing: SAP HANA license + provider infrastructure
*License does not permit direct connection to other SAP Software except SAP Lumira.

SAP HANA on AWS BYOL

If you already own a license of SAP HANA, you may bring you own license (BYOL) and leverage the full value that the AWS Cloud offers and provision infrastructure in minutes versus weeks or months.

 

Getting started:

  • The SAP HANA on the AWS Cloud Quick Start Reference Deployment Guide provides architectural considerations and configuration steps necessary for deploying SAP HANA in the Amazon Web Services (AWS) Cloud in a self-service fashion. The guide utilizes a “Bring Your Own License (BYOL)” scenario for SAP HANA software and recommended best practices for deploying SAP HANA on AWS using services such as Amazon EC2 and Amazon Virtual Private Cloud (Amazon VPC).

 

 

The guide also provides links to automated AWS CloudFormation templates that you can leverage to more quickly and easily automate your deployment or launch directly into your AWS account. Download the full SAP HANA on the AWS Cloud Quick Start Reference Deployment Guide and get started today!

 

 

 

 

Come talk to our experts at the SAP booth #200 or to setup a 1X1 meeting please write to Mike Craig: Michael.craig@sap.com

 

 

 

For more info please visit:

 

SAP HANA: www.saphana.com

SAP HANA on AWS: http://aws.amazon.com/sap/saphana

Running SAP HANA on the Amazon Web Services Cloud: Free Webcast

SAP HANA in the AWS Cloud Quick Start Deployment Guide http://aws.amazon.com/quickstart/

For the Keynote at SAP TechEd/d-code 2014 Las Vegas, we built out a quarter trillion row model in a single scale-up HANA system. John has put together a great high level overview of the solution in his blog Unleashing Lightening with SAP HANA or you can watch the video.

 

John kicked off the in-depth explanation of how the solution was structured which you can see in more detail at Unleashing Lightening: Building a quarter trillion rows in SAP HANA

 

In this blog I'm going to briefly talk about the front-end of the demo. I will firstly briefly cover the oData layer and then touch on the technologies used to build the web UI.

 

The oData layer

 

The oData layer of the solution was pretty simple. Firstly we put together a stored procedure which took care of the query logic which John covers in more detail in his blog. We then wrapped that SQL Script in a HANA calculation view. The purpose of this was to allow ourselves to call this stored procedure via an oData service while passing in query parameters (in this case our search terms).

 

The oData service, once we had the calculation view, was pretty easy to define using SAP HANA's XS engine (.xsodata file):

 

service {

  "wiki.data::CV_WIKI3" as "Historical"

  keys generate local "ID"

  parameters via entity;

 

  "wiki.data::CV_WIKIPREDICT" as "Predictive"

  keys generate local "ID"

  parameters via entity;

}

In the above example, we are creating a new oData service based on the calculation view CV_WIKI3 and CV_WIKIPREDICT which are stored in the package "wiki" and the subpackage "data". We expose these services under the names Historical and Predictive and generate an ID column automatically based on the results returned from the view. Finally we allow any available parameters of the calculation view to be passed in via the oData service.

 

Once the oData service above is active we can now explore it by loading the $metadata - for example the metadata can be found for my service at:

 

http://myHANAServer.mycorp.com:8000/wiki/data/wiki.xsodata/$metadata

The metadata gives the structure of the parameters that are required to be passed into the calc view:

 

Screen Shot 2014-11-08 at 22.04.28.png

 

In this case my parameters are imaginatively called "CalcViewParameters" and it has two fields, TITLE1 and TITLE2. So now we can construct our query URL which is formed as follows:

 

https://myHANAServer.mycorp.com:8000/wiki/data/wiki.xsodata/CalcViewParameters(TITLE1='ABC',TITLE2='DEF')/Results?$format=json

The above URL will call our oData service, pass in the TITLE1 and TITLE2 parameters and return the results in the result set "Results".

 

OK so now that we have our data we can move onto the UI.

 

The UI

 

The UI for the solution was pretty simple and used standard web technologies.

 

Screen Shot 2014-11-08 at 22.14.39.png

 

The main surround of the UI was built using Bootstrap a superb framework for developing responsive user interfaces. The form at the top that we used to specify the search criteria, the type of query and the predictive variables were created using standard HTML form elements combined with none other than openui5.

 

Finally the data visualisations showing the results were created using sapui5 which provides some amazing out of the box data visualisation functions which I can highly recommend. In this case the chart was of the type sap.viz.ui5.Column.

 

I won't go through the code of the UI in detail but I can recommend that anybody interested in getting started with a UI like this - please check out OpenUI5 or the sapui5 hosted SDK which has great tutorials and getting started guides.

 

For anybody trying this out for themselves, if you have any questions or comments I'd be more than happy to help so please do post below.

 

That's it from me for the front-end of Unleashing Lightning with SAP HANA. I encourage you all to try it out - it really is a lot of fun!!

 

Happy coding,

 

Brenton.

Companies with complex distribution networks or spare parts supply chains are continually trying to achieve the balance between not enough and too much when it comes to inventories. Too little adversely affects service capabilities, while conversely, too much may result in costly overstocking and excessive storage.

 

To help alleviate these issues, customers can set up materials requirements planning (MRP) algorithms in their SAP ERP application, which order parts automatically based on the requirements that are visible at a point in time. The MRP algorithms use parameters such as safety stock, maximum level, and minimum order quantity in order to deal with the unavoidable uncertainties about future demand quantities and supply lead times.

 

The operation of a typical supply chain involves a large variety of materials, events, interactions and tactical decisions. Different types of demand with different degrees and types of uncertainty have to be served from the same stock. Stock locations are organized on multiple tiers that supply one another, so the stock availability in one location influences the supply lead time for another one. All this complexity forces the business to make limiting assumptions when looking for the best strategies and parameters, without having a good way to assess the real impact of these assumptions on the accuracy of the result. A lot of times the assumptions that are correct for a material might be completely off for another one. And sometimes, the real case of inventory problems is in a place so unlikely that no one is looking at it – the wrong number in a rarely used field, a mismatch between the units of measurement used in different locations, etc.

 

Unveil the real problems behind low service levels

 

Now there’s a way to have better insights into inventory control through simulation and optimization functionality developed by SAP Data Sciences and the custom development team.

 

This new functionality, which runs on the SAP HANA database, is an analytic “side-car” to solutions like SAP ERP and models a company’s actual supply chain, including all the interactions and events that impact inventory levels.

 

With this software, the expected future behaviour of the full supply chain is simulated in detail, revealing all interactions and allowing for the identification of known and unknown problem areas. Based on the outcome of the simulation, a company’s current strategies and parameters in the supply chain can be adjusted to ones that produce an ideal outcome.

 

Realistic simulation, real-life optimization

 

The simulation model includes both deterministic events (such as purchases and stock transfers) as well as stochastic events (unexpected demand and lead-time variability). The stochastic events are described by statistical models which are custom built from the historical data and enhanced using the experience of the business users.


Starting from the current state of the supply chain and including the existing MRP algorithms, the simulation computes the future inventory situation in detail.
The optimization algorithms make use of the simulation to find the ideal replenishment strategies and parameters.

 

Empower supply chain analysts

 

The interface of this functionality brings together all the information about the current state, the expected future state and the optimal state of the supply chain. Supply chain analysts are given a complete view of their supply chain – they can view KPIs that reflect all materials, in all locations, over a long period of time and quickly switch to a view of the expected stock level or demand for one material, in one location, on a daily basis.


They can simulate the effects of any parameter changes and even new configurations of the network and in this way get additional insight before implementing changes in the real system.

 

This functionality allows companies to:

  • Visualize consolidated information for each material part across the whole supply chain
  • Predict the behaviour of the supply chain in the future, based on a data snapshot from SAP ERP
  • Simulate the effect of the existing network configuration, replenishment strategies, and parameters
  • Calculate KPIs, such as service levels or average inventory, based on the simulations
  • Optimize inventory policies to achieve the best possible trade-offs between service level and inventory stocking costs

 

The key: Event level simulation of the supply chain

 

At the heart of this new functionality is the simulation of events on the supply chain, on a daily level.

 

Data - such as weekly historical demand, future planned demand, MRP parameters, supplier and internal lead times - can be pulled from SAP ERP, other applications and even flat files and brought together in SAP HANA to form a coherent, detailed and easily accessible supply chain overview.
Rules – such as the quantities that MRP would order or priority of some types of demand – are modelled by a simulation algorithm written in JAVA, which allows the fast simulation of the effect of a large number of individual events on the situation of the supply chain.


The simulation algorithm is able to compute what will happen on the supply chain in the future and report the possible ranges for all supply chain KPIs: average stock values, order values, numbers of orders, average waiting days, etc.

The optimization algorithms are able to modify all parameters a business user is able to modify in reality, and do so automatically until the expected simulation KPIs reach their optimal values.

 

The results of simulations and optimization are stored in SAP HANA along with the supply chain data that was copied from ERP and other sources. This, along with the next generation of user interfaces that the SAP HANA Platform enables, makes it possible for supply chain analysts to get a clear picture of the whole supply chain and the way in which present decisions influence future KPIs.

 

Improved service – and significant savings

 

With rapid simulation and optimization, the real problems behind low service levels become apparent and inventory issues are resolved.

And based on our experience of working with customers in this area, there is potential for significant savings by reducing average stock levels or order frequencies and increasing the service levels for materials which cause the most costly delays.

 

If you are interested in utilizing this technology, contact me to learn how this functionality can be integrated into your distribution network.

I was inspired by Wenjun Zhou who wrote Play the game of life with SAP HANA to have a little fun and solve the Eight Queens problem using SAP HANA.

 

The Eight Queens is a classic problem in computer science: how can we place eight queens on a chess board, so that none of them can take each other? This is often taught to computer scientists, because it requires use of a backtracking algorithm to solve. I learnt this in a Modula-3 course back in the mid-90s. Here's a picture of the solution thanks to Eight queens puzzle - Wikipedia, the free encyclopedia

 

Screen Shot 2014-11-05 at 5.17.08 PM.png

It turns out that there are exactly 92 solutions to this problem on an 8x8 board. I can still remember my Modula-3 program spitting out solutions on a Sun UNIX server. The SQL Server Pro folks, wrote a blog Use T-SQL to Solve the Classic Eight Queens Puzzle which I then adapted to SQLScript. It's quite elegant, because it first only considers solutions where the queens are in distinct columns. This massively reduces the result space from n^n to n! (40320 for an 8x8 board).


It's even more fascinating if you do a PlanViz on this, because it only materializes 1704 rows at the most - it doesn't materialize the whole 40320 result set before it filters. Another example of the efficiency of the HANA column store engine.


I wrote a little piece to create an array of size N to represent the rows, which would be generic, but I couldn't figure out a way to recurse like you can in T-SQL. Can anyone see a more elegant solution?

 

DROP PROCEDURE queens;

CREATE PROCEDURE queens ()

  LANGUAGE SQLSCRIPT

  SQL SECURITY INVOKER

  READS SQL DATA AS

BEGIN

 

DECLARE v_queens INTEGER ARRAY;

DECLARE v_n INTEGER;

 

FOR v_n IN 1 .. 8 DO

  v_queens[:v_n] := :v_n;

END FOR;

 

queens = UNNEST(:v_queens) AS (n);

 

SELECT a.n AS a, b.n AS b, c.n AS c, d.n AS D,

       e.n AS e, f.n AS f, g.n AS g, h.n AS h

  FROM :queens AS a

  JOIN :queens AS b

    ON b.n <> a.n

   and (b.n - a.n) NOT IN (-1, +1)

  JOIN :queens AS c

    ON c.n NOT IN (a.n, b.n)

   AND (c.n - a.n) NOT IN (-2, +2)

   AND (c.n - b.n) NOT IN (-1, +1)

  JOIN :queens AS d

    ON d.n NOT IN (a.n, b.n, c.n)

   AND (d.n - a.n) NOT IN (-3, +3)

   AND (d.n - b.n) NOT IN (-2, +2)

   AND (d.n - c.n) NOT IN (-1, +1)

  JOIN :queens AS e

    ON e.n NOT IN (a.n, b.n, c.n, d.n)

   AND (e.n - a.n) NOT IN (-4, +4)

   AND (e.n - b.n) NOT IN (-3, +3)

   AND (e.n - c.n) NOT IN (-2, +2)

   AND (e.n - d.n) NOT IN (-1, +1)

  JOIN :queens AS f

    ON f.n NOT IN (a.n, b.n, c.n, d.n, e.n)

   AND (f.n - a.n) NOT IN (-5, +5)

   AND (f.n - b.n) NOT IN (-4, +4)

   AND (f.n - c.n) NOT IN (-3, +3)

   AND (f.n - d.n) NOT IN (-2, +2)

   AND (f.n - e.n) NOT IN (-1, +1)

  JOIN :queens AS g

    ON g.n NOT IN (a.n, b.n, c.n, d.n, e.n, f.n)

   AND (g.n - a.n) NOT IN (-6, +6)

   AND (g.n - b.n) NOT IN (-5, +5)

   AND (g.n - c.n) NOT IN (-4, +4)

   AND (g.n - d.n) NOT IN (-3, +3)

   AND (g.n - e.n) NOT IN (-2, +2)

   AND (g.n - f.n) NOT IN (-1, +1)

  JOIN :queens AS h

    ON h.n NOT IN (a.n, b.n, c.n, d.n, e.n, f.n, g.n)

   AND (h.n - a.n) NOT IN (-7, +7)

   AND (h.n - b.n) NOT IN (-6, +6)

   AND (h.n - c.n) NOT IN (-5, +5)

   AND (h.n - d.n) NOT IN (-4, +4)

   AND (h.n - e.n) NOT IN (-3, +3)

   AND (h.n - f.n) NOT IN (-2, +2)

   AND (h.n - g.n) NOT IN (-1, +1)

ORDER BY a, b, c, d, e, f, g;

 

END;

 

CALL queens();


Unfortunately there are some extremely efficient solutions to the N Queens problem like Jeff Somers's N Queens Solutions using C++, and the SQL solution can't compare to these for this type of problem. I tried running a 16x16 version of this and it was extremely slow.


Still, it was a little fun. I hope you enjoy.

A common inquiry

 

Last week I got two very similar question from colleagues in the consulting teams:

 

"[...] We created a user (“john” with select / execute privileges on “SYS_BI & SYS_BIC” schema.

User can access data using bo tools and all looking good.

When the user logged into hana studio with his userid…

He was able to execute

“CREATE view “stest”  as select * from <sys_bic> table…..

Is there any option to block user from creating views using sql script as above.[...]"

 

and

 

"[...]When a user is created in SAP HANA there is a schema created for this user automatically, for which the user has all privileges.

One of our customers wants to prevent this, as they fear that users can create their own secret data collections.

How can that be prevented for users with SAP HANA Studio access?[...]"

 

GEIGOKAI - a fierce enemy

ninja-155848_640.png

free picture linked from pixabay.com.

 

Bottom line of these requests is that we want to have a true no-privileges-included user account that we can later provide with just the privileges it should have.

Killing two birds with one stone I decided to put out this blog post, instead of adding to the Great-Email-Inbox-Graveyard-Of-Knowledge-And-Information (GEIGOKAI... sounds like a Japanese management principle, but in fact, is just a sort of organisational dysfunction of communication ).

 

So, here we go again

As of SAP HANA SPS 08 this is really easy to have.

The tool of choice here is called "restricted user".

 

Restricted users are explained in the documentation and we cover them of course in chapter 12 "User Management and Security" of the SAP HANA Adminstration book.

 

Instead of going over the theoretical parts again, let's just look at an example.

 

I have my application schema I028297 which contains a table SOMESALESDATA and I have an analytic view build using this data called "I028297/AV_2013".

Now, I want to have a read only user that cannot just create a table or view himself, but who can read the data from my table and the analytic view.

 

1. I need to create the user:

CREATE RESTRICTED USER justread PASSWORD Zer0writes;

Not much to say about this... no black magic required here.

 

2. I add the user to the SAP HANA Studio

Here I get the first "surprise".

After I changed the initial password (a favorite activity with all first time SAP HANA users ) I am confronted with this friendly message:

    "You are not authorized to execute this action; you do not have the required privileges".

 

So, what's going on here?

The reason for this is that the restricted users by default can only connect via HTTP/HTTPS.

Access via this channel can be controlled by the XS engine access rules.

But there is no default access to the database level for restricted users.

 

In order to allow users accessing the database level via ODBC or JDBC we need to explicitly grant built-in system roles RESTRICTED_USER_ODBC_ACCESS or RESTRICTED_USER_JDBC_ACCESS.

 

I want to stick with SAP HANA Studio access here (that's JDBC access), so I use the later:

GRANT restricted_user_jdbc_access TO readonly;

 

Now I can logon to the system, but I cannot really do much:

SELECT current_user FROM dummy;

works but that is nearly everything.

 

Other simple actions like

 

CREATE VIEW whoami AS (SELECT current_user FROM dummy);

fail with

    SAP DBTech JDBC: [258]: insufficient privilege: Not authorized

 

Same with accessing the application data:

SELECT * FROM i028297.somesalesdata;

    SAP DBTech JDBC: [258]: insufficient privilege: Not authorized

 

and

 

SELECT "TXNO", "TXDATE", sum("AMOOUNT") AS "AMOOUNT"

FROM "_SYS_BIC"."I028297/AV_2013" GROUP BY "TXNO", "TXDATE";


   SAP DBTech JDBC: [258]: insufficient privilege: Not authorized

or (depending on which privileges are used for the analytic view)

    SAP DBTech JDBC: [2048]: column store error: search table error:  [2950] user is not authorized

 

3. Clearly, we need to grant the read privileges for the application table and view to our user.

So, let's do that quickly:

 

GRANT SELECT ON i028297.SOMESALESDATA TO justread;

GRANT SELECT ON "_SYS_BIC"."I028297/AV_2013" TO justread;

 

Now, the JUSTREAD user can access the application data but (s)he cannot create own tables or views:

 

SELECT * FROM i028297.somesalesdata;

works, but

CREATE VIEW my_salesdata AS (SELECT * FROM i028297.somesalesdata);

again results in

    SAP DBTech JDBC: [258]: insufficient privilege: Not authorized

 

Same thing with the analytic view:

SELECT "TXNO", "TXDATE", sum("AMOOUNT") AS "AMOOUNT"

FROM "_SYS_BIC"."I028297/AV_2013" GROUP BY "TXNO", "TXDATE";

 

now works nicely, but something like

 

CREATE COLUMN TABLE sales_2013 AS

    (SELECT "TXNO", "TXDATE", sum("AMOOUNT") AS "AMOOUNT"

    FROM "_SYS_BIC"."I028297/AV_2013" GROUP BY "TXNO", "TXDATE");

 

does not work.

 

So, there we have it.

Important to remember is that it is not possible to change existing normal users to be restricted users or vice versa.

This decision has to be made when the user is created.

Restricted users still have their own schema listed in the SCHEMAS view, so don't get confused here.

 

Also, it is not good practice to directly assign privileges to single user accounts like I did it in this example.

Instead privileges should be assigned to roles and these roles in turn should be assigned to the users.

 

There you go, now you know.

Another pearl of wisdom saved from GEIGOKAI.

 

Cheers,

Lars

I stumbled upon this interesting blog written by Wenjun Zhou , in which he explains the implementation of the game using SQL script and window functions ( I would recommend reading Wenjun's blog to understand the Game of life before reading this blog). This inspired me to try the game of life using decision table and graphical calculation views.

 

 

Approach

 

1. Create the initial pattern as a table in HANA.

 

2. Find the number of neighboring live cells for each cell using a calculation view.

 

3. Implement the rules of the game in a decision table to get the next generation cells.

 

 

Initial pattern

 

We will start with the below pattern ,

 

3.PNG

 

Pattern is created as a table in HANA ( lets call it CELLGAME ),

 

If a cell is alive , value in column S will be 1 otherwise the value will be 0.

 

cellgame_table.png

 

Building the calculation view

 

Number of live neighbors could be derived from the below SQL ( from Wenjun's Blog ). Let us try to implement this SQL in a Calculation view.

 

 

SELECT A.X, A.Y, A.S, SUM(B.S) N  FROM LIFE A INNER JOIN LIFE B ON ABS(A.X - B.X) < 2 AND ABS(A.Y - B.Y) < 2 AND (A.X <> B.X OR A.Y <> B.Y)

GROUP BY A.X, A.Y, A.S; 

 


To create an inner join to the table, add two projection nodes and add the CELLGAME table to it. A dummy calculated column with a default value as 1 is created in both the projections.

 

cellgame_join.png

 

Join both the projections using a join node and Rename one of the S column to N ( Number of neighbors ) as shown below,

 

cellgame_join_cond.png

 

 

With this set up all the rows of the tables will be joined . To apply the join condition, create two calculated columns ABS_X and ABS_Y to calculate ABS("X"-"X_1") and ABS("Y"-"Y_1")

 

join_condition.png

 

Apply filter to the projection using the calculated columns created in the previous step.

 

filter.png

 

To implement group by clause and SUM function from SQL to calculation view,  Link the projection node to the calculation view's aggregation node and add X,Y,S and N columns to the output. N as a measure with aggregation type as SUM.

 

Aggregation.png

 

 

Result of this calculation view will give the number of live neighbors for each cells

 

cv_result.png.

 

Decision table

 

Below are the rules of game of life,

 

"1. Any live cell with fewer than two live neighbours dies, as if caused by under-population.

2. Any live cell with two or three live neighbours lives on to the next generation.

3. Any live cell with more than three live neighbours dies, as if by overcrowding.

4. Any dead cell with exactly three live neighbours becomes a live cell, as if by reproduction."


These rules can be converted to decision table conditions,

 

1. Any live cell with fewer than two live neighbours dies, as if caused by under-population --> if  S = 1 and N < =1 , then Result = 0

2. Any live cell with two or three live neighbours lives on to the next generation. -->  if S =1 and ( N = 2 or N = 3 ) , then Result = 1

3.  Any live cell with more than three live neighbours dies, as if by overcrowding. --> if S = 1 and N > 3 , then Result = 0

4.  Any dead cell with exactly three live neighbours becomes a live cell, as if by reproduction. --> if S = 0 and N = 3 , then Result = 0.


Using the above conditions, create a decision table with S , N columns as conditions and a new parameter S_NEW ( Result of the game ) as action.

 

decision table para.png


Conditions

 

decision_table_cond.png

 

 

Result of this decision table will give the next generation cells.

 

DT_result.png

 

Update : The next part of the blog which explains how to create an interactive game using the views that we have built is available here

Part 2 - Playing the Game of life with HANA using decision tables and Calculation views

Jenny Lee

HANA Health Check

Posted by Jenny Lee Oct 31, 2014

     For this blog, I would like to focus on some basic health checks for HANA. These checks can give you a good idea of how your HANA system is performing. We will go through some SQL statements and the thresholds to determine what the status of your HANA system is in. To know how the HANA system is performing, it can allow us to plan ahead and avoid unnecessary system disaster.

 

 

 

System Availability:

 

The following query shows you how many time each service was restarted in the specified hour and date within the analyzed period.

select  to_dats(to_date("SNAPSHOT_ID"))AS "DATE", hour("SNAPSHOT_ID") AS "HOUR",
SUBSTR_BEFORE(T1.
INDEX,RIGHT(T1.INDEX,  5)) AS "HOST"RIGHT(T1.INDEX,5)AS "PORT",  T2.SERVICE_NAME, count ("ALERT_ID") AS "NUM_RESTART" from "_SYS_STATISTICS"."STATISTICS_ALERTS" T1 JOIN "SYS"."M_VOLUMES"
T2
ON SUBSTR_BEFORE(T1.INDEX,RIGHT(T1.INDEX,5))=T2.HOST AND RIGHT(T1.INDEX, 5)=T2.PORT   WHERE ALERT_ID = '004'  AND
SNAPSHOT_ID >= add_days(now(), -14)
GROUP BY to_date("SNAPSHOT_ID"), hour ("SNAPSHOT_ID"), SUBSTR_BEFORE(T1.INDEX,RIGHT(T1.INDEX,  5)), RIGHT(T1.INDEX,5), T2.SERVICE_NAME ORDER BY to_date("SNAPSHOT_ID") DESChour ("SNAPSHOT_ID") DESC

 

STATUS

THRESHOLDS

RED

Name server is not running

Name server/ Index server had 3 or more restarts in the analyzed period

YELLOW

Statistics server is not running

Name server / Index server had up to 2 restarts in the analyzed period

Remaining servers had 2 or more restarts in the analyzed period

GREEN

All other cases

 

The example below shows that this standalone test system got restarted 1 time on October 22nd, 2 times on October 21st at around 11pm and another 2 times at around 10pm. In total, there are 3 restarts of the indexserver and nameserve in the analyzed period. If the nameserver is currently not running, then this will be rated as RED. To find out rather the database is restarted manually or due to some other reasons, you may go to index server and name server traces to get more information. If you need further assistance, please consider opening an incident with Product Support.


systemAvail3.png

 

Top 10 Largest Non-partitioned Column Tables (records)

The following query displays the top 10 non-partitioned column tables and how many records exist in each.

 

SELECT top 10 schema_name, table_name, part_id, record_count from SYS.M_CS_TABLES where schema_name not LIKE '%SYS%' and part_id = '0' order by record_count desc, schema_name, table_name

STATUS

THRESHOLD

RED

If tables with more than 1.5 billion records exist.

YELLOW

If tables with more than 300 million records exist.

GREEN

No table has more than 300 million records.


In the threshold chart, it shows that if the column table has more than 300 million records; then it is in yellow rating.This is not yet critical with regards to the technical limit of 2 billion records but you should consider partitioning those tables that are expected to grow rapidly in the future to ensure parallelization and sufficient performance. For more information, please refer to the below SAP Notes or the SAP HANA Administration Guide.

 

Useful SAP Notes:

- 1650394  - SAP HANA DB: Partitioning and Distribution of Large Tables

- 1909763 - How to handle HANA Alert 17: ‘Record count of non-partitioned column-store tables’

 

Top 10 Largest Partitioned Column Tables (records)

This check displays the 10 largest partitioned column tables in terms of the number of records.


select top 10 schema_name, table_name, part_id, record_count

from SYS.M_CS_TABLES

where schema_name not LIKE '%SYS%' and part_id <> '0'

order by record_count desc, schema_name, table_name

STATUS

THRESHOLD

RED

If table with more than 1.9 billion records exist.

YELLOW

If table with more than 1.5 billion records and below 1.9 billion records exist.

GREEN

No table has more than 1.5 billion records.

 

The recommendation is to consider re-partitioning after it has passed 1.5 billion records as the technical limit is two billion records per table. If table is more than 1.9 billion records, then you should do the re-partitioning as soon as possible. For more information, please refer to the below SAP Notes or the SAP HANA Administration Guide.

 

Useful SAP Notes:

-   1650394  - SAP HANA DB: Partitioning and Distribution of Large Tables

 

Top 10 Largest Column Tables in Terms of Delta size (MB):

This check displays the 10 largest column tables in terms of the size of the delta and history delta stores.


select top 10 schema_name, table_name, part_id, round(memory_size_in_main /(1024*1024),2), round(memory_size_in_delta/(1024*1024),2), record_count, RAW_RECORD_COUNT_IN_DELTA from SYS.M_CS_TABLES

where schema_name not LIKE '%SYS%'

order by memory_size_in_delta desc, schema_name, table_name

STATUS

THRESHOLD

RED

MEMORY_SIZE_IN_DELTA >10 GB

YELLOW

MEMORY_SIZE_IN_DELTA >=5 GB AND <=10 GB

GREEN

MEMORY_SIZE_IN_DELTA < 5 GB

 

The mechanism of main and delta storage allows high compression and high write performance. Write operations are performed on delta store and changes are taken over from the delta to main store asynchronously during Delta Merge. The column store performs a delta merge if one of the following events occurs:

- The number of lines in delta storage exceeds the specified limit

- The memory consumption of the delta storage exceeds the specified limit

- The delta log exceeds the defined limit

 

Ensure that delta merges for all tables are enabled either by automatic merge or by application-triggered smart merge. In critical cases trigger forced merges for the mentioned tables. For more detail, please refer to the following SAP Note or the SAP HANA Administration Guide.

 


Useful SAP Notes:

-1977314 - How to handle HANA Alert 29: 'Size of delta storage of column-store tables

 

CPU Usage:

To check the CPU usage in relation to the available CPU capacity, you can go to the Load Monitor from SAP HANA Studio.

STATUS

Header 2

RED

Average CPU usage >=90% of the available CPU capacity

YELLOW

Average CPU usage >=75% and < 90% of the available CPU capacity

GREEN

Average CPU usage < 75% of the available CPU capacity

hostCPU.png

 

The Load Graph and the Alert tabs can provide the information of time frame of the high CPU consumption. If you are not able to determine the time frame because the issue happened too long ago, check the following StatisticsServer table which includes historical host resource information up to 30 days:

 

"_SYS_STATISTICS"."HOST_RESOURCE_UTILIZATION_STATISTICS"

 

With the time frame, you may search through the trace files of the responsible process as they will provide indications on the threads or queries that were running at the time. If the high CPU usage is a recurrent issue that is due to scheduled batch jobs or data loading processes, then you may want to turn on the Expensive Statements trace to record all involved statements. For recurrent running background jobs like backups and Delta Merge, you may want to analyze the two system views: "SYS". "M_BACKUP_CATALOG" and "SYS"."M_DELTA_MERGE_STATISTICS" or "_SYS_STATICTICS"."HOST_DELTA_MERGE_STATISTICS"

 

For more information, please refer to the following SAP Note and also the SAP HANA Troubleshooting and Performance Analysis Guide.

 

SAP Note:

- 1909670 - How to handle HANA Alert 5: ‘Host CPU Usage'

 

 

Memory Consumption:

To check the memory consumption of tables compare to the available allocation limit, you may go to the Load Monitor From HANA Studio.

 

memoryUsage2.png

 

STATUS

THRESHOLD

RED

Memory consumption of tables >= 70% of the available allocation limit.

YELLOW

Memory consumption of tables >= 50% of the available allocation limit.

GREEN

Memory consumption of tables < 50% of the available allocation limit.

 

As an in-memory database, it is critical for SAP HANA to handle and track its memory consumption carefully and efficiently; therefore, HANA database pre-allocates and manages its own memory pool. The concepts of the in-memory HANA data include the physical memory, allocated memory, and used memory.

- Physical Memory: The amount of physical (system) memory available on the host.

- Allocated Memory: The memory pool reserved by SAP HANA from the operating system

- Used Memory: The amount of memory that is actually used by HANA database.

 

Used Memory serves several purposes:

- Program code and stack

- Working space and data tables (heap and shared memory) The heap and shared area is used for working space, temporary data, and storing all data tables (row and column store tables).

 

For more information, please refer to the following SAP Note and also the SAP HANA Troubleshooting and Performance Analysis Guide.

 

Useful SAP Note:

- 1999997 - FAQ: SAP HANA Memory

 

HANA Column Unloads:

 

Check Column Unloads on Load Graph under the Load Tab in the SAP HANA Studio. This graph will give you an idea of the time frame of any high activities of column unloads.

Header 1

Header 2

RED

>= 100,000 column unloads

YELLOW

>= 1001 and <100,000 column unloads

GREEN

<=1000 column unloads

 

Column Store unloads indicates the memory requirements exceed the current available memory in the system. In a healthy situation, it could be that the executed code request a reasonable amount of memory and requires SAP HANA to free up memory resources that are used rarely. However, if  there is a high number of table unloads then it will have an impact on the performance as the tables needs to be fetched again from the disk.

There are a couple of things to look for.

 

-  If the unloads happen on the statistics server, then it might be that the memory allocated for statistics server is not sufficient and most of the time it would accompany by Out of Memory errors. If this is the case, refer to SAP Note 1929538 HANA Statistics Server - Out of memory. On the other hand, if the unload motivation is 'Unused resource' then you should increase parameter global.ini [memoryobjects] unused_retention_period.

 

- If the unloads happen on the indexserver server and the reason for the unloads is due to low memory then it could be either of the reasons:

1) The system is not properly sized

2) The table distribution is not optimized

3) Temporary memory shortage due to expensive SQL or mass activity

 

For more detail information on this, please refer to SAP Note 1977207.


1977207 - How to handle HANA Alert 55: Columnstore unloads


License Information:

The view M_LICENSE can show the date that the HANA license will expire. You can also check the HANA license information from HANA Studio, right click the HANA system > Properties > License. If the license expires, the HANA system will be in a lockdown state; therefore, it is important to make sure the license is renewed before it expires.

 

select system_id, install_no, to_date(expiration_date), permanent, valid, product_name, product_limit, product_usage FROM "SYS"."M_LICENSE"

 

HANA database supports two kinds of license keys:

1) Temporary license key:

      - It is valid for 90 days.

      - It comes with a new SAP HANA database. During these 90 days, you should request and apply a permanent license key.

2) Permanent license key:

     - It is valid until the predefined expiration date.

     - Before a permanent license key expires, you should request and apply a new permanent license key.

 

For more information and steps to request for a license, please refer the SAP Note 1899480

 

- 1899480 - How to handle HANA Alert 31: 'License expiry'

Actions

Filter Blog

By author:
By date:
By tag: