Posts
SYBASE ASE 15.0.2 – Case Sensitive to Non-Case Sensitive.
Sybase ASE database by default is case sensitive. But Oracle is not. We are testing one application with SYBASE ASE 15.0.2 developer edition where we will be having a data from Oracle RDBMS which needs to be replicated to ASE. That raw data is non-case and it's in various forms. To avoid more re-work as this is an immediate testing so we planned to test this.
I tried few sites which I could not able to get the resource and after browsing Google my search ended with infocenter.sysbase.com.
1; select @@version
2; go
----------------------------------------------------------------
Adaptive Server Enterprise/15.0.2/EBF 14336/P/x86_64/Enterprise
Linux/ase1502/2486/64-bit/FBO/Thu May 24 08:06:35 2007
I Here is the procedure I used to find the default sort order of the database which is 25, Binary ordering for UTF-16.
1; sp_helpsort
2; go
………………..
Sort Order Description
------------------------------------------------------------------
Character Set = 190, utf8
Unicode 3.1 UTF-8 Character Set
Class 2 Character Set
Sort Order = 25, binary
Binary ordering for UTF-16
(return status = 0)
The are two ways to approach this one is via server console on a Unix system with sqlloc or with sp_configure. I preferred to go with sp_configure as I will always have control of what I am doing. This is a test purpose and I did not bother to dump the database. So FULL DATABASE is must, whenever we do configuration changes, always makes us on safer sideJ.
2; select s1.value2
3; from syscurconfigs s1, sysconfigures s2
4; where s1.config = s2.config
5; and s2.name = 'configuration file'
6; go
value2
----------------------------------------------------
/u02/sybase/RAP/RAPCache/ASE-15_0/RAPCache.cfg
Take a copy of the Configuration file before you proceed further. It helps us to know what we have before system changes.
4; sp_configure "default sortorder id", 52
5; go
You have just reconfigured ASE's default sort order. System table indexes will be rebuilt when you reboot ASE.
Parameter Name Default Memory Used Config Value
Run Value Unit Type
------------------------------ ----------- ----------- ------------
------------ -------------------- ----------
default sortorder id 50 0 52
25 id static
(1 row affected)
Configuration option changed. Since the option is static, Adaptive Server must be rebooted in order for the change to take effect.
Changing the value of 'default sortorder id' does not increase the amount of memory Adaptive Server uses.
(return status = 0)
Shutdown the ASE Server…
1; shutdown
2; go
Server SHUTDOWN by request.
ASE is terminating this process.
Start the ASE Server
Once you bring the ASE server it will check for consistency and it will rebuild all the indexes.
00:00000:00001:2009/02/16 15:41:29.67 server Default Sort Order successfully changed.
00:00000:00001:2009/02/16 15:41:29.67 server *** Be sure to dump all databases after
00:00000:00001:2009/02/16 15:41:29.67 server ASE has booted with the new
00:00000:00001:2009/02/16 15:41:29.67 server *** sort order, since your old dumps will
00:00000:00001:2009/02/16 15:41:29.67 server *** be unusable with the new sort order.
00:00000:00001:2009/02/16 15:41:29.67 server ASE shutdown after verifying System Indexes.
00:00000:00001:2009/02/16 15:41:29.67 kernel ueshutdown: exiting
00:00000:00001:2009/02/16 15:41:29.67 kernel SySAM: Checked in license for 1 ASE_CORE (2009.09220/permanent/1623 DCB3 B018 5E2B).
[1]+ Done startserver -f RUN_RAPCache
Once you see the above message start the server and check the service.
3; sp_helpsort
4; go
Sort Order Description
------------------------------------------------------------------
Character Set = 190, utf8
Unicode 3.1 UTF-8 Character Set
Class 2 Character Set
Sort Order = 52, nocase
General-purpose case-insensitive dictionary ordering
(return status = 0)
RAVI PRAKASH ©
