Introduction

An earlier article on developerWorks introduced an SQL Procedure Profiler tool that discovers the SQL procedures invoked by an application in flight, profiles the procedures, and presents the collected data side-by-side with each procedure's source code. Now, rather than using a separate tool, use Developer Workbench to profile the execution of SQL stored procedures on DB2 LUW, Version 8.2 or later. This functionality is available in Developer Workbench, Version 9.1 or later and Rational Application Developer, Version 7.0 or later.

You can use this function to profile both procedures that are created within Developer Workbench or outside of it.

The profiling data captured can include the elapsed time for a given statement, the number of times a statement was called, the number of rows read, and more.

Back to top

Usage notes

This tool only captures metrics for SQL stored procedures. Data is not captured for any other kind of stored procedure (in other words, for Java stored procedures). However, in the case of nested stored procedure calls, any SQL stored procedures in the call stack have metrics captured.

Note that not all statements may have profiling data captured for it. There are certain statements DB2 does not capture profiling data for.

The tool does not determine whether profiling data will be captured at all, so it is possible that no profiling data is displayed after the stored procedure call finishes execution.

The list below summarizes the task flow of the tool, which is explained in detail in the sections that follow:

1. Launch the run profiling action on one stored procedure that is deployed on a DB2 LUW, Version 8.2 or later server, from either the Data Project Explorer or Database Explorer views.
2. Select optional event monitors to include in the report.
3. Select the stored procedures, if any, to include in the report.
4. View the report in the Profiling Data tab of the Data Output view.
5. Optionally, print the report, save it to a text file, or both.

Back to top

Launching the SQL Procedure Profiling tool

You can launch the SQL Procedure Profiling tool in two ways:

• From the context menu of one deployed stored procedure in the Data Development Project, by launching the Run Profiling action
• From the context menu of one stored procedure in the Database Explorer, by launching the Run Profiling action

Note that since profiling data can be captured for nested stored procedure calls, even non-SQL stored procedures have the Run Profiling action active. For example, you can launch the Run Profiling action on a Java stored procedure, which in turn calls an SQL stored procedure. If possible, profiling data will be captured for the called SQL stored procedure (but not for the Java stored procedure).

Figure 1 shows the Run Profiling action being launched from a stored procedure in the Database Explorer view:

Specifying optional event monitors

After launching the Run Profiling action, a dialog that allows you to specify event monitors opens. There are 15 event monitors you can select from. You can select multiple monitors from the list in the same way you select multiple elements from a list in your operating system. For example, on the Windows platform, you can select multiple elements by holding the Ctrl key while left-clicking on the desired elements.

Figure 2 shows the optional event monitor selection dialog:

The event monitor names correspond to the event monitors as defined in the DB2 System Monitor Guide and Reference.

The following list provides a brief description for the selectable event monitors (for more details, refer to the System Monitor Guide and Reference):

• TOTAL_SORT_TIME: The total elapsed time (in milliseconds) for all sorts that have been executed.
• TOTAL_SORTS: The total number of sorts that have been executed.
• FETCH_COUNT: For the stmt snapshot monitoring level and the statement event type: the number of successful fetches performed on a specific cursor.
• ROWS_READ: The number of rows read from the table.
• ROWS_WRITTEN: The number of rows changed (inserted, deleted or updated) in the table.
• INT_ROWS_DELETED: The number of rows deleted from the database as a result of internal activity.
• INT_ROWS_INSERTED: The number of rows inserted into the database as a result of internal activity caused by triggers.
• INT_ROWS_UPDATED: The number of rows updated from the database as a result of internal activity.
• POOL_DATA_L_READS: The number of logical read requests for data pages that have gone through the buffer pool.
• POOL_DATA_P_READS: The number of read requests that required I/O to get data pages into the buffer pool.
• POOL_INDEX_L_READS: The number of logical read requests for index pages that have gone through the buffer pool.
• POOL_INDEX_P_READS: The number of physical read requests to get index pages into the buffer pool.
• POOL_TEMP_DATA_L_READS: The number of data pages that have been requested from the buffer pool (logical) for temporary table spaces.
• POOL_TEMP_DATA_P_READS: The number of data pages read from the table space containers (physical) for temporary table spaces.
• POOL_TEMP_INDEX_L_READS: The number of index pages that have been requested from the buffer pool (logical) for temporary table spaces.

After you select the event monitors and click on OK, a dialog appears that allows you to specify parameter values, if the stored procedure has any input parameters. If there are no input parameters, the stored procedure proceeds to be called so that profiling data can be captured.

Selecting the stored procedures to include in the report

After the stored procedure is executed, there can be two outcomes:

• No profiling data was captured for the stored procedure call because either there were no SQL stored procedures in the call stack, or because there were no statements within any stored procedure that data could be captured for. In this case, you are presented with a dialog stating this situation, as shown in Figure 3.
  
  Figure 3. No profiling data captured dialog
  
  
  
• Profiling data was captured for the stored procedure call. The user is presented with a dialog to select what stored procedures should be included in the report.

If there was data captured, you are presented with a dialog that allows selection of the procedures to include in the report. You can filter on a particular schema or select procedures from all schemas. Each procedure in the list has a number next to it, which specifies the number of parameters that procedure has. Figure 4 shows the report procedure selection dialog:

In the case of non-nested stored procedure calls, there is only one entry in the procedures list. For nested calls, there are additional entries for each SQL stored procedures that had profiling data captured.

Select the procedures you want to include in the report, and click OK to close the dialog and to trigger the collection of the profiling data for the report.

Back to top

Viewing captured profiling data

If there was any profiling data captured, you can view it in the Data Output view, after the stored procedure finishes executing. You can view the output in the Profiling Data tab. Regardless of what optional event monitors you select for the report, the following columns are always present in the report:

• ROWNUM: The row number of the report.
• ROUTINESCHEMA: The schema of the stored procedure at that particular row.
• SPECIFICNAME: The specific name of the stored procedure at that particular row.
• LINE: The line number of the stored procedure at that particular row.
• NUM_ITERATION: The number of times that statement at that particular row was called.
• ELAPSED TIME: The elapsed time spent running the statement (in seconds). For a cursor, this is the elapsed time between open and close. Also note that the sum of all elapsed times within a routine is not equal to the elapsed time of the calls invoking the routine. The reason for this mostly lies in the fact that the event monitoring itself has a significant overhead. However, elapsed time is a good indicator of relative performance between statements and routines.
• CPU (microseconds): The total CPU time (in microseconds) consumed to execute an SQL statement. The higher the value, the more CPU-intensive the statement is. CPU time comprises user as well as system CPU. Unlike elapsed time, in case of a declare cursor statement the CPU time is the sum of open, all fetched, and closed. So it is a much better indicator, along with other fields, of the cost of a cursor.
• TEXT: The text of the stored procedure at that particular line.

In general, column values of -1 or -1.0 indicate that the field is not applicable.

You may find it helpful to expand the Data Output view and stretch out the columns for better viewing. To expand the Data Output view, double-click on the Data Output view tab. To expand the columns, select and drag the column dividers in the Profiling Data table. Figure 5 shows the Data Output view Profiling Data tab:

You can also save the report to a file or print it. To do this, right-click anywhere in the table body. Select Save As to specify a file to write the report to, or select Print to send the output to a printer. The Save As action produces a tabular formatted report, in plain text, within a directory and file that you specify.

Back to top

Conclusion

Using the SQL Stored Procedure Profiling feature of Developer Workbench, you can gather information on the execution of SQL stored procedures, whether they are called directly or nested within other stored procedure calls. The data gathered can include metrics such as the number of times a particular statement has been executed, how much time has elapsed for a particular statement, and more. These metrics can help you identify bottlenecks within your stored procedure call stack, so that you can optimize your stored procedures accordingly.

Resources

Learn

• "DB2 Developer Workbench, Part 1: Developer Workbench concepts and basic tasks" (developerWorks, August 2006): Discover the basics of Eclipse and DWB.
  
  
• "Profiling SQL procedures" (developerWorks, Jun 2004): Get an introduction to the SQL PL Profiler tool, a Java-based GUI tool that discovers the SQL procedures invoked by an application in flight.
  
  
• developerWorks Information Management zone: Learn more about Information Management. Find technical documentation, how-to articles, education, downloads, product information, and more.
  
  
• Stay current with developerWorks technical events and webcasts.
  
  
• Technology bookstore: Browse for books on these and other technical topics.
  
  

Get products and technologies

• DB2 9 Developer Workbench: Available as a free download.
  
  
• Build your next development project with IBM trial software, available for download directly from developerWorks.
  
  

Discuss

• Participate in the discussion forum.
  
  
• Participate in developerWorks blogs and get involved in the developerWorks community.
  
  

About the author

Rate this article

Comments

Back to top

Table of contents

• Introduction
• Usage notes
• Launching the SQL Procedure Profiling tool
• Viewing captured profiling data
• Conclusion
• Resources
• About the author
• Comments

Next steps from IBM

• Try: DB2 Enterprise
• Demo: Create an electronic form solution with DB2 pureXML and Lotus Forms
• Community: DB2
• Buy: DB2 Enterprise

Dig deeper into Information Management on developerWorks

Try IBM PureSystems. No charge.

Experience today!

---

Get started with the cloud-based trial and pattern development kit.

Special offers