Dave,
It's pretty simple; to ensure that you've done things properly, simply check that the Format Level
Identifier of the new logical matches the Format Level Identifier of the original physical.
Record Format List
Record Format Level
Format Fields Length Identifier
FCUSTOMP 39 253 4608F8EFC2394
Also long as the IDs are the same, you won't get any level checks. To get the same Format Level
Identifier, make sure the record format name, number/types/sizes/order of fields are the same.
If you don't currently have the DDS for the physical and existing logicals, you'll have to create
some. There are tools out there that will retrieve the DDS source of a file for you. DBU, has an
option to do so, or you can always build it manually. To make sure you've got the right DDS, you can
create the PF/LF file in a temporary library and check the Format Level Identifier against the
existing objects.
Simply use iSeries Navigator to retrieve the DDL for the current PF. Now rename the original PF. In
the retrieved DDL source, give the table a new name and add your identity or ROWID column and run it
to create the new PF. Now in the DDS for the original physical, change it to create a logical over
the new physical. After creating the replacement logical check the Format Level Identifier against
the saved PF. Once that all checks out, change the DDS for the existing logicals to point to the new
PF and re-create them.
Note, for performance reasons, you may want to identify the access paths used by existing logicals.
Then create SQL indexes for those access paths before recreating the logicals.
By going through this, not only will you add the unique key you need for DataPropagator, but your
existing apps/queries will see some performance benefits due to the use of SQL DDL to create the
physical (and indexes if you do them as recommended above).
The only additional concern is the possibility that the vendor code is doing something to the current
file that it won't be able to do to the logical view. For example, CLRPFM, ADDPFM, STRJRNPF won't
work on a logical. But it seems unlikely that a file you'd want to replicate would be subject to
those operations regularly.
It's really pretty easy to do once you've done it a couple of times. Don't hesitate to ask questions.
Charles Wilt
--
Software Engineer
CINTAS Corporation - IT 92B
513.701.1307
wiltc@xxxxxxxxxx
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-
bounces@xxxxxxxxxxxx] On Behalf Of Dave Odom
Sent: Thursday, May 29, 2008 8:19 PM
To: midrange-l@xxxxxxxxxxxx
Subject: RE: Best way to make a table of non-unique rows, unique?
Charles,
Thanks for your idea of creating a new PF and a LF that looks like to old
PF. Your last sentence in your note said "However, when done properly,
you won't need to recompile any vendor programs and the vendor application
should continue as if nothing has changed." Again, I like the idea but
how does one ensure "when done properly"?
Thanks,
Dave
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
This e-mail transmission contains information that is intended to be confidential and privileged. If you receive this e-mail and you are not a named addressee you are hereby notified that you are not authorized to read, print, retain, copy or disseminate this communication without the consent of the sender and that doing so is prohibited and may be unlawful. Please reply to the message immediately by informing the sender that the message was misdirected. After replying, please delete and otherwise erase it and any attachments from your computer system. Your assistance in correcting this error is appreciated.
midrange.com
