When you administer your database with the SAP HANA database explorer, you probably find yourself relying on the same set of SQL statements over and over. So why not have them stored somewhere handy, rather than having to re-type them in the SQL console for every execution?
As of the on-premise version 2.0 SP04 and the cloud version released on November 23, 2017, the database explorer now contains a statement library.
We get it; the HANA studio also had a statement library. So are there any differences between the two? And does the database explorer offer additional functionality? The answer to both questions is YES.
Take a look below to see what the database explorer’s statement library has to offer…
Store User-Defined Statements
Similar to the functionality of the HANA studio statement library, the database explorer statement library can also be used to store frequently-used user-defined statements. Simply create the statement in the SQL console and then click the Add to Statement Library button. Choose the name of the statement and save it to the statement library.
Access System-Defined Statements
Unlike HANA studio, where many system-defined statements had to be imported, the database explorer statement library comes installed with all system-defined statements that are available in the M_SYSTEM_INFORMATION_STATEMENTS system view and all statements that are included in the SQL Statement Collection for SAP HANA SAP note.
You’ll see these system-defined statements as soon as you open your statement library. You can run stored statements directly from the statement library, and if you’re moving to another system, or want to add statements that you’ve stored locally, you can use the statement library’s Import and Export functionality.
Run Statements in the Background
Because the database explorer, unlike studio, provides the functionality to run database queries in the background, you can set your stored statement to run against one or more database and then close your Internet browser and disconnect from your system. The query will still run in the background and its results will be waiting for you in the Background Activity Monitor the next time you connect to your system in the database explorer!
For example, say you want to run some of the system-defined diagnostic scripts (which are updated every release) against all the tenant databases in your system. Perform the following steps to run the scripts as background activities on multiple databases:
- Open the statement library by right-clicking your database and clicking Show Statement Library.
- Click the diagnostic script that you want to run and click Open in SQL Console.
- In the console, click Run > Run on Multiple Databases.
- Select the databases where you want the script to run and click Run SQL.
- Repeat these steps for every diagnostic script you want to run.
Result: The scripts run as background activities on all targeted databases, meaning you can close your browser session and/or disconnect from your system.
Things to Consider…
System-defined statements are only available for named database connections. The maximum size allowed per user for all stored statements is 10 MB, uncompressed.
For more information, visit the official statement library documentation: Use the Statement Library to Administer Your Database.
For more information on running activities in the background, see Running Operations in the Background in SAP HANA Database Explorer.