Runstats进度观察与耗时估计方法

 

1. 查看当前的数据库后台任务

arlqsap01:db2qsa:QSA:/db2/QSA/db2qsa> db2 list utilities

 

ID                               = 1002044

Type                             = RUNSTATS

Database Name                    = QSA

Partition Number                 = 0

Description                      = SAPR3.STXH

Start Time                       = 07/15/2013 13:29:34.913788

State                            = Executing

Invocation Type                  = User

Throttling:

   Priority                      = Unthrottled

 

ID                               = 1002043

Type                             = RUNSTATS

Database Name                    = QSA

Partition Number                 = 0

Description                      = SAPR3.VBOX

Start Time                       = 07/15/2013 12:42:16.182205

State                            = Executing

Invocation Type                  = User

Throttling:

   Priority                      = Unthrottled

 

1. 查看当前数据库runstats的进度

arlqsap01:db2qsa:QSA:/db2/QSA/db2qsa> db2pd -d qsa -runstats | grep -p STXH

Retrieval Time: 07/16/2013 04:02:51

TbspaceID: 23       TableID: 4330

Schema: SAPR3    TableName: STXH

Status: Completed     Access: Allow write

Sampling: No          Sampling Rate: -

Start Time: 07/15/2013 13:29:34   End Time: 07/15/2013 23:36:36

Total Duration: 10:07:01

Cur Count: 0                      Max Count: 0

（以上是表的runstats进度）

 

Retrieval Time: 07/16/2013 04:02:53

TbspaceID: 23       TableID: 4330

Schema: SAPR3    TableName: STXH

Status: In Progress   Access: Allow write

Start Time: 07/15/2013 23:36:36   End Time: -

Total Duration:  -

Prev Index Duration [1]:  -

Prev Index Duration [2]:  -

Prev Index Duration [3]:  -

Cur Index Start: 07/15/2013 23:36:36

Cur Index: 1            Max Index: 3            Index ID: 1

Cur Count: 1832147                Max Count: 11674708

（以上是索引的runstats进度）

 

由此可见，虽然表的runstats已经完成，但是由于其索引还在继续进行runstats，所以，此任务还在进行，还能由db2 list utilities查看到。

1. 如何估计runstats还需要的时间

按时间间隔连续查看几次，观察Cur Count 和Max Count的值，确定大概需要多少时间完成

例如

arlqsap01:db2qsa:QSA:/db2/QSA/db2qsa> db2pd -d qsa -runstats | grep -p STXH

Retrieval Time: 07/16/2013 04:02:51

TbspaceID: 23       TableID: 4330

Schema: SAPR3    TableName: STXH

Status: Completed     Access: Allow write

Sampling: No          Sampling Rate: -

Start Time: 07/15/2013 13:29:34   End Time: 07/15/2013 23:36:36

Total Duration: 10:07:01

Cur Count: 0                      Max Count: 0

 

Retrieval Time: 07/16/2013 04:02:53

TbspaceID: 23       TableID: 4330

Schema: SAPR3    TableName: STXH

Status: In Progress   Access: Allow write

Start Time: 07/15/2013 23:36:36   End Time: -

Total Duration:  -

Prev Index Duration [1]:  -

Prev Index Duration [2]:  -

Prev Index Duration [3]:  -

Cur Index Start: 07/15/2013 23:36:36

Cur Index: 1            Max Index: 3            Index ID: 1

Cur Count: 1832147                Max Count: 11674708

 

arlqsap01:db2qsa:QSA:/db2/QSA/db2qsa> db2pd -d qsa -runstats | grep -p STXH

Retrieval Time: 07/16/2013 04:03:53

TbspaceID: 23       TableID: 4330

Schema: SAPR3    TableName: STXH

Status: Completed     Access: Allow write

Sampling: No          Sampling Rate: -

Start Time: 07/15/2013 13:29:34   End Time: 07/15/2013 23:36:36

Total Duration: 10:07:01

Cur Count: 0                      Max Count: 0

 

Retrieval Time: 07/16/2013 04:03:56

TbspaceID: 23       TableID: 4330

Schema: SAPR3    TableName: STXH

Status: In Progress   Access: Allow write

Start Time: 07/15/2013 23:36:36   End Time: -

Total Duration:  -

Prev Index Duration [1]:  -

Prev Index Duration [2]:  -

Prev Index Duration [3]:  -

Cur Index Start: 07/15/2013 23:36:36

Cur Index: 1            Max Index: 3            Index ID: 1

Cur Count: 1842767                Max Count: 11674708

 

arlqsap01:db2qsa:QSA:/db2/QSA/db2qsa>

 

通过Retrieval time，得到时间间隔为1分钟，两次Cur Count的差值为10620，则估计剩余时间为（ Max Count-Cur Count2）/ 10620

即

（11674708-1842767）/10620 = 925.7948 分钟