#!/bin/bash

db2set DB2_LARGE_PAGE_MEM=*

# make sure that db2 is running before executing
db2stop
db2start
db2 -v activate db spec04

# db2 tuning script

db2 -v update dbm cfg using SHEAPTHRES         33761
db2 -v update dbm cfg using INTRA_PARALLEL     OFF
db2 -v update dbm cfg using MAX_QUERYDEGREE    1
db2 -v update dbm cfg using MAXAGENTS          1000
db2 -v update dbm cfg using DFT_MON_LOCK       OFF
db2 -v update dbm cfg using DFT_MON_STMT       OFF
db2 -v update dbm cfg using DFT_MON_TABLE      OFF
db2 -v update dbm cfg using DFT_MON_UOW        OFF
db2 -v update dbm cfg using DFT_MON_TIMESTAMP  OFF
db2 -v update dbm cfg using notifylevel        0
db2 -v update dbm cfg using DIAGLEVEL          1
db2 -v update dbm cfg using query_heap_sz      52428
db2 -v update dbm cfg using rqrioblk           65535
db2 -v update dbm cfg using aslheapsz          2000
db2 -v update dbm cfg using svcename           db2c_db2inst1 


db2 -v connect to spec04 user db2inst1 using password
#db2 -v update db cfg for spec04 using database_memory 8650752
db2 -v update db cfg for spec04 using database_memory Automatic 
#db2 -v update db cfg for spec04 using database_memory 524288
db2 -v update db cfg for spec04 using dft_queryopt 0
db2 -v update db cfg for spec04 using dbheap 128000
db2 -v update db cfg for spec04 using locklist 16000
db2 -v update db cfg for spec04 using logbufsz 4096
db2 -v update db cfg for spec04 using seqdetect NO
db2 -v update db cfg for spec04 using logfilsiz 65535
db2 -v update db cfg for spec04 using logprimary 70
db2 -v update db cfg for spec04 using newlogpath /mnt/db2data/spec04/log
db2 -v update db cfg for spec04 using num_ioservers 1
db2 -v update db cfg for spec04 using num_iocleaners 1
db2 -v update db cfg for spec04 using softmax 16
db2 -v update db cfg for spec04 using dft_degree 1
db2 -v update db cfg for spec04 using MAXAPPLS 4000
db2 -v update db cfg for spec04 using MAXLOCKS 100
#db2 -v alter bufferpool ibmdefaultbp size 6815744
#db2 -v alter bufferpool ibmdefaultbp size 262144
#db2 -v alter bufferpool ibmdefaultbp size 1048576
db2 -v alter bufferpool ibmdefaultbp size 3000000

###########################################
db2 alter table append on
echo "append on customerinventory"
db2 alter table c_customerinventory append on
 
echo "append on o_orders"
db2 alter table o_orders append on
  
echo "append on o_orderline"
db2 alter table o_orderline append on
  
echo "append on o_workorder"
db2 alter table m_workorder append on
  
echo "append on o_largeorder"
db2 alter table m_largeorder append on
  
echo "append on s_purschase_order"
db2 alter table s_purchase_order append on
  
echo "append on s_purchase_orderline"
db2 alter table s_purchase_orderline append on
  
# index adjustment
echo "index on m_workorder"
db2 drop index M_wo_idx
db2 "CREATE UNIQUE INDEX M_wo_idx ON M_workorder (wo_number) PCTFREE 20 LEVEL2 PCTFREE 20 PAGE SPLIT HIGH"
  
echo "index on m_largeorder"
db2 drop index M_lo_idx
db2 "CREATE UNIQUE INDEX M_lo_idx ON M_largeorder (lo_id) PCTFREE 20 LEVEL2 PCTFREE 20 PAGE SPLIT HIGH"
  
echo "index on m_largeorder"
db2 drop index M_lo_cat_idx
db2 "CREATE INDEX M_lo_cat_idx ON M_largeorder (lo_category) PCTFREE 20 LEVEL2 PCTFREE 20 PAGE SPLIT HIGH"
  
echo "index on m_largerorder"
db2 drop index M_OL_O_idx
db2 "CREATE UNIQUE INDEX M_OL_O_idx ON M_largeorder (lo_o_id, lo_ol_id) PCTFREE 20 LEVEL2 PCTFREE 20 PAGE SPLIT HIGH"
  
echo "index on o_orderline"
db2 drop index O_ordl_idx
db2 "CREATE UNIQUE INDEX O_ordl_idx ON O_orderline (ol_o_id, ol_id) PCTFREE 20 LEVEL2 PCTFREE 20 PAGE SPLIT HIGH"
  
echo "index on customerinventory"
db2 drop index C_ci_idx
db2 "CREATE UNIQUE INDEX C_ci_idx ON C_customerinventory(ci_id) PCTFREE 20 LEVEL2 PCTFREE 20 PAGE SPLIT HIGH"
 
echo "index on customerinventory"
db2 drop index c_ci_cid
db2 "CREATE INDEX C_ci_cid ON C_customerinventory(ci_customerid) PCTFREE 20 LEVEL2 PCTFREE 20 PAGE SPLIT HIGH"
####################################
 
db2 -v alter table m_largeorder volatile
db2 -v reorgchk update statistics on table all
 
db2 connect reset
db2 terminate

db2set DB2_APM_PERFORMANCE=ON

db2set DB2_RR_TO_RS=YES
db2set DB2_PARALLEL_IO=*

db2set DB2LINUXAIO=true

db2set DB2_SCATTERED_IO=ON
db2set DB2_EXTENDED_OPTIMIZATION=ON

db2set db2comm=tcpip

db2stop

#echo "Flushing raw logs"
#dd if=/dev/zero of=/dev/raw/raw1 bs=256k count=1000

db2start

echo "Filling spec04log"
db2 connect to spec04
db2 connect reset

echo "Activating dbs"
db2 -v activate db spec04
