Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004

    Unanswered: Redirected Restore with many tablespaces

    Hi there,

    some days ago I posted a thread about restore troubles. Now my restore worked fine.

    I have questions about redirected restore and tablespace containers.

    In production I have seven filesystems with 750 GB altogether which all
    begin with "/p/.../.../...".
    In test the filesystems begin with "/t/.../.../...".

    I used for my restore symbolic links where "/p" points to "/t". But after restore
    the symbolic links had to be deleted due to HA reasons.

    Now all tablespaces are offline as the database searches the tablespaces in "/p".

    Is it possible to update all container names in some system tables?
    (may be a deep risk but it's just for the test).

    I know that I may use redirected restore but does anyone know how to redirect approx. 1700 tablespaces which are spread through seven filesystems?

    I would be glad if someone has some hints.

    Thanks in advance.



  2. #2
    Join Date
    Nov 2002
    I would copy out 1 tablespace and try running the db2relocatdb command to see if it would update the information correctly. You could restore the database, we have a script which creates a restore.clp file for us, by gathering the information about the tablespaces. Then we just edit it and change the prod to dev few other minor changes.
    David Quigley

  3. #3
    Join Date
    Sep 2003
    If the container name is derived from the tablespace name you can write a SQL to generate the redirected restore "set tablespace containers for 0 using(file 'xxx' ,ppp )"; you still have the problem of filling in the pages for the size ppp. You could list container NNN show detail and extract container name and page size; it will be complicated script; but well worth it. The problem is there is no system table containing container name and page size that you can query with SQL.

  4. #4
    Join Date
    Aug 2004

    Smile Redirect Restore

    Redirect restore is a 3 step process as you may be aware.
    In the second step we need to set the containors.
    Here with attached the script which generate the set statements for redirected restore.

    You can run the script in the server from where you have taken the backup.
    (I assume you are restoring in to different server).

    case tbspacetype
    when ('S') then
    'set tablespace containers for '
    concat char(tbspaceid )
    concat ' using (path '''
    concat s.tbspace concat ''');'
    'set tablespace containers for '
    concat char(tbspaceid )
    concat ' using (file '''
    concat s.tbspace
    concat ''' '
    concat coalesce(char(tmp.size),'5000')
    concat ');'

    from syscat.tablespaces s
    left outer join
    (select sum(npages) + 2500 as size, tbspace from syscat.tables group by tbspace
    union all
    select sum(nleaf) + 2500 as size, index_tbspace as tbspace from syscat.tables t, syscat.indexes x
    where t.tabschema=x.tabschema and t.tabname = x.tabname
    group by index_tbspace
    ) as tmp
    on s.tbspace = tmp.tbspace
    order by tbspaceid

Posting Permissions

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