星期四, 8月 29, 2013

Sybase 15.7 資料庫安裝步驟測試(one day Sybase DBA)

今天花了一天體驗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

沒有留言:

LinkWithin-相關文件

Related Posts Plugin for WordPress, Blogger...