Drop DB2 database if exist

Question

I would like to write db2 command which find out first database exist or not and if exist that data base then drop this database and create a new updated database .

Please help for same

Answer 1

calling this function will let you drop the table if exist:

CALL FNC.DROP_IF_EXISTS('TABLENAME')!

Answer 2

There is not a command or language in DB2 that support that instruction. You have to define what to do when the given name exist as an alias of a database (not the real name of the database), drop the alias? rename it?

The script will look like:

1. List the database directory and filter the name and the alias (db2 list db directory). Filtering can be done in linux with Awk or Grep, in Windows with Filter.
2. If there is a database alias with that name, then YOU decide what to do.
3. Instead if there is a database name with that name, then drop it (db2 drop db xxx)
4. Create the database.
5. If there is an error of existent database in the system (SQL1005N), then catalog it and drop it (db2 catalog db xxx, db2 drop db xxx)
6. Retry the database creation.

Answer 3

First query system table, in db2 for zos table SYSIBM.SYSDATABASE or SYSIBM.SYSTABLES, in db2 LUW version table SYSCAT.tables . If there are rows in query, database exists.

Answer 4

In LUW, you can do:

--#SET TERMINATOR @
BEGIN
 DECLARE TABLE_NOT_FOUND CONDITION FOR SQLSTATE '42704';
 DECLARE CONTINUE HANDLER FOR TABLE_NOT_FOUND;
 EXECUTE IMMEDIATE 'DROP TABLE myschema.mytable';
END @

You can convert this snippet into a function that receives the schema name and table name.