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.

Regards,

Oktay

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.

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.

Hi,
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).


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

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
;