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

Wow that sounds like a great utility!

Especially the option to omit certain fields.

Maybe time to go to market with this one??

Thanks for the ideas.

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

On 5/2/2013 2:35 PM, Stone, Joel wrote:

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.

Many years ago I wrote a utility to do this. Often, I want to compare a
test version of a file against a production version but the date / time
stamps will always show as 'different'.

The code is dependent on subprocedures and utilities I wrote and bundled
in my 'toolkit' service program so it's not easy to share. The general
idea is this:

Compare two files column by column. Display each difference tagged by
the key value - some common value between the two files. Example:
Customer file. Key = customer number. Omit date stamp fields. Add
FIRST and LAST to display along with the key (CUST#).

1) Command allows 2 qualified files, 10 keys, 10 'additional' columns to
display in addition to the keys and 10 fields to omit from the comparison.
2) Command processing program creates a temporary CL source file; calls
RPG program
3) RPG program builds the CL program source line by source line. The
commands feed a utility like 7.1s RUNSQL.
a) Write the PGM
b) Write OVRDBFs
c) Write TEST exception join PROD
d) Write PROD exception join TEST
e) Use QUSLFLD API to list all the fields in the table
i) Loop through the list
ii) If the field is on the omit list, skip it
iii) Do a SELECT COUNT(*) TEST join PROD where TEST.FIELD <>
PROD.FIELD If > 0, write SELECT key, additional(s), TEST.FIELD, PROD.FIELD
f) Write ENDPGM
4) Compile the CL program
5) CALL that CL program.

An example looks like this:

CMPF PRDF(PROD/FILE1)
TSTF(TEST/FILE1)
KEYS(SOCSEC)
ADLFLD(FIRST LAST)
OMTFLD(DATE)

rs 'select tst.SOCSEC, prd.FIRST, tst.FIRST, prd.LAST, tst.LAST,
prd.LOCNO, tst.LOCNO from tst join prd on prd.SOCSEC=tst.SOCSEC where
prd.LOCNO<>tst.LOCNO order by 1' output(*print)

I hope this gives you some ideas to work with. This is really old code
and you could probably do it all in a single RPG program these days.
This works for me and I'm not re-writing it... I have other stuff to get
to first :-)
--buck