Hi Martin,
Could you conditionally call another procedure or external program to do the
alter table? That should stop you getting the duplicate message shouldn't
it?
Cheers
Colin.w
On 5 Jan 2009, 10:52 AM, "Mccallion, Martin" <Martin.Mccallion@xxxxxxxxx>
wrote:
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.
--
Martin McCallion
Senior Software Engineer
Misys
Solutions for Banking
One Kingdom Street, Paddington, London, W2 6BL
T +44 (0)20 3320 5242
F +44 (0)20 3320 1712
E martin.mccallion@xxxxxxxxx
skype: martin.mccallion
www.misys.com
"Misys" is the trade name for Misys plc (registered in England and Wales).
Registration Number: 01360027. Registered office: One Kingdom Street, London
W2 6BL, United Kingdom. For a list of Misys group operating companies please
go to
http://www.misys.com/html/about_us/group_operating_companies/. This
email and any attachments have been scanned for known viruses using multiple
scanners. This email message is intended for the named recipient only. It
may be privileged and/or confidential. If you are not the named recipient of
this email please notify us immediately and do not copy it or use it for any
purpose, nor disclose its contents to any other person. This email does not
constitute the commencement of legal relations between you and Misys plc.
Please refer to the executed contract between you and the relevant member of
the Misys group for the identity of the contracting party with which you are
dealing.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at
http://archive.midrange.com/midrange-l.
midrange.com
