Solved

create table if it does not exist

Posted on 2006-11-07
28
5,311 Views
Last Modified: 2008-01-09
I need a DB2 sql script that creates a table only if it does not exist. I do not want to drop the table first.

Example:

IF (MYTABLE exists)
THEN
   -- DO NOTHING
ELSE
   CREATE TABLE MYTABLE (COL1 INT);
   CREATE INDEX MYINDEX ON MYTABLE(COL1);
END IF;

Please provide this sql script. Thank you!!
0
Comment
Question by:mbevilacqua
  • 13
  • 5
  • 3
  • +3
28 Comments
 
LVL 37

Expert Comment

by:momi_sabag
Comment Utility
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
0
 

Author Comment

by:mbevilacqua
Comment Utility
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;
0
 
LVL 37

Expert Comment

by:momi_sabag
Comment Utility
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
0
 

Author Comment

by:mbevilacqua
Comment Utility
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?
0
 
LVL 37

Expert Comment

by:momi_sabag
Comment Utility
db2 does have sql scripts, i just don't think you can invoke them with a single jdbc call
0
 
LVL 45

Expert Comment

by:Kdo
Comment Utility

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
0
 
LVL 13

Expert Comment

by:ghp7000
Comment Utility
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



0
 

Author Comment

by:mbevilacqua
Comment Utility
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.
0
 
LVL 45

Expert Comment

by:Kdo
Comment Utility

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

Good idea.  Tell us how it comes out.....
0
 
LVL 13

Expert Comment

by:ghp7000
Comment Utility
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.
0
 
LVL 27

Expert Comment

by:tliotta
Comment Utility
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
0
 
LVL 13

Expert Comment

by:ghp7000
Comment Utility
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?
0
 

Author Comment

by:mbevilacqua
Comment Utility
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.
0
 
LVL 18

Accepted Solution

by:
daveslash earned 500 total points
Comment Utility

Apparently, I missed this question way back when it cam through. But, I've accomplished this in a stored proc before in the following way:

create procedure mjaTest
result sets 1
language sql
Begin
DECLARE CONTINUE HANDLER FOR SQLSTATE '42710' Delete from MyTable;
CREATE TABLE MyTable (AnInt int);
...
End;

If the Create Table statement thows the '42710' error ("already exists"), then the proc simply deletes all the records in the existing table and moves on.

HTH,
DaveSlash
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:mbevilacqua
Comment Utility
That looks closer to answer I was looking for. I need this stored procedure to work with AS400 edition. Does that?
0
 
LVL 18

Expert Comment

by:daveslash
Comment Utility

Yes, this proc was written for AS/400.

Regards,
DaveSlash
0
 
LVL 27

Expert Comment

by:tliotta
Comment Utility
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
0
 

Author Comment

by:mbevilacqua
Comment Utility
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?
0
 
LVL 18

Expert Comment

by:daveslash
Comment Utility

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
0
 

Author Comment

by:mbevilacqua
Comment Utility
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
0
 
LVL 18

Expert Comment

by:daveslash
Comment Utility

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

-- DaveSlash
0
 

Author Comment

by:mbevilacqua
Comment Utility
I wish it was that simple.

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

Author Comment

by:mbevilacqua
Comment Utility
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
0
 

Author Comment

by:mbevilacqua
Comment Utility
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
0
 

Author Comment

by:mbevilacqua
Comment Utility
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
0
 

Author Comment

by:mbevilacqua
Comment Utility
Is the code for IF..THEN ELSE different between LUW and AS400? How do I translate this IF..THEN ELSE to AS400?
0
 
LVL 18

Expert Comment

by:daveslash
Comment Utility

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
0
 

Author Comment

by:mbevilacqua
Comment Utility
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.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now