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.