Constraint umbenennen – mit Hilfe einer SQL Stored Procedure
Gewissenhafte DBA’s benennen allle ihre Primary Key und Check Constraints explizit. Damit kann man prima über Entwicklungs-, Test- und Produktions-Umgebungen hinweg, sofern erforderlich, einen Constraint überarbeiten, sprich löschen und geändert ieder aufbauen.
Aber was tun, wenn man keinen Constraint-Namen vergeben hat, DB2 den Constraint also implizit nach eigener Nomenklatur benannt hat. Unter Umständen kann der von DB2 vergebene Constraint-Name in verschiedenen Datenbanken unterschiedlich ausfallen. Jede einzelne Anpassung müsste individuell und sehr exakt erfolgen – Fehler beim ALTER TABLE DROP Constraint könnten Folgen haben. Das kann sehr aufwändig werden.
Ein praktisches Hilfmittel, z.B. in Form einer SQL Stored Procedure, kann da sehr nützlich sein. Der nachfolgend abgebildeten Stored Procedure gibt man den Creator, Table und Column-Namen mit, sie sucht einen Constraint und löscht ihn. Mit weiteren Statements lässt sich dann der gewünschte neue Constraint definieren – und dann aber mit explizitem Namen!
------------------------------------------------------------------------------- -- Ausführung mit db2 -td@ -vf C20180201_SQLP_DUC.sql ------------------------------------------------------------------------------- -- 01.02.2018/GR: Erste Version ------------------------------------------------------------------------------- DROP PROCEDURE DROP_UNKNOWN_CONSTRAINT @ CREATE PROCEDURE DROP_UNKNOWN_CONSTRAINT (IN ptbcreator VARCHAR(128), IN ptbname VARCHAR(128), IN pcolname VARCHAR(128), OUT pconstname VARCHAR(128)) LANGUAGE SQL SPECIFIC DROP_UNKNOWN_CONSTRAINT aa: BEGIN DECLARE SQLSTATE CHAR(5) DEFAULT '00000'; DECLARE err INT DEFAULT 0; DECLARE stmt VARCHAR(480); DECLARE EXIT HANDLER FOR SQLEXCEPTION SET err = 4; DECLARE EXIT HANDLER FOR SQLSTATE '21000' SET err = 3; DECLARE EXIT HANDLER FOR NOT FOUND SET err = 2; DECLARE EXIT HANDLER FOR SQLSTATE '99999' SET err = 1; SET pconstname = 'No constraint found for ' || ptbcreator || '.' || ptbname || '.' || pcolname; SELECT CONSTNAME INTO pconstname FROM SYSIBM.SYSCOLCHECKS WHERE TBNAME = ptbname AND TBCREATOR = ptbcreator AND COLNAME = pcolname; IF SQLSTATE = '00000' THEN IF pconstname <> '' THEN SET stmt='ALTER TABLE ' || ptbcreator || '.' || ptbname || ' DROP CONSTRAINT '|| pconstname; EXECUTE IMMEDIATE stmt; END IF; END IF; RETURN err;
Die abgebildete StoProc geht immer nur mit einem Constraint um. Müssten mehrere verarbeitet werden, müsste die StoProc um Cursor-Steuerung erweitert werden. Aber auch ohne wir sie in den meisten Fällen hilfreich sein.
Wenn die Stored Procedure dann erstmal zur Verfügung steht, kann man sich an den Rückbau des alten Constraint machen, ihn löschen lassen, und dann neu aufbauen:
VALUES ((CHAR(CURRENT SERVER,8)), (CHAR(CURRENT USER,8)), (CHAR(CURRENT SCHEMA,8))); -- -------------------------------------------------------------------------------- -- Der alte CHECK Constraint muss erstmal weg -------------------------------------------------------------------------------- SELECT CHAR(CONSTNAME,18), CHAR(TBNAME,16), CHAR(TBCREATOR,8), CHAR(COLNAME,16) FROM SYSIBM.SYSCOLCHECKS WHERE TBNAME ='MYTABLE_WITH_CONST' ; CALL DROP_UNKNOWN_CONSTRAINT('MYSCHEMA','MYTABLE_WITH_CONST','MYCOL_WITH_CONST',null) ; Value of output parameters -------------------------- Parameter Name : PCONSTNAME Parameter Value : SQL160215103234830 Return Status = 0 -- -------------------------------------------------------------------------------- -- Neuen Check Constraint, diesmal mit explizitem Namen, anlegen -------------------------------------------------------------------------------- ALTER TABLE MYTABLE_WITH_CONST ADD CONSTRAINT CHECK_MYCOL_CONST CHECK ( MYCOL_WITH_CONST = '' OR LOCATE('SELECT',STRIP(VARCHAR(FRACTION_SQL))) = 1 OR LOCATE('WITH',STRIP(VARCHAR(FRACTION_SQL))) = 1 ) ;
Und was ist nun der Witz an der ganzen Sache? Der DBA spart sich aufwändige Einelaktivitäten oder gar den DROP und re-CREATE der Tabelle, die mit dem Constraint belegt ist.
Weitere Anregungen oder Verbesserungsvorschläge sind willkommen!
Comments
Comments are closed.