1969700 - SQL statement collection for SAP HANA

Symptom

During a SAP HANA database analysis it is required to collect special information.
Last update: 2016/01/24

Reason and Prerequisites

As part of SAP services like

• SAP Technical Performance Optimization - HANA (TPO - HANA; see SAP Note 2177604)
• SAP System Administration - HANA (SA - HANA)

and during the analysis of complex problems it can be required to determine special database information which is partially not available in standard functionalities like SAP HANA Studio (SAP Note 2073112) or DBACOCKPIT (SAP Note 2222220).

Solution

The attached archive contains a collection of useful SQL statements. Some of them contain configuration settings marked with "/* Modification section */" that can be adapted to change the results.

The modification section typically contains settings of the following types:

Number parameters:

Used for various purposes, -1 disables the setting

  30 NUM_RECORDS       /* Limit result set to 30 records */

  -1 NUM_RECORDS       /* No limitation of the result set */

String parameters (fix set of possible values):

Used to choose from a fix set of different processing options, allowed values are provided in comment

      'SUM' AGGREGATION_TYPE,       /* MAX, AVG, SUM */

String parameters (variable values):

Used for various purposes, '%' is placeholder for an arbitrary string (comparable to '*' in other environments), '_' is placeholder for a single character (comparable to '?' in other environments), single '%' disables the setting

      'indexserver' SERVICE_NAME,    /* Perform analysis only for indexserver */

      '%' SERVICE_NAME,                 /* Perform analysis for all services */

      '%server' SERVICE_NAME,        /* Perform analysis for all services ending with 'server' */

On / off switches:

 Used to enable and disable a certain feature, 'X' for enabling the functionality, ' ' for disabling the functionality

      'X' ONLY_ACTIVE_THREADS,     /* Show only information for active threads */

      ' ' ONLY_ACTIVE_THREADS,     /* Show information for all threads */

Be aware that the retention time of histories is typically related to the configuration of the statistics server. Normally histories of 42 days are configured, but on demand you can adjust it based on your needs. See SAP Note 2147247 for more information.

SAP doesn't take over responsibility for the correctness of the commands. The statements can put a significant load of the system, that can result in resource bottlenecks and terminations in the worst case. Therefore you should familiarize yourself with the commands in a non-production environment.

If you receive an error when running a SQL command, consider the following information:

Code	Error	Details
-709 -813 -813	Cannot connect to VolumeID=<volume_id>: Cannot connect to host <host>:<port> [Unknown host] Cannot connect to VolumeID=<volume_id>: Cannot connect to host <host>:<port> [Connection refused] Cannot connect to VolumeID=<volume_id>: Cannot connect to host <host>:<port> [Connection timed out]	These errors indicate that SAP HANA hosts and disks can't be reached. It is not a problem of the SQL statements, instead it is an infrastructure issue. See SAP Note 2222200 and take appropriate actions that the SAP HANA host and disk volumes are accessible.
259 260	Could not find table/view <name> in schema <schema> invalid column name: <column>	Make sure that you use the SQL command variant fitting to your system (proper Revision level and statistics server type).

Further details can be provided as part of a TPO or SA service for HANA.

Attachment: 

http://sh.st/moBwn