SAP HANA Studio runs on the Eclipse platform Version 3.6. SAP HANA Studio can be used on the following platforms:

Java JRE 1.6 or 1.7 must be installed to run SAP HANA Studio. The path variable parameters have to be set for JRE. The correct Java variant installation has to be selected accordingly, 32 bit or 64 bit.

An installation path has to be defined while installing, otherwise default values will be applied, as shown:

We can monitor the system using the option. When we click on this button, details of all the available systems will be displayed. By default, information available in the memory, used memory, and so on will be displayed system wise. These results can be configured with what data needs to be displayed on the monitor screen. We can further drill down to the administration mode from this menu. Just a double-click on the system will take us to the administration section of that system, as shown in the following screenshot:

The option helps us with the administration of a particular system. More details on this option will be covered in the Administering SAP HANA – the Administration Console perspective recipe of this chapter.

As we deal with the administration of the system, this completely depends on the roles and authorizations we possess in the system.

The option opens up an SQL console, where we can write the SQL code for different purposes. We usually write the SQL code for DDL/DML/TCL operations, such as creating/altering a table, inserting/updating/previewing/deleting data contents of a table, or committing updates. The code written in this is reusable. We can save the code as a file on our local PC and use the same code in the future.

The SQL code will be executed based on the roles/authorizations we have on a system. We should have authorization to execute SQL on a schema; otherwise, the execution fails with an invalid authorization issue. The SQL Console window is as shown in the following screenshot:

The level we select while opening the SQL console is very important. As shown in the preceding screenshot, we can see the name of the schema for which the SQL console has been opened. Text in the header section of the SQL console will be in the following format:

SYSTEM_NAME (USER_NAME) HOST_NAME (Current Schema: SCHEMA_NAME)

Let's have a look at each of the fields:

The important thing here is that when we execute any SQL command without giving a schema name, it works on the schema which we have selected. When tables/views are created without giving the schema name, these will go and sit in the schema. There is no restriction on fetching data from any schema; we can run SQL on any schema to retrieve data.

The option helps us in searching for a table in the system. This icon will be active only when we select a catalog folder in the system, as tables will be located in the catalog section. Even though a schema is selected to find a table, the search will be executed on the entire system. All the tables with the given search string will be returned. We can also select to include column names in the search. In this case, results will consist of the column names as well. The minimum length of the search string is two characters.

The option can be used to search systems. The name of the system will be stored in the following format:

SYSTEM_NAME HOST_NAME (USER_NAME)

A search will be executed soon after giving a single character. A search string can be a part of the system name, host name, or a username. All values that match will be displayed. There are two more options, Open Administration and Open SQL Console, at the bottom of the search window, as shown in the following screenshot. If these are checked, it automatically opens the corresponding windows—Administration/SQL Console.

The option links the objects opened with the navigation pane. When this option is enabled and an object is selected, the corresponding object in the navigation pane will be highlighted automatically. For example, let us assume that we are working on a few tables, views, and procedures opened in SQL Console, which are present in different schemas. When we change from one object to another in the main window, the same object in the respective schema will be highlighted in the navigation pane. This allows us to search where the objects are located exactly when required.

In order to connect to a SAP HANA instance, we need to know the server credentials and details (user ID, server, password, and instance number). The left-hand side of the studio Navigator Pane shows the available HANA instances in SAP HANA Studio. Following are the steps to add a new system:

In this tab, we can have a snapshot of the overall status of the system. As shown in the screenshot in the Getting ready section of this recipe, it includes the system status, build version, hardware and software details, disk memory utilization, CPU utilization, and so on. We can dive into each category to get in-depth details.

In the Landscape menu, we can have a glance at different services running and their administration, configuring hosts, redistribution operation on tables, and so on.

Under this category, activities related to services are handled. All the services running across every host will be available here. We can start, stop, or kill the services. Memory usage and allocation details across different servers can be monitored, and the result set can be customized by fields to be displayed.

Hosts can be configured using Configuration. Assignment details of master/slaves for all servers can be monitored from this screen.

Tables have to be redistributed among index servers for effective utilization of resources and optimal performance. Performance will be optimal when frequently joined tables are located in the same index server compared to those that are spread into different index servers. Therefore, it is highly recommended to distribute the tables among index servers. This can be performed soon after adding the hosts or later as well based on the models we build on the tables, for which we have separate options available.

The same operation has to be applied for partitioned tables as well. Partitioned tables will be distributed across different index servers. This location can be specified manually or determined by the database when it has been partitioned initially. The size of these partitions will be growing in time, which may not give us optimal results. Therefore, it is required to redistribute the partition tables as well.

For an administrator, system status, services, and resources have to be monitored continuously. Configuring alerts will help in making timely decisions. In the generation of alerts, the statistics server plays a major role. When the SAP HANA system is started, the statistics server is automatically activated on the host of the active master name server. The statistics server runs SQL commands internally and collects all the information from all index servers. From this data, important alerts information will be displayed in the Overview tab and the Alerts tab will hold detailed level data.

E-mail notifications can be configured for these alerts and thereby all stakeholders will be updated on alerts. The sender's e-mail address, recipient's e-mail address, and SMTP port details are required for this setting. The recipients listed will be notified by e-mail upon generation of alerts by the statistics server.

All performance-related processes can be monitored from this tab. This includes threads, sessions, SQL cache, expensive statements, progress in jobs, load details, and so on.

Whenever there is an I/O operation in the SAP HANA system, it starts threads on the corresponding server. The detailed level of data on these threads can be monitored and can be cancelled if required. Similarly, details related to open sessions, SQL plan cache, expensive statements executed, and jobs which are progressing can be monitored.

Apart from these, the load impact on the SAP HANA system can be monitored. Flexible options are provided to change the filters dynamically so that we can drill down or drill up on the data of any server in the host. This really helps in understanding where the server is being impacted with peak loads on which server.

It makes no sense if system resources are not effectively utilized after taking on a powerful, in-memory database such as SAP HANA.

The Volumes tab gives us complete detail on the memory statistics of all servers (name server, index server, statistics server, and XS Engine) on the host. This includes total disk size, used disk size, remaining disk size, and volume size. Details about data volumes, log volumes along with the page size, and block size are also available.

The Configuration tab has highly sensitive data as all the system configurations are maintained in this section. For the SAP HANA system, configurations are stored in the form of configuration files—the .ini files. We will have several values, and these settings can be changed for the host or the entire system. When the value is changed, a green mark will be shown indicating changed values. Against a few values, a minus sign will be displayed. We cannot change these values and they are set by the system itself, as shown in the following screenshot:

The System Information tab holds the system information table, which has data related to the SAP HANA system. Data in each table can be viewed and a detailed analysis can be performed to take decisions for system maintenance. This includes tables on memory consumption, work load statistics, transactions in use, and many more.

Trace and logfiles of the system can be browsed from this menu (all the trace and logfiles right from the starting date of the system setup). These files can be viewed or downloaded to a local PC.

Detailed information about the actions of the HANA database can be obtained using different traces. We can activate these traces and configure them as per our requirements.

We can configure the following traces:

Information views are of different types— Attribute View, Analytic View, and Calculation View. When we use an information composer, a calculation view is created. Calculation views are basically a query which is built on top of analytic views and other calculation views to meet a complex business requirement.

Some of the features are as follows:

SQLScript provides a flexible programming language environment as a combination of imperative and functional expressions of SQL. The significant part is that it allows developers to easily express data and control flow logic by using DDL, DML, and SQL query statements as well as imperative language constructs, such as loops and conditionals. On the other hand, functional expressions are used to express declarative logic for the efficient execution of data-intensive computations. This logic is internally represented as data flow, which can be executed in parallel as SAP HANA supports massive parallel processing.

Some of the features are as follows: