SNAPTBSP administrative view

se puede usar el siguiente SQL para obtener la lista de los tablespaces y sus caracteristicas de las base de datos a la que se esta conectado

db2 "SELECT SUBSTR(TBSP_NAME,1,30) AS TBSP_NAME, TBSP_ID id, TBSP_TYPE, TBSP_CONTENT_TYPE,, TBSP_USING_AUTO_STORAGE auto_storage, TBSP_AUTO_RESIZE_ENABLED  auto_resize FROM SYSIBMADM.SNAPTBSP"

TBSP_NAME                      ID                   TBSP_TYPE  TBSP_CONTENT_TYPE AUTO_STORAGE AUTO_RESIZE
------------------------------ -------------------- ---------- ----------------- ------------ -----------
SYSCATSPACE                                       0 DMS        ANY                          0           1
SYSTOOLSPACE                                      1 DMS        ANY                          0           0
SYSTOOLSTMPSPACE                                  2 SMS        USRTEMP                      0           -
...

fuente:
http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.db2.luw.sql.rtn.doc/doc/r0022008.html


Carlos Hiram

como monitorear el avance del reorg

se de 2 maneras para monitorear el avance del reorg

haciendo un snapshot  y filtrando por una tabla 

$ db2 get snapshot for tables on test1| grep -p table_a
 Table Schema        = test
 Table Name          = table_a
 Table Type          = User
 Data Object Pages   = 181858
 Rows Read           = Not Collected
 Rows Written        = 2265
 Overflows           = 3944
 Page Reorgs         = 83
 Table Reorg Information:
   Reorg Type        =
        Reclustering
        Table Reorg
        Allow Read Access
        Recluster Via Table Scan
        Reorg Data Only
   Reorg Index       = 5
   Reorg Tablespace  = 7
   Start Time        = 05/19/2008 16:06:21.218714
   Reorg Phase       = 4 - Index Recreate
   Max Phase         = 4
   Phase Start Time  = 05/19/2008 16:13:01.673256
   Status            = Started
   Current Counter   = 150857
   Max Counter       = 358338
   Completion        = 0
   End Time          =

usando la herramienta db2pd

db2pd -database test1-reorg

Database Partition 0 -- Database test1-- Active -- Up 0 days 00:00:10

Table Reorg Information:
Address            TbspaceID TableID PartID MasterTbs MasterTab TableName          Type    IndexID    TempSpaceID
0x07000001379335A8 8         3899    n/a    n/a       n/a       TEST_B        Offline 0          8

Table Reorg Stats:
Address            TableName          Start               End                 PhaseStart          MaxPhase   Phase      CurCount   MaxCount  

Status  Completion
0x07000001379335A8 TEST_B        04/07/2010 20:03:54 04/07/2010 20:03:57 04/07/2010 20:03:57 3          IdxRecreat 0          0         

Done    0


ambas útiles


carlos hiram

SNAPSTORAGE_PATHS administrative view

esta funcion regresa los path del automatic storage de la db

>db2 "SELECT SUBSTR(DB_NAME,1,8) AS DB_NAME, SUBSTR(DB_STORAGE_PATH,1,8) AS DB_STORAGE_PATH, STO_PATH_FREE_SIZE FROM SYSIBMADM.SNAPSTORAGE_PATHS"

DB_NAME  DB_STORAGE_PATH STO_PATH_FREE_SIZE
-------- --------------- --------------------
TEST      /db2/TEST                 61039665152
TEST      /db2/TEST                 61039677440
TEST      /db2/TEST                 61039644672
TEST      /db2/TEST                 61039689728
TEST      /db2/TEST                 61039640576
TEST      /db2/TEST                 60928073728

  6 record(s) selected.

http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.db2.luw.sql.rtn.doc/doc/r0022006.html

carlos hiram