DB2 Execution Plan specific actions

Explain

1. If first execution, please connect to dbname (under Dbinst user),
Perform DB2-TVF $HOME/sqllib/misc/explain. DDL Build Execution Plan table


2.DB2 Set Current explain mode explain (under the user of the database)
is set to interpreted mode and does not actually execute the SQL command that will be issued below


3. Execute the SQL statement you want to parse
......
DB2 "SELECT DISTINCT RTRIM (market_code), RTRIM (Currency_type), t.* from KS. Branch_param t WHERE param_code= ' 0986 ' "
DB2 "Select A.name, A.cert_type, A.cert_no, A.cust_no,a.open_date,a.branch_code, a.* from KS. Cust_base_info A where a.cert_no= ' 0050000101 ' "
......


4.DB2 set Current explain mode no
Cancel Interpretation mode
5.db2exfmt-d ksdbs-g tic-w-l-s%-n%-O db2exmt.out

Execution plan output to file Db2exmt.out

EXPLN:

Single table
db2expln-d <database>-C <schema>-P <package>-O <explan out file>-S 0-G


Full library:
DB2 Connect to Database
Db2-x "SELECT ' db2expln-d <database>-C ' | | RTrim (LTrim (pkgschema)) | | '-P ' | | RTrim (LTrim (pkgname)) | | '-o ' | | RTrim (LTrim (pkgschema)) | | RTrim (LTrim (pkgname)) | | '. Out-s 0-g ' from syscat.packages where Pkgschema <> ' nullid ' with ur ' > exp.sh
chmod +x exp.sh
./exp.sh

Note:<database> is replaced with a database name such as: Ksdbs, it is best to create a new folder when the whole library executes, and many files are generated.

Description of the DB2EXPLN output

Http://www.ibm.com/support/knowledgecenter/zh/SSEPGG_10.5.0/com.ibm.db2.luw.admin.perf.doc/doc/c0005739.html

Document, performance tuning, query optimization, description tool->sql and XQuery description tool->DB2EXPLN Output description

DB2 Execution Plan specific actions