Remove UNIQUE flag on custom field

Products

• CA Service Management - Service Desk Manager
• CA Service Desk Manager

Issue/Introduction

Within Web Screen Painter when creating a custom field, there exists the option to select the UNIQUE flag under the 'DBMS Index Option' section.

Once this option is selected and the schema changes saved and published, it is not possible to uncheck the UNIQUE flag as the option is greyed out (see screenshot)

How can the UNIQUE flag be removed for custom fields?

Environment

CA Service Desk Manager 17.x

 

Resolution

For this example, there is custom table called 'z_tspCode' and within the table there is a field called 'problemID' which has the UNIQUE flag checked

When we set a field as UNQUE via WSP, the following things happens:

• NX_ROOT\site\mods\wsp_index.sch will have

INDEX SORT ASCENDING UNIQUE problemID ;

• NX_ROOT\site\mods\wsp_schema.sch and ddict.sch will have

problemID REAL UNIQUE;

• WSPCOL MDB table will be updated
• SQL Index is added

You can try the following steps (test this in a DEV environment first):

1. Extract WSPCOL MDB table info

pdm_extract -f "select id, table_name, column_name, is_indexed, is_unique from wspcol where column_name like 'problemID' and table_name = 'z_tspCode'" > wspcol.txt

2. Output file should look like this:

TABLE wspcol
id table_name column_name is_indexed is_unique
{ "400002" ,"z_tspCode" ,"problemID" ,"1" ,"1" }

3. Using a text editor, change the "1" to "0"

TABLE wspcol
id table_name column_name is_indexed is_unique
{ "400002" ,"z_tspCode" ,"problemID" ,"0" ,"0" }

4. Via a administrator command prompt on the SDM Primary/BG Server, upload the modified file

pdm_userload -v -f wspcol.txt

5. Run the following command to refresh the WSPCOL table cache

pdm_cache_refresh -t wspcol

6. Restart the SDM service to clear the WSP cache

7. Via WSP, perform a "dummy update". For example, go to the definition of the problemID field and at description field add or remove a character

8. Save and publish via WSP. This will create a new schema and the .sch files above will be updated

9. With the CA SDM service stopped, via SQL Management Studio

- search for for table z_tspCode
- open the indexes entry
- check the index related with ProblemID
- If the index found is z_tspCode_X0, then delete the index
--> drop index z_tspCode_X0 on z_tspCode

10. Restart the CA SDM service