Unique key in CMOD

[pankaj.puranik]

While indexing in CMOD, is there a ready made way of enforcing primary key on a particular index field?
I have an index field which I want should not have duplicates. So is there a way to enforce this?

Cheers
Pankaj.

[dr_te_z]

Yes it can be done in the DB2-layer. A lot of possibilities and I can help if you want. The CMOD layer on top requires more attention: when a db2-table is 'full' the application will fire some DDL to create a new table and start using that one. The new table will NOT have the new features by default so you will have to act pro-active on that.

[pankaj.puranik]

Ah got you!
So you mean to say that we manually mark the index column as primary key on the index table that CMOD is currently using.
We constantly monitor this table and once this table is full the new table will not cary over this primary key and hence we again manually mark the index as primary key.

[Justin Derrick]

I think the problem here is that it's difficult, if not impossible to enforce a unique key on an Application Group -- because CMOD uses multiple tables to keep query performance linear as the number of documents in the AG grows.  You might be able to enforce uniqueness of a key within a single table, but I don't think that's what you're looking for.

You might be able to get very close to what you're trying to do by increasing the number of rows per table to a very large number, like 100 to 200 million -- depending on your volume, a large number like this might cover all your documents for the duration they're kept in CMOD -- or at the very least would make sure that your documents have 'mostly unique' key fields.

[dr_te_z]

So you mean to say that we manually mark the index column as primary key on the index table that CMOD is currently using.

No. I was thinking of identity columns, sequence objects , triggers and so on.
http://db2portal.blogspot.com/2006/09/sequence-objects-and-identity-columns.html.
http://www.ibm.com/developerworks/data/library/techarticle/0205pilaka/0205pilaka2.html
Any column can be made unique in a reational table, enforced or not, primary key or not. Just think out-of-the-box.

Just be ware that those 'things'  are related to a table and CMOD creates new tables on-the-fly. CMOD thinks segments and uses tables for that purpose.
[cmod pseudo code]
so you expect me to insert the 1 000 000 000 and first row in that table. Let's not do that. Instead I'll create a new table for you and insert the row over there..... oops
[/cmod pseudo code]
When that happens all your "manual work"  on the old table is not copied to the new.  

[dr_te_z]

You might be able to get very close to what you're trying to do by increasing the number of rows per table to a very large number, like 100 to 200 million -- depending on your volume, a large number like this might cover all your documents for the duration they're kept in CMOD

You could store your "alter table DDL" in an INSERT TRIGGER and activate that trigger when the magical number is almost reached. This (before) trigger checks for the table-switch and triggers the execution of the DDL. Not sure if that is going to work but it would be a nice experiment in your test-system.

[Alessandro Perucchi]

Well if you want that each table have the same Layout of "definition" without using "manuel" steps, then the Tablespace User Exit is your friend here.
Then all the tricks and tips on DB2 to have unique key might work, or not... that part needs to be tested very carefully, as Justin said.

Sincerely yours,
Alessandro

[ewirtz]

Hi Pankaj,
current DB2 versions are able to maintain big data volumes without problems. You can deactivate segementation with the flag 'single table for all loads'. The benefit of segmentation can be reached (in a better way) with DB2 partitioning. So you will have only one table that will never be changed by CMOD. You can change on the DB2 side everything you want p.e. defining a primary key.

regards Egon

[Greg Ira]

You could store your "alter table DDL" in an INSERT TRIGGER and activate that trigger when the magical number is almost reached. This (before) trigger checks for the table-switch and triggers the execution of the DDL. Not sure if that is going to work but it would be a nice experiment in your test-system.

This method would work.  We currently do a similar thing by propagating a trigger automatically everytime a new segment is created.