How to create unique coulmn DBMS index in out of the box tables

[Pradeep_k]

Hi,

Please let me know if it is possible to create column in out of the box table with  unique DBMS box checked.

 

 

Regards

Pradeep

[Legacy User]

Hi Pradeep,

 

looks like there is no possibility to define Unique flag for OOTB tables, only for custom created.

BTW: You can create SPEL macro to avoid duplicates, example could be found here : https://communities.ca.com/message/241839474

 

Regards,

Timur

[Pradeep_k]

Hi cdtj,

 

Thanks for your reply, will your macro code work when administrator use pdm_load for data insert.

 

regards,

pradeep

[Legacy User]

Yeah, macro will validate all insertions performed vendor agreed way: via other script, web form or batch load,

except of SQL insertion which is not recommended.

Also I recommend to extend filter to check when object updating:

FILTER( EVENT("INSERT") || (zcustomattr{}) );

[Pradeep_k]

Hi cdtj

 

After creation of mod and spel file for uniqueness  on one custom column, it is working fine from web form.

 

But when i am trying from pdm_load, that validation is not happening and pdm_load allowing insertion of duplicate data.

 

Please help me here.

 

Regards,

Pradeep

[JEROME MAYER]

HI Pradeep,

This is normal behavior as pdm_load is doing direct sql insertion as mentioned by cdtj.

That why I never recommend the use of pdm_load but better web service in order to not bypass your object layer

/J

[Legacy User]

Hello Pradeep, Hello Jerome,

as I remember pdm_load (and pdm_userload, and dbload) should trigger spels.

I'll perform tests and give result.

 

Edited: yeah jmayer, you're right, pdm_load didn't trigger spel :C

 

Regards,

cdtj

[Pradeep_k]

Thanks cdtj for your prompt help and support.

 

Regards,

Pradeep

[Legacy User]

bad news, pdm_load, pdm_userload and even dbload, didn't trigger spels...

[JEROME MAYER]

I told you

The only way to enforce it there will be to add a key at the DBMS level but for me again this is bad habit to use those pdm_load/userload/dbload command.

I have see many people in the past corrupting their data without realizing and resulting on problem that some time surface long time after.

This is also not compliant with auditing.

my 2 cents

/J

[JEROME MAYER]

Up to my knowledge, DBMS option can only be set for custom tables

OOB tables are already have their DBMS index/constraints created by CA and modify them may have impact to system performance, then you must be carefull with this and I suppose that why this option is not available on those tables.

 

Hope this help

/J

 

edited: thanks for the time of testing cdtj  3 pair of eyes always better than only 1