关于snapshot_tbs_cfg定时调度的问题

llouis

为了监视数据库表空间的使用情况，我写了一个shell脚本get_tbstat.ksh，通过调用snapshot_tbs_cfg收集表空间信息，并将结果写入库中，该脚本每隔1小时执行一次。但是，出现了一个很奇怪的问题：该脚本有时一天24小时均能正常执行，有时一天当中只能正确执行2、3次，出错信息如下：

DB21034E The command was processed  as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0443N Routine "SYSPROC.SNAPSHOT_TBS_CFG"(specific name "SNAPSHOT_TBS_CFG") has returned an error SQLSTATE with diagnostic text "SQL1611". SQLSTATE=38553


附脚本如下：

#!/bin/ksh
#
#  before running this script ,the "bufferpool" monitor switch
#  must be turned on by :
#  db2_all "db2 update monitor switches using bufferpool on"
#

#. /opt/ETL/.profile>/dev/null
. /db2home/db2inst1/sqllib/db2profile

db2 connect to mydb

SNAP_TS=`date +"%Y-%m-%d %H:%M:%S"`

echo "Start at : $SNAP_TS"
db2 -tp-<<EOF
insert into cnc.sys_tbstat_log(snap_ts,partition_num,tbs_name,tbs_used_size,tbs_total_size)
select timestamp('$SNAP_TS'),0,tablespace_name as tbs_name,page_size * used_pages/1024/1024 as tbs_used_size,total_pages * page_size/1024/1024 as tbs_total_size
from table(snapshot_tbs_cfg('mydb',0)) as tbs
where tablespace_type = 0
union
select timestamp('$SNAP_TS'),1,tablespace_name as tbs_name,page_size * used_pages/1024/1024 as tbs_used_size,total_pages * page_size/1024/1024 as tbs_total_size
from table(snapshot_tbs_cfg('mydb',1)) as tbs
where tablespace_type = 0
union
select timestamp('$SNAP_TS'),2,tablespace_name as tbs_name,page_size * used_pages/1024/1024 as tbs_used_size,total_pages * page_size/1024/1024 as tbs_total_size
from table(snapshot_tbs_cfg('mydb',2)) as tbs
where tablespace_type = 0
union
select timestamp('$SNAP_TS'),3,tablespace_name as tbs_name,page_size * used_pages/1024/1024 as tbs_used_size,total_pages * page_size/1024/1024 as tbs_total_size
from table(snapshot_tbs_cfg('mydb',3)) as tbs
where tablespace_type = 0
;
EOF

mahongwei

是没有数据返回吧, 系统函数没有取得数据，可能是系统问题

whiterain

我知道了，和你这个cnc.sys_tbstat_log的结构有关系，是不是类型不一致，db2钟的数据类型不会自己转化

llouis

应该不是数据类型的问题，否则怎么会有时成功，有时失败呢？！

llouis

根据IBM 800的回复及自己亲自做的一些试验，得出以下结论：

1、怀疑这是DB2这一版本(8.2 Fixpack 10)的bug；

2、这一bug还是有work around的办法，如下：
  
原有脚本中同时对4个数据库分区调用snapshot_tbs_cfg函数，然后union,这个过程改为：

for PARTITION_NO in 0 1 2 3
do
    export DB2NODE=$PARTITION_NO
    db2 -v terminate
    db2 -v connect to cncdb

db2 -tvp-<<EOF
insert into cnc.sys_tbstat_log(snap_ts,partition_num,tbs_name,tbs_used_size,tbs_total_size)
select timestamp('$SNAP_TS'),$PARTITION_NO,tablespace_name as tbs_name,page_size * used_pages/1024/1024 as tbs_used_size,total_pages * page_size/1024/1024 as tbs_total_size
from table(snapshot_tbs_cfg('cncdb',$PARTITION_NO)) as tbs
where tablespace_type = 0;
EOF

done


3、以上过程保证每次调用的时候能够返回正确结果，而不出现SQL1611N的错误