Home > ASE, Database, SAP, Sybase ASE/REP Interview Questions > Changing ASE Sort Order using sqllocres

Changing ASE Sort Order using sqllocres


Task : Sort Order change of PROD_ASE_DS2 
Current Sort Order :50 Binary Sort Order (Server Default Sort Order)
New Sort Order : 51 Dictionary Based Sort Order
 
Before running any command , please import Sybase Enviorment in your current Shell, you can get the same by executing SYBASE.sh which resides in $SYBASE ( Sybase Installation Directory).
 
1. Started the Dataserver PROD_ASE_DS2 :
[sybase@localhost install]$ ./startserver -f RUN_PROD_ASE_DS2 ……………….. …………………… 00:00:00000:00001:2012/06/06 00:26:06.61 server ASE’s default unicode sort order is ‘binary’. 00:00:00000:00001:2012/06/06 00:26:06.61 server ASE’s default sort order is: 00:00:00000:00001:2012/06/06 00:26:06.61 server ‘bin_iso_1’ (ID = 50) 00:00:00000:00001:2012/06/06 00:26:06.61 server on top of default character set: 00:00:00000:00001:2012/06/06 00:26:06.61 server ‘iso_1’ (ID = 1). 00:00:00000:00001:2012/06/06 00:26:06.61 server Master device size: 30 megabytes, or 15360 virtual pages. (A virtual page is 2048 bytes.)

2. Checking the Current Sort Order of Server

[sybase@localhost install]$ isql -Usa -SPROD_ASE_DS2 -w999
Password:
1> select @@servername,getdate()
2>
3> go
———————————————————— ————————–
PROD_ASE_DS2 Jun 6 2012 12:26AM
(1 row affected)
1> sp_helpsort
2> go
Sort Order Description
————————————————————————————————————————————
Character Set = 1, iso_1
ISO 8859-1 (Latin-1) – Western European 8-bit character set.
Sort Order = 50, bin_iso_1
Binary ordering, for the ISO 8859/1 or Latin-1 character set (
iso_1).
Characters, in Order
————————————————————————————————————————————
! ” # $ % & ‘ ( ) * + , – . / 0 1 2 3 4 5 6 7 8 9 : ; < = > ?
@ A B C D E F G H I J K L M N O P Q R S T U V W X Y Z [ ] ^ _
` a b c d e f g h i j k l m n o p q r s t u v w x y z { | } ~ Â
¡ ¢ £ ¤ Â¥ ¦ § ¨ © ª « ¬ ­ ® ¯ ° ± ² ³ ´ µ ¶ · ¸ ¹ º » ¼ ½ ¾ ¿ Ã
à à à Ã
à à à à à à à à à à à à à à à Ã
à à à à à à à à á â ã ä å æ ç è é ê ë ì í î ï ð ñ ò ó ô õ ö ÷ ø ù ú û ü ý þ ÿ
(return status = 0)
1> exit

3. Moving in Sample Resource file Directory

[sybase@localhost init]$ pwd
/opt/sybase/15/ASE-15_0/init/sample_resource_file
[sybase@localhost sample_resource_files]$ ls -ltr
total 128
-rw-r–r– 1 sybase sybase 868 Jan 6 1998 sqlloc.rs
-rw-r–r– 1 sybase sybase 179 Jan 14 2000 sqlupgrade.backup_server.rs
-rw-r–r– 1 sybase sybase 294 Jan 14 2000 sqlupgrade.monitor_server.rs
-rw-r–r– 1 sybase sybase 401 Jan 26 2000 srvbuild.xp_server.rs
-rw-r–r– 1 sybase sybase 671 Jul 14 2004 srvbuild.text_server.rs
-rw-r–r– 1 sybase sybase 959 Apr 13 2005 srvbuild.job_scheduler.rs
-rw-r–r– 1 sybase sybase 621 Mar 5 2009 srvbuild.backup_server.rs
-rw-r–r– 1 sybase sybase 283 Jun 28 2009 sqlupgrade.adaptive_server.rs
-rw-r–r– 1 sybase sybase 1822 Sep 1 2009 srvbuild.adaptive_server.rs
-rw-r–r– 1 sybase sybase 562 Feb 21 2010 srvbuild.monitor_server.rs
-rw-r–r– 1 sybase sybase 3658 Aug 26 2010 auditinit.res
-rw-rw-r– 1 sybase sybase 825 Mar 31 12:12 PRDO_ASE.rs
-rw-r–r– 1 sybase sybase 547 Mar 31 12:23 srvbuild.backup_server.rs_new
-rw-r–r– 1 sybase sybase 864 Apr 17 21:28 srvbuild.adaptive_server.rs_PROD_ASE1
-rw-r–r– 1 sybase sybase 864 Apr 17 21:32 srvbuild.adaptive_server.rs_PROD_ASE2
-rw-r–r– 1 sybase sybase 848 May 31 21:01 sqlloc.rs_new
sqlloc.rs is the sample resource file for changing the sort order
[sybase@localhost sample_resource_files]$ cat sqlloc.rs
sybinit.release_directory: USE_DEFAULT
sqlsrv.server_name: PUT_YOUR_SERVER_NAME_HERE
sqlsrv.sa_login: sa
sqlsrv.sa_password:
sqlsrv.default_language: USE_DEFAULT
sqlsrv.language_install_list: USE_DEFAULT
sqlsrv.language_remove_list: USE_DEFAULT
sqlsrv.default_characterset: USE_DEFAULT
sqlsrv.characterset_install_list: USE_DEFAULT
sqlsrv.characterset_remove_list: USE_DEFAULT
sqlsrv.sort_order: USE_DEFAULT

4. Copying Original Resource file with new name

[sybase@localhost sample_resource_files]$ cp sqlloc.rs sqlloc.rs_ase2
[sybase@localhost sample_resource_files]$ vi sqlloc.rs_ase2

5. Updaing the new Resouce file with new Sort Order name, Server Name, Password for sa user (if any)

[sybase@localhost sample_resource_files]$ cat sqlloc.rs_ase2
sybinit.release_directory: USE_DEFAULT
sqlsrv.server_name: PROD_ASE_DS2
sqlsrv.sa_login: sa
sqlsrv.sa_password:
sqlsrv.default_language: USE_DEFAULT
sqlsrv.language_install_list: USE_DEFAULT
sqlsrv.language_remove_list: USE_DEFAULT
sqlsrv.default_characterset: USE_DEFAULT
sqlsrv.characterset_install_list: USE_DEFAULT
sqlsrv.characterset_remove_list: USE_DEFAULT
sqlsrv.sort_order: dictionary

6. We will be change sort order with sqllocres exe ,which resides in bin dir(same as dataserver binary).

[sybase@localhost sample_resource_files]$ sqllocres -v
sqllocres/15.5 ESD #2/P/Linux Intel/Linux 2.6.9-55.ELsmp i686/EBF 18159 SMP/OPT/Wed Aug 25 12:18:46 PDT 2010
Confidential property of Sybase, Inc.
Copyright 1997 – 2010
Sybase, Inc. All rights reserved.
Unpublished rights reserved under U.S. copyright laws.
This software contains confidential and trade secret information of Sybase,
Inc. Use, duplication or disclosure of the software and documentation by
the U.S. Government is subject to restrictions set forth in a license
agreement between the Government and Sybase, Inc. or other written
agreement specifying the rights of the Government to use the software
and any applicable FAR provisions, for example, FAR 52.227-19.
Sybase, Inc. One Sybase Drive, Dublin, CA 94568, USA.

7. Final step executing the sqllocres with our created resource file.

[sybase@localhost sample_resource_files]$ sqllocres -r sqlloc.rs_ase2
Installing sort order ‘General purpose dictionary ordering’ in the Adaptive
Server…
00:00:00000:00012:2012/06/06 00:30:11.00 kernel Setting console to nonblocking mode.
00:00:00000:00012:2012/06/06 00:30:11.00 server Configuration file ‘/opt/sybase/15/ASE-15_0/PROD_ASE_DS2.cfg’ has been written and the previous version has been renamed to ‘/opt/sybase/15/ASE-15_0/PROD_ASE_DS2.019’.
00:00:00000:00012:2012/06/06 00:30:11.00 server The configuration option ‘allow updates to system tables’ has been changed by ‘sa’ from ‘0’ to ‘1’.
00:00:00000:00012:2012/06/06 00:30:11.08 server Configuration file ‘/opt/sybase/15/ASE-15_0/PROD_ASE_DS2.cfg’ has been written and the previous version has been renamed to ‘/opt/sybase/15/ASE-15_0/PROD_ASE_DS2.020’.
00:00:00000:00012:2012/06/06 00:30:11.08 server The configuration option ‘allow updates to system tables’ has been changed by ‘sa’ from ‘1’ to ‘0’.
Sort Order ‘General purpose dictionary ordering’ was successfully installed in
the Adaptive Server.
Making ‘General purpose dictionary ordering’ the Adaptive Server’s default sort
order…
00:00:00000:00012:2012/06/06 00:30:11.10 server Configuration file ‘/opt/sybase/15/ASE-15_0/PROD_ASE_DS2.cfg’ has been written and the previous version has been renamed to ‘/opt/sybase/15/ASE-15_0/PROD_ASE_DS2.021’.
00:00:00000:00012:2012/06/06 00:30:11.10 server The configuration option ‘allow updates to system tables’ has been changed by ‘sa’ from ‘0’ to ‘1’.
00:00:00000:00012:2012/06/06 00:30:11.58 server Configuration file ‘/opt/sybase/15/ASE-15_0/PROD_ASE_DS2.cfg’ has been written and the previous version has been renamed to ‘/opt/sybase/15/ASE-15_0/PROD_ASE_DS2.022’.
00:00:00000:00012:2012/06/06 00:30:11.58 server The configuration option ‘default sortorder id’ has been changed by ‘sa’ from ’50’ to ’51’.
00:00:00000:00012:2012/06/06 00:30:11.58 server WARNING: ***************************
00:00:00000:00012:2012/06/06 00:30:11.58 server Default sort order being reconfigured:
00:00:00000:00012:2012/06/06 00:30:11.58 server old sortord ID = 50 new sortord ID = 51
00:00:00000:00012:2012/06/06 00:30:11.60 server Configuration file ‘/opt/sybase/15/ASE-15_0/PROD_ASE_DS2.cfg’ has been written and the previous version has been renamed to ‘/opt/sybase/15/ASE-15_0/PROD_ASE_DS2.023’.
00:00:00000:00012:2012/06/06 00:30:11.60 server The configuration option ‘allow updates to system tables’ has been changed by ‘sa’ from ‘1’ to ‘0’.
00:00:00000:00014:2012/06/06 00:30:13.67 server Shutdown started by user ‘sa’. SQL text: shutdown
00:00:00000:00014:2012/06/06 00:30:13.80 server ASE shutdown by request.
00:00:00000:00014:2012/06/06 00:30:13.80 kernel ueshutdown: exiting
00:00:00000:00014:2012/06/06 00:30:13.82 kernel SySAM: Checked in license for 1 ASE_CORE (2013.1231/permanent/0A1F EC50 7138 C903).
The Adaptive Server’s default sort order is now ‘General purpose dictionary
ordering’.
Done

8. Checking the Sort order of server now.

[sybase@localhost sample_resource_files]$ isql -Usa -SPROD_ASE_DS2 -w999
Password:
1> select @@servername,getdate()
2>
3> go
———————————————————— ————————–
PROD_ASE_DS2 Jun 6 2012 12:31AM
(1 row affected)
1> sp_helpsort
2> go
………….
…….
….
Sort Order Description
————————————————————————————————————————————
Character Set = 1, iso_1
ISO 8859-1 (Latin-1) – Western European 8-bit character set.
Sort Order = 51, dictionary_iso_1
General purpose dictionary sort order for use with several Wes
tern-European languages including English, French, and German.
Uses the ISO 8859-1 character set and is case-sensitive.
Characters, in Order
————————————————————————————————————————————
! ” # $ % & ‘ ( ) * + , – . / : ; < = > ? @ [ ] ^ _ ` { | }
~   ¡ ¢ £ ¤ ¥ ¦ § ¨ © ª « ¬ ­ ® ¯ ° ± ² ³ ´ µ ¶ · ¸ ¹ º » ¼ ½ ¾
¿ à ÷ 0 1 2 3 4 5 6 7 8 9 A a à à à á à â à ã Ã
ä Ã
å à æ B b C í à î à ï J
j K k L l M m N n à ñ O o à ò à ó à ô à õ à ö à ø P p Q q R r S
s à T t U u à ù à ú û à ü V v W w X x Y y Ã
(return status = 0)
1> exit
Note :
i)Always try to change the sort order of newly built server , changing sort order of existing data is not recommended.
ii) All Sort Orders are not applicable to all charset. So before changing the Sort Order , please identify which one is valid for your current chracter set.
iii) For More Infomration : Please visit : http://sybooks.sybase.com
Check the Sybase Wiki @ sybasewiki.com
  1. No comments yet.
  1. No trackbacks yet.