Use of unique index

[ibmarthin]

To avoid dublicate values, we want to have Unique index on a column "Customer_ID". In the Application Group Identifier the table have the name GBA1, and Maximum rows is set to 100.000.

What happens when reaching 100.001 rows and the table expands to GBA2 ?
Does OnDemand automatically to set this Unique index on GBA2, GBA3 etc. or do we have to remember this and watch when tables are expanding?

[Justin Derrick]

Hi Marthin.

Before I get started, CMOD really isn't the place to be enforcing this type of thing.  The 'uniqueness' of incoming data should probably be managed by the upstream system, and not managed at the CMOD level.  Maybe you have a unique use case (maybe you could tell us more about your situation?) but I've never seen this requirement before.

Next, I don't think there's a way to enforce this type of uniqueness through the CMOD Admin client.  If you're considering altering the table definitions at the database level, I need to advise you that this sort of modification isn't supported by IBM and could cause unforeseen issues.

Having said all that, if you decide to go down that road, you'll want to make your tables as large as possible, setting your maximum rows parameter to accomodate at least a year's worth of data.  Because of the table segmentation in CMOD (and the fact that I think you're doing something that isn't supported) you'll end up with one unique customer ID per year.

If you can give us more details on your use case, it might help us answer your question better.

-JD.

[ibmarthin]

Hi Justin

The reason why the customer wants to have Unique Index, is the fact that they discovered two customers having same customer number. I agree that this should have been handled before loading into OnDemand.

So I think we have to watch when the tables expand, and set the Unique index on the new table then.
It has so far been this way for several month with no problems, but the table has not expanded yet....