Category Archives: SAP HANA

HANA backtrace 44/2011

This week I’m starting a new blog post series aimed at the readers that are technically interested in SAP HANA but don’t want to spend hours each week checking every new post, article, forum discussion or sap note about this hot topic.

These regular posts will be little collections of what I found interesting, notable or important in the area of HANA.
Since I’m rather the technology guy and severely biased towards the database-backend-part of it, it shouldn’t surprise anybody that this is not going to be a comprehensive overview that covers all HANA related topics.
Instead, it’ll merely be what I read on the topic – so I hope you like this ūüôā

If one or the other now thinks “hmm… this concept is somewhat familiar…” then you’re absolutely right. I really like Pythians “Log Buffer) a lot and HANA is just missing there.

So let’s start and look what the last week (CW 44, 29.10. – 5.11.2011) had for the HANA community:

Several SAP notes had been published

The most important to start with: Revision 18 of the SAP HANA database (and of the related client software of course) had been released and it fixes a lot of issues. On top of that, it also brings several enhancements, especially in the area of the error handling with delta index merging… at least this is mentioned twice in SAP note #1646454 SAP HANA appliance: Revision 18 of SAP HANA database

[...]
o Improved error handling for delta merge
o Improved delta merge error handling
[...]

Maybe they double-enhanced it … ūüôā

++++ —- ++++ —- ++++

If you’re using the segment builder from the SAP CRM solution and want to access HANA then SAP note #1577019 SAP HANA Database: Abap module for Segment Builder is for you.

++++ —- ++++ —- ++++

SAP BW on HANA is about to be published soon, so sizing is a topic that will become important. Details on this can be found in note #1637145 SAP BW on HANA: Sizing SAP In-Memory Database.

++++ —- ++++ —- ++++

HANA instances can, of course, be monitored via the DBACockpit like any other supported DBMS. As a security enhancement, there is now a special user (DBACOCKPIT<SID>) setup available that allows the DBACockpit usage with minimal permissions for the monitoring user account.
SAP note #1640741 Different database user for DBA Cockpit for SAP HANA has all the details.

++++ —- ++++ —- ++++

If you want to install all already published SAP BASIS corrections in one go, make sure to check SAP note #1644214 Comp. SAP Note Basis porting to SAP HANA DB, NW 7.30 SP06.
It’s a composite note that contains all HANA related corrections for SAP BASIS 7.30.

++++ —- ++++ —- ++++

In case you’re moving your SAP BW system to HANA you won’t need to export the aggregate fact tables since these aren’t necessary anymore with HANA. The correction from SAP note #1644396 SMIGR: Kein Datenexport der Aggregate-Tabellen modifies the famous SMIGR_CREATE_DDL report accordingly.

++++ —- ++++ —- ++++

For the rare cases where you want to create a database index just for a HANA instance, you need to set this up in the ABAP dictionary. SAP note
#1644788 Datenbank-spez. Index: SAP HANA DB ist nicht in Auswahlliste now allows to actually select HDB in the GUI by adding the value ‘HDB’ to the list of values of domain DDIXDBSEL.

++++ —- ++++ —- ++++

SQL Hints will still be with us with HANA and SAP note #1645317 SQL Hint wird nicht im Datenbank Trace angezeigt makes sure that we’ll even see them in the SQL trace (ST05).

++++ —- ++++ —- ++++

By using secondary database connections it’s possible to run your reports against several HANA instances. Unfortunately, there had been a bug with the initialization of the connection information – but no more: SAP note #1646010 SAP HANA: different DB connections are not handled correctly fixes this!

++++ —- ++++ —- ++++

Although HANA database provides many different client technologies SAP provides support just for a defined set of them.
Make sure to check SAP note #1577128 Supported clients for SAP HANA 1.0 to figure out, which combination of HANA database and clients are actually supported.

++++ —- ++++ —- ++++

Finally, two SAP notes for the trigger based data replication solution for HANA (LTR for Landscape TRansformation, not for L-language TRigger, or License To Rage … ) provide fixes for bugs:
#1647648 HANA LTR – corrections for DMIS 2010 SP4 111031 (stop repl) #1648783 HANA LTR – corr for DMIS 2010 SP4 11/11/03 (pool 16+keys)

Other releases

In Siri + HANA Sina Moatamed discusses future use cases of HANA and interface technology like Apples Siri.

++++ —- ++++ —- ++++

SAP BW on HANA will surely be the next big thing and a lot of people have a lot of different guessing about this. In How BW-on-HANA Combines With Native HANA Thomas Zurek explains how it’s actually going to work. Definitively worth the time to read!

++++ —- ++++ —- ++++

In Why SAP HANA 1.0 SP03 – Project Orange – will be a runaway success John Appleby explains why BW on HANA really will turn out great.

++++ —- ++++ —- ++++

Test driving HANA is what many customers and partners ask for and Jeff Word lays out the current options in his blog SAP HANA Test Drive in Madrid.

++++ —- ++++ —- ++++

If you’re now really interested in becoming involved with HANA you may check Paul Desmonds Experienced HANA? How to Educate Yourself about SAP HANA blog post.

Et fini!

That’s it for this week and for the first issue of this little backtrace of my HANA readings. Hope you liked it.

A nice weekend for everyone!

HANA backup script hint… a little bit more security, please!

+++ UPDATE 23.11.2011 +++

After the release of revision 20 (SPS 03) of the HANA database, some more changes concerning command syntax and system object naming had been identified.
The commands in this blog post have been adapted to reflect this.

+++ UPDATE 23.11.2011 +++

+++ UPDATE 24.10.2011 ++++
I’ve been informed that with the current HANA revisions, starting with rev.16 which is available for download in the SAP Service Marketplace since 10th of September, an altered syntax for taking backups is used.
So, to make them easier to follow in the future, I changed the examples in this blog accordingly.
+++ UPDATE 24.10.2011 ++++

When you check the SAP In-Memory Database Backup and Recovery Guide on https://help.sap.com/hana you’ll find on page 13 (4.3.2 Using SQL Commands) the first reference to a command line SQL client:
hdbsql.

The example that follows explains how to create a very simple SQL script that can be fed into the hdbsql tool in batch mode.
That way a super simple schedulable backup command is created, e.g. for scheduling via CRON or any other scheduler tool.

This is what we find in the PDF (syntax updated – see update note above):

-----------8<------snip--------8<----------
[...]
Create a file backup.sql with the following content:
connect -n <server name> -i <instance number> -u <user> -p <password>
//
BACKUP DATA USING FILE ('Monday')
//
Store this file with read rights for the crontab user.
Execute the backup by calling the file backup.sql:
/usr/sap/<SID>/HDB<instance number>/exe/hdbsql -I backup.sql
[...]
-----------8<------snip--------8<----------

Of course, this is what most customers and partners do and use in their first HANA implementation.
And in the cases, I’ve seen, for -u <user> the SYSTEM user was used.
This basically leads to a situation where there is a plain text sql script file that contains the full logon data for the SYSTEM user of your HANA box.

NOT GOOD! NOT AT ALL!

But are there alternatives? Sure there are!

Step 1 (and keep it repeating like a mantra) – Never use user SYSTEM for plain operation tasks like backups.

Instead, create a backup operator user that is not allowed to do anything else than performing backups.
Let’s check how to do this step by step:

Login to the HANA box as SYSTEM. You can do all the steps as well in HANA Studio, but as I assume most of you already are familiar with the user management there, so I present the command line version here (I chose a reddish color for the SYSTEM account sessions, just to make it a bit easier to recognize them – definitively NOT for decorative purposes…):

C:\Program Files\SAP\\dbclient>hdbsql -U S
Welcome to the SAP HANA Database interactive terminal.

Type:  \h for help with commands
\q to quit

hdbsql HAN=> select user() from dummy
CURRENT_USER
“SYSTEM”

1 row selected (0 usec)

So, I’m logged on as SYSTEM user. Great! Let’s go and create the user for backups!
(+++ 24.10.2011 – changed the logon from -U b to -U S more clarity +++)

hdbsql=> create user backop password 123
* 412: invalid password layout: minimal password length is [8] SQLSTATE: HY000

Ok, I always forget – we have a password rule build into HANA and minimal password length is 8 characters.
Let’s try again!

hdbsql HAN=> create user backop password 12345678
* 412: invalid password layout: password has to meet the rule ['A1a'] SQLSTATE: HY000

Grrr… I forgot again… this is really something I don’t like about password rules… but anyway: one more try!

hdbsql HAN=> create user backop password Aa12345678
0 rows affected (0 usec)

Success! Yes! Time for the first coffee break! (Ok, just kiddin’ we’re nearly done…)
Let’s connect with this user and try to take a backup!

hdbsql=> \\connect -n vml3012 -i 00 -u backop -p Aa12345678
Connected to HAN@vml3012:30015
hdbsql HAN=>
hdbsql HAN=> BACKUP DATA USING FILE ('BACKME')
* 414: user is forced to change password: alter password needed for current user SQLSTATE: HY000

This is also something I already had a few support messages for.
The very first time you login with a specific user to HANA you are forced to change your password.
Unfortunately there usually is no dialogue for this (it’s present only in HANA Studio but which end-user has access to this?), so one either has to have access to a SQL command line (like we happen to have right now) or somebody must have taken care of this, by e.g. implementing a little macro in Excel for this.
Anyhow, there should be a better general solution, but for now just move on and change the password:

hdbsql HAN=> alter user backop password Back1234
0 rows affected (0 usec)

Worked like a charm. Next step: take the backup!

hdbsql HAN=> BACKUP DATA USING FILE ('BACKME')
* 258: insufficient privilege: Not authorized SQLSTATE: HY000

Dang! We didn’t provide any of the required system privileges yet.

In a separate command line window I login again as SYSTEM and run the following:

hdbsql -U S

Welcome to the SAP HANA Database interactive terminal.

Type:  \\h for help with commands
\\q to quit
hdbsql HAN=> grant BACKUP ADMIN to backop
0 rows affected (1 usec)

That was easy cheesy, wasn’t it?
Back to our backop-session and try once again:

hdbsql HAN=> BACKUP DATA USING FILE ('BACKME')
0 rows affected (12 usec)

HOOORAY!

We finally did it! Now we’ve got a safe user that can login to the HANA DB but is only able to create backups.
Very nice.
Still, we would have to put the login data into the backup script file. But we have a second step to go…

Step 2 – Create and use a user store entry

The MaxDB veterans amongst the readers of this blog will already have recognized it:
HDBSQL is a ported version of SQLCLI the command line SQL client for SAPs very own MaxDB.
SQLCLI had a way to store user credentials and login data called XUSER (I even wrote about it way back…).
And guess what: this had been ported as well.
It’s called hdbuserstore now but works pretty much the same way.

ATTENTION: hdbuserstore is part of the HANA client package ONLY. I wasn’t able to find it on a vanilla installation of HANA, so if you want to use it on the server (e.g. for the backup use case I present here) you’ve to install the HANA client on the server manually.

For this demonstration, I simply used the HANA client on my laptop. To start, let’s review the command line options for this tool:

C:\Program Files\SAP\hdbclient>hdbuserstore.exe -h
Usage: hdbuserprofileadm [options] command [arguments]
Options:
-u <USER>       perform operation for other operating system user
-v              verbose mode, print operations done
-h              this help message
Commands:
HELP
Print help message.
SET <KEY> <ENV> <USERNAME> <PASSWORD>
Set a profile entry.
<KEY>       entry key
<ENV>       connection environment (host and port)
<USERNAME>  user name
<PASSWORD>  password
DELETE <KEY>
Delete entry with key <KEY>.
LIST [<KEY> ...]
List entries of store. The passwords are not shown.

In short, the program allows us to create, display and delete sets of login data.
These sets have a

  • name or <KEY>,
  • the ‘connection environment’ <ENV> which means HANA hostname and port (in my case this is vml3012:30015),
  • of course a <USERNAME>
  • and the <PASSWORD>.

Let’s go straight ahead and create an entry:

hdbuserstore set b vml3012:30015 backop Aa12345678

As you see, I’ve used

  • b as <KEY>,
  • vml3012:3001 as <ENV>,
  • backop as <USERNAME>
  • and Aa12345678 as <PASSWORD>.

I’d wish the hdbuserstore command would provide more feedback in case everything went OK, something along “OK, profile b successfully created!” or similar, but today it’s more along “no news is good news“…

Anyhow, we can review our newly created login profile:

hdbuserstore list
LIST command for all entries is not yet implemented.

I guess we have to provide exactly what we want to see:

hdbuserstore list b
KEY b
ENV : vml3012:30015
USER: backop

Et voil√°, there is our entry.

One thing to keep in mind is that these entries are accessible only by the operating system user that created it.
So in order to use this for the CRONJOB backup, you’d have to login to the user cron executed the backup script with and create the login profile there!

Now we put it all together and change the backup script to

BACKUP DATA USING FILE ('Monday')
//

With this file we can now call the backup just like this:

hdbsql -U b -I backup.sql

Again, we don’t get any output here if no errors occur.
This can become a bit annoying, especially when problems occur and debugging of the script should be done.
For that, you can add the -f (show the SQL command) parameter and/or the -t (printout debugging information) parameter.
The output then looks like this:

hdbsql -U b -I backup.sql -f
BACKUP DATA USING FILE ('Monday')

or like this:

hdbsql -U b -I backup.sql -f -tUsername¬† : “”
DB name¬†¬† : “”
Instance¬† : “(null)”
server¬†¬†¬† : “”
xuserkey¬† : “b”
nullvalue : “?”
fieldsep¬† : “(null)”
cmdsep¬†¬†¬† : “//”
CMDTrace¬† : “TRUE”
autocommit: “TRUE”
noHeader¬† : “FALSE”
useDBS¬†¬†¬† : “TRUE”
batch¬†¬†¬†¬† : “FALSE”
rollbOnErr: “FALSE”
SQL mode¬† : “(null)”
Command¬†¬† : “NULL”
BACKUP DATA USING FILE (‘Monday’)


That’s it once again!

As always I hope you liked the hands-on style of my blog and that you share your experiences with HANA.

HANA operational concept released to customers

During the past months, HANA made a fairly big wave in the SAP community. Looked like everybody wanted to get the hands onto this and gather some experience with the new piece of technology. With those, tons of questions arisen about how to operate such a system.

The recently released note #1599888 – SAP HANA: Operational Concept¬†answers these questions and clearly describes what’s supported and what’s not.

I’m pretty sure that this will make life a lot easier for everybody involved in HANA projects.

A big THANK YOU from my side to the authors of this note!