DB2 Table spaces, Containers

Create database with automatic storage :

#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> 

Check the DB size :

db2 "call get_dbsize_info(?,?,?,-1)"

DB Path on Windows (on Drive letter only by default) :

db2set DB2_CREATE_DB_ON_PATH=YES

Tablespace utilization ( > 9.5 ) :

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'"

Containers size :

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"

All DB PATHS for all dbs in instance :

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

Stripe sets for Tablespace :

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"

Add new RAW device to tablespace :

db2 "alter tablespace <TS> add (device '<RAWDEVICE>' <SIZE>M)"

List tablespace containers for all DBs :

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

Tablespace metadata

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"

Bufferpools for tablespaces :

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"

Create storage groups :

db2 "create stogroup <STOGROUP> on '</PATH>', '</PATH>'"

Add new storage path to database :

db2 "alter database add storage on '</PATH>'"

Check storage groups :

db2 "select substr(sgname,1,15) SGNAME,substr(sgid,1,3) ID,substr(REMARKS,1,30) COMMENT,create_time from syscat.stogroups"

Add new storage path to storage group :

Move tablespace to new storage group :

Create tablespaces :

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>

Check which tablespaces are created as reclaimable :

db2 "select substr(TBSP_NAME,1,14),reclaimable_space_enabled from table (MON_GET_TABLESPACE(NULL,-1)) WHERE TBSP_NAME NOT  LIKE ('SYS%')"

Check for high water mark :

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'"

Show me MB of 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-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'"

Reclaim unused space of Automatic storage TBSP to maximum extent:

db2 "alter tablespace <TBSP> reduce max"

Monitor extent remap during space reclaim :

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))"

Possible since 9.7

Check if DB/Table space is using Automatic storage paths :

db2pd -db <DBNAME> -storagepaths
db2 get snapshot for database on <DB> |grep "automatic storage"
db2 get snapshot for tablespaces on <TABLESPACE>

1. Define previously created filesystems for Automatic storage :

db2 "alter database <DBNAME> add storage on '</path/to/FS1>', '</path/to/FS2>'"

2. Convert suitable table spaces (Any/Large DMS) :

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')"

3. Verify new stripe sets and extent map from table space snapshot :

! TBD db2 get snapshot for tablespace TBD !

4. Rebalance the table spaces to move data to new Automatic storage paths :

db2 "ALTER TABLESPACE <TablespaceName> REBALANCE"

5. Monitor the progress of rebalancing :**

db2 "SELECT * from table (MON_GET_REBALANCE_STATUS( ’<TABLESPACE>’, -2))"

6. Throttle the rebalancing utility in case of need :

db2 list utilities show detail
db2 "SET UTIL_IMPACT_PRIORITY FOR <utility_id> TO <priority>;"

5. Create new Automatic storage Temporary tablespaces (replace old SMS/DMS) :

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"

1. Generate the restore script with redirected option :

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;

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'"

monitor rebalancing progress

 
db2 "SELECT * FROM TABLE(SYSPROC.MON_GET_EXTENT_MOVEMENT_STATUS('', -1))"