DB2 8.2.3
the task I have is to write a script to restore a database from a
backup on to another machine.
the backup can be as old as seven years. The container layout on the
machine to be restored
can be totally different from the machine which was backedup. So we are
going with redirect
restore.
I plan to write a script in perl to change all containers from the
original path to the new
path and then complete the redirect restore.
The first task is to get the information about the tablespaces and
container
as it existed on the backup image. It seems the only way is to do the
following:
db2 restore db dbname from /dev/rmt/0c taken at yyyymmdd into tmpdb
redirect without prompting
db2 list tablespaces
db2 list tablespace containers for 5 show detail
....
what it does is to create a new tmp db in an inconsistent state and
leaves my current db2 session connected to that. the next two lines
(list tablespaces) shows me the information
about tablespaces and containers as it existed on the backup image.
that information
can be saved and after that it is easy to reconstruct all required
information for the redirect restore.
Then we decided to not use list tablespaces command, but use perl DBI
as it makes the
code easier to maintain. The SQL for that is
select tbspaceid,tbspace
from syscat.tablespaces ;
select
tablespace_id,container_id,total_pages,substr(cont ainer_name,1,40)
from table (snapshot_container('',-1)) as A
order by tablespace_id,container_id ;
Now here is the problem. Db2 does not allow any SELECT statment on the
tmpdb after it
got created by the db2 restore db command. It tells
SQL3022N An SQL error "-1350" occurred while processing the SELECT
string in
the Action String parameter.
SQL1350N The application is not in the correct state to process this
request.
Reason code="3".
Why is DB2 allowing list tablespaces to work, but not the equivalent
SQL command?
IMO this is annoying even though I can understand why this is
happening. I think list tablespaces
directly goes to the NODE directory and reads it from the catalog files
created there, bypassing
the engine.
Anyhow, solutions from anyone?
TIA.
