Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2009
    Zoetermeer, Holland

    Unanswered: I do not LIKE this :(

    Just take a look at my tiny little test script:
    drop   table orig0_table;
    create table orig0_table
          attrib#1 int  not null generated always as identity primary key
        , attrib#2 date not null default current_date
        , attrib#3 date not null 
        , attrib#4 date generated always as (case when year(attrib#2) < 1980
                                                 then attrib#2 + 1 year
                                                 else attrib#3 - 3 months
    insert   into orig0_table (attrib#2 , attrib#3) values ('1959-04-26' , '1985-09-22'  );
    insert   into orig0_table (attrib#2 , attrib#3) values ('1985-09-22' , current_date  );
    insert   into orig0_table (attrib#2 , attrib#3) values ('2000-01-01' , '2112-12-21'  );
    insert   into orig0_table (attrib#3) values ( '1111-11-11');
    insert   into orig0_table (attrib#3) values ( '1212-12-12');
    select * from orig0_table;
    drop   table copy1_table;
    create table copy1_table
           like  orig0_table;
    declare   x1    cursor for select *  from orig0_table                ; 
    load from x1 of cursor     insert    into copy1_table nonrecoverable ;
    insert   into copy1_table (attrib#3) values ( '111-11-11');
    insert   into copy1_table (attrib#3) values ( current_date);
    select * from copy1_table;
    Try for yourself and see whay happens.... The inserts in copy1_table are not executed. Why?
    The "generate always" logic did not survive the "create table A like B" syntax. Okay, it was a nonsence rule, but DB2 accepted it for the orig0_table, didn't it?
    attrib#4 is not a generated field anymore but a "normal" column which did remember the "not null" property.

    Right: when you dig deep into the documentation I do not doubt that this is "expected behaviour" so I will not issue a PMR, but I do not LIKE this LIKE syntax which forgets parameters without any warning

    You all know this already, but when there is only 1 soul out there who did not realize this (like me): this thread is for you.

  2. #2
    Join Date
    Jan 2007
    Jena, Germany
    The manual says that the column names and data types are copied, together with nullability and, if desired, identity clauses. It doesn't mention any generation clauses. CREATE TABLE - IBM DB2 9.7 for Linux, UNIX, and Windows
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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