3.3Analyzing Database Accesses Using the SQL Trace

In this section, we’ll introduce some database programming tools. We’ll focus on tools you can use for the database programming tasks described in this chapter. Tools for performance and error analysis are introduced in Chapter 7.

In the previous sections, we explained how the ABAP language, the database interface in the kernel (DBI, DBSL), and the database interact. We also described how SQL access from ABAP is used via primary or secondary connections. Using the SQL trace tool, you can track and check this procedure. In the following text, we’ll use examples to demonstrate how you can analyze the following aspects directly within the system:

• Statement transformations (transformation of Open SQL into Native SQL via the database interface)

• Native SQL

• Usage of secondary connections and Native SQL (ADBC)

• Usage of the ABAP table buffer

3.3.1Statement Transformations

Statement transformations of the DBI are described based on the example in Listing 3.16.

"Variables for the result
DATA: ls_sflight TYPE sflight,
lt_sflight TYPE TABLE OF sflight,
ls_scarr TYPE scarr,
ls_sbook TYPE sbook,
lv_count TYPE i.

"Parameter for airlines
SELECT-OPTIONS: so_carr FOR ls_sflight-carrid,
so_conn for ls_sflight-connid.
"Client handling and Open SQL -> Native SQL
SELECT *
FROM sflight UP TO 200 ROWS
INTO ls_sflight
WHERE carrid IN so_carr
and connid in so_conn.

APPEND ls_sflight TO lt_sflight.
WRITE: / ls_sflight-mandt, ls_sflight-carrid,
ls_sflight-connid, ls_sflight-fldate.

ENDSELECT.

"Open SQL -> Native SQL
MODIFY sflight FROM TABLE lt_sflight.
COMMIT WORK.

DELETE ADJACENT DUPLICATES FROM lt_sflight
COMPARING carrid connid.

"FOR ALL ENTRIES on SBOOK
IF lines( lt_sflight ) > 0.
SELECT *
FROM sbook
INTO ls_sbook
FOR ALL ENTRIES IN lt_sflight
WHERE carrid = lt_sflight-carrid
AND connid = lt_sflight-connid
AND fldate = lt_sflight-fldate.

ENDSELECT.
ENDIF.

lv_count = sy-dbcnt.
WRITE: / lv_count, 'SBOOK'.

DELETE ADJACENT DUPLICATES FROM lt_sflight
COMPARING carrid.

"FOR ALL ENTRIES on SFLIGHT
IF lines( lt_sflight ) > 0.
SELECT *
FROM scarr
INTO ls_scarr
FOR ALL ENTRIES IN lt_sflight
WHERE carrid = lt_sflight-carrid.
ENDSELECT.
ENDIF.

lv_count = sy-dbcnt.
WRITE: / lv_count, 'SCARR'.

Listing 3.16Sample Program 1 for DBI Functions

Using the SQL trace in Transaction ST05, you can record the SQL statements that are sent to the database:

1. Start Transaction ST05 (see Figure 3.10).

2. Click Activate Trace. Start the program and select a range of airlines. We want to display all airlines with an abbreviation from AA to LH, but not DL. Only the selection option for airlines (CARRID) is filled; the selection option for the connection numbers (CONNID) remains empty.

3. Click Deactivate Trace.

4. Click Display Trace.

   

   Figure 3.10Transaction ST05: Recording an SQL Trace

A list with the recorded SQL statements is displayed. We’ll now briefly explain the most important columns; for more detailed information on SQL traces, refer to Chapter 7. In the result list of the recorded SQL statements (see Figure 3.11), the columns listed in Table 3.3 are important for the explanations in this section.

Figure 3.11SQL Trace List

Column	Description
hh:mm:ss:ms	Time stamp of the execution in milliseconds
Duration	Duration of the statement in microseconds
Records	Number of records processed by the statement
Program Name	Name of the program where the statement is executed
Object Name	Name of the object to which the statement refers
Statement	The actual SQL statement
DB Conn.	Database connection used to execute the statement
User	SAP user who executed the statement

Table 3.3Fields of the SQL Trace Analysis

Further columns, which aren’t shown in Figure 3.11, are the Client and Work Process Type where the SQL statement was executed. We’ll now take a closer look at the first SQL statement for table SFLIGHT. When this statement was executed, the system read the first 200 rows (all columns) from table SFLIGHT that match the selected airlines.

Double-click the first statement for table SFLIGHT in the trace list to open the detail view shown in Figure 3.12. This view shows the SQL statement as it was sent from the DBI to the database.

When comparing the Native SQL statement in the SQL trace to the Open SQL statement in the ABAP program, you’ll notice the following:

• The client was inserted automatically in the WHERE condition of the Native SQL statement.

• The Open SQL addition UP TO <n> ROWS was translated into TOP 200 for the SAP HANA-specific Native SQL.

• The selection option IN so_carr was translated into a WHERE condition.

• The selection option IN so_conn wasn’t sent to the database because it doesn’t include any data.

  

  Figure 3.12Detail View of the SQL Trace Record

Let’s now look at the second SQL statement for table SFLIGHT in the list from Figure 3.11. The Open SQL MODIFY command was translated into an UPSERT statement by the DBI. The UPSERT command (a combination of the terms UPdate and inSERT) first tries to update the transferred records. If this isn’t possible because the records don’t yet exist, they are inserted via an INSERT statement.

[»]Implementation of the MODIFY Statement in the Database

On other database platforms and older SAP releases, the MODIFY statement is split by the DBI, which can also be traced and analyzed in Transaction ST05. In the SQL trace, you’ll see two statements in this case: an UPDATE statement, and—if this first statement wasn’t successful—an INSERT statement. However, an increasing number of database vendors provide native statements for this logic. The names of those statements are MERGE or UPSERT. As soon as such statements are available for a database, SAP will use them in the DBI. This means that the well-known UPDATE/INSERT sequence for the MODIFY statement will gradually disappear and be replaced by a Native SQL statement with the same function. This reduces the number of SQL statements sent to the database (round trips) and thus increases the performance.

Let’s now take a look at the two FOR ALL ENTRIES statements for tables SBOOK and SCARR (Listing 3.16). When analyzing the list of SQL statements in Figure 3.11, you’ll notice that even though only one FOR ALL ENTRIES statement was written in the program for the two tables, table SBOOK is listed four times, while there is only one entry for table SCARR. This is because the driver table of the FOR ALL ENTRIES statement is divided into packages so that several statements are created if the driver table doesn’t fit in one package.

When comparing the FOR ALL ENTRIES statement for table SBOOK in the ABAP program’s Open SQL versus the statement in the SQL trace’s Native SQL, you’ll notice that there are several references to the internal table (driver table) in Open SQL. The CARRID, CONNID, and FLDATE fields are compared to a column from the internal table. As a consequence, for every row of the internal table, an OR expression is created. Figure 3.13 shows such a chain of OR comparisons. This way, the comparisons are appended to the statement using OR operators. When the maximum package size (a certain number of OR operators) is reached (blocking factor), the first statement is sent to the database. Further packages are then created until all entries of the internal table are processed.

Figure 3.13FOR ALL ENTRIES with OR Operators

For the second FOR ALL ENTRIES statement for table SCARR, there is only one reference to the internal table (for the CARRID field). This results in the statement being translated with an IN list. For every row of the internal table (driver table), an element is generated in the IN list (see Figure 3.14).

Figure 3.14FOR ALL ENTRIES with the IN List

The FOR ALL ENTRIES clause is described in more detail with regard to performance and memory consumption in Chapter 14. In this section, we mainly wanted to show you how the DBI translates Open SQL into Native SQL, and how you can trace and analyze both variants in Transaction ST05.

You learned how Open SQL statements are modified for the SAP HANA database and translated into Native SQL statements. We mentioned automatic client handling, the selection options, and the FOR ALL ENTRIES clause. In addition to those aspects, there are further transformations (e.g., loading the table buffer or accessing number range buffers) that weren’t presented in this chapter.

[»]FOR ALL ENTRIES with the Fast Data Access Protocol

If the Fast Data Access (FDA) protocol is active, the DBI implements the FOR ALL ENTRIES statement in a different manner than described here.

FDA is an optimized protocol for exchanging data between the database and the database client. For FOR ALL ENTRIES using FDA, the data transport of the internal table to the database is run in an optimized form. Here, the internal table is transferred more efficiently to the database, where it’s created as a temporary table. It’s then joined with the database table of the FOR ALL ENTRIES statement. The transport of results from the database to the database client is also run in an optimized form. The following parameters are prerequisites: rsdb/fda_level = 3 and rsdb/prefer_join_with_fda = 1. For more information, refer to SAP Note 1987132.

3.3.2Secondary Connections

We’ll now use the example from Listing 3.14 to demonstrate how you can analyze accesses to a secondary connection. In the second sample program, all unique connections of table SFLIGHT are read once via a secondary connection (with the addition CONNECTION) and the standard connection.

DATA: ls_sflight TYPE sflight.

SELECT distinct connid
FROM sflight CONNECTION ('QH3')
INTO ls_sflight-connid
WHERE carrid = 'LH'.
WRITE: / ls_sflight-connid.
ENDSELECT.

ULINE.

SELECT distinct connid
FROM sflight
INTO ls_sflight-connid
WHERE carrid = 'LH'.
WRITE: / ls_sflight-connid.
ENDSELECT.

Listing 3.17Sample Program 2 for Database Connections

In the SQL trace list in Transaction ST05, the name of the logical database connection is displayed in the DB Conn. column (Figure 3.15). In this list, R/3 always stands for the standard connection. Other connections are displayed using the name that was defined upon their creation.

Figure 3.15 shows that the statement was executed once for every connection and that table SFLIGHT contains different flight connections in the QH3 system. Transaction STAD, which will be described in Chapter 7, provides further information (e.g., the number of records read and the duration per database connection).

Figure 3.15SQL Trace—Standard and Secondary Connection

3.3.3Native SQL

Moreover, Transaction ST05 can be used to check whether ADBC was used. To do so, take another look at the source code shown earlier in Listing 3.11. The corresponding SQL trace analysis is shown in Figure 3.16. What’s interesting in this analysis is the Program Name, which refers to the CL_SQL_STATEMENT class. This indicates that ADBC was used for access. While the SQL statement is created as a string somewhere else within the program, it is first executed in the CL_SQL_STATEMENT class in this case (after the string was passed with the statement).

Figure 3.16Analyzing Native SQL (ADBC) in Transaction ST05

The Object Name is the name of the table, which was set using the SET_TABLE_NAME_FOR_TRACE method. If no name is specified, the system tries to translate and display the object name from the FROM clause. In general, however, you should always define a table name because this is very important for other tools as well.

3.3.4Buffer

You can also analyze table accesses to buffered tables using Transaction ST05. For this purpose, the table buffer trace (refer to Figure 3.10) must be activated. Otherwise, the SQL trace doesn’t display table accesses processed via the table buffer. As an example, in Listing 3.15, we’ll analyze the program from Figure 3.17 using the SQL and the table buffer trace.

" Variables for the result
DATA: ls_sflight TYPE sflight,
ls_spfli TYPE spfli,
lv_count TYPE i.

"Parameter for airlines
SELECT-OPTIONS: so_carr FOR ls_sflight-carrid.
" Read all flights
SELECT *
FROM sflight
INTO ls_sflight
WHERE carrid IN so_carr.

" Details (buffered table)
SELECT SINGLE *
FROM spfli
INTO ls_spfli
WHERE carrid = ls_sflight-carrid
AND connid = ls_sflight-connid.

IF sy-subrc = 0.
WRITE: / ls_sflight-mandt, ls_sflight-carrid,
ls_sflight-connid,
ls_sflight-fldate, ls_spfli-countryfr,
ls_spfli-cityfrom, '->',
ls_spfli-countryto, ls_spfli-cityto.
ENDIF.

ENDSELECT.

Listing 3.18Sample Program 3: Accesses to the Table Buffer

Figure 3.17 shows the common result list of the traces (for Listing 3.15). Database accesses appear in yellow (first row), while accesses to the table buffer appear in blue (second through fifth rows). In the Statement column, accesses to the database can be identified by the SQL syntax, while accesses to the buffer are only displayed using the technical keys. As you can see, no SELECT statement is displayed for the buffer accesses from our example.

Figure 3.17Transaction ST05: SQL and Buffer Trace