Strobe for Db2 overview

The Strobe Application Performance Measurement System is a product that determines where and how application time is spent in online regions and batch processing programs and how system resources are used. Strobe collects several types of data as it tracks activity within an MVS environment and produces a collection of reports that helps you determine where to revise applications to improve their performance.

Strobe for Db2 extends the functions of Strobe. With Strobe for Db2, you can measure and evaluate the performance of applications that use IBM DATABASE 2 (Db2). Strobe for Db2 identifies database request modules (DBRMs) and SQL statements within your application that are most active within your Db2 system so you can detect opportunities to improve efficiency.

You can further extend the function of Strobe for Db2 with iStrobe a separate BMC product. SQL Analysis gives statement-specific performance suggestions for the SQL statements in Db2 reports. Stored Procedure and Trigger SQL reports for SQL not executed in the measured address space are available in iStrobe. Additionally, Db2 Run Time Statistics are available in all of the iStrobe SQL Activity reports. An iStrobe SQL Summary report containing SQL accounting data is also available.

This section describes the characteristics of Strobe for Db2 and its benefits.

Important

To measure the Distributed Data Facility (DDF) address space, for Db2 DDF, which is a separate BMC option, must be installed. For information about how to use for Db2 DDF and interpreting the DDF reports, see Strobe-for-Db2-DDF.

Strobe and the Db2 operating environments

Strobe for Db2 gathers measurement data in all the environments in which Db2 functions. It gathers data for:

The allied address spaces, which consist of the batch processing programs and CICS, IMS, and TSO address spaces that use Db2 services. Db2 Allied Address Spaces shows the relationship between the allied address spaces and the Db2 subsystem.
The Db2 database and system services address spaces as well as the stored procedures address space.

When you measure an application that uses Db2 in a CICS, IMS, or TSO environment, the resulting Strobe Performance Profile might show significant levels of activity in Db2 system modules. Strobe for Db2 attributes activity in Db2 system modules to the SQL statements within the application programs that caused the activity.

Db2 Allied Address Spaces

Db2 allied address spaces

When you measure the allied address space, Strobe monitors any application threads that make calls to Db2 database tables. If heavy CPU usage is attributed to these calls, Strobe reports this activity in program usage reports, and you will see the largest CPU usage values for the Db2 system service modules.

Batch jobs or online regions that access Db2 databases often generate significant amounts of CPU activity in Db2 system modules. The key to improving the performance of such applications is identifying the SQL statements that invoke the Db2 services. A prime consideration is whether the SQL statements are static or dynamic.

To prepare static statements, you invoke the Db2 precompiler to process your application program. The precompiler checks the syntax of your SQL statements and replaces them with the code necessary to invoke Db2. If the syntax is correct, the precompiler copies the SQL into a DBRM, which Db2 later binds into a plan. After you issue the Db2 command to bind the plan, Db2 determines the access path. The application program can then use the plan without further binding. For an illustration of the plan preparation process, see Application Program/DBRM Preparation.

Dynamic SQL applications also use a DBRM, but the SQL statements in the DBRM (for example, PREPARE and EXECUTE) do not directly access data. The data-accessing SQL statements (for example, SELECT, UPDATE) are created by the application program and sent to Db2 for processing via EXECUTE IMMEDIATE or the PREPARE and EXECUTE statements in the DBRM.

Application Program/DBRM Preparation

SQL Processor Using File Input (SPUFI) is a special type of dynamic SQL applications which uses a Db2 DBRM to process the user-entered SQL statements. With SPUFI, the user can specify a series of SQL statements that are contained in a single file.

Strobe for Db2 differentiates between the user-entered (target) statements and the system-generated (executing) statements in the Strobe Performance Profile, and displays both types of statements in the CPU Usage by SQL Statement reports.

The Strobe CPU Usage by SQL Statement reports show CPU execution as occurring in the SQL statements that are part of a DBRM. For dynamic SQL statements, Strobe identifies both the target and the executing SQL statement. The target statements contain the most useful information for the application developer, because those are the ones that can be modified.

Other Db2 information

Strobe for Db2 also:

Shows which Db2 service modules were active during the measurement session
Shows the SQL statement that invoked these routines
Provides function descriptors for the Db2 modules
Includes automatic control section identification for the Db2 batch processing application running under the TSO control program for TSO address spaces and batch processing programs.

Db2 services address spaces

You can measure the performance of the following types of Db2 address spaces:

Stored procedures address spaces are now WLM managed (WLM-SPAS)
Db2 Distributed Data Facility (DDF) address space (DIST), if Strobe for Db2 DDF (which is a separate option) is also installed.

However, because the Db2 system services modules are executed in cross-memory mode by the allied address spaces, most of the Db2 activity is shown in the Performance Profile for the allied address space.

Database services address space

Measuring the DBM1 provides little help in analyzing your applications and is therefore not supported.

System services address space

Measuring the MSTR provides little help in analyzing your applications and is therefore not supported.

Stored procedures address space

External stored procedures execute in WLM managed address spaces. There are multiple WLM managed address spaces for a given Db2 subsystem. When a given WLM managed address space is measured, the Performance Profile shows SQL and CPU used only for the stored procedures directed to that WLM.

Tip

will show SQL and CPU for stored procedures and triggers in Performance Profiles from any measured allied or DDF address space.

Distributed Data Facility address space

The DDF reports show CPU and execution time usage by SQL statements from remote requesters in the DDF address space.

Benefits of Strobe for Db2

The data provided by Strobe for Db2 will help you throughout the application life cycle:

In design, to evaluate the performance of prototypes
In development, to streamline Db2 programs so that database calls are coded efficiently
In production and quality assurance, to ensure efficient performance of Db2 programs and services, optimizing the use of existing hardware resources
In maintenance, to assess the impact of changes in business requirements and data volume on the performance of Db2 applications.