今天花了一天體驗Sybase
參考資料主要如下:
Sybase ASE 15 資料庫安裝實作及優化 Ref: http://blogstars.no-ip.org/blogstars/?p=932
----------------------------------------------------------------------------------------------------------------
1.Download it from sybase website.
2.Extract it to specifical location.
3.Run the setup.bin from extract directory to install.
gunzip ase157_sun64.tgz
tar -xvf ase157_sun64.tar
-bash-3.2# ./setup.bin -i console
Preparing to install...
Extracting the JRE from the installer archive...
Unpacking the JRE...
Extracting the installation resources from the installer archive...
Configuring the installer for this system's environment...
Launching installer...
===============================================================================
Sybase Adaptive Server Enterprise Suite (created with InstallAnywhere)
-------------------------------------------------------------------------------
Preparing CONSOLE Mode Installation...
===============================================================================
Introduction
------------
InstallAnywhere will guide you through the installation of Sybase Adaptive
Server Enterprise Suite 15.7 SP101.
It is strongly recommended that you quit all programs before continuing with
this installation.
Respond to each prompt to proceed to the next step in the installation. If you
want to change something on a previous step, type 'back'.
You may cancel this installation at any time by typing 'quit'.
PRESS <ENTER> TO CONTINUE:
===============================================================================
Choose Install Folder
---------------------
Where would you like to install?
Default Install Folder: /opt/sybase
ENTER AN ABSOLUTE PATH, OR PRESS <ENTER> TO ACCEPT THE DEFAULT
:
The directory /opt/sybase does not exist. Do you want to create it?
(Y/N): Y
===============================================================================
Choose Install Set
------------------
Please choose the Install Set to be installed by this installer.
->1- Typical
2- Full
3- Customize...
ENTER THE NUMBER FOR THE INSTALL SET, OR PRESS <ENTER> TO ACCEPT THE DEFAULT
: 2
===============================================================================
Software License Type Selection
-------------------------------
What would you like to do?
->1- Install licensed copy of Sybase Adaptive Server Enterprise Suite
2- Install Free Developer Edition of Sybase Adaptive Server Enterprise Suite
Enter one of the options above: [Enter]
===============================================================================
Software License Type Selection
-------------------------------
What would you like to do?
->1- Install licensed copy of Sybase Adaptive Server Enterprise Suite
2- Install Free Developer Edition of Sybase Adaptive Server Enterprise Suite
Enter one of the options above: 2
===============================================================================
End-user License Agreement
--------------------------
1) All regions
Please enter the number of the location you are installing. (1-1) (DEFAULT:
1): 1
.
.
.
I agree to the terms of the Sybase license for the install location
specified. (Y/N): y
===============================================================================
Pre-Installation Summary
------------------------
Please Review the Following Before Continuing:
Product Name:
Sybase Adaptive Server Enterprise Suite
Install Folder:
/opt/sybase
Product Features:
Adaptive Server Enterprise,
Additional ASE Language Modules,
Open Client,
DB-Library,
Embedded SQL/C,
Embedded SQL/Cobol,
XA Interface Library for ASE Distributed Transaction Manager,
ASE ODBC Driver,
Additional Connectivity Language Modules,
jConnect 7.0 for JDBC,
Interactive SQL,
QPTune,
SySAM License Utilities,
Remote Command and Control Agent for Adaptive Server,
SNMP Support for Adaptive Server,
SySAM License Server,
ASE Extension Module for Python,
ASE Extension Module for PHP,
Sybase Control Center,
Management User Interface for Adaptive Server
Disk Space Information (for Installation Target):
Required: 2,517,286,509 Bytes
Available: 260,310,287,360 Bytes
PRESS <ENTER> TO CONTINUE:
===============================================================================
Ready To Install
----------------
InstallAnywhere is now ready to install Sybase Adaptive Server Enterprise Suite
onto your system at the following location:
/opt/sybase
PRESS <ENTER> TO INSTALL:
===============================================================================
Installing...
-------------
[==================|==================|==================|==================]
[------------------|-------------
To select an item enter its number, or 0 when you are finished: (DEFAULT: 0)
: 6
[X] 1 - Configure new Adaptive Server
[X ] 2 - Configure new Backup Server
[X ] 3 - Configure new XP Server
[X ] 4 - Configure new Job Scheduler
[X ] 5 - Enable Self Management
[X] 6 - Configure Sybase Control Center
To select an item enter its number, or 0 when you are finished: (DEFAULT: 0)
: [Enter]
===============================================================================
Configure New Adaptive Server
-----------------------------
Adaptive Server Name (DEFAULT: ORADB2):
System Administrator's Password:
Confirm System Administrator's Password:
Port Number (DEFAULT: 5000):
Error Log (DEFAULT: /opt/sybase/ASE-15_0/install/ORADB2.log):
Application Type
->1- Mixed (OLTP/DSS)
2- Online Transaction Proccessing (OLTP)
3- Decision Support Systems (DSS)
Select an application type: 1
Page Size
1- 2k
->2- 4k
3- 8k
4- 16k
Select a page size: 2
Warning: You have selected 4k as the logical page size for the
Adaptive Server. 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 Adaptive Server
versions was 2KB.
PRESS <ENTER> TO CONTINUE:
1- chinese
2- french
3- german
4- japanese
5- korean
6- polish
7- portuguese
8- spanish
9- thai
->10- us_english
Default Language:
1- cp437 : Code Page 437, (United States) character set.
2- cp850 : Code Page 850 (Multilingual) character set.
3- iso15 : ISO_8859-15:1998, Latin9, Western Europe
->4- iso_1 : ISO 8859-1 (Latin-1) - Western European 8-bit character set.
5- mac : Macintosh default character set for Western European locales.
6- roman8 : Hewlett-Packard proprietary character set for European locales.
7- roman9 : Hewlett-Packard proprietary character set for European locales.
8- utf8 : Unicode 3.1 UTF-8 Character Set
Default Character Set:
->1- bin_iso_1 : Binary ordering, for the ISO 8859/1 or Latin-1 character set
(iso_1).
2- dictionary_iso_1 : General purpose dictionary ordering.
3- espdict_iso_1 : Spanish dictionary ordering.
4- espnoaccents_iso_1 : Spanish case and accent insensitive dictionary order.
5- espnocase_iso_1 : Spanish case insensitive dictionary order.
6- noaccents_iso_1 : Dictionary order, case insensitive, accent insensitive.
7- nocase_iso_1 : Dictionary order, case insensitive.
8- nocasepref_iso_1 : Dictionary order, case insensitive with preference.
Default Sort Order:
Optimize ASE Configuration
->1- No
2- Yes
Do you want to optimize ASE configuration?:
Create sample databases
->1- No
2- Yes
Do you want to create sample databases?: 2
Master Device (DEFAULT: /opt/sybase/data/master.dat):
Master Device Size (MB) (DEFAULT: 73):
Master Database Size (MB) (DEFAULT: 26):
System Procedure Device (DEFAULT: /opt/sybase/data/sysprocs.dat):
System Procedure Device Size (MB) (DEFAULT: 172):
System Procedure Database Size (MB) (DEFAULT: 172):
System Device (DEFAULT: /opt/sybase/data/sybsysdb.dat):
System Device Size (MB) (DEFAULT: 6):
System Database Size (MB) (DEFAULT: 6):
Tempdb Device (DEFAULT: /opt/sybase/data/tempdbdev.dat):
Tempdb Device Size (MB) (DEFAULT: 100):
Tempdb Database Size (MB) (DEFAULT: 100):
Enable PCI
->1- No
2- Yes
Do you want to configure PCI/JAVA?:
===============================================================================
New Server Configuration Summary
--------------------------------
Adaptive Server
Adaptive Server Name ORADB2
Port Number 5000
Application Type Mixed (OLTP/DSS)
Create sample databases true
Page Size 4k
Error Log /opt/sybase/ASE-15_0/install/ORADB2.log
Master Device /opt/sybase/data/master.dat
Master Device Size (MB) 73
Master Database Size (MB) 26
System Procedure Device /opt/sybase/data/sysprocs.dat
System Procedure Device Size (MB) 172
System Procedure Database Size (MB) 172
System Device /opt/sybase/data/sybsysdb.dat
System Device Size (MB) 6
System Database Size (MB) 6
Tempdb Device /opt/sybase/data/tempdbdev.dat
Tempdb Device Size (MB) 100
Tempdb Database Size (MB) 100
Default Language <use default>
Default Character Set <use default>
Default Sort Order <use default>
Do you want to continue? (DEFAULT: yes):
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.
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 Adaptive Server...
Server name added.
Setting optimization goal...
Setting optimization goal complete.
Server 'ORADB2' was successfully created.
===============================================================================
Create sample databases
-----------------------
Creating the "pubs2" database
CREATE DATABASE: allocating 1792 logical pages (7.0 megabytes) on disk 'master'
(1792 logical pages requested).
Database 'pubs2' is now online.
Database option 'trunc log on chkpt' turned ON for database 'pubs2'.
Running CHECKPOINT on database 'pubs2' for option 'trunc log on chkpt' to take
effect.
(return status = 0)
Type added.
(return status = 0)
Type added.
(return status = 0)
New primary key added.
(return status = 0)
New primary key added.
(return status = 0)
New primary key added.
(return status = 0)
New primary key added.
(return status = 0)
New primary key added.
(return status = 0)
New primary key added.
(return status = 0)
New primary key added.
(return status = 0)
New primary key added.
(return status = 0)
New primary key added.
(return status = 0)
New primary key added.
(return status = 0)
New primary key added.
(return status = 0)
New foreign key added.
(return status = 0)
New foreign key added.
(return status = 0)
New foreign key added.
(return status = 0)
New foreign key added.
(return status = 0)
New foreign key added.
(return status = 0)
New foreign key added.
(return status = 0)
New foreign key added.
(return status = 0)
New foreign key added.
(return status = 0)
New foreign key added.
(return status = 0)
New foreign key added.
(return status = 0)
Default bound to column.
(return status = 0)
Default bound to column.
(return status = 0)
Default bound to column.
(return status = 0)
Rule bound to table column.
(return status = 0)
Rule bound to table column.
(return status = 0)
Rule bound to table column.
(return status = 0)
(return status = 0)
===============================================================================
Create sample databases
-----------------------
(1 row affected)
Database option 'select into/bulkcopy/pllsort' turned ON for database 'pubs2'.
Running CHECKPOINT on database 'pubs2' for option 'select
into/bulkcopy/pllsort'
to take effect.
(return status = 0)
(1 row affected)
(1 row affected)
(1 row affected)
(1 row affected)
(1 row affected)
(1 row affected)
(1 row affected)
(1 row affected)
Return parameters:
txts
------------------
0x0000000000002379
(1 row affected)
(1 row affected)
Return parameters:
txts
------------------
0x00000000000023a0
(1 row affected)
(1 row affected)
Return parameters:
txts
------------------
0x00000000000023c5
(1 row affected)
(1 row affected)
Return parameters:
txts
------------------
0x00000000000023f2
(1 row affected)
(1 row affected)
Return parameters:
txts
------------------
0x0000000000002410
(1 row affected)
(1 row affected)
Return parameters:
txts
------------------
0x000000000000244a
Database option 'select into/bulkcopy/pllsort' turned OFF for database 'pubs2'.
Running CHECKPOINT on database 'pubs2' for option 'select
into/bulkcopy/pllsort'
to take effect.
(return status = 0)
The script installpix is complete.
No further action on your part is required.
===============================================================================
Create sample databases
-----------------------
Creating the "pubs3" database
CREATE DATABASE: allocating 1536 logical pages (6.0 megabytes) on disk 'master'
(1536 logical pages requested).
Database 'pubs3' is now online.
Database option 'trunc log on chkpt' turned ON for database 'pubs3'.
Running CHECKPOINT on database 'pubs3' for option 'trunc log on chkpt' to take
effect.
(return status = 0)
Type added.
(return status = 0)
Type added.
(return status = 0)
Default bound to column.
(return status = 0)
Default bound to column.
(return status = 0)
Default bound to column.
(return status = 0)
Rule bound to table column.
(return status = 0)
Rule bound to table column.
(return status = 0)
Rule bound to table column.
(return status = 0)
(return status = 0)
===============================================================================
Installation Completed
----------------------
The installation was successful.
Please check regularly for updates at http://www.sybase.com/downloads.
If you have not done so, please go to https://sybase.subscribenet.com to obtain
the Sybase software licenses.
PRESS <ENTER> TO EXIT THE INSTALLER:
寫好~/.profile, copy from /opt/sybase/SYBASE.env
export LANG=C
export SYBASE_JRE7=/opt/sybase/shared/JRE-7_0_7
export SYBASE_JRE7_32=/opt/sybase/shared/JRE-7_0_7
export SYBASE_JRE7_64=/opt/sybase/shared/JRE-7_0_7
export SYBASE_OCS=OCS-15_0
export INCLUDE=/opt/sybase/OCS-15_0/include:$INCLUDE
export LIB=/opt/sybase/OCS-15_0/lib:$LIB
export PATH=/opt/sybase/OCS-15_0/bin:$PATH
export LD_LIBRARY_PATH=/opt/sybase/OCS-15_0/lib:/opt/sybase/OCS-15_0/lib3p64:/op
t/sybase/OCS-15_0/lib3p:$LD_LIBRARY_PATH
export LD_LIBRARY_PATH_64=/opt/sybase/OCS-15_0/lib:/opt/sybase/OCS-15_0/lib3p64:
/opt/sybase/OCS-15_0/lib3p:$LD_LIBRARY_PATH_64
export LD_LIBRARY_PATH=/opt/sybase/DataAccess64/ODBC/lib:/opt/sybase/DataAccess6
4/ODBC/dm/lib64:$LD_LIBRARY_PATH
export LD_LIBRARY_PATH_64=/opt/sybase/DataAccess64/ODBC/lib:/opt/sybase/DataAcce
ss64/ODBC/dm/lib64:$LD_LIBRARY_PATH_64
export PATH=/opt/sybase/DBISQL/bin:$PATH
export PATH=/opt/sybase/SCC-3_2/bin:$PATH
export SCC_JAVA_HOME=/opt/sybase/shared/JRE-7_0_7
export SYBASE=/opt/sybase
export SYBASE_ASE=ASE-15_0
export PATH=/opt/sybase/ASE-15_0/bin:/opt/sybase/ASE-15_0/install:$PATH
export SYBROOT=/opt/sybase
:$
export PATH=/opt/sybase/SCC-3_2/bin:$PATH
export SCC_JAVA_HOME=/opt/sybase/shared/JRE-7_0_7
export SYBASE=/opt/sybase
export SYBASE_ASE=ASE-15_0
export PATH=/opt/sybase/ASE-15_0/bin:/opt/sybase/ASE-15_0/install:$PATH
export SYBROOT=/opt/sybase
export SYBASE_JRE_RTDS=/opt/sybase/shared/JRE-7_0_7
export LD_LIBRARY_PATH=/opt/sybase/ASE-15_0/lib:$LD_LIBRARY_PATH
export LD_LIBRARY_PATH_64=/opt/sybase/ASE-15_0/lib:$LD_LIBRARY_PATH_64
export SYBASE_WS=WS-15_0
export PATH=/opt/sybase/ASE-15_0/jobscheduler/bin:$PATH
alias isqldba='/opt/sybase/OCS-15_0/bin/isql -U sa -P root123 -S ORADB2'
#如何連到資料庫
/opt/sybase/OCS-15_0/bin/isql -S localhost -U root -P root123
或使用alias isqldba
-bash-3.2# cat SYBASE.env >> ~/.profile
-bash-3.2# source ~/.profile
-bash-3.2#
#Sybase client的連接參數檔
cat /opt/sybase/interfaces
ORADB2 è 這邊是自動帶hostname
master tcp ether oradb2 5000
query tcp ether oradb2 5000
#Sybase 的記憶體參數檔
/opt/sybase/ASE-15_0/ORADB2.cfg
/opt/sybase/OCS-15_0/bin/isql -U sa -P root123 -S ORADB2
#user_name: 登入帳號，例如sa
#password: 密碼，例如sa的口令123456
#server_name: 資料庫伺服器的名稱或者是在Dsedit使用程式中定義的相應的伺服器名稱，例如SYB125
#看所有參數的指令
sp_configure
go
#開啟sp_monitor 需調的參數(須重啟)
sp_configure "max SQL text monitored"
sp_configure "max SQL text monitored",1024
go
sp_configure "sp_monitor",1
go
#sp_monitor的一些用法
#http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.sprocs/html/sprocs/sprocs104.htm
sp_monitor enable,'connection monitoring'
sp_monitor enable,'statement monitoring'
sp_monitor enable,'event monitoring'
go
#Disable all monitoring
sp_monitor disable,'connection monitoring'
sp_monitor disable,'statement monitoring'
sp_monitor disable,'event monitoring'
go
sp_monitor "connection","diskio" --需先enable connection
sp_monitor "statement" --需先enable statement
go
#Displays event data for spid 25:
1>
sp_monitor "event","25" --需先enable event
go
WaitTime Description
-------- --------------------------------------------------
4883 waiting for incoming network data
24 waiting on run queue after sleep
8 wait for i/o to finish after writing last log page
#如何停止資料庫
1> shutdown
2> go
Server SHUTDOWN by request.
ASE is terminating this process.
CT-LIBRARY error:
ct_results(): network packet layer: internal net library error: Net-Library operation terminated due to disconnect
#如何啟動
startserver -f $SYBASE/$SYBASE_ASE/install/RUN_ORADB2
#查看全部資料庫的大小
1> sp_helpdb
2> go
name db_size owner dbid created durability lobcomplvl
inrowlen
status
-------------- ------------- ----- ----- ------------ ----------- ----------
--------
-----------------------------------------------------------------------------------------
master 26.0 MB sa 1 Aug 29, 2013 full 0
NULL
mixed log and data
model 6.0 MB sa 3 Aug 29, 2013 full 0
NULL
mixed log and data
pubs2 7.0 MB sa 4 Aug 29, 2013 full 0
NULL
trunc log on chkpt, mixed log and data
pubs3 6.0 MB sa 5 Aug 29, 2013 full 0
NULL
trunc log on chkpt, mixed log and data
sybsystemdb 12.0 MB sa 31513 Aug 29, 2013 full 0
NULL
mixed log and data
sybsystemprocs 172.0 MB sa 31514 Aug 29, 2013 full 0
NULL
trunc log on chkpt, mixed log and data
tempdb 106.0 MB sa 2 Aug 29, 2013 no_recovery 0
NULL
select into/bulkcopy/pllsort, trunc log on chkpt, mixed log and data, a
llow wide dol rows
(1 row affected)
(return status = 0)
1>
1> sp_helpdevice master
2> go
device_name physical_name
description
status cntrltype vdevno vpn_low vpn_high
----------- ---------------------------
----------------------------------------------------------------------------------------------------------
------ --------- ------ ------- --------
master /opt/sybase/data/master.dat
file system device, special, dsync on, directio off, default disk, phys
ical disk, 73.00 MB, Free: 16.00 MB
3 0 0 0 37375
(1 row affected)
dbname size allocated vstart lstart
----------- ------------- ------------------- ------ ------
master 26.00 MB Aug 29 2013 12:15PM 4 0
model 6.00 MB Aug 29 2013 12:15PM 13316 0
tempdb 6.00 MB Aug 29 2013 12:15PM 16388 0
sybsystemdb 6.00 MB Aug 29 2013 12:15PM 19460 0
pubs2 7.00 MB Aug 29 2013 12:19PM 22532 0
pubs3 6.00 MB Aug 29 2013 12:20PM 26116 0
(1 row affected)
(return status = 0)
1>
#加大master db 空間(新增10MB)
disk resize name="master", size="10M"
go
#Creating additional Sybase devices
1> select distinct vdevno from sysdevices order by vdevno
2> go
vdevno
-----------
0
1
2
3
(4 rows affected)
1>
#Initial 200MB的空間, vdevno 不可重複
disk init name= "users_device",
physname="/opt/sybase/data/users_device.dat",
vdevno = 4, size= 102400
go
#建立log device
disk init
name=logdev,
physname="/opt/sybase/data/logdev.dat",
size=102400
go
#Turn off the master device as default device:
sp_diskdefault "master", defaultoff
go
#把users_device 設定成default device
1> sp_diskdefault "users_device", defaulton
2> go
1> select @@maxpagesize
2> go
-----------
4096
(1 row affected)
1>
1> select pagesize ("master..sysobjects")
2> go
-----------
4096
(1 row affected)
1>
1> sp_logiosize
2> go
The transaction log for database 'master' will use I/O size of 4 Kbytes.
(return status = 0)
1>
1> sp_helpdevice users_device
go2>
device_name physical_name
description
status cntrltype vdevno vpn_low vpn_high
------------ -----------------------------
----------------------------------------------------------------------------------------------
------ --------- ------ ------- --------
users_device /opt/sybase/data/users_device
file system device, special, dsync off, directio on, physical disk, 200
.00 MB, Free: 200.00 MB
2 0 4 0 102399
(1 row affected)
dbname size allocated vstart lstart
------ ---- --------- ------ ------
(1 row affected)
(return status = 0)
1>
1> sp_helpdevice
2> go
device_name physical_name
description
status cntrltype vdevno vpn_low vpn_high
----------- ------------------------------
----------------------------------------------------------------------------------------------------------
------ --------- ------ ------- --------
master /opt/sybase/data/master.dat
file system device, special, dsync on, directio off, default disk, phys
ical disk, 73.00 MB, Free: 16.00 MB
3 0 0 0 37375
sysprocsdev /opt/sybase/data/sysprocs.dat
file system device, special, dsync off, directio on, physical disk, 172
.00 MB, Free: 0.00 MB
2 0 1 0 88063
systemdbdev /opt/sybase/data/sybsysdb.dat
file system device, special, dsync off, directio on, physical disk, 6.0
0 MB, Free: 0.00 MB
2 0 2 0 3071
tapedump1 /dev/rmt4
unknown device type, disk, dump device
16 2 0 0 20000
tapedump2 /dev/rst0
unknown device type, tape, 625 MB, dump device
16 3 0 0 20000
tempdbdev /opt/sybase/data/tempdbdev.dat
file system device, special, dsync off, directio on, physical disk, 100
.00 MB, Free: 0.00 MB
2 0 3 0 51199
(6 rows affected)
(return status = 0)
1>
#查詢資料庫字元集編碼
sp_helpsort
go
2> go
Collation Name Collation ID
------------------------------ ------------
altdict 45
altnoacc 39
altnocsp 46
binary 25
cyrnocs 64
defaultml 20
dict 51
elldict 65
espdict 55
espnoac 57
espnocs 56
gbpinyin 163
hundict 69
hunnoac 70
hunnocs 71
iso14651 22
noaccent 54
nocase 52
nocasep 53
rusnocs 59
scandict 47
scannocp 48
thaidict 21
turknoac 73
turknocs 74
utf8bin 24
nocase_eucgb 52
nocase_cp936 52
nocase_gb18030 52
nocase_eucjis 52
nocase_sjis 52
nocase_deckanji 52
gbpinyinnocs 26
gbpinyin_eucgb 163
gbpinyin_gb18030 163
gbpinyin_cp936 163
gbpinyinnocs_eucgb 26
gbpinyinnocs_gb18030 26
gbpinyinnocs_cp936 26
Loadable Sort Table Name Collation ID
------------------------------ ------------
cp932bin 129
cyrdict 140
dynix 130
eucjisbn 192
euckscbn 161
gb2312bn 137
rusdict 165
sjisbin 179
turdict 155
big5bin 194
Sort Order Description
------------------------------------------------------------------
Character Set = 1, iso_1
ISO 8859-1 (Latin-1) - Western European 8-bit character set.
Sort Order = 50, bin_iso_1
Binary ordering, for the ISO 8859/1 or Latin-1 character set (
iso_1).
Characters, in Order
------------------------------------------------------------------
! " # $ % & ' ( ) * + , - . / 0 1 2 3 4 5 6 7 8 9 : ; < = > ?
@ A B C D E F G H I J K L M N O P Q R S T U V W X Y Z [ \ ] ^ _
` a b c d e f g h i j k l m n o p q r s t u v w x y z { | } ~
¡ ¢ £ ¤ ¥ ¦ § ¨ © ª « ¬ ® ¯ ° ± ² ³ ´ µ ¶ · ¸ ¹ º » ¼ ½ ¾ ¿ À
Á Â Ã Ä Å Æ Ç È É Ê Ë Ì Í Î Ï Ð Ñ Ò Ó Ô Õ Ö × Ø Ù Ú Û Ü Ý Þ ß à
á â ã ä å æ ç è é ê ë ì í î ï ð ñ ò ó ô õ ö ÷ ø ù ú û ü ý þ ÿ
(return status = 0)
1>
#從系統檢查sybase process
-bash-3.2# ps -ef |grep -i sybase
root 11372 1 0 12:19:19 pts/1 0:00 sh -c /opt/sybase/ASE-15_0/bin/dataserver -sORADB2 -d/opt/sybase/data/master.da
root 11373 11372 0 12:19:19 ? 2:49 /opt/sybase/ASE-15_0/bin/dataserver -sORADB2 -d/opt/sybase/data/master.dat -e/o
root 12508 25153 0 14:00:38 pts/2 0:00 grep -i sybase
-bash-3.2#
1> sp_listener status
2> go
#啟動新的listener, 監聽5002 port
sp_listener "start","oradb2:5002" ,remaining
go
#停止listener
sp_listener "stop","oradb2:5002" ,remaining
go
#看有誰連入
-bash-3.2# isqldba
1> sp_who
2> go
fid spid status loginame origname hostname blk_spid dbname tempdbname
cmd block_xloid threadpool
--- ---- ---------- -------- -------- -------- -------- ------ ----------
----------------- ----------- ----------------
0 2 sleeping NULL NULL NULL 0 master tempdb
DEADLOCK TUNE 0 syb_default_pool
0 3 sleeping NULL NULL NULL 0 master tempdb
KPP HANDLER 0 syb_default_pool
0 4 sleeping NULL NULL NULL 0 master tempdb
ASTC HANDLER 0 syb_default_pool
0 5 sleeping NULL NULL NULL 0 master tempdb
CHECKPOINT SLEEP 0 syb_default_pool
0 6 sleeping NULL NULL NULL 0 master tempdb
HK WASH 0 syb_default_pool
0 7 sleeping NULL NULL NULL 0 master tempdb
HK GC 0 syb_default_pool
0 8 sleeping NULL NULL NULL 0 master tempdb
HK CHORES 0 syb_default_pool
0 9 sleeping NULL NULL NULL 0 master tempdb
PORT MANAGER 0 syb_default_pool
0 10 sleeping NULL NULL NULL 0 master tempdb
NETWORK HANDLER 0 syb_default_pool
0 11 sleeping NULL NULL NULL 0 master tempdb
LICENSE HEARTBEAT 0 syb_default_pool
0 21 sleeping NULL NULL NULL 0 master tempdb
NETWORK HANDLER 0 syb_default_pool
0 23 sleeping NULL NULL NULL 0 master tempdb
NETWORK HANDLER 0 syb_default_pool
0 24 recv sleep sa sa oradb2 0 master tempdb
AWAITING COMMAND 0 syb_default_pool
0 25 running sa sa oradb2 0 master tempdb
INSERT 0 syb_default_pool
(14 rows affected)
(return status = 0)
1>
使用以下指令模擬網路連入
/opt/sybase/OCS-15_0/bin/isql -U sa -P root123 -S ORADB2 -H localhost
於另一個視窗會看到spid 26的連線
1> sp_who
2> go
fid spid status loginame origname hostname blk_spid dbname tempdbname
cmd block_xloid threadpool
--- ---- ---------- -------- -------- --------- -------- ------ ----------
----------------- ----------- ----------------
0 2 sleeping NULL NULL NULL 0 master tempdb
DEADLOCK TUNE 0 syb_default_pool
0 3 sleeping NULL NULL NULL 0 master tempdb
KPP HANDLER 0 syb_default_pool
0 4 sleeping NULL NULL NULL 0 master tempdb
ASTC HANDLER 0 syb_default_pool
0 5 sleeping NULL NULL NULL 0 master tempdb
CHECKPOINT SLEEP 0 syb_default_pool
0 6 sleeping NULL NULL NULL 0 master tempdb
HK WASH 0 syb_default_pool
0 7 sleeping NULL NULL NULL 0 master tempdb
HK GC 0 syb_default_pool
0 8 sleeping NULL NULL NULL 0 master tempdb
HK CHORES 0 syb_default_pool
0 9 sleeping NULL NULL NULL 0 master tempdb
PORT MANAGER 0 syb_default_pool
0 10 sleeping NULL NULL NULL 0 master tempdb
NETWORK HANDLER 0 syb_default_pool
0 11 sleeping NULL NULL NULL 0 master tempdb
LICENSE HEARTBEAT 0 syb_default_pool
0 21 sleeping NULL NULL NULL 0 master tempdb
NETWORK HANDLER 0 syb_default_pool
0 23 sleeping NULL NULL NULL 0 master tempdb
NETWORK HANDLER 0 syb_default_pool
0 25 running sa sa oradb2 0 master tempdb
INSERT 0 syb_default_pool
0 26 recv sleep sa sa localhost 0 master tempdb
AWAITING COMMAND 0 syb_default_pool
(14 rows affected)
(return status = 0)
select hostname, hostprocess, program_name
from sysprocesses
where spid = 26
go
hostname hostprocess
program_name
------------------------------ ------------------------------
------------------------------
localhost 12539
isql
(1 row affected)
1>
此時已經反查到OS pid 為12539
-bash-3.2$ ps -ef |grep 12539
jay 12554 12549 0 14:15:00 pts/3 0:00 grep 12539
root 12539 25153 0 14:12:28 pts/2 0:00 /opt/sybase/OCS-15_0/bin/isql -U sa - -S ORADB2 -H localhost
-bash-3.2$
1>kill 26
go
可以看到原來的session 已經被kill了
7> sp_who
8> go
CT-LIBRARY error:
ct_results(): network packet layer: internal net library error: Net-Library operation terminated due to disconnect
#查詢某個物件的方法(表格定義)
1> sp_help sysprocesses
2> go
Name Owner Object_type Object_status Create_date
------------ ----- ------------ ------------- -------------------
sysprocesses dbo system table -- none -- Aug 29 2013 12:15PM
(1 row affected)
Column_name Type Length Prec Scale Nulls Not_compressed Default_name
Rule_name Access_Rule_name Computed_Column_object Identity
---------------- -------- ------ ---- ----- ----- -------------- ------------
--------- ---------------- ---------------------- ----------
spid smallint 2 NULL NULL 0 0 NULL
NULL NULL NULL 0
kpid int 4 NULL NULL 0 0 NULL
NULL NULL NULL 0
enginenum int 4 NULL NULL 0 0 NULL
NULL NULL NULL 0
status char 12 NULL NULL 0 0 NULL
NULL NULL NULL 0
suid int 4 NULL NULL 0 0 NULL
NULL NULL NULL 0
hostname varchar 30 NULL NULL 1 0 NULL
NULL NULL NULL 0
program_name varchar 30 NULL NULL 1 0 NULL
NULL NULL NULL 0
hostprocess varchar 30 NULL NULL 1 0 NULL
NULL NULL NULL 0
cmd varchar 30 NULL NULL 1 0 NULL
NULL NULL NULL 0
cpu int 4 NULL NULL 0 0 NULL
NULL NULL NULL 0
physical_io int 4 NULL NULL 0 0 NULL
NULL NULL NULL 0
memusage int 4 NULL NULL 0 0 NULL
NULL NULL NULL 0
blocked smallint 2 NULL NULL 0 0 NULL
NULL NULL NULL 0
dbid smallint 2 NULL NULL 0 0 NULL
NULL NULL NULL 0
uid int 4 NULL NULL 0 0 NULL
NULL NULL NULL 0
gid int 4 NULL NULL 0 0 NULL
NULL NULL NULL 0
tran_name varchar 64 NULL NULL 1 0 NULL
NULL NULL NULL 0
time_blocked int 4 NULL NULL 1 0 NULL
NULL NULL NULL 0
network_pktsz int 4 NULL NULL 1 0 NULL
NULL NULL NULL 0
fid smallint 2 NULL NULL 1 0 NULL
NULL NULL NULL 0
execlass varchar 30 NULL NULL 1 0 NULL
NULL NULL NULL 0
priority varchar 10 NULL NULL 1 0 NULL
NULL NULL NULL 0
affinity varchar 30 NULL NULL 1 0 NULL
NULL NULL NULL 0
id int 4 NULL NULL 1 0 NULL
NULL NULL NULL 0
stmtnum int 4 NULL NULL 1 0 NULL
NULL NULL NULL 0
linenum int 4 NULL NULL 1 0 NULL
NULL NULL NULL 0
origsuid int 4 NULL NULL 1 0 NULL
NULL NULL NULL 0
block_xloid int 4 NULL NULL 1 0 NULL
NULL NULL NULL 0
clientname varchar 30 NULL NULL 1 0 NULL
NULL NULL NULL 0
clienthostname varchar 30 NULL NULL 1 0 NULL
NULL NULL NULL 0
clientapplname varchar 30 NULL NULL 1 0 NULL
NULL NULL NULL 0
sys_id smallint 2 NULL NULL 1 0 NULL
NULL NULL NULL 0
ses_id int 4 NULL NULL 1 0 NULL
NULL NULL NULL 0
loggedindatetime datetime 8 NULL NULL 1 0 NULL
NULL NULL NULL 0
ipaddr varchar 64 NULL NULL 1 0 NULL
NULL NULL NULL 0
nodeid tinyint 1 NULL NULL 1 0 NULL
NULL NULL NULL 0
Object does not have any indexes.
keytype object related_object object_keys
related_keys
------- ------------ -------------- -------------------------
-------------------------
common sysdatabases sysprocesses dbid, *, *, *, *, *, *, *
dbid, *, *, *, *, *, *, *
common syslocks sysprocesses spid, *, *, *, *, *, *, *
spid, *, *, *, *, *, *, *
common syslogins sysprocesses suid, *, *, *, *, *, *, *
suid, *, *, *, *, *, *, *
name type partition_type partitions partition_keys
------------ ---------- -------------- ---------- --------------
sysprocesses base table roundrobin 1 NULL
partition_name partition_id compression_level pages row_count segment
create_date
--------------- ------------ ----------------- ----- --------- -------
-------------------
sysprocesses_32 32 none 0 0 system
Jan 1 1900 12:00AM
Partition_Conditions
--------------------
NULL
Avg_pages Max_pages Min_pages Ratio(Max/Avg)
Ratio(Min/Avg)
----------- ----------- ----------- ---------------------------
---------------------------
0 0 0 0.000000
0.000000
Table LOB compression level 0
Lock scheme Allpages
The attribute 'exp_row_size' is not applicable to tables with allpages lock
scheme.
The attribute 'concurrency_opt_threshold' is not applicable to tables with
allpages lock scheme.
exp_row_size reservepagegap fillfactor max_rows_per_page identity_gap
ascinserts
------------ -------------- ---------- ----------------- ------------
-----------
0 0 0 0 0
0
(1 row affected)
concurrency_opt_threshold optimistic_index_lock dealloc_first_txtpg
------------------------- --------------------- -------------------
0 0 0
(return status = 0)
1>
#Sybase dump database command
dump database pubs2
to "/dev/nrmt0"
with init
#dump_dbs.sql
dump database pubs2
to "/opt/sybase/scripts/syb_compressed.dmp"
with init, compression = "4"
go
-bash-3.2# cat dump_dbs.sh
/opt/sybase/OCS-15_0/bin/isql -U sa -P root123 -S ORADB2 -i /opt/sybase/scripts/dump_dbs.sql
-bash-3.2# ./dump_dbs.sh
Backup Server: 4.171.1.1: The current value of 'reserved pages threshold' is
85%.
Backup Server: 4.171.1.2: The current value of 'allocated pages threshold' is
40%.
Backup Server: 4.171.1.5: The current value of 'parallel scan' is 2.
Backup Server session id is: 10. Use this value when executing the
'sp_volchanged' system stored procedure after fulfilling any volume change
request from the Backup Server.
Backup Server: 4.41.1.1: Creating new disk file
/opt/sybase/scripts/syb_compressed.dmp.
Backup Server: 6.28.1.1: Dumpfile name 'pubs2132410D809 ' section number 1
mounted on disk file '/opt/sybase/scripts/syb_compressed.dmp'
Backup Server: 4.188.1.1: Database pubs2: 808 kilobytes (40%) DUMPED.
Backup Server: 4.188.1.1: Database pubs2: 1722 kilobytes (100%) DUMPED.
Backup Server: 3.43.1.1: Dump phase number 1 completed.
Backup Server: 3.43.1.1: Dump phase number 2 completed.
Backup Server: 3.43.1.1: Dump phase number 3 completed.
Backup Server: 4.188.1.1: Database pubs2: 1732 kilobytes (100%) DUMPED.
Backup Server: 3.42.1.1: DUMP is complete (database pubs2).
-bash-3.2#
1> create database pubs5 on default = "20M" log on logdev="20M"
2> go
CREATE DATABASE: allocating 5120 logical pages (20.0 megabytes) on disk
'users_device' (5120 logical pages requested).
CREATE DATABASE: allocating 5120 logical pages (20.0 megabytes) on disk 'logdev'
(5120 logical pages requested).
Database 'pubs5' is now online.
1>
#Import table from dump file
load database pubs5 from "/opt/sybase/scripts/syb_compressed.dmp"
go
Backup Server session id is: 20. Use this value when executing the
'sp_volchanged' system stored procedure after fulfilling any volume change
request from the Backup Server.
Backup Server: 6.28.1.1: Dumpfile name 'pubs2132410D809 ' section number 1
mounted on disk file '/opt/sybase/scripts/syb_compressed.dmp'
Backup Server: 4.188.1.1: Database pubs5: 2824 kilobytes (6%) LOADED.
Backup Server: 4.188.1.1: Database pubs5: 7174 kilobytes (17%) LOADED.
Backup Server: 4.188.1.1: Database pubs5: 7184 kilobytes (100%) LOADED.
Backup Server: 3.42.1.1: LOAD is complete (database pubs5).
All dumped pages have been loaded. ASE is now clearing pages above page 1792,
which were not present in the database just loaded.
ASE has finished clearing database pages.
Started estimating recovery log boundaries for database 'pubs5'.
Database 'pubs5', checkpoint=(1364, 19), first=(1364, 19), last=(1364, 19).
Completed estimating recovery log boundaries for database 'pubs5'.
Started ANALYSIS pass for database 'pubs5'.
Completed ANALYSIS pass for database 'pubs5'.
Started REDO pass for database 'pubs5'. The total number of log records to
process is 1.
Completed REDO pass for database 'pubs5'.
Use the ONLINE DATABASE command to bring this database online; ASE will not
bring it online automatically.
1>
1> sp_helpdb pubs5
2> go
name db_size owner dbid created durability lobcomplvl inrowlen
status
----- ------------- ----- ---- ------------ ---------- ---------- --------
-------
pubs5 40.0 MB sa 6 Aug 29, 2013 full 0 NULL
offline
(1 row affected)
device_fragments size usage
created free kbytes
------------------------------ ------------- --------------------
------------------------- ----------------
users_device 7.0 MB data and log
Aug 29 2013 3:45PM 7140
users_device 13.0 MB data and log
Aug 29 2013 3:45PM 13260
logdev 20.0 MB log only
Aug 29 2013 3:45PM not applicable
Msg 921, Level 14, State 2:
Server 'ORADB2', Procedure 'sp_helpdb', Line 817:
Database 'pubs5' has not been recovered yet - please wait and try again.
--------------------------------------------------------------------------------------------------------------
log only free kbytes = 0
(return status = 0)
1>
#至此, 建出了一個資料庫, 且使用獨立的log device
#安裝auto extend 套件for device
/opt/sybase/OCS-15_0/bin/isql -U sa -P root123 -S ORADB2 -i /opt/sybase/ASE-15_0/scripts/installdbextend
#p_dbextend 設定每次自動成長(Auto-Growth) 5M, 最大50M
exec sp_dbextend 'set', 'device', 'logdev', '5M', '500M'
go
exec sp_dbextend 'set', 'device', 'users_device' , '10M', '500M'
go
#設定資料庫獨立tempdev
1> sp_tempdb show
2> go
Temporary Database Groups
---------------------------
default
(1 row affected)
Database GroupName
---------- -----------
tempdb default
(1 row affected)
Login Application Group Database Hardness
------- ------------- ---------------- ---------- -------------------
(0 rows affected)
(return status = 0)
1>
use master
go
disk init name ='tempdb2dev' ,
physname='/opt/sybase/data/tempdb2dev.dat', size='128M'
go
create temporary database tempdb2 on tempdb2dev='60G' --超過居然可以
go
#建立test帳號
sp_addlogin test, root123, pubs5
go
#在這邊可以設定user group
sp_adduser 'test', group_name
go
Bind 方式有兩種, LG(login name), AP(application name)
#Bind isql application to tempdb2 temp tablespace
sp_tempdb 'bind','AP','isql','DB','tempdb2'
go
#Bind tempdb2 to user test
#sp_tempdb "bind","LG","<login-name>","DB","<database name>",null,null
sp_tempdb "bind","LG","test","DB","tempdb2",null,null
1>
online database pubs5
go
1> use pubs5
2> go
1> create table test (a varchar(10))
2> go
1>
1> use pubs5
2> go
1> insert into test values ('111')
2> go
(1 row affected)
1> commit
2> go
1> select * from test
2> go
a
----------
111
(1 row affected)
1>
今天就暫時到此....zzzZZZ
沒有留言:
張貼留言