CA Service Management

 View Only
  • 1.  Integer field - Change to String

    Posted Mar 05, 2019 02:58 PM

    Way back in the day, via Web Screen Painter > DB Schema > ISS table, a custom field (zCustomer_Zipcode) was created as an Integer field type and has worked great until now.


    We now have customers from a different state where the zip code begins with a 0 (zero). When we create Issue tickets and enter a zip code that begins with a 0, it is removed when we save the ticket. I believe this is due to it being an Integer and not String.


    Is there a way for me to change the field type from Integer to String easily and not corrupt any data I already have entered into the field for my Issue tickets?



    thank you


  • 2.  Re: Integer field - Change to String

    Broadcom Employee
    Posted Mar 06, 2019 04:19 PM

    Hi Katherine,


    Please reference the post below:



    As noted here please test this out in a NON PROD environment, and take a backup before performing any changes, anytime data already exists there is a chance it can become corrupt.




  • 3.  Re: Integer field - Change to String

    Broadcom Employee
    Posted Mar 07, 2019 04:21 PM

    you could pdm_extract that custom field value + id to a file...change the type from integer to string, both in SDM and mdb...then pdm_load the data back...this way, no matter what happens on dbms side you won't lose a bit of data

  • 4.  Re: Integer field - Change to String

    Posted Mar 08, 2019 01:00 AM

    Yes. This plus Scott's answer are the full solution.


    Well, that and take a full backup before all of this is started, of course.

  • 5.  Re: Integer field - Change to String

    Posted Mar 08, 2019 09:07 AM

    Chi Chen,


    Can you help me out with the pdm_extract and pdm_load steps ... I am not that familiar with those commands (have only used them once before).


    Here is the information I have about my custom field if it helps:


    my select statement

          select *
          from dbo.wspcol
          where display_name LIKE 'zCustomer%'


    my results

          id = 400370
          persid = wspcol:400370
          table_name = iss
          column_name = zCustomer_ZipCode
          status = 3
          schema_name = zCustomer_ZipCode
          display_name = zCustomer_ZipCode
          type = 1             *** will be changed to 2 based upon a step in the other instructions from Scott
          string_len = 0
          is_wsp = 1



    thank you


  • 6.  Re: Integer field - Change to String
    Best Answer

    Broadcom Employee
    Posted Mar 08, 2019 11:42 AM

    Katherine, IF you don't have many records in issue table(for example, not a million records), you could


    pdm_extract -f "select id, zCustomer_Zipcode from Issue" > mydata.txt

    --go ahead change zCustomer_Zipcode data type in SDM and database


    pdm_extract -u -f mydata.txt

    However, if you have too many records, you would better do this from database side and your DBA can assist you with this. Doing pdm_load with a million records will take too long time.

    Thanks _Chi

  • 7.  Re: Integer field - Change to String

    Posted Mar 13, 2019 09:26 AM



    Thanks for all the information, I am going to work with my DBA on creating a new field and then copying the data from the current field to the new field via the backend.



    thank you