This stored procedure example is performing a runstats on all tables in the database.
The two output parameters are the total number of tables on the database and the number of successful runstats.
The procedure don't have any condition handlers and will stop immediately after an error occurs.
The runstats will be performed by the ADMIN_CMD procedure.
create procedure runstats (out nr_tables integer, out nr_ok integer)
begin
declare SQLCODE integer;
declare stmt varchar(100);
set nr_tables = 0;
set nr_ok = 0;
for line as select tabschema, tabname from syscat.tables where type='T' and tabschema='SPODEN'
do
set nr_tables = nr_tables + 1;
set stmt = 'CALL SYSPROC.ADMIN_CMD (RUNSTATS ON TABLE ' concat rtrim(line.tabschema) concat '.' concat line.tabname concat ')';
execute immediate stmt;
if SQLCODE = 0 then
set nr_ok = nr_ok + 1;
end if;
end for;
end!
The two output parameters are the total number of tables on the database and the number of successful runstats.
The procedure don't have any condition handlers and will stop immediately after an error occurs.
The runstats will be performed by the ADMIN_CMD procedure.
create procedure runstats (out nr_tables integer, out nr_ok integer)
begin
declare SQLCODE integer;
declare stmt varchar(100);
set nr_tables = 0;
set nr_ok = 0;
for line as select tabschema, tabname from syscat.tables where type='T' and tabschema='SPODEN'
do
set nr_tables = nr_tables + 1;
set stmt = 'CALL SYSPROC.ADMIN_CMD (RUNSTATS ON TABLE ' concat rtrim(line.tabschema) concat '.' concat line.tabname concat ')';
execute immediate stmt;
if SQLCODE = 0 then
set nr_ok = nr_ok + 1;
end if;
end for;
end!
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/22661144/viewspace-1477096/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
