Wednesday 26 October 2011

IBM DB2 useful scripts

If we want to write sql script in db2 that will drop table if exists
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: