Add Databases to the SAP HANA Database Explorer

Databases can also be added directly to the SAP HANA database explorer. To connect to an SAP HANA Cloud or on-premise database, the host, port, user name, and password must be provided.

1. In the SAP HANA database explorer, press the + button to add a new database.

   

2. For database type, choose SAP HANA Database.

   

   Note that an SAP HANA, express edition or on-premise database can have two types of databases; system and tenant. This is known as multitenant. System databases are used to manage one or more tenant databases and are only applicable to on-premise systems. For further details, see Server Architecture of Tenant Databases.

3. Provide the host, port, user name, password, and name to show in display.

   

   Remember to remove the colon and port number from the host name and add the port to the port field.

   ---

   When connecting to an SAP HANA Cloud instance, the database connection must use TLS.

   

   The public root certificate of the certificate authority (CA) that signed the SAP HANA Cloud instance’s server certificate is required. This certificate is likely already available in the system certificate store on the operating system and accessible by the browser, but if not, it can be pasted into the UI. For more information see Secure Communication Between SAP HANA Cloud and JDBC/ODBC Clients.

   ---

   For a HANA Cloud database, the host and port values can be copied from SAP HANA Cloud Central.

   

   ---

   If you are using an SAP HANA, express edition or on-premise database, the port numbers for a system or tenant database can be determined by running the following query against the System database.

   SELECT "DATABASE_NAME", "HOST", "SERVICE_NAME", "SQL_PORT" FROM SYS_DATABASES.M_SERVICES WHERE SQL_PORT != 0;
   

   

4. After pressing OK, a new database will appear whose type is SAP HANA Database.

   

   Advanced options can be used to specify database properties.

   

   An example of what can be configured is shown below.

   isolationLevel=SERIALIZABLE;locale=fr_FR;schema=HOTEL;client=55
   

   These values can be seen by opening a SQL console and noticing the schema that the database is using or by executing the following queries.

   SELECT * FROM M_SESSION_CONTEXT WHERE CONNECTION_ID=current_connection;
   SELECT * FROM SYS.M_CONNECTIONS WHERE CONNECTION_ID=current_connection;
   SELECT ISOLATION_LEVEL FROM PUBLIC.M_TRANSACTIONS where CONNECTION_ID = current_connection;
   

   For additional details, see Add Instances to the SAP HANA Database Explorer.

A Data Lake IQ is a column oriented, disk based relational store that can be used to economically store data that is not updated frequently. Additional details can be found at What is SAP HANA Cloud, Data Lake.

1. Add a Data Lake.

   

   Ensure that the allowed connections list is set to Allow all IP addresses.

   

2. In the database explorer, choose to add a new connection of type Data Lake, IQ.

   

   The user name is HDLADMIN.

   

   The connection details can be copied from the instance tile.

   

3. The catalog browser can be used to view database objects and a SQL Console can be opened to query the database.

   SELECT CURRENT USER FROM DUMMY;
   SELECT * FROM SYS.SYSINFO;
   SELECT * FROM SA_DB_PROPERTIES() WHERE UPPER(PropName) LIKE '%NAME%';
   SELECT * FROM SYS.SYSOPTIONS WHERE UPPER("option") LIKE '%AUTO%' OR UPPER("option") LIKE '%COMM%' OR UPPER("option") LIKE '%ISOL%';
   

   

An SAP HANA database can be set to have a specified usage, such as development or production. The following SQL statements will display the current usage value and then change it to production.

SELECT * FROM M_INIFILE_CONTENTS WHERE KEY = 'usage';
ALTER SYSTEM ALTER CONFIGURATION ('global.ini', 'DATABASE') SET ('system_information', 'usage') = 'production' WITH RECONFIGURE;

After refreshing the page, there will be indicators that the database instance being worked with is a production database as shown below and care should be taken before executing operations that may affect performance or make unintentional changes to the database.

For additional details on this parameter, see the system_information usage parameter in SAP HANA Configuration Parameter Reference.

1. Open the SAP BTP cockpit and from the Service Marketplace under the subaccount level (named trial in the screenshot below) find and open the SAP Business Application Studio.

   

2. Create a new SAP HANA Native Application dev space.

   

3. Once the dev space has been created, open it.

4. Set the cloud foundry org and space by pressing F1 or Ctrl + Shift + P to open the command palette. Search for the command CF Set Org and Space.

   

   The endpoint value can be found in the SAP BPT Cockpit.

   

5. From the Welcome tab (can be opened from the command palette if it is not open), choose Start from template.

   

6. Select SAP HANA Database Project.

   

7. Provide the following values and click Finish.

   Setting	Value
   Project name:	myHANAProj
   Module name:	db
   Schema name:	mySchema
   SAP HANA Database Version:	HANA Cloud

8. Once the project generation finishes, open the tool palette (F1) and choose SAP HANA: Create SAP HANA Database Artifact. Provide the following values and click Finish.

   Setting	Value
   Path:	/home/user/projects/myHANAProj/db/src
   Version:	HANA Cloud
   Type:	Table (hdbtable)
   Name:	myTable

9. Paste the below content into the file.

   COLUMN TABLE myTable
   (
       "ID" INTEGER,
       "VALUE" VARCHAR(50)
   )
   

10. Use the SAP HANA Projects Explorer to deploy the table.

    

11. Open the database explorer to view the deployed HDI container and table.

    

    A new HDI container with the table is shown.

    

1. Open SAP HANA Web IDE for SAP HANA if using SAP HANA, express edition or an on-premise install.

   

   An alternative way to determine the URL for the SAP Web IDE for SAP HANA is to run the below command on the machine where SAP HANA on-premise is installed.

   xs apps
   

   

2. Ensure that the SAP HANA extensions are enabled.

   

3. Open the development pane.

   

4. Create a new HANA database project.

   Right-click Workspace and choose New | Project from Template.

   In the Search field, enter HANA.

   If you are using SAP HANA Cloud, in the Environment dropdown, select Cloud Foundry.

   Select the template SAP HANA Database Application.

   

   Fill in the following values and press Finish.

   Setting	Value
   Project Name:	myHANAProj
   Space:	development
   SAP HANA Database Version:	Choose the appropriate version such as HANA Cloud or 2.0 SPS 04

5. After the wizard finishes, create a table by right-clicking on the src folder and choosing New | Database Artifact.

   Specify a file name of test and a file type of .hdbtable.

   Paste the below content into the file and choose File | Save.

   COLUMN TABLE test
   (
       "ID" INTEGER,
       "VALUE" VARCHAR(50)
   )
   

   

6. Deploy the HDI container. Right-click the db folder and choose Build | Build.

   

   The Console window will show the result of the build process.

   

7. Once complete, the HDI container can be opened in the SAP HANA database explorer by right-clicking on the db folder and choosing Open HDI Container.

   Alternatively, it can be added via the Add Database dialog.

   

   Notice, above, that a user name and password are not requested. The connection will use a technical user, which was generated when the HDI container was created.

   In the database browser and in the SQL console, notice that the schema name is not shown.

   

   It is also possible to connect to an HDI container as an admin, which enables the ability to grant additional permissions in the HDI container. For an example of where this might be used, see Grant a Support User Access to an SAP HDI Container. Note, never use the Admin connection to perform DDL (such as create, update, or delete) operations.

   

For an example of creating a calculation view inside an HDI container, see the video titled Develop Apps at the end of the blog post Getting Started with SAP HANA Cloud II | Basics

The mission Get Started with XS Advanced Development provides further examples of using an HDI container.

In the next tutorial, additional tables, views, functions, and procedures that will be used in subsequent tutorials in this group will be created directly in the database rather than in an HDI container.

Congratulations! You have added different databases to the SAP HANA database explorer.