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

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

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

Why don't you create the new tables of the new application in a different schema?

i think you can try to revoke privileges from users on these tables.

How about moving the db to a seperate tablespace and then putting that tablespace for that 7 tables in the quiesced state....

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

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,

Thanks,

I will be testing all these steps on mirror database.


DBFinder