Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2002
    Posts
    3

    Question Unanswered: Checking for the existence of a table.

    I need to check for the existence of a table prior to dropping it in my script. I tried the following.

    if ((select count(*) from syscat.tables where TABNAME='ORDER_ITEMS' AND TYPE='T') > 0) then drop table ORDER_ITEMS;

    The error message I received was:

    if ((select count(*) from syscat.tables whe" was found following "BEGIN-OF-STATEMENT". Expected tokens may include: "<space>". SQLSTATE=42601

    Any suggestions would be appreciated.

  2. #2
    Join Date
    Sep 2002
    Posts
    456

    Re: Checking for the existence of a table.

    Are you doing it all in staright SQL? If so, then this programming construct might now work...

    Paul
    Originally posted by cmoore
    I need to check for the existence of a table prior to dropping it in my script. I tried the following.

    if ((select count(*) from syscat.tables where TABNAME='ORDER_ITEMS' AND TYPE='T') > 0) then drop table ORDER_ITEMS;

    The error message I received was:

    if ((select count(*) from syscat.tables whe" was found following "BEGIN-OF-STATEMENT". Expected tokens may include: "<space>". SQLSTATE=42601

    Any suggestions would be appreciated.

  3. #3
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    You can't do that in a SQL script (or am I missong something?)

    If you're on a Unix box, you could do something like

    db2 "select tabname from syscat.tables where tabname = 'YOURTABLE' " 1>&2 > /dev/null && db2 "drop table YOURTABLE"

    i.e. if the first db2 call returns true (table found) process the second db2 command (drop the table)

  4. #4
    Join Date
    Dec 2002
    Posts
    3

    Re: Checking for the existence of a table.

    Originally posted by dollar489
    Are you doing it all in staright SQL? If so, then this programming construct might now work...

    Paul
    I attempted to run this sql in the command center and received the same error. Sorry, I should have documented what I meant by script. I have a file containing sql statements that are read in via a java class which then passes them along to the db2 jdbc driver.

    I'm using the db2 version 7.2 fp 8.
    Last edited by cmoore; 12-11-02 at 12:14.

  5. #5
    Join Date
    Dec 2002
    Posts
    3

    Question Re: Checking for the existence of a table.

    I ended up placing the drop table statements in a stored procedure to get around my problem because the IF syntax I stated in a previous post works, but only if its inside of a stored procedure. Here is the procedure def.

    CREATE PROCEDURE DropTablesAndProcedures ( )
    LANGUAGE SQL
    ------------------------------------------------------------------------
    -- SQL Stored Procedure
    ------------------------------------------------------------------------
    P1: BEGIN
    IF ((select count(*) from syscat.tables where TABNAME='ORDERS' AND TYPE='T') > 0) THEN drop table ORDERS; END IF;

    IF ((select count(*) from syscat.procedures where PROCNAME='SP_TESTPLAN1') > 0) THEN drop procedure SP_TESTPLAN1; END IF;
    END P1

    My issue now is I want to add a drop procedure call to the stored procedure but it balks at this syntax. The error I receive is [IBM][CLI Driver][DB2/SUN] SQL0104N An unexpected token "drop procedure" was found following "ESTPLAN1') > 0) THEN". Expected tokens may include: "<psm_labellable_stmt>". I don't understand what is meant by <psm_labellable_stmt> in the error description. I'd appreciate if anyone can enlighten me on what I'm doing wrong.

Posting Permissions

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