Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2014
    Posts
    2

    Unanswered: Create table if it doesnt exist in db2

    Hi all,
    Looking for some assistance with a db2 issue i am having. The issue is probably me not having the knowledge as opposed to a db2 problem -
    Anyhow I am using db2 version 10. I am making calls from within java so i am using
    IBM DB2 JDBC Universal Driver Architecture version 3.66.46.

    Basically i am trying to create a table if it doesnt exists and then insert data into the table.
    I am doing a similar thing for mysql:
    CREATE TABLE IF NOT EXISTS versions (
    Name VARCHAR(255) NOT NULL, .........
    )
    INSERT INTO versions values('component-name') etc etc

    Can anyone help me in generation a command that will do this for me in db2 ?
    I have tried many things ex
    begin
    execute immediate 'create table ..........'
    exception etc


    But nothing seems to work. Am i missing something ?
    My goal is theory is simple - if table exists insert something else create table and insert.

    Any help is much appreciated
    Clive

  2. #2
    Join Date
    May 2005
    Posts
    29
    DB2 syntax doesn't have the IF NOT EXISTS in the CREATE TABLE statement, you might want to do the checking before, if you want the similar functionality. An option would be to query to syscat.tables

    Here is the create table syntaxIBM DB2 10.1 for Linux, UNIX, and Windows

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    thats odd, as IBM and Apache claim that Derby is DB2 compatable, and Derby definitely supports the if not exist syntax.....

    Reading syscat would be a smarter call (assuming you have the privileges to do so), the only other approach I can think of would be to attempt to read the table and trap an error. not an elegant way of doing things
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi Clive,

    try this:
    Code:
    begin
      declare continue handler for sqlstate '42710' begin end;
      execute immediate 'create table versions(name varchar(255)) in userspace1';
      execute immediate 'insert into versions values (''component-name'')';
    end/
    Regards,
    Mark.

  5. #5
    Join Date
    Mar 2014
    Posts
    2

    Question answered

    Thanks to all
    Mark.b code worked perfectly

    Really appreciate the help folks
    Clive

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by healdem View Post
    thats odd, as IBM and Apache claim that Derby is DB2 compatable, and Derby definitely supports the if not exist syntax
    That does not mean that DB2 is Derby compatible.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Tags for this Thread

Posting Permissions

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