List tablespaces work, but not SQL

[dcruncher4]

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.

[Lew]

use the db2bkckp command with -t option. you'll have to parse the
output but it shouldn't too bad

Lew

dc********@aim.com wrote:

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.

[dcruncher4]

Lew wrote:

use the db2bkckp command with -t option. you'll have to parse the
output but it shouldn't too bad

db2bkckp does not work with Legato backups. IBM tech support told
us that. we backup small dbs on tapes and big ones on legato.

[Ian]

dc********@aim.com wrote:

Lew wrote:

>use the db2bkckp command with -t option. you'll have to parse the
output but it shouldn't too bad

db2bkckp does not work with Legato backups. IBM tech support told
us that. we backup small dbs on tapes and big ones on legato.

Then I would suggest that you capture a snapshot of your tablespace
layout prior to doing the backup. (i.e. get snapshot for tablespaces
on DB).

The DB2 module for NetWorker has the ability to run pre-backup scripts
as well as specify additional files/directories that should get saved
with the database backup, so you could leverage these two things to
collect/store this info with your DB2 backups.