Issuing a REORG TABLE command to DB2 from VA Smalltalk (and Glorp)

You may have realized already that I misuse my blog and therewith you, my valued reader, as a swap space for small and maybe not so small little tricks I find in my day job from time to time.

And here is one little thing I just learned about how to invoke commands in DB2 that are not SQL statements form VA Smalltalk, in this specific case I mean REORG TABLE, but there are many other commands for which this may be useful.

Let me give you a little bit of context on why on earth I’d need that. Real men and even more so real DBA’s would simply fire up their DB2 command prompt and solve the problem at hand like a man. A simple table reorg would never stop a real man from saving the world in a day…

But here’s my problem. I am working on a Seaside Application in VA Smalltalk that is to be deployed to a Linux server. This application uses GLORP for persistence and I added some code that changes the database tables on server startup whenever I deploy a new image version. So if I add a new attribute to some persistent class, I have to add a new column to the underlying table(s). Some changes to the object model or some optimizations also need changes to foreign keys, indexes or even primary keys. These changes often cause DB2 to stop doing anything before I Reorg the modified table (Hint to IBM: Maybe that could be automated. The error message already tells me I need to Reorg, so why doesn’t it just do it for me???). The Error after such a change looks like this:

[SQLSTATE=57016 - [IBM][CLI Driver][DB2/LINUXX8664] SQL0668N  Operation not allowed for reason code "7" on 
table "MYTABLE".  SQLSTATE=57016
 [Native Error=-668]]

Yes, you’re guessing right: this error has made my life harder than I wanted it to be more than once in the past.

So one step in these schema migrations often is to change tables and then move data around, add foreign keys and stuff. In theory, that’s not too hard (I’ve learned a whole lot about this stuff from one of my friends and customers, hi Peter!). Unless DB2 gets into my way and tells me now that I’ve changed the primary key, I need to reorg the table first before I can change data.

Unfortunately, REORG TABLE is not a normal SQL statement. It is not intended to be used by normal SQL users and therefor cannot be issued just like a  normal SQL statement. Here’s what you get from DB2 if you inspect myGlorpSession accessor executeSQLString: 'REORG TABLE schema.tablename':

AbtError:  rc=-1 for '42601' in an AbtIbmCliCSDatabaseConnection at (24.04.2013 15:50:41)  '[SQLSTATE=42601 - [IBM][CLI Driver][DB2/LINUXX8664] SQL0104N  An unexpected token "TABLE" was found following "REORG ".  Expected tokens may include:  "JOIN <joined_table>".  SQLSTATE=42601
 [Native Error=-104]]

So this meant whenever I wanted to change a table’s primary key or add an index, I had to start the server once, stop it, use db2 to REORG tables by hand and start the server again. The steps of modifying the indexes and the following steps had to be separate migrations, each of them in their own transaction. Quite annoying.

But I found a solution to this problem. Real men, of course, knew it already. You can use a normal CLI client and issue admin commands by wrapping them into this:

myGlorpSession accessor executeSQLString:  'CALL ADMIN_CMD (''REORG TABLE schema.tablename'')' .

With this I can reorg a table first and then modify data in one single step during my server startup.

Doesn’t sound like a big deal? You’re wrong. This was a real hurdle to simple deployment. Just imagine you have to redo the same stuff on a development machine, a test server and a production server over and over again. And you must remember when to do what in the right order. Especially on a production server which shouldn’t be offline for too long, this is very important.

Before you ask: I am aware of the AUTO_REORG parameter, but as far as I know, Table reorganization can only run in an offline window. That’s not exactly what I am looking for. I need to do it in the middle of a migration script, not somewhen later tonight …