Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655

    Unanswered: Rename Tables -- How ?

    Hi Guys,

    At my workplace we have 7 tables that can not be dropped for now.
    Dev team wants these renamed to hide these from running appl.

    This is due to appl switch over.


    Is there a good way of renaming tables keeping all other properties except the name.

    OR, Can I hide these tables for now - if so, how ?

    Thanks
    DB Finder
    Last edited by DBFinder; 03-12-09 at 17:34.

  2. #2
    Join Date
    Mar 2006
    Posts
    104
    Hi,

    Use rename command. Refer DB2 manuals for the syntax. Make sure you stop all applications that use the table before renaming the table.

    Harikumar

  3. #3
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Is that so simple !

    No, I can't believe. Here is why :

    7 tables as a group. Just imagine these might be having refrential constraints, triggers and views. Having any of these will disallow rename.

    I posted here to find a workaround. Tables cannot be dropped at this stage because new application and new tables taking their place have to be tested before old are dropped.

    So some how if it is possible to hide them or make all disabled by some way so that these can be revived in case new setup is not satisfactory.

    I am not sure at his point how they are dependent among themselves.

    I am working hard to find a way to make these 7 tables invisible so that new setup cannot see these. So far no solution.

    Please help !

    Thanks
    DBFinder

  4. #4
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    Why don't you create the new tables of the new application in a different schema?

  5. #5
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    i think you can try to revoke privileges from users on these tables.
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  6. #6
    Join Date
    May 2007
    Location
    somewhere in dbforums
    Posts
    221
    How about moving the db to a seperate tablespace and then putting that tablespace for that 7 tables in the quiesced state....
    IBM Certified Database Associate, DB2 9 for LUW

  7. #7
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Creating New Schema and moving db to it -- No reason follows

    New tables in New schema - No because the developers decided to create 3 new tables and the old app and new app work together for a month to Synch new tables to current state. Now the tables (old set (7) and New set (3) ) are up and fully synched, now it is time to stop using old ones for two weeks. these will stay with same config but no access for next two weeks.

    Well I have decided to do one of two
    1. Set Integrity off for these 7 tables.
    or
    2. Revoke privileges for main user ( old app uses main user)

    I have 2 more days to it actually

    Any suggestion or warning before I do it ( it is in prod !!) ??

    Thanks guys for your effort - I will feed back
    DBFinder

  8. #8
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    I would
    0. check the catalog for existing grants, views, indexes, and constraints where these tables are involved in. (SELECT ... FROM SYSCAT.*)
    1. revoke all authorizations from the tables
    2. remove all views, indexes, check constraints and referential constraints (also from other tables pointing to one of these 7)
    3. Meanwhile keeping the necessary "alter table" and "create view" and "grant" statements, in case you have to restore everything
    4. RENAME TABLE current_name TO new_name
    5. Create the new tables etc.

    Note that authorizations and indexes are transferred by the rename statement to the newly named tables, so removing grants and indexes is maybe not necessary.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  9. #9
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Peter,

    Thanks,

    I will be testing all these steps on mirror database.


    DBFinder

Posting Permissions

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