Adding columns conditionally using SQL

Hi list, and a Happy New Year to you all.

I haven't posted here for a long time, but I now find myself with a
seemingly-insurmountable problem.

Our application has to support multiple database platforms, including
DB2 for System i. As part of that, we have to be able to upgrade our
clients' systems when a new release comes out.

With the way we do things, though, we sometimes have clients at various
interim levels. So we have to write a database upgrade script that
copes with the possibility of a table having a particular column, or not
having it, in which case we have to add it.

This should be easily done with logic like this:

IF NOT EXISTS (
SELECT * FROM XXGMMM4.SYSCOLUMNS SC WHERE SC.TABLE_NAME =
'MESSAGEQUERYRESULTS' AND
SC.COLUMN_NAME = 'BATCHFILEID' )
THEN
ALTER TABLE XXGMMM4.MESSAGEQUERYRESULTS ADD
BATCHFILEID
FOR COLUMN BATCHFID INT;
END IF;

XXGMMM4 is just a placeholder that gets replaced with the real DB
library name.

Now the above doesn't work in interactive SQL; but you can put it in a
stored procedure, create that with RUNSQLSTM, and then call the
procedure from the controlling CL.

Trouble is, creating the stored procedure fails if the database has the
column in question. You get a level 30 SQL0612 message, "Position 9
BATCHFILEID is a duplicate column name."

I tried modifying the RUNSQLSTM to have ERRLVL(30), to force it to
create. That ought to be safe, since the conditional code should handle
things at runtime. That gets us a step further, but now it fails on the
CRTSQLCI command that is used under the covers to create the actual
program object. Same problem:

SQL0612 30 132 Position 46 BATCHFILEID is a duplicate column name.

I don't really want to get into changing the command defaults on the
CRTSQLCI command, not least because this will be running on clients'
machines.

I've realised while typing this is that one solution is to create the
stored procedure over a database that doesn't have the columns, and ship
the object with our upgrade utility. And that should work. But my
overall question remains: is there no easy way to run SQL on the iSeries
that says, "if this column does not exist, add it"?

Cheers,

Martin.