Jan 17 2014

Enable / disable pentru constrangerile de tip FOREIGN KEY in IBM DB2 si SQL Server

Categorie: DBA | IBM DB2 | Import | Performanta | SQL ServerCatalin Dumitru @ 08:24

Bazele de date relationale se bazeaza pe relatii intre tabele. Exista cazuri cand avem nevoie ca aceste relatii sa nu se verifice, asa cum este cazul unui import masiv de date in care dezactivarea constragerilor si indecsilor ajuta la micsorarea timpului necesar importului de date.

IBM DB2 LUW (Linux, Unix, Windows)

Acest lucru poate fi realizat executand instructiunea de mai jos:

ALTER TABLE SCHEMA.TABELA ALTER FOREIGN KEY DENUMIRE_CONSTRANGERE_FOREIGN_KEY NOT ENFORCED;

Reactivarea constrangerilor de tip FOREIGH KEY se face executand instructiunea:

ALTER TABLE SCHEMA.TABELA ALTER FOREIGN KEY DENUMIRE_CONSTRANGERE_FOREIGN_KEY  ENFORCED;

Acest lucru este usor de facut cand este vorba de cateva relatii insa cand discutam despre un import care implica multe tabele, cel mai bine este sa rulam un script care dezactiveaza verificarile facute de SGBD:

CREATE PROCEDURE FOREIGNKEYS  (IN p_ACTION CHAR(1), IN p_DISPLAYSTATUS char(1))
DYNAMIC RESULT SETS 1
BEGIN ATOMIC

DECLARE vMESSAGE_TEXT CHAR(56) DEFAULT 'Parameter p_ACTION must be "E"(enable) or "D"(disable) !';
DECLARE V_text VARCHAR(8000);
DECLARE c_FK CURSOR WITH RETURN 

FOR SELECT ENFORCED, TABSCHEMA, TABNAME, CONSTNAME 
  FROM SYSCAT.TABCONST
  WHERE TYPE = 'F'  
  ORDER BY TABSCHEMA, TABNAME;

  SET p_ACTION = NULLIF(p_ACTION, '');
SET p_ACTION = UPPER(p_ACTION);

  IF p_ACTION NOT IN ('E', 'D') OR p_ACTION IS NULL THEN
  SIGNAL SQLSTATE '70000' SET MESSAGE_TEXT = vMESSAGE_TEXT;
END IF;

FOR v_FK AS SELECT 'ALTER TABLE "' || TRIM(TABSCHEMA) || '"."' || TABNAME || '" ALTER FOREIGN KEY "' || CONSTNAME || '" ' || CASE p_ACTION WHEN 'D' THEN 'NOT' WHEN 'E' THEN '' END  || ' ENFORCED' AS SQL_ACTION_FK
      FROM SYSCAT.TABCONST
      WHERE TYPE = 'F' AND ENFORCED=(CASE p_ACTION WHEN 'E' THEN 'N' WHEN 'D' THEN 'Y' END)
      ORDER BY TABSCHEMA, TABNAME

DO
  FK: BEGIN
    SET V_text = v_FK.SQL_ACTION_FK;
    EXECUTE IMMEDIATE V_text;
  END FK; 
END FOR;

IF UPPER(p_DISPLAYSTATUS)='Y' THEN 
  OPEN c_FK;
END IF;
END 

Parametrii folositi sunt:
1. p_ACTION: parametru de tip caracter care poate lua una din valorile "E" sau "D". E pentru activare si D pentru dezactivare;
2. p_DISPLAYSTATUS: parametru de tip caracter care daca primeste valoarea "Y" va intoarce lista contrangerilor de tip FOREIGN KEY din baza de date impreuna cu proprietatea ENFORCED (adica status-ul constrangerii).

SQL Server

In SQL Server ne putem folosi de procedura nedocumentata sp_msforeachtable:

EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

respectiv

exec sp_msforeachtable @command1="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

Si in acest caz se poate realiza o procedura stocata cu aceeasi functionalitate de mai sus.

Etichete: , , , ,