( DB2 v9.5.1 ESE on AIX v6.1 ) the tablespace SYSTOOLSTMPSPACE of one of our DBs get into Drop Pending mode all the time. Could you tell how and what gets it into this state:

db2 list tablespaces show detail
...
Tablespace ID = 4
Name = SYSTOOLSTMPSPACE
Type = System managed space
Contents = User Temporary data
State = 0xc000
Detailed explanation:
Offline
Drop Pending

db2diag -g db=QADB9 -time 2009-06-01
2009-06-03-15.23.13.972957-240 I30126417A526 LEVEL: Error
PID : 1208386 TID : 2475 PROC : db2sysc 0
INSTANCE: db2v9q1 NODE : 000 DB : QADB9
APPHDL : 0-5143 APPID: *LOCAL.db2v9q1.090603192310
AUTHID : DBBACKUP
EDUID : 2475 EDUNAME: db2agent (QADB9) 0
FUNCTION: DB2 UDB, buffer pool services, sqlbGetPoolStats, probe:1022
MESSAGE : ZRC=0x80020035=-2147352523=SQLB_NOT_ALLOWED_RECOVERY_P
"Access not allowed. Tblspc Recovery Pend."

2009-06-03-15.26.42.966061-240 I30126944A526 LEVEL: Error
PID : 1208386 TID : 2475 PROC : db2sysc 0
INSTANCE: db2v9q1 NODE : 000 DB : QADB9
APPHDL : 0-5143 APPID: *LOCAL.db2v9q1.090603192310
AUTHID : DBBACKUP
EDUID : 2475 EDUNAME: db2agent (QADB9) 0
FUNCTION: DB2 UDB, buffer pool services, sqlbGetPoolStats, probe:1022
MESSAGE : ZRC=0x80020035=-2147352523=SQLB_NOT_ALLOWED_RECOVERY_P
"Access not allowed. Tblspc Recovery Pend."

2009-06-03-15.45.50.828440-240 I30147572A526 LEVEL: Error
PID : 1208386 TID : 2475 PROC : db2sysc 0
INSTANCE: db2v9q1 NODE : 000 DB : QADB9
APPHDL : 0-5143 APPID: *LOCAL.db2v9q1.090603192310
AUTHID : DBBACKUP
EDUID : 2475 EDUNAME: db2agent (QADB9) 0
FUNCTION: DB2 UDB, buffer pool services, sqlbGetPoolStats, probe:1022
MESSAGE : ZRC=0x80020035=-2147352523=SQLB_NOT_ALLOWED_RECOVERY_P
"Access not allowed. Tblspc Recovery Pend."

I just dropped this TS (it's temp anyway) and did database backup and it was ok, but I need to know why it always happens. Thanks in advance

from IBM web site

It will be placed in this state only if the table space is found to be damaged during the restart operation.

If a problem occurs with a container for a specified table space during the restart process, the corresponding table space will not be available (it will be in drop-pending state) after the restart operation. If a table space is in the drop-pending state, the only possible action is to drop the table space.

Are you restarting your db daily?

Also, do you do a partial restore of this db (with the rebuild option)?

- we don't restart this db, but we had to restore it almost every day as this is used by QA.
- we do full off-line backup and full restore of this db.

I had this problem in one DB and I was able to fix it by dropping SYSTOOLSTMPSPACE TS and then taking the backup. BUT... another DB had both SYSTOOLSPACE and SYSTOOLSTMPSPACE in Drop Pending mode and I was not sure if I can drop SYSTOOLSPACE TS to fix it. I am trying to find the source of the problem. Any ideas?

thanks/spasibo, Bella

When you do a partial restore, tablespaces you don't restore will be put in offline/drop pending state. Are you sure you're not using the rebuild option on the restore command?

Do you notice this tablespace state right after the restore? Next time you do a restore, verify that all tablespace are in normal state.

A tablespace is put in offline state when its container(s) is not accessible for some reason. A tablespace is put in drop pending state after a partial restore (when tablespace is not restored) or restart db with the drop pending tablespaces option. There are could be other reasons and the db2diag.log should tell us why. Please check the db2diag.log entries between the last restore and first 0x80020035

there was only 1 restore at the beginning of all QA testing, and then we were taking daily backups with which we had problems sometimes. I suspect the QA applications did something so those TSs went into drop pending mode but noone can tell me what.

Does 'buffer pool services, sqlbGetPoolStats' mean anything? Can it be a problem with BP sizings?

The error message in the db2diag.log indicates that the tablespace access is not allowed, ZRC 0x80020035 maps to sql0290n. This error is dumped when some application tries to access the tablespace, but it doesn't tell us why the tablespace is in this state. There is nothing wrong with the bufferpool / its size. To find out why it's in offline/drop pending state, look for the first occurance of 0x80020035 (after restore) and then go up in the diag.log and look for any entry that indicate a problem with this tablespace. If you like, attach your db2diag.log here and I'll take a look.

you are right, Bella. I went all the way back to when this DB was restored (from another DB backup) and found that 'one or more table spaces from the backup were not restored.' Unfortunately restore (backup of QASM3 db to QASM1 db) was not done by me and a tester who did it did not pay attention to error messages. Why would it happen? Container(s) problem? See exerpt below:

2009-04-29-08.59.37.542424-240 E20098916A1275 LEVEL: Warning
PID : 1208386 TID : 34944 PROC : db2sysc 0
INSTANCE: db2v9q1 NODE : 000 DB : QASM1
APPHDL : 0-9592 APPID: *LOCAL.db2v9q1.090429125937
AUTHID : DBBACKUP
EDUID : 34944 EDUNAME: db2agent (QASM1) 0
FUNCTION: DB2 UDB, database utilities, sqluCompareDB, probe:1401
MESSAGE : SQL2528W Warning! Restoring to an existing database that is the
same as the backup image database, but the alias name "" of the
existing database does not match the alias "" of backup image, and
the database name "" of the existing database does not match the
database name "" of the backup image. The target database will be
overwritten by the backup version.
DATA #1 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes
sqlcaid : SQLCA sqlcabc: 136 sqlcode: 2528 sqlerrml: 23
sqlerrmc: QASM1 QASM3 QASM1 QASM3
sqlerrp : sqluComp
sqlerrd : (1) 0x00000000 (2) 0x00000000 (3) 0x00000000
(4) 0x00000000 (5) 0x00000000 (6) 0x00000000
sqlwarn : (1) (2) (3) (4) (5) (6)
(7) (8) (9) (10) (11)
sqlstate:

2009-04-29-08.59.56.285486-240 E20141521A550 LEVEL: Warning
PID : 1208386 TID : 34944 PROC : db2sysc 0
INSTANCE: db2v9q1 NODE : 000 DB : QASM1
APPHDL : 0-9592 APPID: *LOCAL.db2v9q1.090429125937
AUTHID : DBBACKUP
EDUID : 34944 EDUNAME: db2agent (QASM1) 0
FUNCTION: DB2 UDB, database utilities, sqludBMResponse, probe:659
DATA #1 : Sqlcode, PD_TYPE_SQLCODE, 4 bytes
2563
DATA #2 : Hexdump, 4 bytes
0x0770000032E54978 : 0000 0A03 ....

2009-04-29-08.59.56.285747-240 E20142072A966 LEVEL: Warning
PID : 1208386 TID : 34944 PROC : db2sysc 0
INSTANCE: db2v9q1 NODE : 000 DB : QASM1
APPHDL : 0-9592 APPID: *LOCAL.db2v9q1.090429125937
AUTHID : DBBACKUP
EDUID : 34944 EDUNAME: db2agent (QASM1) 0
FUNCTION: DB2 UDB, database utilities, sqludBMResponse, probe:659
MESSAGE : SQL2563W The restore process has completed successfully, but one or
more table spaces from the backup were not restored.
DATA #1 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes
sqlcaid : SQLCA sqlcabc: 136 sqlcode: 2563 sqlerrml: 0
sqlerrmc:
sqlerrp : sqludBMR
sqlerrd : (1) 0x00000000 (2) 0x00000000 (3) 0x00000000
(4) 0x00000000 (5) 0x00000000 (6) 0x00000000
sqlwarn : (1) (2) (3) (4) (5) (6)
(7) (8) (9) (10) (11)
sqlstate:

2009-04-29-08.59.57.064633-240 E20143039A423 LEVEL: Info
PID : 1208386 TID : 34944 PROC : db2sysc 0
INSTANCE: db2v9q1 NODE : 000 DB : QASM1
APPHDL : 0-9592 APPID: *LOCAL.db2v9q1.090429125937
AUTHID : DBBACKUP
EDUID : 34944 EDUNAME: db2agent (QASM1) 0
FUNCTION: DB2 UDB, database utilities, sqludrsa, probe:774
MESSAGE : Restore Complete.

Yes, this looks like a problem with some tablespace. Most likely, the tablespace container was already in use by another database. I'd expect to see more information in the db2diag.log as to why some tablespace was not restored.

you are right again, Bella. I just wonder why we had to use WITHOUT ROLLING FORWARD in restore? Here it is:

2009-04-28-09.27.56.903441-240 E19896846A846 LEVEL: Severe
PID : 1208386 TID : 34687 PROC : db2sysc 0
INSTANCE: db2v9q1 NODE : 000 DB : QASM1
APPHDL : 0-5273 APPID: *LOCAL.DB2.090428132758
AUTHID : QASM1
EDUID : 34687 EDUNAME: db2agent (QASM1) 0
FUNCTION: DB2 UDB, buffer pool services, sqlbCheckBPFConsistancy, probe:120
DATA #1 : <preformatted>
** WARNING: Database QASM1 was successfully activated. However, memory
utilization may not be optimal due to the current settings in
the buffer pool information files, SQLBP.1 and SQLBP.2. This
will not cause any functional problem, but may mean DB2 will
not perform optimally. It is recommended that you contact IBM
service personnel to investigate.

2009-04-29-08.59.37.380039-240 I20096763A501 LEVEL: Event
PID : 1208386 TID : 34944 PROC : db2sysc 0
INSTANCE: db2v9q1 NODE : 000 DB : QASM1
APPHDL : 0-9592 APPID: *LOCAL.db2v9q1.090429125937
AUTHID : DBBACKUP
EDUID : 34944 EDUNAME: db2agent (QASM1) 0
FUNCTION: DB2 UDB, config/install, sqlfLogUpdateCfgParam, probe:20
CHANGE : CFG DB QASM1 : "Database_memory" From: "42864" <automatic> To: "36624" <automatic>

2009-04-29-08.59.37.541779-240 E20097265A611 LEVEL: Info
PID : 1208386 TID : 34944 PROC : db2sysc 0
INSTANCE: db2v9q1 NODE : 000 DB : QASM1
APPHDL : 0-9592 APPID: *LOCAL.db2v9q1.090429125937
AUTHID : DBBACKUP
EDUID : 34944 EDUNAME: db2agent (QASM1) 0
FUNCTION: DB2 UDB, database utilities, sqludValidateUserOptionsAgainstMediaHead, probe:465
DATA #1 : <preformatted>
The database detected in the backup image requires that this restore
be done WITHOUT ROLLING FORWARD. No rollforward will be required
following this restore.

2009-04-29-08.59.37.542022-240 E20097877A488 LEVEL: Info
PID : 1208386 TID : 34944 PROC : db2sysc 0
INSTANCE: db2v9q1 NODE : 000 DB : QASM1
APPHDL : 0-9592 APPID: *LOCAL.db2v9q1.090429125937
AUTHID : DBBACKUP
EDUID : 34944 EDUNAME: db2agent (QASM1) 0
FUNCTION: DB2 UDB, database utilities, sqludPrintStartingMsg, probe:1294
DATA #1 : <preformatted>
Starting a full database restore.
Agent EDU ID: 34944

"...restore be done WITHOUT ROLLING FORWARD" just indicates that the backup image you're using to restore is from a previous release (ie. v8 or 9.1). Logs can't be applied following the restore so it has to be done without rolling forward. db2diag.log should contain some messages that indicate why there was a problem restoring this tablespace, look for something like container already in use or anything that indicates why it was not restored.

QASM1 DB had this problems before restore (replace):

2009-04-27-13.37.52.813547-240 E19881160A846 LEVEL: Severe
PID : 1208386 TID : 49473 PROC : db2sysc 0
INSTANCE: db2v9q1 NODE : 000 DB : QASM1
APPHDL : 0-64161 APPID: *LOCAL.DB2.090427173756
AUTHID : QASM1
EDUID : 49473 EDUNAME: db2agent (QASM1) 0
FUNCTION: DB2 UDB, buffer pool services, sqlbCheckBPFConsistancy, probe:120
DATA #1 : <preformatted>
** WARNING: Database QASM1 was successfully activated. However, memory
utilization may not be optimal due to the current settings in
the buffer pool information files, SQLBP.1 and SQLBP.2. This
will not cause any functional problem, but may mean DB2 will
not perform optimally. It is recommended that you contact IBM
service personnel to investigate.

2009-04-28-09.27.56.903441-240 E19896846A846 LEVEL: Severe
PID : 1208386 TID : 34687 PROC : db2sysc 0
INSTANCE: db2v9q1 NODE : 000 DB : QASM1
APPHDL : 0-5273 APPID: *LOCAL.DB2.090428132758
AUTHID : QASM1
EDUID : 34687 EDUNAME: db2agent (QASM1) 0
FUNCTION: DB2 UDB, buffer pool services, sqlbCheckBPFConsistancy, probe:120
DATA #1 : <preformatted>
** WARNING: Database QASM1 was successfully activated. However, memory
utilization may not be optimal due to the current settings in
the buffer pool information files, SQLBP.1 and SQLBP.2. This
will not cause any functional problem, but may mean DB2 will
not perform optimally. It is recommended that you contact IBM
service personnel to investigate.

Then restore was done on 2009-04-29 -
I can't see anything else there, Bella. May be you can:


2009-04-29-08.59.37.380039-240 I20096763A501 LEVEL: Event
PID : 1208386 TID : 34944 PROC : db2sysc 0
INSTANCE: db2v9q1 NODE : 000 DB : QASM1
APPHDL : 0-9592 APPID: *LOCAL.db2v9q1.090429125937
AUTHID : DBBACKUP
EDUID : 34944 EDUNAME: db2agent (QASM1) 0
FUNCTION: DB2 UDB, config/install, sqlfLogUpdateCfgParam, probe:20
CHANGE : CFG DB QASM1 : "Database_memory" From: "42864" <automatic> To: "36624" <automatic>

2009-04-29-08.59.37.541779-240 E20097265A611 LEVEL: Info
PID : 1208386 TID : 34944 PROC : db2sysc 0
INSTANCE: db2v9q1 NODE : 000 DB : QASM1
APPHDL : 0-9592 APPID: *LOCAL.db2v9q1.090429125937
AUTHID : DBBACKUP
EDUID : 34944 EDUNAME: db2agent (QASM1) 0
FUNCTION: DB2 UDB, database utilities, sqludValidateUserOptionsAgainstMediaHead, probe:465
DATA #1 : <preformatted>
The database detected in the backup image requires that this restore
be done WITHOUT ROLLING FORWARD. No rollforward will be required
following this restore.

2009-04-29-08.59.37.542022-240 E20097877A488 LEVEL: Info
PID : 1208386 TID : 34944 PROC : db2sysc 0
INSTANCE: db2v9q1 NODE : 000 DB : QASM1
APPHDL : 0-9592 APPID: *LOCAL.db2v9q1.090429125937
AUTHID : DBBACKUP
EDUID : 34944 EDUNAME: db2agent (QASM1) 0
FUNCTION: DB2 UDB, database utilities, sqludPrintStartingMsg, probe:1294
DATA #1 : <preformatted>
Starting a full database restore.
Agent EDU ID: 34944

2009-04-29-08.59.37.542216-240 E20098366A549 LEVEL: Warning
PID : 1208386 TID : 34944 PROC : db2sysc 0
INSTANCE: db2v9q1 NODE : 000 DB : QASM1
APPHDL : 0-9592 APPID: *LOCAL.db2v9q1.090429125937
AUTHID : DBBACKUP
EDUID : 34944 EDUNAME: db2agent (QASM1) 0
FUNCTION: DB2 UDB, database utilities, sqluCompareDB, probe:1401
DATA #1 : Sqlcode, PD_TYPE_SQLCODE, 4 bytes
2528
DATA #2 : Hexdump, 4 bytes
0x0770000032E54978 : 0000 09E0 ....

2009-04-29-08.59.37.542424-240 E20098916A1275 LEVEL: Warning
PID : 1208386 TID : 34944 PROC : db2sysc 0
INSTANCE: db2v9q1 NODE : 000 DB : QASM1
APPHDL : 0-9592 APPID: *LOCAL.db2v9q1.090429125937
AUTHID : DBBACKUP
EDUID : 34944 EDUNAME: db2agent (QASM1) 0
FUNCTION: DB2 UDB, database utilities, sqluCompareDB, probe:1401
MESSAGE : SQL2528W Warning! Restoring to an existing database that is the
same as the backup image database, but the alias name "" of the
existing database does not match the alias "" of backup image, and
the database name "" of the existing database does not match the
database name "" of the backup image. The target database will be
overwritten by the backup version.
DATA #1 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes
sqlcaid : SQLCA sqlcabc: 136 sqlcode: 2528 sqlerrml: 23
sqlerrmc: QASM1 QASM3 QASM1 QASM3
sqlerrp : sqluComp
sqlerrd : (1) 0x00000000 (2) 0x00000000 (3) 0x00000000
(4) 0x00000000 (5) 0x00000000 (6) 0x00000000
sqlwarn : (1) (2) (3) (4) (5) (6)
(7) (8) (9) (10) (11)
sqlstate:

2009-04-29-08.59.56.285486-240 E20141521A550 LEVEL: Warning
PID : 1208386 TID : 34944 PROC : db2sysc 0
INSTANCE: db2v9q1 NODE : 000 DB : QASM1
APPHDL : 0-9592 APPID: *LOCAL.db2v9q1.090429125937
AUTHID : DBBACKUP
EDUID : 34944 EDUNAME: db2agent (QASM1) 0
FUNCTION: DB2 UDB, database utilities, sqludBMResponse, probe:659
DATA #1 : Sqlcode, PD_TYPE_SQLCODE, 4 bytes
2563
DATA #2 : Hexdump, 4 bytes
0x0770000032E54978 : 0000 0A03 ....

2009-04-29-08.59.56.285747-240 E20142072A966 LEVEL: Warning
PID : 1208386 TID : 34944 PROC : db2sysc 0
INSTANCE: db2v9q1 NODE : 000 DB : QASM1
APPHDL : 0-9592 APPID: *LOCAL.db2v9q1.090429125937
AUTHID : DBBACKUP
EDUID : 34944 EDUNAME: db2agent (QASM1) 0
FUNCTION: DB2 UDB, database utilities, sqludBMResponse, probe:659
MESSAGE : SQL2563W The restore process has completed successfully, but one or
more table spaces from the backup were not restored.
DATA #1 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes
sqlcaid : SQLCA sqlcabc: 136 sqlcode: 2563 sqlerrml: 0
sqlerrmc:
sqlerrp : sqludBMR
sqlerrd : (1) 0x00000000 (2) 0x00000000 (3) 0x00000000
(4) 0x00000000 (5) 0x00000000 (6) 0x00000000
sqlwarn : (1) (2) (3) (4) (5) (6)
(7) (8) (9) (10) (11)
sqlstate:

2009-04-29-08.59.57.064633-240 E20143039A423 LEVEL: Info
PID : 1208386 TID : 34944 PROC : db2sysc 0
INSTANCE: db2v9q1 NODE : 000 DB : QASM1
APPHDL : 0-9592 APPID: *LOCAL.db2v9q1.090429125937
AUTHID : DBBACKUP
EDUID : 34944 EDUNAME: db2agent (QASM1) 0
FUNCTION: DB2 UDB, database utilities, sqludrsa, probe:774
MESSAGE : Restore Complete.

2009-04-29-08.59.58.188582-240 I20143463A501 LEVEL: Event
PID : 1208386 TID : 56570 PROC : db2sysc 0
INSTANCE: db2v9q1 NODE : 000 DB : QASM1
APPHDL : 0-9593 APPID: *LOCAL.db2v9q1.090429125958
AUTHID : DBBACKUP
EDUID : 56570 EDUNAME: db2agent (QASM1) 0
FUNCTION: DB2 UDB, config/install, sqlfLogUpdateCfgParam, probe:20
CHANGE : CFG DB QASM1 : "Database_memory" From: "36624" <automatic> To: "86560" <automatic>

2009-04-29-09.00.03.774288-240 E20143965A541 LEVEL: Info
PID : 1208386 TID : 56570 PROC : db2sysc 0
INSTANCE: db2v9q1 NODE : 000 DB : QASM1
APPHDL : 0-9593 APPID: *LOCAL.db2v9q1.090429125958
AUTHID : DBBACKUP
EDUID : 56570 EDUNAME: db2agent (QASM1) 0
FUNCTION: DB2 UDB, buffer pool services, sqlbStartPools, probe:15
DATA #1 : <preformatted>
Non-recoverable database detected and table space SYSTOOLSPACE
is in RESTORE_PENDING. Switching to DROP_PENDING.

To understand what's happening, can you get the following info:

source system:
db2pd -d <db name> -tablespaces


target system:
df -k


Also, restore script


We need to know whether your tablespaces are using automatic storage or not, container definitions, and if the filesystems are the same on source and target

source DB is v8.2, target DB - v9.5.1
db2 restore db QASM3 from . taken at <timestamp> into QASM1

source DB TS containers:
Name = /database/db2v8q1/db2v8q1/NODE0000/SQL00006/SQLT0000.0
Name = /database/db2v8q1/db2v8q1/NODE0000/SQL00006/SQLT0001.0
Name = /database/db2v8q1/db2v8q1/NODE0000/SQL00006/SQLT0002.0
Name = /database/db2v8q1/db2v8q1/NODE0000/SQL00006/SYSTOOLSPACE
target DB TS containers:
Name = /database/db2v9q1/NODE0000/SQL00009/SQLT0000.0
Name = /database/db2v9q1/NODE0000/SQL00009/SQLT0001.0
Name = /database/db2v9q1/NODE0000/SQL00009/SQLT0002.0
Name = /database/db2v9q1/NODE0000/SQL00009/SYSTOOLSPACE
Name = /database/db2v9q1/NODE0000/SQL00009/SYSTOOLSTMPSPACE

source DB:
db2pd -d <db name> -tablespaces - did not work! My guess it because source DB does not have SYSTOOLSTMPSPACE TS.
target DB:
devdb2[/home/dba1]db2pd -d QASM1 -tablespaces

Database Partition 0 -- Database QASM1 -- Active -- Up 0 days 00:43:58

Tablespace Configuration:
Address Id Type Content PageSz ExtentSz Auto Prefetch BufID BufIDDisk FSC NumCntrs MaxStripe LastConsecPg Name
0x0700000142878100 0 SMS Regular 4096 32 No 32 1 1 On 1 0 31 SYSCATSPACE
0x070000014287A7E0 1 SMS SysTmp 4096 32 No 32 1 1 On 1 0 31 TEMPSPACE1
0x070000014287EEE0 2 SMS Regular 4096 32 No 32 1 1 On 1 0 31 USERSPACE1
0x070000014287F580 3 SMS Regular 4096 32 Yes 192 1 1 On 1 0 31 SYSTOOLSPACE
0x070000014CE24100 4 SMS UsrTmp 4096 4 Yes 24 1 1 On 1 0 3 SYSTOOLSTMPSPACE

Tablespace Statistics:
Address Id TotalPgs UsablePgs UsedPgs PndFreePgs FreePgs HWM State MinRecTime NQuiescers
0x0700000142878100 0 87875 87875 87875 0 0 0 0x00000000 0 0
0x070000014287A7E0 1 1 1 1 0 0 0 0x00000000 0 0
0x070000014287EEE0 2 3617 3617 3617 0 0 0 0x00000000 0 0
0x070000014287F580 3 149 149 149 0 0 0 0x00000000 0 0
0x070000014CE24100 4 1 1 1 0 0 0 0x00000000 0 0

Tablespace Autoresize Statistics:
Address Id AS AR InitSize IncSize IIP MaxSize LastResize LRF
0x0700000142878100 0 No No 0 0 No 0 None No
0x070000014287A7E0 1 No No 0 0 No 0 None No
0x070000014287EEE0 2 No No 0 0 No 0 None No
0x070000014287F580 3 No No 0 0 No 0 None No
0x070000014CE24100 4 No No 0 0 No 0 None No

Containers:
Address TspId ContainNum Type TotalPgs UseablePgs StripeSet Container
0x0700000140D0F9A0 0 0 Path 87875 87875 0 /database/db2v9q1/NODE0000/SQL00009/SQLT0000.0
0x0700000140D0FB20 1 0 Path 1 1 0 /database/db2v9q1/NODE0000/SQL00009/SQLT0001.0
0x0700000140D0FCA0 2 0 Path 3617 3617 0 /database/db2v9q1/NODE0000/SQL00009/SQLT0002.0
0x0700000140D0FE20 3 0 Path 149 149 0 /database/db2v9q1/NODE0000/SQL00009/SYSTOOLSPACE
0x070000014287FC20 4 0 Path 1 1 0 /database/db2v9q1/NODE0000/SQL00009/SYSTOOLSTMPSPACE

target system:
df -k
(DBs are in ‘/database’ directory):
devdb2[/home/dba1]df -k
Filesystem 1024-blocks Free %Used Iused %Iused Mounted on
/dev/hd4 5242880 4779520 9% 5672 1% /
/dev/hd2 5111808 1513100 71% 62985 5% /usr
/dev/hd9var 1048576 588880 44% 49795 19% /var
/dev/hd3 1048576 1011600 4% 78 1% /tmp
/dev/hd1 26214400 4562952 83% 14470 1% /home
/proc - - - - - /proc
/dev/hd10opt 2228224 977088 57% 8069 2% /opt
/dev/fslv00 536870912 67016452 88% 61150 1% /database
/dev/fslv01 471859200 265579520 44% 4058 1% /dbbackup
/dev/lvian 314572800 287921392 9% 17291 1% /databasetemp
/dev/hd11admin 131072 126912 4% 18 1% /admin
/dev/livedump 262144 253876 4% 17 1% /var/adm/ras/livedump

Based on your list, source db doesn't have SYSTOOLSTMPSPACE tablespace. Is this right?

If the backup image doesn't contain SYSTOOLSTMPSPACE, then your target db will not have this tablespace after the restore (restore will override your target db)

If the backup image does contain SYSTOOLSTMPSPACE, then restore should be able to restore all tablespaces since the container path for all of them is stored under the db directory and /database filesystem exists on both system. If DB2 thought that the container path for SYSTOOLSTMPSPACE was in use by some other database/tablespace, then db2 should dumped errors in the diag.log (I don't see any). Next time you have to restore this db, drop the target db first and then check the db directory to make sure that there are files left over from the drop command.