Skip to Content
author's profile photo Former Member
Former Member

Changing the Collation without rebuilding the DB

Hi Gurus,

I am looking for an easy option to change the collation of my SQLA10 DB from 'windows-1252' to 'UTF-08'.

I know that I can do it using unload, create new DB with 'UTF-08' and reload the data back into the new DB.

I have few challenges in doing so and trying to avoid this cumbersome process.

1. The no of database that need to be changed - around 1000

2. I found that this is not a easy and straightforward process and faced lot of errors during loading the data. e.g. right truncate of string data, foreign key errors.

3. The cost of supporting the users if any error occurs- As the no. of DB are huge, if any errors occur during the migration may lead to spend lot of time.

I need to know if anyone faced similar kind of issue and have any quick solution to handle it.

The reason I want to change collation to 'UTF-08' is to support Non-English characters in the DB and also have to export it into XML files.

Any help will be appreciated.

Thanks

Ramendra

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • Posted on Jul 13, 2016 at 02:28 AM

    Hi Ramendra,

    I recommend the following methods.

    https://wiki.scn.sap.com/wiki/display/SQLANY/Changing+the+Collation+and+Codepage+When+Rebuilding+an+SQL+Anywhere+Database


    I do not know the person who tried a different method.


    Thanks,

    Atsushi

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member Atsushi Asano

      Hi Atsushi,

      Thanks for sharing your inputs.

      I did given another try by switching off string_rtruncation and able to unload the data successfully with 'UTF-08' collation.

      set option public.string_rtruncation = 'Off'

      But referring to Volker's point below, I think, I also should be considering the actual storage in char data type.

      I will verify the overall impact before migrating.

      Regards

      Ramendra

  • Posted on Jul 15, 2016 at 10:48 AM

    Hi Ramendra,

    there may be a fundamentally different approach to your issue, but I don't know if that's possible in v.10. Btw I think it's somewhat odd to run a system that you can't afford to rebuild on a software that has been unsupported for such a long time.

    Anyway, why don't you change those columns intended to accept international characters to [long ]n[var]char? This should be possible while the database is running and provides the extra benefit that you don't have to recalculate column max lengths. Remember that [var]char max length is specified in bytes, so a char (20) in a unicode encoded database will not be able to store arbitrary 20 characters in such a column. The max length of n[var]char columns is specified in characters.

    HTH

    Volker

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member Volker Barth

      Hi Volker

      The XML we are generating is pretty complex and may not be possible to handle that way.

      I am really thankful for your valuable suggestions but we created a giant and have handle it either with Non-English or without it.

      If something SAP can add to handle as part of enhancements for XML, this will make life easy.

      The Enhancement like : with NCHAR conversation of a column, I can now store and read the output in ISQL but not in XML.

      If XML can able to do the same thing, its so simple to manage.

      Thanks for all your help.

      Regards,

      Ramendra

      Message was edited by: Ramendra Singh

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.