PDF《Troubleshooting SQL problems in》

7423945 105007

97988 69014

67510 67482

54926 51359

37685 28501

27246 Code of the Top

10 ranking SQLs in the database script This script displays the top

10 SQL statements in the cache at the time of execution by the total execution time, the average execution time, the average CPU time, the number of executions, and the number of sorts. Usage: TopRankSQLS.ksh [-I <

Get the instance name>

] [-D <

Database name to connect to>

] [-M <

Mail id to which the logfile details would be sent>

# USAGE= Usage: TopRankSQLS.ksh [-I<

Get the instance name>

(mandatory)] [-D<

Database name to connect to>

(mandatory)] [-M<

Mail id to which the logfile details would be sent>

# Sample: ./TopRankSQLS.ksh -i db2pr1 -d PR1 -m [email protected] #set -x if [ -f $HOME/sqllib/db2profile ];

then . $HOME/sqllib/db2profile fi # Parse Input ibm.com/developerWorks/ developerWorks? Troubleshooting SQL problems in your database Page

5 of

8 while getopts :h:I:i:D:d:M:m: OPT ;

do case $OPT in h) echo ${USAGE} exit

0 ;

;

[Ii])LINSTNAME=`echo ${OPTARG} ` ;

;

[Dd])LSTDATABASENAME=`echo ${OPTARG} ` ;

;

[Mm])LMAILID=`echo ${OPTARG} ` ;

;

*) # Display the usage string echo ${USAGE} 1>

&

amp;

2 exit

1 ;

;

esac done shift `expr ${OPTIND} - 1` # Make the validation for the instance name if test -z ${LINSTNAME} then echo ${USAGE} exit

1 fi if [ ${LINSTNAME} != $DB2INSTANCE ];

then echo Incorrect Instance name provided echo ${USAGE} exit

1 fi # Make sure that the threeshold Value is entered if test -z ${LSTDATABASENAME} then echo ${USAGE} exit

1 fi DBCHECK=`db2 list active databases |grep ${LSTDATABASENAME} |wc -l` if [ $DBCHECK -eq

0 ];

then tput bold echo Incorrect Database Name Provided tput rmso echo ${USAGE} exit

1 db2 connect to ${LSTDATABASENAME} >

/dev/null if test $? -gt

0 then tput bold echo Unable to connect to database. Check the Database Name provided as input tput rmso exit

1 fi fi #Make sure that the threshold value is entered if test -z ${LMAILID} then echo ${USAGE} exit

1 fi echo Please find the Top

10 SQL&

apos;

s in the database based on Total Execution Time,Average Execution Time,Average CPU developerWorks? ibm.com/developerWorks/ Troubleshooting SQL problems in your database Page

6 of

8 Time,Number of Executions, Number of Sorts >

SQLRanK.out echo n \n >

>

SQLRanK.out #Get the TOP

10 SQLS by the Total Execution time echo 1) Top

10 Ranking SQL&

apos;

s by the Total Execution Time\n\n >

>

SQLRanK.out echo &

amp;

&

amp;

&

amp;

&

amp;

&

amp;

&

amp;

&

amp;

&

amp;

&

amp;

&

amp;

&

amp;

&

amp;

&

a db2 SELECT substr(stmt_text,1,50) as sql_statement, total_exec_time, total_sys_cpu_time, total_usr_cpu_time,num_executions, num_compilations FROM sysibmadm.snapdyn_sql ORDER BY total_exec_time desc FETCH FIRST

10 ROWS ONLY >

>

SQLRanK.out if test $? -gt

0 ;

then echo Error in getting TOP

10 SQL&

apos;

s by Total Execution Time >

>

SQLRanK.out fi #Get the Top

10 SQLS by Average Execution Time echo 2) Top

10 Ranking SQL&

apos;

s by the Average Execution Time\n\n >

>

SQLRanK.out echo &

amp;

&

amp;

&

amp;

&

amp;

&

amp;

&

amp;

&

amp;

&

amp;

&

amp;

&