Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399

    Unanswered: tablespace SYSTOOLSTMPSPACE in Drop Pending mode?

    ( 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
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  2. #2
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    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?
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  3. #3
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Also, do you do a partial restore of this db (with the rebuild option)?

  4. #4
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    - 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
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  5. #5
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    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

  6. #6
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    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?
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  7. #7
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    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.

  8. #8
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    Quote Originally Posted by db2girl
    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.
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  9. #9
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    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.

  10. #10
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    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
    Last edited by MarkhamDBA; 06-05-09 at 14:49.
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  11. #11
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    "...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.

  12. #12
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    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.
    Last edited by MarkhamDBA; 06-05-09 at 15:40.
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  13. #13
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    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

  14. #14
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    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
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  15. #15
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •