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
exists?
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
it.
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.
Ie
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
midrange.com
