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去掉。