WorkingScripts

   1: -- to find tablespace status, type , usage run following SQL

   2: select substr(tablespace_name,1,20) Tablespace_name, 

   3:     case t.TBSPACETYPE 

   4:         when 'S' then 'System Managed'

   5:         when 'D' then 'Database managed'

   6:     end as TBSManaged, 

   7:     (used_pages/total_pages ) * 100 usedPCT, 

   8:     s.total_pages Total_pages, 

   9:     s.used_pages Used_pages, 

  10:     (s.total_pages - s.USABLE_PAGES ) Overhead_pgs, 

  11:     s.NUM_CONTAINERS No_of_containers,

  12:     s.free_pages Free_pages,

  13:     case t.DATATYPE 

  14:         when 'A' then ' ALL typ perm '

  15:         when 'L'  then ' Large '

  16:         when 'T'  then ' System Temp'

  17:         when 'U'  then ' User Temp '

  18:     end as TBStype, 

  19:     case s.TABLESPACE_STATE 

  20:         when 0 then ' Normal ' 

  21:         when 1 then ' Quiesced: SHARE ' 

  22:         when 2 then ' Quiesced: UPDATE ' 

  23:         when 4 then ' Quiesced: EXCLUSIVE ' 

  24:         when 8 then ' Load pending ' 

  25:         when 16 then ' Delete pending ' 

  26:         when 32 then ' Backup pending ' 

  27:         when 64 then ' Roll forward in progress ' 

  28:         when 128 then ' Roll forward pending ' 

  29:         when 256 then ' Restore pending ' 

  30:         when 512 then ' Disable pending ' 

  31:         when 1024 then ' Reorg in progress ' 

  32:         when 2048 then ' Backup in progress ' 

  33:         when 4096 then ' Storage must be defined ' 

  34:         when 8192 then ' Restore in progress ' 

  35:         when 16384 then ' Offline and not accessible ' 

  36:         when 32768 then ' Drop pending ' 

  37:         when 33554432 then ' Storage may be defined ' 

  38:         when 67108864 then ' Storage Definition is in (final) state ' 

  39:         when 134217728 then ' Storage Definition was changed prior to rollforward ' 

  40:         when 268435456 then ' DMS rebalancer is active ' 

  41:         when 536870912 then ' TBS deletion in progress ' 

  42:         when 1073741824 then ' TBS creation in progress ' end TABLESPACE_STATE 

  43: from table(snapshot_tbs_cfg(' ',-1)) as s, sysibm.systablespaces t where s.tablespace_name = t.tbspace ;

   1: -- GETTING WASTED SPACE

   2: select TBSPACE, ((80 - usedPCT) *  total_pages/100)*4/1000 SPACE_WASTED

   3: FROM

   4:     ( select substr(tablespace_name,1,20) TBSPACE,

   5:         total_pages,

   6:         used_pages,

   7:         free_pages, 

   8:         (used_pages*100/total_pages )  usedPCT

   9:     from table(snapshot_tbs_cfg(' ',-1)) as tbs ) 

  10: AS TABLE_SPACE

  11: WHERE usedPCT < 60