Show TOC

DiagnosticsLocate this document in the navigation structure

These sections describe functions to monitor database activity and to analyze specific issues that may occur.

Audit Log

The DBA audit log records all actions that make changes to the database from DBA Cockpit. For example, starting, stopping, and reconfiguring services, changes to parameters in configuration files, deletion of trace files, and table imports.

Note The Audit Log shows only changes that were made using DBA Cockpit. Changes made using SAP HANA studio or other tools are not shown in the DBA Cockpit Audit Log.

The following information is displayed about changes made to the database:

Field

Description

Date

Start date of the action

Time

Start time of the action

System

Target system on which the action was performed

Action

Type of action (name of the action in DBA Cockpit)

Command

Type of command (for example, ADD, DELETE, or EDIT)

Object

Name of the changed object (for example, database or tablespace name)

User

Name of the SAP user who performed the action

From System

System from which the action was performed

Missing Tables and Indexes

Missing Tables and Indexes shows the differences between the database in the SAP system and the ABAP dictionary. For example, tables in the database, which are not in the ABAP dictionary.

Note The Missing Tables and Indexes function is only available for local systems and for remote ABAP systems, for which an additional RFC destination has been assigned.

Missing Tables and Indexes can be helpful, for example, to check whether an installation was performed correctly. You can determine which indexes are missing, and check the consistency of database objects and ABAP Dictionary objects.

To access Missing Tables and Indexes:

From DBA Cockpit, choose Start of the navigation path Diagnosis Next navigation step Missing Tables and Indexes End of the navigation path.

The following information from the last consistency check is displayed:

Section

Description

Objects missing from the database

Objects that are defined in the ABAP Dictionary, but not found in the database

Unknown objects in the ABAP Dictionary

Objects that are found in the database, but not defined in the ABAP Dictionary

Inconsistent objects

Results of the detailed comparison of the ABAP Dictionary and the database

Other checks

Different checks are performed here:

Check whether the primary index of tables defined in the ABAP Dictionary was created uniquely on the database.

Check for objects in the SAP system tables that cannot be described at all or that cannot be completely described in the ABAP Dictionary.

If inconsistencies in these objects are detected, they are also displayed here with additional information about the type of inconsistency.

Optional indexes

Mismatch between ABAP Dictionary and database regarding secondary indexes

Note

Information displayed is not up-to-the-minute. Last check displays the date of the last update. As a result, the data could be up to several days old. Any issues may have been resolved in the meantime.

To update, choose Refresh. It may take some time to update all the information.

EXPLAIN

The EXPLAIN function shows the execution plan for SELECT, INSERT, UPDATE, or DELETE statements, and provides information about the internal processes.

The EXPLAIN function describes the search strategy used internally by the database system for a statement, and shows indexes are used for the search.

You can use the EXPLAIN function to find out which search strategy the SQL optimizer selects to execute a statement and what this search strategy costs. You can check the effect of creating or deleting indexes on the choice of search strategy for a SQL statement. You can also estimate the time needed by the database system to process an SQL statement.

Note When you perform an EXPLAIN, the SQL statements are not executed. To execute a statement, use the Execution Trace function.
Table 1: EXPLAIN Options
Option How To...
To save a statement to a file. Choose Save as local file.

You are prompted to specify a name for the file.

The content of the pane is saved to a plain text file. Depending on when you save, the contentof the pane may be a statement or the result of a statenment execution.

To upload a statement from a file. Choose Start of the navigation path Diagnostics Next navigation step Explain Next navigation step Load local file End of the navigation path and locate the file to upload.
To analyze the statement. Choose Explain.

The execution plan is displayed.

To save the execution plan. Choose Explain.

The execution plan is displayed.

Choose Start of the navigation path System Next navigation step List Next navigation step Save Next navigation step Local File End of the navigation path.

You are prompted to specify a name for the file.

To execute a statement (instead of analyzing the search strategy). Choose Start of the navigation path Explain Next navigation step Execution Trace End of the navigation path.
To show a graphical structure of the query. Choose Start of the navigation path Explain Next navigation step Graphical Explain End of the navigation path.

A browser window is opened.

Choose Explain Query Plan to display a graphic.

Choose Detailed Graph to show the details for all the nodes.

Self-Monitoring

You can use the self-monitoring functions in DBA Cockpit to identify and resolve issues related to database configuration or monitoring configuration that may prevent DBA Cockpit from running properly.

The self-monitoring infrastructure offers a set of checks that are predefined by SAP. These checks automatically run for each database monitored by the DBA Cockpit.

Note

The self-monitoring functions only check the monitoring infrastructure, not the database systems.

To check the database systems, use the alerting infrastructure.

You can use self-monitoring as follows:

Table 2: Self-Monitoring Tasks
Self-Monitoring Task Description

For a single database

Choose Start of the navigation path Diagnostics Next navigation step Self-Monitoring End of the navigation path.

For all the systems in your system landscape

Choose Start of the navigation path System Landscape Next navigation step Landscape Self-Monitoring End of the navigation path.

The underlying self-monitoring checks are the same for both a single database and for all databases in a landscape.

Note

If a check was successful, it is not displayed here.

Checks that are not relevant are not performed, and not displayed. For example, if you are not working in a SAP Solution Manager system, checks that are specific to SAP Solution Manager are not performed.

The checks include the following:

Table 3: Self-Monitoring Checks
Self-Monitoring Check Description

Check the database connection.

This check is offered by the framework.

Note You can perform this check manually by choosing Start of the navigation path System Landscape Next navigation step System Configuration Next navigation step Test Connection End of the navigation path.

Check the availability of the statistics server.

This check verifies that SELECT statements can be performed on tables and table views on the statistics server.

Access to the statistics server is needed to read alerts, information about large tables, information about memory consumption, disk sizes, and IO statistics. If the statistics server cannot be accessed, this information cannot be displayed.

Check for an expired SAP HANA license.

From DBA Cockpit, choose Start of the navigation path System Information Next navigation step License End of the navigation path.

Check whether database user passwords have expired or whether a user is locked.

To display information about the error codes for passwords:

  1. From DBA Cockpit, choose Start of the navigation path System Information Next navigation step Others Next navigation step SQL Error codes End of the navigation path.
  2. Use the filter: *pass*.
    Example

    The following error codes may be displayed:

    • 10 ERR_INV_PASSWORD invalid username or password

    • 408 ERR_SQL_PASS_OR_PARAMETER_NEEDED password or parameter required for user

    • 412 ERR_SQL_INV_PASSWORD_LAYOUT invalid password layout

    • 413 ERR_SQL_PASSWORD_REUSED last n passwords can not be reused

    • 414 ERR_SQL_ALTER_PASSWORD_NEEDED user is forced to change password

    • 420 ERR_SQL_ALTER_PASSWORD_NOT_ALLOWED password change currently not allowed

    • 431 WRN_SQL_NEARLY_EXPIRED_PASSWORD user's password will expire within few days

    • 453 ERR_SQL_USER_WITHOUT_PASSWORD user does not have a password

Check alert thresholds.

Checks are done to determine whether the alert thresholds in the SAP Solution Manager match the alert thresholds in the database. If any thresholds do not match, they are displayed in a list. If the values are not the same, and the value in the database was changed, (db) is displayed. If the value in SAP Solution Manager was changed, (sm) is displayed.

There are two types of checks, which are shown in column Depth:

  • Fast

    This check type can be executed at runtime. To update its status, choose Refresh.

  • Detailed

    This check type is more detailed than a fast check, but only runs in batch mode.

    A red status indicates that the check resulted in an error and you should investigate the error.

    If a SAP Note is available for the issue, it is displayed by the check. Double-click the SAP Note cell to display the SAP Note.

    To display more information, double-click a check. The Self-Monitoring Details screen is displayed.

Self-Monitoring Details

The Self-Monitoring Details screen shows the detailed results of each check and advises you as to what action to take.

To analyze and repair an issue identified by the check, double-click a row with a comment in the Repair Tool column. Choose Go to Repair Tool to analyze and repair the issue identified by the check.

SQL Editor

You can use the SQL Editor to execute SQL statements, including complex statements.

Prerequisites for Executing SQL Statements

To be able to use the SQL editor to efficiently analyze a database, specific authorizations are required.

More information: SAP Authorization Objects and SAP Roles for SQL Editor in Related Information

Rules for Using the SQL Editor

When you use the SQL Editor, the following rules apply:

  • You can write SQL syntax elements in either upper or lower case.

    To force the system to distinguish between uppercase and lowercase letters in database object names, enter the object name enclosed by double quotation marks. For example: "My_Table"

  • You can add any number of spaces and line breaks.

More information: SAP Note 1969700 - SQL statement collection for SAP HANA in Related Information

Table 4: Functions of the SQL Editor

Function

Description

Create an SQL query.

You can type an SQL query in the Input Query pane.

To insert code from the clipboard, choose Insert.

Save an SQL query.

Choose Save Query, specify a name for the query, and Save.

To save a query to a plain text file on your computer, choose Save as local file, go to the desired location, and confirm.

You can schedule saved queries using the DBA Planning Calendar.

More information: DBA Planning Calendar in Related Information

Delete an SQL query.

Choose Delete Query and confirm.

Load an SQL query.

To insert a saved query, select a name from the Query Name field and choose Load Query.

To insert a query from a plain text file on your computer, choose Load local file, locate the file, and confirm.

Execute an SQL query.

Choose Execute.

The output is displayed in the Results tab.

Display the access strategy and the execution plan.

Choose Explain.

Note The statement is not executed; its execution is only simulated and the resulting information is then displayed. The time that the statement would need to run is estimated.

Display queries that have been executed.

The History field displays a list of queries that were executed.

Select a query from the history list to insert it into the Input Query pane.

Trace the execution of an SQL query

Select an SQL query and choose Execution Trace.

Note To trace their execution, statements need to have a value. The execution cannot be traced for statements that have '?' instead of a value.

Example

To use native SQL to display current statistics for large tables, adapt the following example statement:

Sample Code
SELECT TOP 500 SCHEMA_NAME, TABLE_NAME,

round(sum(greatest(ESTIMATED_MAX_MEMORY_SIZE_IN_TOTAL,0)/1024/1024),2) as estimated_max_memory,

round(sum(greatest(MEMORY_SIZE_IN_TOTAL,0)/1024/1024),2) as memory_total,

round(sum(greatest(MEMORY_SIZE_IN_MAIN,0) /1024/1024),2) as memory_main,

round(sum(greatest(MEMORY_SIZE_IN_DELTA,0)/1024/1024),2) as memory_delta,

sum(greatest(RECORD_COUNT,0)) as records

FROM SYS.M_CS_TABLES group by SCHEMA_NAME, TABLE_NAME

order by estimated_max_memory desc, SCHEMA_NAME, TABLE_NAME

More information: Large Tables in Related Information

Execution Trace

You can specify a SQL statement to trace. You can edit the statement and execute it.

Note If you change a SQL statement, the changes are not saved.

Execution Trace is similar to the EXPLAIN function. With EXPLAIN, the statement is not executed; with Execution Trace, the statement is executed and data is transferred. The trace is stored locally as a file.

Table 5: Execution Trace
Execution Trace Option How To...
To execute a statement Choose Execute.

The trace is stored as a file, which can be opened with SAP HANA studio or e-mailed to another user, who can then open it with SAP HANA studio.

You are prompted to download the trace data to local hard drive.

To download the SQL statement to a local file Choose Save as local file.

The statement is saved in plain text format.

To upload the SQL statement from a local file Choose Load local file and locate the file to upload.

Alternatively, you can drag a file with an SQL statement to the Execution Trace pane.

To display a graphic Drag and drop the trace file anywhere in the SAP HANA studio window.
Note The SAP HANA used to display the graphic in the SAP HANA studio does not need to be integrated in SAP HANA studio.
To show which ABAP coding originated the SELECT statement Choose Navigation to Editor.
To find the name of the ABAP program and the source code (ABAP only) Choose Application Source.

The code is displayed from the system in which DBA Cockpit is running. If this system is different from the monitored system, no code is displayed. DBA Cockpit cannot access code in a remote system.

Note DBA Cockpit can access code in a remote system through an RFC destination. For this, you need a SAP user and password.

If a statement was generated in code other than ABAP, DBA Cockpit displays the name of the program if the source cannot be displayed.

To display information about lock situations. By default, much of the lock information is not displayed. Display the relevant columns that are not displayed by default.

Tables/Views

You can display a table view, a view, or a monitoring view.

  1. Specify schema or a table name.

    You can use wildcard characters.

  2. Select Tables, Views, or Monitoring Views
  3. Choose Display/Find.
  4. From the results overview, double-click a row to display more information.
  5. The following tabs display detailed information: Properties (General info)/ Columns/ Indexes.

    For example, DBA Cockpit displays the estimated maximum size in memory for a table and its size on disk.

  6. To send the currently displayed table to an SQL statement in the SQL editor, choose Send to SQL Editor.

    A SQL statement is generated. You can edit the SQL statement first before you execute it.

Procedures

This section provides an overview of schemas, procedures, and their parameters.

For example, if you need to call an SAP HANA procedure, this screen can tell you the input and output parameters, and provide information about the data types.

Table 6: Functions for Procedures

Function

Description

To find a specific procedure.

Choose Find, specify a search term, and choose OK.

You are taken to the first occurrence of the specified search term.

Choose OK again to move to the next ocurrence of the search term.

To display the definition of a database procedure

Select a procedure and choose Show Procedure Definition.

The underlying SQL statement is displayed.

Optimize the column width

Choose Optimal Column Width.

The width of each column is changed to display all the content of each cell.

DB Users/Privileges

DB Users/Privileges displays the effective privileges for a database user for SAP HANA.

Diagnosis Files

This section provides an overview of a mixture of trace files, log files, and error messages.

You can filter, merge, delete, and download diagnosis files.

By default, diagnosis files are stored at the following location on the SAP HANA server:

/usr/sap/<SID>/HDB<instance>/<host>/trace

To display an overview of the diagnosis files, from DBA Cockpit, choose Start of the navigation path Diagnostics Next navigation step Diagnosis Files End of the navigation path.

Tip Periodically monitor the disk space that is used for diagnosis files and delete diagnosis files that are no longer needed.
Table 7: Functions for Diagnosis Files
Action Steps

To display the content of a diagnosis file

Choose Display File Content.

Display merged individual diagnosis files contained in a local database table.

Choose Merged Diagnosis Files.

More information: Merged Diagnosis Files in Related Information

Generate a runtime environment dump (RTE).

Note To generate an RTE dump, you require the system, privilege GRANT RESOURCE ADMIN.

Choose Generate RTE Dump.

You can specify the following additional information:

  • The host from which to collect the RTE dump files

    To display the available hosts, choose Possible Values.

  • The port.

    To display the available ports, choose Possible Values.

  • Alternatively, specify a date range.

    You can include or exclude system views.

After you have generated an RTE dump, you can display its content by choosing List Diagnosis Information.

Download the content of the diagnosis files.

Select one or more files, choose Download File(s) Content and specify the download location.

The content of the file(s) is saved in plain text format.

To download the content compressed, choose Download Compressed File(s) Content.

The plain text file is saved in .zip format.

Merged Diagnosis Files

You can select a series of individual diagnosis files and merge them into a local database table.

Merging diagnosis files allows you to review both trace and log files at the same time. This can be helpful, for example, if you are looking for a specific event, but do not know which trace file it is recorded in. You can search for a specific event in the merged diagnosis files.

The diagnosis data is not available in real time; it is stored in a separate table. The field Timestamp of the Newest Record in the DB Table displays the date and time of the last merge.

Note If no diagnosis files traces were merged, the Merged Diagnosis Files overview is empty.
Table 8: Merged Diagnosis Files
Task Steps
To merge the diagnosis files Choose Merge Trace Files into Local DB Table.

The timestamp of the last record in the database table shows you if up-to-date traces are displayed.

The diagnosis files are added to the database table.

To search for traces from a specific date Choose Read from Local DB Table.

You can use the filter options and timeframe slider to drill down and analyze further.

The overview of traces is updated.

To delete the databse table manually Choose Start of the navigation path Goto Next navigation step Delete local DB table End of the navigation path.
Note Depending on the size and number of files to be merged, merging diagnosis files can take some time.

Backup Catalog

The backup catalog stores information about the data and log backups that were executed for a database. This includes backups that were scheduled using the DBA Planning Calendar as well as using other tools such as SAP HANA studio.

The data backups are divided into entries for the nameserver and the index server, and are sorted into blocks for each system. For distributed systems, the blocks show the entries for all the services for each participating system. The log backups are not displayed in blocks as there is only one log backup.

Table 9: Functions for the Backup Catalog
Action Steps

To display different backups

You can switch between the following views:

  • Data backup, storage snapshot, differential backup, and incremental backup entries in the backup catalog

  • Log backup entries in the backup catalog

Toggle between Log Backup and Data Backup.

By default, the data backup view is active.

To specify the number of rows to display

Enter the number in <ROWNUM> and choose Refresh.

To display more information about executed backups

Select a backup and choose Details.

To cancel a running backup

Note The option to cancel a backup is only available while a backup is running.

You have the option of canceling a data backup, differential backup, or incremental backup.

Select a running backup and choose Cancel Backup. When the backup has been canceled, its status is changed to Canceled with the message:
Backup was canceled by user

After a backup has been canceled, you can start a new backup.

Note

If you cancel a running backup that is waiting for other resources, the cancelation is postponed until the cause of the wait situation has been resolved. Until that time, the backup is flagged as cancel pending.

In some situations, it may not be possible to cancel a running backup. For example, if it is not possible to access internal locks or if writing to a file on an NFS mount does not work.

To display or change the backup configuration.

Go to Start of the navigation path Configuration Next navigation step Backup Configuration End of the navigation path.

More information: Backup Configuration in Related Information

To delete one or more backups

(Use CTRL + click to select multiple backup entrues.)

Select one or more backup entries and choose Delete Backups.

Choose an option:

  • Only the marked backup(s)

  • All backups older than the marked

Confirm whether you want to delete the backup entries from the backup catalog, or also physically delete the backups.

Locks

This section provides an overview of information about blocked transactions, table locks, and record locks.

Here, you can find out what objects are locked, and on what level. For example, the number of the transaction that caused the locks. You can use this information to find out more under Start of the navigation path System Information Next navigation step Transactions End of the navigation path.

Blocked Transactions

Blocked transactions are transactions, for which processing cannot continue until specific resources are available. For example:

  • A transaction needs to acquire transactional locks (record or table locks),which are currently being held by another transaction.

  • A required network or disk (database or metadata locks) is currently unavailable.

The type of lock held by the blocking transaction (record, table, or metadata) is indicated in the Lock Type column.

The Lock Mode column shows the level of access that other transactions have to the locked record, table, or database.

Exclusive row-level locks prevent concurrent write operations on the same record. They are acquired implicitly by update and delete operations or explicitly with the SELECT FOR UPDATE statement. Table-level locks prevent operations on the content of a table from interfering with changes to the table definition (such as drop table, alter table). DML operations on the table content require an intentional exclusive lock, while changes to the table definition (DDL operations) require an exclusive table lock.

The LOCK TABLE statement is used to explicitly lock a table. Intentional exclusive locks can be acquired if no other transaction is holding an exclusive lock for the same object. Exclusive locks require that no other transaction hold a lock for the same object (either intentional exclusive or exclusive).

Note In a SAP HANA system, Analyze Exclusive Lockwaits (transaction DB01) opens Diagnostics: Locks: Blocked Transactions in DBA Cockpit.
Object Locks

This section provides an overview of the currently acquired locks on objects.

To display the information for a lock in a separate window, select a row and choose Details.

Record Locks

This section provides an overview of the status of record locks.

To display the information for a lock in a separate window, select a row and choose Details.

LiveCache Locks

This section is displayed only if liveCache is supported in this SAP HANA release.

liveCache uses its own lock manager for object locks, container locks, and schema locks. This section provides an overview of information about all the locks that are currently in this lock manager.

Exclusive locks on objects are not normally managed by this lock manager, but the information about a lock is stored in the object header.

LiveCache Lock Statistics

This section is displayed only if liveCache is supported in this SAP HANA release.

liveCache uses its own lock manager for object locks, container locks, and schema locks. This section provides an overview of the accumulated statistics for lock requests to this lock manager since restart.

liveCache Objects

These sections provide an overview of the SAP liveCache statistics.

Note These section is displayed only if liveCache is supported in this SAP HANA release.
OMS Versions

This section provides an overview of information about Object Management System (OMS) versions.

Heap Usage

This section provides an overview of the memory allocator statistics.

Container

This section provides an overview of the liveCache container statistics.

Schemas

This section provides an overview of the liveCache schema statistics.

Procedure Statistics

This section provides an overview of the liveCache procedure statistics.