How often has your DBA wanted to be able to quickly get a set of files containing the SQL of commonly run queries? Perhaps you'd like to make sure you version that SQL to make sure that you can see how it changes over time. With a little PeopleCode, you can do this pretty easily.
Take a look at what you can get when you version your query SQL. Pretty cool, right?
Yeah... How'd you do it?
Well, let me walk you through it one step at a time. Otherwise, the PeopleCode can get a little daunting. In order to simplify things, this example creates a simple app engine program with one section in it containing PeopleCode. This way, it can be run anytime you want (and even scheduled).
Step 1
The first step is to open a query and prove you did it successfully. Here's a link to the code that does this.
Step 2
Step 2 is to extend the PeopleCode to get the metasql property of the query object. Here are the changes to the original code (you may want to play with the settings of the difference viewer to see more of the code).
Step 3
Step 3 is to create a file from the query we've opened and gotten the SQL from. Here are the modifications for that.
Step 4
Step 4 is to loop through all the queries that beging with "GS" and create a filename with the queryname in it. Here are the changes for this.
So, here's the final Peoplecode
Check it out. Most likely, you've also seen your first set of code in Code.GreySparling.com.
Potential Enhancements
The code discussed here does have a number of places where it should be enhanced as part of deploying it in your environment. Those can be viewed here.
Labels: query sql code
© 2011 Grey Heller Solutions.
All rights reserved.
I though of doing an enhancement on top of this . In my environment lots of users create thier own queries with bad performance and it is quite tedious to pull them out . I though of Gluing this code with Plan table and extracting cost of new query . Some how , Execute plan will provide your cost of query even though you have bind variables in it but if you type same stuff through SQLexec , it asks for a bind variable which process might not have . any suggestion on this ?
The great thing about using our version control server for sharing code is that it is a living thing. I've just entered a ticket with your enhancement idea for this project (as well as thoughts as to how to implement it).
http://code.greysparling.com/ticket/25
If you'd like to add your own comments or create new tickets, all you need to do is register:
http://code.greysparling.com/register
You can also register and put yourself on the interested parties of this ticket (since I expect to see this feature implemented in the near future ;-)