Help with sql script that creates a table if it doesn't exist

[LanaR]

Hi,
I need to create sql script that creates a table if it doesn't exist.

I tried to use

if not exists
(select * from sysibm.systables where name ='mytable'
then
create table mytable ...
...
end if;


It reported problems. The task doesn't seem to be that hard. But apparently there is a problem using
DDL in the scripts. How could this be implemented than?
I would appreciate your help very much.

Thanks in advance.
Lana

[chandu031]

Hi Lana,

If you are doing this inside a procedure then you cannot use static CREATE or ALTER table commands.. Or any DDL for that matter. You should be using Dynamic query for that:

For example :

IF NOT EXISTS (SELECT 1 FROM SYSCAT.TABLES WHERE
TABSCHEMA = <YOURSCHEMA> AND
TABNAME = <YOURTABLENAME>
) THEN
EXECUTE IMMEDIATE 'CREATE TABLE...........'


Hope this is helpful...........