I was working on creating relatively simple DB2 stored procedure. In that procedure, I had to create a temporary table, insert some values in it, and in the next call to the procedure, i needed to delete the temporary table. I searched a lot to find right way of deleting the temporary table but nothing worked.
Please know that when you use just the following syntax, it does not work. DB2 complains when the first time the procedure is compiled, because the temporary table does not exist yet.
// procedure creation, definition, result, etc etc. drop table SESSION.tempresult; declare global temporary table SESSION.tempresult ( FIRST INTEGER, SECOND INTEGER) on commit preserve rows not logged; //Rest of the logic
So what can you do? Following worked for me.
// procedure creation, definition, result, etc etc. DECLARE SQLSTATE char(5); DECLARE v_TABLE FOUND INT DEFAULT 0; DECLARE c_TABLE FOUND CONDITION FOR SQLSTATE '42704'; DECLARE CONTINUE HANDLER FOR c_TABLE_FOUND SET v_TABLE FOUND = 1 DROP TABLE SESSION.tempresult; declare global temporary table SESSION.tempresult ( FIRST INTEGER, SECOND INTEGER) on commit preserve rows not logged; //Rest of the logic
No comments:
Post a Comment