#!/bin/ksh
##########################################################3
# Script to monitor queries from xmp output
#
# Syntax: xmp_mon [ query_id [ frequency ]]
#
# xqs no longer shows interim numbers.  Go to xmp output for
# more data in detail
#
# Jack Parker / Robert Burba Summer/2000
# Permission is granted to copy, distribute and/or modify this document
# under the terms of the GNU Free Documentation License, Version 1.1
# or any later version published by the Free Software Foundation;
#
##########################################################3

disp_status()
{
	onstat -g xqp $1 > /tmp/xqp.tmp
	onstat -g xqs $1 > /tmp/xqs.tmp
	xctl onstat -g xmp > /tmp/xmp.tmp
	nawk '
	
	FILENAME=="/tmp/xqp.tmp"  {
		if (NR==1) {
			sw==0
			idx=0
			}
		if (sw==1) {
			if (NF>0) {
				idx++
				op[idx]=$1	# operation
				sg[idx]=$2	# Segment Id
				tb[idx]=$5	# Tabname (if any)
			}
		}
		if (substr($1,1,10)=="----------") {sw=1}
		next
	}
	FILENAME=="/tmp/xqs.tmp" {
		if (NF==0) { sw==0 }
		if ($4=="inst") {
			for (i=1;i<=idx;i++) {
				if (op[i]==$1 && sg[i]==$2) { 
					curr_idx=i
					}
			}
		}
		if ( sw==2 ) { 
			xqs_sum[curr_idx]=$0
			sw=0
			}
		if (substr($1,1,10)=="----------") { sw=2 }
		next
		}
	FILENAME=="/tmp/xmp.tmp" {
			# Toss all but this query plan
		if ($2==qid) {
			segid=$3
			opname=$6
			phase=$7
			nrows=$8
			onrows[segid,opname,phase]+=nrows
			thrds[segid,opname,phase]+=1
			srt[segid,opname,phase]=$9
			parent[segid,opname,phase]=$1
			}
		}
	END {
	# When there are multiple steps in one segment, the xmp output is repeated.
	# Change this piece to step through by segment, not by index.
		max_seg=0
		for (i=1;i<=idx;i++) {
			if (max_seg<sg[i]) {max_seg=sg[i]}
			}
		for (j=max_seg;j>=1;j--) {
			for (i=1;i<=idx;i++) {
				if (j==sg[i]) { 
					printf("%-10s %d %s %-18s\n", op[i], sg[i], xqs_sum[i], tb[i]) }
					}
			# separate loop, print all the xqs, THEN the xmp
			chld="0x0"
			hdr=0
			for (i=1;i<=idx;i++) {
			# printed twice, because there are two elements of the segment, 
			# test for last
			
			# Also, need to print this out as a link list, parent[x] points to srt[x]
			# Find an srt of "0x0".  Get THAT parent, start looking for him, if not found, exit
				if (j==sg[i] && sg[i] != sg[i-1] ) { 
					for (x in onrows) {
						n=split(x,y,SUBSEP)
						if ( y[1] == sg[i] ) {
							if (hdr==0) {
								printf("\t\tOperation   Phase    Threads                Rows\n")
								hdr=1
							}
							if (srt[x]==chld) {
							printf("\t\t%-8s  %8s	%d   %16d\n",y[2],y[3],thrds[x],onrows[x]) 
							chld=parent[x]
							# CHEAT - subtract from index to force go over ground
							i-=8
							if (i<1) {i=1}
# I dont like this solution, its sloppy, you should be using an associative array
# If I had realized in the beginning that a regular sort wasnt going to work I might have
# opted for that.  Alas, theres not much better we can do, we dont have the addresses 
# to use a link list until weve done the last set, so we cant assign them to an associative
# array on the fly.  We have to roll them up by segment, operation and phase.
							}
						}
					}
				}
			}
		}
	}
	' qid=$1 /tmp/xmp.tmp /tmp/xqp.tmp /tmp/xqs.tmp
rm -f /tmp/xqs.tmp /tmp/xqp.tmp /tmp/xmp.tmp
echo "\n"
}

get_queries()
{
onstat -g rgm | nawk '
BEGIN { a_sw=0 }

/^\(KB\)/ {
  totmem=$2
}

/---------------/ {
	if (a_sw==1) {a_sw++}
	}

/RGM Active Queue:/ {
	a_sw=1
        junk=split($6,xyz,")")
        ln=xyz[1]
	}   

{
if (a_sw>2) { 
	for (i=1;i<=ln;i++) {
		n=split($2,b,".")
		if ($5 != 0) {
			print $3, b[2], $4, $5, (($5/totmem)*100) >> "/tmp/queries.tmp"
		} else {
			print $3, b[2], $4, 0, 0 >> "/tmp/queries.tmp"
		}
		getline
		}
	a_sw=0
	}
}

/Lvl/ {
	if (a_sw==2) { a_sw++ }
	}

' 
}

disp_qry()
{
	onstat -g ses $1 > /tmp/ses_det.tmp
	uid=`grep $1 /tmp/ses_det.tmp | head -1 | awk '{print $3}'`
	iso=`grep Wait /tmp/ses_det.tmp | head -1 | awk '{print $5}'`
	rm -f /tmp/ses_det.tmp
	echo "User: $uid  Isolation: $iso  PDQ: $2  Plan: $5  Actual Memory: $3 ($4%)"
	onstat -g sql $1 | tail +9
}

if [ $# -gt 0 ]
then
	qry_id=$1
	if [ $# -gt 1 ]
	then
		freq=$2
	fi
else
	freq=10
fi

while true
do
	echo "===`date`============================"
	if [ -z "$qry_id" ]
	then 
		get_queries
		cat /tmp/queries.tmp |
		while read qry_id sess_id pdq actual_mem actual_pct
		do
			echo "==================================================="
			disp_qry $sess_id $pdq $actual_mem $actual_pct $qry_id
			disp_status $qry_id
		done
		rm -f /tmp/queries.tmp
	else
		disp_status $qry_id
	fi
	sleep $freq
done