On 19-Dec-2014 11:32 -0600, Dale Janus wrote:
<<SNIP>>
We modernize the database with DDL in ops navigator and add new
fields to the end like real dates, identity columns, sometimes a time
stamp, and new stuff with long field names. We keep the original 6
character field names on the existing fields as system name. Any new
fields get both a long name and a system name that is longer than 6
characters. Then we use the old physical file name as a logical
file.
The new physical, since created with DDL, does not have any DDS
specs. (AROPENSY in the example below)
We then modify this new DDS logical (that used to be a physical) to
include only the old fields with each old field name listed. And of
course the key, which has not changed and is an old field. New
fields are not listed in the DDS. <<SNIP>>
The above scenario, re-described as two scripts below [noting: the
lines staring with // characters need to be shifted left 4-bytes to form
valid CL stream, e.g. for use by SBMDBJOB, for which preceding the
script with //BCHJOB and ending the script with //ENDBCHJOB is also
required]:
• The old way:
//DATA AROPENDDS *SRC
A* PF:AROPENPF RCDFMT:ARREC
A* This DDS PF is uniquely keyed
A UNIQUE
A R ARREC
A*
A ARCODE 15P00
A ARPRFX 2A
A ARMNTH 2S00
A* etc.
A K ARCODE
//
CRTPF AROPENPF SRCFILE(AROPENDDS)
DSPFFD AROPENPF
CRTRPGPGM ... /* RPGIII pgm has F-Spec for AROPENPF */
• The new way:
//DATA AROPENSQL *SRC
-- PF:AROPENSY RCDFMT:ARREC
-- This SQL TABLE enhances PF AROPENPF RCDFMT:ARREC
-- The file name AROPENPF, since, will be a DDS LF
drop table AROPENSY cascade
;
create table AROPENSY
( ARCODE dec ( 15, 00) not null with default
, ARPRFX char ( 2) not null with default
, ARMNTH numeric ( 2, 00) not null with default
/* , primary key (ARCODE) */
/* new fields, the enhancements, follow: */
, ARaddedDateColumn for
ARdate date default current_date
)
;
//
//DATA AROPENDDS *SRC
A* LF:AROPENPF RCDFMT:ARREC
A* This DDS LF replaces PF AROPENPF RCDFMT:ARREC
A UNIQUE
A R ARREC PFILE(AROPENSY)
A*
A ARCODE
A ARPRFX
A ARMNTH
A* etc.
A K ARCODE
//
RUNSQLSTM SRCFILE(AROPENSQL) SRCMBR(AROPENSY) COMMIT(*NONE)
CRTLF AROPENPF SRCFILE(AROPENDDS)
DSPFFD AROPENPF
CRTRPGPGM ... /* RPGIII pgm has F-Spec for AROPENPF */
When we run DSPFFD on the logical, it shows all the fields, the old
with old names, but also the new fields longer than 6 characters that
RPGIII does not like.
How can I create a logical file with only the old field names yet points
to my new modern database defined by DDL?
The effects from running the above two CL streams, as verified by the
DSPFFD in both requests to DSPFFD AROPENPF, per reviewing the QPDSPFFD
spooled output for the PF in the old-way and the LF in the new-way, both
database files have the same RcdFmt LvlId of 2BBB20088FCCA which implies
they are quite likely the /same/ record format definition. A further
comparison of all columns shows matching names and attributes.
<<SNIP>>
I was hoping to ease into some of these new features, without having
to change every program all at once. So far, I am not doing so well.
<<SNIP>>
If the above scripted requests do not exhibit the same issue being
experienced, then by expanding them to be complete reproductions of the
scenario, perhaps either an error will become conspicuous [with what is
currently failing] or what are then apparent scripted re-creates, can be
shared to allow others to review and perhaps even experience the same
failure [by running the same scripting]. If the same issue does not
exist for others then presumably there is a missing fix\PTF that is
required. And if the same issue does exist for others, then perhaps
they can explain why the effect they see is [in]correct and how the
issue might be avoided with either modifications to the script or a
fix-level that eliminates the defect.?
midrange.com
