Back in my previous job, we had a contractor who worked in our dept as a developer-cum-dev-dba, called Steve. He did wonderful things for us, mainly because he wrote (or updated so they actually worked) quite a few scripts that were used to roll out releases, etc.
He was extremely good at writing the scripts, and he’d often come out with a script just as it was needed, rather than say, doing something manual for ages before realising that it could be scripted. Of course, he had a lot of experience under his belt, so no longer needed to go through that manual period first, before realising that it would be better scripted.
In the past week, I’ve begun to turn into Steve! Every morning when I get into work, I check the log table in 7 schemas (on 7 different databases) to see if any errors occurred in the overnight runs. I’ve done this pretty much for the past 18 months, by using Toad, having a connection for each schema, and in the editor window, running the query in one schema, swapping to the next, rerunning the query, etc.
Would you believe that it was only last week that I thought “Why don’t I script this up?”? Anyway, I scripted it up, and what used to take 10mins or so now takes less than a minute, and the results are spooled to a file. Why didn’t I do this ages ago?! *facepalm* It’s now a simple matter to run the script, look at the results and decide whether any further investigation is needed or not. The results return much faster too (I guess the ordering on a time-restricted set of rows is easier than ordering over the entire result set!), and I feel unaccountably proud of myself.
The databases that I work on involve adding config into various tables, etc whenever a new overnight run is required, so I’ve been manually creating scripts to insert the config – usually by copying the config of an existing run and amending it as appropriate. In the spirit of Steveness that now appears to have descended upon me, when I was told we’d need to create several new runs based on existing runs, I decided to see if I could create a script that would at least do most of the donkey work for me.
It’s taken a while of trial and error to generate a select statement that would generate the correct insert statements(*), but I’m pretty much there now! It’s not perfect, as you still have to amend the resulting files to add in the new names and various amendments that are required, but obviously it’d take a lot more experience and some crystal ball technology before a psychic script generator is possible! *{;-)
I think it’s safe to say that I’m pleased with the results of the scripts, and they should save a fair amount of time, which is nice! (Even better, as generating release scripts has never been my favourite task…)
So, there’s hope that I may end up like Steve; able to see when it’s best to create a script or not, and obviously the more scripts I write, the better I’ll get at them and the more complex they can become!
(*) And let me tell you, there’s a reason why I hate dynamic SQL; faffing about with trying to work out how many quotes you need is a royal PITA!!
Leave a Comment