######################################################################################
# DISCLAIMER: The use of this information or the implementation of any of these techniques is a customer 
# responsibility and depends on the customer’s
# ability to evaluate and integrate them into the customer’s operational environment.
# 
# This script create indexes into the performance test environment, based on test load
# For function -based indexes, it requires DB2 v10.5 or above.
#
# Optionally, append the following to each create index clause
#     ALLOW REVERSE SCANS COLLECT STATISTICS
######################################################################################

db2 connect to replace_with_the_actual_qm_dbname

########## begin of single vtable indices ###########
########## to improve default browse XXX pages 
db2 'CREATE INDEX "EXECUTION"."TER_JZ_PRJ_ARC_MOD_ITM_ST" ON "EXECUTION"."EXECUTION_RECORD" ( "JZ_DISCRIMINATOR" ASC, "PROJECT_AREA_ITEM_ID" ASC, "ARCHIVED" ASC, "MODIFIED" ASC, "ITEM_ID" ASC, "STATE_ID" ASC )'
db2 'CREATE INDEX "EXECUTION"."TER_JZ_ARC_MOD_PRJ_ITM_ST" ON "EXECUTION"."EXECUTION_RECORD" ( "JZ_DISCRIMINATOR" ASC, "ARCHIVED" ASC, "MODIFIED" DESC, "PROJECT_AREA_ITEM_ID" ASC, "ITEM_ID" ASC, "STATE_ID" ASC )'
db2 'CREATE INDEX "PLANNING"."JZ_ARC_PRJ_SNPS_ITM_ST" ON "PLANNING"."VERSIONABLE_ITEM" ( "JZ_DISCRIMINATOR" ASC, "ARCHIVED" ASC, "PROJECT_AREA_ITEM_ID" ASC, "SNAPSHOT" ASC, "ITEM_ID" ASC, "STATE_ID" ASC )'
db2 'CREATE INDEX "EXECUTION"."TCER_JZ_PRJ_ARC_ET_ITM_ST" ON "EXECUTION"."EXECUTION_TASK_RESULT" ( "JZ_DISCRIMINATOR" ASC, "PROJECT_AREA_ITEM_ID" ASC, "ARCHIVED" ASC, "END_TIME" ASC, "ITEM_ID" ASC, "STATE_ID" ASC ) '
db2 'CREATE INDEX "EXECUTION"."TCER_JZ_ARC_ET_PRJ_ITM_ST" ON "EXECUTION"."EXECUTION_TASK_RESULT" ( "JZ_DISCRIMINATOR" ASC, "ARCHIVED" ASC, "END_TIME" ASC,"PROJECT_AREA_ITEM_ID" ASC,"ITEM_ID" ASC, "STATE_ID" ASC ) '
########## end of single vtable indices ###########

########## begin of functional based indices #############
########## the following are function based indices for name search filters for large scale, only to add them to DB2 v10.5 and above #################
########## to improve searching via applying keywords in the Name filter 
db2 'CREATE INDEX EXECUTION.NAMECOL_UPPER_TER on EXECUTION.EXECUTION_RECORD(UPPER(NAME_COL))'
db2 'CREATE INDEX EXECUTION.NAMECOL_UPPER_TCER on EXECUTION.EXECUTION_TASK_RESULT(UPPER(NAME_COL))'
db2 'CREATE INDEX PLANNING.NAMECOL_UPPER_TS on PLANNING.EXECUTION_SCRIPT(UPPER(NAME_COL))'
db2 'CREATE INDEX PLANNING.NAMECOL_UPPER_CATG on PLANNING.CATEGORY(UPPER(NAME_COL))'
db2 'CREATE INDEX PLANNING.NAMECOL_UPPER_TC on PLANNING.TEST_CASE(UPPER(NAME_COL))'
db2 'CREATE INDEX PLANNING.NAMECOL_UPPER_TSU on PLANNING.TEST_SUITE(UPPER(NAME_COL))'
db2 'CREATE INDEX PLANNING.NAMECOL_UPPER_TP on PLANNING.TEST_PLAN(UPPER(NAME_COL))'
db2 'CREATE INDEX REPOSITORY.NAMECOL_UPPER_CTRR on REPOSITORY.CONTRIBUTOR_RECORD(UPPER(NAME_COL))'
db2 'CREATE INDEX REPOSITORY.USERID_UPPER_CTRR on REPOSITORY.CONTRIBUTOR_RECORD_USER_IDS(UPPER(USER_ID))'
########## end of functional based indices #############

db2 disconnect replace_with_the_actual_qm_dbname
echo "run runstats to make the indexes effective if needed"