Re: Add a unique key field to view without redefining table

[Charles Wilt <charles.wilt@xxxxxxxxx>]

Can't be done safely in a view.  One RGZPFM and you are up the creek...

The right way to do this..
1) Create a SQL table with all the same columns + a GENERATED ALWAYS AS
IDENTITY column
2) Copy the data from the existing PF to the new table.
3) Recreate the existing logicals, pointing them to the new table with an
explicit field list that leaves off the new identity column.
4) Delete the old physical, create a new logical with the original
physical's name.  Again, with an explicit field list that leaves off the
new identity column.

Done correctly, you don't have to recompile any programs.

You can tell it's done correctly by checking the record format ID for all
the logicals + original physical...
Record Format List
                       Record  Format Level
 Format       Fields   Length  Identifier
 MYFORMAT         31      416  5530F7BBE8D09

As long as the format record identifier is the same before & after, you
won't get level checks.

Charles

On Mon, Dec 12, 2016 at 2:09 PM, Dan <dan27649@xxxxxxxxx> wrote:

> We have a table with millions of records and has no unique key defined, and
> no data exists in it for which a unique key could be defined.
> Historically, it has been ONLY a write and read only file; no updates or
> deletes.  The exception is that, sometimes, one of the developers will need
> to manually update a record.
>
> We have a new requirement to send inserts, updates, and deletes of several
> files to another system.  With this new requirement, we now need to
> uniquely identify all records in this file with no unique keys.  We are
> trying to avoid changing the definition of the physical file or its
> logicals, since there are 600 programs that use it.
>
> So, I am wondering if it is possible to create a view that "attaches" some
> kind of unique key value that the system manages.  I seem to vaguely recall
> that SQL provides some mechanism to do this, but my search failed either
> because I am wrong or I am using the wrong search terms.
>
> If this sounds workable, I just need the right terms to search and RTFM and
> find online articles.
>
> TIA,
> - Dan
> --
> 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.
>
> Please contact support@xxxxxxxxxxxx for any subscription related
> questions.
>
> Help support midrange.com by shopping at amazon.com with our affiliate
> link: http://amzn.to/2dEadiD