Dopping架构DB2 UDB v8.1.9 [英] Dopping a schema DB2 UDB v8.1.9
问题描述
DROP SCHEMA的语法图需要RESTRICT。是否有一种简单的方法来删除旧的，不必要的，但已填充的架构？
The syntax diagram of DROP SCHEMA requires RESTRICT. Is there an easy
way to drop an old, unnecessary, but populated schema?
推荐答案
您可以创建一个（ shell / CLP，perl，java等）脚本或存储过程
读取数据库字典（系统目录）和dinamicaly
构建DROP语句列表不必要的对象
（记住对象之间的依赖关系）并最终发布DROP
SCHEMA ... RESTRICT当架构为空时。
-Eugene
You can create a (shell/CLP, perl, java, etc.) script or a stored proc
that reads the database dictionary (system catalog) and dinamicaly
builds the list of DROP statements for the unnecessary objects
(remember about dependences between objects) and finally issues DROP
SCHEMA ... RESTRICT when the schema(s) empty.
-Eugene
Bob Stearns写道：
Bob Stearns wrote:
DROP SCHEMA的语法图需要RESTRICT。是否有一种简单的方法来删除旧的，不必要的，但填充的架构？
The syntax diagram of DROP SCHEMA requires RESTRICT. Is there an easy
way to drop an old, unnecessary, but populated schema?
当然:-)
DROP程序DROPSCHEMA
@
CREATE PROCEDURE DROPSCHEMA（在pschemaname VARCHAR（128））
BEGIN
DECLARE txt VARCHAR （1000）;
DECLARE进度INTEGER;
DECLARE成功SMALLINT;
REPEAT
SET progress = 0;
FOR thisObject
AS SELECT类型，名称FROM
（SELECT类型，tabname FROM SYSCAT.TABLES WHERE tabschema =
pschemaname
UNION ALL
SELECT''s''，seqname FROM SYSCAT.SEQUENCES WHERE seqschema =
pschemaname
AND seqtype =''S''
UNION ALL
SELECT routinetype，specificname FROM SYSCAT.ROUTINES
WHERE routineschema = pschemaname
AND routinetype IN（''F''，''P''）
AND origin NOT IN（''S''，''T' '）
UNION ALL
SELECT''t''，typename FROM SYSCAT.DATATYPES WHERE typeschema =
pschemaname
UNION ALL
SELECT''g''，trigname FROM SYSCAT.TRIGGERS WHERE trigschema =
pschemaname）
AS T（类型，名称）
按名称订购
DO
BEGIN
DECLARE CONTINUE HANDLER for SQLSTATE''42893''，SQLSTATE''42704' '
BEGIN
SET成功= 0;
结束;
SET成功= 1;
SET txt =''DROP''
|| CASE类型
当''T''那么''表''
什么时候''那么''表''
什么时候''你'那''表''
什么时候''''那么''NICKNAME''
当''''那么''的时候''
当''那么''SEQUENCE''
当''V''那么'''''$ $
当''W''那''观''
当''F''那么'具体功能''
当''P''那么''具体程序''
什么时候'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' $ b结束
|| ''''''|| pschemaname || ''"。"''||名字|| ''"'';
EXECUTE IMMEDIATE txt;
如果成功= 1那么
SET进度= 1;
END IF;
END;
END FOR;
UNTIL progress = 0 END REPEAT;
SET txt =''DROP SCHEMA'''|| pschemaname || " RESTRICT'';
EXECUTE IMMEDIATE txt;
END
@
说到哪一个。 ..我正在寻找beta-testers对于我的备份/恢复
架构开发人员正在准备未来文章。任何接受者？
干杯
Serge
-
Serge Rielau
DB2解决方案开发
适用于Linux，Unix，Windows的DB2 UDB
IBM多伦多实验室
Of course :-)
DROP PROCEDURE DROPSCHEMA
@
CREATE PROCEDURE DROPSCHEMA(IN pschemaname VARCHAR(128))
BEGIN
DECLARE txt VARCHAR(1000);
DECLARE progress INTEGER;
DECLARE success SMALLINT;
REPEAT
SET progress = 0;
FOR thisObject
AS SELECT type, name FROM
(SELECT type, tabname FROM SYSCAT.TABLES WHERE tabschema =
pschemaname
UNION ALL
SELECT ''s'', seqname FROM SYSCAT.SEQUENCES WHERE seqschema =
pschemaname
AND seqtype = ''S''
UNION ALL
SELECT routinetype, specificname FROM SYSCAT.ROUTINES
WHERE routineschema = pschemaname
AND routinetype IN (''F'', ''P'')
AND origin NOT IN (''S'', ''T'')
UNION ALL
SELECT ''t'', typename FROM SYSCAT.DATATYPES WHERE typeschema =
pschemaname
UNION ALL
SELECT ''g'', trigname FROM SYSCAT.TRIGGERS WHERE trigschema =
pschemaname)
AS T(type, name)
ORDER BY name
DO
BEGIN
DECLARE CONTINUE HANDLER FOR SQLSTATE ''42893'', SQLSTATE ''42704''
BEGIN
SET success = 0;
END;
SET success = 1;
SET txt = ''DROP ''
|| CASE type
WHEN ''T'' THEN ''TABLE ''
WHEN ''S'' THEN ''TABLE ''
WHEN ''U'' THEN ''TABLE ''
WHEN ''N'' THEN ''NICKNAME ''
WHEN ''A'' THEN ''ALIAS ''
WHEN ''s'' THEN ''SEQUENCE ''
WHEN ''V'' THEN ''VIEW ''
WHEN ''W'' THEN ''VIEW ''
WHEN ''F'' THEN ''SPECIFIC FUNCTION ''
WHEN ''P'' THEN ''SPECIFIC PROCEDURE ''
WHEN ''t'' THEN ''TYPE ''
WHEN ''g'' THEN ''TRIGGER''
END
|| ''"'' || pschemaname || ''"."'' || name || ''"'';
EXECUTE IMMEDIATE txt;
IF success = 1 THEN
SET progress = 1;
END IF;
END;
END FOR;
UNTIL progress = 0 END REPEAT;
SET txt = ''DROP SCHEMA "'' || pschemaname || ''" RESTRICT'';
EXECUTE IMMEDIATE txt;
END
@
Talking of which... I''m looking of "beta-testers" for my backup/restore
schema developer works article-to-be. Any takers?
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab
Serge Rielau写道：
Serge Rielau wrote:
Bob Stearns写道：
Bob Stearns wrote:
DROP SCHEMA的语法图需要RESTRICT。是否有一种简单的方法来删除旧的，不必要的，但填充的架构？
The syntax diagram of DROP SCHEMA requires RESTRICT. Is there an easy
way to drop an old, unnecessary, but populated schema?
当然:-)
DROP PROCEDURE DROPSCHEMA
@
CREATE PROCEDURE DROPSCHEMA（在pschemaname VARCHAR（128））
BEGIN
DECLARE txt VARCHAR（1000）;
DECLARE进度INTEGER;
DECLARE成功SMALLINT;
REPEAT
SET progress = 0;
FOR thisObject
AS SELECT类型，名称FROM
（SELECT类型，tabname FROM SYSCAT.TABLES WHERE tabschema =
pschemaname
UNION ALL
SELECT''s'，seqname FROM SYSCAT.SEQUENCES WHERE seqschema =
pschemaname
AND seqtype =''S''
UNION ALL
SELECT routinetype，specificname FROM SYSCAT.ROUTINES
WHERE routineschema = pschemaname
AND routinetype IN（''F''，''P''）
AND origin NOT IN（''S''，''T''）
UNION ALL
SELECT''t''，typename FROM SYSCAT.DATATYPES WHERE typeschema =
pschemaname
UNION ALL
选择''g''，trigname来自SYSCAT.TRIGGERS WHERE trigschema =
pschemaname）
AS T（类型，名称）
ORDER BY名称
DO
BEGIN
为SQLSTATE''42893''，DECSTARE CONTINUE HANDLER，SQLSTATE''42704''
BEGIN
SET success = 0;
END;
SET成功= 1;
SET txt =''DROP''
||案例类型
什么时候'''那么''表''
什么时候''那么''表''
什么时候''''那么''表'''
当''N''那''NICKNAME''
什么时候''那么'''''''''''''''''''''''''''' />什么时候''V''然后''看''
什么时候''''那'''''''''''''''''''''''''''''''' >当''P''然后''具体程序''
什么时候'''''''''
当''''那么''触发''
结束
|| ''''''|| pschemaname || ''"。"''||名字|| ''"'';
EXECUTE IMMEDIATE txt;
如果成功= 1那么
SET进度= 1;
END IF;
END;
END FOR;
UNTIL progress = 0 END REPEAT;
SET txt =''DROP SCHEMA"''|| pschemaname || " RESTRICT'';
EXECUTE IMMEDIATE txt;
END
@
说到哪......我正在寻找beta-testers对于我的备份/恢复
架构开发人员正在准备未来文章。任何接受者？
干杯
Serge
Of course :-)
DROP PROCEDURE DROPSCHEMA
@
CREATE PROCEDURE DROPSCHEMA(IN pschemaname VARCHAR(128))
BEGIN
DECLARE txt VARCHAR(1000);
DECLARE progress INTEGER;
DECLARE success SMALLINT;
REPEAT
SET progress = 0;
FOR thisObject
AS SELECT type, name FROM
(SELECT type, tabname FROM SYSCAT.TABLES WHERE tabschema =
pschemaname
UNION ALL
SELECT ''s'', seqname FROM SYSCAT.SEQUENCES WHERE seqschema =
pschemaname
AND seqtype = ''S''
UNION ALL
SELECT routinetype, specificname FROM SYSCAT.ROUTINES
WHERE routineschema = pschemaname
AND routinetype IN (''F'', ''P'')
AND origin NOT IN (''S'', ''T'')
UNION ALL
SELECT ''t'', typename FROM SYSCAT.DATATYPES WHERE typeschema =
pschemaname
UNION ALL
SELECT ''g'', trigname FROM SYSCAT.TRIGGERS WHERE trigschema =
pschemaname)
AS T(type, name)
ORDER BY name
DO
BEGIN
DECLARE CONTINUE HANDLER FOR SQLSTATE ''42893'', SQLSTATE ''42704''
BEGIN
SET success = 0;
END;
SET success = 1;
SET txt = ''DROP ''
|| CASE type
WHEN ''T'' THEN ''TABLE ''
WHEN ''S'' THEN ''TABLE ''
WHEN ''U'' THEN ''TABLE ''
WHEN ''N'' THEN ''NICKNAME ''
WHEN ''A'' THEN ''ALIAS ''
WHEN ''s'' THEN ''SEQUENCE ''
WHEN ''V'' THEN ''VIEW ''
WHEN ''W'' THEN ''VIEW ''
WHEN ''F'' THEN ''SPECIFIC FUNCTION ''
WHEN ''P'' THEN ''SPECIFIC PROCEDURE ''
WHEN ''t'' THEN ''TYPE ''
WHEN ''g'' THEN ''TRIGGER''
END
|| ''"'' || pschemaname || ''"."'' || name || ''"'';
EXECUTE IMMEDIATE txt;
IF success = 1 THEN
SET progress = 1;
END IF;
END;
END FOR;
UNTIL progress = 0 END REPEAT;
SET txt = ''DROP SCHEMA "'' || pschemaname || ''" RESTRICT'';
EXECUTE IMMEDIATE txt;
END
@
Talking of which... I''m looking of "beta-testers" for my backup/restore
schema developer works article-to-be. Any takers?
Cheers
Serge
非常感谢，我今天晚些时候会尝试这个。
我很高兴成为你的beta测试人员。此电子邮件地址应该有效。
Thanks a lot, I will try this later today.
I''d be glad to be a beta tester for you. This email address should work.
这篇关于Dopping架构DB2 UDB v8.1.9的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
