Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2002
    Posts
    13

    Unanswered: problem in SQL script to drop tables

    Hi,

    How can we write a SQL statement to drop a table. The drop statement should not generate any error if the table doesn't exists. I have achieved the same in Oracle by catching the 'table not found exception'

    The code I wrote is as follows:

    CREATE PROCEDURE drop_table(pTableName VARCHAR2)
    IS
    vCommand VARCHAR2(1024);
    table_not_exist EXCEPTION;
    PRAGMA EXCEPTION_INIT(table_not_exist,-942);
    BEGIN
    vCommand := 'DROP TABLE ' || pTableName || ' CASCADE CONSTRAINTS';
    EXECUTE IMMEDIATE vCommand;
    EXCEPTION
    WHEN table_not_exist THEN
    NULL;
    WHEN OTHERS THEN
    RAISE;
    END drop_table;

    Can I do something equivalent in DB2?
    Thanks,
    Siddharth

  2. #2
    Join Date
    Jun 2004
    Posts
    57

    Use of if exists in droping tables if they exists. IS it possible in Db2?

    IS it possible to drop and create a table in sql server style?
    I mean to do somethoing like that

    if exists(select * from sysobjects where name = table_name and ...)
    drop table table_name
    else
    create table table_name

    In Db2 is it possible to do something similar?

  3. #3
    Join Date
    Apr 2003
    Location
    Singapore
    Posts
    59
    Hi,
    I have successfully used the following to continue handling in the procedure when no rows are found. I suspect if u can find out th SQLSTATE for dropping a nonexistant table, you can use the below. Do let me know if it works. To be honest I have not tried it yet.

    P.N: 02000 is SQLSTATE for row not found when a sql query returns no rows


    P1: Begin
    DECLARE MASTER_NOT_FOUND CONDITION for SQLSTATE '02000';

    DECLARE CONTINUE HANDLER for MASTER_NOT_FOUND SET MASTER_EXCEPTION_TMP='T';

    <the sql to drop the table>

    IF MASTER_EXCEPTION_TMP='T' THEN
    <do some processing>
    ELSE
    <do some processing>

    ENd IF
    END P1

    The variable MASTER_EXCEPTION_TMP gets a value of 'T' if the <sql query> returns no rows.

    HTH
    Cheers
    Brat

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    As mentioned above, you need to use "continue handling" in a stored procedure if you want to intercept and act on specific error return codes in your procedure. Otherwise DB2 will exit the routine when it thinks a fatal error has occured.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •