On 06-Apr-2012 14:59 , Stone, Joel wrote:
The file was created using DDS, has no null support (like all our
files), and the new version (with one field name chg, and several new
fields) will also be DDS.

As long as the usage is limited to scenarios that do not exceed the capabilities of CPYF to effect data copy without data loss, then I see little reason to use SQL over CPYF. That of course precludes the hope that such a CLP could generically "work to promote any file with field name changes AND new fields inserted into the middle of the record", *excepting* the imposed pre-conditions of no null-capable columns and only database files created with the DDS. And although I had mentioned only the caveat for data loss for the null values per ALWNULL, I am not sure if there might not be others; I simply do not recall any, nor is there anything else I see mentioned in the help text. If anything other than the column rename were to be in effect for the CPYF FMTOPT(*NOCHK) [excluding /text/ item changes], then that could be an entirely different story, because the effect of each modification [e.g. CCSID] would have to be understood.

Why create all this SQL when both the old and new DDS already

With SQL? Agreed. I do not recall having alluded specifically to using the SQL for DDL. I only recall suggesting the use of the SQL DML over CPYF, per the SQL enabling the data copy in just one pass. Any scripted SQL DDL that I may have presented would have been only for ease of a reader, because including a //DATA for each DDS and interspersing various CL requests [like CRTPF] within a script is more unwieldy than just presenting a script that is entirely SQL.

Regarding a temp version of DDS for just the name change, I am
planning on skipping that - at least skipping the manual creation of

I plan on using SQL to rename the field in a QTEMP version of the DDS
and QTEMP intermediate file - the target of CPYF *NOCHK.

Still leaving two copies. Seriously consider making the library name of the FROMFILE() come from a variable; reset to the value of &SAVLIB when no column renames are in effect.


Ovrdbf sourceFL QTEMP/QDDSSRC mbr(oldDDSname)
update sourceFL set srcdta = substr(srcdta,1,18) || 'NewFldName ' ||
substr(srcdta,29,51) where substr(srcdta,19,10) = 'OldFldName '

I think this might be preferable; cleaner and safer for PF DDS?:

update sourcFL
set srcdta=insert(srcdta, 19, 10, 'NewFldName ')
where substr(srcdta, 17, 01) in (' ', 'K')
and substr(srcdta, 19, 10)= 'OldFldName '

To be sure argument 03 [length] is correctly understood, that might be more safely described as [where the literals are not necessarily nor required to be padded to the full 10 bytes]:

update sourcFL
set srcdta=insert(srcdta, 19, length('NewFldName'), 'NewFldName')
where substr(srcdta, 17, 01) in (' ', 'K')
and substr(srcdta, 19, 10)= 'OldFldName'

The old and new field names would be extracted from a data record,
not hard-coded.

Since the code would know the names of the columns that are being renamed, to effect that UPDATE, then the SQL could also be used to effect the copy [insert into... select... from...] using a dynamically generated statement.

However since the effect of RENAME COLUMN is really nothing more than a CREATE VIEW, consider...

A different and much more simplistic dynamic SQL than the previously mentioned INSERT could be used in conjunction with the CPYF, while also allowing just one-pass for the data copy. The following SQL DDL CREATE VIEW against which just one CPYF FMTOPT(*MAP *DROP) could operate, should be just as simple as the above UPDATE to replace a column name?:

create view qtemp/oldddsfile as
( select old.*, old.OldFldName as NewFldName
from oldddsflib/oldddsfile as old
cpyf fromfile(qtemp/oldddsfile) tofile(newddsfile) ...

Using this method, I won't need to have the intermediate DDS around
as an extra object & source member.

The point is understood with regard to eliminating a pre-created and modified copy of the original DDS source member in the production source file. But of course pedantically, _literally_ that is untrue, because a source file and member objects would still be created in QTEMP as the intermediate; i.e. still "have around", albeit temporarily.

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].