Persistence services on HANA Cloud Platform: Not j...

manjunath_b · ‎08-22-2016

This post is part of a 3-part series about HCP Persistence Service ASE.

Part1 is an overview of ASE DBaaS on the HANA Cloud Platform, and

Part3 details the usage of ASE on HCP with Java Apps and JPA.

In this post we will have a detailed look at connecting to your ASE instance on HCP, using Eclipse.

Prerequisites:

User should have Eclipse on his system

ASE database system should be provisioned on your HCP account. ASE DB is not available on HCP trial and a valid license is needed to provision the same on your HCP account.

To validate this, login to your HCP account cockpit and navigate to Persistence -> Database Systems and verify that the asedb (ASE) system is present in the list.

Create ASE DB instance on HCP:

You can use the HCP cockpit of your account to create an SAP ASE database instance and assign properties to it.

On the cockpit of your HCP account, navigate to Persistence -> Databases & Schemas. A list of database instances associated with your account will be displayed. Click on New to create a new database instance.

A dialog New Database/Schema is displayed. Enter the details as follows:
1. Database ID : <user defined name>E.g. asedemo
2. Database System : asedb (ASE)or similar (The database version provisioned on the account will be displayed accordingly)
3. Database Size : <max size of the database instance> E.g. 4GB (The size of the database should be a natural number, a multiple of 4, > 24MB and less than the quota of the database system. The quota is displayed in the dialog to assist you)
4. Database User : <name of the user you wish to create> E.g. DBUSER
5. Password and Verify Password : <password for the user to connect to the instance>. Click on Save.

Click on Events (or it could automatically takes you to the Events tab) to monitor the progress of the data base instance creation.

Click on Overview and wait until the status of the instance changes to Created.

Download the HCP SDK:

To connect to the ASE DB instance on you HCP account, it is necessary that we have the SAP HANA Cloud Platform SDK.

This SDK provides the HANA Cloud Platform Console Client (the ‘neo’ tool set) which can be used to open a database tunnel to HCP, so that we can access the ASE instance on cloud like a local database.

To download the SDK, navigate to the URL SAP Development Tools and download and unzip the Java Web Tomcat 7 or Java Web Tomcat 8 versions to your system.

Proxy settings:

Before we open the tunnel using the console client, we need to set the proxy details in the system, if there is a firewall behind which your system exists.

To do this, we need to set the values of the respective OS Environment Variables. Like on Windows, we need to run the following commands on your command prompt:

set HTTP_PROXY_HOST=<proxyhost>

set HTTP_PROXY_PORT=<proxyport>

set HTTPS_PROXY_HOST=<proxyhost>

set HTTPS_PROXY_PORT=<proxyport>

set HTTP_NON_PROXY_HOSTS="localhost"

If proxy authentication is involved, then set the corresponding variables as well.

set HTTP_PROXY_USER=<user name>

set HTTP_PROXY_PASSWORD=<password>

set HTTPS_PROXY_USER=<user name>

set HTTPS_PROXY_PASSWORD=<password>

Note: Sample proxy settings can be found in the readme.txt file in the \tools folder of your HCP SDK.

With these settings complete, we are now ready to open a database tunnel from your system to the ASE DB instance on your HCP account.

Open Database Tunnel:

A database tunnel allows you to connect to a remote database instance through a secure connection.In the command prompt, navigate to the \tools folder of the SDK which was downloaded and unzipped in the previous section. The tools folder hosts the neo.bat and neo.sh files.

Note: The actual SDK version could differ from the one shown in the screenshot, based on when and what version were downloaded.

To open a tunnel, we use the ‘open-db-tunnel’ command from the neo tool set. Enter the command,

neo open-db-tunnel -h <host> -u <user> -a <account> --id <schema ID>

<host> : host name of your HCP account E.g. eu1.hana.ondemand.com

<user> : your HCP account user E.g. S-user ID

<account> : your HCP account name

<schema ID> : Database ID of the ASE instance created in previous section E.g. asedemo

Note: The id parameter has two -, while the others have only one.

The SAP HANA Cloud Platform Console Client should open and ask you for the password. When prompted, provide the password of your HCP account.

If all goes well, the tunnel is opened and the details are displayed for further usage.

                Host name: localhost

                Database type: ASE

                JDBC Url: <the jdbc url string> Make a note of the host name and jdbc port. These will be needed in further steps.

                Database name: NEO_<generated GUID> Make a note of this too.

Now that the tunnel is opened, you can connect to the remote ASE database instance like a local database, using the details you have just noted.

Note: The database tunnel must remain open while you are connected to the remote instance. You can close it when you have completed the session. Else the tunnel will automatically close after 24hrs.

Connect to remote ASE via Eclipse:

You use the Eclipse Data Tools Platform (DTP) to connect to the SAP ASE database in the cloud. In your Eclipse, open the Data Source Explorer view. You can navigate to Window -> Show View -> Data Source Explorer to open the view.

In the Data Source Explorer, select the Database Connections node, right-click and from the context menu click on New…

In the New Connection Profile dialog, select Connection Profile Type as ‘Sybase ASE’, enter a NameE.g. Demo Sybase ASE and a Description (optional). Click on Next.

We need to choose a driver definition before we proceed. In the New Sybase ASE Connection Profile dialog, click on the . In the New Driver Definition dialog, under the Name/Type tab, select the driver template as Sybase JDBC Driver for Sybase ASE 15.x and enter the Driver name: as ASE JDBC Driver.

Click on JAR List tab and remove any existing driver files by using Remove JAR/Zip. Add the correct driver file by clicking on Add JAR/Zip and browsing to the driver file in the downloaded SDK. The driver file should be located in your SDK under the path /repository/.archive/lib/jconn-<version>.jar (In my case jconn4.jar)

Click on the Properties tab and edit the Driver Class to match the version of driver file. Click on OK.

In the Connection tab, enter the details of the database noted from the console client.

Host: localhost

                Port: <jdbc port noted from JDBC Url>   E.g. 30015

                Database name: <database name noted from tunnel details> E.g. NEO_<GUID>

                User name: <database user created in HCP cockpit> E.g. DBUSER

                Password: <password of the above user from HCP cockpit>

                Select Connect when the wizard completes. Click on Test Connection.

If everything is fine then you should get a message saying Ping Succeeded. Click on OK.

Click Finish. If everything is fine then the database connection should be added to the view for usage.

ASE DB - SQL Smoke Test:

Now that the remote ASE database instance has been connected in Eclipse, we will run a quick smoke test with SQL statements to create a simple table, insert a couple of values, view the inserted values with a select and drop the table.

Right click on the database connection and in the context menu choose Open SQL Scrapbook to open a SQL console to interactively run SQL queries on the ASE database.

Select your database name in the dropdown Database: to execute SQL queries on.

In the SQL Scrapbook, enter the following SQL statements. Right click and in the context menu, click on Execute All.

create table employee (firstname char(40), lastname char(40));

insert into employee values ( 'testhcp', 'user' );

insert into employee values ( 'testhcp1', 'user1' );

select * from employee;

drop table employee;

The statement execution starts and completes. We can monitor the status of the execution in the SQL Results view and the Status views.

You can click on individual statements to view the state of each of their execution. Click on the select statement to view the created table with the inserted values.

Done! You have successfully created an ASE DBaaS instance on HCP, opened a database tunnel to access the remote database, connect to the instance using Eclipse and run a SQL Smoke Test to check the database instance!!

Coming up: In the next part, we will look at how to bind an ASE DB on HCP with a sample Java application and interact with it using JPA.

Other interesting blogs on ASE in HCP:

SAP HANA Cloud Platform (HCP) – SAP Adaptive Server Enterprise Persistence Instance (Part 1)

SAP HANA Cloud Platform (HCP) – SAP Adaptive Server Enterprise Persistence Instance (Part 2)