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
format.
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
<<SNIP>>
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
midrange.com
