Document Actions

Format snapshot for dynamic SQL

#!/usr/bin/ksh#

#########################

####### ########

##### MAIN PROC ######

####### ########

#########################

# Check input

usage="\n Usage: $0 <DynamicSQL Snapshot File> "

example="\n Example: $0 ECCM_DYNSQL.snap \n"

if [[ $# < 1 ]]

then

echo "${usage}"

echo "${example}"

exit

# Initialize Command line Variables

FILE=${1}

echo Processing File ${FILE} for Database ${DB}

awk 'BEGIN { FS="="

numexec = "Number of executions"

numcomp = "Number of compilations"

wrstprep = "Worst prep time"

bestprep = "Best prep time"

irowsd = "Internal rows deleted"

irowsi = "Internal rows inserted"

rowsr = "Rows read"

arowsr = "Average Rows read"

irowsu = "Internal rows updated"

rowsw = "Rows Written"

arowsw = "Average Rows Written"

sorts = "Statement Sorts"

asorts = "Average Statement Sorts"

sorto = "Statement Sort Overflows"

asorto = "Average Sort Overflows"

sortt = "Statement Sort Time"

asortt = "Average Sort Time"

bpdlr = "BP Data Logical Reads"

abpdlr = "Average BP Data Logical Reads"

bpdpr = "BP Data Physical Reads"

abpdpr = "Average BP Data Physical Reads"

bptdlr = "BP Temp Data Logical Reads"

abptdlr = "Average BP Temp Data Logical Reads"

bptdpr = "BP Temp Data Physical Reads"

abptdpr = "Average BP Temp Data Physical Reads"

bpilr = "BP Index Logical Reads"

abpilr = "Average BP Index Logical Reads"

bpipr = "BP Index Physical Reads"

abpipr = "Average BP Index Physical Reads"

bptilr = "BP Temp Index Logical Reads"

abptilr = "Average BP Temp Index Logical Reads"

bptipr = "BP Temp Index Physical Reads"

abptipr = "Average BP Temp Index Physical Reads"

xtime = "Total exec time"

atime = "Average exec time"

ucpu = "Total user cpu"

scpu = "Total system cpu"

text = "Text"

printf( "%s~", numexec )

printf( "%s~", numcomp )

printf( "%s~", wrstprep )

printf( "%s~", bestprep )

printf( "%s~", irowsd )

printf( "%s~", irowsi )

printf( "%s~", rowsr )

printf( "%s~", arowsr )

printf( "%s~", irowsu )

printf( "%s~", rowsw )

printf( "%s~", arowsw )

printf( "%s~", sorts )

printf( "%s~", asorts )

printf( "%s~", sorto )

printf( "%s~", asorto )

printf( "%s~", sortt )

printf( "%s~", asortt )

printf( "%s~", bpdlr )

printf( "%s~", abpdlr )

printf( "%s~", bpdpr )

printf( "%s~", abpdpr )

printf( "%s~", bptdlr )

printf( "%s~", abptdlr )

printf( "%s~", bptdpr )

printf( "%s~", abptdpr )

printf( "%s~", bpilr )

printf( "%s~", abpilr )

printf( "%s~", bpipr )

printf( "%s~", abpipr )

printf( "%s~", bptilr )

printf( "%s~", abptilr )

printf( "%s~", bptipr )

printf( "%s~", abptipr )

printf( "%s~", xtime )

printf( "%s~", atime )

printf( "%s~", ucpu )

printf( "%s~", scpu )

printf( "%s~", text )

printf( "\n" )

numexec = ""

numcomp = ""

wrstprep = ""

bestprep = ""

irowsd = ""

irowsi = ""

rowsr = ""

arowsr = ""

irowsu = ""

rowsw = ""

arowsw = ""

sorts = ""

asorts = ""

sorto = ""

asorto = ""

sortt = ""

asortt = ""

bpdlr = ""

abpdlr = ""

bpdpr = ""

abpdpr = ""

bptdlr = ""

abptdlr = ""

bptdpr = ""

abptdpr = ""

bpilr = ""

abpilr = ""

bpipr = ""

abpipr = ""

bptilr = ""

abptilr = ""

bptipr = ""

abptipr = ""

xtime = ""

atime = ""

ucpu = ""

scpu = ""

text = ""

} \

{

if($0 ~ /Number of executions/)

{

numexec = $2

if(numexec == 0)

div = 1

else

div = numexec

}

if($0 ~ /Number of compilations/)

numcomp = $2

if($0 ~ /Worst preparation time/)

wrstprep = $2

if($0 ~ /Best preparation time/)

bestprep = $2

if($0 ~ /Internal rows deleted/)

irowsd = $2

if($0 ~ /Internal rows inserted/)

irowsi = $2

if($0 ~ /Rows read/)

{

rowsr = $2

arowsr = rowsr/div

}

if($0 ~ /Internal rows updated/)

irowsu = $2

if($0 ~ /Rows written/)

{

rowsw = $2

arowsw = rowsw/div

}

if($0 ~ /Statement sorts/)

{

sorts = $2

asorts = sorts/div

}

if($0 ~ /Statement sort overflows/)

{

sorto = $2

asorto = sorts/div

}

if($0 ~ /Total sort time/)

{

sortt = $2

asortt = sorts/div

}

if($0 ~ /Buffer pool data logical reads/)

{

bpdlr = $2

abpdlr = bpdlr/div

}

if($0 ~ /Buffer pool data physical reads/)

{

bpdpr = $2

abpdpr = bpdpr/div

}

if($0 ~ /Buffer pool temporary data logical reads/)

{

bptdlr = $2

abptdlr = bptdpr/div

}

if($0 ~ /Buffer pool temporary data physical reads/)

{

bptdpr = $2

abptdpr = bptdpr/div

}

if($0 ~ /Buffer pool index logical reads/)

{

bpilr = $2

abpilr = bpilr/div

}

if($0 ~ /Buffer pool index physical reads/)

{

bpipr = $2

abpipr = bpipr/div

}

if($0 ~ /Buffer pool temporary index logical reads/)

{

bptilr = $2

abptilr = bptilr/div

}

if($0 ~ /Buffer pool temporary index physical reads/)

{

bptipr = $2

abptipr = bptipr/div

}

if($0 ~ /Total execution time/)

{

xtime = $2

atime = xtime/div

}

if($0 ~ /Total user cpu time/)

ucpu = $2

if($0 ~ /Total system cpu time/)

scpu = $2

if($0 ~ /Statement text/)

{

text = substr( $0, index($0, "=") +2 )