Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367

    Unanswered: 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 Attached Files

  2. #2
    Join Date
    Sep 2009
    Location
    Ontario
    Posts
    1,057
    Provided Answers: 1
    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

  3. #3
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    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

  4. #4
    Join Date
    Sep 2009
    Location
    Ontario
    Posts
    1,057
    Provided Answers: 1
    Does that imply that there is always exactly 3 lines for the statement?

  5. #5
    Join Date
    Sep 2009
    Location
    Ontario
    Posts
    1,057
    Provided Answers: 1
    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.

  6. #6
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    It works great! Thanks once again. The cheque is on its way

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •