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

Others have already answered but just thought I'd throw this in.

You *may* be thinking of "add unique constraint....".  That presumes, however, that you have columns from which a unique key can be discerned.  Or, maybe you're thinking of an identity column - generated by the database.

Roger Harman
COMMON Certified Application Developer - ILE RPG on IBM i on Power





From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxx> on behalf of Charles Wilt <charles.wilt@xxxxxxxxx>
Sent: Monday, December 12, 2016 1:30 PM
To: Midrange Systems Technical Discussion
Subject: 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

MIDRANGE-L Info Page
lists.midrange.com
To unsubscribe from MIDRANGE-L, get a password reminder, or change your subscription options enter your subscription email address:

or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

midrange.com -- MIDRANGE-L mailing list archive
archive.midrange.com
midrange.com MIDRANGE-L mailing list archive

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