Improvements to IFMX SQL Editor

The previously announced IFMX SQL Editor is now improved with some new features:

• option to choose whether to stop or continue execution when executing multiple commands
• abillity to change the window title (useful when working with multiple windows).

Also, all the config files are now located in the application directory (instead of users home).

For more info on IFMX SQL Editor, check out the project page. There is also new ready-to-use binary archive available for download (direct link), along with some getting started instructions.

New SQL Editor

Anyone remember Informix’s SQL Editor? I bet some of you still use it regulary. It’s simple and easy to work with, whether you are a database developer, a DBA or for whatever purpose you use SQL/SPL. I always liked that you can work with lot of SQL in the editor and execute just the selected commands, that you can execute more commands at the same time and get results in separate tabs, and that you can go through tabs with shortcut keys, so most of the time you can use keyboard only. It has some shortcomings, such as extremely annoying single level undo. And it’s a Windows application. However, it is light, doesn’t take gigs of memory, unlike some other database tools. And it is not available any more from IBM for quite some time now.

When I was migrating my desktop environment from Windows to Ubuntu several years ago, I needed a new SQL tool to manage my development and DBA tasks. As there were no light, reasonably fast, free solutions available (perhaps I’ll elaborate on this in some future post), I made one myself. I was learning Groovy in that time, so this was a great opportunity to explore the power of this language and make something that I’ll use. The goal was to make useful tool, very similar in appearance and functionality to Informix SQL Editor, with as simple code as possible. I’m using it on a daily basis for years now. And finally, I made an effort this month and open-sourced it.

So, without further ado, I’m proud to present the IFMX SQL Editor! It is released under GNU GPL license, and everything is available at GitHub as ifmx-sql-editor project – the code, downloads, wiki, issue tracker, etc.

As it is compiled to Java byte code, it can be executed on various operating systems. There is ready-to-use binary archive available for download (direct link), along with some getting started instructions.

Here are some screenshots to get the idea:

In summary, some of the main features:

• fully keyboard centric
• allows you to execute only a portion of SQL commands in editor
• allows execution of multiple SQL commands at once, displaying query results in separate tabs.

Improvements to original Informix SQL Editor:

• multilevel undo/redo
• SQL syntax highlighting (thanks to jsyntaxpane project)
• modification of the results grid – ordering, show/hide columns (thanks to SwingX project).

Further development is also planned. There will soon be some issues created in the project’s issue tracker.

Hope someone will find this piece of software useful. Comments and contributions are more than welcome.

Migrating your onconfig

The one really inconvenient thing about migrating an Informix instance is what the upgrade does to the onconfig file. Well, basically, it only adds the new config parameters to the end of file. So, what you lose is nice order and grouping of parameters with comments describing them. After doing a major upgrade on some instance, your onconfig would be a mess – fewer parameters would be documented in the file itself, and there’d be lots of new parameters at the end of file.

If you don’t want to live with this, you could take new version’s onconfig.std, copy it, run a diff to your current onconfig and manually insert your values in the copy. I like my production onconfigs neat, but as the onconfig grows, maintaining it becomes more time consuming.

So here is the much quicker solution. A groovy script that’ll do that for you. In one of my previous posts, I’ve described how to run groovy scripts. What the script does is take the onconfig.std, make a copy and put your values to the right places in that copy. Parameters that are commented out or don’t show in the std file are added to the end. Comments at the end of line containing actual parameter value are preserved. New onconfig file is named like the original one, with the “.new” at the end.

Here is a usage example. Prepare the current onconfig file for migration to version 11.70FC4:

groovy migrateOnconfig.groovy \
    -i $INFORMIXDIR/etc/$ONCONFIG \
    -s /opt/IBM/informix1170FC4/etc/onconfig.std

The output would be the $INFORMIXDIR/etc/$ONCONFIG.new file.

And here is the script itself. Just copy the code in a file called migrateOnconfig.groovy, and feel free to try it. There’s nothing to lose, as there is no impact to existing files.

/**
 * script to help migrating onconfig to whichever version
 *
 * provide actual onconfig file and onconfig.std of version
 * your migrating on
 *
 * parameters that have no default value in std are appended to the end of file
 *
 * @author Ognjen Orel
 */

// command line parsing specification
def cl = new CliBuilder(usage: getClass().getName() + ' options')
cl.h(longOpt: 'help', 'Show usage information and quit')
cl.i(argName: 'inputFile', longOpt: 'inputFile', args: 1, required: true, 
     'onconfig file currently in use, REQUIRED')
cl.s(argName: 'stdFile', longOpt: 'stdFile', args: 1, required: true, 
     'onconfig.std file to use as a template, REQUIRED')

def options = cl.parse(args)

File std = new File(options.s), output = new File(options.i + '.new')
List input = new File(options.i).readLines()

String param, additional
List written = new ArrayList()
def inputLines
def needsAdditional = ['VPCLASS', 'BUFFERPOOL']

output.delete()
output.createNewFile()

std.eachLine { stdLine ->
   // copy all comment or empty lines
   if (stdLine.startsWith('#') || stdLine.trim().isEmpty())
      output << stdLine + '\n'
   else {
      param = stdLine.tokenize()[0]

      if (needsAdditional.contains(param))
         additional = stdLine.tokenize(',')[0].tokenize(' ')[1]
      else
         additional = null

      inputLines = input.findAll{ 
          it.matches('(' + param + ')(\\s+)(.*)') || it.equals(param) }
      if (!inputLines.isEmpty()) {
         if (additional != null)
            inputLines = inputLines.findAll { it.contains(additional) }

         inputLines.each {
            output << it + '\n'
            written.add it
         }
      }
      else
         output << stdLine + '\n'
   }
}
// write all parameters with no default value in onconfig.std at the end
output << '\n\n### parameters with no default value in onconfig.std: \n\n'
(input - written).each {
   if (!it.trim().startsWith('#') && !it.trim().isEmpty())
      output << it + '\n'
}

Using Groovy in Daily DBA Work

Groovy is a relatively new programming language. I like to think of it as Java on steroids. It is based on Java, so all of Java code is automatically Groovy code, but not vice versa. Groovy is also compiled to Java byte-code, so it can run on every JRE. Better yet, you can use GDK (Groovy Development Kit) which provides many very easy to use APIs – working with files, XML is lot easier than in Java.

Some of its main characteristics are:

• it is real object oriented language,
• it is a dynamic language,
• it can be used for scripting.

This last thing means that you can write Groovy script file and execute it from the command line without the need to compile it first, and that’s what makes it appealing. You get to use real powerful language within simple script. Of course, lots of things you’d write in Groovy could be done using shell scripts and if you’re not a programming soul, you won’t find many usefulness in it. Nevertheless, some of my next posts will include ready-to-use Groovy scripts that you may find useful, so here are some hints on what you need to do in order to run Groovy on your PC or server.

First of all, you’ll need JRE (Java Runtime Environment), which is probably already installed on every desktop PC and most of servers. As you’re probably  going to run scripts from shell or command line, ensure that you have JAVA_HOME environment variable set. It should point to main Java install directory which contains bin, lib and other directories.

After that, you need to download Groovy binaries from Groovy download site. I suggest to download zipped binaries. This file is suitable for all operating systems, as libraries are all jar files, and binaries contain both windows batch files and Unix/Linux shell scripts. All you need to do after that is unzip the file wherever you find suitable.

Final step is adjusting your PATH environment variable. It should contain both Java binaries directory and Groovy binaries directory (e.g. /usr/java/bin and /usr/groovy/bin).

You can check if all is set well by executing the command “groovy” from the command line. It should output standard usage message, something like this:

[~]> groovy
error: neither -e or filename provided
usage: groovy [options] [args]
options:
  -a,--autosplit <splitPattern>    split lines using splitPattern (default '\s')
                                   using implicit 'split' variable
  -c,--encoding <charset>          specify the encoding of the files
  -D,--define <name=value>         define a system property
  -d,--debug                       debug mode will print out full stack traces
  -e <script>                      specify a command line script
  -h,--help                        usage information
  -i <extension>                   modify files in place; create backup if
                                   extension is given (e.g. '.bak')
  -l <port>                        listen on a port and process inbound lines
  -n                               process files line by line using implicit
                                   'line' variable
  -p                               process files line by line and print result
                                   (see also -n)
  -v,--version                     display the Groovy and JVM versions

So now we can start groovying. As a teaser, here’s a readable example: calculate bufwaits ratio based on onstat -p output (version 11.50xC9):

groovy -e "out='onstat -p'.execute().in.text.readLines(); \
pg=out[5].tokenize()[1].toLong(); \
bwr=out[5].tokenize()[6].toLong(); \
bwai=out[17].tokenize()[0].toLong(); \
println '\nBufwaits Ratio: ' + ((pg+bwr)>0 ? (bwai*100)/(pg+bwr) : 0)"

Although Groovy doesn’t require a semicolon at the end of the statement, it has to be used here to delimit the statements because this is actually one-liner. You could make a script out of it, bw_ratio.groovy, with the same contents:

out='onstat -p'.execute().in.text.readLines()
pg=out[5].tokenize()[1].toLong()
bwr=out[5].tokenize()[6].toLong()
bwai=out[17].tokenize()[0].toLong()
println '\nBufwaits Ratio: ' + ((pg+bwr)>0 ? (bwai*100)/(pg+bwr) : 0)

and execute it:

[~/]> groovy bw_ratio.groovy 

Bufwaits Ratio: 0.0602958416

In addition to Groovy home page with loads of examples and information, there are many sites dedicated to this powerful technology, just google for Groovy. There are some cool books about it, but I’d definitely recommend “Groovy in Action” authored by Dierk Konig.