On 04-Apr-2012 14:51 , Stone, Joel wrote:
I'm trying to write a CL routine to promote a file with fields
renamed AND several fields added into the middle of the record

I am looking for an example data conversion program. If you have
one you could share to get me started, that would be great!

1) CPYF *nochk to copy the data
2) CPYF *map to move the data into the proper fields


Irrespective of CMS, the CPYF has no support to effect [well], the described changes; i.e. not without either significant data loss or using multiple passes of DDL and data-copy activity. The use of CHGPF SRCFILE(named) also can not assist AFaIK because it depends on CPYF FMTOPT(*MAP *DROP) which depends on the system column [not alias, nor in conjunction with alias] names; i.e. the same limitations exist for both the CHGPF-as-ALTER and the SQL ALTER TABLE, as for the use of CPYF.

Use the SQL instead, to implement such a change:

// given: create table orig (a char, i int, n numeric)
// strjrnpf orig /* or in effect already */
// changes: column name a renamed to c
// column name p added as second column
// column name n renamed to s
// run under cmtctl with automatic journaling for CREATE TABLE,
// and e.g. COMMIT(*CHG)
rename table orig to orig_rnm ;
create table orig (c char, p decimal, i int, s numeric) ;
insert into orig (c, i, s)
( select a, i, n from orig_rnm ) ;
// if all went well, then:
drop table orig_rnm ;
commit ;

As to how that can be made to fit properly in the promotion activity for the CMS being used... I have no clue.

In the last real CMS and delivery model I used [the OS and LPPs], the change would generally be to have the new version of the CREATE TABLE source simply replace the prior version of that "same" CREATE TABLE source. Then either the run-time or install\upgrade processing would effect the actual conversion work to /alter/ the existing object and copy the data on the system where the original file resides. That is, the object represented in the CMS was merely a model that was restored during an install\upgrade, enabling that object to be interrogated during install\upgrade or run-time in order to allow an installation exit program or some specific run-time entry-point to determine if the current version of the object [represented by the model-object] needs conversion to match the newer model.

Regards, Chuck

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2020 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].