Hi,

I have a problem with my procedure. My procedure works fine , but on each & every run . My output count has been increasing & adding to my result. I don't want this to be done,
Kindly correct my code.

CREATE PROCEDURE M_WR (IN USERID VARCHAR(10),IN PASSWORD VARCHAR(10))
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_DA 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 M_SR = 'CREATE SERVER "'||DBNAME||'" TYPE DB2/UDB VERSION ''10.5'' '
||'WRAPPER "M_WR" AUTHORIZATION "'||USERID||'" PASSWORD "'||"PASSWORD"||'" '
||'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_DA where SERVER = '''||DBNAME||''');

call DBMS_OUTPUT.PUT_LINE(tableExists);

IF (tableExists > 0)
THEN

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

ELSE

set M_M_DA = 'INSERT INTO M_M_DA 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_DA;

--CALL DBMS_OUTPUT.PUT_LINE(M_M_DA);

-- execute immediate 'INSERT INTO M_M_DA 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


Please help me to reserve my output with the same count of result on each & every run.

Thanks in advance