db2caem - Capture activity event monitor data tool command

The db2caem tool automates the procedure of creating an activity event monitor.

Run the db2caem command to create the activity event monitor to capture data for an SQL statement. This data can be collected with the db2support command. The information collected and generated by the db2caem tool includes:
  • Detailed activity information captured by an activity event monitor including monitor metrics, for example total_cpu_time for statement execution
  • Formatted EXPLAIN output, including section actuals (statistics for different operators in the access plan).

The db2caem tool uses an activity event monitor to capture information about the statements and then extracts and formats the information.

The db2caem tool automates the process for creating an activity event monitor,
  1. Enabling capture for the statements of interest
  2. Invoking the statements (each statement is rolled back after being executed to prevent side effects in the database)
  3. Formatting the output information (including exporting activity information for the statements of interest and generation of formatted explain output from the captured section and section actuals).

Authorization

  1. To create activity event monitor, the privileges must include one of the following authorities:
    • DBADM authority
    • SQLADM authority
    • WLMADM authority
    and also EXECUTE privilege on the WLM_SET_CONN_ENV procedure.
  2. If there is not a need to create activity event monitor, the following privileges and authority are required:
    • EXECUTE privilege on the EXPLAIN_FROM_ACTIVITY procedure
    • INSERT privilege on the Explain tables in the specified schema
    • SELECT privilege on the event monitor tables for the source activity event monitor
    and also one of the following authorities:
    • DATAACCESS authority on the activity event monitor tables
    • CONTROL or SELECT privilege on the activity event monitor tables

Required connection

None

Command syntax

Read syntax diagramSkip visual syntax diagramdb2caem-ddatabase-name-uuserid-ppasswordquery-statement-optionsevent-monitor-options-ooutput-path-tn-h
query-statement-options
Read syntax diagramSkip visual syntax diagram -stquery-statement-sfquery-statement-file -cs-fp-compenvcompilation-environment-file -tbspnametable-space-name-terminatortermination-character
event-monitor-options
Read syntax diagramSkip visual syntax diagram-actevmevent-monitor-name-appidapplication-id-uowiduow-id-actidactivity-id

Command parameters

-d database-name
Specifies the name of the database to be connected to.
-u userid
Specifies the user ID when connecting to the database.
-p password
Specifies the password for the user ID when connecting to the database.
-o output-path
The output files of db2caem will be written to the path that you specify.
tn tenant_name
Specifies the name of the tenant. Note: This parameter is available starting from 11.5.6.
-h
Displays help information. When this option is specified, all other options are ignored, and only the help information is displayed.

query-statement-options

-st query-statement
Specifies the SQL statement for which activity event monitor data is to be captured.
Note: The SQL statement will be executed against the specified database by the tool.
-sf query-statement-file
Specifies the file path containing the SQL statement for which activity event monitor data is being captured. Use the -terminator option to specify the character that marks the end of the SQL statement.
Note: The SQL statement will be executed against the specified database by the tool.
cs current_schema
Specifies the current schema name value. Note: This parameter is available starting from 11.5.6.
fp function_path
Specifies the function path special register value. Note: This parameter is available starting from 11.5.6.
-compenv compilation-environment-file
Specifies that the compilation environment will be used when the SQL statement is executed. The compilation environment (comp_env_desc) is in BLOB data type and specified through a file as an optional input. If the option is not provided, the default compilation environment will be used when executing the SQL statement.
-tbspname table-space-name
Specifies the table space name for which the activity event monitor will be created in. For a partitioned database environment, the table space should exist on all the database partitions where the SQL statement of interest will be run. If the option is not provided, the default table space will be used when there is a need to create the activity event monitor.
-terminator termination-character
Specifies the character that indicates the end of the SQL statement in the –sf SQL file if there are multiple statements in the file. The default is a semicolon.

event-monitor-options

The following options uniquely identify an SQL statement that has already been captured by an existing activity event monitor. They are specified together to identify the statement for which activity data and explain output should be extracted.
Note: Formatted explain output will only be gathered if the section for the statement was captured, and the formatted explain output will only include section actuals if section actuals had been captured for the statement.
-actevm event-monitor-name
Specifies the name of the existing activities event monitor containing the data for the statement of interest.
-appid application-id
Specifies the application identifier (appl_id monitor element) uniquely identifying the application that issued the statement of interest.
-uowid uow-id
Specifies the unit of work ID (uow_id monitor element) in which the statement of interest was executed.
-actid activity-id
Specifies the activity ID (activity_id monitor element) of the statement of interest.

Examples

The following examples show how you can use the db2caem tool to create the activity event monitor to capture data for an SQL statement:
  • db2caem –d sample –st "select * from staff"

    Creates the activity event monitor and capture information of details, section and values, as well as actuals for the SQL statement "select * from staff".

  • db2caem –d sample –sf badquery.sql -terminator $

    Creates the activity event monitor and capture information of details, section and values, as well as actuals for the SQL statement specified in the file badquery.sql.

  • db2caem –d sample -actevm mymon –appid *LOCAL.mikita.100203234904 -uowid 44 -actid 1

    Captures the activity event monitor information of details, section and values, as well as actuals for the SQL statement identified by the event monitor options from the existing activity event monitor. The db2caem tool will not create activity event monitor in this example.

Usage notes

The db2caem tool is used to create the activity event monitor for capturing data which can be collected with the db2support command. DB2CAEM_<timestamp> directory will be generated to contain all the information captured by the db2caem tool.

The db2caem tool does not support parameter markers in SQL statements. For example, the following query statement cannot be used with the db2caem tool. 
SELECT * FROM syscat.tables WHERE tabname=? and tbspaceid =?