logo

Vinchin Blog

Vinchin Blog Use db2top and db2advis to generate index

Use db2top and db2advis to generate index

2021-07-13

There is a tool called db2top in unix/linux environment, but not in windows

Operating environment

AIX 7.1 + DB2 9.7.0.6


1. Create the .db2toprc file in the $HOME directory and add the following content, otherwise an alarm will be issued when db2top is running

$ cat ~/.db2toprc

streamsize=2000M


2. Run db2top to collect data with an interval of 15 seconds and a duration of 20 minutes to generate a trace file

db2top -d mydb -C -i 15 -m 20


3. Use db2top to analyze the trace file

db2top -d mydb -f db2snap-mydb-AIX64.bin -b l -A -L


4. Create explain table under myuser

cd /home/MYUSER/sqllib/misc

db2 connect to mydb user myuser using mypass

db2 -tf EXPLAIN.DDL


5. Use db2advis to analyze SQL files and generate index suggestions

db2advis -d mydb -i ALL.sql -noxml -user myuser/mypass -schema myuser


Operation record of steps 2-5:


Step 2

$ db2top -d mydb -C -i 15 -m 20

[17:16:33] Starting DB2 snapshot data collector, collection every 15 second(s), max duration 20 minute(s), max file growth/hour 100.0M, hit to cancel...

[17:16:33] Writing to'db2snap-mydb-AIX64.bin', should I create a named pipe instead of a file [N/y]? n

[17:16:38] Creating'db2snap-mydb-AIX64.bin' as a normal file

[17:16:53] 1.7M written, time 20.283, 315.4M/hour

[17:18:09] 3.5M written, time 95.585, 133.4M/hour

[17:19:09] 5.2M written, time 155.821, 122.7M/hour

[17:20:09] 11.6M written, time 216.337, 193.9M/hour

[17:23:55] 13.4M written, time 442.204, 109.7M/hour

[17:25:10] 15.3M written, time 517.495, 106.6M/hour

[17:26:11] 17.1M written, time 577.729, 106.9M/hour

[17:28:26] 20.8M written, time 713.314, 105.2M/hour

[17:30:42] 24.5M written, time 848.855, 104.1M/hour

[17:32:57] 28.2M written, time 984.388, 103.2M/hour

[17:34:58] 31.8M written, time 1104.858, 103.9M/hour

[17:36:43] Max duration reached, 33.7M bytes, time was 1210.271...

[17:36:43] Snapshot data collection stored in'db2snap-mydb-AIX64.bin'

Exiting...


Step 3

$ db2top -d mydb -f db2snap-mydb-AIX64.bin -b l -A -L

Analyzing objects doing the most 'Cpu%_Total' in function 'Sessions'

*** End of input stream reached, size was 35381589...

--
-- Top twenty performance report for 'Sessions' between 17:18:54 and 17:35:58
-- Sort criteria 'Cpu%_Total'
--

 Rank Application_Handle(Stat)        Percentage fromTime toTime                   sum(Cpu%_Total)
----- ------------------------------ ----------- -------- --------- ------------------------------
    1 2998                              26.8995% 17:18:54 17:35:58                          107114
    2 2010                              25.1130% 17:18:54 17:35:58                          100000
    3 64360                             24.8757% 17:18:54 17:35:58                           99055
    4 3420                              23.1118% 17:24:55 17:35:58                           92031
    5 64784                              0.0000% 17:18:54 17:35:58                               0
    6 64629                              0.0000% 17:18:54 17:35:58                               0
    7 60282                              0.0000% 17:18:54 17:35:58                               0
    8 60281                              0.0000% 17:18:54 17:35:58                               0
    9 60225                              0.0000% 17:18:54 17:35:58                               0
   10 60230                              0.0000% 17:18:54 17:35:58                               0
   11 3399                               0.0000% 17:33:43 17:35:58                               0
   12 2687                               0.0000% 17:18:54 17:35:58                               0
   13 2895                               0.0000% 17:18:54 17:35:58                               0
   14 3449                               0.0000% 17:30:27 17:35:58                               0
   15 1296                               0.0000% 17:18:54 17:35:58                               0
   16 2847                               0.0000% 17:18:54 17:35:58                               0
   17 3058                               0.0000% 17:18:54 17:35:58                               0
   18 2864                               0.0000% 17:18:54 17:35:58                               0
   19 1916                               0.0000% 17:18:54 17:35:58                               0
   20 3392                               0.0000% 17:19:54 17:35:58                               0

--                                                    
-- Performance report, breakdown by 300 seconds       
--                                                    

fromTime                sum(Cpu%_Total) Percentage       Top Five in 300 seconds interval
-------- ------------------------------ ----------      +----------------------------------------------+
17:18:54                          99100   24.8870%      |Rank|Percentage|Application_Handle(Stat)      |
                                      -          -      |   1|  99.9546%|64360                         |
                                      -          -      |   2|   0.0454%|2998                          |
                                      -          -      |   3|   0.0000%|64784                         |
                                      -          -      |   4|   0.0000%|64629                         |
                                      -          -      |   5|   0.0000%|60282                         |
17:24:55                         100000   25.1130%      +----+----------+------------------------------+
                                      -          -      |   1| 100.0000%|2010                          |
                                      -          -      |   2|   0.0000%|64784                         |
                                      -          -      |   3|   0.0000%|64629                         |
                                      -          -      |   4|   0.0000%|60282                         |
                                      -          -      |   5|   0.0000%|60281                         |
17:30:27                         199100   50.0000%      +----+----------+------------------------------+
                                      -          -      |   1|  53.7765%|2998                          |
                                      -          -      |   2|  46.2235%|3420                          |
                                      -          -      |   3|   0.0000%|64784                         |
                                      -          -      |   4|   0.0000%|64629                         |
                                      -          -      |   5|   0.0000%|60282                         |
17:35:58                              0    0.0000%      +----+----------+------------------------------+
                                      -          -      |   1|   0.0000%|64784                         |
                                      -          -      |   2|   0.0000%|64629                         |
                                      -          -      |   3|   0.0000%|60282                         |
                                      -          -      |   4|   0.0000%|60281                         |
                                      -          -      |   5|   0.0000%|60225                         |
                                                        +----------------------------------------------+
--                                                    
-- Performance report, breakdown by 0.5 hour          
--                                                    

fromTime                sum(Cpu%_Total) Percentage       Top Five in 0.5 hour interval
-------- ------------------------------ ----------      +----------------------------------------------+
17:18:54                         398200  100.0000%      |Rank|Percentage|Application_Handle(Stat)      |
                                      -          -      |   1|  26.8995%|2998                          |
                                      -          -      |   2|  25.1130%|2010                          |
                                      -          -      |   3|  24.8757%|64360                         |
                                      -          -      |   4|  23.1118%|3420                          |
                                      -          -      |   5|   0.0000%|64784                         |
                                                        +----------------------------------------------+
Exiting...



Step 4
$ db2 connect to mydb user myuser using mypass

   Database Connection Information

 Database server        = DB2/AIX64 9.7.6
 SQL authorization ID   = MYUSER
 Local database alias   = MYDB
 
$ db2 -tf  EXPLAIN.DDL

******* IMPORTANT **********

USAGE: db2 -tf EXPLAIN.DDL 

******* IMPORTANT **********


DB20000I  The UPDATE COMMAND OPTIONS command completed successfully.

DB20000I  The SQL command completed successfully.

DB20000I  The SQL command completed successfully.
...

Step 5
$  cat ALL.sql
select * from ( select rownumber() over(order by job0_.DUEDATE_ asc) as rownumber_,
job0_.ID_ as ID1_111_, job0_.VERSION_ as VERSION3_111_, job0_.DUEDATE_ as DUEDATE4_111_,
job0_.PROCESSINSTANCE_ as PROCESSI5_111_, job0_.TOKEN_ as TOKEN6_111_,
job0_.TASKINSTANCE_ as TASKINST7_111_, job0_.ISSUSPENDED_ as ISSUSPEN8_111_,
job0_.ISEXCLUSIVE_ as ISEXCLUS9_111_, job0_.LOCKOWNER_ as LOCKOWNER10_111_,
job0_.LOCKTIME_ as LOCKTIME11_111_, job0_.EXCEPTION_ as EXCEPTION12_111_,
job0_.RETRIES_ as RETRIES13_111_, job0_.NAME_ as NAME14_111_, job0_.REPEAT_ as REPEAT15_111_,
job0_.TRANSITIONNAME_ as TRANSIT16_111_, job0_.ACTION_ as ACTION17_111_,
job0_.GRAPHELEMENTTYPE_ as GRAPHEL18_111_, job0_.GRAPHELEMENT_ as GRAPHEL19_111_, job0_.NODE_ as NODE20_111_,
job0_.SMS_ as SMS21_111_, job0_.CLASS_ as CLASS2_111_ from MYUSER.JBPM_JOB job0_
where (job0_.LOCKOWNER_ is null or job0_.LOCKOWNER_='x') and job0_.RETRIES_>0 and job0_.ISSUSPENDED_<>1
order by job0_.DUEDATE_ asc ) as temp_ where rownumber_ <= 9                                               
;

$ db2advis -d mydb -i ALL.sql -noxml -user db2inst1/db2inst1  -schema shoaex

execution started at timestamp 2013-05-14-17.16.37.209276
found [1] SQL statements from the input file
found [1] SQL statements from the input file
Recommending indexes...
total disk space needed for initial set [   0.009] MB
total disk space constrained to         [   7.947] MB
Trying variations of the solution set.
  1  indexes in current solution
 [  0.0117] timerons  (without recommendations)
 [  0.0105] timerons  (with current solution)
 [10.39%] improvement

--
--
-- LIST OF RECOMMENDED INDEXES
-- ===========================
-- index[1],    0.009MB
   CREATE INDEX "MYUSER  "."IDX1305140908520" ON "MYUSER  "."JBPM_JOB"
   ("DUEDATE_" ASC, "RETRIES_" ASC, "CLASS_" ASC, "SMS_"
   ASC, "NODE_" ASC, "GRAPHELEMENT_" ASC, "GRAPHELEMENTTYPE_"
   ASC, "ACTION_" ASC, "TRANSITIONNAME_" ASC, "REPEAT_"
   ASC, "NAME_" ASC, "EXCEPTION_" ASC, "LOCKTIME_" ASC,
   "ISEXCLUSIVE_" ASC, "TASKINSTANCE_" ASC, "TOKEN_"
   ASC, "PROCESSINSTANCE_" ASC, "VERSION_" ASC, "ID_"
   ASC, "ISSUSPENDED_" ASC, "LOCKOWNER_" ASC) ALLOW REVERSE
   SCANS COLLECT SAMPLED DETAILED STATISTICS;
   COMMIT WORK ;


--
--
-- RECOMMENDED EXISTING INDEXES
-- ============================
-- RUNSTATS ON TABLE "MYUSER  "."JBPM_JOB" FOR SAMPLED DETAILED INDEX "MYUSER "."IDX_JOB_TSKINST" ;
-- COMMIT WORK ;


--
--
-- UNUSED EXISTING INDEXES
-- ============================
-- DROP INDEX "MYUSER "."IDX_JOB_PRINST";
-- DROP INDEX "MYUSER "."IDX_JOB_TOKEN";
-- ===========================
--

14 solutions were evaluated by the advisor
DB2 Workload Performance Advisor tool is finished.

  • Tag:
  • Trending

Interested Blogs More

DOWNLOAD NOW YOU CAN ENJOY A 60-DAYS FULL-FEATURED FREE TRIAL !