Table Space Management Tools

1. View Table Space Status

1) Use a single command to view the table space status information, formatted output DB2 list tablespaces Show Detail

DB2 list tablespaces Show Detail | while read line ; Do w1=$ (echo "$line" | cut-d ""-F1); If ["$w 1" ==  "tablespace"];then tabid=$ (echo "$line" | cut-d ' = '-f2); fi; If ["$w 1" ==  "Name"];then   name=$ (echo "$line" | cut-d ' = '-f2); Fi If ["$w 1" ==  "Type"];then  type=$ (echo "$line" | cut-d ' = '-f2); type1=$ (echo "$type" | awk ' {print $} '); If ["$type 1" = = "System"];then ttype= ' SMS ';  else ttype= ' DMS '; fi;  fi; If ["$w 1" = = "Contents"];then contents=$ (echo "$line" | cut-d ' = '-f2); Fi if  ["$w 1" ==  "state"];then state=$ (echo "$line" | cut-d ' = '-f2); fi; If ["$w 1" = = "Total"];then   total=$ (echo "$line" | cut-d ' = '-f2); fi;if ["$w 1" = = "used"]; Then used=$ (echo "$line" | cut-d ' = '-f2); fi;  if ["$w 1" ==  "free"]; Then free=$ (echo "$line" | cut-d ' = '-f2); Fi If ["$w 1" = = "page"];then page=$ (echo "$line" | cut-d ' = '-f2); printf "%-25s%-5s%-5s%-5s%-10s%-20s%-20s%-30s\n"   $name $tabiD $page $Ttype $state $total $used; fi  Done

We sometimes need to see the table space state, the way there are many, DB2PD, DB2 list, when the table space is more, often too much information, resulting in not very convenient to view information. The convenience of DB2PD is that you can query the table space information without connecting the database, and make a more complete output of the table space information; DB2 list, you can output the information of the corresponding tablespace in table space, but these two methods are inconvenient for a database with more tablespaces.
View Table Space Status
DB2 list tablespaces Show Detail | while read line; Do w1=$ (echo "$line" | cut-d ""-F1); If ["$w 1" = = "Tablespace"];then tabid=$ (echo "$line" | cut-d ' = '-f2); Fi If ["$w 1" = = "Name"];then name=$ (echo "$line" | cut-d ' = '-f2); fi; If ["$w 1" = = "Type"];then type=$ (echo "$line" | cut-d ' = '-f2); type1=$ (echo "$type" | awk ' {print $} ');  If ["$type 1" = = "System"];then ttype= ' SMS '; else ttype= ' DMS '; Fi Fi If ["$w 1" = = "state"];then state=$ (echo "$line" | cut-d ' = '-f2); printf "%-25s%-10s%-10s%10s\n" $name $tabid $Ttype $state; Fi Done

===display:
Syscatspace 0 DMS 0x0000
TEMPSPACE1 1 SMS 0x0000
USERSPACE1 2 DMS 0x0000
Systoolspace 3 DMS 0x0000
Systoolstmpspace 4 SMS 0x0000

2) View tablespace information through a view
DB2 "SELECT SUBSTR (tbsp_name,1,20) as Tbspname,
Tbsp_type,
SUBSTR (tbsp_state,1,10) as Tbspstate,
SUBSTR (tbsp_total_pages,1,10) as Total_pages,
SUBSTR (tbsp_usable_pages,1,10) as Usable_pages,
SUBSTR (tbsp_used_pages,1,5) as Used_pages,
SUBSTR (tbsp_free_pages,1,5) as Free_pages,
SUBSTR (tbsp_max_size,1,5) as Max_size,
SUBSTR (tbsp_using_auto_storage,1,2) as AUTOSTR,
SUBSTR (tbsp_auto_resize_enabled,1,2) as Autores from Sysibmadm. Tbsp_utilization "

2. Table Space Auto-expansion settings

1) See if the tablespace is automatically expanded
DB2 "SELECT SUBSTR (tbsp_name,1,30) as Dms_tablespace_without_autoresize, Tbsp_using_auto_storage, Tbsp_auto_resize_ ENABLED from Sysibmadm. tbsp_utilization where tbsp_type= ' DMS ' and tbsp_auto_resize_enabled=0 "

2) Generate modified table space for automatic expansion

"Select ' ALTER tablespace ' | | Tbsp_name | | ' AutoResize YES; '  From Sysibmadm. tbsp_utilization where tbsp_type= ' DMS ' and tbsp_auto_resize_enabled=0 "

"SELECT ' ALTER tablespace ' | | Tbsp_name | | ' AutoResize YES; '  From Sysibmadm. tbsp_utilization where tbsp_type= ' DMS ' and tbsp_auto_resize_enabled=0'  |

-TVF Changetbsauto.sql

Table Space Management Tools