Changing a sort order of an existing ASE Server is rarely, if ever, done. One tends to install the sort order with the ASE Server binaries and that sort order exists for the life of the ASE Server. The chief reason being is, any data that is stored on the ASE Server will have the characteristics of that sort order. A Servers's "sort order" governs how data is ordered in storage and how it is presented to you when you do an SQL query. A common sort order is "binary" which sorts according to letters, the upper or lower case of those letters also affecting the order. Needless to say its important to establish exactly what is needed up front before the installation. Sometimes life gets in the way and we have to change the soft order after the installation of the original ASE Server.
Here is an example I was involved in: my Customer had an ASE Server that was created as case-insensitive, meaning the data and compiled code did not discern an 'a' being different from an 'A.' The design was a knowledge-based look-up system where you could type in a word and the database would give you all the possible choices in syntax examples or documents. In this example you really are interested in the word itself and not the case of the letters in the search pattern. In the migration of this environment to a linux platform and in keeping with the Customer's present ASE standards, the standard was to implement this database on a 'case-sensitive' ASE linux platform.
While there have been examples of changing the sort-order of a server and then rebooting that server to allow the new sort order to take effect, my experience is that these procedures do not do far enough; we need to do a bit more work to ensure we have a correct working environment. Take for example a commonly presented procedure for sort order conversion: sp_configure 'default sortorder id', 52 . You can use sp_helpsort on the existing ASE Server to find out the supported sort orders. If you cannot find the sort order existing on the ASE Server then you would need to use the charset program, an example being:
$SYBASE/bin/charset -Psa_pw -Sservername nocase.srt cp936 for sort order and character set respectively.
In my case, after changing the sort order from case insensitive to case sensitive I found the following issues:
- Third-party tools did not reverse engineer objects such as tables correctly.
- Procedures and compiled code were now sensitive to character case and produced syntax failures.
In reality, after changing the case I had to manually go into all the compiled code: procedures, views, triggers and recreate these objects. I dealt with code having '@Sting' being referenced as '@string' in various instances. Really a programming error that gets missed in case insensitive Situations. I also needed to drop and recreate all indexes. The only thing I did not touch was the data itself. In many ways the dump and load I did onto the new platform was more effort than a simple bcp in and out.The other half of the equation is the changing of the Client-side code that works with this application. At this point we have 2 choices: either keep the data the same and enforce all searches to be upper case:
select <fields> from <table> where UPPER(field) = UPPER(user input)
or we can change all data to be upper-case and all user input is relegated to be upper case. Either method does involve a Client-side change. This design could have avoided any Client-side changes if all selects, inserts, updates, deletes used procedures and views as we could have just changed the search patterns in these objects, very simply done.
Perhaps the lessons to be learnt here are:
- be cognizant of the future of your database design with respect to case sensitivity
- possible use of procedures and views can help to minimize potential Client-side changes
- to cover all events of changing the sort-order be prepared to drop and recreate all indexes and compiled code in the database.
This Field Notes Series is dedicated to observations from the field taken from personal consulting experiences. The examples used have been created for this blog and do not reflect any existing SAP Customer configuration.
