Changing a table to have a new column before existing columns

I need to change a table to add a column; this would be straightforward
with the ALTER TABLE SQL statement, if the new column could go on the
end of the row.  However, for reasons that I won't go into unless people
are interested, the new column has to be inserted before some existing
columns; and the existing data has to be preserved.

With a DDS-defined file this would be easy: modify the DDS, CHGPF with
the SRCFILE parameter, and you're done.  However, this is an SQL-defined
table (it's part of a multiplatform solution).

I gather that, in some other SQL dialects, you can create a temporary
copy of the data, using SELECT INTO, which would allow me to save the
data, drop and create the table, and copy the data back.  However, as
far as I can tell from the documentation and various websites, this
doesn't appear to work on the iSeries.  I read something that suggested
that this should be possible using INSERT INTO, as long as the temporary
file exists first, but I couldn't make a brief test of this work.

I am in a position to write an iSeries-specific upgrade program, so I
can use CPYF to save the data, RUNSQLSTM to drop views and indexes and
the table, and then re-create them all, and CPYF *MAP to put the data
back.  However, this seems rather clumsy when compared with the modern
DDS version mentioned above.  If possible I'd like to do it all with
SQL.  Any ideas?

I have, of course, searched the web (and specifically the list archives)
for a solution to this, but to no avail.

Cheers,

Martin.

-- 
Martin McCallion
Senior Technical Consultant
Misys Wholesale Banking Systems
1 St George's Road, London, SW19 4DR, UK
T +44 (0)20 8486 1951
F +44 (0) 20 8947 3373
martin.mccallion@xxxxxxxxx
www.misys.com

This email and any attachments have been scanned for known viruses using
multiple scanners. We believe that this email and any attachments are
virus free, however the recipient must take full responsibility for
virus checking. This email message is intended for the named recipient
only. It may be privileged and/or confidential. If you are not the
intended named recipient of this email then you should not copy it or
use it for any purpose, nor disclose its contents to any other person.
You should contact the Misys Banking and Securities Division as shown
below so that we can take appropriate action at no cost to yourself. 

Misys Banking and Securities Division, 1 St George's Road, Wimbledon,
London, SW19 4DR, England. Email: banking.postmaster@xxxxxxxxxx Tel: +44
(0) 20 8879 1188 Fax: +44 (0) 20 8947 3373