If you have had anything to do with SAP lately I’m sure you have heard of HANA. Okay, well what is it? (No, it’s not the Joe Wright film).
HANA is a database which uses In-Memory technology to speed up the retrieval of data. Additionally, HANA stores data in not only in rows but also by columns to speed up OLAP (online analytical processing) queries on very large data sets. HANA is optimized to utilize the capabilities of multi-core processors to achieve a high level of parallelization. (For a very good in-depth look at the performance of HANA, see the series of wiki posts by Werner Daehn on SAP SCN). One real benefit of HANA for business is it allows for real time analysis and forecasting.
From the PowerBuilder programmer perspective, HANA is another data source which can be connected to. At this time it means using a Java Database Connectivity (JDBC) connection. The first step in this process is to set up the database server and install the driver. The easiest and relatively cheapest way here is to set up an instance using Amazon Web Services (AWS). You can learn more and set up an AWS account here . After you have established an account on AWS you will need to create a key pair. This must be done on the same server you intend to have HANA hosted on. See the instructions on AWS here. Once this is set up you will need your AWS account number (found on the ‘Account Activity’ page when you have logged in) to request a HANA instance.
Now go to this link on the SCN to get the process rolling.
Accept the user agreement then enter your AWS account number on the following window.
Make sure the regional dropdown has the same value as where you created your key pair earlier.
Once this process is done you will jump to the AWS website to configure your HANA instance.
You may wish to give your stack a more meaningful name.
The default is the smallest size. The larger sizes are listed; my understanding is that they all have the same amount of storage space – 154GB.
The KeyName is the same name specified when the key-pair was created. Don’t make the mistake I made and use the full filename of the key file saved locally – this will not work.
Finally you can review everything prior to creating the stack.
If the create is successful you will see ‘CREATE_COMPLETE’ in the status. Errors can be checked on the Outputs tab. In general any errors will relate to having the key-pair name wrong or perhaps trying to create the stack on a server where you do not have a key-pair set up.
You need to set up an Elastic IP to associate with your Instance. From the main console page choose the Elastic IP link.
Allocate a new one.
Select the address then choose ‘Associate Address’.
Then choose your Instance.
A note about usage of HANA on AWS. To keep your Amazon charges down, you will want to stop any running Instances when you are not using them. This is a simple process from the AWS Console. Remember, however, you MUST start the Instance AND associate the Elastic IP address to the newly started Instance each time you wish to connect to it.
You can change the start page on your AWS account to show the EC2 virtual servers to save time.
Remember to Start the Instance and associate the Elastic IP to it each time you wish to connect.
To start the Instance you check it, select ‘Instance Actions’ and then ‘Start’. After a few moments – maybe a minute – the instance status will say ‘Running. Then go to the Elastic IP tab, choose the IP address, and associate it to the now running instance.
Now you need to load the SAP HANA Studio software on your workstation so you can administer your database.
On the SAP HANA Developer Center page on the SAP Community Network there are links to download the HANA Client and HANA Studio. Make sure you use this portal since it will have the most current versions available. The Client portion you need primarily for the database drivers. The Studio is similar to any number of database administration packages found for other databases. It allows you to create users, tables, schemas, etc. in a graphical environment (you can also do these at a command prompt as well).
When you have downloaded the archive files, unpack them to a temporary folder then run the hdbsetup.exe installer. You do this for both the Client and the Studio.
In order to connect your local HANA Studio to your database on AWS you need to modify the ‘hosts’ file on your machine. Create an entry in your hosts file similar to this:
123.45.678.9 imdbhdb
Where ‘123.45.678.9’ is the elastic IP address you have associated with your instance on AWS. The hosts file is normally found in Windows at “C:\Windows\System32\drivers\etc\”. In Windows 7 you need administrator access to write to it. (Click on Start, search for ‘notepad’, right click on notepad.exe in the search results, choose ‘Run as administrator’).
Now open the HANA Studio; the left section defaults to the ‘Navigator’ tab. Right click inside the blank area below the tab toolbar and choose ‘Add System’.
In the System Dialogue enter the Hostname (from the hosts file), the instance number (generally ‘00’) and a description.
Click ‘Next’
Use ‘SYSTEM’ for the user and ‘manager’ as the password (standard default password). Don’t worry, the password can be changed later. Once you have your system set up you want to create a new user you will use going forward when connecting anyway (i.e., don’t use SYSTEM).
Click ‘Finish’ and, if all goes well and your AWS instance is running you will see the following in the Navigator.
With the green box indicating you are connected.
To add a user, open the Catalog on the System and choose New User.
Fill in the user name, password information, Role (use ‘Public’) and then click on the execute icon.
You must now give SQL Privileges to the user. Click on the SQL Privileges tab.
Type in the name of the Catalog (SCHEMA) to grant to – in this case SYSTEM. The matching items list is populated automatically. Choose the SCHEMA object.
To the left this list of privileges will be shown. For this example I choose Execute, select, insert, update, and delete. Note that when you check them the ability to grant them is defaulted to ‘No’. Make sure to choose ‘Yes’.
After you execute the changes to the user to save them, you will need to ‘log on’ to the system as the new user. To do this, return to the ‘Navigator’ tab and add a new system. This time use ‘SAMPLEUSER’ as the user for the system. When you connect you will be prompted to change the password. This step must be done prior to attempting to connect via JDBC in PowerBuilder or the connection will fail. Once the password change has been done you may delete the System for the SAMPLEUSER id if you wish.
Now let’s set up our connection to HANA in PowerBuilder. First you need to set up your Classpaths in the System Options dialog, Java tab from the Tools menu. This should be the full path and filename of the driver installed via the HANA Client install. You MUST make sure you use the 32bit driver.
In addition, you may have to update the CLASSPATH environment variable on your machine to the same value as above. Per the PowerBuilder documentation you shouldn’t need to do this but in my case I couldn’t get things to work correctly unless I did this.
Now go to the Database Profiles, choose JDB JDBC and then New.
- Profile name: pbhanatest
- Driver Name: com.sap.db.jdbc.Driver
- URL: jdbc:sap://ec2-55-222-56-111.compute-1.amazonaws.com:30015? reconnect=true
(Note the punctuation here. The ec2-55-222-56-111.compute-1.amazonaws.com portion includes the public DNS for the database and will be different for you. The ‘00’ portion of the ‘30015’ represents the instance number – change it if you used a different value than ‘00’.)
Logon ID/Password: the user you created in HANA (SAMPLEUSER was the example)
Save and connect to your HANA database.
Now you can create and load data into tables. I chose to download some sample data relating to aircraft animal strikes to simulate a sufficiently large table. In this case the data set included over 180 thousand rows of data.
Once you have your data loaded you can then create your standard PowerBuilder datawindows via a SQL statement or stored procedure.
In my application I used the following Connection string within PowerBuilder (open event):
SQLCA.DBMS = "JDBC"
SQLCA.LogPass = <password>
SQLCA.LogId = "MB"
SQLCA.AutoCommit = False
SQLCA.DBParm = "Driver='com.sap.db.jdbc.Driver',URL='jdbc:sap://ec2-55-222-56-111.compute-1.amazonaws.com:30015? reconnect=true',PBCatalogOwner='SYSTEM',OJSyntax='ANSI'"
I set up two simple datawindows which grouped the data, one the number of strikes by animal, the other the number of strikes by airport.
These two datawindows perform simple grouping selects and return within moments. In the future I will be exploring stored procedure datawindows.
Conclusion
Setting up and connecting to a SAP HANA database via JDBC is fairly simple once you have all the pieces in place. Utilizing Amazon Web Services is a low cost way to investigate the capabilities of a HANA database from within PowerBuilder.deProject
