sybase的isql命令初使用

sybase的isql命令初使用

转载

isql

初用

sybase

这几日被Sybase烦透了。。。

isql命令存在于 /sybase/OCS-15_0/bin  路径下。

Sybase登录命令及参数：

usage: ./isql [-b] [-e] [-F] [-p] [-n] [-v] [-W] [-X] [-Y] [-Q]
        [-a display_charset] [-A packet_size] [-c cmdend] [-D database]
        [-E editor] [-h header] [-H hostname] [-i inputfile]
        [-I interfaces_file] [-J client_charset] [-K keytab_file]
        [-l login_timeout] [-m errorlevel] [-M labelname labelvalue]
        [-o outputfile] [-P password] [-R remote_server_principal]
        [-s col_separator] [-S server_name] [-t timeout] [-U username]
        [-V [security_options]] [-w column_width] [-y sybase directory]
        [-z localename] [-Z security_mechanism] [-x trusted.txt_file]
        [--retserverror] [--conceal [wildcard]] [--help]

待续。。。

./isql -U username -P password -S SVC

试图监控一下Sybase资源

1> use master
2> go
1> select * from monProcessStatement
2> go

出错：

Msg 12036, Level 17, State 1:
Server 'SVC', Line 1:
Collection of monitoring data for table 'monProcessStatement' requires that the
'enable monitoring', 'statement statistics active', 'per object statistics
active', 'wait event timing' configuration option(s) be enabled. To set the
necessary configuration, contact a user who has the System Administrator (SA)
role.

经炳华提示，网上google搜索:sp_configuration   'enable monitoring'

相关一解决办法地址http://www.sypron.nl/mda.html

操作：

1> sp_configure "enable monitoring" , 1
2> go
 Parameter Name                 Default     Memory Used Config Value
         Run Value    Unit                 Type
 ------------------------------ ----------- ----------- ------------
         ------------ -------------------- ----------
 enable monitoring                        0           0            1
                    1 switch               dynamic

(1 row affected)
Configuration option changed. ASE need not be rebooted since the option is
dynamic.
Changing the value of 'enable monitoring' does not increase the amount of memory
Adaptive Server uses.
(return status = 0)

1> sp_configure "statement statistics active", 1
2> go

 Parameter Name                 Default     Memory Used Config Value
         Run Value    Unit                 Type
 ------------------------------ ----------- ----------- ------------
         ------------ -------------------- ----------
 statement statistics active              0           0            1
                    1 switch               dynamic

(1 row affected)

1> sp_configure "per object statistics active" , 1
2> go
 Parameter Name                 Default     Memory Used Config Value
         Run Value    Unit                 Type
 ------------------------------ ----------- ----------- ------------
         ------------ -------------------- ----------
 per object statistics active             0           0            1
                    1 switch               dynamic

(1 row affected)

执行结果：

1> select * from monProcessStatement
2> go
 SPID   KPID        DBID        ProcedureID PlanID      BatchID     ContextID
         LineNumber  CpuTime     WaitTime    MemUsageKB  PhysicalReads
         LogicalReads PagesModified PacketsSent PacketsReceived
         NetworkPacketSize PlansAltered RowsAffected
         DBName                         StartTime
 ------ ----------- ----------- ----------- ----------- ----------- -----------
         ----------- ----------- ----------- ----------- -------------
         ------------ ------------- ----------- ---------------
         ----------------- ------------ ------------
         ------------------------------ --------------------------
    374   111149190           1           0           0          23           0
                   1           0           0          26             0
                    3             0           0               0
                      2048            0            0
         master                                Jul 14 2009  7:38PM

(1 row affected)

按《Performance and Tunning: Monitoring and Anaylzing》的示例执行：

1> select s.SPID, s.CpuTime, t.LineNumber, t.SQLText from master..monProcessStat
ement s, master..monProcessSQLText t where s.SPID=t.SPID order by s.CpuTime DESC
2> go

仍是出错：

Msg 12036, Level 17, State 1:
Server 'SVC', Line 1:
Collection of monitoring data for table 'monProcessSQLText' requires that the
'max SQL text monitored', 'SQL batch capture' configuration option(s) be
enabled. To set the necessary configuration, contact a user who has the System
Administrator (SA) role.
(0 rows affected)

1> sp_configure "SQL batch capture" , 1
2> go
 Parameter Name                 Default     Memory Used Config Value
         Run Value    Unit                 Type
 ------------------------------ ----------- ----------- ------------
         ------------ -------------------- ----------
 SQL batch capture                        0           0            1
                    1 switch               dynamic

(1 row affected)

1> sp_configure "max SQL text monitored", 1024
2> go
 Parameter Name                 Default     Memory Used Config Value
         Run Value    Unit                 Type
 ------------------------------ ----------- ----------- ------------
         ------------ -------------------- ----------
 max SQL text monitored                   0          30            1024
                    0 bytes                static

(1 row affected)

一直试了N次，每次显示参数修改成功，但运行总是出错。

后来在DBArtisan里运行sp_configure "max SQL text monitored", 1024   多出一个提示：

Configuration option changed. Since the option is static, Adaptive Server must be rebooted in order for the change to take effect.

Changing the value of 'max SQL text monitored' to '1024' increases the amount of memory ASE uses by 448 K.

需要reboot, 汗死了！

现在reboot中。。。漫长等待。。。。。。

/sybase/ASE-15_0/scripts

• This query creates a stored procedure that prints the executed SQL and the
backtrace of any stored procedures for diagnostic purposes:
create procedure sp_backtrace @spid int as
begin
select SQLText
from master..monProcessSQLText
where SPID=@spid
print "Stacktrace:"
select ContextID, DBName, OwnerName, ObjectName
from master..monProcessProcedures
where SPID=@spid
end