Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2012
    Posts
    3

    Lightbulb Unanswered: Backup only table using create like

    just fews back i logged in DB2 not so much experience. I recieved request from client they will add some column in table after that, they will do some testing . Once test over we need to rollback. So i did

    1. create table <newtabname> like <oldtabname>
    Once test over..
    2. drop old tabname
    3. rename new table by oldname
    4. import data to newtab.

    AFter this what happend , when application owner verifying data, they getting error of permission on views and etc..

    Just want to know where i did mistake, why permission was missed. You feedback is valueable for me..

  2. #2
    Join Date
    Apr 2012
    Posts
    1,034
    Provided Answers: 18
    You left out one step: you must GRANT the permissions on the new table that are the same as those that previously were on the old table.

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    ... and you should make sure that referential constraints referring to the old table are now re-defined to refer to the new table. Likewise, have a look at other dependent objects like views, packages, UDFs, ...

    Depending on your platform and version of DB2 you are using, you may want to consider ALTER TABLE ... DROP COLUMN ... instead of the much more complex work-around to address the requirement ALTER TABLE - IBM DB2 9.7 for Linux, UNIX, and Windows

    If that doesn't work, it may also be better to create a backup of the whole database and set up a test system that way, do the tests and leave the production environment untouched. You know... the usual precautions.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    Jul 2012
    Posts
    3
    First i would thanks for your quick response.
    I checked my DDL stmt and i found constraint and permission on view was missing may be the DDL which i have generated is not the correct ways, Is there any other way to do this task except backup and restore.

    Drop column i can't use because during testing they inserted some data into the table that they don't want.

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    If you use the db2look command, with the correct options, you can get all the DDL for a table and the permissions, and then manually change the table name in the script. Consult the documentation on the db2look command for more information.

    As noted above, "create table like" does not reproduce all the DDL or Grants you need to completely replicate a table definition.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by rinks_dba View Post
    Drop column i can't use because during testing they inserted some data into the table that they don't want.
    Well, I don't understand why you do tests on the real data instead a copy, but I guess you have a very good reason for doing so...
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    Oct 2009
    Location
    221B Baker St.
    Posts
    486
    Well, I don't understand why you do tests on the real data instead a copy, but I guess you have a very good reason for doing so...
    Most kind, you are. . .

    Some of my clients have terminated people for experimenting with the Production envisonment.

    I guess if it worked, they wouldn't call it a test. . .

Posting Permissions

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