Validating Table Existence on Drop

alwick · #1 (**permalink**) 10-13-03, 17:04

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

n_i · #2 (**permalink**) 10-13-03, 21:36

Quote:

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

alwick · #3 (**permalink**) 10-13-03, 23:30

Quote:

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.

n_i · #4 (**permalink**) 10-14-03, 09:36

Quote:

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='...'

alwick · #5 (**permalink**) 10-14-03, 09:54

Quote:

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

n_i · #6 (**permalink**) 10-14-03, 10:51

Quote:

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 ...");

alwick · #7 (**permalink**) 10-14-03, 11:05

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

Internet Services

Web Development

Digital Marketing

Consumer Tech