Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2004
    Location
    Inside Intel
    Posts
    165

    Question Unanswered: Ms-sql 'if... Exists' Equivalent In Db2

    Hi all,

    I want to do something like this in DB2.

    MS-SQL:
    IF EXISTS (SELECT * FROM sysobjects WHERE name = 'TEST')
    DROP TABLE dbo.TEST;

    Thanks,
    Newbie

  2. #2
    Join Date
    Aug 2004
    Location
    India
    Posts
    13
    Hi there,

    You can do the following

    IF EXISTS (SELECT * FROM sysibm.sysobjects WHERE name = 'TEST')
    then
    DROP TABLE dbo.TEST;
    end if ;


    ciao

    Falcon_daown

  3. #3
    Join Date
    Jun 2004
    Posts
    57

    It doesn't works

    Unfortunately it doesn't work. I try to execute a string in which to drop the table but I notice that in a string you cannot drop a table.
    And the table is named sysibm.systables not sysobjects (this is the sql server system table)

  4. #4
    Join Date
    Apr 2004
    Location
    Inside Intel
    Posts
    165
    Thank you AStefan, I had been tied up with other issues and i had not tried getting this to work. Actually the situation i am in is that it would be nice if i could drop stored procedures (if existing) before i want to create them.

    I tried:

    IF EXISTS (SELECT name FROM syscat.procedures WHERE procname = 'sp_test') THEN
    DROP Procedure sp_test;
    END IF;

    Others,
    Any pointers? Thanks in Advance.

    Newbie

  5. #5
    Join Date
    Jun 2004
    Posts
    57

    Mabe tricks

    I have a similar problem but in Db2 it is difficult to drop if exists an object (table, view or whatever). This needs appear generally in a script.
    My idea was to create one-two procedures who will help me to generate a script with drop procedures.
    I still working.
    The first procedure is DROP_IF_EXISTS. This one inserts in the table OBJECT_NOT_CREATED the existing procedure (defined by name and schema).
    The second procedure is GENERATE_DROP_SCRIPT. It takes from the table OBJECT_NOT_CREATED and fill an output string with the contains of the script (drop procedure 1 @ drop procedure 2 etc.). I hope will helps, I didn't find another way.
    The procedures are the followings:

    CREATE PROCEDURE DROP_IF_EXISTS (IN V_OBJECT_NAME VARCHAR(128),IN V_SCHEMA_NAME VARCHAR(128))
    SPECIFIC DROP_IF_EXISTS
    DYNAMIC RESULT SETS 0
    MODIFIES SQL DATA
    LANGUAGE SQL
    P1: BEGIN
    DECLARE V_COUNT INTEGER DEFAULT 0;

    SELECT COUNT (*)
    INTO V_COUNT
    FROM SYSCAT.PROCEDURES
    WHERE PROCSCHEMA = V_SCHEMA_NAME AND PROCNAME = V_OBJECT_NAME;

    IF V_COUNT = 1
    THEN
    IF NOT EXISTS (SELECT * FROM OBJECT_NOT_CREATED WHERE NAME = V_OBJECT_NAME AND SCHEMA = V_SCHEMA_NAME)
    THEN INSERT INTO OBJECT_NOT_CREATED (NAME, SCHEMA, DATE, TYPE, DROP_CONDITION)
    VALUES (V_OBJECT_NAME, V_SCHEMA_NAME, CURRENT DATE, 'PROCEDURE', 'DROP PROCEDURE ' || V_SCHEMA_NAME || '.' || V_OBJECT_NAME);
    END IF;
    END IF;
    END

    CREATE PROCEDURE GENERATE_DROP_SCRIPT (OUT DROP_OBJECTS_LIST VARCHAR(2000))
    SPECIFIC GENERATE_DROP_SQL
    DYNAMIC RESULT SETS 1
    MODIFIES SQL DATA
    LANGUAGE SQL
    P1: BEGIN

    DECLARE V_ERROR INT DEFAULT 0;
    DECLARE V_DROP_CONDITION VARCHAR(130);

    DECLARE C_DROP_IF_EXISTS CURSOR WITH RETURN FOR
    SELECT DROP_CONDITION FROM OBJECT_NOT_CREATED;

    DECLARE EXIT HANDLER FOR SQLEXCEPTION, NOT FOUND
    SET V_ERROR = 1;

    SET DROP_OBJECTS_LIST = '';
    OPEN C_DROP_IF_EXISTS;

    FETCH C_DROP_IF_EXISTS INTO V_DROP_CONDITION;
    WHILE V_ERROR = 0 DO

    SET DROP_OBJECTS_LIST = DROP_OBJECTS_LIST || V_DROP_CONDITION || ' @ ';
    FETCH C_DROP_IF_EXISTS INTO V_DROP_CONDITION;
    END WHILE;

    CLOSE C_DROP_IF_EXISTS;
    END

  6. #6
    Join Date
    Jul 2004
    Posts
    76

    Try this one!!

    If Exists(select Name From Sysibm.systables Where Name = 'MyTab' And Creator = 'MySchemaOfTable') Then
    Drop Table MySchemaOfTable.MyTab;
    End If;

  7. #7
    Join Date
    Jun 2004
    Posts
    57

    If exists

    Unfortunately it doesn't work. Thanks anyway.

Posting Permissions

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