DB2大型数据之数据库维护、监控和问题确定

paranoiass

已于 2024-06-21 07:39:17 修改

阅读量326

点赞数 5

文章标签：数据库

于 2024-06-02 10:16:15 首次发布

本文链接：https://blog.csdn.net/weixin_61703384/article/details/139364776

版权

本实验目标：

本演示使用几个DB2工具和实用程序来改进
SQL查询的性能。您将使用Data Server Manager Visual Explain工具查看访问计划和处理SQL语句的估计成本。您将使用DB2设计顾问来建议一个新的索引，以降低处理成本。您将执行DB2 REORG实用程序来重新组织表以提高性能。

准备工作：

首先需要连接实例

Linux系统先连接LInux用户并登录，然后切换到实例所在的用户：

su - db2inst1

输入密码即可

然后换到ddl脚本所在的目录，并连接到数据库

cd /home/inst23/ddl

db2 connect to musicdb

任务1：加载一个测试表，并创建一组用于查询分析的解释表

explain.ddl

 call SYSINSTALLOBJECTS('EXPLAIN','C','USERSPACE1',NULL) ;

执行文件：

b2 -tvf explain.ddl

输出：

call SYSINSTALLOBJECTS('EXPLAIN','C','USERSPACE1',NULL)

Return Status = 0

执行call操作需要一点时间，请耐心等待，返回状态为0表示成功了。

create_testhist.ddl

CONNECT TO MUSICDB;

------------------------------------------------
-- DDL Statements for table "TEST    "."HISTORY"
------------------------------------------------
 

CREATE TABLE "TEST    "."HISTORY"  (
		  "ACCT_ID" INTEGER NOT NULL , 
		  "TELLER_ID" SMALLINT NOT NULL , 
		  "BRANCH_ID" SMALLINT NOT NULL , 
		  "BALANCE" DECIMAL(15,2) NOT NULL , 
		  "DELTA" DECIMAL(9,2) NOT NULL , 
		  "PID" INTEGER NOT NULL , 
		  "TSTMP" TIMESTAMP NOT NULL WITH DEFAULT CURRENT TIMESTAMP , 
		  "ACCTNAME" CHAR(20) NOT NULL , 
		  "TEMP" CHAR(6) NOT NULL )   
		 IN "USERSPACE1" ; 



LOAD FROM /home/inst23/ddl/histdata.ixf OF IXF REPLACE INTO TEST.HISTORY NONRECOVERABLE ;
RUNSTATS ON TABLE TEST.HISTORY ;


CONNECT RESET;

执行文件：

db2 -tvf create_testhist.ddl

输出：

CONNECT TO MUSICDB

Database Connection Information

Database server = DB2/LINUXX8664 11.5.4.0

SQL authorization ID = DB2INST1

Local database alias = MUSICDB

CREATE TABLE "TEST "."HISTORY" ( "ACCT_ID" INTEGER NOT NULL , "TELLER_ID" SMALLINT NOT NULL , "BRANCH_ID" SMALLINT NOT NULL , "BALANCE" DECIMAL(15,2) NOT NULL , "DELTA" DECIMAL(9,2) NOT NULL , "PID" INTEGER NOT NULL , "TSTMP" TIMESTAMP NOT NULL WITH DEFAULT CURRENT TIMESTAMP , "ACCTNAME" CHAR(20) NOT NULL , "TEMP" CHAR(6) NOT NULL ) IN "USERSPACE1"

DB20000I The SQL command completed successfully.

LOAD FROM /home/inst23/ddl/histdata.ixf OF IXF REPLACE INTO TEST.HISTORY NONRECOVERABLE

SQL3109N The utility is beginning to load data from file

"/home/inst23/ddl/histdata.ixf".

SQL3500W The utility is beginning the "LOAD" phase at time "06/01/2024

07:16:12.233267".

SQL3150N The H record in the PC/IXF file has product "DB2 02.00", date

"20120817", and time "154700".

SQL3050W Conversions on the data will be made between the IXF file code page

"1252" and the application code page "1208".

SQL3153N The T record in the PC/IXF file has name "histdata.del", qualifier

"", and source " ".

SQL3519W Begin Load Consistency Point. Input record count = "0".

SQL3520W Load Consistency Point was successful.

SQL3110N The utility has completed processing. "200000" rows were read from

the input file.

SQL3519W Begin Load Consistency Point. Input record count = "200000".

SQL3520W Load Consistency Point was successful.

SQL3515W The utility has finished the "LOAD" phase at time "06/01/2024

07:16:12.974190".

Number of rows read = 200000

Number of rows skipped = 0

Number of rows loaded = 200000

Number of rows rejected = 0

Number of rows deleted = 0

Number of rows committed = 200000

RUNSTATS ON TABLE TEST.HISTORY

DB20000I The RUNSTATS command completed successfully.

CONNECT RESET

DB20000I The SQL command completed successfully.

可以看到涉及的行数有20000行，表示成功了。

任务2：使用Data Server Manager的Explain SQL功能来检查访问计划并估计处理SQL语句的成本

query_history.sql

SELECT  BRANCH_ID,  TELLER_ID,  ACCT_ID,  BALANCE,  ACCTNAME
    FROM test.HISTORY
  WHERE  branch_id = 20 and teller_id between 100 and 180
   ;

在IBM的DMC上运行这个段SQL语句（实际上不需要点击执行）：

即IBM Db2 Data Management Console上连接到数据点，点击左边菜单栏的【运行SQL】，复制上述query_history.sql文件里的脚本，点击执行。

笔者这里可以出现右边的结果，第一次执行时报错：

无法生成存取方案图:请选择一个语句来进行说明。

查找原因：调优 IBM DB2 UDB SQL 存取路径_数据库_软件频道_至顶网

https://soft.zhiding.cn/software_zone/2008/0922/1143839.shtml

使用的Linux查看

ls -l /home/db2inst1/sqllib/misc

ls -l /opt/ibm/db2/V11.5/misc

sudo cat /opt/ibm/db2/V11.5/misc/EXPLAIN.DDL

这个报错没有影响。

全选SQL语句后点击上方工具栏的“说明描述”，等待片刻，注意需要开启浏览器允许弹框，会新增一个网页。如果没有开启会有对应的提示。可以看到现在return为3203.79。这是没有优化之前的。

• Estimated Cardinality： 3,202.79

• Cumulative Total Cost: 568,579,712.00

• Cumulative I/O Cost : 3,575.00

鼠标移动到根节点出现三点，点击后选择【显示节点描述】，可以看到很多的统计信息，包括Properties和Environment。

任务3：使用db2advis命令推荐可以降低SQL查询处理成本的其他表索引

query_history.sql

SELECT  BRANCH_ID,  TELLER_ID,  ACCT_ID,  BALANCE,  ACCTNAME
    FROM test.HISTORY
  WHERE  branch_id = 20 and teller_id between 100 and 180
   ;

使用db2advis命令推荐可以降低SQL查询处理成本：

db2advis -d musicdb -i /home/inst23/ddl/query_history.sql | more

输出：

Using user id as default schema name. Use -n option to specify schema

execution started at timestamp 2024-06-01-16.56.40.889133

found [1] SQL statements from the input file

Recommending indexes...

total disk space needed for initial set [ 10.981] MB

total disk space constrained to [ 31.157] MB

Trying variations of the solution set.

1 indexes in current solution

[3203.0000] timerons (without recommendations)

[ 22.0000] timerons (with current solution)

[99.31%] improvement

--

--

-- LIST OF RECOMMENDED INDEXES

-- ===========================

-- index[1], 10.981MB

   CREATE INDEX "DB2INST1"."IDX2406010856550" ON "TEST "."HISTORY"

   ("BRANCH_ID" ASC, "TELLER_ID" ASC, "ACCTNAME" ASC,

   "BALANCE" ASC, "ACCT_ID" ASC) ALLOW REVERSE SCANS COLLECT SAMPLED DETAILED ST

ATISTICS;

   COMMIT WORK ;

--

--

-- RECOMMENDED EXISTING INDEXES

-- ============================

--

--

-- UNUSED EXISTING INDEXES

-- ============================

-- ===========================

--

-- ====ADVISOR DETAILED XML OUTPUT=============

-- ==(Benefits do not include clustering recommendations)==

--

--<?xml version="1.0"?>

--<design-advisor>

--<index>

--<identifier>

--<name>IDX2406010856550</name>

--<schema>DB2INST1</schema>

--</identifier>

--<table><identifier>

--<name>HISTORY</name>

--<schema>TEST </schema>

--</identifier></table>

--<statementlist>0</statementlist>

--<benefit>3181.000000</benefit>

--<overhead>0.000000</overhead>

--<diskspace>10.981469</diskspace>

--</index>

--<statement>

--<statementnum>0</statementnum>

--<statementtext>

-- SELECT BRANCH_ID, TELLER_ID, ACCT_ID, BALANCE,

-- ACCTNAME FROM test.HISTORY WHERE branch_id

-- = 20 and teller_id between 100 and 180

--</statementtext>

--<objects>

--<identifier>

--<name>HISTORY</name>

--<schema>TEST </schema>

--</identifier>

--<identifier>

--<name>IDX2406010856550</name>

--<schema>DB2INST1</schema>

--</identifier>

--</objects>

--<benefit>3181.000000</benefit>

--<frequency>1</frequency>

--</statement>

--</design-advisor>

-- ====ADVISOR DETAILED XML OUTPUT=============

--

14 solutions were evaluated by the advisor

DB2 Workload Performance Advisor tool is finished.

create_testhist.ddl

CONNECT TO MUSICDB;

------------------------------------------------
-- DDL Statements for table "TEST    "."HISTORY"
------------------------------------------------
 

CREATE TABLE "TEST    "."HISTORY"  (
		  "ACCT_ID" INTEGER NOT NULL , 
		  "TELLER_ID" SMALLINT NOT NULL , 
		  "BRANCH_ID" SMALLINT NOT NULL , 
		  "BALANCE" DECIMAL(15,2) NOT NULL , 
		  "DELTA" DECIMAL(9,2) NOT NULL , 
		  "PID" INTEGER NOT NULL , 
		  "TSTMP" TIMESTAMP NOT NULL WITH DEFAULT CURRENT TIMESTAMP , 
		  "ACCTNAME" CHAR(20) NOT NULL , 
		  "TEMP" CHAR(6) NOT NULL )   
		 IN "USERSPACE1" ; 



LOAD FROM /home/inst23/ddl/histdata.ixf OF IXF REPLACE INTO TEST.HISTORY NONRECOVERABLE ;
RUNSTATS ON TABLE TEST.HISTORY ;


CONNECT RESET;

执行文件：

db2 -tvf create_testhist.ddl

输出：

CONNECT TO MUSICDB

Database Connection Information

Database server = DB2/LINUXX8664 11.5.4.0

SQL authorization ID = DB2INST1

Local database alias = MUSICDB

CREATE TABLE "TEST "."HISTORY" ( "ACCT_ID" INTEGER NOT NULL , "TELLER_ID" SMALLINT NOT NULL , "BRANCH_ID" SMALLINT NOT NULL , "BALANCE" DECIMAL(15,2) NOT NULL , "DELTA" DECIMAL(9,2) NOT NULL , "PID" INTEGER NOT NULL , "TSTMP" TIMESTAMP NOT NULL WITH DEFAULT CURRENT TIMESTAMP , "ACCTNAME" CHAR(20) NOT NULL , "TEMP" CHAR(6) NOT NULL ) IN "USERSPACE1"

DB21034E The command was processed as an SQL statement because it was not a

valid Command Line Processor command. During SQL processing it returned:

SQL0601N The name of the object to be created is identical to the existing

name "TEST.HISTORY" of type "TABLE". SQLSTATE=42710

LOAD FROM /home/inst23/ddl/histdata.ixf OF IXF REPLACE INTO TEST.HISTORY NONRECOVERABLE

SQL3109N The utility is beginning to load data from file

"/home/inst23/ddl/histdata.ixf".

SQL3500W The utility is beginning the "LOAD" phase at time "06/01/2024

16:59:39.554819".

SQL3150N The H record in the PC/IXF file has product "DB2 02.00", date

"20120817", and time "154700".

SQL3050W Conversions on the data will be made between the IXF file code page

"1252" and the application code page "1208".

SQL3153N The T record in the PC/IXF file has name "histdata.del", qualifier

"", and source " ".

SQL3519W Begin Load Consistency Point. Input record count = "0".

SQL3520W Load Consistency Point was successful.

SQL3110N The utility has completed processing. "200000" rows were read from

the input file.

SQL3519W Begin Load Consistency Point. Input record count = "200000".

SQL3520W Load Consistency Point was successful.

SQL3515W The utility has finished the "LOAD" phase at time "06/01/2024

16:59:40.076336".

Number of rows read = 200000

Number of rows skipped = 0

Number of rows loaded = 200000

Number of rows rejected = 0

Number of rows deleted = 0

Number of rows committed = 200000

RUNSTATS ON TABLE TEST.HISTORY

DB20000I The RUNSTATS command completed successfully.

CONNECT RESET

DB20000I The SQL command completed successfully.

任务4：使用DMC的Explain SQL功能检查访问计划，并根据新索引估算成本

在DMC执行以下脚本：

create_testhist_ix.ddl.

create index test.histix on test.history (branch_id, teller_id) ;

Call SYSPROC.ADMIN_CMD( 'runstats on table test.history and indexes all ' ) ;

查看fetch，可以看到只有699了，只有1/5了。

任务5.使用REORG实用程序重新组织表

在DMC上执行重组SQL语句：

对 TEST.HISTORY 表及其索引 TEST.HISTIX 进行重新组织，以优化其性能和/或节省存储空间

CALL SYSPROC.ADMIN_CMD ('REORG TABLE TEST.HISTORY INDEX TEST.HISTIX
USE TEMPSPACE1');

该语句适用于Windows与Linux系统。

在DMC里运行代码：

对 TEST.HISTORY 表及其所有相关索引进行详细的抽样统计信息收集

CALL SYSPROC.ADMIN_CMD(
'RUNSTATS ON TABLE TEST.HISTORY AND SAMPLED DETAILED INDEXES ALL');

任务6：使用DMC的Explain SQL功能重新检查表重组后的访问计划和估计成本

在DMC查看说明描述

可以看到只有33了，重组有效

paranoiass

关注

5
点赞
踩
5

收藏

觉得还不错? 一键收藏
0
评论
DB2大型数据之数据库维护、监控和问题确定

本实验目标：本演示使用几个DB2工具和实用程序来改进SQL查询的性能。您将使用Data Server Manager Visual Explain工具查看访问计划和处理SQL语句的估计成本。您将使用DB2设计顾问来建议一个新的索引，以降低处理成本。您将执行DB2 REORG实用程序来重新组织表以提高性能。
复制链接

扫一扫