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,
- Enabling capture for the statements of interest
- Invoking the statements (each statement is rolled back after being
executed to prevent side effects in the database)
- 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
- 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.
- 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
Command syntax
>>-db2caem--+--------------------+------------------------------>
'- -d--database-name-'
>--+----------------------------+------------------------------->
'- -u--userid-- -p--password-'
>--+-| query-statement-options |-+--+------------------+-------->
'-| event-monitor-options |---' '- -o--output-path-'
>--+-----+-----------------------------------------------------><
'- -h-'
query-statement-options
|--+----------------------------+------------------------------->
+- -st--query-statement------+
'- -sf--query-statement-file-'
>--+-----------------------------------------+------------------>
'- -compenv--compilation-environment-file-'
>--+------------------------------+----------------------------->
'- -tbspname--table-space-name-'
>--+-------------------------------------+----------------------|
'- -terminator--termination-character-'
event-monitor-options
|--+----------------------------------------------------------------------------------------------+--|
'- -actevm--event-monitor-name-- -appid--application-id-- -uowid--uow-id-- -actid--activity-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.
- -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.
- -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 =?