Tag Archives: hana

New year, same issues and some tips.

Most folks working with SQL DBs for a while will likely come to see how beginners in this area struggle with “declaritive code” and “tell the DB what you want not how to do it”

In my experience, this issue stems partly from the way SQL is tought – examples are often way too simplistic to cover “advanced” topics – partly this is because it is very different thinking required when one comes from Python, JAVA or the whole notion of “telling the computer what to do”.

This often leads to horrendous SQL code and data models, that neither give correct results nor deliver them fast.

Despite all of this, I see many questions around tiny optimization options, command variations and “optimizer flags” from beginners all the time.

Astonishingly, this approach of looking for workarounds for problems with the data model understanding occurs regardless of the DBMS platform used.

– Lars Breddemann

What’s often missing in those questions is the broader context, WHY they want the DB to do something and WHAT eventually should be achived.

Basically, the core question “WHAT should be the outcome, the result?” remains unanswered both on top- and bottom-level.

Maybe this is because it is hard to express this desired outcome in a good way?

I recently read a really good presentation slide deck from Markus Winand that, besides explaining an interesting new SQL standard feature (row pattern matching), makes use of a nice way to illustrate what data should be selected and returned.

Screenshot of page 19 from the presentation "Row Pattern Matching" by Markus Winand.
 https://www.slideshare.net/MarkusWinand/row-pattern-matching-in-sql2016

The example used is non-trivial and certainly beyond what most SQL 101 courses cover and that’s another positive!
The visualization he uses gives a really good grasp on the kind of data and what the result should be.

Maybe this could be a pointer for how to improve your SQL writing in 2019:
Draw a picture of the data you have and what aspects of it that you want and then work build your SQL statement step by step based on this.
This is what I do in many cases when I try to understand what a SQL statement does, so this technique works both for constructing a new statement as well as for deciphering existing ones.

To finish this slightly ranting post of here are some links to presentations and twitter accounts that think are worthwhile to read or follow (not exhaustive at all).
If you do SQL programming as part of your daily struggle for bread work, take some time and check these resources.

There you go, now you know!

With that, have yourself a Happy New Year 2019.

IF EXISTS would exist

A recurring question of folks using SAP HANA is how to only run a DROP command for objects that actually exist in the database. One could argue that the effect of executing DROP does not depend on whether the object to be dropped exists, because after running the command it will not be there either way, but there is a bit more to it.

See, DROP commands, like say DROP TABLE, report back an error message when the object cannot be found (e.g. because it does not exist).

drop table bla;

Could not execute 'drop table bla'
SAP DBTech JDBC: [259]: invalid table name: BLA: line 1 col 12 (at pos 11)

This can be annoying when running scripts to set up a database structure which is a common part of product installation and update procedures.

On MS SQL Server there exists (ha ha ha – extra point for the flattest pun) an extension to the DROP command (and to other DDL commands): DROP IF EXISTS

SAP HANA up to now (HANA 2 SPS 03) does not provide such a functionality, but it is easy to come up with a workaround.

The following utility function can be used:

drop function obj_exists ;

create function obj_exists (in schema_name NVARCHAR(256)
                          , in object_name NVARCHAR(256))
returns obj_exists int
as 
begin
declare _exists int := 0;    
    
      select case 
                when count(*) >0 then 1
                else 0
             end into _exists
       from objects
       where 
           schema_name = IFNULL (:schema_name, current_schema)
       and object_name = IFNULL (:object_name, '');

    obj_exists = :_exists;

end;

The function returns 1 if the object exists and 0 if it does not. If the input parameters contain NULL, then the function still works using the CURRENT schema but will likely return 0 as output.

select 
    obj_exists (schema_name => NULL 
              , object_name => 'OBJ_EXISTS') as OBJ_EXISTS
from dummy;

OBJ_EXISTS
1         

The above worked because the OBJ_EXISTS function was created in the current schema. With that one can easily write a SQL Script that “looks before it fires“.

HANA in a pocket, a skull and some dirty hands on Linux

DISCLAIMER: this blog describes unsupported and unlicensed use of the SAP HANA software. I describe my personal try-out of a hardware/software combination. It’s not in any way meant to be used productively or recommended to use the software in the way I describe.

If you loose data, waste life-time, money, your good mood or damage anything while trying to do what I describe: that’s all on you and there’s no guarantee, warranty or liability whatsoever from SAP or myself.

The frustrating reality of ‘big machine computing’

Just like everyone else in the SAP HANA community, I really like having a private instance available for me.

Trying things out, practicing, researching solutions, working with sample data, checking system behaviour etc. are just so much easier than e.g. on a HCP instance or some “playground” instance on the company’s SAP HANA hardware.

Yes, there are always ways to work around this but e.g. for me this means I have to live with average round trip times of ca. 320ms. That’s certainly not too bad and definitively works for most use cases. Unfortunately the server is not “mine”.

So, if I e.g. wanted to reboot the server for any reason, well, folks probably wouldn’t be too happy …

Also, this server really is only available when I can logon into our corporate network in one way or another. And this turns out to be a limitation that is easily hit.

Customer visit with no immediate WIFI logon?

Nope, no way to access the SAP HANA system then.

Cable provider outage in the home office?

Yep, that SAP HANA server is running fine, far away – without any chance to be used by me.

In short a somewhat local SAP HANA installation would be a really nice thing. Especially since I am not permanently working with tons of data, but rather look into the development/admin aspect of things.

Is there a way or what?

A few weeks ago, I was on a customer visit in San Francisco and the development team there came up with a valid and interesting question: how to provision developers with private SAP HANA instances?

With a private instance, each developer could mess around with his system without impacting anyone else. This is pretty much a standard approach for development nowadays, so the request is rather reasonable.

SAP internally there exist multiple approaches to this problem.

From “small” SAP HANA hardware under the developer’s desk (actually quite expensive machines, that we would have called “workstations” back in the day) to spawn-on-demand-systems on virtual servers: there’s a flavor for everyone.

Unfortunately all that sort of screams: high initial investment.

Not great for smaller teams or individuals who would want to get deeper into SAP HANA development/administration without access to “allowed” hardware.

Coincidentally I read Mr. Atwood’s blog post and was intrigued: this Intel Skull Canyon NUC sounded awesome.

Even though I am not a gamer and the last time I assembled a PC myself was somewhat around 2000 this looked really interesting to me.

The system runs on an Intel i7 quad-core processor and allows up to 32GB of RAM.

That’s certainly enough to run and operate a small SAP HANA instance.

Here’s a picture of this nice piece of technology:

2016_07_04_14_56_37_988523

Cutting the story short

I ordered that thing online together with two 16 GB DDR4 strips and an old/slow but relatively cheap IBM SSD with 480GB capacity.

All in all the bill for this was around 1400 Australian Dollars – not too bad for a system way more powerful than my MacBook Pro.

Of course: this doesn’t come with any display or input device (well, in fact the online shop I ordered from had a special and chipped in a Logitech K400 plus for free 😉 . You have to have a monitor to use this thing.

 

A few days later the whole pack arrived and I was really surprised by the weight of it: easily below one kilogram.

I’ll skip the whole un-boxing and assembly part, but will tell you: it’s easy.

The RAM modules just need to be clicked into place and the SSD installation requires to loosen and tighten a single screw. Definitively doable for a hardware n00b like myself.

The more complicated part – again: for me, others might just do this without blinking – was to put an operating system onto the new machine.

I didn’t buy a Windows license, since the whole purpose of this buy was to eventually run SAP HANA on it, so I went for OpenSuse Linux (Leap Distribution). Creating a boot-able USB thumb drive is relatively easy.

The only changes to this tutorial I did was

  • using ‘pv‘ to get an idea on the progress of the data transfer (installed via ‘brew‘) – yes, I used a Mac for this.
  • using a blocksize of 1M instead of the 4k mentioned in the tutorial.

 

The whole command set then looked like this:

sudo zsh

pv /path/to/downloaded.iso | dd of=/dev/rdisk2 bs=1M

 

Armed with this USB thumb drive stuck into one of the 4 USB ports, I simply started my “skullbox” and the OpenSuse installation screen appeared.

Again, I’m not the Linux expert on the installation side of things, so I basically when with most of the default settings and clicked through.

One reboot after that, I had OpenSuse running on the new machine.

Meanwhile in the kitchen

As TV chefs would say “… meanwhile I prepared something else …” which translates here into the download of the SAP HANA installation files from SAP Service Marketplace (Yes, you do need to have paid access to this).

 

The installation files come in a big multi-part RAR archive with Windows Auto-Extraction, which means, that the first part is an .EXE file.Unpacking on Linux however is not a problem; all you need is the UNRAR tool (if all fails ‘zypper install http://download.opensuse.org/repositories/openSUSE:/Leap:/42.1:/NonFree/standard/x86_64/unrar-5.3.3-1.3.x86_64.rpm should fetch this).

 

The rest of the installation is fairly well documented, and only small additional changes were required to make the installation work on this NOT SUPPORTED operating system:

 

a) PNG Library couldn’t be loaded, and the hdblcmgui woudln’t start.

Fixed by installing an older version via zypper install –oldpackage libpng12-0′

 

b) nameserver couldn’t be started due to missing SSL libs.The trace file listed

Can’t load ‘/playfield/hana_inst/51050838/DATA_UNITS/HDB_SERVER_LINUX_X86_64/instruntime/SSLeay.so’ for module Net::SSLeay: libcrypto.so.0.9.8: cannot open shared object file: No such file or directory at DynaLoader.pm line 193.”

 

Fixed by ‘zypper install libssl*’ and

ln -s /lib64/libcrypto.so.1.0.0 /usr/libcrypto.so.0.9.8

ln -s /lib64/libssl.so.1.0.0 /usr/lib64/libssl.so.0.9.8

 

(as seen here)After that the installation went through without further troubles.

The looooong fork

I have enabled the option for remote administration in OpenSuse, which means that the system now can be accessed via VNC.

For my Windows 10 based work laptop I find that MobaXTerm makes it really simple to access the Linux box – it even has a plugin that contains a VNC client.

For the Mac, there is actually a VNC client build in (I’m using El Capitan): just start “Screen Sharing” or put vnc://hostname:port into the Safari address bar and off you go…

 

After all of this (and some more wrestling with the Linux network setup – I still barely have a clue on that, but it seems to work now…)

I can now access my “HANA skullbox” via:

  • WIFI connection at homeoffice
  • drect CAT5 cable connection between my work laptop and the NUC

which are exactly the scenarios I wanted to support.

Starting a HANA Studio via X-Server works too:

2016_07_04_16_29_01_988524

But that’s neither fast nor pretty.

For working with the system I prefer using the local HANA Studio or the Web UI.

Of course I used ‘memory allocation limit’, but to have some fun, I cranked it up to the total 32 GB of installed RAM; so far no functional problems with this 😉

All of this setup happened after hours over maybe three evenings. Someone with more Linux know-how would probably be done in way less time.

So far I’m quite happy with this setup and I’m eager to try out what things work and where the hard limit of usability for this micro-HANA lies.

OK, that’s it for now.

AGAIN: this is a completely unsupported setup. No support, warranty, maintenance or endorsement is provided by SAP or myself for this.

There you go – now you know!

Lars

 

Things keep changing around here!

Fellow Community members, readers and otherwise interested!

Today I share a change in the organisation of this community.

Last week I returned the moderator and space editor privileges and the duties that come with those.

Lucas, who has done a awesome job as a moderator since he came aboard last year, will take it from here.

If this was a live audience I would ask for a round of applause for him – here instead I can only wish him the best.

As to why I took this decision (in case you’re wondering – this will take moment, so grab your coffee or ):

When we started the SAP HANA journey in 2010 internally and then 2011 with the global availability of the product, there was no community around it.

In fact, there was very little at all around it. Barely any documentation, nearly no SAP notes and only a few people that had a clue what this new thing did and how it did it.

Finding the right information, people who knew something and correct answers actually was hard.

I had some experience with SDN and sites like AskTom.com, so I knew that answering questions, explaining concepts and discussing specific questions can go a long way when it comes to “enablement” and knowledge sharing.

So, with the hope to spark active community participation I volunteered to do just that in 2011.

Fast forward five years on, there are now around 50 blog posts on SAP HANA, several topic leader batches, other communities (SAP internal JAM, stackoverflow) and a SAP Press book ‘under my belt’. I managed to talk a couple of SAP colleagues into spending their time here and provided answers as well as opinions to thousands of discussion threads. And let’s not forget the countless emails I wrote to colleagues, partners and people I don’t even know who asked me questions about HANA – only to be redirected to this very forum 🙂 .

And the community is thriving! There’s a good set of regulars (both SAP employees and externals) who share their expertise on a daily basis – just look at the current 12-month leadership board. Every week several blog posts and documents are created and shared.

Of course there’s more:

SAP notes on SAP HANA had been massively improved (thanks to Martin for his fabulous FAQ series), the SAP HANA docu. development team churned out guide after guide (Small surprises, well documented, a tip to the hat to Ralph Schroeder and the whole team) and the SAP HANA Academy brought video learning into the SAP HANA area – awesome!

In short, the community has grown and developed into a full fledged developer and admin community.

While I like to think that I had a share in that, I also think that my definition of “done” has been reached.

That bridge got built – let’s move on“, if you see what I mean.

In the wake of the upcoming conversion to 1DX the technical platform for this community will change and that is something that others will do better than I would. I assume that the SAP HANA product management and development teams might continue to provide more and more official material into via this channel. Also, there’s tons of high quality user generated that will be converted and available in the new forums.

All in all really nice times for anyone starting off with SAP HANA nowadays.

https://i0.wp.com/s2.quickmeme.com/img/4f/4f15ca326d661fd71177bcd909ec6023cc4b1688aee152ebb815e15a8fc380f8.jpg?w=474

And that’s all folks!

See you around.

Lars