Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2003
    Posts
    4

    Unanswered: Creating DB objects if they don't exist

    Hi,

    In SQL Server I am able to create a .sql script for creating tables, with statements like:
    IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[MySchema].[MyTable]') AND type in (N'U'))
    BEGIN
    CREATE TABLE [MySchema].[MyTable](
    [Number] [int] ,

    (
    [Number] ASC
    )
    )
    END

    This ensures that I can run the script daily and any new tables added in the script get created without affecting my original tables.

    How can I do this in DB2? Does DB2 support having CREATE statements in an IF statement?

    Thanks,
    Yash

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    you could just execute the create table stmnt
    if table exists you get sql code indicating already exists -- will not be executed
    do not include any drop stmnt
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

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

    Assuming you have db2 9.7 or newer:
    Code:
    begin
      if (not exists (select 1 from syscat.tables where tabschema='MYSCHEMA' and tabname='MYTABLE')) then
        execute immediate 'create table MYSCHEMA.MYTABLE (number int) in userspace1';
      end if;
    end/
    You can place this code into some script.sql and run it using the DB2 Command Line Processor for example:
    Code:
    c:\> db2 connect to mydb user ... using ...
    c:\> db2 -td/ -vf script.sql -z script.log
    Regards,
    Mark.

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
  •