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

I like this idea, Charles. However, there are 32 logical files on this
physical file. In order to keep the same format level identifier on all of
these, I would need to explicitly define all of the columns (except for the
new identity column) in each of these LFs. Doable, but a maintenance
nightmare.

What if we promised to never, ever reorganize the PF? (Which is virtually
guaranteed, given how it is used here.) Is that the only risk to trying to
do this in a view? If we wanted to try this, would we create an SQL view
with all the same columns + a GENERATED ALWAYS AS IDENTITY column?

Thanks,
(and thanks to all who responded!)
Dan

On Mon, Dec 12, 2016 at 4:30 PM, Charles Wilt <charles.wilt@xxxxxxxxx>
wrote:

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

--
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