How to Increase the Location Field Size (user_location)

Products

• CA Service Management - Asset Portfolio Management
• CA IT Asset Manager
• CA IT Asset Manager Asset Portfolio Management

Issue/Introduction

The default size for the location field (user_location) in CA Asset Portfolio Management (APM/ITAM) is 30 characters.

This may be insufficient for long building addresses or location strings, causing data truncation.

Environment

CA Service Management - Asset Portfolio Management (APM/ITAM) 17.x

Resolution

To increase the field size of the APM/ITAM location field from 30 to 200 characters, follow these steps. 

Prerequisites

• Perform a full backup of the APM/ITAM MDB database before executing any scripts
• Ensure you have database administrator rights or access to the `MDBADMIN` database account

**Warning:** The following steps involve direct database modifications. Execute these only after confirming a valid MDB database backup exists.

1.  Run the following script against your MDB database using a database management tool (e.g. SQL Plus, SQL Developer, SQL Management Studio):

-- Modify the physical table columns
   ALTER TABLE usp_owned_resource MODIFY user_location NVARCHAR2(200);
   ALTER TABLE aud_usp_owned_resource MODIFY user_location NVARCHAR2(200);

-- Update metadata definitions to reflect the change in the UI
   update arg_field_def 
   set data_size=200 
   where table_name='usp_owned_resource' 
   and field_name='user_location';

   update arg_field_def 
   set data_size=200 
   where table_name='aud_usp_owned_resource' 
   and field_name='user_location';

If using Oracle, commit the changes

COMMIT;

2.  For the changes to take effect in the ITAM Web interface, run the `IISRESET` command from an Administrator Command Prompt on both the ITAM Web and Application servers.

3.  Log in to the ITAM User Interface and verify that the Location field now correctly accepts and displays up to 200 characters.