Show TOC

Background documentationSQL Trace Analysis Example Locate this document in the navigation structure

 

Using the SQL trace, you can check exactly how the system handles database requests. In this example, an application reads and changes records on the database tables TMP_EDM_EMPLOYEE and TMP_EDM_EMP_PRJ by means of Open SQL/JDBC. The trace file also logs the time of occurrence and the duration of the requests.

Read Access

The following SQL trace list shows each read request to the database table TMP_EDM_EMPLOYEE that the application sends.

This graphic is explained in the accompanying text.

The system executes several JDBC methods that are used for retrieving records from the table:

  • Connection.prepareStatement

    This method prepares the statement to be executed against the database. The column Statement shows the statement itself.

  • PreparedStatement.executeQuery

    This method executes the prepared statement. The columns StmId and ResultSetId show the IDs of the Vendor statement and the result set, respectively. You can also view the SQL statement bind parameters in the Record Details view. Since no error is reported, the statement is successfully executed. The database should return all employees who earn more than 50,000.

  • ResultSet.next

    The column Num. displays the number of accumulated subsequent next-calls. Hence, 22 records (employees) fulfill the requirement. The last next-call returns FALSE.

  • ResultSet.close

    This method closes the result set.

Write Access

The following SQL trace list shows each write request to the database table TMP_EDM_EMP_PRJ that the application sends.

This graphic is explained in the accompanying text.

The system executes the following JDBC methods:

  • Connection.prepareStatement

    This method prepares a statement that defines a relationship between employees and projects.

  • PreparedStatement.addBatch

    Four combinations of employees and projects are added to the batch. You can view these combinations in the Record Details view.

  • PreparedStatement.executeBatch

    The system executes the batch successfully.

  • PreparedStatement.clearBatch

    The system clears the batch.

  • PreparedStatement.close

    The system closes the statement.

Analyzing Large SQL Traces

To view a particular record in a large SQL trace list, filter the list by the date and time under interest. Using the Advanced Filter, you can reduce the list according to other criteria, for example user name or application name.

Caution Caution

Sometimes the system does not record the user name or the application name, and they are not listed in the SQL trace.

End of the caution.

After displaying the SQL trace list, you can also filter it by the content of its columns.

Note Note

To enable the column filter, choose . A blank row appears at the top of the trace list. You can type filter criteria in each of the blank cells to reduce the number of records in the list.

End of the note.

When you analyze SQL traces, the typical scenarios are the following:

  • To find all statements that are executed on a specific database table, enter the table name in the Statement column filter.

  • To find all statements that last for a long time, sort the Duration column in a descending order and scroll to the top of the list.

  • To find all next-calls for a specific result set, filter the list by the ResultSetId column.

  • To find all statements that failed and returned some error, filter the list by the error code under interest or sort the Error column in a descending order and scroll to the top of the list.

  • To check if a commit is executed within a connection, filter the list by the Connection column and enter commit in the Jdbc Method or in the Statement column.

  • If you find a statement that returns a database error, and if you want to view all statements executed before or after this error statement, then:

    • Select the statement under interest.

    • Sort the list by the Time column in ascending order.

    • Remove all other filters.

      The system displays the original list and highlights the error statement, so that you can analyze the environment of this statement.

  • If you want to analyze the SQL trace file within a different environment than the SAP NetWeaver Administrator, you can download it as an XML file.

    For more information, refer to Working with the SQL Trace File in Managing the SQL Trace.