Skip to content

Latest commit

 

History

History
45 lines (33 loc) · 1.35 KB

README.md

File metadata and controls

45 lines (33 loc) · 1.35 KB
Raw

db2_explain_tools

A set of tools for generating bulk explain statements in a DB2 database

The db2procEplnAct.sh script

A script used to generate explain from activity statements for all statements executed by a stored procedure call including sestion actuals by using a session enabled event monitor for activities (minimal possible impact for performance for this type of explain plan generation)

Command syntax

db2procEplnAct.sh <db-name> <mode:base|actuals> "<SP call SQL>"

Where

  • db-name is the database name where the SP call will be execued and explain data will be collected
  • mode:base|actuals is one of the keywords 'base' or 'actuals'
    • base basic explains are collected without including section actuals
    • actuals full exlain data including section actuals will be collected
  • SP call - is the SQL call statement to be executed

Use Cases

If the database name is SAMPLE

  • Setup

This will create an event monitor for activities and one for package cache and a helpper stored procedure in the database you will use the script on.

db2procEplnAct.sh SAMPLE _setup_
  • Execute a stored procedure call and collect explain plans for its statements
db2procEplnAct.sh SAMPLE base "call my_proc('param1','param2',?)"

The command will create a .tgz archive with a name pattern:

db2exfmt_<db-name>_<current-timestamp>.tgz