Shulou(Shulou.com)05/31 Report--

This article introduces you how to analyze the DB2 implementation plan, the content is very detailed, interested friends can use for reference, I hope it can be helpful to you.

Analyzing an execution plan that runs SQL for a long time, it is suspected that the estimated value of the optimizer differs greatly from the actual value, resulting in the inaccuracy of the execution plan, so the following method is used to output both the actual value and the estimated value in the execution plan.

# modify parameters at DB level

Db2 update db cfg for SAMPLE using SECTION_ACTUALS BASE

# Connect to the database

Db2 connect to SAMPLE

# create an EXPLAIN table. If you already have one, ignore this step.

Db2 "call sysproc.sysinstallobjects ('EXPLAIN','C',null,null)"

# create an activity event monitor

Db2 "

Create event monitor actEvmon for activities write to table

Activity (table activity, in USERSPACE1)

Activityvals (table activityvals, in USERSPACE1)

Activitystmt (table activitystmt, in USERSPACE1)

Activitymetrics (table activitymetrics, in USERSPACE1)

Control (table control, in USERSPACE1)

Manualstart "

Db2 "set event monitor actEvmon state 1"

# View the current connection

Db2 "values sysproc.mon_get_application_id ()"

one

-

* LOCAL.db2inst1.200403101703

1 record (s) selected.

Db2 "values sysproc.mon_get_application_handle ()"

one

-

10372

1 record (s) selected.

# Turn on the collection of activity data for this specific application-handle

Db2 "CALL WLM_SET_CONN_ENV (10372, 'WITH DETAILS, SECTION AND VALUES ALL')"

# execute SQL

Db2-tvf s1.sql > s1.out

Db2 flush event monitor actEvmon buffer

# View the UOW_ID and ACTIVITY_ID of the executed SQL

Db2 "select a.APPL_ID, a.ACTIVITY_ID, a.UOW_ID, a.ACT_EXEC_TIME, a.TIME_CREATED, SUBSTR (b.STMT_TEXT, 1,20) as STMT_TEXT

From activity a, activitystmt b

Where a.APPL_ID=b.APPL_ID and a.ACTIVITY_ID=b.ACTIVITY_ID and a.UOW_ID=b.UOW_ID

AND a.APPL_ID like'% db2inst1.200403101703%'

Order by ACT_EXEC_TIME desc "

APPL_ID ACTIVITY_ID UOW_ID ACT_EXEC_TIME TIME_CREATED STMT_TEXT

-

* LOCAL.db2inst1.200403101703 18 1797313075 2020-04-03-05.18.33.868968 with temp as (selec

* LOCAL.db2inst1.200403101703 1 9 1549 2020-04-03-05.48.52.396869 flush event monitor

* LOCAL.db2inst1.200403101703 17 50 2020-04-03-05.18.33.860221 select current CLIEN

3 record (s) selected. # call EXPLAIN_FROM_ACTIVITY

Db2 "CALL EXPLAIN_FROM_ACTIVITY ('* LOCAL.db2inst1.200403101703', 8,1, 'ACTEVMON',' DB2INST1',?,?)"

# generate execution plan

Db2exfmt-1-d SAMPLE-o explain.out

# disable monitoring

Db2 "CALL WLM_SET_CONN_ENV (10372, 'NONE')"

Db2 "set event monitor actEvmon state 0"

On how to analyze the DB2 implementation plan to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.