Enter Your Email

Enter your email address:

Delivered by FeedBurner

Custom Search

Monday, February 16, 2009

SYBASE ASE 15.0.2 – Case Sensitive to Non-Case Sensitive

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)

Reference: http://manuals.sybase.com/onlinebooks/group-as/asg1250e/svrtsg/@ebt-link;pt=5693?target=%25N%14_5953_START_RESTART_N%25

RAVI PRAKASH ©

1 comment:

  1. Hi All,
    Presently am working as senior software developer (vc++) as I am not much intrusting to work on this technology, am very much interested in data base domain like DB/Oracle/Linux administration and related to this. So just I wanted to move in DB side.
    Can anyone help / suggest me to altering domain change?
    Post your comments: Vijay.rajput06@gmail.com , Vijay_i4@yahoo.com
    Thanks in Advance,
    Vijay R

    ReplyDelete

 
Copyright © 2005 - 2008 DBA-ONWeb Technical blog. All rights reserved