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

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