====== DB2 Table spaces, Containers ======

===== Database =====
**Create database with automatic storage :** 
<sxh bash>
#db2 create database <DBNAME> automatic storage yes on '</path/to/as>' 
using codeset <CODESET> territory <TERRITORY> collate using <COLLATE> 
pagesize 4 K dft_extent_sz <dft_extent_sz> 
</sxh>

**Check the DB size : **
<sxh bash>
db2 "call get_dbsize_info(?,?,?,-1)"
</sxh>

**DB Path on Windows (on Drive letter only by default) :**
<sxh bash>
db2set DB2_CREATE_DB_ON_PATH=YES
</sxh>

===== Table spaces =====
**Tablespace utilization ( > 9.5 ) :**
<sxh sql>
echo "Table space utilization : "; db2 "select cast(tbsp_id as varchar(3)) ID, char(tbsp_type,5) MS, TBSP_CONTENT_TYPE TYPE, (TBSP_PAGE_SIZE/1024) PS, char(tbsp_name,30) TBSP, 
char(tbsp_state,20) STATUS, (tbsp_total_size_kb / 1024 ) as SIZE_MB, 
int((tbsp_free_size_kb) / 1024 ) as FREE_MB, 
((tbsp_total_size_kb / 1024 )-int((tbsp_free_size_kb) / 1024 )) as USED_MB, cast(TBSP_NUM_CONTAINERS as varchar(2)) C,
TBSP_UTILIZATION_PERCENT as PCT_USED, TBSP_USING_AUTO_STORAGE AS, 
TBSP_AUTO_RESIZE_ENABLED AR
from sysibmadm.tbsp_utilization order by PCT_USED";echo "Reclaimable space :";db2 "select char(TBSP_NAME,20) TABLESPACE, TBSP_PAGE_SIZE, TBSP_TOTAL_PAGES,TBSP_USED_PAGES,TBSP_PAGE_TOP,((TBSP_TOTAL_PAGES-TBSP_PAGE_TOP-1)*TBSP_PAGE_SIZE)/1024/1024 RECLAIM_MB from table (MON_GET_TABLESPACE(NULL,-1)) where TBSP_NAME not like 'SYS%' and tbsp_type != 'SMS'"
</sxh>

**Containers size :**
<sxh bash>
db2 "select substr(u.TBSP_NAME,1,20) TBSP_NAME, substr(u.CONTAINER_NAME,1,100) CONTAINER,
int(u.TOTAL_PAGES*t.TBSP_PAGE_SIZE/1024/1024) TOTAL_MB,int(u.USABLE_PAGES*t.TBSP_PAGE_SIZE/1024/1024) USABLE_MB, 
int(u.FS_TOTAL_SIZE_KB/1024) FSTOTAL_MB, int(u.FS_USED_SIZE_KB/1024) FSUSED_MB 
from SYSIBMADM.CONTAINER_UTILIZATION u join sysibmadm.tbsp_utilization t on u.TBSP_ID = t.TBSP_ID"
</sxh>

**All DB PATHS for all dbs in instance :**
<sxh bash>
for db in `db2 list db directory | grep -p Indirect | grep alias | awk '{print $NF}'`; do db2 connect to ${db} > /dev/null;db2 -x "select varchar(current schema,9) instance, varchar(current server,9) database,varchar(type,20) type, varchar(path,60) path from sysibmadm.dbpaths with ur";db2 terminate >/dev/null;done
</sxh>

**Stripe sets for Tablespace :**
<sxh bash>
db2 "select varchar(left(container_name,15),20) path, varchar(container_name,70) container,accessible,stripe_set from SYSIBMADM.CONTAINER_UTILIZATION where tbsp_name = 'TS_32K_L01' with ur"
</sxh>

**Add new RAW device to tablespace :**
<sxh bash>
db2 "alter tablespace <TS> add (device '<RAWDEVICE>' <SIZE>M)"
</sxh>

**List tablespace containers for all DBs :**
<sxh bash>
for db in `db2 list db directory | grep -p Indirect | grep alias | awk '{print $NF}'`; do db2 connect to ${db}; db2 "select substr(u.TBSP_NAME,1,20) TBSP_NAME, substr(u.CONTAINER_NAME,1,100) CONTAINER,
        int(u.TOTAL_PAGES*t.TBSP_PAGE_SIZE/1024/1024) TOTAL_MB,int(u.USABLE_PAGES*t.TBSP_PAGE_SIZE/1024/1024) USABLE_MB, 
        int(u.FS_TOTAL_SIZE_KB/1024) FSTOTAL_MB, int(u.FS_USED_SIZE_KB/1024) FSUSED_MB 
        from SYSIBMADM.CONTAINER_UTILIZATION u join sysibmadm.tbsp_utilization t on u.TBSP_ID = t.TBSP_ID"; done
</sxh>

**Tablespace metadata **
<sxh bash>
db2 "select tbspace,
  cast(case when a.datatag = -1 then b.datatag else a.datatag end as smallint) 
	 eff_datatag,
  cast(case when a.overhead = -1 then b.overhead else a.overhead end as double) 
	 eff_overhead,
  cast(case when a.transferrate = -1 then 
	 (1 / b.devicereadrate) / 1024 * a.pagesize else a.transferrate end as double) 
		eff_transferrate
 	from syscat.tablespaces a left outer join syscat.stogroups b on a.sgid = b.sgid"
</sxh>

===== Bufferpools =====
**Bufferpools for tablespaces :**
<sxh bash>
db2 "select varchar(t.tbspace,40) tablespace, varchar(b.bpname,40) bp, b.npages,b.pagesize/1024 || 'K' from syscat.tablespaces t join syscat.bufferpools b on b.bufferpoolid = t.bufferpoolid order by 1 with ur"
</sxh>

==== Automatic storage ====
**Create storage groups :**
<sxh bash>
db2 "create stogroup <STOGROUP> on '</PATH>', '</PATH>'"
</sxh>

**Add new storage path to database :**
<sxh bash>
db2 "alter database add storage on '</PATH>'"
</sxh>

**Check storage groups :**
<sxh bash>
db2 "select substr(sgname,1,15) SGNAME,substr(sgid,1,3) ID,substr(REMARKS,1,30) COMMENT,create_time from syscat.stogroups"
</sxh>

**Add new storage path to storage group :**
<sxh bash>
</sxh>

**Move tablespace to new storage group :**
<sxh bash>
</sxh>

**Create tablespaces :**
<sxh bash>
db2 create tablespace ts1
db2 create tablespace ts2 managed by automatic storage
db2 create temporary tablespace tempts
db2 create user temporary tablespace usrtmp managed by automatic storage
db2 create large tablespace largets
db2 create tablespace ts3 initialsize 8K increasesize 20 percent managed by automatic storage
db2 create tablespace ts4 maxsize 2G
db2 create tablespace ts5 using stogroup <STOGROUP>
</sxh>

==== Reclaimable storage ====

**Check which tablespaces are created as reclaimable :**
<sxh bash>
db2 "select substr(TBSP_NAME,1,14),reclaimable_space_enabled from table (MON_GET_TABLESPACE(NULL,-1)) WHERE TBSP_NAME NOT  LIKE ('SYS%')"
</sxh>

**Check for high water mark :**
<sxh bash>
db2 "select substr(TBSP_NAME,1,14) as TS_NAME, TBSP_TOTAL_PAGES,TBSP_USED_PAGES,TBSP_PAGE_TOP,reclaimable_space_enabled from table (MON_GET_TABLESPACE(NULL,-1)) as ts where TBSP_NAME not like 'SYS%' and tbsp_type = 'DMS'"
</sxh>

**Show me MB of reclaimable space :**
<sxh bash>
db2 "select char(TBSP_NAME,20) TABLESPACE, TBSP_PAGE_SIZE, TBSP_TOTAL_PAGES,TBSP_USED_PAGES,TBSP_PAGE_TOP,((TBSP_TOTAL_PAGES-TBSP_PAGE_TOP-200)*TBSP_PAGE_SIZE)/1024/1024 RECLAIM_MB from table (MON_GET_TABLESPACE(NULL,-1)) where TBSP_NAME not like 'SYS%' and tbsp_type != 'SMS'"
</sxh>

**Reclaim unused space of Automatic storage TBSP to maximum extent:**
<sxh bash>
db2 "alter tablespace <TBSP> reduce max"
</sxh>

**Monitor extent remap during space reclaim :**
<sxh bash>
db2 "SELECT char(TBSP_NAME,20), NUM_EXTENTS_MOVED,NUM_EXTENTS_LEFT,TOTAL_MOVE_TIME/1000 ELAPSED_TIME FROM TABLE(SYSPROC.MON_GET_EXTENT_MOVEMENT_STATUS(NULL, -1))"
</sxh>

===== Convert database/table space to Automatic storage =====
Possible since 9.7

Check if DB/Table space is using Automatic storage paths : 
<sxh bash>
db2pd -db <DBNAME> -storagepaths
db2 get snapshot for database on <DB> |grep "automatic storage"
db2 get snapshot for tablespaces on <TABLESPACE>
</sxh>

==== Online way ====

1. Define previously created filesystems for Automatic storage : 
<sxh bash>
db2 "alter database <DBNAME> add storage on '</path/to/FS1>', '</path/to/FS2>'"
</sxh>

2. Convert suitable table spaces (Any/Large DMS) : 
<sxh bash>
db2 -x "select 'alter tablespace ' || CHR(34) || TBSP_NAME || CHR(34) || ' managed by automatic storage;'
from SYSIBMADM.SNAPTBSP where TBSP_USING_AUTO_STORAGE != 1 and TBSP_TYPE = 'DMS' and TBSP_CONTENT_TYPE in ('ANY', 'LARGE')"
</sxh>

3. Verify new stripe sets and extent map from table space snapshot :
<sxh bash>
! TBD db2 get snapshot for tablespace TBD !
</sxh>

4. Rebalance the table spaces to move data to new Automatic storage paths : 
<sxh bash>
db2 "ALTER TABLESPACE <TablespaceName> REBALANCE"
</sxh>

5. Monitor the progress of rebalancing :**
<sxh bash>
db2 "SELECT * from table (MON_GET_REBALANCE_STATUS( ’<TABLESPACE>’, -2))"
</sxh>

6. Throttle the rebalancing utility in case of need : 
<sxh bash>
db2 list utilities show detail
db2 "SET UTIL_IMPACT_PRIORITY FOR <utility_id> TO <priority>;"
</sxh>

5. Create new Automatic storage Temporary tablespaces (replace old SMS/DMS) :
<sxh bash>
db2 "rename tablespace PSAPTEMP16 to oldTEMP16"
db2 "rename tablespace SYSTOOLSTMPSPACE to oldTOOLSTMPSPACE"
db2 "create temporary tablespace PSAPTEMP16
in nodegroup IBMTEMPGROUP pagesize 16k extentsize 2 prefetchsize automatic
no file system caching
dropped table recovery off"
db2 "create user temporary tablespace SYSTOOLSTMPSPACE
in nodegroup IBMCATGROUP pagesize 16k extentsize 2 prefetchsize automatic
no file system caching
dropped table recovery off"

db2 "drop tablespace oldTEMP16"
db2 "drop tablespace oldTOOLSTMPSPACE"
</sxh>

==== Offline way ====
1. Generate the restore script with redirected option :
<sxh bash>
db2 restore db <dbname> from <backup_location> redirect;
db2 set tablespace containers for <tablespace_id1> using automatic storage;
db2 set tablespace containers for <tablespace_id2> using automatic storage;
...
db2 set tablespace containers for <tablespace_idn> using automatic storage;
db2 restore database <dbname> continue;
db2 rollforward db <dbname> to end of the logs;
</sxh>


==== Scripts for online way ====
<sxh bash>
db2 -x "select 'alter tablespace ' || tbsp_name || ' add (file ''/tmp/'|| tbsp_name || ''' ' || int(((tbsp_total_size_kb / 1024 )-int((tbsp_free_size_kb) / 1024 ))*1.2) || ' M);' from sysibmadm.tbsp_utilization where tbsp_name = 'TSIDX02'"

db2 -x "select 'alter tablespace ' || tbsp_name || ' drop (device '''|| CONTAINER_NAME || ''');' from SYSIBMADM.CONTAINER_UTILIZATION where CONTAINER_TYPE like 'DISK%' and tbsp_name = 'TSIDX02'"

db2 -x "select 'alter tablespace ' || tbsp_name || ' managed by automatic storage using stogroup GRP_DATA01 autoresize yes increasesize 20 PERCENT maxsize ' || int((TBSP_USED_SIZE_KB/1024)*2) || ' M;' from SYSIBMADM.TBSP_UTILIZATION  where tbsp_name = 'TSDATA02'"

db2 -x "select 'alter tablespace ' || tbsp_name || ' managed by automatic storage using stogroup GRP_DATA01 autoresize yes increasesize 20 PERCENT;' from SYSIBMADM.TBSP_UTILIZATION  where tbsp_name = 'TSDATA02'"
</sxh>

monitor rebalancing progress
<sxh bash> 
db2 "SELECT * FROM TABLE(SYSPROC.MON_GET_EXTENT_MOVEMENT_STATUS('', -1))"
</sxh>