I just restored a tablespace from a backup and tried to roll the tablespace forward. When I entered the rollforward command and specified only the tablespace that was recovered name in the tablespace () part of the comamnd I got the message:

SQL4906N The list of table space names specified is an incomplete set for the rollforward operation.

After this I entered the other tablespace that I knew had indexes to tables in the tablespace I had specified and got the same message. How can you tell which tablespace names should be included in the rollforward command ?

$ db2 "select tabname,tbspaceid,tbspace,index_tbspace,long_tbspa ce
from syscat.tables where tbspaceid =<tablespace id>"

This will shows all the tables that have parts in other tablespaces.
remember that only DMS has parts in different tablespaces.

The tablespace id can be found by list tablespaces command.



regards,

mujeeb

I added all the tablespaces that were returned from running you sql statement, but I received the same error message.

SQL4906N The list of table space names specified is an incomplete set for the rollforward operation.

There must be a bug in db2, because I even added every tablespace name in the database, and got the same error message. I'm using db2 7.1 GA. I'm trying to rollforward to a point in time. Is there a work-around for this 'possible' problem. Has anyone else run into this problem?

Thanks in advance

You can perform PIT recovery until a certain time.. so what time is your cut-off time.

list the commands you are using.also your restore image timestamp

Before rolling a table space forward, invoke the LIST TABLESPACES SHOW
DETAIL command.
This command returns the minimum recovery time, which is
the earliest point in time to which the table space can be rolled forward.

regards,

mujeeb

remember if you have multiple tables then take the max of that time.

regards,

mujeeb

also put the rollforward query status

regards,

mujeeb

I using:

rollforward database records to 2004-07-08-04.00.00.000000 and stop tablespace (OCCSPACE2, OCCIDXSPACE) online


Here is the tablespace info: I'm sure these are the only tablespaces required. Why is db2 telling me otherwise?

Tablespace ID = 1
Name = OCCSPACE2
Type = Database managed space
Contents = Any data
State = 0x0080
Detailed explanation:
Roll forward pending
Total pages = 13172736
Useable pages = 13169664
Used pages = 0
Free pages = 0
High water mark (pages) = 0
Page size (bytes) = 4096 Tablespace ID = 13
Name = OCCIDXSPACE
Type = Database managed space
Contents = Any data
State = 0x0000
Detailed explanation:
Normal
Total pages = 8781824
Useable pages = 8780800
Used pages = 4569472
Free pages = 4211328
High water mark (pages) = 5336704
Page size (bytes) = 4096
Extent size (pages) = 128
Prefetch size (pages) = 1024
Number of containers = 8
Minimum recovery time = 2002-07-23-01.59.08.000000
Extent size (pages) = 256
Prefetch size (pages) = 768
Number of containers = 12
Minimum recovery time = 2004-06-15-17.20.24.000000

Tablespace ID = 1
Name = OCCSPACE2
Type = Database managed space
Contents = Any data
State = 0x0080
Detailed explanation:
Roll forward pending
Total pages = 13172736
Useable pages = 13169664
Used pages = 0
Free pages = 0
High water mark (pages) = 0
Page size (bytes) = 4096
Extent size (pages) = 256
Prefetch size (pages) = 768
Number of containers = 12
Minimum recovery time = 2004-06-15-17.20.24.000000





Tablespace ID = 13
Name = OCCIDXSPACE
Type = Database managed space
Contents = Any data
State = 0x0000
Detailed explanation:
Normal
Total pages = 8781824
Useable pages = 8780800
Used pages = 4569472
Free pages = 4211328
High water mark (pages) = 5336704
Page size (bytes) = 4096
Extent size (pages) = 128
Prefetch size (pages) = 1024
Number of containers = 8
Minimum recovery time = 2002-07-23-01.59.08.000000

I got the same error when I used the statement below, not specifying any tablespace names.

rollforward database records to 2004-07-08-04.00.00.000000 and complete

This is what I will see after the restore
I have a partition db , so take only the node 0 as applicable

[db2dev@aqeel SAMPLE]$ db2 "rollforward database sample query status"

Rollforward Status

Input database alias = sample
Number of nodes have returned status = 4

Node number Rollforward Next log Log files processed Last committed transaction
status to be read
----------- -------------------------- ------------------- ------------------------- --------------------------
0 TBS pending S0000233.LOG-S0000236.LOG 2004-04-06-09.26.42.000000
1 not pending S0000129.LOG-S0000135.LOG 2004-04-06-09.26.42.000000
2 not pending S0000106.LOG-S0000108.LOG 2004-04-06-09.26.42.000000
3 not pending S0000122.LOG-S0000124.LOG 2004-04-06-09.26.42.000000


if it is working instead of pending then
1. cancel the roll forward.
2. restore the tablespaces again.
3. query using the rollforward command.
4. rollforward the tablespaces, using stop(I prefer it)
5. again query it.
6. rollforward with complete command.

regards,

mujeeb

The working will appear only after the rollforward command, if it is unsuccessful as in your case.

regards,

mujeeb

Here is the info from the query status:

rollforward database records query status

Rollforward Status

Input database alias = records
Number of nodes have returned status = 1

Node number = 0
Rollforward status = TBS pending
Next log file to be read = S0072282.LOG
Log files processed = -
Last committed transaction = 2004-07-19-14.28.13.000000

I added a tablespace to the list that got rid of the SQL4906N error above, but know I'm getting the following:

db2 => rollforward database records to 2004-07-13-20.01.18.000000 tablespace (OCCSPACE1, OCCSPACE2, OCCIDXSPACE) online
SQL4908N The table space list specified for roll-forward recovery on database
"RECORDS" is invalid on node(s) "0".

2 of the tablespaces are not in rollforward pending state, but apparently I had to add them in order to get a complete list of tablespaces. i.e. tablespaces which either has indexes or tables in them related to the rollforward pending tablespace.

Thanks

1. Cancel the rollforward on the tablespace that is in pending.

2. restore all the tablespaces from the backup image before the time that you want to rollforward (the min of minimum recovery, as I mentioned before).

3. rollforward all the tablespaces(only the tablespaces that you restored,no addition or no subtraction).

regards,

mujeeb

Would it work if I used the End time of the backup to rollforward ? For example the end time of the backup is 20040708003538. So I run:

rollforward database records to 2004-07-08-00.00.00.000000 and stop tablespace(TBS1, TBS2, TBS3) online

In this scenario I assume all the tablespace min recovery will be < than the backup end time, correct?

you can't give a time less than the minimum recovery time.
The reason is simple, your performed that steps.

1. You performed backup of the tablespace. at time t1.
2. you add a object to that tablespace at time t2.
3. Now you restored and rollforward the tablespaces at time t1, but the
catalog tablespace containes information of that object, but this
object is not in tablespace.

Big error!!!

You can do a test

1. list tablespaces show detail and note down the minimum recovery time.
2. create an object in that tablespace.
3. Now check the minimum recovery time.

regards.

mujeeb