first we are writing something like this
BEGIN ATOMIC
IF EXISTS (SELECT * FROM SYSIBM.TABLES WHERE table_name = 'TEST_TABLE') THEN
DROP TABLE TEST_TABLE;
END IF;
END;
but it does't works in db2 . Error message writes that there is
DB2 Database Error: ERROR [42601] [IBM][DB2/AIX64] SQL0104N An unexpected token "DROP" was not found following "'TEST_TABLE'")
for avoiding from this , we need to create stored procedure in our database
CREATE PROCEDURE SP_DROP_OBJECT( IN statement VARCHAR(1000) )
LANGUAGE SQL
BEGIN
DECLARE SQLSTATE CHAR(5);
DECLARE NotThere CONDITION FOR SQLSTATE '42704';
DECLARE NotThereSig CONDITION FOR SQLSTATE '42883';
DECLARE EXIT HANDLER FOR NotThere, NotThereSig
SET SQLSTATE = ' ';
SET statement = 'DROP ' || statement;
EXECUTE IMMEDIATE statement;
END
So the statement passed in is something like
CALL SP_DROP_OBJECT('table TEST_TABLE') ;
Which executes " DROP TEST_TABLE "
No comments:
Post a Comment