RE: SQL Alter table: can I remove the UNIQUE attribute from a key?

Why use CMPPFM when SQL can compare the data? The SQL can easily
ignore the /key/ column and compare all of the row data as a SET; see a
very recent message from a somewhat recent thread about /comparing/
using the EXCEPT in a UNION.

How can I find thread?

I searched

http://archive.midrange.com/midrange-l/index.htm

for "sql union except"

How can SQL compare all fields except the key field WITHOUT naming each field?

Thanks



-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of CRPence
Sent: Thursday, May 02, 2013 2:00 PM
To: midrange-l@xxxxxxxxxxxx
Subject: Re: SQL Alter table: can I remove the UNIQUE attribute from a key?

On 02 May 2013 11:35, Stone, Joel wrote:

Vern Hamberg on Thursday, May 02, 2013 12:35 PM wrote:

Didn't he ask how to remove just the UNIQUE attribute?

Yes that is correct; I would PREFER to remove the UNIQUE attribute,
while leaving the field intact as a key.

So change the DDS to reflect what is wanted, and issue the CHGPF
SRCFILE() SRCMBR() where the SRCxxx parameters point to the updated DDS.

If that were not possible, then I would accept dropping the field
and re-adding the field as a non-unique key.

That would lose all of the data for the column; rarely a desirable
effect. The SQL does not have a concept of a physical key, except as
defined by a CONSTRAINT which only offers a UNIQUE constraint on the
data. The keyed access path would have to move to an INDEX.

Goal:

I am running a TEST pgm which outputs to a file, and also running
[almost the same version with a few small changes] PROD pgm.

I want to run CMPPFM but the key field (which is an incrementing
seq#) is one-off at times.

I thought an easy solution would be to clear that seq# prior to
CMPPFM, but the UNIQUE constraint won't allow that.

That does not seem very helpful, because the PROD version will still
have the column. CMPPFM would mis-compare on every record.?

If I can SQL ALTER the field to remove the UNIQUE constraint, then
set all keys to zero,

Rob's script shows how using SQL; the same can be done with ADDPFCST
and RMVPFCST, although there is an inquiry message sent using that
interface.

then I could compare the TEST vs PROD versions
of the file using CMPPFM.

Still confusing.... because the PROD version still has the non-zero
sequence numbers.?

Why use CMPPFM when SQL can compare the data? The SQL can easily
ignore the /key/ column and compare all of the row data as a SET; see a
very recent message from a somewhat recent thread about /comparing/
using the EXCEPT in a UNION.