存档
Sybase ASE15.0及以后的版本中的bcp命令实用工具中有个-Y参数，这个参数的作用是：禁用在服务器中的字符集转化，在使用bcp in时由bcp在客户端上执行字符集转化过程。
只有ASE15.0或更高版本中支持客户端unicode转化。
另外：所有字符集转化都是在bcp out期间在服务器中执行的。
提供一个能够自动生成Sybase数据库内所有表的表结构、索引语法的存储过程。
存储过程名字：sp_gettabledll 下载
过程语法如下:
----------------------------------------------------------------------------------------------------------------------
if exists(select 1 from sysobjects where name = 'sp_gettabledll' and type = 'P')
drop procedure sp_gettabledll
go
create procedure sp_gettabledll
@tblname varchar(30) = null,
@prechar varchar(4) = null, --$:no print
@table_dll varchar(16384) = null out,
@dbname varchar(32) = null,
@droptg char(1) = '1',
@prxytx varchar(255) = null,
@replace varchar(20) = null,
@tabtype varchar(1) = 'A', --A:所有表;P:代理表;U:用户表
@indextg varchar(3) = 'TPI', --T:纯表;P:主键;I:纯索引;J:除主键外的纯索引（和TP使用与I相同,和I同时使用I失效）
@table_seg varchar(32) = null,
@index_seg varchar(32) = null
as
begin
set nocount on
在sybase中找回丢失的sa密码
前提条件是sa未被锁定！
1、在sybase目录的install子目录的启动server文件
RUN_server（这里的server表示你的服务器名称，如这里我的是RUN_FENGLB）名，编辑该文件，
在其中的“<Sybase目录>\data\master.dat -sFENGLB”后加入参数 -psa，
保存该文件。
2、如果服务器已经启动，先停止之。
3、启动一个DOS窗口，转到上面的install目录，将DOS窗口的属性的屏幕缓冲区大小设置为9999，将执行第1步批处理文件以启动 server，在启动之后，将DOS窗口中的内容全部复制到记本事中，查找字符串“New SSO password for sa:”，后面的就是sa的新口令，记录之。
4、切换到SQL Advangtage以sa帐号登录，口令为刚刚得到的密码。
5、进入server以后，用命令sp_password修改sa口令：sp_password '原密码','新密码','用户名'
新密码的位数一定要大于6位，否则不能够更改成功。
6、回到第1步，去掉增加的选项-psa，保存退出。
转自：http://blog.csdn.net/fenglibing/archive/2008/02/15/2097060.aspx
用sybase自带的客户端工具sybase central连接ase的时候，如果连接字符集设置不合适，查询结果中汉字会出现乱码。
如果ASE数据库的字符集为：iso_1或者cp850，执行：
sp_configure "disable character set conversi",1
go
修改此配置参数，需要重启ASE服务器。
然后，在sybase central连接配置窗口中，将Character set调整为：cp936。必须设置Character set这个参数，否则sybase central中显示的汉字肯定是乱码，这和sybase central使用的jdbc驱动有关。
其实，将Character set也可以调整为：eucgb,gb18030，这些都是支持中文的字符集。
另外，如果ASE服务器的字符集为：cp936或者utf8，可以不用修改参数disable character set conversi为：1。仅仅通过调整Character set为：cp936 也能解决汉字显示乱码的问题。
安装中文语言支持、配置字符集
在windows平台上，Sybase ASE15.0.3安装完成后默认语言是：英语（us_english），默认字符集为：cp850。cp850是一个西欧字符集，虽然也能使用cp850字符集保存汉字，但是不推荐在生产环境中使用该字符集。
如果生产系统中Sybase ASE数据库仅考虑支持简体中文的话，则可以使用cp936或者utf8这两种字符集。如果需要国际化支持，那么需选择utf8字符集。
在生产环境中，需要将默认字符集由cp850调整为utf8（或者调整成支持简体中文的cp936也可以，根据项目情况而定！），将默认语言调整为中文。
备注：如果想设定ASE的默认语言为中文，必须先将默认字符集设定为utf8，才能设置默认语言为中文。否则会报错。
1查看当前数据库的默认字符集：
执行：
sp_configure "default character set id"
go
查看到Run Value为：2，执行：
select id,name from master..syscharsets where id=2
go
查看到当前字符集为：cp850
以下的脚本能够自动创建数据库、登录、用户并能给用户分配命令权限。只需要指定用户数据库的名称，数据设备、日志设备的大小，登录名称以及登录密码即可。
局限性：
没有判断即将要创建的设备是否存在？
将数据库设备创建在和master设备同一目录下，不能提高物理IO的性能。
脚本文件下载：sybase数据库自动创建脚本.sql
-----------------------------------------------------------------------------------------------------------------------------
go
declare @MasterDevicePath varchar(255),@LogicalDataDevName varchar(30),@LogicalLogDevName varchar(30)
declare @DATDevicePath varchar(255),@LOGDevicePath varchar(255),@DataDevSize int,@LogDevSize int,@DataDevPageCount int,@LogDevPageCount int
declare @SQLTEXT varchar(255)
declare @loginname varchar(30),@loginpasswd varchar(30)
Adaptive Server version 15.0.2 uses row-level locking on system tables to enhance performance.
Versions of Adaptive Server earlier than 15.0.2 used exclusive table locks on system tables while executing data definition language (DDL) and utility commands. The set of system tables Adaptive Server locked depended on the type of DDL operation you executed. If another DDL running concurrently tried to take a conflicting exclusive table lock on the same system table, this DDL had to wait to acquire the lock on any system catalogs. These DDL operations were executed serially.
This methodology impeded performance in temporary databases, where their DDL activity is very high also, and consequently their catalog contention is very high. This limited the Adaptive Server throughput for applications using temporary tables.
Adaptive Server version 15.0.2 uses row-level locking to resolve these issues:
*
System-table contention, caused a bottleneck for many DDLs and utilities.
*
tempdb contention. Because the system tables are locked at the row level, Adaptive Server 15.0.2 eliminates tempdb contention.
*
Shared or exclusive table-level locks while executing DDLs and utilities. Earlier versions converted most system tables to data-only locking (DOL), but still created shared or exclusive table-level locks while executing DDLs and utilities. Using row-level locks for system tables eliminates this contention.
Adaptive Server sets intent locks on catalogs only, which removes potential contention (An intent lock indicates that page-level or row-level locks are currently held on a table.).
*
DDLs and utilities blocking each other. Adaptive Server 15.0.2 allows DDLs and utilities to run in parallel.
Earlier versions of Adaptive Server used table locks to achieve system catalog synchronization. Adaptive Server 15.0.2 uses intent locks for table-level synchronization and row locks for row-level synchronization. Earlier releases of Adaptive Server locked the entire system catalog while performing operations on the object, so a single lock request was made. However, Adaptive Server version 15.0.2 requests locks for all applicable rows while performing operations on the object if there are multiple rows corresponding to an object in a system catalog.
This change means that Adaptive Server 15.0.2 requests more locks to perform the same operation than earlier releases, and increases the number of lock resources the system needs. Consequently, you may need to change the number of locks configuration option after you upgrade Adaptive Server.
sybase官方文档上关于设备的dsync,directio这两个属性也是语焉不详，可能不同版本不同平台的ASE这两个属性有不同的默认值。
下面是一位Sybase大牛的论述，很清晰明朗。
The use of dsync/directio (typically) means the dataserver has to wait for a write to complete on the physical disk
before the dataserver considers the write to be 'successful' (aka guaranteed write). While this *wait* will extend the
time it takes to complete the associated database action, the guaranteed write to disk is required to insure
recoverability (in the case of a dataserver/machine/disk subsystem failure).
If dsync/directio are disabled then a dataserver write may finish more quickly due to the disk subsystem *caching* the
write IO. This means the associated dataserver action completes more quickly but there's no guarantee the IO made it to
the physical disk. If the dataserver/machine/disk subsystem fails between the successful disk cache write and the
physical disk write, you could end up losing data (ie, the dataserver thinks the data is on disk while the disk has no
record of the data existing).
For important databases (eg, master, RSSDs, user databases), especially in production environments, the recoverability
of the data usually takes precedence over speed. In these scenarios the dataserver must wait for physical disk writes
to complete 'successfully', with the caveat that the associated database action takes longer to complete.
For trivial/development databases where recoverability is not a concern, or for databases that are rebuilt from scratch
at dataserver startup (eg, temporary databases), guaranteed disk writes are not as important. In these scenarios a
successful write to disk cache is sufficient, with the added benefit that the associated database action completes more
quickly.
So, generally speaking:
dsync/directio enabled : guaranteed disk writes, guaranteed recoverability, associated database actions take more time
to complete
dsync/directio disabled : no guarantee of writes to physical disks, no guarantee of recoverability, associated database
actions take less time to complete
------------------
Now-a-days there are lots of ways to configure disk subsystems ... raw disks, cached disks, cached file systems,
journaled file systems, multiple layers of logical disk management, sans (w/ and w/out cache), etc, etc, etc ...
Some of these configurations may support dsync/directio operations by the dataserver while actually performing the write
to cache, ie, the physical disk write takes place at a later time. For some systems this may be ok if the disk
subsystem vendor can guarantee that those cache writes will always make their way to disk.
The issue here is that regardless of which attribute settings (dsync/directio) are used for dataserver devices, it's the
DBA's responsibility to insure the disk subsystem can really guarantee disk writes for those database actions that
require guaranteed recoverability.
总结一下：文件系统将directio属性关闭。文件系统上临时数据库或者不太“重要”的数据库的设备可以将dsync调整成false，其余数据库为了保证恢复将dsync设置成true。