SAP ASE数据库安装使用
检查安装环境，安装必要的lib软件包
确认数据库版本与操作系统版本是否兼容，如无法确认亦可尝试安装
本安装使用linux平台，其他操作系统需求可官网查阅或联系管理员admin@dbask.cn
测试环境：操作系统suse 12 sp5，数据库版本SAP ASE 16
| 必要的库文件 | 描述 |
|---|---|
| • openmotif • libXp • libXt • libXtst • libXi • libXmu • libXext • libSM • libICE • libX11 |
配置yum源，不同操作系统使用dnf或yum或zypper方式安装 zypper install openmotif libXp libXt libXtst libXi libXmu libXext libSM libICE libX11 |
| hostname | cpu | memory | 存储空间 | IP地址 |
|---|---|---|---|---|
| dbask.cn | 4core | 4G | 40G（文件系统，如使用raw设备，请提前划分，并设置为数据库用户权限） | 192.168.1.1 |
存储划分：
| 目录 | 大小 | 说明 |
|---|---|---|
| /opt/sybase | 10G | SAP ASE16数据库环境目录 |
| /data | 10G | SAP ASE16数据库设备目录 |
| /backup | 10G | 备份目录 |
数据库设备：
| 设备名 | 路径 | 大小 | 备注 |
|---|---|---|---|
| master | /data/master.dat | 500M | 使用300M |
| sybsystemprocs | /data/sybsystemprocs.dat | 500M | |
| sybsystempdb | /data/sybsystempdb.dat | 500M | |
| tempdb | /data/tempdb.dat | 1024M | |
| testdb | /data/testdb.dat | 2048M | 测试数据库数据设备 |
| testdb_log | /data/testdb_log.dat | 1024M | 测试数据库日志设备 |
数据库环境信息：
| 项目 | 内容 | 说明 |
|---|---|---|
| 数据库版本 | SAP ASE16.X for linux 64 | |
| /opt/sybase | 10G | SAP ASE16数据库环境目录 |
| 主机名 | dbask.cn | 编辑 /etc/hosts写入主机名及ip地址 示例：vi /etc/hosts 192.168.1.1 dbask.cn |
| 数据库用户 | sybase | groupadd sybase useradd sybase -d/opt/sybase -gsybase |
| 数据库设备目录 | /data | 注意修改属主为sybase用户;chown sybase:sybase /data |
| IP地址 | 192.168.1.1 | |
| 数据库端口 | 6000 | 备份服务端口6001 |
| 数据库服务名 | test | 备份服务名test_bak |
| 数据库页面大小 | 4k | 支持2k 4k 8k 16k |
| 数据库字符集 | cp936 | 支持iso utf8 gbk等 |
说明：数据库安装前，需修改host信息，增加相关用户；上传数据库安装包（介质）到服务器并解压
· 创建系统数据库服务脚本（服务名test）test.rs：
sybinit.release_directory: /opt/sybase sybinit.product: sqlsrv sqlsrv.server_name: test sqlsrv.sa_password: dbask.cn sqlsrv.new_config: yes sqlsrv.do_add_server: yes sqlsrv.network_protocol_list: tcp sqlsrv.network_hostname_list: 192.168.1.1 sqlsrv.network_port_list: 6000 sqlsrv.application_type: USE_DEFAULT sqlsrv.server_page_size: 4096 sqlsrv.force_buildmaster: no sqlsrv.master_device_physical_name: /data/master.dat sqlsrv.master_device_size: 500 sqlsrv.master_database_size: 300 sqlsrv.errorlog: USE_DEFAULT sqlsrv.do_upgrade: no sqlsrv.sybsystemprocs_device_physical_name: /data/sybsystemproc.dat sqlsrv.sybsystemprocs_device_size: 500 sqlsrv.sybsystemprocs_database_size: 500 sqlsrv.sybsystemdb_device_physical_name: /data/sybsystempdb.dat sqlsrv.sybsystemdb_device_size: 500 sqlsrv.sybsystemdb_database_size: 500 sqlsrv.tempdb_device_physical_name: /data/tempdb.dat sqlsrv.tempdb_device_size: 1024 sqlsrv.tempdb_database_size: 1024 sqlsrv.default_backup_server: test_bak
· 创建数据库备份服务脚本（服务名test_bak）test_bak.rs：
sybinit.release_directory: /opt/sybase sybinit.product: bsrv bsrv.server_name: test_bak bsrv.new_config: yes bsrv.do_add_backup_server: yes bsrv.do_upgrade: no bsrv.network_protocol_list: tcp bsrv.network_hostname_list: 192.168.1.1 bsrv.network_port_list: 6001 bsrv.allow_hosts_list: PUT_ALLOW_HOSTS_TO_USE_BACKUPSERVER_HERE bsrv.character_set: USE_DEFAULT bsrv.tape_config_file: USE_DEFAULT bsrv.errorlog: USE_DEFAULT sqlsrv.related_sqlsrvr: test sqlsrv.sa_login: sa sqlsrv.sa_password: dbask.cn
· 创建测试数据库脚本crdb_test.sql：
-- disk初始数据设备testdb
disk init name='testdb',physname='/data/testdb.dat',size='2048M'
go
-- disk初始日志设备testdb_log
disk init name='testdb_log',physname='/data/testdb_log.dat' size='1024M'
go
--创建test数据库，使用数据设备testdb和日志设备testdb_log
create database test on testdb='2048M' log on testdb_log='1024M'
go
· 数据库服务创建记录
srvbuildres -r test.rs
Warning: You have selected '4096' as the logical page size for the SAP Adaptive Server Enterprise. If you plan to load dump from another database, make sure this logical page size matches the size of the source database. The default logical page size in previous SAP Adaptive Server Enterprise versions was 2KB. Building SAP Adaptive Server Enterprise 'test': Writing entry into directory services... Directory services entry complete. Building master device... Master device complete. Writing RUN_test file... RUN_test file complete. Starting server... Server started. Set SA password... SA password is set. Building sysprocs device and sybsystemprocs database... sysprocs device and sybsystemprocs database created. Running installmaster script to install system stored procedures... installmaster: 10% complete. ... installmaster: 100% complete. installmaster script complete. Creating two-phase commit database... Two phase commit database complete. Extending tempdb database ... Extending tempdb 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 SAP Adaptive Server Enterprise... Server name added. Setting optimization goal... Setting optimization goal complete. Server 'test' was successfully created.
· 数据库备份服务创建记录
srvbuildres -r test_bak.rs
Building Backup Server 'test_bak': Writing entry into directory services... Directory services entry complete. Set allow hosts for Backup Server... Allow hosts are set. Writing RUN_test_bak file... RUN_test_bak file complete. Starting server... Server started. Server 'test_bak' was successfully created.
查阅已安装字符集及对应id
isql -Usa -Pdbask.cn -Stest
select name,id from syscharsets
go
修改字符集：
sp_configure "default character set id",ID
| 字符集 | ID |
|---|---|
| cp936 | 171 |
| cp850 | 2 |
| utf8 | 190 |
| cp858 | 197 |
| cp932 | 144 |
| gb18030 | 173 |
| iso_1 | 1 |
切换到sybase用户，进入解压缩的数据库安装目录执行 ./setup -i console 开始安装数据库环境
su - sybase
cd ebf*
./setup -i console
Preparing to install
...
Preparing CONSOLE Mode Installation...
...
you want to change something on a previous step, type 'back'.
PRESS ENTER TO CONTINUE:
· 选择安装目录 输入 /opt/sybase并确认
===============================================================================
Choose Install Folder
---------------------
Where would you like to install?
Default Install Folder: /opt/sap
ENTER AN ABSOLUTE PATH, OR PRESS ENTER TO ACCEPT THE DEFAULT
: /opt/sybase
INSTALL FOLDER IS: /opt/sybase
IS THIS CORRECT? (Y/N): Y
· 一般选择完整安装即可，输入3 回车
===============================================================================
Choose Install Set
------------------
Please choose the Install Set to be installed by this installer.
->1- Typical
2- Typical SAP ASE, database always-on option
3- Full
4- Custom
ENTER THE NUMBER FOR THE INSTALL SET, OR PRESS ENTER TO ACCEPT THE DEFAULT
: 3
· host agent 一般不用安装，可选
===============================================================================
SAP Host Agent
--------------
...
Do you want SAP ASE Installer to install SAP Host Agent? (Y/N): N
· liscense这里，先选3即可，部分版本选项为开发类；安装完成后可以单独加载授权licenses（如果有）
===============================================================================
Software License Type Selection
-------------------------------
What would you like to do?
->1- Install licensed copy of SAP Adaptive Server Enterprise
2- Install Express Edition of SAP Adaptive Server Enterprise
3- Evaluate SAP Adaptive Server Enterprise
Enter one of the options above: 3
· 下面这部分back跳过即可，当然也可以一直看完
===============================================================================
End-user License Agreement
--------------------------
1) All regions
Please enter the number of the location you are installing. (1-1) (Default: 1):
SOFTWARE TRIAL LICENSE AGREEMENT
SAP(r) ADAPTIVE SERVER(r) ENTERPRISE VERSION 16.0.x
· 30天免费试用......
Scroll down and read this 30-Day Trial License Agreement
...
Press ENTER to read the text [Type 'back' and press ENTER to skip the text] : back
同意，并继续即可
I agree to the terms of the SAP license for the install location specified.
(Y/N): Y
===============================================================================
Pre-Installation Summary
------------------------
Please Review the Following Before Continuing:
Product Name:
SAP Adaptive Server Enterprise
Install Folder:
/opt/sybase
Product Features:
SAP Adaptive Server Enterprise, Additional SAP ASE Language Modules,
...
Disk Space Information (for Installation Target):
Required: 2,763,856,548 Bytes
Available: ...... Bytes
PRESS ENTER TO CONTINUE:
===============================================================================
Ready To Install
----------------
InstallAnywhere is now ready to install SAP Adaptive Server Enterprise onto your system at the following location:
/opt/sybase
PRESS ENTER TO INSTALL:
===============================================================================
Installing...
-------------
[==================|==================|==================|==================]
· 等待安装完毕。
===============================================================================
Configure New Servers
---------------------
Please select products that you want to configure now :
[ ] 1 - Configure new SAP ASE
[ ] 2 - ...
To select an item enter its number, or 0 when you are finished: (Default: 0)
: 0
PRESS
cd --进入sybase用户家目录/opt/sybase
cat SYBASE.sh >>.bash_profile
--如果sybase用户的家目录，不是/opt/sybase，请注意修改为/opt/sybase/SYBASE.sh的路径;然后exit退出，并重新切换到sybase用户
exit
su - sybase
showserver
提示F S UID PID ...TIME CMD信息，即表明环境变量生效
srvbuilres -r test.rs
--test.rs 见《脚本》部分
srvbuilres -r test_bak.rs
--test_bak.rs 见《脚本》部分
数据库服务创建完毕后，执行showserver 即可看到服务进程
isql -Usa -Pdbask.cn -Stest
使用isql访问SAP ASE数据库，格式 isql -U用户名 -P密码 -S服务名
disk init name='testdb',physname='/data/testdb.dat',size='2048M'
go
disk init name='testdb_log',physname='/data/testdb_log.dat' size='1024M'
go
create database test on testdb='2048M' log on testdb_log='1024M'
go
use test
go
create table ......开始正式使用吧
或采用如下方式创建数据库
isql -Usa -Pdbask.cn -Stest -icrdb_test.sql --crdb_test.sql 见《脚本》部分
cd /opt/sybase/charsets charset -Usa -Pdbask.cn -Stest binary.srt cp936 isql -Usa -Pdbask.cn -Stest sp_configure "default character set id",171 go shutdown go cd /opt/sybase/ASE-1*/install startserver -f RUN_test
· 等提示结束退出后再执行一次，startserver -f RUN_test
Default Sort Order successfully changed. ASE shutdown after verifying System Indexes. ueshutdown: exiting bucket manager consolidator terminating Main thread performing final shutdown. Blocking call queue shutdown.
· 字符集更改成功，正常启动数据库即可...
startserver -f RUN_test isql -Usa -Pdbask.cn -Stest sp_helpsort
go