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.
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.
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 DROPTABLE, 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: : 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
declare _exists int := 0;
when count(*) >0 then 1
end into _exists
schema_name = IFNULL (:schema_name, current_schema)
and object_name = IFNULL (:object_name, '');
obj_exists = :_exists;
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.
obj_exists (schema_name => NULL
, object_name => 'OBJ_EXISTS') as OBJ_EXISTS
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“.
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.
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:
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.
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 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.”
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.
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.