Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2002

    Unanswered: redirected restore

    I used the following guidelines to do a redirected restore.

    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-
    ADVISE_WORKLOAD AVCPB1 T 2004-07-16-
    ARCHIVE_SITE AVCPB1 T 2003-08-28-

    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?



  2. #2
    Join Date
    Jun 2003
    Toronto, Canada
    Provided Answers: 1
    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...

  3. #3
    Join Date
    Sep 2003
    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

Posting Permissions

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