Running stored procedures to capture tuning data

You can run a stored procedure with profiling to discover SQL procedures and nested procedures that are being called and to profile the procedures.

When you capture tuning data for SQL procedures, the collected data is presented next to the source code for each procedure. Application developers or database administrators can use this data to more efficiently tune resource-consuming statements or algorithms.

Restriction:

SQL procedure profiling is only supported for SQL procedures that target DB2® Universal Database™ for Linux®, UNIX®, and Windows®, Version 8.2 or higher.

When you capture tuning data for SQL procedures, events are generated for DML statements, such as INSERT, SELECT, DELETE, and UPDATE, that are issued in the procedure. However, procedural statements, such as variable assignments and control structures, do not generate events in a deterministic fashion. For example, the control structures are WHILE or IF.

Tuning data is captured only for certain kinds of SQL statements and not for non-SQL procedures. However, if a nested SQL procedure is called, data is captured for that procedure.

If no SQL procedure profiling data is captured, no report is generated.

To run a stored procedure with SQL procedure profiling:

  1. Optional: Change the run settings. For example, you can specify SQL statements that you want to run before and after you run the routine.
  2. In either the Data Source Explorer or the Data Project Explorer, right-click a SQL procedure, and click Run Profiling.
  3. In the window that opens, specify monitor element options and click OK. The routine runs on the database server. If the routine has parameters, a window opens so that you can specify parameter values. After the procedure runs, a window opens so that you can select the SQL procedures to include in the profiling report.
The profiling data is displayed on the Profiling Data page in the SQL Results view.

Feedback