Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2014
    Posts
    294

    Unanswered: Sqlcode=-1822, sqlstate=560bd, sqlerrmc=-668

    Hi,

    I have coded one db2luw stored procedure.

    CREATE OR REPLACE PROCEDURE g_CONSTRAINTS (IN id FLOAT , dbname VARCHAR(32672))
    DYNAMIC RESULT SETS 10
    P1: BEGIN
    DECLARE text9 varchar(8000) ;
    DECLARE db_name varchar(200) ;
    DECLARE tablename varchar(200) ;
    DECLARE constr_name VARCHAR(32672);
    DECLARE string1 VARCHAR(32672);
    DECLARE r_count INTEGER;
    DECLARE l_error INTEGER;
    DECLARE process varchar(32672);
    DECLARE TEXT VARCHAR(32672);
    DECLARE SQLCODE INTEGER DEFAULT 0;
    DECLARE exitcode INTEGER DEFAULT 0;
    DECLARE v_NumRows INTEGER;
    DECLARE ERROR INTEGER;
    DECLARE adv_error INT;
    DECLARE adv_rowcount INT;
    DECLARE SQLSTATE CHAR(5) DEFAULT '00000';

    DECLARE var1 VARCHAR(1000);
    DECLARE var2 VARCHAR(1000);
    DECLARE var3 VARCHAR(1000);
    DECLARE var4 VARCHAR(1000);
    DECLARE TBNAME VARCHAR(100);


    -- Declare cursor
    DECLARE table_list CURSOR WITH HOLD for STMT1;

    DECLARE CONTINUE HANDLER FOR NOT FOUND
    SET exitcode = 1;


    SET TEXT = ('SELECT DB_NAME,TABLE_NAME,CONSTRAINT_NAME FROM G726589H_CONSTRAINTS WHERE DB_NAME = ''' || dbname || '''
    GROUP BY DB_NAME , TABLE_NAME, CONSTRAINT_NAME');
    PREPARE STMT1 FROM TEXT;


    open table_list;
    FETCH table_list INTO db_name,
    tablename,
    constr_name;

    wloop:
    WHILE (SQLCODE = 0)
    DO

    ---ADDING THE FOREIGN KEY CONSTARAINT

    SET var1= (LO689D_list(db_name,tablename,constr_name)) ;
    SET var2 = (LO670D_list(db_name,tablename,constr_name));


    SET var3 = RTRIM(var1);
    SET var4 = RTRIM(var2);

    call dbms_output.put_line(var3);
    call dbms_output.put_line(var4);

    ----REMOVING BRACES
    SET TEXT = ('set ? =(SELECT STRIP('''||var3||''',BOTH,'')'') FROM SYSIBM.SYSDUMMY1)');
    PREPARE S1 FROM TEXT;
    EXECUTE S1 into var3;

    SET TEXT = ('set ? =(SELECT STRIP('''||var3||''',BOTH,''('') FROM SYSIBM.SYSDUMMY1)');
    PREPARE S1 FROM TEXT;
    EXECUTE S1 into var3;

    SET TEXT = ('set ? =(SELECT STRIP('''||var4||''',BOTH,'')'') FROM SYSIBM.SYSDUMMY1)');
    PREPARE S1 FROM TEXT;
    EXECUTE S1 into var4;

    SET TEXT = ('set ? =(SELECT STRIP('''||var4||''',BOTH,''('') FROM SYSIBM.SYSDUMMY1)');
    PREPARE S1 FROM TEXT;
    EXECUTE S1 into var4;

    ------------
    IF(db_name NOT IN '*')
    THEN
    SET TEXT = ('set ? =(SELECT TABNAME FROM ' || db_name ||'.SYSCAT.REFERENCES WHERE CONSTNAME = ''' || constr_name || ''')');
    PREPARE S1 FROM TEXT;
    EXECUTE S1 into TBNAME;
    END IF;
    ------

    IF(db_name NOT IN '*')
    THEN
    EXECUTE IMMEDIATE 'SET PASSTHRU '|| db_name ;
    --SET text9 = ('ALTER TABLE '|| tablename ||' ADD CONSTRAINT '|| constr_name || ' FOREIGN KEY '|| var1 || ' REFERENCES '||TBNAME || var2 ||'');
    --EXECUTE IMMEDIATE text9;

    ---DISABLING THE FOREIGN KEY
    SET text9 = ( 'ALTER TABLE '|| tablename ||' ALTER FOREIGN KEY ' || constr_name || ' ENFORCED ');
    EXECUTE IMMEDIATE text9;

    COMMIT;
    CALL SYSPROC.ADMIN_CMD('REORG TABLE '|| tablename );

    EXECUTE IMMEDIATE 'SET PASSTHRU RESET' ;
    END IF;
    IF(SQLCODE <> 0 AND SQLCODE <> 100)
    THEN
    SET ERROR = SQLCODE;
    END IF;

    GET DIAGNOSTICS v_NumRows = ROW_COUNT;
    SET l_error = ERROR;
    SET r_count = v_NumRows;

    IF (l_error <> 0)
    THEN
    SET process = ('-- *** ERROR *** Code: '|| CAST( l_error AS VARCHAR(30) ) || ' while running ' || text9 ) ;
    ELSE

    SET process = (' - Created constraint for ' || db_name ||'.'|| tablename ||'.'|| constr_name);

    SET TEXT = ('DELETE FROM G726589H_CONSTRAINTS WHERE DB_NAME = ''' || dbname || ''' AND TABLE_NAME = ''' || tablename ||
    ''' AND CONSTRAINT_NAME = ''' || constr_name || '''') ;
    --EXECUTE IMMEDIATE TEXT;
    END IF;

    CALL SP_MENTIS_LOGS(id,process);


    SET exitcode = 0 ;
    FETCH table_list INTO db_name,
    tablename,
    constr_name;

    IF (exitcode = 1)
    THEN
    LEAVE wloop;
    END IF;

    END WHILE;
    CLOSE table_list;

    END P1

    Error:
    The cursor specified in a FETCH statement or CLOSE statement is not open or a cursor variable in a cursor scalar function reference is not open.. SQLCODE=-501, SQLSTATE=24501, DRIVER=4.17.30
    Run of routine failed.

    Reason for my error is As I am using the reorg inside the loop , my cursor is closing & unable to fetch the next row.

    If I remove the REORG statement from the loop: This is the error which I am facing:

    DB2 SQL Error: SQLCODE=-1822, SQLSTATE=560BD, SQLERRMC=-668;GSDB1; SQL0668N Operation not allowed for reason code "7" on tab, DRIVER=4.17.30
    Run of routine failed.

    And after this If i try to compile my procedure , i m getting an error as signal 11.

    After this until & unless I restart my db2 server , I am not able to compile any procedure.After restart my server , i can able to compile my procedure.

    I am using Db2 c express on windows with IBM data studio.

    can some one let me know how to handle this situation. I tried a lot but unable to figure it out. Sorry for posting my complete code.

    Thanks

  2. #2
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Which statement causes this error?
    Have you tried to read about SQL0668N rc=7?
    Regards,
    Mark.

  3. #3
    Join Date
    Jul 2014
    Posts
    294
    Thanks for the reply mark.

    This error occurred due to the tables under reorganize pending state.
    After this error, I can find some table from the below query

    select TABSCHEMA, TABNAME from SYSIBMADM.ADMINTABINFO where REORG_PENDING = 'Y';

    In order to prevent this, i tried to do REORG inside the loop itself.That is breaking my cursor completely.

    Here how can i prevent my tables not go into the reorganize pending state after Alter statement.

  4. #4
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Some alter statements against a table (so-called reorg-recommended operations) put this table in the reorg-pending state.
    You are not able to prevent setting such a table state after these operations.
    If you want to reorg a number of tables in a loop, you can use an array as I described in another post.
    Regards,
    Mark.

Tags for this Thread

Posting Permissions

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