Results 1 to 10 of 10
  1. #1
    Join Date
    May 2011
    Posts
    15

    Unanswered: how to inert values into generated always column

    Hi all,

    I have a table with generated always column, I want to insert data itno the table but I can't as one of the column is generated always.

    I am trying to use OVERRIDING USER VALUE but it is giving syntax error, so please guide me how to use this cmd. It i very imp foe me.

    I am using db2 version 8 and OS is Window.

    Thanks..............

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    You could use the LOAD utility with the generatedoverride modifier.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Did you specified a column list after table name in your INSERT statement?

  4. #4
    Join Date
    May 2011
    Posts
    15
    I have two table t1 and t2.
    describe table t1

    Column Type Type
    name schema name Length Scale Nulls
    ------------------------------ --------- ------------------ -------- ----- ------
    CHARCOL1 SYSIBM VARCHAR 5 0 Yes
    ID SYSIBM INTEGER 4 0 Yes


    and t2 table is

    Column Type Type
    name schema name Length Scale Nulls
    ------------------------------ --------- ------------------ -------- ----- ------
    CHARCOL2 SYSIBM VARCHAR 5 0 Yes
    ID SYSIBM INTEGER 4 0 No

    the id column in t2 table is generated always...
    I am trying the command

    insert into t2 overriding user value select * from t1;

  5. #5
    Join Date
    May 2011
    Posts
    15
    Can I use import utility to insert.

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You can solve the problem by changing the GENERATED ALWAYS to GENERATED BY DEFAULT.

    Code:
    alter table t2 alter column id set generated by default
    Andy

  7. #7
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Go with Andy's advice! And to add to it, Never use generated always. The generated by default will perform the same way, but it allows you to replace data that may have been deleted and retain the same number you had previously. It allows you to easily copy the data to put into a test system and retain the RI that may be based on that identity column, etc...

    Dave

  8. #8
    Join Date
    May 2011
    Posts
    15
    Thanks all

    I tried that , I altered table as generated by default but the data is not inserting sequentially so that I am trying this Overriding option............

  9. #9
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    What do you mean with "not inserting sequentially"? A table has no order of its rows unless you use an ORDER BY clause. So you could sort on the generated column.

    If you are referring to gaps in the generated values, you cannot avoid that anyway: a transaction could do an insert and then rollback. The generated value is used but doesn't occur in any row in the table after the rollback. (If you rely on there being no gaps, you should revisit your design - usually, it has a problem. I know that there are sometimes legal requirements that you must not have gaps. In that case, generated values are not the way to go.)
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    The basic question is why did you designed tables such way?

    Is it not enough to add charcol2 to t1 and specify GENERATED ALWAYS(or BY DEFAULT) for id in t1?

    Even if you want to separate tables by some reasons(for example, to reduce lock contensions),
    why didn't specify GENERATED ALWAYS(or BY DEFAULT) for id in t1 and insert to t2 from t1?

Posting Permissions

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