PUBLIC
SAP HANA Database Explorer 2.0 SP 12 Document Version: 1.0 – 2020-05-14
SAP HANA Database Explorer
AP affiliate company. All rights reserved.
Content
1 Getting Started With the SAP HANA Database Explorer. . . .4
1.1 What's New in SAP HANA Database Explorer. . . 5
1.2 Open Catalog Objects. . . .8
1.3 Create URL Shortcuts to Database Objects in the SAP HANA Database Explorer. . . 9
1.4 Search for Database Objects in the SAP HANA Database Explorer. . . .9
1.5 Add a Database to the SAP HANA Database Explorer. . . .10
1.6 Add HDI Containers and Databases to the SAP HANA Database Explorer. . . .13
2 Security in the SAP HANA Database Explorer. . . .17
2.1 Data Protection in SAP HANA Database Explorer. . . 17
Delete Personal Data. . . 19
Delete Your Personal Data. . . .21
2.2 Securing the SAP HANA Database Explorer. . . .22
Secure the SAP HANA Database Explorer from Web Socket Attacks. . . .23
3 Analyzing and Diagnosing Errors. . . . 25
3.1 Set Session Logging. . . 25
3.2 View SQLScript Code Coverage Report. . . .26
3.3 View Database Procedures in the SAP HANA Database Explorer. . . 27
Debug Procedures in the SAP HANA Database Explorer. . . .27
3.4 SAP HANA Database Explorer Debugger Tutorials. . . .29
Tutorial: Connecting the Debugger. . . 30
Tutorial: Debugging a Procedure by Setting Breakpoints. . . .31
Tutorial: Debugging a Function by Setting Breakpoints. . . 32
Tutorial: Debugging an Anonymous Block. . . 33
Tutorial: Debugging a SQLScript Library. . . .34
Tutorial: Working With Expressions in the Debugger. . . 34
Tutorial: Working with Variables in the Debugger. . . .35
Tutorial: Working With Watchpoints in the Debugger. . . .36
Tutorial: Quitting the Debugging Session. . . 37
4 Querying the Database. . . .39
4.1 Execute SQL Statements. . . .40
4.2 Execute Parameterized Statements. . . 42
4.3 Run a Query as a Background Activity. . . .43
4.4 Use the Statement Library to Administer Your Database . . . 44
4.5 Work With Graph Workspaces in SAP HANA Database Explorer. . . .46
5 Analyzing Performance. . . . 48
5.1 Configure Tracing in the SAP HANA Database Explorer. . . .48
5.2 View Diagnostic Files in the SAP HANA Database Explorer. . . 49
Viewing the Trace File List. . . .50
View Diagnosis Files of an Unavailable Tenant Database. . . 50
5.3 Traces. . . 51
Database Trace (Basic, User-Specific, and End-to-End). . . .53
SQL Trace. . . .58
Performance Trace. . . 62
Expensive Statements Trace. . . .63
Kernel Profiler. . . .66
Traces and Trace Configuration for Internal Web Dispatcher. . . .68
5.4 Analyzing Statement Performance. . . .70
Analyze Statement Performance. . . .72
5.5 Analyzing SQL and Saving Plans. . . .76
Save Plans as Files. . . .76
Download Files. . . 78
5.6 Analyze Procedure and Function Performance. . . .79
5.7 View Execution Plans for SQL Statements, Procedures, and Anonymous Blocks. . . .80
6 View Diagnostic Files in the SAP HANA Database Explorer. . . .81
6.1 Viewing the Trace File List. . . 82
6.2 View Diagnosis Files of an Unavailable Tenant Database. . . .82
7 Importing, Exporting, and Accessing Data. . . . 84
7.1 Create a Remote Source. . . .84
Edit a Remote Source. . . .86
7.2 Edit a Remote Source. . . 87
7.3 Create Virtual Objects (Smart Data Access). . . 88
7.4 Create Virtual Objects (SAP HANA Smart Data Integration). . . 89
7.5 Import Schemas, Tables, and Other Catalog Objects. . . 90
7.6 Import Data Into a New or Existing Table. . . .91
7.7 Import HDI Containers. . . 93
7.8 Export Schemas, Tables, and Other Catalog Objects . . . .94
7.9 Export HDI Containers. . . 96
8 Important Disclaimer for Features in SAP HANA. . . . 98
1 Getting Started With the SAP HANA Database Explorer
Use the SAP HANA database explorer to query information about the database, as well as view information about your database's catalog objects.
The database explorer is integrated into both the SAP Web IDE for SAP HANA and in the SAP HANA cockpit.
The database explorer contains features and functions required by both database administrators and developers.
What can I do with the SAP HANA database explorer?
The SAP HANA database explorer allows you to query your database and view information about your catalog objects, by providing the following functionality:
A catalog browser View the definitions of all types of catalog objects, for example: tables, views, stored procedures, functions, and synonyms. Also, view the content (data) of your tables and views.
An SQL console Create SQLScript procedures and queries, and then execute them or analyze their performance using the SQL analyzer.
An SQL analyzer View detailed information on your queries and evaluate potential bottlenecks and optimizations for these queries. The SQL analyzer is accessible from the SQL console, as well as from the plan trace and expensive statement features in the SAP HANA cockpit.
An SQL debugger View the call stack, set break points, view and evaluate expressions and variables.
Related Information
SAP Note 2373065
1.1 What's New in SAP HANA Database Explorer
The following features are new or changed for this release of SAP HANA database explorer.
Change a Database
Name (New) You can now change the name that is displayed for your database in the catalog browser tree. Right-click your database and click Properties, then edit the Name to Show in Display field.
Add a Database With a Different User (New)
When adding a cockpit database to the database explorer, you can now choose to enter a User and Password that is different than that provided in the cockpit for that database.
Moreover, once you've added a database to the database explorer, you can go back later and re-add it with different user credentials by right-clicking the database and clicking Add Database with Different User.
See Add a Database to the SAP HANA Database Explorer [page 10]
SQL Console
(Enhancements) ● The SQL Console page of the Global Preferences contains the following new options:
On error Allows you to control the behavior of the SQL console when it encounters a SQL error. You can choose the following actions: Stop, Continue, or Prompt.
Command Separator
If your SQL statements contain a custom command separator, then set the custom separator in the SQL Console section of the Global Preferences so that the parser recognizes your command separator.
Command separators differ from statement delimiters in that they separate commands, which can be either a single-line SQL
statement or a block of SQL statements that are separated by their own statement delimiter.
● A new History tab has been added to the SQL console. This tab shows the 50 most recently executed statements.
For more information, see Querying the Database [page 39].
● Statement Help (New)
Use the new SQL console side panel to access syntax for SQL statements and to view metadata SQL objects. You can view the following information:
Statement/
Syntax
View the syntax of the statement you are currently editing and use the statement link to access full reference documentation for the statement.
Tables and Views
View information on the tables or views being referenced in the current statement and use the title link to open the table or view definition.
Procedures and Functions
View information on procedures or functions being referenced in the current console and use the link to open the procedure or function definition.
SQL Functions View information on built-in functions and use the link to access full reference documentation for the function.
Import Catalog Objects (Enhancement)
If you are including object data when importing a catalog object, you now have the option of selecting Fail import if it contains invalid data. Selecting this checkbox on the Import Catalog Objects dialog ensures that your import is canceled if any of the object data is incomplete or corrupt.
See Import Schemas, Tables, and Other Catalog Objects [page 90].
Table Editor (Enhancement)
The table editor now contains a Runtime Information tab that displays general information and information about memory and disk usage, as well as partitions, and columns.
Database Overview (New)
Right-click a database in your catalog browser and click Show Overview to view general information about your database such as information about Disk Usage,Used Memory, Resident Memory, CPU Usage, system usage type, the most recently started service, and the version.
In SAP Web IDE, the Overview tab is not available for HDI containers.
Column Views (Enhancement)
When searching for column views, you can now select the filter icon to filter your results by Calculation Views, Calculation Views and User-Defined Hierarchies, or All.
Smart Data Integration (Enhancements)
The following enhancements have been made to SDI catalog objects:
Adapters On the Adapters overview page, use the new Reload button to update your selected adapter, or use the new Remove Location button to remove the adapter from an agent instance.
Agents You can now create, edit, and drop agents from the Agents overview page.
You can also add an adapter for your agent by clicking the new Add Adapter button in the Agents overview page when you have an agent selected.
Agent Groups You can now create and drop agents from the Agent Groups overview page.
You can also add an agent to your agent group by clicking the new Add Agent button in the Agent Groups overview page when you have an agent selected.
Remote Subscriptions (Enhancements)
The Remote Subscriptions tab for SDI remote subscriptions now allows to Queue, Distribute, Reset, and Drop the remote
subscription.
Tasks
(Enhancements)
For SDI tasks, you can now execute a task and cancel a running task in the Tasks tab.
Remote Statements
As part of an SDI task execution, you can now see the remote statements that were executed. Click on a task in the catalog browser to view the task's details, then click on either a completed execution or the specific partition of a completed execution and click the Remote Statements button to view remote statements for the task.
You can also choose to stop the task once it has started.
For more information about SDI and the privileges required to work with SDI objects, see the SAP HANA Smart Data Integration and SAP HANA Smart Data Quality guide.
Tracing
(Enhancements) Expensive Statements Trace (Enhancement)
When configuring expensive statements trace, you can now configure the following settings:
● CPU Threshold
● Memory Threshold
● Table or View Filter
● Store Trace Information
● In-Memory Tracing Records
● Trace Flush Interval Viewing Diagnostic
Files
(Enhancement)
To view a diagnostic file in an editor, you now right-click the file and click Show Files in the context menu.
Binary trace files are now identified in the catalog browser by a binary icon and you can no longer open binary trace files from the catalog browser. Binary trace files can only be downloaded.
Trace files have also been enhanced to be more user-friendly by containing the following information in their tooltips:
● the full file name
● the file size
● the last modified time Performance
Tracing (New)
You can now configure performance tracing in the database explorer.
See Performance Trace [page 62] and Configure Tracing in the SAP HANA Database Explorer [page 48], View Diagnostic Files in the SAP HANA Database Explorer [page 49].
1.2 Open Catalog Objects
Browse your database's catalog using the SAP HANA database explorer.
Prerequisites
You must be a user of the database that you want to explore and you must have the required privileges to view the catalog items.
Context
Some monitoring and problem analysis may require you to examine individual tables and views, for example, system views provided by the SAP HANA database. Use the catalog browser, which is located in the left pane, to find and open these catalog objects.
Procedure
1. In the catalog browser, choose the database that you want to explore.
If your database is not listed in the catalog browser, then click Add a database to the Database Explorer () to add the database.
The catalog browser lists the catalog objects, grouped by schema.
2. Choose an object type to view its objects.
For example, choose Tables to list the tables in the database.
3. Choose an object to view its definition in an editor in the right pane, or right-click the object to choose a different action.
For example, right-click a table and choose Open Data to view the table's data.
Related Information
Add a Database to the SAP HANA Database Explorer [page 10]
Add HDI Containers and Databases to the SAP HANA Database Explorer [page 13]
View Database Procedures in the SAP HANA Database Explorer [page 27]
1.3 Create URL Shortcuts to Database Objects in the SAP HANA Database Explorer
Use the SAP HANA database explorer to create a URL shortcut, and then use it to create a bookmark or favorite in your browser.
Procedure
1. In the catalog browser, right-click a database object and choose Create Shortcut.
2. Click Copy URL to copy the shortcut to your clipboard.
3. Paste the URL into your browser.
4. Use your browser to create a bookmark or favorite for the copied URL.
Results
The shortcut is created. Use it to open the database explorer and quickly navigate to the database object.
Related Information
Search for Database Objects in the SAP HANA Database Explorer [page 9]
1.4 Search for Database Objects in the SAP HANA Database Explorer
Search for database objects across all databases that the SAP HANA database explorer is connected to.
Prerequisites
In the database explorer, connect to each database that you want to search.
Context
Use the catalog browser to find your object when you know the type of object and the database that it exists in.
If you are unsure of the object type or the database that it exists in, then use the database object search. This search looks for matches in either a specified database or all of the connected databases in the database explorer and it can search across more than one object type. For example, use this search to find all tables and procedures that contain the word production in their names.
Procedure
From the right sidebar of the database explorer, click Object Search ().
a. Specify a search term that is longer than one character.
Specify * to return all results.
b. Optionally, restrict your search to a specific database.
c. Target your search to specific object types by selecting them at the bottom of the pane.
Results
The search results appear in the Search for Database Objects pane.
Related Information
Create URL Shortcuts to Database Objects in the SAP HANA Database Explorer [page 9]
Open Catalog Objects [page 8]
1.5 Add a Database to the SAP HANA Database Explorer
Add a database to the SAP HANA database explorer so that you can browse its catalog and execute SQL statements against it.
Context
Adding a database to the database explorer is similar to registering a database in the cockpit. Once a database is added, it is listed in the catalog browser pane on the left.
You cannot add a database to the database explorer that uses LDAP authentication.
Procedure
1. Open the database explorer from the SAP HANA cockpit.
2. Add a database by clicking the Add a Database to the Database Explorer icon () at the top of the catalog browser pane on the left.
3. From the Database Type dropdown list, choose the type of database to add:
Database Type Action Add a database
that has been reg
istered in the cock
pit
1. Choose Cockpit Database.
2. Choose a resource from the list.
3. Choose to either use the credentials supplied in the cockpit, or to enter a different user name or password.
An SAP HANA da
tabase
1. Choose SAP HANA database.
2. Specify the fully qualified domain name (FQDN) of the host on which the system is installed.
Specify the instance number or port of the database you are adding.
When adding a database that is part of a multi-host system, specify the master host.
You do not have to enter all host names explicitly as they are determined automatically. If the master host becomes unavailable, then the connection is automatically established through one of the other hosts. Hosts that are added to the system later are also detected automatically.
A tenant or the system database that is part of a multitenant data
base container sys
tem
1. Choose SAP HANA database (Multitenant).
2. Specify the host and instance number or port of the system database.
3. Specify whether you are adding the system database or a tenant database. If you are adding a tenant database, then specify the name of the tenant database.
4. (Optional) If you are adding an SAP HANA database, a tenant database, or a system database that is part of a multi-tenant database container system, then specify encryption information and advanced
connection properties as required.
Choose from the following encryption options:
Option Description
Save user and password (stored in the SAP HANA secure store.)
By default, your database credentials are not saved. Each time you need to connect to an added database, you must provide your user credentials.
Choose this option to save these user credentials so that you do not have to re-enter them each time you connect.
These credentials are saved to the SAP HANA secure store.
Option Description
Connect to the database securely using TLS/SSL.
(Prevents data eavesdropping.)
Choose this option to encrypt communication between the database explorer and the SAP HANA database using the Transport Security Layer (TLS)/Secure Sockets Layer (SSL) protocol.
Verify the server's certificate using the trusted certificate below.
Choose the Verify the server's certificate using the trusted certificate below option and provide a trusted certificate, if you want to verify the server's certificate when connect
ing. This prevents server impersonation. The certificate field must contain the contents of a certificate, and not a file name. For more information, see the SAP HANA Secur
ity Guide.
Note
You can only choose this option if you have also chosen the Connect to the database securely using TLS/SSL. (Prevents data eavesdropping.) option.
Choose from the following advanced options.
In the Advanced Options field, specify the advanced options as semi-colon-separated name=value pairs.
For example: locale=en-US, isolationLevel=READ COMMITTED.
Option Description
isolationLevel The isolation level for the connection. The supported val
ues are: READ COMMITTED, REPEATABLE READ, and SE
RIALIZABLE. The default is to specify no isolation level.
locale The locale to use for the connection. If you do not set this
option, then the database explorer looks for a locale set
ting in your user parameter, and then in your browser. If no locale setting is found, then the locale is set to en-US.
schema The name of the schema that you want to use
CLIENT Sets the session client for the connection. The value is a
three character string. For example, CLIENT=100.
5. (Optional) Choose a display name that is used to identify your database in the database explorer. Each database must have a unique display name. If you do not choose a display name, then one is generated for you.
6. Click OK.
Results
The database is added to the database browser.
Next Steps
After adding a cockpit database, you can always go back and re-add the database with different user credentials. Just right-click the cockpit database and click Add Database with Different User.
Related Information
Securing the SAP HANA Database Explorer [page 22]
Open Catalog Objects [page 8]
Execute SQL Statements [page 40]
1.6 Add HDI Containers and Databases to the SAP HANA Database Explorer
Add a built HDI container to the SAP HANA database explorer in the SAP Web IDE for SAP HANA , so that you can browse its catalog objects and test its procedures and functions.
Prerequisites
The HDI container must be configured on the same XS advanced server that the SAP Web IDE is running on.
To add an SAP HANA database to the database explorer, you must have a user ID and a password for that database.
You must be a space developer of the space that the HDI container is deployed in.
You must be a user of the SAP Web IDE for SAP HANA.
Context
Once an HDI container or database is added to the database explorer, it is listed in the catalog browser pane on the left.
You cannot add a database to the database explorer that uses LDAP authentication.
Procedure
1. Open the database explorer from the SAP Web IDE by choosing Tools Database Explorer .
2. Add a database by clicking the Add a database to the Database Explorer icon () from the catalog browser toolbar.
3. From the Database Type dropdown list, choose the type of database to add:
Database Type Action
An HDI container 1. Choose HDI container.
2. Choose an HDI container from the list. The list contains all HDI containers used by the SAP Web IDE and the database explorer as well as all user-defined HDI containers.
3. Set the XS_APPLICATIONUSER session variable to make the connection personalized for the current user.
An SAP HANA data
base
1. Choose SAP HANA database.
2. Specify the fully qualified domain name (FQDN) of the host on which the system is instal
led. Specify the instance number or port of the database you are adding.
When adding a database that is part of a multi-host system, specify the master host.
You do not have to enter all host names explicitly as they are determined automatically. If the master host becomes unavailable, then the connection is automatically established through one of the other hosts. Hosts that are added to the system later are also detected automatically.
A tenant or the sys
tem database that is part of a multitenant database container system
1. Choose SAP HANA database (Multitenant).
2. Specify the host and instance number or port of the system database.
3. Specify whether you are adding the system database or a tenant database. When adding a tenant database, specify the name of the tenant database.
HDI containers con
tained in instance managers
1. Choose Application Managed Service Instances.
2. Specify the instance manager that contains the HDI container you want to add.
3. Choose an HDI container from the list. The list contains all HDI containers used by the SAP Web IDE and the database explorer as well as all user-defined HDI containers.
4. Set the XS_APPLICATIONUSER session variable to make the connection personalized for the current user.
4. (Optional) Specify advanced connection properties as required.
Choose from the following encryption options:
Option Description
Save user and password (stored in the SAP HANA secure store)
By default, your database credentials are not saved. Each time you need to connect to an added database, you must provide your user credentials.
Choose this option to save these user credentials so that you do not have to re-enter them each time you connect.
These credentials are saved to the SAP HANA secure store.
Connect to the database securely using TLS/SSL.
(Prevents data eavesdropping.)
Choose this option to encrypt communication between the database explorer and the SAP HANA database using the Transport Security Layer (TLS)/Secure Sockets Layer (SSL) protocol.
Verify the server's certificate using the trusted certificate below.
Choose the Verify the server's certificate using the trusted certificate below option and provide a trusted certificate, if you want to verify the server's certificate when connect
ing. This prevents server impersonation. The certificate field must contain the contents of a certificate, and not a file name. For more information, see the SAP HANA Secur
ity Guide.
Note
You can only choose this option if you have also chosen the Connect to the database securely using TLS/SSL. (Prevents data eavesdropping.) option.
In the Advanced Options field, specify the advanced options as a semi-colon-separated, option- name=value pair. For example: locale=en-US, isolationLevel=READ COMMITTED.
Option Description
isolationLevel The isolation level for the connection. The supported val
ues are: READ COMMITTED, REPEATABLE READ, and SE
RIALIZABLE. The default is to specify no isolation level.
locale The locale to use for the connection. If you do not set this
option, then the database explorer looks for a locale set
ting in your user parameter, and then in your browser. If no locale setting is found, then the locale is set to en-US.
schema The name of the schema that you want to use
CLIENT Sets the session client for the connection. The value is a
three character string. For example, CLIENT=100.
5. Click OK.
Results
The HDI container or database is added to the database browser.
By default, you are connected to the HDI container as the technical user.
Related Information
Open Catalog Objects [page 8]
Execute SQL Statements [page 40]
2 Security in the SAP HANA Database Explorer
The database explorer supplies secure authentication, authorization, and connections and provides a simple method to delete personal information as needed.
2.1 Data Protection in SAP HANA Database Explorer
Data protection is associated with numerous legal requirements and privacy concerns. In addition to
compliance with general data privacy regulation, it is necessary to consider compliance with industry-specific legislation in different countries. SAP provides specific features and functions to support compliance with regards to relevant legal requirements, including data protection. SAP does not give any advice on whether these features and functions are the best method to support company, industry, regional, or country-specific requirements. Furthermore, this information does not give any advice or recommendation in regards to additional features that would be required in particular IT environments; decisions related to data protection must be made on a case-by-case basis, under consideration of the given system landscape and the applicable legal requirements.
Note
In the majority of cases, compliance with applicable data protection and privacy laws will not be covered by a product feature. SAP software supports data protection compliance by providing security features and specific data protection-relevant functions, such as simplified blocking and deletion of personal data. SAP does not provide legal advice in any form. Definitions and other terms used in this document are not taken from any given legal source.
Glossary
Term Definition
Consent The action of the data subject confirming that the usage of his or her personal data shall be allowed for a given purpose. A consent functionality allows the stor
age of a consent record in relation to a specific purpose and shows if a data sub
ject has granted, withdrawn, or denied consent.
Deletion The irreversible destruction of personal data.
Term Definition
Personal data Any information relating to an identified or identifiable natural person ("data sub
ject"). An identifiable natural person is one who can be identified, directly or indi
rectly, in particular by reference to an identifier such as a name, an identification number, location data, an online identifier or to one or more factors specific to the physical, physiological, genetic, mental, economic, cultural, or social identity of that natural person.
Purpose A legal, contractual, or in other form justified reason for the processing of per
sonal data. The assumption is that any purpose has an end that is usually already defined when the purpose starts.
Collection of Personal Data
SAP HANA database explorer stores only personal data entered by users; it never collects personal data without a user's knowledge. The only personal information stored by the SAP HANA database explorer consists of the following:
XSA user
ID The XSA user ID is received from the XSA or Cloud Foundry login page and is recorded in both the SAP HANA database explorer and in log files. The following information associated with the user ID is also stored in the SAP HANA database explorer:
User-Defined SQL Statements User-defined SQL statements are associated with a specific user and may contain personal data.
Long-Running SQL Queries These queries are associated with a specific user and may contain personal data.
User ID for Cloud Foundry Login When Connecting to the Database Explorer from WebIDE
This ID is retained only until WebIDE is restarted or upgraded.
Database Credentials User credentials associated with a specific user for HANA databases are stored in the SAP HANA database explorer.
User Preferences These preferences are associated with a specific user and are stored in the SAP HANA database explorer.
Logging SAP HANA database explorer collects XSA logs (when running on premise), Cloud Foundry logs (when running on SCP), and audit logs. Audit logs can track read access and changes and may contain user-specific data.XSA logs are retained indefinitely by XSA. For more information about XSA logging, see the SAP HANA Developer Guide for XS Advanced Model. Cloud Foundry logs are retained for one week. Audit logs cannot be deleted.
By default, logging is set to an application-wide level that does not record any personal data.
However, you can set logging to session level, which collects all SQL statements for the current session. These statements may contain personal data.
2.1.1 Delete Personal Data
Delete user-specific information that is stored by the SAP HANA database explorer.
Context
This procedure deletes the following personal data that is associated with the specified user ID:
● All database credentials
● All user-defined SQL statements
● All user preferences
● All background tasks
● All content in open SQL consoles
Procedure
1. Using the xs command-line utility to log in as a user with the SpaceDeveloper role to the organization and space where the database explorer is installed.
For example, in SAP HANA Express, the command looks similar to the following:
xs login -a https://hxehost:39030 -o HANAExpress -s SAP -u XSA_ADMIN
2. Run the xs env hrtt-service command to find the connection parameters for the hrtt-datasource.
Search the command results for the credentials with path VCAP_SERVICES.hana[0].
The results are similar to the following:
{
"name": "hrtt-datasource", "label": "hana",
"tags": [ "hana", "database", "relational"
],
"plan": "securestore", "credentials": {
"schema": "USR_BTVXBP585K74Z43VYJBW8WWZU", "password": "password",
"driver": "com.sap.db.jdbc.Driver", "port": "port-number",
"host": "hxehost", "db_hosts": [ {
"port": 39013, "host": "hxehost"
} ],
"user": "USR_BTVXBP585K74Z43VYJBW8WWZU", "url": "jdbc:sap://hxehost:39013/?
currentschema=USR_BTVXBP585K74Z43VYJBW8WWZU"
}
}
3. Open the database explorer from the SAP HANA cockpit or SAP Web IDE for HANA and add a database by using the values from the hrtt-datasource entry above.
a. Click the Add a Database to the Database Explorer icon () and choose either SAP HANA Database (Multitenant) or SAP HANA Database (if you have a single database container).
b. Determine the instance number from the hrtt-service environment port field value. If the port number is 3<xx>13, then <xx> is the instance number.
c. Enter the user name and the password from the hrtt-service environment.
d. After connecting to your database, navigate to the schema that has the same name as the user. This is the schema where HRTT stores persistent user data.
4. Open a SQL console and execute the following SQL batch to delete all data for the user, substituting
<user-ID> with the actual user ID:
DO ( IN user_id VARCHAR(56) => '<user-ID>' ) BEGIN
DECLARE c INT;
DECLARE v_sql VARCHAR(1000);
SELECT COUNT(*) INTO c FROM SYS.TABLES WHERE schema_name=current_schema AND ( table_name='ACTIVITY_RESULTS' or table_name='ACTIVITIES_' );
IF c = 2 THEN
v_sql = 'DELETE FROM ACTIVITY_RESULTS AR WHERE AR.ACTIVITY_ID IN ( SELECT ACTIVITY_ID FROM ACTIVITIES_ A
WHERE A.USER_ID = ''' || :<user_ID> || ''' )';
exec :v_sql;
END IF;
SELECT COUNT(*) INTO c FROM SYS.TABLES WHERE schema_name=current_schema AND table_name='ACTIVITIES_';
IF c = 1 THEN
v_sql = 'DELETE FROM ACTIVITIES_ WHERE USER_ID = ''' || :<user_ID> ||
'''';
exec :v_sql;
END IF;
SELECT COUNT(*) INTO c FROM SYS.TABLES WHERE schema_name=current_schema AND table_name='STATEMENTS';
IF c = 1 THEN
v_sql = 'DELETE FROM STATEMENTS WHERE OWNER = ''' || :<user_ID> || '''';
exec :v_sql;
END IF;
SELECT COUNT(*) INTO c FROM SYS.TABLES WHERE schema_name=current_schema AND table_name='USER_DATABASE_LOCKS';
IF c = 1 THEN
v_sql = 'DELETE FROM USER_DATABASE_LOCKS WHERE USERID = '''
|| :<user_ID> || '''';
exec :v_sql;
END IF;
SELECT COUNT(*) INTO c FROM SYS.TABLES WHERE schema_name=current_schema AND table_name='USER_DATABASES';
IF c = 1 THEN
v_sql = 'DELETE FROM USER_DATABASES WHERE USERID = ''' || :<user_ID> ||
'''';
exec :v_sql;
END IF;
SELECT COUNT(*) INTO c FROM SYS.TABLES WHERE schema_name=current_schema AND table_name='USER_PREFERENCES';
IF c = 1 THEN
v_sql = 'DELETE FROM USER_PREFERENCES WHERE USERID = ''' || :<user_ID>
|| '''';
exec :v_sql;
END IF;
SELECT COUNT(*) INTO c FROM SYS.TABLES WHERE schema_name=current_schema AND table_name='XRAY_STORIES';
IF c = 1 THEN
v_sql = 'DELETE FROM XRAY_STORIES WHERE CREATED_BY = ''' || :<user_ID>
|| '''';
exec :v_sql;
END IF;
COMMIT;
END;
Results
The data for the specified user is removed from the tables in hrtt-datasource.
2.1.2 Delete Your Personal Data
Delete your personal information stored by the SAP HANA database explorer.
Context
This procedure deletes the following personal data associated with the connected user ID:
● All database credentials
● All user-defined SQL statements
● All user preferences
● All background tasks
● All content in open SQL consoles
Procedure
1. Click the Preferences icon () and click Database Explorer.
2. Click Remove all user data then click Yes.
Results
Your user data is deleted and SAP HANA database explorer restarts.
2.2 Securing the SAP HANA Database Explorer
Security considerations for SAP HANA database explorer include authentication, authorization, and secured connections.
Authentication and
authorization Authentication and authorization for the database explorer is governed by the application in which it resides.
The database explorer is integrated into SAP HANA cockpit and SAP Web IDE for SAP HANA. To use the database explorer you must be a non-administrator user of one of these applications.
Access to registered cockpit databases
As a cockpit user, you can access any HDI container that exists in a space in which you have been assigned the SpaceDeveloper role.
As a cockpit user, you can access any cockpit that is assigned to a group of which you are a member.
Access to HDI containers in SAP Web IDE
As an SAP Web IDE user, you can access any HDI container that is configured to run on the same XS advanced server that SAP Web IDE runs on, and that exists in a space in which you have been assigned the SpaceDeveloper role.
By default, an HDI container user is assigned a few basic database privileges. For example, the object owner (“#OO” user) is only assigned the CREATE ANY privilege on the container's run-time schema (schema “TEST” for an HDI container “TEST”). To access database objects inside other database schemata or other HDI containers, and to be able to deploy synonyms into the HDI container that point to objects outside the container, or to be able to import catalog objects into an HDI container, you must grant the object owner additional privileges.
For more information see the SAP HANA Developer Guide (For SAP HANA XS Advanced Model).
Connections from the browser to the database explorer XS advanced server
As a user of the cockpit, you can add any registered database to the database explorer.
When the database browser sends a connection request to the XS advanced server that is associated with the database explorer, the browser verifies that the host name of the responding XS advanced server matches the host name that it provides. Specifically the browser includes the Origin header, which contains the host name of the XS advanced server in the request. If the host name of the responding XS advanced server does not match the host name in the Origin header, then the connection does not proceed. You can provide the XS advanced server with a list of host names to use during this verification by setting the WEBSOCKET_ORIGIN environment variable (using xs set - env) for the XS advanced server.
TLS/SSL
connections from the database
Specify the root certificate when you add a database that supports TLS/SSL connections with certificates to the database explorer. SAP HANA databases that accept TLS/SSL connections usually use certificates that are obtained from a
explorer to
databases Certification Authority (CA), but you can use your own signed certificates. To create a signed certificate, perform the following steps:
1. Create a self-signed root certificate.
2. Create a second certificate that is signed by the root certificate 3. Provide the second, signed certificate to the database server.
4. Provide the root certificate to your clients (such as the database explorer).
Storing database
user credentials When you specify the credentials to connect to an SAP HANA database, those credentials are not saved to your browser. To have the credentials persist between sessions, save the credentials to the SAP HANA secure store.
Saving SQL console information to your browser's local storage
If your browser supports saving content to local storage, then, for the duration of your session, the SQL content in your SQL console is saved to your browser. (Configure this behavior in your user preferences within the SAP Web IDE.)
Exporting and importing catalog objects
When you export catalog objects, the content that is saved is not encrypted.
When you import catalog objects into a database, no automatic virus scan or content validation of the files is performed before they are imported. Malicious content can be imported. Use external tools to validate the content of the files and scan them for viruses before importing them.
Related Information
Getting Started With the SAP HANA Database Explorer [page 4]
Secure User Store (hdbuserstore) Permissions for Container Objects Known Security-Related Issues
2.2.1 Secure the SAP HANA Database Explorer from Web Socket Attacks
When the host name in the URL for the SAP Web IDE for SAP HANA or SAP HANA cockpit does not match the hostname of its XS advanced server, then you must provide the XS advanced server with the accepted host names to use when the XS advanced server verifies connections to the SAP HANA database explorer.
Prerequisites
You must have one of the following roles:
● OrgManager
● SpaceManager
● SpaceDeveloper
Procedure
1. Connect to the XS command line interface.
For example:
xs login -a https://myhostname.mycorporatesite.com:30030 -u XSA_ADMIN -p mypassword
2. Set the WEBSOCKET_ORIGIN environment variable for the XS advanced server to the list of acceptable host names. Use the set-env command and enclose the acceptable host names between square brackets.
On a Unix system, the value must be enclosed in single quotes. For example:
xs set-env hrtt-service WEBSOCKET_ORIGIN '["myhost1", "myhost2"]' On Windows, the double quote characters must be escaped. For example:
xs set-env hrtt-service WEBSOCKET_ORIGIN [\"myhost1\",\"myhost2\"]
3. Execute the following commands to restage the hrtt-service.
xs restage hrtt-service
4. Execute the following commands to restart hrtt-service.
xs restart hrtt-service
3 Analyzing and Diagnosing Errors
The SAP HANA database explorer allows you to configure different types of traces and view diagnostic files for your database. You can also use the SQL debugger to ensure that your database queries are error-free.
3.1 Set Session Logging
Log all back-end service requests for the specific user session.
Context
Set user-specific session logging when you require debug traces for individual requests, for example to provide to your cloud administrator for support purposes, but you do not want to expose sensitive data from other users.
Session-based logging records all SQL statements executed during the database explorer session as well as their result sets. These SQL statements may contain personal data.
Procedure
1. Click the Preferences icon () and click Database Explorer.
2. Click Debug Logging, confirm the logging changes, then click Save.
Results
Debug logging is now active only for the connected user for the duration of the SAP HANA database explorer session.
3.2 View SQLScript Code Coverage Report
Retrieve code coverage information for SQLScript procedures, functions, and anonymous blocks.
Prerequisites
To see a database object in the coverage report, you must have the DEBUG object privilege for that object.
You must have a minimum database version of HANA 2.0.030.
Context
Information is provided for code executed within one user-provided SQL session.
Procedure
1. Enter your procedure, function, or anonymous block into the SQL console.
2. Click ANALYZE Report Code Coverage .
Results
The code is executed and a coverage report is generated and is displayed under the Code Coverage Report tab.
The Overview table of the code coverage report contains one row per procedure/function/anonymous block involved in the code execution.
3.3 View Database Procedures in the SAP HANA Database Explorer
Use the SAP HANA database explorer to view and test a database procedure.
Prerequisites
● You have access to a running SAP HANA system that includes a database procedure.
Procedure
Find your procedure in your catalog browser, and then choose one of the following options:
Option Action
View information about the procedure's parameters Click the procedure to open.
View the CREATE statement associated with the proce
dure
Open the procedure, and then click the CREATE Statement tab.
Generate a CALL statement for the procedure Open the procedure, and then click Generate CALL Statement.
Open the procedure to debug it (SAP Web IDE only) Right-click the procedure, and then choose Open for debugging.
Related Information
Create a Simple "Tiny-World" Application
3.3.1 Debug Procedures in the SAP HANA Database Explorer
Debug your stored procedures by using the SQL debugger in the SAP HANA database explorer.
Prerequisites
You must have the DEBUG object privilege granted to the schema or the procedures, that you want to debug.
To allow a user to debug a procedure in another user's session, the secondary user must be granted the ATTACH DEBUGGER privilege. For example, user A can grant the ATTACH DEBUGGER privilege (using the
GRANT statement) to user B to allow user B to debug a procedure in user A’s session. User B also needs the DEBUG privilege on the procedure.
Context
In the SAP Web IDE, use the development perspective to create stored procedures in .hdbprocedure modules and build them into HDI containers. Switch to the database explorer to test and debug the built procedures. To make corrections to the stored procedures, return to the development perspective.
Using the SQL debugger with user-provided services or application-managed service instances is not supported.
Note
If you are debugging a procedure and subsequently make changes to the procedure, then you must restart the debugging session by detaching and re-attaching the debugger. Each debug session works with a snapshot of the procedure(s) and any subsequent changes to procedures are not visible in active debug sessions.
Procedure
1. Choose one of the following options to open the procedure for debugging:
Option Action
From the development perspective: 1. Open the stored procedure (the .hdbprocedure file.)
2. Right-click in the editor and choose Open Runtime Object.
The database explorer opens, the HDI container ap
pears in the catalog browser, and the metadata viewer for the procedure opens.
3. Right-click the procedure in the catalog browser item list and click Open for debugging.
From the database explorer: 1. Connect to the database.
2. Locate the stored procedure in the database browser, and then right-click and click Open for debugging.
The procedure opens in a code editor, the debugger opens on the right sidebar and connects to the database, and a debugger session starts.
2. Set breakpoints in the code of your stored procedure by clicking a line number.
3. Call the procedure by right-clicking the procedure in the catalog browser item list and clicking Generate CALL statement.
a. In the code, enter values for any input parameters.
b. Choose Run () from the global toolbar.
The SQL console opens and it contains the CALL statement for your procedure.
The debugger stops at the first breakpoint and the session is suspended until you resume the debugging.
4. Choose (Resume module execution (F8)) or (Step over next function call (F10)) to step through the code execution.
Next Steps
Once you are finished debugging, click Detach debugger from running application () to stop the debugging session.
3.4 SAP HANA Database Explorer Debugger Tutorials
Use the database explorer debugger to debug stored procedures and functions.
Use the tutorials in this section to help you understand the following tasks you can perform with the database explorer debugger:
● Attaching and detaching the debugger from your database
● Using breakpoints to debug procedures and functions
● Working with debugger variables and expressions
● Using the step over feature to debug your code step by step
● Working with watchpoints
Tutorial Requirements
Execute the following statements (using the same line numbering as below) to create the function func_add, and the procedure dp2, which are used extensively in the debugger tutorials:
CREATE FUNCTION func_add(x DOUBLE, y DOUBLE) RETURNS result_add DOUBLE LANGUAGE
SQLSCRIPT READS SQL DATA AS BEGIN
result_add :=x + y;
END;
CREATE PROCEDURE "dp2" () LANGUAGE SQLSCRIPT SQL SECURITY INVOKER
--DEFAULT SCHEMA <default_schema_name>
READS SQL DATA AS BEGIN
DECLARE lt1 TABLE(a INT);
DECLARE category VARCHAR(20) ARRAY;
DECLARE I INT:= 0;
lt1.a[1] = 3;
lt1.a[2] = 4;
lt1.a[3] = 2;
category [1]:= 'CategoryA';
category [2]:= 'CategoryB';
category [3]:= 'CategoryC';
i=FUNC_ADD(2,4);
END;
3.4.1 Tutorial: Connecting the Debugger
Attach the database explorer debugger to your database.
Context
Start an active debugging session for a specific database and specific types of connections.
Procedure
1. Click the Debugger icon () on the right-hand tool bar to open the debugger.
2. Click the Attach debugger icon () and click the database you want to attach the debugger to.
3. Click OK.
4. In the Debugger Attach Options dialog, choose one of the following options for how the debugger is attached:
SQL console connections Debug executions of a procedure from a SQL console Connections with the
following debug token
Debug connections that use the specified debug token. Click Choose a Debug Token... opens a dialog that shows current tokens in use by the current database.
Connections with the following HANA
connection IDs (comma- separated):
Debug connections with the given connection IDs are debugged. Click Choose a Debug Token... opens a dialog that shows connection IDs for connections to the HANA database.
Connections with the following attributes
Debug connections with the specified HANA database user name and, optionally, the specified application user name. Use the Application user name option primarily for ABAP applications, as ABAP connections use the same HANA database user name, but a different application user name.
Results
A message appears on the top right corner of the screen indicating that the debugger has successfully connected and the Active Session dropdown menu shows the name of the connected database.
3.4.2 Tutorial: Debugging a Procedure by Setting Breakpoints
Create a sample procedure and then set breakpoints where you want the debugging to pause during the execution of your code.
Prerequisites
You must have created the function func_add, and the procedure dp2, as specified in the SAP HANA Database Explorer Debugger Tutorials topic.
Context
To debug a procedure, HANA must first compile it. Breakpoints that are set before the procedure is compiled do not have a checkmark. Once the procedure is compiled, valid breakpoints show checkmarks. Setting a breakpoint after procedure compilation automatically moves the breakpoint to the next valid line, if it is not currently on a valid line.
Procedure
1. Right-click the dp2 file and click Open for Debugging.
2. Create a breakpoint on a specific line by clicking next to the line number.
A blue background appears at the line number, along with a checkmark. The checkmark indicates that the breakpoint has been validated by HANA as a valid line number for the breakpoint to exist on.
3. Right-click on dp2 again and click Generate CALL Statement.
4. Click Run to execute the procedure.
The procedure starts to run, but stops at the breakpoint, with a message in the top right corner of the screen indicating that the procedure has been suspended. The Console section of the debugger displays the message, Stopped at breakpoint.
5. To resume the execution of the procedure, click Resume (), or press F8, in the debugger window.
Results
The procedure has successfully executed, pausing at the set breakpoints.
Related Information
SAP HANA Database Explorer Debugger Tutorials [page 29]
3.4.3 Tutorial: Debugging a Function by Setting Breakpoints
Create a sample function and then set breakpoints where you want the debugging to pause during the execution of your code.
Prerequisites
You must have created the function func_add, and the procedure dp2, as specified in the SAP HANA Database Explorer Debugger Tutorials topic.
Context
You cannot set breakpoints and debug a function directly. Instead, the function must be called through a procedure or a table function.
Procedure
1. Right-click the func_add function and click Open for debugging.
2. Click on a line in the function to create a breakpoint.
A dialog pops up that says that debugging scalar UDFs is only supported if the UDF is assigned to a variable within a procedure or a table function that also has a breakpoint set.
3. Click OK in the dialog box and call the procedure, dp2, which calls func_add, by right-clicking the procedure and clicking Generate CALL Statement Run .
Results
When dp2 is called, you can see that func_add breaks on the set breakpoints, since dp2 calls the function at line 17.
Related Information
SAP HANA Database Explorer Debugger Tutorials [page 29]
3.4.4 Tutorial: Debugging an Anonymous Block
Create a function that contains an anonymous block and then set breakpoints where you want the debugging to pause during the execution of your code.
Prerequisites
To set breakpoints in an anonymous block, set the Stop at execution start option in the Debugger Attach Options dialog when you attach the debugger to your database.
Context
You cannot set breakpoints and debug a function directly. Instead, the function must be called through a procedure or a table function.
When debugging anonymous blocks, breakpoints and watchpoints are not persisted between executions.
Procedure
Call the procedure dp2, which contains an anonymous block, by right-clicking the dp2 procedure in the Procedures item list and clicking Generate CALL Statement.
Results
When you execute the SQL, a second, read-only editor tab opens and execution is paused at the start of the SQL. You can step through the code, set breakpoints, and debug just as you can when debugging procedures and functions.
3.4.5 Tutorial: Debugging a SQLScript Library
Debug a SQLScript Library in the SAP HANA database explorer.
Prerequisites
You must be running a HANA database of version 2.0 SPS 04 or later.
To set a watchpoint in the SQLScript library, you must first set the SQLScript library member in the Add Watchpoint dialog.
Procedure
Right-click the SQL library that you want to debug and click Open for Debugging.
Results
The library opens in the debugging editor where you can set breakpoints.
3.4.6 Tutorial: Working With Expressions in the Debugger
Select one or more expressions to evaluate during the debugging session.
Prerequisites
You must have created the function func_add, and the procedure dp2, as specified in the SAP HANA Database Explorer Debugger Tutorials topic.
Context
The Expressions tab in the debugger allows you to watch expressions as executions are paused. As you step through the code, the debuggers watches the expression and returns any results.
Procedure
1. Right-click the dp2 file and click Open for Debugging.
2. Click the Expressions tab and then click Add Watch Expression ().
3. Choose SQLScript in the Module type drop-down menu.
4. In the Expression Text field, type the expression i + 5.
The debugger watches the expression during the debug session and will return all values for i plus 5.
5. Click Add.
6. (Optional) To edit an expression, select the expression and click Edit ().
7. Set breakpoints on lines 13 and 18 in the dp2 procedure and run the procedure by right-clicking dp2 and clicking Generate CALL Statement Run .
The procedure stops at line 13, and in the Expressions tab, the value of i + 5 is displayed as 5, since i was declared as 0 on line 9.
8. Click Resume () to continue execute the procedure.
The procedure now stops at line 18, and in the Expressions tab, the value of i + 5 has changed from 5 to 11. This is because on line 17, the value of i became 6, when the code i=FUNC_ADD(2,4); was executed.
Results
You have run a debugging session on procedure dp2, where the expression i + 5 was watched and returned varying results, depending on the breakpoint set.
3.4.7 Tutorial: Working with Variables in the Debugger
View variable values within the procedure or function that you are debugging.
Prerequisites
You must have created the procedure dp2, as specified in the SAP HANA Database Explorer Debugger Tutorials topic.
Context
The Variables tab in the debugger allows you to view variables and their definitions as executions are paused during the debug session.
Procedure
1. Right-click the dp2 file and click Open for Debugging.
2. Set a breakpoint at line 14.
3. Right-click the procedure and click Generate CALL Statement Run to run the procedure.
The procedure pauses on line 14.
4. Click Variables and click Local to view the different variables in your procedure along with their values.
5. Click the LT1: table[3] variable and then click Display Content () to view the contents of the selected variable.
6. If the variable can be changed, then the Edit icon () is enabled and you can click the icon to change value.
The following global and local variable types can be changed:
○ BINARY, VARBINARY
○ BOOLEAN
○ STRING
○ CHAR, NCHAR, VARCHAR, NVARCHAR, VARCHAR3, VARCHAR2
○ DATE, TIME, TIMESTAMP, DAYDATE, SECONDTIME, LONGDATE, SECONDDATE
○ INTEGER, INT, TINYINT, SMALLINT, BIGINT
○ DECIMAL, SMALLDECIMAL
○ REAL, DOUBLE, FLOAT
Results
You have run a debugging session on procedure dp2, where you examined the definition for the variable LT1:
table[3], when the procedure paused execution at line 14.
3.4.8 Tutorial: Working With Watchpoints in the Debugger
Setting a watchpoint in the debugger to pause code execution when a specified condition is encountered.
Prerequisites
You must have created the function func_add, and the procedure dp2, as specified in the SAP HANA Database Explorer Debugger Tutorials topic.
Context
A watchpoint acts like a breakpoint, but rather than specifying a line at which to pause code execution, you specify a condition at which to pause code execution. For example, you can pause code execution when the a specified expression is true or when the result of a specified variable changes values.
Procedure
1. Right-click the dp2 file and click Open for Debugging.
2. Click Add Watchpoint () to add a watchpoint.
3. In the Add Watchpoint dialog, choose the condition When expression is true from the Watchpoint condition dropdown menu.
4. Specify i=6 in the Expression after field.
Notice that the watchpoint is added to the Breakpoints view.
5. Run the dp2 procedure by clicking Generate CALL Statement Run .
The procedure stops at line 18 with a message in the Console box stating that the procedure execution stopped due to the specified watchpoint.
6. (Optional) Edit the expression in the watchpoint.
a. Click the watchpoint and click Edit Watchpoint ().
b. Change the value in Expression after to i<6.
7. Re-run procedure dp2.
The code now pauses execution at line 11, since that is the first instance where the value of the variable i is less than 6.
Results
You have successfully created and edited a watchpoint in the debugger.
3.4.9 Tutorial: Quitting the Debugging Session
Explicitly quit your debugging session by detaching the debugger from your application, or by setting a debugger timeout limit.
Context
Procedure
1. Quit your debugging session.
a. Click Detach Debugger from Running Application ()
A message appears on the top right of the screen, indicating that the debugger is detatched.
2. Set the timeout limit for the debugger.
a. Click Preferences ().
b. Click the SQLScript Debugger option.
c. Enter the number of minutes you want to set the debugger session timeout limit to and click Save.
Results
You have quit your current debug session and set a timeout (in minutes) preference for all debugging sessions.
4 Querying the Database
Execute queries on your database using the SQL console. These queries can be run as background activities.
The database explorer SQL console includes the following functionality:
● Executes batches of statements, separated by semicolons.
● Includes a code-completion feature, as well as a code-formatting feature. Right-click within the SQL console to run these features.
● Includes the SQL analyzer to help you analyze the performance of your queries.
To view keyboard shortcuts, right-click anywhere in the SQL console and then click Keyboard Shortcuts from the context menu.
SQL Console Preferences
Set preferences for your SQL consoles by navigating to Global Preferences SQL Console . You can set the following preferences:
● The theme and font size for your console editor
● The behavior of results sets, such as:
○ LOB byte limit
○ Display format of NULL values
○ The maximum numbers of rows to display
○ The maximum number of open results
○ Whether to search for a custom command separator when parsing
○ Whether to continue, stop, or provide a prompt when a SQL error is encountered
○ Whether SQL statements should be prepared before executing
○ Whether potential SQL errors should be indicated
○ Whether or not text should be parsed into multiple statements before executing
○ Whether to close results before executing another SQL statement
● Auto-saving the contents of your console to your local storage for the duration of your session (if your browser supports saving content)
● Auto-committing new SQL console contents (by default DML contents are auto-committed)
Statement Syntax Help
Use the SQL console side panel to access syntax for SQL statements and to view metadata SQL objects. You can view the following information:
Statement/Syntax View the syntax of the statement you are currently editing and use the statement link to access full reference documentation for the statement.
Tables and Views View information on the tables or views being referenced in the current console and use the title link to open the table or view definition.
Procedures and Functions
View information on procedures or functions being referenced in the current console and use the link to open the procedure or function definition.
SQL Functions View information on built-in functions and use the link to access full reference documentation for the function.
Statement History
View the 50 most recently executed SQL statements in the History tab, located beside the Results and Messages. Double-click a statement in the History tab to load it into the text editor. Closing the SQL console erases the current statement history.
4.1 Execute SQL Statements
Execute SQL statements and analyze their results by using the SQL console that is included with the SAP HANA database explorer.
Prerequisites
You must have the required privileges in the SAP HANA database to execute your SQL statements.
Context
Be default, DML contents are autocommited and syntax errors are indicated.
To view keyboard shortcuts, right-click anywhere in the SQL console and then click Keyboard Shortcuts from the context menu.
Procedure
1. Open an SQL console from the database explorer by right-clicking your database and clicking Open SQL Console.
2. Specify a SQL statement.
For example, the following statement returns users who have the EXPORT or IMPORT system privilege:
SELECT * FROM EFFECTIVE_PRIVILEGE_GRANTEES WHERE (OBJECT_TYPE = 'SYSTEMPRIVILEGE')
AND (PRIVILEGE = 'EXPORT' OR PRIVILEGE='IMPORT');
You can also import SQL directly into the console by clicking the Import File icon ( ) and choosing a local file.
3. Execute on or more statements by choosing one of the following options:
Option Action
Execute all statements Click the Run icon () from the global toolbar or press F8.
Execute individual statements Highlight the statement, and then click the Run icon () from the global toolbar or press F8.
Execute a SQL query on multiple databases Open a SQL console, create your query and then click Run Run on Multiple Databases . Click the data
bases, groups, or resources where you want to run the query. The query runs as a background activity. Option
ally, click Cancel all queries on SQL error, to stop subse
quent queries from running on all databases if one of your queries encounters a SQL error. Click Run SQL.
Execute a query and open the SQL analyzer to view in
formation about the query's plan
Open the Run dropdown list from the global toolbar, and then choose Analyze SQL.
A new tab opens to display the query plan for your state
ment in the SQL analyzer.
To save the plan, click Analyze SQL and Save Plan and specify a filename for the plan. You can also specify whether to run the analysis as a background activity.
Execute the content of the current line Open the Run dropdown list from the global toolbar, and then choose Run Line.
Prompt for the values of parameters before executing the statement
Open the Run dropdown list from the global toolbar, and then choose Prepare Statement.
Results
The Result pane appears with the results. Multiple Result tabs appear when there is more than one result set.
By default, only the first 1000 rows in a result set are retrieved.
Related Information
Introduction to SQL SAP HANA SQL Reference About SAP HANA SQLScript
Analyzing Statement Performance [page 70]
Setting User Preferences
Run a Query as a Background Activity [page 43]
Use the Statement Library to Administer Your Database [page 44]
4.2 Execute Parameterized Statements
Create and execute parameterized statements in the SQL console.
Context
Parameterized statements provide more flexibility to your application when the name of an object, or the value for a statement option, is not known until execution time.
Procedure
1. Enter your statement in the SQL console and click Run.
For example:
SELECT * FROM SYS.TABLES WHERE SCHEMA_NAME=?;
A Parameters tab appears below the code.
2. Provide the parameter value by either entering it in the field provided or uploading it from a CSV file.
3. Click the Run button on the Parameters tab to execute the statement with the given parameter values.
4. (Optional) To prepare the parameterized statement again, click the Run button on the SQL console. Any parameter values that were entered in the Parameters tab are discarded.
Results
The statement executes with the given parameters and the results are returned on the Result tab.
