Automatic Storage Path in DROP status

[b***@gmail.com]

All -

DB2 for LUW 9.7.6 with HADR on AIX 6

I have a potentially serious situation where I have a Systemp tablespace defined to /db2syscat which is under root defined to AUTOMATIC STORAGE. Whomever created this database also has the SYSCATSPACE, USERSPACE1 and SYSTOOLSTMPSPACE also in the same AUTOMATIC STORAGE path.

Not realizing this I did a DROP of the AUTOMATIC STORAGE space so the space is now in DROP PENDING. The problem is that my AUTOMATIC STORAGE space evidently includes the SYSTEM CATALOG along with my Systemp tablespaces and all are in DROP PENDING.

Am I going to find all my SYSCATSPACE system catalog objects gone?

I would appreciate any comments here...

Thanks,

Bruce

db2 "SELECT TBSP_NAME FROM SYSIBMADM.SNAPCONTAINER WHERE CONTAINER_NAME LIKE '%/db2syscat%' GROUP BY TBSP_NAME";

TBSP_NAME
----------------
SYSCATSPACE
SYSTOOLSTMPSPACE
TSREORG32K
USERSPACE1

4 record(s) selected.

db2 "SELECT DISTINCT A.TBSP_NAME FROM SYSIBMADM.SNAPTBSP A, SYSIBMADM.SNAPTBSP_PART B WHERE A.TBSP_ID = B.TBSP_ID AND A.TBSP_CONTENT_TYPE IN ('USRTEMP','SYSTEMP') AND B.TBSP_PATHS_DROPPED = 1";

TBSP_NAME
--------------------
SYSTOOLSTMPSPACE
TSREORG32K

2 record(s) selected.


df -g
Filesystem GB blocks Free %Used Iused %Iused Mounted on
/dev/hd4 0.25 0.11 56% 7100 21% /
/dev/hd2 1.62 0.22 87% 39110 42% /usr
/dev/hd9var 0.38 0.12 68% 982 4% /var
/dev/hd3 1.88 1.87 1% 169 1% /tmp
/dev/hd1 0.12 0.11 10% 253 1% /home
/dev/hd11admin 0.12 0.12 1% 5 1% /admin
/proc - - - - - /proc
/dev/hd10opt 3.00 0.57 81% 20895 14% /opt
/dev/db2inst1lv 6.75 6.13 10% 841 1% /home/db2inst1
/dev/java_lv 0.50 0.50 1% 7 1% /java
/dev/db2_sys_data 15.75 12.22 23% 288 1% /db2syscat


db2 "SELECT DISTINCT A.TBSP_NAME, A.TBSP_ID, A.TBSP_CONTENT_TYPE FROM SYSIBMADM.SNAPTBSP A, SYSIBMADM.SNAPTBSP_PART B WHERE A.TBSP_ID = B.TBSP_ID AND B.TBSP_PATHS_DROPPED = 1";

TBSP_NAME TBSP_ID TBSP_CONTENT_TYPE
-------------------------------------
SYSCATSPACE 0 ANY
USERSPACE1 2 LARGE
TSREORG32K 20 SYSTEMP
SYSTOOLSTMPSPACE 47 USRTEMP

4 record(s) selected.

db2 list tablespace containers for 0 show detail

Tablespace Containers for Tablespace 0

Container ID = 0
Name = /db2syscat/db2inst1/NODE0000/OW_PROD/T0000000/C0000000.CAT
Type = File
Total pages = 270336
Useable pages = 270332
Accessible = Yes


db2 list tablespace containers for 1 show detail

Tablespace Containers for Tablespace 1

Container ID = 0
Name = /db2_data/temparea/container1/ow_prod/userts_temp04k
Type = Path
Total pages = 1
Useable pages = 1
Accessible = Yes
Container ID = 1
Name = /db2_data/temparea/container2/ow_prod/userts_temp04k
Type = Path
Total pages = 1
Useable pages = 1
Accessible = Yes
Container ID = 2
Name = /db2_data/temparea/container3/ow_prod/userts_temp04k
Type = Path
Total pages = 1
Useable pages = 1
Accessible = Yes
Container ID = 3
Name = /db2_data/temparea/container4/ow_prod/userts_temp04k
Type = Path
Total pages = 1
Useable pages = 1
Accessible = Yes
Container ID = 4
Name = /db2_data/temparea/container5/ow_prod/userts_temp04k
Type = Path
Total pages = 1
Useable pages = 1
Accessible = Yes


db2 list tablespace containers for 20 show detail

Tablespace Containers for Tablespace 20

Container ID = 0
Name = /db2syscat/db2inst1/NODE0000/OW_PROD/T0000020/C0000000.TMP
Type = Path
Total pages = 1
Useable pages = 1
Accessible = Yes

[Alexander Veremyev]

That's not a problem.

It seems you already added some other storage paths to the automatic storage (db2 prevents dropping last storage path from an automatic storage)

Just use
---------
db2 alter tablespace <tbsp_name> rebalance
----
for non-temporary tablespaces located in the automatic storage.

Temp tablespaces (which are actually SMS tablespaces) will be moved with next database stop/start.


BTW Drop pending containers are not physically removed until tablespace is rebalanced.

Best regards, Alex.

[b***@gmail.com]

Post by Alexander Veremyev
That's not a problem.
It seems you already added some other storage paths to the automatic storage (db2 prevents dropping last storage path from an automatic storage)
Just use
---------
db2 alter tablespace <tbsp_name> rebalance
----
for non-temporary tablespaces located in the automatic storage.
Temp tablespaces (which are actually SMS tablespaces) will be moved with next database stop/start.
BTW Drop pending containers are not physically removed until tablespace is rebalanced.
Best regards, Alex.

Alex -

Great...thanks you SO MUCH for your help on this... I will do what you suggested in my test environment.

-Bruce