This script is made of 2 parts.  A shell script and a awk script.  The awk script is used to modify the output from printqry which is then loaded into a table so you can query on it.

The script also tries to calculate the qet (query execution time).

You need to have a database available to load the printqry details in.

Parameters to input are:

input_file = file name of your printqry output

output_file = output from running printqry through the awk script

database = database to load details into

You will end up with a table in your database that you can sort on execution time to find long running SQL.

The ksh script is shown first, followed by the awk script.

#!/bin/ksh
############################################################################
#
#   PROGRAM NAME: strip_printqry.ksh
#          USAGE: strip_printqry.ksh <input_file> <output_file> <database>
############################################################################
# Assign input params
INPUT_FILE=$1
OUTPUT_FILE=$2
DATABASE=$3
AWK_FILE=strip_printqry.awk
#
# Check all parameters are input
#
if [[ -z $INPUT_FILE ]]
then
echo ‘Usage: strip_printqry.ksh <input_file> <output_file> <database>’
exit
fi
if [[ -z $OUTPUT_FILE ]]
then
echo ‘Usage: strip_printqry.ksh <input_file> <output_file> <database>’
exit
fi
if [[ -z $DATABASE ]]
then
echo ‘Usage: strip_printqry.ksh <input_file> <output_file> <database>’
exit
fi
#
# Run awk script against printqry file to strip statements and timing
#
awk -f $AWK_FILE $INPUT_FILE >| $OUTPUT_FILE
#
# Insert details into a table
#
sql $DATABASE  << !
set autocommit on
\p\g
drop table strip_printqry_tmp
\p\g
create table strip_printqry_tmp (
qet  float4 not null with default
, stmt varchar(1900) not null with default
, st_tim varchar(12) not null with default
, en_tim varchar(12) not null with default)
\p\g
copy table strip_printqry_tmp (
stmt = char(0)’;’
, st_tim = char(0)’;’
, en_tim = c0nl)
from ‘$OUTPUT_FILE’
\p\g
update strip_printqry_tmp
set qet = (interval(‘secs’,date(en_tim)-date(st_tim)))
\p\g
/*
delete from strip_printqry_tmp
where qet = 0
*/
\p\g
modify strip_printqry_tmp to heapsort on qet
\p\g
!

exit

####################################### end of KSH script

strip_printqry.awk

{
ORS=” ”
if ($1==”Query”&&$2==”Send”)
{
print ” ;”$7 ” ;”
LINE=”no”
}
if ($1==”Query”&&$2==”Response”)
{
print $7
printf”\n”
LINE=”no”

}
if (LINE==”process”)
{
print $0
}
if ($1==”Query”&&$2==”text:”)
{
LINE=”process”
}
}