Sybase字符集的修改
Sybase服务器端字符集的修改
可以在图形化界面使用sqlloc命令修改sybase服务器端字符集,本文主要讲述命令行下服务器字符集修改操作说明:
2.1?先查询sybase系统中已加载的字符集情况
sybase@wap2atca5:~/sql> isql -Usa -P -Seds
1> select name,id from syscharsets order by name???? ------先查出字符集的序号id
2> go
?name?????????????????????????? id?
?------------------------------ ---
... ...
?iso_1??????????????????????????? 1
... ...
(56 rows affected)
1>
2.2?如果sybase没有加载需要的字符集，则加载sybase server中没有的字符集
下面以加载cp936字符集为例
sybase@wap2atca5:~> cd charsets/cp936/
sybase@wap2atca5:~/charsets/cp936> charset -Usa -P -Seds binary.srt cp936
Loading file 'binary.srt'.
Found a [sortorder] section.
This is Class-1 sort order.
Finished loading the Character Set Definition.
Finished loading file 'binary.srt'.
1 sort order loaded successfully
sybase@wap2atca5:~/charsets/cp936>
2.3?修改默认字符集
sybase@wap2atca5:~/charsets/cp936> isql -Usa -P -Seds
1> select name,id from syscharsets where name="cp936"
2> go
?name?????????????????????????? id?
?------------------------------ ---
?cp936????????????????????????? 171
(1 row affected)
1> sp_configure "default character set id",171
2> go
在更改缺省排序顺序的过程中，也会重新配置 ASE 的缺省字符集。
?Parameter Name???????????????? Default???? Memory Used Config Value
???????? Run Value??? Unit???????????????? Type??????
?------------------------------ ----------- ----------- ------------
???????? ------------ -------------------- ----------
?default character set id???????????????? 1?????????? 0????????? 171
????????????????? 170 id?????????????????? static????
(1 row affected)
配置选项改变.因为选项是静态的,Adaptive Server必须重启,以使改动生效.
更改 'default character set id' 的值不会增加 Adaptive Server 使用的内存量。
(return status = 0)
然后将sybase重启二次。第一次启动后，server会自动宕掉，需要第二次重启后才能使用。
1> shutdown SYB_BACKUP
2> go
Backup Server: 3.48.1.1: 备份服务器将马上关闭。中断会话。
1> shutdown with nowait
2> go
服务器SHUTDOWN被请求。
ASE 正在终止此进程。
CT-LIBRARY error:
??????? ct_results(): 网络包层: 内部net library错误: 由于断开使得 Net-Library 的操作中断。
sybase@wap2atca5:~/charsets/cp936>
sybase@wap2atca5:~/ASE-15_0/install> startserver -f RUN_eds
... ...
00:00000:00001:2009/12/03 15:43:54.70 server? Default Sort Order successfully changed.
00:00000:00001:2009/12/03 15:43:54.70 server? ASE shutdown after verifying System Indexes.
00:00000:00001:2009/12/03 15:43:54.70 kernel? ueshutdown: exiting
00:00000:00001:2009/12/03 15:43:54.70 kernel? SySAM: Checked in license for 1 ASE_CORE (2011.04260/permanent/131C 6FD4 1D4C 0E96).
sybase@wap2atca5:~/ASE-15_0/install> startserver -f RUN_eds
2.4?安装语言集
运行命令langinstall -Usa -P -Seds chinese cp936，该步骤时间稍长，须耐心等待。
Solaris：
sybase@Sun280R $ langinstall -Usa -P -Seds chinese cp936
Langinstall will now attempt the installation, ASE will convert messages from 'utf-8' to server's default character set 'cp936'.
Loading datetime information from file /export/home/sybase/ASE-15_0/locales/chinese/common.loc.
Loading system messages from file /export/home/sybase/ASE-15_0/locales/chinese/server.loc.
9075 Messages processed.
Loading complete.
Summary: 0 deleted, 8640 inserted, 0 updated, 435 duplicates,?? 0 unsuccessful.
sybase@Sun280R $
Linux64：
sybase@wap2atca4:~> langinstall -Swap -Usa -P chinese
Langinstall will now attempt the installation, ASE will convert messages from 'utf-8' to server's default character set 'eucgb'.
Loading datetime information from file /home/sybase/ASE-15_0/locales/chinese/common.loc.
Loading system messages from file /home/sybase/ASE-15_0/locales/chinese/server.loc.
9056 Messages processed.
Loading complete.
Summary: 0 deleted, 8621 inserted, 0 updated, 435 duplicates,?? 0 unsuccessful.
sybase@wap2atca4:~>
Linux32
sybase@wap2atca5:~> langinstall -Usa -P -Seds chinese eucgb
SQLServer message 4017, state 1, severity 16:
??????? Neither language name in login record 'chinese' nor language name in syslogins '<NULL>' is an official language name on this ASE. Using server-wide default 'us_english' instead.
Langinstall will now attempt the installation, ASE will convert messages from 'utf-8' to server's default character set '936'.
Loading datetime information from file /home/sybase/ASE-15_0/locales/chinese/common.loc.
Loading system messages from file /home/sybase/ASE-15_0/locales/chinese/server.loc.
9075 Messages processed.
Loading complete.
Summary: 0 deleted, 8640 inserted, 0 updated, 435 duplicates,?? 0 unsuccessful.
sybase@wap2atca5:~>
2.5?查询字符集命令
sybase@wap2atca5:~> isql -Usa -P -Seds
1> sp_helpsort
2> go
... ...
排序顺序描述
???????????????????????????????????????????????????????????????????
?------------------------------
?字符集 = 171, cp936???????????????????????????????????????????????
???? CP936 (Simplified Chinese).???????????????????????????????????
???? Class 2字符集?????????????????????????????????????????????????
?排序顺序 = 50, bin_cp936??????????????????????????????????????????
???? Binary sort order for simplified Chinese using cp936.?????????
(return status = 0)
1>
或
1>?sp_configure "default character set id"
2> go
?Parameter Name???????????????? Default???? Memory Used Config Value
???????? Run Value??? Unit???????????????? Type??????
?------------------------------ ----------- ----------- ------------
???????? ------------ -------------------- ----------
?default character set id???????????????? 1?????????? 0????????? 171
????????????????? 171 id?????????????????? static????
(1 row affected)
(return status = 0)
1>
Sybase客户端字符集的修改
在Windows下使用Sybase客户端连接到Sybase服务器的时候需要注意客户端字符集。
同样，在Solaris/Linux上运行isql连接到Sybase服务器时，也需要注意客户端字符集。如果出现类似如下的提示，那么则需要设置Sybase客户端的字符集
sybase@wap3atca13:~/locales> isql -Usa -P
The context allocation routine failed when it tried to load localization files!!
One or more following problems may caused the failure
Your sybase home directory is /home/sybase. Check the environment variable SYBASE if it is not the one you want!
Using locale name "zh" defined in environment variable LANG
Locale name "zh" doesn't exist in your /home/sybase/locales/locales.dat file
An error occurred when attempting to allocate localization-related structures.
sybase@wap3atca13:~/locales>
sybase@linux:~/ASE-12_5/install> isql -Usa -P -Seds
Msg 2401, Level 11, State 2:
Server 'eds':
Character set conversion is not available between client character set 'iso_1'
and server character set 'eucgb'.
No conversions will be done.
1>
这时候需要进入$SYBASE/locales目录下，编辑locales.dat，找到对应的平台，例如[linux]或者[sun_svr4]，然后找到缺省以及当前的语言集，例如zh、zh_CN.UTF-8、zh_CN.gbk等，将语言集的后面的字符集设置为cp936。
系统当前的语言集可以用echo $LANG检查，缺省语言集有C、POSIX、default等。
如果LANG设置为zh_CN.UTF-8、zh_CN.gbk或者zh，那么需要修改locale = zh_CN.UTF-8开头的内容，将后面的字符改为chinese，cp936。
如果语言集设置为zh，locales.dat里面没有，那么还需要在相应的平台下添加一行
? locale = zh, chinese, cp936
这样的纪录。
如下：
$cd? $SYBASE/locales
$ vi locales.dat
找到[linux]配置组或者[sun_svr4]配置组，
? [linux]
??????? locale = FRENCH, french, iso_1
??????? locale = fr, french, iso_1
??????? locale = fr_BE, french, iso_1
??????? locale = fr_BE.437, french, cp437
??? locale = zh, chinese, cp936
??? locale = zh_CN.UTF-8, chinese, cp936
??? locale = zh_CN.gbk, chinese, cp936
??????? locale = zh_CN.gb18030, chinese, gb18030
??????? locale = zh_CN.GB18030, chinese, gb18030
??????? locale = zh_TW, tchinese, big5
??????? …..
此外，找到[linux]配置组或者[sun_svr4]配置组的default一行记录，如：
??????? locale = default, us_english, iso_1
将default的语言集改为：
locale = default, chinese, cp936
修改保存即可。default这个参数对于缺省LANG以及unset LANG这两个环境生效。
如果LANG设置为C，那么找[linux配置组]的C一行记录，如：
??????? locale = C, us_english, iso_1
将default的语言集改为：
??? locale = C, chinese, cp936
修改保存即可．
然后启动isql观察效果，在iSQL环境中执行：
1> select @@client_csname
2> go
字符集服务器和客户端设置完毕后，确保在本机使用isql登录Sybase不再报错，将.bash_profile或者.profile中的unset LANG去掉。
