Skip to Content

Choose an SAP HANA Database

test
0 %
Choose an SAP HANA Database
Details

Choose an SAP HANA Database

November 23, 2020
Created by
March 29, 2020
Learn about SAP HANA Cloud and SAP HANA, express edition and choose one that will be used with the SAP HANA client interfaces in subsequent tutorials.

You will learn

  • How to create an instance of SAP HANA Cloud or SAP HANA, express edition
  • How to connect to a SAP HANA Cloud or an SAP HANA, express edition database

Prerequisites

  • A Microsoft Windows, Linux or Mac computer
  • A machine that can run SAP HANA, express edition if the SAP HANA Cloud trial is not used

This tutorial will provide guidance on setting up an instance of SAP HANA running in the cloud or on-premise so that it can then be connected to using a few of the SAP HANA Client interfaces.

For more information on SAP HANA, consult What Is SAP HANA.

Note, for connections from additional sources such as SAP Analytics Cloud, Jupyter Notebooks, SAP Business Warehouse, SAP Data Intelligence Cloud, and SAP ERP Central Component see Connection Guides for SAP HANA Cloud.


IMPORTANT: Complete the first 3 tutorials, and then you can select any of the following tutorials about connecting with different client interfaces.


Step 1: Overview of SAP HANA Cloud and SAP HANA, express edition

There are multiple versions of SAP HANA. The information below is a list of links for the documentation of each version. Note that this tutorial will discuss SAP HANA Cloud and SAP HANA, express edition.

Version Notes
SAP HANA Platform 1.0 Released in 2010. Current version is 1.0 SPS (Support Package Stack) 12.
SAP HANA Platform 2.0 Released in November 2016. Current version is 2.0 SPS 05, which was released on June 26, 2020.
SAP HANA, express edition Released in September 2016. Current version is 2.0 SPS 04.
SAP Cloud Platform, SAP HANA Service Cloud version of SAP HANA 2.0.
SAP HANA Cloud Released in March 2020 and is the successor of the SAP HANA service. New features are released quarterly.
Log on to answer question
Step 2: SAP HANA Cloud

Here are a few benefits of using SAP HANA Cloud:

  • Software updates are automatically applied by SAP.

  • Hardware is managed by a cloud provider (e.g. AWS, Azure, or GCP).

  • Backups are automatic and recovery requests are handled via service requests.

  • The memory, compute and storage settings can be changed as your needs change.

  • The ability is provided to expand data storage from in-memory, to native storage extensions, to a data lake, while providing a common access layer that enables you to have further control over performance and cost. See also Lower Your Data Management Costs With SAP HANA Cloud.

Here are a few differences between SAP HANA Cloud and an on-premise version:

  • Every SAP HANA Cloud instance is one SAP HANA database. An on-premise version can have multiple databases per install, which is known as multi-tenant.

  • Connections to an SAP HANA Cloud instance must be secure and require a minimum SAP HANA client version of 2.4.167.

  • The administration user for SAP HANA Cloud is named DBADMIN while for an SAP HANA 2.0 database it is SYSTEM. For additional details see Predefined Users, SAP HANA Cloud Administrator DBADMIN, and Predefined Users in HANA 2.0.

Log on to answer question
Step 3: Connect to SAP HANA Cloud
  1. Follow the instructions at Create an SAP HANA Cloud Instance Using the Cloud Cockpit or SAP HANA Cloud Trial.

    Once the SAP HANA Cloud instance is created, take note of the admin user as well as the endpoint (host:port) needed to connect to the database. This will be needed in subsequent tutorials in this mission.

    You should now have a new SAP HANA Cloud instance created in the SAP Cloud Platform.

    For more information on the SAP Cloud Platform see the following:

  2. Open the SAP HANA database explorer by choosing Actions > Open SAP HANA Database Explorer from the landscape tool.

    Open with DBX

    You will be asked to enter the credentials for the database. Enter DBADMIN and the password that was specified when the instance was created.

    authentication for dbx

    The SAP HANA database explorer provides the ability to browse catalog objects and execute SQL statements from the SQL console. For more information, see the tutorial group Get Started with the SAP HANA Database Explorer and SAP Help Portal topic SAP HANA Database Explorer.

  3. Open a SQL console by clicking the Open SQL Console toolbar item in the top-left of the screen.

    Database Explorer
  4. Run the following query to see the name of the database you are currently connected to. Notice it is H00.

    SELECT * FROM M_DATABASE;
    
    Database Explorer Result
  5. In the SAP HANA database explorer, the current schema shows DBADMIN. A schema is a container for other database objects such as tables and views.

    current schema

    Another way to see the current schema and the connected user is via SQL as shown below. The DUMMY table is available in every HANA database that has one column and one row. It provides a convenient way to call a function or perform a simple test.

    SELECT CURRENT_USER, CURRENT_SCHEMA FROM DUMMY;
    
  6. To see the list of services, enter the following SQL statement:

    SELECT * FROM M_SERVICES;
    
  7. To see information about the database server, enter the following SQL statement:

    SELECT * FROM M_HOST_INFORMATION;
    

Congratulations! You have connected to SAP HANA Cloud and performed a few queries.

Log on to answer question
Step 4: SAP HANA, express edition

SAP provides a free streamlined version of SAP HANA that runs on developer laptops called SAP HANA, express edition.

SAP HANA runs on a few versions of Linux. SAP HANA, express edition provides virtual machine images that can be run on Microsoft Windows, macOS and Linux machines as well as binary installs as described at SAP HANA, express edition - Implement. In terms of availability, a choice between either the server or the server + applications can be made. The applications include the SAP HANA cockpit, the SAP HANA database explorer and the SAP HANA Web IDE. Further details about a specific version can be found in the release notes.

Choose an option and proceed to install SAP HANA, express edition. The server-only install, which requires less memory, is sufficient to complete the tutorials in this mission. Be sure to note the SAP HANA database master password, as it will be needed later for connecting.

At this point, you should have a running instance of SAP HANA, express edition.

Log on to answer question
Step 5: Connect to SAP HANA, express edition

A default installation will contain one system database named SYSTEMDB and one tenant database named HXE.

The system database contains information about the tenant database(s) and is used for overall administration. The default port for the system database is 39013. Later in this tutorial mission, a connection to the tenant database will be made and tables will be created and populated with data. For more information, see SAP HANA Tenant Databases Operations Guide.

The SAP HANA, express edition VM install includes a command line tool (part of the SAP HANA client install) called HDBSQL that can be used to query the database.

The following steps will demonstrate connecting to and examining a SAP HANA, express edition database.

  1. Enter the following to connect to the system database:

    hdbsql -n localhost:39013 -u SYSTEM -p Your_Password
    
    hdbsql connected

    Notice that the database being connected to is SYSTEMDB.

    The example above uses localhost since hdbsql is running on the same machine that the database is trying to connect to. The host name, hxehost, is seen in the terminal above.

    If hdbsql is not found and you are logged in with another user, try connecting as the user hxeadm and run the command again.

    su hxeadm
    

    If the hdbsql command cannot be found, simply read through the following examples. The installation and further examples of running HDBSQL will be covered in subsequent tutorials.

  2. Determine the IP address of the machine that is running SAP HANA, express edition. Record the value as it will be needed in later tutorials in this mission. It is important to note the IP address or host name of the machine, as this will be needed in later tutorials.

    ip addr
    
  3. The following are a couple examples of commands followed by a SQL query, which when run against SYSTEMDB returns information about the databases running on the SAP HANA instance.

    \al
    \s
    SELECT * FROM SYS_DATABASES.M_SERVICES WHERE SQL_PORT != 0;
    
    hdbsql commands

    The \al or align interactive option when enabled increases the readability of the output.

    The \s command shows status information. It shows that that the SAP System Identification (SID) is HXE, that the connected database is SYSTEMDB, and the currently logged in user is SYSTEM.

    The result of the SELECT against the M_SERVICES table shows that there are two databases, named SYSTEMDB and HXE and that they are accessible on ports 39013 and 39015.

    The instance number can be derived from a port number using the second and third numbers in the port number 39013. In this case, the instance number is 90.

    If the SQL statement returns more than one screen of text, entering a space will show the next screen of results. See also the pager option \pa.

  4. Enter one of the below commands to exit from viewing the results of the select statement. Note that commands can be prefixed with a forward slash(\) or a colon(:).

    \q
    :q
    
  5. Connect using the instance number and database name as shown below.

    hdbsql -n localhost -i 90 -d HXE -u SYSTEM -p Your_Password
    
  6. Enter the following to display database connection information.

    \s
    

    Notice that this time the database connected to is HXE.

    hdbsql commands

For further information, see Port Assignment in Tenant Databases and Connecting to SAP HANA Databases and Servers.

Congratulations! You now have access to an SAP HANA instance and understand some of the differences between SAP HANA Cloud and SAP HANA, express edition.

Which versions of SAP HANA are covered in this tutorial?
×

Next Steps

Back to top