Wow thanks for the in-depth analysis here.
I am unclear
what is the significant difference between having to name the columns in
an intermediate DDS versus having named them on an SQL DML statement,
such that the desire seems so strong to avoid the SQL.
OK now we are to the crux of the matter.
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.
Why create all this SQL when both the old and new DDS already exists?
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.
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 '
The old and new field names would be extracted from a data record, not hard-coded.
Using this method, I wont need to have the intermediate DDS around as an extra object & source member.
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of CRPence
Sent: Friday, April 06, 2012 3:10 PM
To: midrange-l@xxxxxxxxxxxx
Subject: Re: we are using Aldon Lifecycle Manager for System i - how to create a file conversion pgm
On 05-Apr-2012 08:47 , Stone, Joel wrote:
Here is what I have so far - am I going in the right direction? Or
totally off base?
<<SNIP>> Example of use: If file is CUSTFILE, then
orig DDS is &SAVLIB/QDDSRC-custfile (no changes);
temp DDS (with renamed fields for CPYF *nochk) is in
TPGMLIB/QDDSSRC-custfileT ("T" appended for temp),
and DDS with both change AND new fields is in
&OBJLIB/QDDSSRC-custfile (for CPYF *MAP *DROP).
I am hoping that this will work to promote any file with field name
changes AND new fields inserted into the middle of the record.
That is overly hopeful in my estimation. Limitations and caveats for
use of the FMTOPT(*NOCHK) feature of the CPYF utility will surely limit
the _general_ capability. That is, files with some specific\limited
Record Format definitions will be capable of being modified in that
manner, while others may have attributes for which caveats result in
data loss. Most notably [for DDS created files, I am not sure of what
else], the nullability or ALWNULL attribute can be maintained in the
RcdFmt in all versions of the file [old, temporary, and new], but the
loss of all null values is a side-effect for any data copy using the
FMTOPT(*NOCHK) feature.
Everyone is telling me (in general) this can't be done, both in my
company, Aldon, and on the BBS. What am I missing?
Can not be done, generically. Also can not be effected nicely, both
for the requirement to perform two passes\copies of the data, and having
to create an intermediate file. The SQL can effect the copy more
generally and generically [with input that describes the rename effects]
using dynamic DML, with no explicit intermediate copy. I am unclear
what is the significant difference between having to name the columns in
an intermediate DDS versus having named them on an SQL DML statement,
such that the desire seems so strong to avoid the SQL.
<<SNIP>>
/* ...TMP dds member doesnt exist? (no field name chgs), then use PROD DDS */
MONMSG MSGID(CPF7302) EXEC(CRTPF
<<SNIP>>
I would think that avoiding the FMTOPT(*NOCHK) would be advisable for
the above scenario, instead of accommodating the hard-coded references
to the library name QTEMP in the later CL command(s).? No reason to
make two copies in that scenario.
/* copy data to QTEMP file, renaming fields */
/* ALL fields including renamed fields MUST retain same position & length attr*/
/* (renamed fields MUST retain same position & length attributes) */
CPYF FROMFILE(&savlib/&objname) TOFILE(QTEMP/&objname) +
MBROPT(*REPLACE) FMTOPT(*NOCHK)
This copy, like the final copy from the temporary to the new file,
should use FROMRCD(1) TORCD(*END)
The changes in this phase really should be _nothing more than_ the
rename of the columns; i.e. ordinal position unchanged is implied, and
although non-attribute changes such as /text/ item [including editing]
changes could be done such as removing them all, there would be no
point. Attribute changes other than just /length/ may have impacts to
the /record buffer/ [i.e. the internal\storage for the field\column], so
those should be avoided [or well-understood as to the effects].
/* copy qtemp version back to NEW version with new fields added */
dltf &objlib/&objname
CRTPF FILE(&objlib/&objname) SRCMBR(&DDSMBR) +
SIZE(*NOMAX) SHARE(*YES)
CPYF FROMFILE(QTEMP/&objname) TOFILE(&objlib/&objname) +
MBROPT(*REPLACE) FROMRCD(1) FMTOPT(*MAP)
endpgm: ENDPGM
Interesting that the member SHARE attribute is explicitly specified
on that CRTPF, instead of being extracted from the old file.? Any other
customized member attributes are also being lost, because the CRTPF
defaults to MBR(*FILE) instead of specifying MBR(*NONE); admittedly,
that may be the intended effect.
Regards, Chuck
midrange.com
