今天在AIX上安装SYBASE,在新建server的时候碰到以下错误。
exec(): 0509-036 Cannot load program /usr/sybase/ASE-12_5/bin/dataserver because of the following errors:
0509-130 Symbol resolution failed for /usr/ccs/lib/libc.a[aio_64.o] because:
0509-136 Symbol kaio_rdwr64 (number 0) is not exported from
dependent module /unix.
0509-136 Symbol listio64 (number 1) is not exported from
dependent module /unix.
0509-136 Symbol acancel64 (number 2) is not exported from
dependent module /unix.
0509-136 Symbol iosuspend64 (number 3) is not exported from
dependent module /unix.
0509-136 Symbol aio_nwait (number 4) is not exported from
dependent module /unix.
0509-136 Symbol aio_nwait64 (number 5) is not exported from
dependent module /unix.
0509-136 Symbol aio_nwait_timeout (number 6) is not exported from
dependent module /unix.
0509-136 Symbol aio_nwait_timeout64 (number 7) is not exported from
dependent module /unix.
0509-028 Symbol used in ? type does not match exported type.
0509-192 Examine .loader section symbols with the
'dump -Tv' command.
检查了一下才发现AIX的异步IO没打开,真是越来越大意了。修改参数,重启机器。
chdev -l aio0 -P -a autoconfig='available'
新建server , 正常安装
$ srvbuildres -r srvbuild.TIPS.rs
Directory is not a suitable Sybase directory.
Port number '5000' is being reserved or currently in use by a running server.
Building Adaptive Server 'TIPS':
Writing entry into directory services...
Directory services entry complete.
Building master device...
Master device complete.
Writing RUN_TIPS file...
RUN_TIPS file complete.
Starting server...
Server started.
Building sysprocs device and sybsystemprocs database...
sybprocs device and sybsystemprocs database created.
Running installmaster script to install system stored procedures...
installmaster: 10% complete.
installmaster: 20% complete.
installmaster: 30% complete.
installmaster: 40% complete.
installmaster: 50% complete.
installmaster: 60% complete.
installmaster: 70% complete.
installmaster: 80% complete.
installmaster: 90% complete.
installmaster: 100% complete.
installmaster script complete.
Creating two-phase commit database...
Two phase commit database complete.
Installing common character sets (Code Page 437, Code Page 850, ISO Latin-1,
Macintosh and HP Roman-8)...
Character sets installed.
Setting server name in Adaptive Server...
Server name added.
Server 'TIPS' was successfully created.
备注:
srvbuild.TIPS.rs 文件内容
srvbuild.release_directory: /export/home/sybase/ASE-12_5
srvbuild.product: sqlsrv
srvbuild.server_name: TIPS
srvbuild.new_config: yes
srvbuild.do_add_server: yes
srvbuild.do_upgrade: no
srvbuild.network_protocol_list: tcp
srvbuild.network_hostname_list: rp3440
srvbuild.network_port_list: 5000
srvbuild.server_page_size: 4k
srvbuild.master_device_physical_name: /export/home/sybase/data/master.dat
srvbuild.master_device_size: 200
srvbuild.master_database_size: 50
srvbuild.errorlog: /export/home/sybase/ASE-12_5/install/TIPS.log
srvbuild.sybsystemprocs_device_physical_name: /export/home/sybase/data/sysprocs.dat
srvbuild.sybsystemprocs_device_size: 120
srvbuild.sybsystemprocs_database_size: 120
srvbuild.sybsystemdb_device_physical_name: USE_DEFAULT
srvbuild.sybsystemdb_device_size: 5
srvbuild.sybsystemdb_database_size: 5
srvbuild.default_backup_server: TIPS_BS
sybase日志一直报以下错误:
00:00000:00000:2006/08/07 16:51:49.20 kernel ksmask__rpacket: Invalid tdslength
value 11569, kpid: 393609261
00:00000:00000:2006/08/07 16:51:57.44 kernel ksmask__rpacket: Invalid tdslength
value 11569, kpid: 393871367
00:00000:00000:2006/08/07 16:52:00.59 kernel ksmask__rpacket: Invalid tdslength
value 12320, kpid: 394068010
00:00000:00000:2006/08/07 16:52:00.61 kernel ksmask__rpacket: Invalid tdslength
value 12320, kpid: 394002498
00:00000:00000:2006/08/07 16:52:00.76 kernel ksmask__rpacket: Invalid tdslength
value 12320, kpid: 394264611
00:00000:00000:2006/08/07 16:52:11.61 kernel ksmask__rpacket: Invalid tdslength
value 11569, kpid: 394657812
前台表现:
有时候会连接不上数据库,但重试几次就可以连接上去了。
处理:
增大additional network memory,然后观察效果.计算方法:
网络请求用户的数量 * 应用开发包的请求大小 * 3 * 1.02
如:500 (# of simultaneous users connections) * 5120 (application packet size) * 3 * 1.02 = 7833600
You should set your additional network memory to 7,833,600 bytes, approx 7.5M.
1. 在 intdb 上添加磁带设备
sp_addumpdevice "type",logicalname,physicalname[,tapesize]
如:
sp_addumpdevice "type",mytapedump,"/dev/nrmt8",100
备注:
mytapedump 为配置磁带设备服务名(可任意取名)
/dev/nrmt8 为本地磁带设备
100 为配置100m .
2. 在 intdb 上配置 netmdb 备份服务连接串
备机上配置主机备份服务连接串在interfaces文件中加入远程备份服务器的条目,加入的名称与主机备份服务名称要一致,
但备份服务名不能与主机备份服务名相同.
3. 在 intdb 使用netmdb备份连接串,导出数据到本地磁带设备
dump database database_name to mytapedump with init at bs_server_name
备注:
database_name 备份的数据库名
mytapedump 磁带设备
bs_server_name 备份服务连接串
dump database my_sf to '/dev/rmt 0.1' at JF_BACKUP capacity=4000000 with init
在Sybase上安装MDA表的步骤如下:
首先配置cis参数为1
sp_configure 'enable cis', 1
go
其次,在servers中增加一个loopback服务,其实也就是指向自己的一个服务了
use master
go
sp_addserver loopback, null, @@servername
go
可以使用下面的语句来测试这个服务是否配置正确
set cis_rpc_handling on
go
这个如果返回是0,或者是执行正确,就表示刚才的配置正确了.
当然,也可以通过下面的方法来测试
exec loopback…sp_who
go
下面就是安装了,这一步非常重要:
isql -U sa -P yourpassword -S YOURSERVER
-i $SYBASE/$SYBASE_ASE/scripts/installmontables
看看屏幕的输出,如果有错误,就要想办法去排除了.
现在,得授予sa一个mon_role的权限了
use master
go
grant role mon_role to sa
go
用下面的语句来测试授权是否成功
select * from master..monState
go
这里返回正常的话,就可以继续下面的操作了
将下面的动态参数进行修改
sp_configure 'enable monitoring', 1
go
sp_configure 'sql text pipe active', 1
go
sp_configure 'sql text pipe max messages', 100
go
sp_configure 'plan text pipe active', 1
go
sp_configure 'plan text pipe max messages', 100
go
sp_configure 'statement pipe active', 1
go
sp_configure 'statement pipe max messages', 100
go
sp_configure 'errorlog pipe active', 1
go
sp_configure 'errorlog pipe max messages', 100
go
sp_configure 'deadlock pipe active', 1
go
sp_configure 'deadlock pipe max messages', 100
go
sp_configure 'wait event timing', 1
go
sp_configure 'process wait events', 1
go
sp_configure 'object lockwait timing', 1
go
sp_configure 'SQL batch capture', 1
go
sp_configure 'statement statistics active', 1
go
sp_configure 'per object statistics active', 1
go
然后,需要修改一个静态参数
sp_configure "max SQL text monitored", 5120
go
现在,你要作的就是重新启动Sybase数据库服务器,然后你就可以使用MDA表来监测Sybase数据库的系统性能
backup_server
srvbuild.release_directory: /export/home/sybase/ASE-12_5
srvbuild.product: bsrv
srvbuild.server_name: TIPS_BS_176
srvbuild.new_config: yes
srvbuild.do_add_backup_server: yes
srvbuild.do_upgrade: no
srvbuild.network_protocol_list: tcp
srvbuild.network_hostname_list: rp3440
srvbuild.network_port_list: 5002
srvbuild.language: USE_DEFAULT
srvbuild.character_set: USE_DEFAULT
srvbuild.tape_config_file: USE_DEFAULT
srvbuild.errorlog: /export/home/sybase/ASE-12_5/install/TIPS_BS_176.log
adaptive_server
sybinit.release_directory: /export/home/sybase/ASE-12_5
sybinit.product: sqlsrv
sqlsrv.server_name: TIPS
sqlsrv.new_config: yes
sqlsrv.do_add_server: yes
sqlsrv.network_protocol_list: tcp
sqlsrv.network_hostname_list: rp3440
sqlsrv.network_port_list: 5000
sqlsrv.server_page_size: 4K
sqlsrv.force_buildmaster: no
sqlsrv.master_device_physical_name: /export/home/sybase/data/master.dat
sqlsrv.master_device_size:120m
sqlsrv.master_database_size: 120m
sqlsrv.errorlog: USE_DEFAULT
sqlsrv.do_upgrade: no
sqlsrv.sybsystemprocs_device_physical_name: /export/home/sybase/data/sybsystemprocs.dat
sqlsrv.sybsystemprocs_device_size: 40m
sqlsrv.sybsystemprocs_database_size: 40m
sqlsrv.sybsystemdb_device_physical_name: /export/home/sybase/data/sybsystemdb.dat
sqlsrv.sybsystemdb_device_size: 40m
sqlsrv.sybsystemdb_database_size: 40m
sqlsrv.default_backup_server: TIPS_BS_176
新建服务
srvbuildres -r server_name.rs
一. Bcp 错误
ct_connect(): network packet layer: internal net library error: Net-Lib protocol driver call to connect two endpoints failed
Establishing connection failed.
无法连接远程服务器
cs_convert: cslib user api layer: common library error: The conversion/operation was stopped due to a syntax error in the source field.
CSLIB Message: - L0/O0/S0/N36/1/0:
导入表对应的列数量不正确
cs_convert: cslib user api layer: common library error: The conversion/operation was stopped due to a syntax error in the source field.
导入数据存在自增长列,但数据源不存在自增长列
blk_rowxfer(): blk layer: internal BLK-Library error: Data truncated while doing local character set conversion. col = 3
导入表对应的字段长度不足
ct_sendpassthru(): network packet layer: internal net library error: Net-Library operation terminated due to disconnect
CTLIB Message: - L5/O3/S5/N5/5/0:
字符集错误
二. Bcp 导入不同字符集数据
bcp in到字符集是utf8的数据库时使用以下的参数,这样就可以解决java程序使用utf8字符集看中文是乱码的问题
bcp dbname..tabname in filename -Uxx -Pxx -Sxx -Jcp936 -c -Y
一. SYBASE 系统参数调整
1.内存
sp_configure "max memory",1500000 重启生效(设置为共享内存的75%)
sp_configure "allocate max shared mem",1 启动的时候自动分配max memory指定的最大内存
sp_cacheconfig "default data cache","1500m" 设置数据缓存(设置为max memory的一半)
sp_cacheconfig "default data cache","cache_partition=2" 是CPU数量的倍数,对数据缓冲区分区
sp_poolconfig "default data cache","64m","16k" 设置16K 数据缓存
sp_poolconfig "default data cache","128m","8k" 设置8K 数据缓存
sp_configure "procedure cache size",90000 存储过程数据缓存sp_cacheconfig 'tempdb_cache','200m','mixed' 创建命名高速缓存sp_bindcache 'tempdb_cache',tempdb 捆绑临时数据库到tempdb_cache高速缓存
2.cpu
sp_configure "max online engines",2 设置使用的CPU数量
sp_configure "number of engines at startup",2 启动时使用CPU数量
3. 网络
sp_configure "default network packet size",2048 设置网络传送包的大小(重启动生效)
sp_configure "max network packet size",2048
4. 其他资源使用
sp_configure "number of locks",100000 锁使用数量
sp_configure "number of open indexes",5000 打开索引
sp_configure "number of open objects",5000 打开对象
sp_configure "number of user connections",1000 用户连接数
sp_configure "number of device",100 新建设备最大数量
二. sybase 设备调整
数据设备与日志设备必须分开,添加临时数据库设备
1. 数据设备
sp_deviceattr devname,"dsync",true
2. 日志设备
sp_deviceattr devname,"dsync",false
3. 临时数据库设备
sp_deviceattr devname,"dsync",false
三. sybase 数据结构调整
1. 数据库对象表、索引。。
(1)对表新建合理的索引,定期分析表
update statistics tabname (不锁表)
(2)整理数据库空间 (锁表,剩余空间必须为最大表的1.2倍)
reorg rebuild tabname
recreate clustered index
(3)重新编译存储过程与触发器
sp_recompile usertable (与表相关联的存储过程和触发器)
四. sybase 数据库监控
1. 数据库死进程
select * from master..syslogshold
五. sybase 数据库启动参数
-T3607 master
-T3608 其他数据
-m 单