Sybase ASE traditionally has supported only one sort order per server. If you wanted a case insensitive database where “MyDB” and “MYDB” and “mydb” all pointed to the same object, you had to set the entire ASE server to case insensitive sort order (no_case) for your default character set. This is not a good situation for some companies as it requires them to purchase a second ASE server license to host two databases with different sort orders.
What I’m proposing to Sybase is how to handle multiple default character sets and sort orders per server. How it would work is this:
Assume there are two user databases each with its own default character set and order order.
- DB_A is character set iso_1 and binary sort order
- DB_B is character set utf8 and case insensitive
There is really only a single scenario that has to be handled; a cross database query from one user database to another
This scenario can easily be handled by accessing the database as if it was a proxy database. This handles any sort order / character set issues that arise when querying the tables.
- However, the current implementation of CIS (proxy tables, proxy databases, proxy stored procedures) is a top heavy and expensive operation. Sybase ASE engineering would have to implement a lite version of CIS that would be used only for internal server operations. Think of it as a Sybase ASE version of Solaris’s Lite-Weight Process, a stripped process used for very specific things.
- This would tend to imply that the model database would use a predefined character set and sort order. Not necessarily but this would be a good opportunity for Sybase to move towards a master database that you can load on to any ASE server, making migrations a simple matter. I would hope Sybase would choose utf8 and binary sort order as this should cover just about everyone. Another option is the utf-16 character set but I’m not sure if that is overkill or not. Either one would be fine.
The beauty of this design is that the granularity can be at the database or the table level with not much more effort from ASE engineering.
You might be asking why you would really need multiple default character sets and/or sort orders per server. There are several but I’ll just cover three of them:
- Limited budget. Due to business or application requirements, you need the backend database to include Hebrew characters and English. You also have payroll that requires ISO8859-1 (iso_1) character set with binary sort ordering. The current budget only allows for the purchase of a single ASE license.
- UAT (User Acceptance Testing) environments. Management often has trouble justifying why they need several Sybase ASE servers to perform testing of different character sets/sort orders. This can create a tremendous amount of work for the DBAs as they are constantly changing the default character sets and sort orders. If ASE had the capability of multiple default character sets and sort orders….
- Development environments. Pretty much the same reasoning for UAT environments. Explaining to upper management why we need more development ASE servers than production ASE servers is often difficult justify.
Part of me is expecting Mr. Linderman to show up, or at least Hero and Ando 😉