SAP IQ 数据库里自动执行DBCC检查的存储过程脚本及使用说明

IQ 数据库在遇到表损坏的情况下，通常需要执行dbcc检查，查找出有问题的表或索引，然后进行修复或重建。Dbcc检查有三种模式：

sp_iqcheckdb ‘allocation database/dbspace/table’

sp_iqcheckdb ‘check  database/dbspace/table/index’

sp_iqcheckdb ‘verify  database/dbspace/table/index’

这几种方式中最快的是 allocation 模式，其次是check模式、verify  模式。当数据库中有表损坏时，allocation、check  、verify  database/dbspace 定位不了有损坏的表或索引，只能把所有表名获取为一个文件，再通过编写sp_iqcheckdb ‘allocation/check/verify table tablename ’脚本来执行，如果表个数比较多，编写这么多脚本及生成check输出结果会增加额外的工作量。为了解决这个问题，编写了dbcc检查的存储过程，把输出结果insert into一张临时表中，并且可以控制每次检查表的个数，提高了数据库dbcc检查的工作效率。以下是运行该存储过程的说明。

存储过程说明

在创建该存储过程前，需要创建两张临时表，一张存放IQ数据库中用户表：verifytbale，一张存放dbcc检查的输出结果：verifytableresult。具体如下：

用户表

create table verifytable (tablename char(128),state  int,createtime char(50));

注：state 列用来标识表的检查状态，默认“0”为初始状态，“1”为已检查状态，2为存在异常错误或表已不存在当前数据库里。

到verifytable

执行以下SQL，把用户表insert into到verifytable表中：

insert into verifytable(tablename,state,createtime) select  c.creator||'.'||table_name,0,b.create_time from systable a,sysiqtable b,syscatalog c where a.table_id = b.table_id and a.table_name=c.tname and c.tabletype='TABLE' and c.creator='DBA'  and b. create_time<=’20140618’  order by create_time;

insert into 完成后，执行commit命令。

检查结果表

创建verifytableresult有两种方式：

select convert(char(128),'') as table_name, * into verifytableresult from sp_iqcheckdb('verify table test1')  --test1表需存在。

create table verifytableresult (table_name varchar(128),Stat varchar(200),Value varchar(200),Flags varchar(20))

存储过程

以上步骤完成后，开始创建自动执行DBCC检查的存储过程：

create procedure sp_iqverifytable (in @tableCount int)

begin

    declare @loopCounter int;

    declare @sqlVerify varchar(2000);

    declare @tablename char(128) ;

    declare @sqlUpdate1 varchar(2000);

    declare @sqlUpdate2 varchar(2000);

    declare tabname_cur cursor for select  tablename from verifytable  where  state=0 order by createtime;

    open tabname_cur with hold;

    fetch tabname_cur into @tablename ;

    set @loopCounter = 1;

    while (@loopCounter <= @tableCount and @@sqlstatus=0)

    loop

        set @sqlVerify = 'insert into verifytableresult select '''+@tablename+''',* from sp_iqcheckdb ('''+'verify table '+@tablename +''')';

        set @sqlUpdate1='update verifytable set state = 1 where tablename='''+@tablename+'''';

        set @sqlUpdate2='update verifytable set state = 2 where tablename='''+@tablename+'''';

        set @loopCounter =@loopCounter +1;

   

        execute immediate @sqlUpdate2;

        execute immediate @sqlVerify;

        execute immediate @sqlUpdate1;

        fetch tabname_cur into @tablename ;

    end loop;

    close tabname_cur;

    deallocate cursor tabname_cur ;

end;

调用方法： sp_iqverifytable(1000)

 

注意：

set @sqlUpdate2='update verifytable set state = 2 where tablename='''+@tablename+'''';

set @sqlUpdate1='update verifytable set state = 1 where tablename='''+@tablename+'''';

sqlUpdate2语句是在执行dbcc检查前，把表的State状态改为2，假设该表存在问题或表已存在数据库里。

sqlUpdate1语句是每次检查完一张表后，把表的State状态改为1，标识该表已经执行过dbcc 检查。

输出结果

检查一个已知表：

(DBA)> select * from verifytableresult where  table_name='DBA.history' and value='No Errors Detected'

table_name   Stat                Value                 Flags                                        

--------------------------------------------------------------------------------------                         

DBA.history     DBCC Status      No Errors Detected                                                 

检查有异常的表：

select * from verifytableresult  where  value=' Errors Detected'

 

事件自动执行DBCC检查

如果在验证节点上执行该存储过程，验证节点每天都需要同步和重启。可以编写一个IQ事件，当IQ数据库启动后，自动调用该存储过程，执行dbcc检查，不需要再登录上去执行该存储过程：

CREATE EVENT doVerifytableEvent type DatabaseStart  HANDLER BEGIN  call sp_iqverifytable(1000) END

每天中午12点调起该事件：

CREATE EVENT doTimeVerifytableEvent SCHEDULE START TIME '11:00PM' EVERY 24 HOURS HANDLER BEGIN call sp_iqverifytable(1000) END