db2caem - Capture activity event monitor data tool command
The db2caem tool automates the procedure of creating an activity event monitor.
- 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.
- 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).
- To create activity event monitor, the privileges must include
one of the following authorities:
- DBADM authority
- SQLADM authority
- WLMADM authority
- 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
- DATAACCESS authority on the activity event monitor tables
- CONTROL or SELECT privilege on the activity event monitor tables
- -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.
- Displays help information. When this option is specified, all other options are ignored, and only the help information is displayed.
- -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.
- -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.
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.
The db2caem tool
is used to create the activity event monitor for capturing data which
can be collected with the db2support command.
directory will be generated to contain all the information
captured by the db2caem tool.
SELECT * FROM syscat.tables WHERE tabname=? and tbspaceid =?