Personal tools
You are here: Home DB2 How To's Format snapshot for dynamic SQL
Navigation
Log in


Forgot your password?
 
Document Actions

Format snapshot for dynamic SQL

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

fi


##

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

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

          }

}'  ${FILE} > ${FILE}_DYNSQL_analysis.txt


echo "Look for ${FILE}_DYNSQL_analysis.txt"


chmod 755 ${FILE}_DYNSQL_analysis.txt


exit

Security Awareness
Would you like your company to implement gamification into your security awareness program?





Polls