create table if it does not exist

hi

do you want to do it all in one script ?
what you can do is

run a script with this command and send the output to a file

your input file will have this command :
select 'your create command goes here...'
from sysibm.sysdummy1
where not exists (select 1 from sysibm.systables where creator='XXX' and name = 'YYY');

then you run from the prompt
db2 -p- -w- -x < "input file name" > "output file name"


run a script that runs the sql statement from the above output file as input (this way if the table exists, the output file would be empty, if the table does not exists the create command will be there)


momi

I am using a jdbc connection to run the scripts, not using the db2 command line processor thingy. We use jdbc since our product supports all DBMS and all platforms, it far easier to use a jdbc connection than to learn all the nuiances of each RDBMS and platform.

Is there a way to do this using a straight SQL script? What about something like this?


IF NOT EXISTS (select 1 from sysidbm.systables where creator = 'XXX' and name = 'MYTABLE')
THEN
   CREATE TABLE MYTABLE (COL1 INT);
   CREATE INDEX MYINDEX ON MYTABLE(COL1);
END IF;

hi

you can just use 2 jdbc calls
the first call will check if the table exists

the second one will create the table

the if logic can be implemented in the language you use

momi

There are lots of other options that we could do, yet I am looking for a solution in a straight forward SQL script.

I would think DB2 can support simple IF THEN logic, how do I use this to write a SQL script?

db2 does have sql scripts, i just don't think you can invoke them with a single jdbc call

SQL does not support if/then/else logic.  PL/SQL does so you can write a stored procedure to implement your solution.

I've never used the *if exists* syntax in a DB2 stored procedure so I don't know if it will work.  You can modify this slightly and query the DB2 view SYSIBM.TABLES for the table name.

  if ((select count(*) from SYSIBM.TABLES where table_schema = 'MY_SCHEMA' and table_name = 'MY_TABLE') = 0)


Good Luck,
Kent

if you are going to write a stored proc, the simplest, most fool proof way is to check the sqlcode for existence.
select * from syscat.tables where tabname="<checking for existence of table name here>" and tabschema="<schema name owner of table being checked for>"
if the sqlcode=0 then the table exists
if the sqlcode=100 then the table name is not found
if the sqlcode is something else, raise an error message and terminate the procedure
if sqlcode=100
create table etc etc
create index
insert
update
call another procedure
do whatever else you need to do
procedure end

Sorry if I seen annoyed, but posts where people just write out some code and then state "don't know if it will work, Good Luck!" offer very little value...

How about creating the procedure, testing it, and then posting? That adds value.

>>How about creating the procedure, testing it, and then posting?

Good idea.  Tell us how it comes out.....

could you mention the OS platform you are using?
I think youll agree that the scope of this exchange is to get you pointed in the  right direction with the correct information so that you can obtain the result you want.
If you look at the other areas of EE where code is involved, you will see that very rarely is a complete solution offered as an answer to a question. Not only is it not really all that practical to do so, it doesnt really add to your knowledge if somebody says , here,implement this and it will work. It may very well work, but not in the way you intended, thats why you have to develop the solutions from the answers posted.
If you are stuck and cant develop the solution, then I would suggest posting another question, or adding more comments to already open question, with the relevant problem. It has to be done in a step by step manner, there are just too many variables to make a 'all at once' answer possible.
So, if you are saying you dont know how to create stored procedures, but you want to make the stored procedure your preferred solution, then it is up to you to learn how to create stored procs, not up to the experts to do it for you.

Why not simply create the table and ignore errors? It'll either exist and CREATE TABLE will tell you so; or it'll get created.

Tom

I just had an idea that you may find useful. I think you should abandon the idea of trying to pre configure a database for your end users, ie, trying to set up the bufferpools, tablespaces, index management etc etc. There are just too many variables involved to make that task worthy of your time, and this is precisely why the job of DBA was created. That being said, probably many of your end users wont have a DBA or cant afford one, so you have to do something, and here is my
suggestion.
If you look in the bin directory of the sqllib directory, there is a dll there named db2sampl. This creates the SAMPLE database that is provided by IBM. Why not use that as a template for your end users?

Not sure I understand your post in this question ghp7000. I need a table to be created if it does not exist. How does running the db2sampl DDL help with that?

I cannot ignore the error if the table already exists. This one table is part of a massive installation of DDL scripts, all run through a JDBC program. We have the ability to "continue on error". Yet it would be cleaner to not have this error. We generated a report at the end of the installation run that reports any errors that occurred during installation. Not having this "table already exists error" would allow the end-user to know with certainly if errors did or did not occur.

That looks closer to answer I was looking for. I need this stored procedure to work with AS400 edition. Does that?

Yes, this proc was written for AS/400.

Regards,
DaveSlash

mbevilacqua:

You said: "I cannot ignore the error if the table already exists."

Sorry I was misleading. I should have been clear and described that you could run your DELETE even if the error occurs, in effect 'ignoring' the error. But Dave provided a precise description that illustrates well.

Strange that I don't recall seeing that you had replied way back in November.  I apologize for not clarifying then. It's good that Dave made an effort to revisit old questions. He deserves credit.

Tom

Ok, this is just bizarre. If the table MyTable already exists, it returns an error when I try to install the store procedure.

The name of the object to be created is identical to the existing name "MYUSER.MYNEWTABLE" of type "TABLE".

Does DB2 not allow the procedure to be created if the table exists?

You're right. That's bizarre.

If I understand you correctly, you're saying that your stored procedure and your table have the same name. That's perfectly legal (although it's pretty confusing).

Can you create ANY stored procedure?  Even with a different name?

-- DaveSlash

No, the stored procedure has a different name from the table.

BUT, it seems the stored procedure will not compile unless the table already exists. I think this line is the problme:

DECLARE CONTINUE HANDLER FOR SQLSTATE '42710' Delete from MyTable;

DB2 tries to validate that this table command will work, which it will not since the table does not exist when the procedure is first created....

I am trying to wrap the sql in a variable and then calling EXECUTE IMMEDIATE

How about just creating the table manually just for the compile? You can dump it after it compiles.

-- DaveSlash

I wish it was that simple.

This is part of a software package that is installed by our customers, not an internal database.

I got it to work as follows on DB2 for LUW, now I am testing on AS400:

CREATE
PROCEDURE CREATEIFNOTEXISTS(IN useTablespace_fl CHAR(1))
    BEGIN
        DECLARE l_sql VARCHAR(2000);
        DECLARE CONTINUE HANDLER FOR SQLSTATE '42710' SET l_sql = NULL;
        DECLARE CONTINUE HANDLER FOR SQLSTATE '42889' SET l_sql = NULL;
        SET l_sql  = 'CREATE TABLE MYTABLE ...';
        IF useTablespace_fl = 'N' THEN
            --DO NOT INCLUDE TABLESPACE  
        ELSE
            SET l_sql = l_sql ||' IN THEDATA INDEX IN THEINDX LONG IN THEBLOB';
        END IF;
        EXECUTE IMMEDIATE l_sql;
        SET l_sql = 'CREATE UNIQUE INDEX MYTABLE_PKX...';
        EXECUTE IMMEDIATE l_sql;
        SET l_sql = 'ALTER TABLE MYTABLE ADD CONSTRAINT MYTABLE_PKX PRIMARY KEY...)';
        EXECUTE IMMEDIATE l_sql;
    END

I get this compile error on AS400:

ILLEGAL USE OF KEYWORD BEGIN;  TOKEN IS NO NEW NOT OLD NULL ALLOW READS CALLED COMMIT DBINFO WAS EXPECTED

Added these two lines to the script before BEGIN:
result sets 1
language sql

Now I get this error:
ILLEGAL USE OF KEYWORD ELSE;  TOKEN ( IF GET SET CALL CASE DROP GOTO LOCK OPEN WITH ALTER CLOSE WAS EXPECTED

Is the code for IF..THEN ELSE different between LUW and AS400? How do I translate this IF..THEN ELSE to AS400?

Since your IF clause has no content, change it to:

IF useTablespace_fl <> 'N' THEN                              
   SET l_sql = l_sql ... blah blah blah
END IF;

Also, since AS/400's have no concept of tablespaces, you may want to study the AS/400 syntax of CREATE TABLE.

-- DaveSlash

Got it working for both LUW and AS400. Same procedure, only differ on whether to add the tablespace clauses. The FOR SQLSTATE clause was the trick. Thanks Dave.