Dopping架构DB2 UDB v8.1.9

本文介绍了Dopping架构DB2 UDB v8.1.9的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 DROP SCHEMA的语法图需要RESTRICT。是否有一种简单的方法来删除旧的，不必要的，但已填充的架构？ The syntax diagram of DROP SCHEMA requires RESTRICT. Is there an easyway 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))BEGINDECLARE txt VARCHAR(1000);DECLARE progress INTEGER;DECLARE success SMALLINT; REPEATSET progress = 0;FOR thisObjectAS SELECT type, name FROM(SELECT type, tabname FROM SYSCAT.TABLES WHERE tabschema =pschemanameUNION ALLSELECT ''s'', seqname FROM SYSCAT.SEQUENCES WHERE seqschema =pschemanameAND seqtype = ''S''UNION ALLSELECT routinetype, specificname FROM SYSCAT.ROUTINESWHERE routineschema = pschemanameAND routinetype IN (''F'', ''P'')AND origin NOT IN (''S'', ''T'')UNION ALLSELECT ''t'', typename FROM SYSCAT.DATATYPES WHERE typeschema =pschemanameUNION ALLSELECT ''g'', trigname FROM SYSCAT.TRIGGERS WHERE trigschema =pschemaname)AS T(type, name)ORDER BY nameDOBEGINDECLARE CONTINUE HANDLER FOR SQLSTATE ''42893'', SQLSTATE ''42704''BEGINSET success = 0;END;SET success = 1;SET txt = ''DROP ''|| CASE typeWHEN ''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 THENSET 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/restoreschema developer works article-to-be. Any takers? CheersSerge--Serge RielauDB2 Solutions DevelopmentDB2 UDB for Linux, Unix, WindowsIBM Toronto Lab 当然:-) 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的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！