[不能上传文件,只好列出内容]
Sybase学习体会
原创:戴黎鸣
第一大块:Sybase安装 [redhat 7.2]
Sybase rpm 包安装顺序 12.5.0.1 for linux
sybase-common-12.5.0.1DE-1.i386.rpm
sybase-ase-12.5.0.1ESD-1.i386.rpm
sybase-efts-12.5.0.1ESD-1.i386.rpm
sybase-openclient-12.5.0.1ESD-1.i386.rpm
sybase-esql-12.5.0.1ESD-1.i386.rpm
sybase-sqlremote-7.0.2-1.i386.rpm
sybase-chinese-12.5.0.1ESD-1.i386.rpm
安装
rpm -ivh sybase-common-12.5.0.3-7.i386.rpm
rpm -ivh sybase-ase-12.5.0.3-7.i386.rpm [ -Uhv --nodeps]
rpm -ivh sybase-efts-12.5.0.3-7.i386.rpm [ -Uhv --nodeps]
rpm -ivh sybase-openclient-12.5.0.3-7.i386.rpm
rpm -ivh sybase-esql-12.5.0.3-7.i386.rpm
rpm -ivh sybase-sqlremote-7.0.2-1.i386.rpm
卸载
rpm -e sybase-efts-12.5.0.3-7
rpm -e sybase-sqlremote-7.0.2-1
rpm -e sybase-esql-12.5.0.3-7
rpm -e sybase-openclient-12.5.0.3-7
rpm -e sybase-openclient-12.5.0.3-7
rpm -e sybase-ase-12.5.0.3-7
rpm -e sybase-chinese-12.5.0.3-7
rpm -e sybase-doc-12.5.0.3-7
rpm -e sybase-common-12.5.0.3-7
rpm -ql libstdc++
安装顺序和依赖关系说明:
sybase-common-12.5.0.1DE-1.i386.rpm The common package is a prerequisite to all other packages. It creates and installs the default runtime environment required by other packages, and includes tools to configure, install, and administer Adaptive Server and related components.
NOTE: The sybase-common-12.5-3.i386.rpm is a pre-required package. You must install it before you install any other rpm package as all depend on the common package.
sybase-ase-12.5.0.1ESD-1.i386.rpm This package installs all binary server components required to run Adaptive Server, Backup Server, XP Server, Monitor Server, Historical Server and related utilities and tools for database development and maintenance.
sybase-efts-12.5.0.1ESD-1.i386.rpm The efts package installs the Enhanced Full-Text Search Specialty Data Store, which provides the capability to do complex searches on character and text data stored in the ASE. The performance of the searches can be orders of magnitude faster than using standard SQL searches.
sybase-openclient-12.5.0.1ESD-1.i386.rpm The Open Client package installs the Sybase Open Client Software Development Kit (SDK). Open Client consists of programming interfaces and networking services for writing client applications for point-to-point communication to Sybase server products.
sybase-esql-12.5.0.1ESD-1.i386.rpm The Embedded SQL package installs the Sybase ESQL binaries, libraries, include files and sample programs. It consists of the programming interfaces and networking services for writing client application in Embedded-SQL for point-to-point communication to ASE.
NOTE: The sybase-esql-12.5.0.1ESD-1.i386.rpm package requires the sybase-openclient-12.5.0.1ESD-1.i386.rpm package.
sybase-sqlremote-7.0.2-1.i386.rpm The SQL Remote package installs the Sybase SQL Remote for Adaptive Server Enterprise. SQL Remote is a data-replication technology designed for two-way replication between a consolidated data server and large numbers of remote databases, typically including many mobile databases.
sybase-chinese-12.5.0.1ESD-1.i386.rpm
sybase-french-12.5.0.1ESD-1.i386.rpm
sybase-german-12.5.0.1ESD-1.i386.rpm
sybase-japanese-12.5.0.1ESD-1.i386.rpm
sybase-korean-12.5.0.1ESD-1.i386.rpm
sybase-polish-12.5.0.1ESD-1.i386.rpm
sybase-portuguese-12.5.0.1ESD-1.i386.rpm
sybase-thai-12.5.0.1ESD-1.i386.rpm
sybase-spanish-12.5.0.1ESD-1.i386.rpm
NOTE: These packages cannot be installed on a platform that is running an incorrect version of Linux, kernel and run-time. These packages do allow for upgrade of previous released versions under Linux. These packages do not allow for degrading later installs.
Consistency Check: Once the files have been transferred to the target machine, perform a consistency check before installing the packages. Enter:
#rpm -- checksig *.rpm
You should receive the following output:
sybase-common-12.5.0.1DE-1.i386.rpm: md5 OK
sybase-ase-12.5.0.1ESD-1.i386.rpm: md5 OK
sybase-efts-12.5.0.1ESD-1.i386.rpm: md5 OK
sybase-esql-12.5.0.1ESD-1.i386.rpm: md5 OK
sybase-openclient-12.5.0.1ESD-1.i386.rpm: md5 OK
sybase-sqlremote-12.5.0.1ESD-1.i386.rpm: md5 OK
sybase-french-12.5.0.1ESD-1.i386.rpm: md5 OK
sybase-german-12.5.0.1ESD-1.i386.rpm: md5 OK
Refer to the Installation Guide for detailed installation instructions
1、 Root用户,先将rpm包解压,解压后的目录为/opt/Sybase-12.5
rpm -ivh *.rpm 顺序为前面所述的顺序。
如果以前安装过,用
rpm -e 'rpm -qa|grep sybase'卸载
2、 执行$SYBASE/install/sybinstall.sh,它将创建sybase用户和口令,用passwd将sybase的用户口令修改为所要设定的口令。
3、 在root用户登录文件中加入以下语句(以bash用户,.bash_profile为例。更改系统内存值为60M):echo "62914560" >; /proc/sys/kernel/shmmax
{
1、 改/etc/sysctl.conf文件加入
kernel.shmmax=10000
fs.aio-max-size=10000
2、 编辑$SYBASE/ASE/init/ sample_resource_files/ srvbuild.adaptive_server.rs文件
其他服务建立文件也如此。
注意输入数据文件时,如果没有建立raw device 将要指定存放的目录和数据库数据文件。
3、 修改后用svrbuildres -r $SYBASE/ASE/init/sample_resource_files/
srvbuild.adaptive_server.rs执行将建立数据库。
} /opt/sybase-12.5/ASE-12_5/bin/srvbuildres -r /opt/sybase-12.5/ASE-12_5/init/
sample_resource_files/leon.adapative_server.rs
4、 设置完成后,重新用sybase登陆[他会自动设定完成环境变量],建一个目录存放日后的数据库文件
5、 重新restart computer
6、 sybase登陆x-windows,执行$SYBASE/ASE-12_5/bin/srvbuild,选择完四个服务器,键入数据,执行创建。
[
在X-Windows中使用srvbuild工具配置ASE产品
1、用sybase用户登录X-Windows,执行sybase安装目录(/opt/sybase-11.9.2)下/bin/srvbuild命令。
2、在srvbuild窗口中,选择要安装Server类型。把四种Server都选上。
3、给Server命名。将Adaptive Server命名为TEST,相应地,Backup Server自动命名为TEST_back,Monitor Server命名为TEST_mon,XP Server命名为TEST_XP。点击OK按钮,进入各Server的配置过程。
4、配置Adaptive Server。填写或选择以下内容:
Master device path(主设备路径):/db/sybsystem/master.dat
Master device size(MB)(主设备大小):60
Master database size(MB)(主数据库大小):20
Sybsystemprocs device path(系统存储过程设备路径):/db/sybsystem/systemprocs.dat
Sybsystemprocs device size(MB)(系统存储过程设备大小):60
Sybsystemprocs database size(MB)(系统存储过程数据库大小):60
Error log path(错误日志路径):/opt/sybase-11.9.2/install/TEST.log
Transport type(传输协议类型):tcp
Host name(主机名):192.168.0.1
Port number(监听端口号):4100
点击OK按钮,进入下一配置过程。
5、配置Backup Server。填写或选择以下内容:
Error log path:/opt/sybase-11.9.2/install/TEST_back.log
Tape configuration file:/opt/sybase-11.9.2/backup_tape.cfg
Language:(不填)
Character set:(不填)
Maximum number of network connections:25
Maximum number of server connections:20
Transport type:tcp
Host name:192.168.0.1
Port number:4200
点击OK按钮,进入下一配置过程。
6、配置Monitor Server。填写或选择以下内容:
Maximum number of connections:5
Error log path:/opt/sybase-11.9.2/install/TEST_mon.log
Configuration file path:/opt/sybase-11.9.2/install/TEST_mon.cfg
Share memory directory:/opt/sybase-11.9.2
Transport type:tcp
Host name:192.168.0.1
Port number:4300
点击OK按钮,进入下一配置过程。
7、配置XP Server。填写或选择以下内容:
Transport type:tcp
Host name:192.168.0.1
Port number:4400
点击Build Server按钮,开始创建Server,这时出现一个窗口,你可以看到整个创建过程。如果有显示以下类似信息,表示创建Server成功:
……
Server TEST was successfully created.
Done.
……
8、创建Server成功后,系统就会问你是否将Server本地化(Localize),即是用另外一种语言代替默认的us_english language,以及改变默认的iso_1字符集和Binary索引顺序。选择是NO。为什么呢?曾经把中文(eucgb)设为默认字符集,反而不支持中文大字集,因为eucgb是基于GB2312标准的。查了Sybase的手册中一些关于本地化的说明,得出的印象是,在ASE中有Unicode转换机制,可以转换来自不同字符集的服务器或客户端的数据。应用也证明,使用ASE的默认的语言、字符集、索引顺序来处理中文是可行的。
9、安装成功后要做的几件事。首先在Linux控制台模式下以sybase用户登录。
① 确认Server是否在运行。使用$SYBASE/install/下的showserver命令($SYBASE表示sybase的安装目录),应该可看见系统有几个sybase相关进程。或者用$SYBASE/bin/下的isql -Usa -P -STEST命令来登录Server,应该可以看见isql的提示符"1>;",再键入exit就可以退出了。
② 用sp_password null, 新口令
go将sa的用户口令改掉。
]
7、 成功后将自动启动数据库。可用isql -Usa -Ppassword -Sserver_name连进数据库。
isql -Usa -P -Ssybase
8、 执行1>;sp_diskdefault master , defaultoff
2>;go
将以后用户数据库数据文件不要建立在master所在的数据文件中。
9、 用sp_password oldpassword,newpassword设定sa的用户口令。
a) sp_password null,Sybase
b) go
10、 用shutdown关闭数据库。
11、 重新启动数据库用$SYBASE/ASE/install/RUN_sybase启动,这为前台启动方式。后台启动方式用。
$SYBASE/ASE/install/startserver -f RUN_Sybase
RUN_sybase例子:
#!/bin/sh
#
# ASE page size (KB): 2048
# Master device path: /opt/sybase-12.5/data/nextip.dat
# Error log path: /opt/sybase-12.5/ASE/install/sybase.log
# Configuration file path: /opt/sybase-12.5/ASE/sybase.cfg
# Directory for shared memory files: /opt/sybase-12.5/ASE
# Adaptive Server name: sybase
#
/opt/sybase-12.5/ASE/bin/dataserver \
-d/opt/sybase-12.5/data/nextip.dat \
-e/opt/sybase-12.5/ASE/install/sybase.log \
-c/opt/sybase-12.5/ASE/sybase.cfg \
-M/opt/sybase-12.5/ASE \
-ssybase \
关闭数据库:
用sa用户进入数据库执行shutdown命令即可完成数据库的关闭。
数据库设备的建立
重启在sybase用户下,
1>; use master
2>; go
3>; sp_diskdefault master , defaultoff
4>; go
5>; sp_helpdevice
6>; go
1>; disk init
2>; name='sybsyntaxdev',
3>; physname='/opt/sybase-12.5/data/user_data/testdb.dat',
4>; vdevno=4,
5>; size='30m'
6>;
7>; go
建立数据库设备后就可以建立用户数据库
sa
create database userdb on db_dev_name=size M
1>; disk init
2>; name='testdbdev',
3>; physname='/opt/sybase-12.5/data/user_data/testdb.dat',
4>; vdevno=6,
5>; size
6>; go
8>; use master
9>; go
10>; create database testdb on testdbdev=10
11>; go
建立用户数据库的用户
1>;sa connenction into database
2>;use testdb
3>;go
4>;sp_addlogin ldai, password, null, null, 'dailiming'
5>;go
6>;sp_adduser ldai,dlm
7>;go
isql -Uldai -Ppassword1 -Ssybase就可以连进testdb用户数据库了。
注意口令长度不得小于6位,如果口令以数字开头,还需要以单引号括起来。
Sp_addlogin提供用户能访问数据库的权限,如果没有指定用户具体访问那个数据库,则还需要在相应数据库中用sp_adduser user_name, user_password建一个相同名和口令的用户。
例如
1>; sp_adduser ldai, dlm
2>; go
3>;
如果用sa用户创建了一个用户数据库,想将他的所有者由sa改为其他用户。
确定其他用户只具有addlogin,不具有adduesr。如果有addusers则要sp_dropuser删除掉,否则不能赋给他所有者权限。
Isql -Usa
1>; use testdb
2>; go
3>; sp_changedbowner ldai, testdb
4>; go
5>; sp_default ldai, testdb
6>; go
7>; sp_helpdb testdb
8>; go
将更改数据库owner了,注master不能更改
Msg 5106, Level 16, State 6:
Server 'sybase', Line 1:
Parameter 'SIZE' requires value of type 'int'.
1>; disk init
2>; name='sybsyntaxdev',
3>; physname='/opt/sybase-12.5/data/user_data/sybsyntaxdev.dat',
4>; vdevno=4,
5>; size='10m'
6>; go
00:00000:00008:2003/10/22 14:33:19.21 kernel Initializing virtual device 4, '/opt/sybase-12.5/data/user_data/sybsyntaxdev.dat' with dsync 'on'.
00:00000:00008:2003/10/22 14:33:19.21 kernel Virtual device 4 started using asynchronous i/o.
00:00000:00008:2003/10/22 14:33:19.22 kernel Initializing device /opt/sybase-12.5/data/user_data/sybsyntaxdev.dat from offset 0 with zeros.
00:00000:00008:2003/10/22 14:33:19.25 kernel Finished initialization.
1>;
初次建立数据库对sa的口令要进行修改。
sp_password old_password, new_password, login name
1>; sp_password null, nextip, sa
2>; go
3>;
sp_dboption 是用来打开或关闭数据库的选项 [select into/bulkcopy]。只用这样数据库才能执行如select identity(int) id0,* into #temp1 from tablename这样的sql语句。查看是否打开option用sp_helpdb就可以查看。Sp_dboption只能在master 数据库中引用。是否允许列字段为空还是不为空也是用这个命令。
1>; use nextipdb
2>; go
3>; master..sp_dboption nextipdb,"select into/bulkcopy","true"
4>; go
5>; checkpoint
6>; go
1>; dbcc checkdb
2>; go
3>; dbcc checkcatalog
4>; go
可知dbserver_name是accunetsvr
dbserver_ip 是192.168.0.6
dbserver_port 是4100
dbname为nextip
url为 jdbc:sybase:Tds:192.168.0.6:4100/nextip
37、 创建identity列,如果是create table 时一定是numeric型。如果想要创建数据库自动为新建的所有表增加一个隐藏的identity字段,用[sp_dboption database_name, "auto identity", "true"]。在检索数据的时候必须隐式加上SYB_IDENTITY_COL作为隐藏的identity列,例如select SYB_IDENTITY_COL, sn_name from t-subnet
默认的隐藏精度大小为10如果用户想要增大其精度,可用[sp_configure "size of auto identity", 新的精度],例如: sp_configure "size of auto identity",15
38、 从select into 创建一个新的idenetity列,这在sql语句分页检索用。
Select idenetity_name=identity(精度) , * into new_table from old_table;
如:
select id0=identity(1,* into #subnets from t_subnet where sn_type=10;
39、 实现用sql语句进行分页查询方法:
A. 创建一个临时表带identity字段 select id=indentity(20), * into #table_anme from table_name where 条件
B. 然后根据id进行检索第n条到m条数据 (也可用between and)
C. 最后Drop掉该临时表
D. 注意要打开数据库的select into /bulk copy属性 sp_dboption database_name, "select into/bulk copy", "true"才能进行select into操作
E. mssql中格式为select identity(int) id, * from #table_name from table_name where 条件
40、 linux下访问sql-server用db-library与sybase相同要素。只是远程访问端口为sql-server指定的1433
41、 JDBC访问MS-SQLSERVER
连接数据库:[需要这三个jar文件msbase.jar msutil.jar mssqlserver.jar]
JDBC DRIVER:com.microsoft.jdbc.sqlserver.SQLServerDriver
URL:jdbc:microsoft:sqlserver://Ip Or Name:1433;DatabaseName=XXX
42、 Oracle中的外连接符为=(+) 或(+)= 在Sybase中为=* 或 *=
43、 执行sybase过程中会有日志满了或存储空间不够了,出现supsend状态,可用
isql -Usa -Ppassword -Sdbservername
进去执行dump tran db_name with truncate_only进行清空操作
或者dump tran db_name to 'path/file'进行备份在执行清空。
44、 ms-sql中的substring(string, start, length)函数参数,start和length为INT型不能为numeric型。
45、 select * into 在oracle中的用法在ms-sql和sybase中的用法为select @变量=column
from 表名 where 条件
46、 游标在ms-sql和sybase中差别主要为while 判断的全局变量不同。
Sybase中为@@sqlstatus = 0
Ms-sql中为@@FETCH_STATUS = 0
关闭游标时在ms-sql中除了[close 游标名] 还要增加[deallocate 游标名]
49、 日期计算在ms-sql中
dateadd(日期代码,日期值, 日期)
select convert(varchar,getdate(),120) as year,convert(varchar,dateadd(ss, 1200,getdate()),120) as nYear
go
日期部分 简写 值
year yy 1753--9999
quarter qq 1--4
month mm 1--12
day of year dy 1--366
day dd 1--31
week wk 1--53
weekday dw 1--7(Sunday--Saturday)
hour hh 0--23
minute mi 0--59
second ss 0--59
milisecond ms 0-999
也可
select convert(varchar, getdate(), 111)+' '+convert(varchar,getdate(),10 as oldtime,convert(varchar,dateadd(ss,1200,getdate()),111)+' '+convert(varchar,dateadd(ss,1200,getdate()),10 as date
也可
select convert(varchar,convert(datetime,'20031223'),111)
go
50、 PostgreSQL中的lib编程时,select 和update /delete的成功失败条件判断是不同的。
Update/delete:: strcmp(PQcmdStatus(temp_res), ""==0
select :: !temp_res||PQresultStatus(temp_res) != PGRES_TUPLES_OK
51、 日期各格式的引用模式ms-sql和sybase中都通用
yyyy/mm/dd hh24:mi:ss
select convert(varchar, getdate(), 111)+' '+convert(varchar,getdate(),10 as oldtime,convert(varchar,dateadd(ss,1200,getdate()),111)+' '+convert(varchar,dateadd(ss,1200,getdate()),10 as date
select name,text from all_source where type='FUNCTION' and name='F_CHECK_IB_SCOPE';
select convert(varchar,convert(datetime,'20031223'),111)
go
Select count(ipgs_host_name)||',host-'||f_ipad_change_dec4('%s','1') From t_ipdev_gs
yyyy/mm/ddhh24miss
Select CO_DESC,to_char(sysdate - 1/48,'yyyymmddhh24miss') co_date from T_LICENSE
dateadd(ss, -1800, getdate())
select co_desc, convert(varchar, dateadd(ss, -1800, getdate()),111)+convert(varchar,dateadd(ss, -1800, getdate()),10 as co_date from t_license
select convert(varchar, getdate(), 111)+' '+convert(varchar,getdate(),10 as oldtime,convert(varchar, dateadd(ss, -1800, getdate()),111)+convert(varchar,dateadd(ss, -1800, getdate()),10 as co_date
yymmddhh:mi:ss
select convert(varchar,getdate(),112)+convert(varchar,getdate(),8)
go
52、 显示sql执行时间用: isql -Unextip -Pnextip -Sleon1 -p
进入即可,加小写-p参数
53、 MS-SQL中创建数据库
create database NEXTIPDB
on primary
(
name = DEVDB,
filename = 'd:\mssql_data\devdb.mdf',
size = 30MB,
maxsize = 100MB,
filegrowth = 10MB
),
(
name = DEVDB1,
filename = 'd:\mssql_data\devdb1.ndf',
size = 10MB,
filegrowth = 10MB
)
Log on
(
name = DEVDBLOG,
filename = 'e:\mssql_log\devdblog.ldf',
size = 10MB,
maxsize = 100MB,
filegrowth = 10MB
)
go
54、 细小区别
Sybase::
IF NOT EXISTS (SELECT * FROM master..syslogins, master..sysdatabases
WHERE master..syslogins.suid = master..sysdatabases.suid
AND master..syslogins.name = 'nextip')
EXEC sp_changedbowner nextip, NEXTIPDB
GO
insert into MSS_Test values('瑯琅')
就会报错
Server Message: Number 2402, Severity 16
Line 1:
Error converting client characters into server's character set. Some character(s) could not be converted作者: emate 时间: 2004-04-27 19:56 标题: 最近一段时间都在弄sybase和ms-sqlserver,写了一份学习笔记 在csdn上看到一位网友说
更改服务器的字符集为cp936 ,