I have been writing a lot of my own IBMi diagnostic tools lately using Clojure and ClojureScript.
One of the problems I often encounter is needing to track down some data without having a great understanding of the tables and data I am querying.. So I built this tool to help me build queries.
When I select a machine, it selects a default schema and gives me a list of all possible tables. I select a table and press 'Get Sample' and it fetches me the 'Select * FROM table Limit 5' and displays the results. This is very helpful in that I can see the field names as well the format used for the data in each field. Sometimes my 5 samples don't give me enough information. Perhaps all five samples have the same value in a field or no value at all. I added a sample button in each row to help me with this case. When it is pressed, it fetches me the first five 5 distinct values from that field. Does this customer use this field at all? Is it what I think it is? Answers are often a click away.
At the bottom of the sample, I have a textbox in which I can type a SQL query. It's much easier now that I have a dynamically generated cheat sheet that gives me hints for any table.
It is already very helpful. Questions like "what date format" are no longer guesswork.
I plan to enhance it by having it suggest joins for me. I also plan to have it save queries for future use, and have it suggest those queries when a particular table is selected. I may add full interactive SQL capabilities to it. Time will tell.
Top comments (0)