Results 1 to 6 of 6

Thread: Moving data

  1. #1
    Join Date
    Apr 2013

    Unanswered: Moving data

    Hi all,

    I am quite confused in moving data among tables, can u pls brief how to load data in another table if source table contain column with type 'Generated as Identity'.


  2. #2
    Join Date
    Feb 2004
    In front of the computer
    Provided Answers: 54
    If only the source table has the generated as identity property, no problem.

    If the destination table has the generated as identity by default property, just insert the data that you want and pray that there are no collisions with existing data. Always do a SELECT before you try the INSERT just to avoid problems.

    If the destination table has the generated always as identity property, then I don't know of a way to work around that. Maybe someone else can help with that.

    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Apr 2006
    Provided Answers: 11
    for import and load you can specify modified by .. generatedignore....and others
    or alter table ... generated ...use load/import and reset
    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

  4. #4
    Join Date
    Apr 2013
    Thanks all

  5. #5
    Join Date
    Jan 2009
    Zoetermeer, Holland
    Generated columns are often used as PK and therefore used as FK's in other tables. Therefore I propagate the "modified by identityoverride" syntax in the load. But be ware: DB2 will reject this syntax when the table does not contain any generated columns (yes, very annoying) so check here syscat.colidentattributes first to see if your schema/table has a match.
    And do not forget:
    nwvalue=$(db2 -x "select digits(max(${col}) + 1) as nwvalue from  ${schema}.${tbl}")
    db2 -v "alter table ${schema}.${tbl} alter column ${col} restart with ${nwvalue}"
    afterwards for each column you find there.
    Somewhere between " too small" and " too large" lies the size that is just right.
    - Scott Hayes

  6. #6
    Join Date
    Jul 2011
    LOAD FROM <filename> OF del modified by identityoverride insert INTO <tablename> NONRECOVERABLE"
    after this may be your table will be in check pending state. So you have to run
    db2 "set integrity for <tabname> immediate checked"

Posting Permissions

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