If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Data Access, Manipulation & Batch Languages > Unix Shell Scripts > script help please

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old 07-29-11, 23:29
db2girl db2girl is online now
∞∞∞∞∞∞
 
Join Date: Aug 2008
Location: Toronto, Canada
Posts: 1,830
script help please

I'd like to ask for some help again.


How can I extract the lines containing:

Number of executions
Total execution time
Statement text


and sort them by [ Total execution time / Number of executions ] in descending order



So, in my example, the output will look like:

Number of executions = 2
Total execution time (sec.microsec)= 10.291562
Statement text = select count(*) from sysibm.systables5

Number of executions = 5
Total execution time (sec.microsec)= 15.291562
Statement text = select count(*) from sysibm.systables3

Number of executions = 10
Total execution time (sec.microsec)= 20.291562
Statement text = select count(*) from sysibm.systables2



Thank you.
Attached Files
File Type: txt dyn.snap.test.txt (4.0 KB, 27 views)
Reply With Quote
  #2 (permalink)  
Old 07-30-11, 12:46
kitaman kitaman is offline
Papabi's friend
 
Join Date: Sep 2009
Location: Ontario
Posts: 629
Code:
#!/usr/bin/bash
IFS="="
rm -f temp temp1 temp2
while read a b
do
	if [ "a$a" = "a Number of executions               " ]
	then
		echo $b >>temp
	fi
	if [ "a$a" = "a Total execution time (sec.microsec)" ]
	then
		echo $b >>temp
	fi
	if [ "a$a" = "a Statement text                     " ]
	then
		echo $b >>temp
	fi
done <dyn.snap.test2
IFS=" "
while read numb
do
	read tot_exe_time
	read stmt
	average=`echo $tot_exe_time / $numb |bc`
	echo $average $numb $tot_exe_time $stmt >>temp1
done <temp
sort -r -n <temp1 >temp2
while read average numb tot_exe_time stmt
do
	echo "Number of executions =" $numb
	echo "Total execution time (sec.microsec)=" $tot_exe_time
	echo "Statement text =" $stmt
	echo  
done <temp2
Reply With Quote
  #3 (permalink)  
Old 07-30-11, 14:22
db2girl db2girl is online now
∞∞∞∞∞∞
 
Join Date: Aug 2008
Location: Toronto, Canada
Posts: 1,830
Excellent. Thanks a lot!

Is it possible to capture the complete sql text if it gets wrapped to multiple lines?

The exact number of lines is unknown, but there will always be two empty lines between the end of "Statement text" and the next section that starts with "Number of executions". For example:


Statement text = SELECT ................long complex statement............................blah......... .................................................. .................................................. .................................................. .... blah.............................................. .................................................. ................ end of statement
<empty line>
<empty line>
Number of executions = 6


Thanks
Reply With Quote
  #4 (permalink)  
Old 07-30-11, 15:21
kitaman kitaman is offline
Papabi's friend
 
Join Date: Sep 2009
Location: Ontario
Posts: 629
Does that imply that there is always exactly 3 lines for the statement?
Reply With Quote
  #5 (permalink)  
Old 07-30-11, 15:56
kitaman kitaman is offline
Papabi's friend
 
Join Date: Sep 2009
Location: Ontario
Posts: 629
Code:
#!/usr/bin/bash
IFS="="
rm -f temp temp1 temp2
while read a b
do
	if [ "a$a" = "a Number of executions               " ]
	then
		echo $b >>temp
	fi
	if [ "a$a" = "a Total execution time (sec.microsec)" ]
	then
		echo $b >>temp
	fi
	if [ "a$a" = "a Statement text                     " ] 
        then                                                   
               d=                                             
               read d                                         
               e=                                             
               read e                                         
               echo $b $d $e >>temp  
	fi
done <dyn.snap.test2
IFS=" "
while read numb
do
	read tot_exe_time
	read stmt
	average=`echo $tot_exe_time / $numb |bc`
	echo $average $numb $tot_exe_time $stmt >>temp1
done <temp
sort -r -n <temp1 >temp2
while read average numb tot_exe_time stmt
do
	echo "Number of executions =" $numb
	echo "Total execution time (sec.microsec)=" $tot_exe_time
	echo "Statement text =" $stmt
	echo  
done <temp2
This still runs, but the data does not prove that it works.

Five bucks a line plus HST.
Reply With Quote
  #6 (permalink)  
Old 07-30-11, 23:24
db2girl db2girl is online now
∞∞∞∞∞∞
 
Join Date: Aug 2008
Location: Toronto, Canada
Posts: 1,830
It works great! Thanks once again. The cheque is on its way
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On