Collect Cost Of all the db2 Stored Proc's in a simple Possible Way

Before writing this post i just need to convey that the kind of approach i'm going to tell you might be done by many others by this time , I have been trying out to find the easy way to find out my need and on my way i came across this and thought of sharing with you guys .

So "What if you came across a need in your DBA life where in you need to collect all the Query Costs of all the Stored Proc's or Packages in your Database ? "

Here is one i have tried 
db2 "connect to db-name" 
mkdir explains
cd explains 
db2 " select '! db2expln -d db-name -c schema-name -p '||BNAME||' -s 0 -o '||LTRIM(RTRIM(PROCNAME))||'.expln ;' FROM SYSCAT.PROCEDURES , SYSCAT.ROUTINEDEP WHERE SYSCAT.PROCEDURES.SPECIFICNAME = SYSCAT.ROUTINEDEP.ROUTINENAME AND SYSCAT.PROCEDURES.PROCSCHEMA='schema-name'" > explains.db2

Now the explains.db2 file have the db2expln commands necessary for execution and next step you need to do is  
vi explains.db2
remove the top unwanted lines and bottom ones and save the file and execute it like .....
db2 -tvf explains.db2
Now you explains folder will have all the packages explain plans with the file names as their package name .
Instead of using the grep utility for finding Cost for every file in explains folder , do a   small script work which makes your day easier .....
grep -nR "Estimated Cost" . |while read line ; do awk '{ if($4>100) print $0}' ; done
  Where this one finds all the costs above 100 , change the value to your desired one depending upon your requirement  

That's it you have done your work smart but 


NOTE : Be cautious of the load impact the server might have , So do it at some time where the application is not busy and remember the statistics need to be up to date and all the packages should be in a valid state , if you find the package to be in invalid state better rebind the package before taking the cost of the package .


1 comment: