It worked after I manipulated the files for the new environment, however I noticed that the schema for the tables is still the old instance name ie
source server instance name - avcpb1
source server database name - avc
target server instance name - avcd2
target server database name - avc
but when I check the tables by doing a db2 list tables for all I see the following:
Table/View Schema Type Creation time
------------------------------- --------------- ----- --------------------------
ADVISE_INDEX AVCPB1 T 2004-07-16-184.108.40.2062654
ADVISE_WORKLOAD AVCPB1 T 2004-07-16-220.127.116.117523
APPLICATION_GROUP AVCPB1 T 2003-08-28-18.104.22.168362
ARCHIVE_SITE AVCPB1 T 2003-08-28-22.214.171.124912
But I would like to see the tables owned by the user avcd2, is there any quick way of changing the schema during the redirected restore or afterwards?
A redirected restore is just that - a restore. It will re-create the original database in different containers but won't change the contents of the database itself.
On the other hand, schema name in general has nothing to do with instance owner userid, or any other userid. It just so happens that if you fail to specify schema name while creating a table DB2 will choose one for you, which happens to be your userid. It might have been something like IBM0012345.
As a result, I guess the only way to change schema name of an existing table is to drop and re-create the table...
you can give the right access(select,insert etc) to avcd2 on all tables; then if your program refers to just tablename, you can set CURRENT SCHEMA = "avcpb1" ; CURRENT SCHEMA is an environmental variable; IF it refers to
avcpb1.tn1 then you can create an alias ; "create alias avcd2.ADVISE_INDEX for AVCPB1.ADVISE_INDEX " and so on ; now when you say avcd2.ADVISE_INDEX in any sql, db2 will look for AVCPB1.ADVISE_INDEX ; typically, if it is an ERP package, there is an interface file giving the schema name of the tables. you have to put "AVCPB1" for the schema name