SAP PRESS :: ABAP® Development for SAP HANA® - Runtime and Error Analysis with SAP HANA

7.2Troubleshooting

Before we discuss performance optimization tools, we want to introduce some important error analysis tools. As the saying goes, “You can’t make an omelet without breaking eggs,” so functional problems may occur when making changes to a program or a new development, especially if the previous program code is very old, and the author is no longer available.

Therefore, in this section, we’ll discuss some aforementioned elements, namely testing, analyzing program terminations, tracing, and debugging. Here, we’ll focus on error analyses within the context of database accesses and the use of native implementations in SAP HANA.

We’ll explain some approaches in relation to writing unit tests for SAP HANA views and procedures in ABAP, discuss the analysis of program terminations in the context of database accesses in Transaction ST22, and introduce the concept of tracing and debugging SQLScript.

7.2.1Unit Tests

When making changes to program code, it’s very helpful to have a set of tests (preferably automatic) that can be performed both before and after making the changes; this helps to identify errors as soon as possible. In this context, the approach of testing single objects (units), either individually or in combination, is known as unit testing. ABAP Unit is integrated into the ABAP language and development infrastructure, and it can be used to write unit tests. This tool is also integrated into the ABAP Test Cockpit, which we’ll discuss in Section 7.3. In addition to the ABAP Unit tool, AS ABAP also provides support for further testing approaches, such as integration tests or simulated user interactions. However, these are beyond the scope of this book.

You should also conduct tests to safeguard complex implementations in SAP HANA (in SQL and SQLScript, in particular). The sophisticated test infrastructure in the ABAP AS provides a good framework here. We’ll use the ABAP Managed Database Procedure (AMDP) GET_UTILIZATION method, which we introduced in Chapter 6, Section 6.5.1, as an example. This procedure determines the average percentage utilization of flights for each flight connection. A simple unit test for the AMDP method is shown in Listing 7.1.

This test validates that the average utilization for the flight connection LH 0400 is calculated as expected.

CLASS ltcl_flights_amdp DEFINITION FINAL FOR TESTING
DURATION SHORT
RISK LEVEL HARMLESS.

PRIVATE SECTION.
DATA:
flights_under_test TYPE REF TO zcl_a4h_chapter6_flights_amdp,
act_utilization TYPE zcl_a4h_chapter6_flights_amdp
=>tt_flight_utilization.
METHODS:
setup,
flights_gt_0_utilization_gt_0 FOR TESTING RAISING cx_static_check.
ENDCLASS.

CLASS ltcl_flights_amdp IMPLEMENTATION.

METHOD setup.
CREATE OBJECT flights_under_test.
ENDMETHOD.

METHOD flights_gt_0_utilization_gt_0.

flights_under_test->get_utilization(
EXPORTING
iv_mandt = sy-mandt
iv_carrid = 'LH'
IMPORTING
et_utilization = act_utilization ).

READ TABLE act_utilization
WITH KEY connid = '0400'
ASSIGNING FIELD-SYMBOL(<connection>).

cl_abap_unit_assert=>assert_subrc(
act = sy-subrc
msg = 'Test data not installed correctly. Flights not
found for LH 0400.'
level = if_aunit_constants=>tolerable ).

cl_abap_unit_assert=>assert_equals(
act = <connection>-utilization
exp = '48.44'
msg = 'Incorrect utilization for LH 0400.' ).

ENDMETHOD.

ENDCLASS.

Listing 7.1Unit Test for an AMDP Method

To gauge whether the calculation within the procedure is correct, the exact output data must be known. In general, it pays to have different sets of stable, consistent test data that can be used in different systems for different purposes (e.g., mass data for conducting performance tests). You can also use the ABAP client concept to generate suitable test data constellations in special clients.

[+]Design Patterns Make It Easier to Write Tests

The use of suitable design patterns makes it easier to write unit tests . These include modularization and decoupling as a result of well-defined interfaces, as well as avoiding dependencies in relation to specific system statuses.

For example, when testing database procedures, it makes sense to avoid reading directly from a Customizing table or application context within the procedure and instead transfer the required values as parameters. Such (generic) implementations are easier to test and increase the potential for reuse in other contexts.

Furthermore, it’s generally recommended to use a suitable interface to abstract a calculation in the ABAP application and therefore encapsulate an SAP HANA-specific implementation.

7.2.2Dump Analysis

If a program terminates during a transaction (known as a dump ), Transaction ST22 provides valuable troubleshooting information. In this section, we’ll explain the information you obtain when an error occurs with a database access.

For SQL statements, different types of runtime errors can occur and trigger a dump. Many of these errors can be caught within the application by means of a class-based exception. Table 7.2 groups together the most important exceptions. Here, special runtime error types exist for each category.

Category	Exception	Example
Error during Open SQL access	`CX_SY_OPEN_` `SQL_DB`	Use of an invalid cursor (see also Chapter 3, Section 3.2.2)
Syntactical error in dynamic Open SQL	`CX_SY_DYNAMIC_` `OSQL_SYNTAX`	Invalid, dynamically generated `WHERE` condition (see also Chapter 3, Section 3.2.2)
Semantic error in dynamic Open SQL	`CX_SY_DYNAMIC_` `OSQL_SEMANTICS`	Aggregation by means of a nonnumerical, dynamically specified column (see also Chapter 3, Section 3.2.2)
Error during ABAP Database Connectivity (ADBC) access	`CX_SQL_EXCEPTION`	Syntactical error in a Native SQL statement (see also Chapter 3, Section 3.2.4)
Error while calling a database procedure	`CX_SY_DB_PROCEDURE`	Runtime error in SQLScript (see Chapter 4)
Errors in the context of AMDP	`CX_AMDP_ERROR` `(and subclasses)`	Runtime errors in SQLScript (see Chapter 6, Section 6.5)
Noncatchable errors	None	Internal error during a database access

Table 7.2Error Categories for SQL Accesses

In Transaction ST22, the short text is the initial starting point for an analysis, in addition to the exception that occurred and the runtime error type (e.g., DBIF_RSQL_SQL_ERROR). The short text contains, for example, information such as SQL error <number> occurred while accessing table <table>. Figure 7.1 shows an example of an error that occurred while accessing an SAP HANA view that doesn’t exist. In most cases, this error text contains enough information to enable you to localize and resolve the problem.

Figure 7.1Error Text in the Database

Further contextual information in relation to an ABAP program is available in the following sections in Transaction ST22:

Information on which statement caused the dump
Source code extract
Contents of system fields
Chosen variables
Active calls/events

[+]Error When Accessing SAP HANA Views and Database Procedures

When you use external views and database procedure proxies (see Chapter 5), development errors can occur as a result of inconsistencies (e.g., if the view or the procedure was changed without updating the proxy). In most cases, this situation causes an exception of the type CX_SY_OPEN_SQL_DB (with the error text Invalidated view …). In this case, you need to synchronize the objects across the development environment as described in Chapter 5.

If you require further information from the database, the analysis must continue there. The information in Transaction ST22 is no longer sufficient, particularly for more extensive implementations within the database (e.g., a database procedure that calls an additional procedure). In such cases, however, you can use the information available to reconstruct the call that triggered the error and then use debugging and tracing to continue the analysis.

7.2.3Debugging and Tracing in SQLScript

If you analyze an error within an implementation in SQLScript, you may want to view certain interim results or trace them successively in the implementation. For this purpose, you can either debug SQLScript or store it as an interim result in temporary tables using the TRACE statement.

We already described the debugging of AMDP in detail in Chapter 6, Section 6.5.2. You can precisely track the SQLScript execution by setting breakpoints in the implementation and inspecting the variable values. For debugging of native SAP HANA procedures, refer to the SAP HANA development guide at http://help.sap.com/hana.

Additionally, SQLScript contains the TRACE calculation engine (CE) plan operator , which enables you to log the contents of a local table variable (displays an interim result for a database procedure) in a local temporary table. It allows you to test calls using various combinations of parameters.

For the GET_AGENCIES_FOR_CONNECTIONS database procedure, which we used as an example in Chapter 4, Section 4.2.1, the TRACE plan operator can look like the following:

LT_AGENCIES = SELECT...
LT_AGENCIES = TRACE(:LT_AGENCIES);
ET_AGENCIES = SELECT...

The system automatically creates the local temporary table when it calls the database procedure. This table has the same structure as the table variable. To determine its name, you can read the SQLSCRIPT_TRACE monitoring view after you call the database procedure. Because this is a local temporary table, it can only be viewed within the same database connection. Note that the system doesn’t undertake some optimizations when the TRACE CE plan operator is used. Furthermore, logging the contents of the table has a negative impact on runtime. Therefore, don’t use the TRACE CE plan operator in productive code.