Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2003
    Posts
    4

    Unanswered: Validating Table Existence on Drop

    I would like to verify that a table or view exists before executing a Drop on the view or table.

    Is there a way to do this without creating a stored procedure?

    the "if" keyword is not available in an sql statement outside of a stored prodedure.

    I am using UDB PE 8.1 on Windows XP using JDBC with the IBM 1.3.1 JDK.

    Thanks,

    -Al Wick
    Last edited by alwick; 10-13-03 at 18:36.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1

    Re: Validating Table Existence on Drop

    Originally posted by alwick
    I would like to verify that a table or view exists before executing a Drop on the view or table.

    Is there a way to do this without creating a stored procedure?

    the "if" keyword is not available in an sql statement outside of a stored prodedure.

    I am using UDB PE 8.1 on Windows XP using JDBC with the IBM 1.3.1 JDK.

    Depending on what you REALLY need, there are two ways to do it:

    1) You don't care if the table exists -- you just need to ensure that your SQL script does not terminate when you try to drop a non-existent table. In that case put this in your sql script:

    update command options using s off;
    drop table schemaname.tablename;
    update command options using s on;

    The above will tell CLP not to terminate in case of a SQL error. Regardless of the result of the DROP command execution of the script will continue.

    2) You actually need to know if the table exist. In that case you'll have to use a batch file, like this:

    db2 drop table schema.tabname
    if errorlevel 0 goto label1
    goto label2
    :label1
    echo table exists
    rem do stuff
    goto endoffile
    :label2
    echo table missing
    rem do other stuff
    :endoffile


    Hope this helps.

    Nick
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Oct 2003
    Posts
    4

    Re: Validating Table Existence on Drop

    Originally posted by n_i
    Depending on what you REALLY need, there are two ways to do it:

    1) You don't care if the table exists -- you just need to ensure that your SQL script does not terminate when you try to drop a non-existent table. In that case put this in your sql script:

    update command options using s off;
    drop table schemaname.tablename;
    update command options using s on;

    The above will tell CLP not to terminate in case of a SQL error. Regardless of the result of the DROP command execution of the script will continue.

    2) You actually need to know if the table exist. In that case you'll have to use a batch file, like this:

    db2 drop table schema.tabname
    if errorlevel 0 goto label1
    goto label2
    :label1
    echo table exists
    rem do stuff
    goto endoffile
    :label2
    echo table missing
    rem do other stuff
    :endoffile


    Hope this helps.

    Nick
    I am trying to execute a batch command from inside a Java program. None of the SQL can fail, throw an exception. I am trying to execute mutliple statements in a single batch transaction to upgrade database tables from one version to another.

    I have a list of views that should be in the system. In some cases the views were never added, so I have to check if they exist and drop them if they do.

    Then upgrade associated table layouts, etc

    Then re-create the views...

    I am doing this inside of a java program so that when a new version of the software is delivered, the database tables are upgraded as part of starting up the system. All table definitions are defined in xml and I can apply different xsl stylesheets to them to generate the sql for the upgrades or for new installs I can create the tables directly.

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1

    Re: Validating Table Existence on Drop

    Originally posted by alwick
    I am trying to execute a batch command from inside a Java program. None of the SQL can fail, throw an exception. I am trying to execute mutliple statements in a single batch transaction to upgrade database tables from one version to another.

    Then what you need is

    select * from syscat.tables where tabschema='...' and tabname='...'
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Oct 2003
    Posts
    4

    Re: Validating Table Existence on Drop

    Originally posted by n_i
    Then what you need is

    select * from syscat.tables where tabschema='...' and tabname='...'
    Yes, but how do I work in the Drop if found clause?

    if (exists( select * from syscat.tables where tabschema='...' and tabname='...' ))
    Drop table ...
    endif;

    The if statement doesn't work outside of a Stored Prodecure. I keep gettingSee details...

    Couldn't execute query:if (exists(select 'A' from syscat.views where tabname='ACCOUNTVIEW')) then drop accountview end if
    COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2/NT] SQL0104N An unexpected token "if (exists" was found following "BEGIN-OF-STATEMENT". Expected tokens may include: "<space>". SQLSTATE=42601

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1

    Re: Validating Table Existence on Drop

    Originally posted by alwick
    Yes, but how do I work in the Drop if found clause?

    if (exists( select * from syscat.tables where tabschema='...' and tabname='...' ))
    Drop table ...
    endif;

    It's java, isn't it?

    PHP Code:
    ResultSet rs stmt1.executeQuery("select * from syscat.tables...");
    if (
    rs.next()) stmt2.addBatch("drop table ...");
    stmt2.addBatch("create table ..."); 
    ---
    "It does not work" is not a valid problem statement.

  7. #7
    Join Date
    Oct 2003
    Posts
    4
    Unfortunately, I was trying to generate the sql from xml and not have to interpret each element...

    Oh, well...

    Other databases support this it would be a nice feature of DB2

Posting Permissions

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