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

    Unanswered: Exception handling for deletion

    Hi,

    I have written a procedure every thing is working fine. I need to do one exception handling , If I run this procedure ,initially it will try to clear a table.after that remaining things will work. But for the first time also, it is trying to delete the data from the table, where the table is empty.

    Please help me how to handle this situation, & this is my code.

    CREATE PROCEDURE M_WR1 ()
    DYNAMIC RESULT SETS 1
    P1: BEGIN
    DECLARE DBNAME varchar(50);
    DECLARE exitcode INTEGER DEFAULT 0;
    DECLARE STMT1 varchar(1000);
    DECLARE M_SR varchar(3000);
    DECLARE M_M_DAA varchar(5000);


    DECLARE mycur CURSOR FOR
    select DATABASE_NAME as v_DBNAME
    from DBLIST;

    DECLARE CONTINUE HANDLER FOR NOT FOUND
    SET exitcode = 1;

    EXECUTE IMMEDIATE 'DROP WRAPPER "M_WR"';
    SET STMT1 = 'CREATE WRAPPER "M_WR" LIBRARY ''libdb2drda.so'' OPTIONS (ADD DB2_FENCED ''N'')';
    EXECUTE IMMEDIATE STMT1;

    OPEN mycur;
    loop_over_dblist:
    LOOP
    FETCH FROM mycur INTO DBNAME;
    IF exitcode = 1 THEN
    LEAVE loop_over_dblist;
    END IF;



    SET M_SR = 'CREATE SERVER "'||DBNAME||'" TYPE DB2/UDB VERSION ''10.5'' '
    ||'WRAPPER "MENTIS_WRAPPER" AUTHORIZATION db2inst1 PASSWORD "mentis" '
    ||'OPTIONS (ADD DBNAME '''||DBNAME||''')';

    execute immediate M_SR;

    --Need if condition here as delete will fail if run for the first time and data wont be inserted. Please do the exception handling here---

    execute immediate 'delete from M_M_DAA where SERVER = '''||DBNAME||'''';

    set M_M_DAA = 'INSERT INTO M_M_DAA select '''||DBNAME||''', TAB.CREATOR, TAB.NAME as TABLE_NAME, COL.NAME, COL.COLTYPE from "'||DBNAME||'".SYSIBM.SYSTABLES TAB, "'||DBNAME||'".SYSIBM.SYSCOLUMNS COL where TAB.name not like ''SYS%'' and TAB.creator not like ''SYS%'' and TAB.type = ''T'' and COL.TBNAME=TAB.NAME';

    execute immediate M_M_DAA;

    -- execute immediate 'INSERT INTO M_M_DAA select '''||DBNAME||''', TAB.CREATOR, TAB.NAME as TABLE_NAME, COL.NAME, COL.COLTYPE from "'||DBNAME||'".SYSIBM.SYSTABLES TAB, "'||DBNAME||'".SYSIBM.SYSCOLUMNS COL where TAB.name not like ''SYS%'' and TAB.creator not like ''SYS%'' and TAB.type = ''T'' and COL.TBNAME=TAB.NAME';

    END LOOP loop_over_dblist;
    CLOSE mycur;
    END P1

  2. #2
    Join Date
    Jul 2014
    Posts
    294

    Exception handling for deletion

    I tried with few steps , but on each run records are adding to the table. duplicate records are existing. I don't know where i am missing the logic.

    This is the code.
    On each run it has to delete the table & after that it has to add the records to that table.

    CREATE PROCEDURE M_WR1 ()
    DYNAMIC RESULT SETS 1
    P1: BEGIN
    DECLARE DBNAME varchar(50);
    DECLARE exitcode INTEGER DEFAULT 0;
    DECLARE STMT1 varchar(1000);
    DECLARE M_SR varchar(3000);
    DECLARE M_M_DAA varchar(4000);
    DECLARE tableExists int;

    DECLARE mycur CURSOR FOR
    select DATABASE_NAME as v_DBNAME
    from DBLIST;

    DECLARE CONTINUE HANDLER FOR NOT FOUND
    SET exitcode = 1;

    EXECUTE IMMEDIATE 'DROP WRAPPER "M_WR"';
    SET STMT1 = 'CREATE WRAPPER "M_WR" LIBRARY ''db2drda.dll'' OPTIONS (ADD DB2_FENCED ''N'')';
    EXECUTE IMMEDIATE STMT1;


    OPEN mycur;
    loop_over_dblist:
    LOOP
    FETCH FROM mycur INTO DBNAME;
    IF exitcode = 1 THEN
    LEAVE loop_over_dblist;
    END IF;


    SET MENTIS_SERVER = 'CREATE SERVER "'||DBNAME||'" TYPE DB2/UDB VERSION ''10.5'' '
    ||'WRAPPER "M_WR" AUTHORIZATION VENKATASD PASSWORD "worldend" '
    ||'OPTIONS (ADD DBNAME '''||DBNAME||''')';

    execute immediate M_SR;

    --Put if condition here as delete will fail if run for the first time and data wont be inserted. Please do the exception handeling

    SET tableExists = (select count(*) from M_M_DAA where SERVER = '''||DBNAME||''');

    IF (tableExists > 0)
    THEN

    execute immediate 'delete from M_M_DAA where SERVER = '''||DBNAME||'''';
    --execute immediate 'delete from M_M_DAA where SERVER = '''||DBNAME||'''';

    ELSE

    set MENTIS_META_DATA = 'INSERT INTO M_M_DAA select '''||DBNAME||''',
    TAB.CREATOR, TAB.NAME as TABLE_NAME, COL.NAME, COL.COLTYPE from "'||DBNAME||'".SYSIBM.SYSTABLES TAB,
    "'||DBNAME||'".SYSIBM.SYSCOLUMNS COL where TAB.name not like ''SYS%''
    and TAB.creator not like ''SYS%'' and TAB.type = ''T'' and COL.TBNAME=TAB.NAME';

    execute immediate M_M_DAA;

    --CALL DBMS_OUTPUT.PUT_LINE(M_M_DAA);

    -- execute immediate 'INSERT INTO M_M_DAA select '''||DBNAME||''', TAB.CREATOR, TAB.NAME as TABLE_NAME, COL.NAME, COL.COLTYPE from "'||DBNAME||'".SYSIBM.SYSTABLES TAB, "'||DBNAME||'".SYSIBM.SYSCOLUMNS COL where TAB.name not like ''SYS%'' and TAB.creator not like ''SYS%'' and TAB.type = ''T'' and COL.TBNAME=TAB.NAME';
    END IF;
    END LOOP loop_over_dblist;
    CLOSE mycur;

    END P1


    Kindly correct me on If Condition on this code.

  3. #3
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    some people prefer to post directly to the forum and hope others will resolve their problem instead of looking/analyzing their problem. If all real programmers would post their small problems to this forum it would be .......
    try at least debug options ..
    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
    Jul 2014
    Posts
    294

    Exception handling for deletion

    I thought of learning from these experts.
    I don't know how to debug, this is my first db2 procedure.
    From now I ll never ever use this forum.
    Good Bye

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
  •