|
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
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.