Clarity Service Management

Expand all | Collapse all

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

Jump to Best Answer
  • 1.  How to create unique coulmn DBMS index in out of the box tables

    Posted 03-23-2016 03:13 AM

    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



  • 2.  Re: How to create unique coulmn DBMS index in out of the box tables

    Posted 03-23-2016 04:38 AM

    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



  • 3.  Re: How to create unique coulmn DBMS index in out of the box tables

    Posted 03-23-2016 06:05 AM

    Hi cdtj,

     

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

     

    regards,

    pradeep



  • 4.  Re: How to create unique coulmn DBMS index in out of the box tables
    Best Answer

    Posted 03-23-2016 06:17 AM

    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{}) );



  • 5.  Re: How to create unique coulmn DBMS index in out of the box tables

    Posted 04-07-2016 06:37 AM

    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



  • 6.  Re: How to create unique coulmn DBMS index in out of the box tables

    Posted 04-07-2016 06:58 AM

    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



  • 7.  Re: How to create unique coulmn DBMS index in out of the box tables

    Posted 04-07-2016 07:23 AM

    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



  • 8.  Re: How to create unique coulmn DBMS index in out of the box tables

    Posted 04-07-2016 07:38 AM

    Thanks cdtj for your prompt help and support.

     

    Regards,

    Pradeep



  • 9.  Re: How to create unique coulmn DBMS index in out of the box tables

    Posted 04-07-2016 07:49 AM

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



  • 10.  Re: How to create unique coulmn DBMS index in out of the box tables

    Posted 04-07-2016 08:36 AM

    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



  • 11.  Re: How to create unique coulmn DBMS index in out of the box tables

    Posted 03-23-2016 04:51 AM

    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