Results 1 to 4 of 4

Thread: set integrity

  1. #1
    Join Date
    Apr 2013
    Posts
    10

    Unanswered: set integrity

    Hi,

    SQL0668N Operation not allowed for reason code "1" on table
    EMPLOYEE. SQLSTATE=57016


    Is there any command to run set integrity for all tables in a single command.

    Thanks in advance.

  2. #2
    Join Date
    Apr 2013
    Posts
    2
    Good Day Nagaraju,

    There is no pre-installed command to run set integrity on all the tables. Although, you can easily create a single command which uses the system catalog to run set integrity on all tables. Here is an example using SQL against the catalog to create a stored procedure which runs set integrity on all tables set set integrity state:

    CREATE PROCEDURE DB_UTIL.SET_INTEGRITY_ALL()
    LANGUAGE SQL
    BEGIN
    DECLARE S_TABSCHEMA VARCHAR(1024);
    DECLARE S_TABNAME VARCHAR(1024);
    DECLARE S_SET_INTEGRITY VARCHAR(1024);

    DECLARE c CURSOR FOR SELECT TABSCHEMA,TABNAME FROM SYSCAT.TABLES WHERE TYPE ='T' AND STATUS = 'C' AND TABSCHEMA NOT LIKE 'SYS%';
    OPEN c;

    FETCH FROM c INTO S_TABSCHEMA, S_TABNAME;

    WHILE(SQLSTATE = '00000') DO
    SET S_SET_INTEGRITY = 'SET INTEGRITY FOR ' || TRIM(S_TABSCHEMA) || '.' || TRIM(S_TABNAME) || ' IMMEDIATE CHECKED';
    CALL SYSPROC.ADMIN_CMD(S_SET_INTEGRITY);
    FETCH FROM c INTO S_TABSCHEMA, S_TABNAME;
    END WHILE;

    CLOSE c;
    END


    ------------------
    You can then call this procdure by doing:

    db2 "call DB_UTIL.SET_INTEGRITY_ALL()"

    --------------------------------------------------------
    Rob Williams - Technical Specialist @ Home

  3. #3
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    create a small script that generates the statements based on catalogs
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  4. #4
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    I use something like this:

    Code:
    BEGIN
    	DECLARE V_TABLENAME VARCHAR(128);
    	DECLARE V_TABLESCHEMA VARCHAR(128);
    	DECLARE V_STMT VARCHAR(300);
    	DECLARE SQLCODE INTEGER;
    	
    	DECLARE CRS_TABLES CURSOR FOR
    	SELECT TABSCHEMA, TABNAME
    	FROM SYSCAT.TABLES
    	WHERE STATUS <> 'N' AND TYPE = 'T'
    	ORDER BY TABSCHEMA, TABNAME;
    
    	OPEN CRS_TABLES;
    	FETCH CRS_TABLES INTO V_TABLESCHEMA, V_TABLENAME;
    	WHILE (SQLCODE <> 100) DO
    		SET V_STMT = 'SET INTEGRITY FOR ' || V_TABLESCHEMA || '.' || V_TABLENAME || ' IMMEDIATE CHECKED';
    		EXECUTE IMMEDIATE V_STMT;
    		FETCH CRS_TABLES INTO V_TABLESCHEMA, V_TABLENAME;
    	END WHILE;
    	CLOSE CRS_TABLES;
    END@
    Note:
    Sometimes, when you have FKs, running SET INTEGRITY on a table brings other tables to check pending state. You should put the above code in a WHILE loop
    Last edited by aflorin27; 04-23-13 at 03:34.
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •