DB2修改存储过程 （达到表结构的修改，和表的REORG）
本帖最后由 gongzhe2011 于 2012-12-06 13:03:58 编辑 需求就是在修改表结构之后
把REORG TABLE 加在存储过程里边 （达到表的解锁。） 一个存储过程完成
create or replace procedure ac(schema varchar(300),tabNM varchar(300),columnname varchar(200),type varchar(200),dataLength int,out err int)
language sql
begin atomic
declare v_sql varchar(200);
declare undo handler for sqlexception set err = 1;
if(dataLength=0) then
set v_sql = 'alter table '|| schema||'.'||tabNM||' alter column '||columnname||' set data type '||type;
else
set v_sql = 'alter table '|| schema||'.'||tabNM||' alter column '||columnname||' set data type '||type|| '('||dataLength||')';
end if;
prepare s1 from v_sql;
execute s1;
set err = 0;
end
[解决办法]
很好，顶一下，又学习了一个存储过程的使用技巧
[解决办法]
从官方的prepare说明上看,不支持REORG的动态执行
Rules for statement strings: The value of the specified statement-name is called the statement string. The statement string must be one of the following SQL statements:
ALLOCATE CURSOR
ALTER
ASSOCIATE LOCATORS
COMMENT
COMMIT
CREATE
DECLARE GLOBAL
TEMPORARY TABLE
DELETE
DROP
EXPLAIN
FREE LOCATOR
GRANT
HOLD LOCATOR
INSERT
LABEL
LOCK TABLE
MERGE
REFRESH TABLE
RELEASE SAVEPOINT
RENAME
REVOKE
ROLLBACK
SAVEPOINT
select-statement
SET CURRENT DEGREE
SET CURRENT DEBUG MODE
SET CURRENT DECFLOAT ROUNDING MODE
SET CURRENT LOCALE LC_CTYPE
SET CURRENT MAINTAINED TABLE
TYPES FOR OPTIMIZATION
SET CURRENT OPTIMIZATION HINT
SET CURRENT PRECISION
SET CURRENT QUERY ACCELERATION
SET CURRENT REFRESH AGE
SET CURRENT ROUTINE VERSION
SET CURRENT RULES
SET CURRENT SQLID
SET ENCRYPTION PASSWORD
SET PATH
SET SCHEMA
SET SESSION TIME ZONESIGNAL
TRUNCATE
UPDATE
[解决办法]
reorg不能在存储过程中直接用。
[解决办法]
2楼已经给出了思路
[解决办法]
嗯，就像WWWWA大神说的，用脚本吧
[解决办法]
SP之类的不用想了,只有在脚本中才能reorg
[解决办法]
9.7的话，除了脚本应该还可以用SYSPROC.ADMIN_CMD(),试试这个
