存档
在ASE12.x版本中Sybase没有提供类似replace的函数。在ASE15.x版本中添加了替换某个字符串中指定的字符为另一个字符的函数：str_replace。
使用方法为：
1> select str_replace('aaaa|bbb|ccc|','|',';')
2> go-------------
aaaa;bbb;ccc;
上面的例子为将字符串aaaa|bbb|ccc|中的|替换为；
但是，在ASE12.x中需要上面上面的功能，就稍微一点麻烦了。需要结合两个函数:charindex,stuff来循环替换。
两个函数的用法分别为：
charindex - Returns an integer representing the starting position of an expression.
charindex(expression1, expression2)
stuff - Returns the string formed by deleting a specified number of characters from one string and replacing them with another string.
stuff(char_expr1|uchar_expr1, start, length,char_expr2|uchar_expr2)
实现的算法为：
declare @my_var char(25)
select @my_var = 'abc|ert|rfrfrf|'
while charindex('|', @my_var) > 0
begin
select @my_var = stuff(@my_var, charindex('|', @my_var), 1, ';')
end
select @my_var
执行结果为：
1> declare @my_var char(25)
2> select @my_var = 'abc|ert|rfrfrf|'
3> while charindex('|', @my_var) > 0
4> begin
5> select @my_var = stuff(@my_var, charindex('|', @my_var), 1, ';')
6> end
7> select @my_var
8> go
-------------------------
abc;ert;rfrfrf;
————————————————————————————————-
—- 本文为andkylee个人原创，请在尊重作者劳动成果的前提下进行转载；
—- 转载务必注明原始出处 : http://www.dbainfo.net
—- 关键字：替换 字符串 replace str_replace stuff charindex
————————————————————————————————-
在Sybase ASE中isql是个小巧而灵活的命令行实用工具。本人非常喜欢用这个工具，无论备份恢复、创建大到数据库小到表索引、配置参数、查看数据库的监控信息、 分析dbcc的命令结果等等我都喜欢用isql来实现。有些用图形界面工具能够实现的功能，我都揣摩如何用命令来实现的，因为在ASE中所有用图形界面能 够实现的功能操作都是可以用命令来完成的（当然图形工具还不能实现的地方太多）。我几乎不用sybase central，感觉这个工具既难用还低级简单。
下面介绍isql工具的使用方法。
呵呵，一些简单的命令参数我就不在这赘述了。我想每个sybase DBA对isql应该再熟悉不过了。今天我仅仅介绍几个本人自认为不太常用但是又比较实用的命令参数。
首先，查看一下isql这个工具都有哪些参数。
可能大家都知道在windows下用程序文件名称后面加空格再加上/? 来查看命令帮助。但是，对isql不能这么用。需要输入isql --help，如下图显示结果（有省略）
C:\Documents and Settings\Administrator>isql --help
usage: isql [option1] [option2] ... where [options] are ...
-b Disables the display of the table headers output.
-e Echoes input.
-F Enables the FIPS flagger.
-p Prints performance statistics.
-n Removes numbering and the prompt symbol when used with -e.
-v Prints the version number and copyright message.
-W Turn off extended password encryption on connection retries.
-X Initiates the login connection to the server with client-side
password encryption.
-Y Tells the Adaptive Server to use chained transactions.
-Q Enables the HAFAILOVER property.
如果输入 isql --h ，则返回简化版的命令参数。
C:\Documents and Settings\Administrator>isql --h
Syntax Error in '--h'.
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]
今天仅介绍 -b, -e, -n,-E,-s,-w这六个参数的使用情况。
(1) 对于-b，命令参数的解释为：Disables the display of the table headers output。也就是不显示表的标题行。
如下面代码所示：
C:\Documents and Settings\Administrator>isql -Usa -Stest -b -Dandkylee
Password:
1> select * from testa
2> go
1 aaaaaa
2 bbbbbb
3 ccccccc
(3 rows affected)
(2) 对于参数-e 和 -n ，-n要依附于-e来使用。
-e 表示Echoes input，也就是回显sql语句。
-n 表示Removes numbering and the prompt symbol when used with -e，也就是不显示sql语句前面的行号。
-e的用法如下：
C:\Documents and Settings\Administrator>isql -Usa -Stest -e -Dandkylee
Password:
1> select db_name()
2> go
1> select db_name()
------------------------------
andkylee
(1 row affected)
1> select * from testa
2> go
1> select * from testa
id name
----------- ------------------------------
1 aaaaaa
2 bbbbbb
3 ccccccc
(3 rows affected)
-e 和 -n 的联合用法如下：
C:\Documents and Settings\Administrator>isql -Usa -Stest -e -n -Dandkylee
Password:
select db_name()
go
select db_name()
------------------------------
andkylee
(1 row affected)
select * from testa
go
select * from testa
id name
----------- ------------------------------
1 aaaaaa
2 bbbbbb
3 ccccccc
(3 rows affected)
这时候你可能在怀疑了，上面我都输入了sql语句，我还要在返回结果中回显刚才输入的sql语句干什么？
的确，这个地方不是-e的应用场景。如果你用isql执行一个sql文件，并想把返回的结果保存到一个文件中，那么这个时候-e就有用处了。isql会在每个sql语句的执行结果前面加上相应的sql语句，这样看起来就很方便了。
简单举个例子：
假设有d:\test_isql.txt，里面的内容为：
select db_name()
go
select * from testa
go
我执行如下的命令参数：isql -Usa -P -Stest -Dandkylee -e -id:\test_isql.txt -od:\test_isql_result.txt
查看d:\test_isql_result.txt的文件内容为：
1> select db_name()
------------------------------
andkylee
(1 row affected)
1> select * from testa
id name
----------- ------------------------------
1 aaaaaa
2 bbbbbb
3 ccccccc
(3 rows affected)
对每个语句的执行结果一目了然，非常直观。
(3) -E 参数的使用
-E editor的意思是：Specifies an editor other than the default editor vi，也就是你可以指定一个文本编辑器用来在isql会话过程中编辑sql语句。这一点和sqlplusw中的ed命令是一模一样的。
多说一点，通过other than the default editor vi字面意思就可以看出，windows下的isql和unix下的isql有点不同。unix的isql下默认就可以使用vi进行编辑sql语句，但是 windows上的isql必须手动指定文本编辑器才能够使用这个功能。
C:\Documents and Settings\Administrator>isql -Usa -Stest -Enotepad -Dandkylee
Password:
1> notepad
[sh:notepad D:\PROGRA~1\ADMINI~1\LOCALS~1\Temp\cti9CE.tmp]
1> select db_name()
2> go
3> reset
1> notepad
[sh:notepad D:\PROGRA~1\ADMINI~1\LOCALS~1\Temp\cti9CE.tmp]
1> select db_name()
2> go
------------------------------
andkylee
(1 row affected)
使用方法是在isql的连接参数中加上-Enotepad（或者其它的文本编辑器，如notepad2，editlus都行，只要在win+R中能够成功执行的都可以！）
然后在提示符下输入notepad加上回车，就会弹出一个记事本让你进行编辑sql语句。这样是不是很方便，尤其在执行很长的sql语句的时候。哪 怕输错一点点都要reset再重新输入一遍。 这样就可以在文本编辑器中编辑好了再去执行了。（呵呵，可能你会说？ 不能在记事本中编辑好了直接复制粘贴到isql下执行吗？当然可以! ）
另外需着重注意一点，在文本编辑器中编辑的sql语句不能带有go，大家可以在我上面的演示代码中看出这一点。这个苛刻的要求就限制了我们不能在文本编辑器中输入多个sql语句命令。只能一个一个得来。
(4) 再来介绍今天的最后两个参数 -s -w, 我也是最近才开始使用这个参数-s的。感觉挺好。
-s col_separator的意思是：Resets the column separator character, which is blank by default，也就是说，你可以重新指定新的列分割符来替代默认的空格分隔符。
-w column_width 的意思是：Sets the screen width for output，也就是说，你可以指定屏幕的显示宽度（是像素还是字节，我没搞清楚）。
我把这两个参数放在一起使用，感觉效果很不错。请看下面的显示结果：
C:\Documents and Settings\Administrator>isql -Usa -Stest -Enotepad -s"|" -w5000
Password:
1> use andkylee
2> go
1> select * from test
2> go
|id |name |net_type|colc |COLD|COLE|
|-----------|------------------------------|--------|----------|----|----|
| 1|liu |NULL |1 |NULL|NULL|
| 1|liu |NULL |1 |NULL|NULL|
| 2|zhang |NULL |1 |NULL|NULL|
| 3|邓啟中 |1 |1 |NULL|0 |
(4 rows affected)
1>
在这里我把列分割符替换成了|，显示出来的效果如上。 是不是有点类似mysql的显示效果？我把屏幕的宽度设置成了-w5000。同时需要注意的是：你也需要把cmd的屏幕宽度设置大些。在命令提示符cmd 的属性里选择布局，调整屏幕缓冲区里面的宽度。这样就会显示类似上面的效果了。
执行一个字段多点的表，看看效果吧！
1> select * from sysobjects
2> where id< 20
3> go
|name |id |uid |type|userstat|sysstat|indexdel|schemacnt|sysstat2 |crdate |expdate |deltrig |instrig |updtrig |seltrig |ckfirst |cache |audflags |objspare |versionts |loginame |identburnmax |spacestate|erlchgts |
|---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|-----------|-----------|----|--------|-------|--------|---------|-----------|--------------------------|--------------------------|-----------|-----------|-----------|-----------|-----------|------|-----------|-----------|--------------------------|------------------------------|-----------------------------------------|----------|------------------|
|sysobjects | 1| 1|S | 0| 97| 1| 0| 229376| Dec 2 2009 6:58PM| Dec 2 2009 6:58PM| 0| 0| 0| 0| 0| 0| 0| 0|NULL |NULL | NULL| 1|0x0000000000000000|
|sysindexes | 2| 1|S | 0| 97| 0| 0| 229376| Dec 2 2009 6:58PM| Dec 2 2009 6:58PM| 0| 0| 0| 0| 0| 0| 0| 0|NULL |NULL | NULL| 1|0x0000000000000000|
|syscolumns | 3| 1|S | 0| 97| 0| 0| 229376| Dec 2 2009 6:58PM| Dec 2 2009 6:58PM| 0| 0| 0| 0| 0| 0| 0| 0|NULL |NULL | NULL| 1|0x0000000000000000|
|systypes | 4| 1|S | 0| 97| 1| 0| 229376| Dec 2 2009 6:58PM| Dec 2 2009 6:58PM| 0| 0| 0| 0| 0| 0| 0| 0|NULL |NULL | NULL| 1|0x0000000000000000|
|sysprocedures | 5| 1|S | 0| 97| 1| 0| 229376| Dec 2 2009 6:58PM| Dec 2 2009 6:58PM| 0| 0| 0| 0| 0| 0| 0| 0|NULL |NULL | NULL| 1|0x0000000000000000|
|syscomments | 6| 1|S | 0| 97| 1| 0| 229376| Dec 2 2009 6:58PM| Dec 2 2009 6:58PM| 0| 0| 0| 0| 0| 0| 0| 0|NULL |NULL | NULL| 1|0x0000000000000000|
|syssegments | 7| 1|S | 0| 65| 0| 0| 98304| Dec 2 2009 6:58PM| Dec 2 2009 6:58PM| 0| 0| 0| 0| 0| 0| 0| 0|NULL |NULL | NULL| 1|0x0000000000000000|
|syslogs | 8| 1|S | 0| 1| 0| 0| 73728| Dec 2 2009 6:58PM| Dec 2 2009 6:58PM| 0| 0| 0| 0| 0| 0| 0| 0|NULL |NULL | NULL| NULL|NULL |
|sysprotects | 9| 1|S | 0| 97| 1| 0| 229376| Dec 2 2009 6:58PM| Dec 2 2009 6:58PM| 0| 0| 0| 0| 0| 0| 0| 0|NULL |NULL | NULL| 1|0x0000000000000000|
|sysusers | 10| 1|S | 0| 97| 3| 0| 229376| Dec 2 2009 6:58PM| Dec 2 2009 6:58PM| 0| 0| 0| 0| 0| 0| 0| 0|NULL |NULL | NULL| 1|0x0000000000000000|
|sysalternates | 11| 1|S | 0| 97| 1| 0| 229888| Dec 2 2009 6:58PM| Dec 2 2009 6:58PM| 0| 0| 0| 0| 0| 0| 0| 0|NULL |NULL | NULL| 1|0x0000000000000000|
|sysdepends | 12| 1|S | 0| 97| 1| 0| 229376| Dec 2 2009 6:58PM| Dec 2 2009 6:58PM| 0| 0| 0| 0| 0| 0| 0| 0|NULL |NULL | NULL| 1|0x0000000000000000|
|syskeys | 13| 1|S | 0| 97| 1| 0| 229376| Dec 2 2009 6:58PM| Dec 2 2009 6:58PM| 0| 0| 0| 0| 0| 0| 0| 0|NULL |NULL | NULL| 1|0x0000000000000000|
|sysgams | 14| 1|S | 0| 1| 0| 0| 73728| Dec 2 2009 6:58PM| Dec 2 2009 6:58PM| 0| 0| 0| 0| 0| 0| 0| 0|NULL |NULL | NULL| NULL|NULL |
|sysusermessages | 15| 1|S | 0| 113| 2| 0| 73728| Dec 2 2009 6:58PM| Dec 2 2009 6:58PM| 0| 0| 0| 0| 0| 0| 0| 0|NULL |NULL | NULL| NULL|NULL |
|sysreferences | 16| 1|S | 0| 97| 3| 0| 229376| Dec 2 2009 6:58PM| Dec 2 2009 6:58PM| 0| 0| 0| 0| 0| 0| 0| 0|NULL |NULL | NULL| 1|0x0000000000000000|
|sysconstraints | 17| 1|S | 0| 97| 2| 0| 229888| Dec 2 2009 6:58PM| Dec 2 2009 6:58PM| 0| 0| 0| 0| 0| 0| 0| 0|NULL |NULL | NULL| 1|0x0000000000000000|
|systhresholds | 18| 1|S | 0| 65| 0| 0| 98304| Dec 2 2009 6:58PM| Dec 2 2009 6:58PM| 0| 0| 0| 0| 0| 0| 0| 0|NULL |NULL | NULL| 1|0x0000000000000000|
|sysroles | 19| 1|S | 0| 97| 1| 0| 229888| Dec 2 2009 6:58PM| Dec 2 2009 6:58PM| 0| 0| 0| 0| 0| 0| 0| 0|NULL |NULL | NULL| 1|0x0000000000000000|
(19 rows affected)
1>
（你要是发现比|还好的列分割符号，麻烦告诉我一下。谢谢！）
(5) 最后，介绍一个对于显示结果非常实用的存储过程sp_autoformat.
我也是在浏览一些系统存错过程源代码的时候发现的这个过程。这个是sybase 未公开的系统过程，用来格式化输出结果。如果想看这个过程的语法，请到sybsystemprocs里面查找。
看个效果吧！
1> sp_autoformat sysobjects,@whereclause="where id < 20"
2> go
|name |id|uid|type|userstat|sysstat|indexdel|schemacnt|sysstat2|crdate |expdate |deltrig|instrig|updtrig|seltrig|ckfirst|cache|audflags|objspare|versionts |loginame|identburnmax|spacestate|erlchgts |
|---------------|--|---|----|--------|-------|--------|---------|--------|-------------------|-------------------|-------|-------|-------|-------|-------|-----|--------|--------|--------------------------|--------|------------|----------|------------------|
|sysobjects | 1| 1|S | 0| 97| 1| 0| 229376|Dec 2 2009 6:58PM|Dec 2 2009 6:58PM| 0| 0| 0| 0| 0| 0| 0| 0|NULL |NULL | NULL| 1|0x0000000000000000|
|sysindexes | 2| 1|S | 0| 97| 0| 0| 229376|Dec 2 2009 6:58PM|Dec 2 2009 6:58PM| 0| 0| 0| 0| 0| 0| 0| 0|NULL |NULL | NULL| 1|0x0000000000000000|
|syscolumns | 3| 1|S | 0| 97| 0| 0| 229376|Dec 2 2009 6:58PM|Dec 2 2009 6:58PM| 0| 0| 0| 0| 0| 0| 0| 0|NULL |NULL | NULL| 1|0x0000000000000000|
|systypes | 4| 1|S | 0| 97| 1| 0| 229376|Dec 2 2009 6:58PM|Dec 2 2009 6:58PM| 0| 0| 0| 0| 0| 0| 0| 0|NULL |NULL | NULL| 1|0x0000000000000000|
|sysprocedures | 5| 1|S | 0| 97| 1| 0| 229376|Dec 2 2009 6:58PM|Dec 2 2009 6:58PM| 0| 0| 0| 0| 0| 0| 0| 0|NULL |NULL | NULL| 1|0x0000000000000000|
|syscomments | 6| 1|S | 0| 97| 1| 0| 229376|Dec 2 2009 6:58PM|Dec 2 2009 6:58PM| 0| 0| 0| 0| 0| 0| 0| 0|NULL |NULL | NULL| 1|0x0000000000000000|
|syssegments | 7| 1|S | 0| 65| 0| 0| 98304|Dec 2 2009 6:58PM|Dec 2 2009 6:58PM| 0| 0| 0| 0| 0| 0| 0| 0|NULL |NULL | NULL| 1|0x0000000000000000|
|syslogs | 8| 1|S | 0| 1| 0| 0| 73728|Dec 2 2009 6:58PM|Dec 2 2009 6:58PM| 0| 0| 0| 0| 0| 0| 0| 0|NULL |NULL | NULL| NULL|NULL |
|sysprotects | 9| 1|S | 0| 97| 1| 0| 229376|Dec 2 2009 6:58PM|Dec 2 2009 6:58PM| 0| 0| 0| 0| 0| 0| 0| 0|NULL |NULL | NULL| 1|0x0000000000000000|
|sysusers |10| 1|S | 0| 97| 3| 0| 229376|Dec 2 2009 6:58PM|Dec 2 2009 6:58PM| 0| 0| 0| 0| 0| 0| 0| 0|NULL |NULL | NULL| 1|0x0000000000000000|
|sysalternates |11| 1|S | 0| 97| 1| 0| 229888|Dec 2 2009 6:58PM|Dec 2 2009 6:58PM| 0| 0| 0| 0| 0| 0| 0| 0|NULL |NULL | NULL| 1|0x0000000000000000|
|sysdepends |12| 1|S | 0| 97| 1| 0| 229376|Dec 2 2009 6:58PM|Dec 2 2009 6:58PM| 0| 0| 0| 0| 0| 0| 0| 0|NULL |NULL | NULL| 1|0x0000000000000000|
|syskeys |13| 1|S | 0| 97| 1| 0| 229376|Dec 2 2009 6:58PM|Dec 2 2009 6:58PM| 0| 0| 0| 0| 0| 0| 0| 0|NULL |NULL | NULL| 1|0x0000000000000000|
|sysgams |14| 1|S | 0| 1| 0| 0| 73728|Dec 2 2009 6:58PM|Dec 2 2009 6:58PM| 0| 0| 0| 0| 0| 0| 0| 0|NULL |NULL | NULL| NULL|NULL |
|sysusermessages|15| 1|S | 0| 113| 2| 0| 73728|Dec 2 2009 6:58PM|Dec 2 2009 6:58PM| 0| 0| 0| 0| 0| 0| 0| 0|NULL |NULL | NULL| NULL|NULL |
|sysreferences |16| 1|S | 0| 97| 3| 0| 229376|Dec 2 2009 6:58PM|Dec 2 2009 6:58PM| 0| 0| 0| 0| 0| 0| 0| 0|NULL |NULL | NULL| 1|0x0000000000000000|
|sysconstraints |17| 1|S | 0| 97| 2| 0| 229888|Dec 2 2009 6:58PM|Dec 2 2009 6:58PM| 0| 0| 0| 0| 0| 0| 0| 0|NULL |NULL | NULL| 1|0x0000000000000000|
|systhresholds |18| 1|S | 0| 65| 0| 0| 98304|Dec 2 2009 6:58PM|Dec 2 2009 6:58PM| 0| 0| 0| 0| 0| 0| 0| 0|NULL |NULL | NULL| 1|0x0000000000000000|
|sysroles |19| 1|S | 0| 97| 1| 0| 229888|Dec 2 2009 6:58PM|Dec 2 2009 6:58PM| 0| 0| 0| 0| 0| 0| 0| 0|NULL |NULL | NULL| 1|0x0000000000000000|
(19 rows affected)
(return status = 0)
(6) 补充，参数-p，显示性能统计信息.
参数-p：Prints performance statistics，与之对应的是会话级别的set statistics time on 。这两个方法都能够显示出 统计信息，但是，会话级别的set statistics time on比参数-p的显示结果更详细些。
示例如下：
利用参数-p来显示简要统计信息：
C:\Documents and Settings\Administrator>isql -Usa -P -p
1> select count(*) from sysobjects
2> go
-----------
160
(1 row affected)
Execution Time (ms.): 0 Clock Time (ms.): 0
在每个SQL语句的最后显示执行时间和耗用时间。
会话级别的选项set statistics time on 显示比较详细的统计时间:
C:\Documents and Settings\Administrator>isql -Usa -P
1>
2> set statistics time on
3> go
Execution Time 0.
Adaptive Server cpu time: 0 ms. Adaptive Server elapsed time: 0 ms.
1> select count(*) from sysobjects
2> go
Parse and Compile Time 0.
Adaptive Server cpu time: 0 ms.
-----------
160
Execution Time 0.
Adaptive Server cpu time: 0 ms. Adaptive Server elapsed time: 0 ms.
(1 row affected)
1>
由上可以看出，会话级别选项set statistics time on会显示解析和编译时间、SQL执行时间、以及总计的执行时间、耗用时间。
啰啰嗦嗦就介绍这么多吧！
————————————————————————————————-
—- 本文为andkylee个人原创，请在尊重作者劳动成果的前提下进行转载；
—- 转载务必注明原始出处 : http://www.dbainfo.net
—- 关键字：isql -b -e -E -n -s -w 命令实用工具 sp_autoformat
————————————————————————————————-
Sybase 在ASE v15.0.2 中新增了“应用程序”跟踪这个特性。
您可以利用“应用程序”跟踪功能收集一个正在运行中的客户会话的有用诊断信息，包括：执行查询、IO 消耗等；并且ASE 会自动把收集到的跟踪信息写到一个文本文件中。可以很方便得用这个特性来诊断一些系统性能瓶颈。
您只需要知道想要跟踪的会话的SPID(Server Process ID) 就可以对那个会话进行跟踪监控。应用系统的用户只需要被授予“set tracing ”权限就可以利用“应用程序”跟踪对自己的会话信息进行跟踪。
执行“应用程序”跟踪需要具有 set tracing 的权限。主要有两类用户：第一：具有sa或者sso角色的用户（默认具有set tracing权限）；第二:被赋予了set tracing 执行权限的用户。下面会分别予以介绍。
（1） 具有管理员特权的用户（属于 sa 或者 sso 角色）
对其他用户连接会话启用“应用程序”跟踪，执行：
set tracefile "<file-path>" for <spid>
关闭刚才打开的针对其他用户连接会话的跟踪，需要执行：
set tracefile off for <spid>
备注：
(1) 针对某个用户会话启用跟踪时，需要知道它的SPID 并填写跟踪结果文件保存的路径。如果没有指定跟踪结果文件保存的路径，那么ASE 会自动保存到$SYBASE 目录下。
(2) 在一个会话中，只能有一个set tracefile 为启用状态；并且，某一个SPID 不能被多个会话同时跟踪。
(3) 当被跟踪的会话自己退出，或者跟踪被手动关闭时，ASE 会生成跟踪结果文件。
(4) 在“应用程序”跟踪的过程中，如果跟踪结果文件所在的磁盘空间被耗尽，那么ASE 会自动保存跟踪文件并结束本次“应用程序”跟踪。
(5) 用sp_helpapptrace 存储过程来查看当前正在执行中的所有应用程序跟踪信息。
（2） 被赋予“set tracing” 权限的应用程序用户
对自己的会话启用跟踪，执行：
set tracefile "<file-path>"
结束对自己的会话上的跟踪，使用：
set tracefile off
备注： 不能使用sp_helpapptrace过程来查看正在被跟踪的会话。
（3 ） 哪些信息可被用来跟踪？
在“应用程序”跟踪中可以追踪以下的set option 命令。
set show_sqltext <on/off>
set showplan <on/off>
set statistics io <on/off>
set statistics time <on/off>
set statistics plancost <on/off>
set option 的子命令也可被用来跟踪。
set option show_lop <normal/brief/long/on/off> ---显示使用的逻辑操作数量
set option show_managers <normal/brief/long/on/off> --- 显示使用的数据结构管理器的数量
set option show_log_props <normal/brief/long/on/off> --- 显示所用的逻辑属性
set option show_parallel <normal/brief/long/on/off> --- 显示并行查询优化器信息
set option show_histograms <normal/brief/long/on/off> --- 显示直方图统计信息处理过程
set option show_abstract_plan <normal/brief/long/on/off> --- 显示虚拟查询计划具体信息
set option show_search_engine <normal/brief/long/on/off> --- 显示搜索引擎的详细信息
set option show_counters <normal/brief/long/on/off> --- 显示优化器找到的计划的数量
set option show_best_plan <normal/brief/long/on/off> --- 显示最优计划信息
set option show_code_gen <normal/brief/long/on/off> --- 显示代码产生器的信息
set option show_pio_costing <normal/brief/long/on/off> --- 显示估计的物理IO数
set option show_lio_costing <normal/brief/long/on/off> --- 显示估计的逻辑IO数
set option show_elimination <normal/brief/long/on/off> --- 显示分区消除信息
（4） 例子 （使用具有sa 和sso 角色的用户）
在一个会话中执行如下的命令：
set tracefile "c:/tmp/trace-spid-16.txt" for 16
set show_sqltext on
set showplan on
go
sp_helpapptrace
go
traced_spid tracer_spid trace_file
----------- ----------- ----------------------------
16 19 c:/tmp/ase-trace-spid-16.txt
(1 row affected)
(return status = 0)
上面的输出结果表明该会话的spid为16。
在另外一个会话中，在被监控的用户会话spid=16中执行sp_helpdb过程。
set tracefile off for 16
go
sp_helpapptrace
go
traced_spid tracer_spid trace_file
----------- ----------- ----------
(0 rows affected)
(return status = 0)
跟踪结果信息如下：
================================================================================
Application Tracing report for spid -1 from application "isql"
run by login "sa" on host "TEST"
Tracing started on 2010/04/24 17:08:10.23
================================================================================
No useful sqltext available.
QUERY PLAN FOR STATEMENT 1 (at line 1).
STEP 1
The type of query is EXECUTE.
2010/04/24 17:08:10.23
No useful sqltext available.
Sproc: sp_helpdb, Line: 0
QUERY PLAN FOR STATEMENT 1 (at line 0).
STEP 1
The type of query is DECLARE.
Sproc: sp_helpdb, Line: 38
QUERY PLAN FOR STATEMENT 2 (at line 38).
STEP 1
The type of query is COND.
1 operator(s) under root
|ROOT:EMIT Operator (VA = 1)
|
| |SCALAR Operator (VA = 0)
Sproc: sp_helpdb, Line: 40
QUERY PLAN FOR STATEMENT 3 (at line 40).
STEP 1
The type of query is SET OPTION OFF.
Sproc: sp_helpdb, Line: 43
QUERY PLAN FOR STATEMENT 4 (at line 43).
STEP 1
The type of query is SET OPTION ON.
Sproc: sp_helpdb, Line: 45
QUERY PLAN FOR STATEMENT 5 (at line 45).
STEP 1
The type of query is SELECT.
1 operator(s) under root
|ROOT:EMIT Operator (VA = 1)
|
| |SCALAR Operator (VA = 0)
Sproc: sp_helpdb, Line: 47
QUERY PLAN FOR STATEMENT 6 (at line 47).
STEP 1
The type of query is COND.
1 operator(s) under root
|ROOT:EMIT Operator (VA = 1)
|
| |SCALAR Operator (VA = 0)
Sproc: sp_helpdb, Line: 49
QUERY PLAN FOR STATEMENT 7 (at line 49).
STEP 1
The type of query is COND.
3 operator(s) under root
|ROOT:EMIT Operator (VA = 3)
|
| |RESTRICT Operator (VA = 2)(0)(0)(0)(4)(0)
| |
| | |SCALAR AGGREGATE Operator (VA = 1)
| | | Evaluate Ungrouped ANY AGGREGATE.
| | | Scanning only up to the first qualifying row.
| | |
| | | |SCAN Operator (VA = 0)
| | | | FROM TABLE
| | | | master.dbo.sysmessages
| | | | Index : ncsysmessages
| | | | Forward Scan.
| | | | Positioning by key.
| | | | Index contains all needed columns. Base table will not be read.
| | | | Keys are:
| | | | error ASC
| | | | Using I/O Size 2 Kbytes for index leaf pages.
| | | | With LRU Buffer Replacement Strategy for index leaf pages.
Sproc: sp_helpdb, Line: 53
QUERY PLAN FOR STATEMENT 8 (at line 53).
STEP 1
The type of query is SELECT.
1 operator(s) under root
|ROOT:EMIT Operator (VA = 1)
|
| |SCALAR Operator (VA = 0)
Sproc: sp_helpdb, Line: 55
QUERY PLAN FOR STATEMENT 9 (at line 55).
STEP 1
The type of query is COND.
3 operator(s) under root
|ROOT:EMIT Operator (VA = 3)
|
| |RESTRICT Operator (VA = 2)(0)(0)(0)(4)(0)
| |
| | |SCALAR AGGREGATE Operator (VA = 1)
| | | Evaluate Ungrouped ANY AGGREGATE.
| | | Scanning only up to the first qualifying row.
| | |
| | | |SCAN Operator (VA = 0)
| | | | FROM TABLE
| | | | master.dbo.sysmessages
| | | | Index : ncsysmessages
| | | | Forward Scan.
| | | | Positioning by key.
| | | | Index contains all needed columns. Base table will not be read.
| | | | Keys are:
| | | | error ASC
| | | | Using I/O Size 2 Kbytes for index leaf pages.
| | | | With LRU Buffer Replacement Strategy for index leaf pages.
Sproc: sp_helpdb, Line: 59
QUERY PLAN FOR STATEMENT 10 (at line 59).
STEP 1
The type of query is SELECT.
1 operator(s) under root
|ROOT:EMIT Operator (VA = 1)
|
| |SCALAR Operator (VA = 0)
Sproc: sp_helpdb, Line: 0
QUERY PLAN FOR STATEMENT 11 (at line 0).
STEP 1
The type of query is GOTO.
Sproc: sp_helpdb, Line: 0
QUERY PLAN FOR STATEMENT 12 (at line 0).
STEP 1
The type of query is GOTO.
Sproc: sp_helpdb, Line: 62
QUERY PLAN FOR STATEMENT 13 (at line 62).
STEP 1
The type of query is SET OPTION ON.
Sproc: sp_helpdb, Line: 69
QUERY PLAN FOR STATEMENT 14 (at line 69).
STEP 1
The type of query is COND.
1 operator(s) under root
|ROOT:EMIT Operator (VA = 1)
|
| |SCALAR Operator (VA = 0)
Sproc: sp_helpdb, Line: 70
QUERY PLAN FOR STATEMENT 15 (at line 70).
STEP 1
The type of query is SELECT.
2 operator(s) under root
|ROOT:EMIT Operator (VA = 2)
|
| |SCALAR AGGREGATE Operator (VA = 1)
| | Evaluate Ungrouped COUNT AGGREGATE.
| |
| | |SCAN Operator (VA = 0)
| | | FROM TABLE
| | | master.dbo.sysdatabases
| | | Index : ncsysdatabases
| | | Forward Scan.
| | | Positioning at index start.
| | | Index contains all needed columns. Base table will not be read.
| | | Using I/O Size 2 Kbytes for index leaf pages.
| | | With LRU Buffer Replacement Strategy for index leaf pages.
Sproc: sp_helpdb, Line: 73
QUERY PLAN FOR STATEMENT 16 (at line 73).
STEP 1
The type of query is SELECT.
3 operator(s) under root
|ROOT:EMIT Operator (VA = 3)
|
| |SCALAR AGGREGATE Operator (VA = 2)
| | Evaluate Ungrouped COUNT AGGREGATE.
| |
| | |RESTRICT Operator (VA = 1)(0)(0)(0)(6)(0)
| | |
| | | |SCAN Operator (VA = 0)
| | | | FROM TABLE
| | | | master.dbo.sysdatabases
| | | | Using Clustered Index.
| | | | Index : cdatabases
| | | | Forward Scan.
| | | | Positioning at index start.
| | | | Index contains all needed columns. Base table will not be read.
| | | | Using I/O Size 2 Kbytes for index leaf pages.
| | | | With LRU Buffer Replacement Strategy for index leaf pages.
Sproc: sp_helpdb, Line: 80
————————————————————————————————-
—- 本文为andkylee个人原创，请在尊重作者劳动成果的前提下进行转载；
—- 转载务必注明原始出处 : http://www.dbainfo.net
—- 关键字：tracefile showplan statistics 跟踪 会话
————————————————————————————————-
在ASE中为对象命名的时候，要避免使用系统保留关键字，以防止带来不必要的麻烦。如果是对象名称必须使用系统的自留字，请用 quoted_identifer将该名称引起来。在ASE15.x中可以使用中括号[]来强制使用自留字作为对象名称。（这点功能和sqlserver 相似！）
演示一下在ASE15.x中使用中括号引起来保留字作为列名来创建一张表。
1> select @@version
2> go
------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------
Adaptive Server Enterprise/15.0.3/EBF 16738 ESD#2/P/NT (IX86)/Windows 2003/ase1503/2708/32-bit/OPT/Mon Jul 27 20:19:56 2009
(1 row affected)
1> create table testa ([create] int ,[alter] int)
2> go
1> sp_help testa
2> go
Name Owner Object_type Create_date
----- ----- ----------- -------------------
testa dbo user table Apr 24 2010 4:10PM
(1 row affected)
Column_name Type Length Prec Scale Nulls Default_name Rule_name Access_Rule_name Computed_Column_object Identity
----------- ---- ------ ---- ----- ----- ------------ --------- ---------------- ---------------------- ----------
create int 4 NULL NULL 0 NULL NULL NULL NULL 0
alter int 4 NULL NULL 0 NULL NULL NULL NULL 0
Object does not have any indexes.
No defined keys for this object.
name type partition_type partitions partition_keys
----- ---------- -------------- ---------- --------------
testa base table roundrobin 1 NULL
partition_name partition_id pages row_count segment create_date
--------------- ------------ ----- --------- ------- -------------------
testa_672718418 672718418 1 0 default Apr 24 2010 4:10PM
Partition_Conditions
--------------------
NULL
Avg_pages Max_pages Min_pages Ratio(Max/Avg) Ratio(Min/Avg)
----------- ----------- ----------- --------------------------- ---------------------------
1 1 1 1.000000 1.000000
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
------------ -------------- ---------- ----------------- ------------ -----------
1 0 0 0 0 0
(1 row affected)
concurrency_opt_threshold optimistic_index_lock dealloc_first_txtpg
------------------------- --------------------- -------------------
0 0 0
(return status = 0)
1>
在12.x中不支持使用中括号来限定的方法。
回到正题，ASE的所有保留关键字有哪些呢？
可以通过查询系统表来得到结果。 在spt_values表中type为W表示系统保留字。
执行select number,name from spt_values where type='W'这条语句可以得到所有的系统自留关键字。
1> select @@version
2> go-------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
----------------
Adaptive Server Enterprise/12.5/SWR 9616 GA/P/NT (IX86)/OS 4.0/main/1647/32-bit
/
OPT/Fri Jun 01 16:58:25 2001
(1 row affected)
1> select number,name from spt_values where type='W'
2> go
number name
----------- ----------------------------
1 add
2 all
3 alter
4 and
5 any
6 arith_overflow
7 as
8 asc
9 at
10 authorization
11 avg
12 begin
13 between
14 break
15 browse
16 bulk
17 by
18 cascade
19 case
20 char_convert
21 check
22 checkpoint
23 close
24 clustered
25 coalesce
26 commit
27 compute
28 confirm
29 connect
30 constraint
31 continue
32 controlrow
33 convert
34 count
35 create
36 current
37 cursor
38 proxy_table
39 database
40 dbcc
41 deallocate
42 declare
43 default
44 delete
45 desc
46 disk
47 distinct
48 double
49 drop
50 dummy
51 dump
52 else
53 end
54 endtran
55 errlvl
56 errordata
57 errorexit
58 escape
59 except
60 exclusive
61 exec
62 execute
63 exists
64 exit
65 exp_row_size
66 external
67 fetch
68 fillfactor
69 for
70 foreign
71 from
72 goto
73 grant
74 group
75 having
76 holdlock
77 identity
78 identity_insert
79 identity_start
80 if
81 in
82 index
83 insert
84 install
85 intersect
86 into
87 is
88 isolation
89 jar
90 join
91 key
92 kill
93 level
94 like
95 lineno
96 load
97 lock
98 max
99 max_rows_per_page
100 min
101 mirror
102 mirrorexit
103 national
104 identity_gap
105 noholdlock
106 nonclustered
107 not
108 null
109 nullif
110 numeric_truncation
111 of
112 off
113 offsets
114 on
115 once
116 online
117 only
118 open
119 option
120 or
121 order
122 over
123 partition
124 perm
125 permanent
126 plan
127 precision
128 prepare
129 primary
130 print
131 privileges
132 proc
133 procedure
134 processexit
135 public
136 raiserror
137 read
138 readpast
139 readtext
140 reconfigure
141 references
142 remove
143 reorg
144 replace
145 quiesce
146 reservepagegap
147 return
148 revoke
149 role
150 rollback
151 rowcount
152 rows
153 rule
154 save
155 schema
156 select
157 set
158 setuser
159 shared
160 shutdown
161 some
162 statistics
163 stripe
164 sum
165 syb_identity
166 syb_restree
167 syb_terminate
168 table
169 temp
170 temporary
171 textsize
172 to
173 tran
174 transaction
175 trigger
176 truncate
177 tsequal
178 union
179 unique
180 unpartition
181 update
182 use
183 replication
184 user
185 user_option
186 using
187 values
188 varying
189 view
190 waitfor
191 when
192 where
193 while
194 with
195 work
196 writetext
197 modify
198 deterministic
199 func
200 function
201 inout
202 new
203 out
204 output
205 returns
206 stringsize
(206 rows affected)
1>
同样在ASE15.x环境上执行同样的语句，发现ASE15.x比12.x版本多了一些保留字。
name number
---------------------------- -----------
xmltest 207
xmlextract 208
xmlparse 209
encrypt 210
decrypt 211
materialized 212
count_big 213
tracefile 214
scroll 215
decrypt_default 216
insensitive 217
semi_sensitive 218
xmltable 219
关于保留字，对应着有一个系统存储过程sp_checkreswords可以用来查看系统内所有使用保留字作为对象名称的对象。
sp_checkreswords语法为:
sp_checkreswords - Detects and displays identifiers that are Transact-SQL
reserved words. Checks server names, device names, database
names, segment names, user-defined datatypes, object names,
column names, user names, login names, and remote login names.
sp_checkreswords [user_name_param]
之前我用create table testa ([create] int ,[alter] int)创建了一张表。在这张表所在的数据库内部执行sp_checkreswords过程，
系统报告出来了create alter 这两个列名在用关键字。
1> sp_checkreswords
2> go
Reserved Words Used as Database Object Names for Database,testdb.
Upgrade renames sysobjects.schema to sysobjects.schemacnt.
Owner
------------------------------
dbo
Table
Reserved Word Column Names
----------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------- -----------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------
testa
alter
testa
create
-------------------------------------------------------------
-------------------------------------------------------------
Owner
------------------------------
escourt4
Table
Reserved Word Column Names
----------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------- -----------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------
syscolumn
check
syscolumn
default
-------------------------------------------------------------
-------------------------------------------------------------
Database-wide Objects
---------------------
Found no reserved words used as names for database-wide objects.
(return status = 4)
1>
————————————————————————————————-
—- 本文为andkylee个人原创，请在尊重作者劳动成果的前提下进行转载；
—- 转载务必注明原始出处 : http://www.dbainfo.net
—- 关键字：12.0 15.0 spt_values 关键字 reserved words sp_checkreswords
————————————————————————————————-
Sybase ASE自12.5.0.3以来引入了一组监控表，称为：MonTables后者MDA tables。可以利用MDA表实现对sybase ASE的监控和诊断。监控表里面存储着对于ASE状态的统计、汇总信息的快照snapshot。我们可以像查询其他系统表（比如sysobjects、 sysindexes、syscolumns等）一样来查询这些监控表MonTables。
在12.x版本中MDA表默认是没有安装的，需要我们手动进行安装。在ASE15.x中新建数据库服务器的时候就默认装上了。
下面开始详细得介绍安装以及配置MDA的过程。
(1) 检查参数:enable cis是否启用？如果没有启用，打开该参数
sp_configure "enable cis"
go
Parameter Name Default Memory Used Config Value Run Value Unit Type
------------------------------ ----------- ----------- ------------ ------------ -------------------- ----------
enable cis 1 0 1 1 switch static
(1 row affected)
(2) 查看sysservers系统表中是否有loopback这一条记录，如果没有，手动添加一个指向自己的远程服务器
(注：在ASE12.5.4以及之后的版本中在创建数据库服务器的时候默认会添加一个loopback服务器。)
use master
go
sp_helpserver
go
name network_name class status id cost
----------- -------------- ------------ ------------------------------------------------------------------------ -- ----
SYB_BACKUP TEST_BS ASEnterprise timeouts, no net password encryption, writable , rpc security model A 1 NULL
SYB_EJB EJBServer ASEJB external engine auto start 2 NULL
SYB_JSAGENT TEST_JSAGENT ASEnterprise no timeouts, no net password encryption, writable , rpc security model A 4 1000
SYB_JSTASK TEST ASEnterprise timeouts, no net password encryption, writable , rpc security model A 6 1000
TEST TEST local 0 0
TEST_XP TEST_XP RPCServer no timeouts, no net password encryption, writable , rpc security model A 3 1000
(return status = 0)
sp_addserver loopback,null,@@servername
go
-- Test this configuration: -- (NB: this step is no longer required in 15.0 ESD#2 or later) set cis_rpc_handling on go -- -- Alternatively, run: -- sp_configure 'cis rpc handling', 1 -- ...and disconnect/reconnect your session exec loopback...sp_who -- note: 3 dots! go
(3) 安装MDA系统表
在unix的shell下执行：isql -Usa -Pyourpassword -Syourservername -i$SYBASE/$SYBASE_ASE/scripts/installmontables -o$SYBASE/$SYBASE_ASE/scripts/instmontables_log.txt
在windows的命令行下执行：isql -Usa -Pyourpassword -Syourservername -i％SYBASE％\％SYBASE_ASE%\scripts\installmontables -o％SYBASE％\％SYBASE_ASE%\scripts\instmontables_log.txt
（注：ASE15.x中不需要再安装mda表）
(4) 给需要有监控权限的登录赋予mon_role角色
use master
go
grant role mon_role to sa
go
use master go grant role mon_role to sa go
(5) 检查测试基本的MDA配置信息
1> select * from master..monState
2> go
LockWaitThreshold LockWaits DaysRunning CheckPoints NumDeadlocks DiagnosticDumps Connections MaxRecovery StartDate CountersCleared
----------------- ----------- ----------- ----------- ------------ --------------- ----------- ----------- -------------------------- --------------------------
5 0 0 0 0 0 9 5 Apr 24 2010 1:34PM Apr 24 2010 1:34PM
(1 row affected)
1>
(6) 启用所有的监控配置参数
sp_configure "sql text pipe active",1
go
sp_configure "sql text pipe max messages",2000
go
sp_configure "plan text pipe active",1
go
sp_configure "plan text pipe max messages",1000
go
sp_configure "statement pipe active",1
go
sp_configure "statement pipe max messages",5000
go
sp_configure "errorlog pipe active",1
go
sp_configure "errorlog pipe max messages",1000
go
sp_configure "deadlock pipe active",1
go
sp_configure "deadlock pipe max messages",1000
go
sp_configure "wait event timing",1
go
sp_configure "process wait events",1
go
sp_configure "object lockwait timing",1
go
sp_configure "SQL batch capture",1
go
sp_configure "statement statistics active",1
go
sp_configure "per object statistics active",1
go
sp_configure "max SQL text monitored",256
go
其中参数：max SQL text monitored需要重启ASE服务器后才能生效。
(7) 重启ASE后，就可以通过查询monTables来了解ASE的监控信息了。比如：查看当前会话执行的sql语句。
1> sp_autoformat "monProcessSQLText"
2> go
SPID KPID ServerUserID BatchID LineNumber SequenceInLine SQLText
---- ------- ------------ ------- ---------- -------------- ----------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------
31 2228258 1 35 1 1 SELECT SPID=right(space(80)+isnull(convert(varchar(80),SPID),'NULL'),4), KPID=right(space(80)+isnull
(convert(varchar(80),KPID),'NULL'),7), ServerUserID=right(space(80)+isnull(convert(varchar(80),ServerUserID),'NULL'),12), BatchID=right(space(80)+isnull
31 2228258 1 35 1 2 (convert(varchar(80),BatchID),'NULL'),7), LineNumber=right(space(80)+isnull(convert(varchar(80),Line
Number),'NULL'),10), SequenceInLine=right(space(80)+isnull(convert(varchar(80),SequenceInLine),'NULL'),14), SQLText=SUBSTRING(convert(varchar(255),SQLTe
31 2228258 1 35 1 3 xt),1,252) FROM monProcessSQLText
(3 rows affected)
(return status = 0)
备注：在ASE12.5.3及后续的版本中的某些罕见的情况下，配置参数"per object statistics active"会导致时间片的错误。这个bug已在ASE15.x中得到了修正。
另外，ASE的MDA监控功能对系统的整体性能是有不小的影响的，据说要损耗20%多的ASE系能。所以，不建议在生产环境上配置MDA监控表；如果启用了监控表，也一定要在不使用监控功能的时候及时关闭监控参数。执行：
sp_configure "enable monitoring",1
go
关于MDA监控表的使用，后面会有博文继续介绍...
————————————————————————————————-
—- 本文为andkylee个人原创，请在尊重作者劳动成果的前提下进行转载；
—- 转载务必注明原始出处 : http://www.dbainfo.net
—- 关键字：监控表 启用 MDA MonTables
————————————————————————————————-
在Sybase ASE中查看或者修改配置参数有多种方法，比如：利用系统存储过程sp_configure；Sybase Central；Embarcadero DBArtisan等。
我比较喜欢用系统过程sp_configure来修改和查看参数。不管用那种方式，ASE的配置参数信息都是存储在两张系统表：sysconfigures和syscurconfigs上的。其中，表sysconfigures存储所有参数已生效 的详细信息；表syscurconfigs存储所有参数的当前 配置信息。
另外，Sybase ASE各个版本中都会备份曾经修改过的配置参数文件。在$SYBASE目录下servername .cfg是系统的配置参数文件，每次修改系统的参数内容时，ASE都会为servername .cfg做一次备份，备份文件名称用servername 加上依次递增的数字。servername .cfg中每个参数值后面的DEFAULT表示该参数没有被改变，仍然使用默认值。我们可以利用这些配置备份文件servername .001,servername .002 ... servername .040 等，来追踪参数的修改过程。如果某个参数修改后对性能反而没有提升，那么我们就可以利用之前备份的参数文件来恢复到修改之前的参数值。
参数文件比较重要的一点功能是：在master系统库崩溃的时候，可以利用cfg或者顺序参数备份文件来恢复之前的ASE的配置信息。
说了这么多，现在回到标题上的主题。如何知道ASE中哪些参数被修改过了呢？或者说：如何查看哪些参数使用了非缺省呢？
我想起来两种方法：
第一种：查看配置文件servername .cfg的所有参数，并找出取值不等于DEFAULT的所有参数。
第二种：利用sp_configure存储过程。
可能还有别的办法，比如用Sybase Central或者其它的第三方工具。我没有在这些工具上寻找查看的方法，您要是知道也可以共享一下方法。
先来看看系统存储过程sp_configure的参数内容。我利用联机的帮助信息来查看sp_configure的参数，详细的使用方法请查看sybase官方文档中的存储过程这一本。
1> sp_syntax "sp_configure"
2> go
Syntax Help
------------------------------------------------------------------------------
System Procedure
sp_configure - Displays or changes configuration parameters.
sp_configure [configname [, configvalue] | group_name
| non_unique_parameter_fragment]
["p|P|k|K|m|M|g|G"]]
Or,
sp_configure "configuration file", 0, {"write" | "read"
| "verify" | "restore" } "file_name"
(return status = 0)
（sybase ASE为15.0.3）
查看非缺省值参数的方法为：
1> sp_configure "display"
2> go
Parameter Name Default Memory Used Config Value Run Value Unit Type
------------------------------ ------------ ------------- ------------------ ----------- ---------------- -------
SQL batch capture 0 0 1 1 switch dynamic
allow updates to system tables 0 0 1 1 switch dynamic
deadlock pipe active 0 0 1 1 switch dynamic
deadlock pipe max messages 0 915 1024 1024 number dynamic
default character set id 2 0 1 1 id static
default database size 6 0 30 30 megabytes dynamic
enable encrypted columns 0 4138 1 1 switch dynamic
enable file access 0 0 1 1 switch dynamic
enable job scheduler 0 0 1 1 switch dynamic
enable monitoring 0 0 1 1 switch dynamic
enable semantic partitioning 0 0 1 1 switch dynamic
enable stmt cache monitoring 0 0 1 1 switch dynamic
errorlog pipe active 0 0 1 1 switch dynamic
errorlog pipe max messages 0 1087 1024 1024 number dynamic
event buffers per engine 100 #977 5000 5000 number static
max SQL text monitored 0 104 4096 4096 bytes static
max memory 39936 200000 100000 100000 memory pages(2k) dynamic
number of devices 10 #24 20 20 number dynamic
number of engines at startup 1 0 2 2 number static
number of histogram steps 20 0 40 40 number dynamic
object lockwait timing 0 0 1 1 switch dynamic
per object statistics active 0 0 1 1 switch dynamic
plan text pipe active 0 0 1 1 switch dynamic
plan text pipe max messages 0 382 1024 1024 number dynamic
print deadlock information 0 0 1 1 number dynamic
procedure cache size 7000 21170 10000 10000 memory pages(2k) dynamic
process wait events 0 0 1 1 switch dynamic
sql text pipe active 0 0 1 1 switch dynamic
sql text pipe max messages 0 1084 2000 2000 number dynamic
stack size 41984 #2813 43008 43008 bytes static
statement cache size 0 4 2 2 memory pages(2k) dynamic
statement pipe active 0 0 1 1 switch dynamic
statement pipe max messages 0 4182 20000 20000 number dynamic
statement statistics active 0 0 1 1 switch dynamic
wait event timing 0 0 1 1 switch dynamic
xp_cmdshell context 1 0 0 0 switch dynamic
呵呵，这个方法的确很方便啊。
最后补充一点，sp_configure过程的源代码有1400多行，可见这个过程要实现的功能还是挺多的。
通过查看该过程的源代码，找到实现sp_configure "display"这个功能的sql语句为：
select Parameter_Name = convert(char(30), name),
Default_Value = convert(varchar(11), defvalue),
Memory_Used = convert(varchar(11), c.comment),
Config_Value = isnull(b.value2, convert(char(32), b.value)),
Run_Value = isnull(c.value2, convert(char(32), c.value)),
Unit = convert(char(20), c.unit),
Type_ = convert(char(10), c.type)
from master.dbo.sysconfigures b,
master.dbo.syscurconfigs c
where
b.config = c.config
and (c.defvalue != isnull(b.value2, convert(char(32), b.value))
or
c.defvalue != isnull(c.value2, convert(char(32), c.value)))
and c.config != 114 /* Exclude option 'configuration file' */
and c.type != "read-only"
and display_level <= 10 --@user_displaylevel
order by Parameter_Name
go
查询出来的结果是一样的。利用这一堆sql语句查询出来的结果可以用作进一步的分析。
就介绍到这里吧!
————————————————————————————————-
—- 本文为andkylee个人原创，请在尊重作者劳动成果的前提下进行转载；
—- 转载务必注明原始出处 : http://www.dbainfo.net
—- 关键字：参数 非缺省 sp_configure
————————————————————————————————-
在新的电脑上安装完Vmware Server2.0.2后，导入以前的虚拟机文件。在左边的虚拟机列表中选中虚拟机后，右边的窗口空白一片， 无法查看虚拟机的配置信息也无法启动虚拟机。
在系统日志看，看到如下内容：
然后到目录：C:\Documents and Settings\All Users\Application Data\VMware\VMware Server下查看虚拟机的日志信息。依次查看hostd-0.log到hostd-9.log日志文件的内容。
发现如下比较重要的信息：
[2011-04-17 00:46:37.859 'Solo' 1264 info] VM inventory configuration: C:\Documents and Settings\All Users\Application Data\VMware\VMware Server\hostd\vmInventory.xml
[2011-04-17 00:46:37.875 'VmMisc' 1264 info] Max supported virtual machines: 512
[2011-04-17 00:46:37.890 'Vmsvc' 1264 info] Foundry_CreateEx failed: Error: (4000) The virtual machine cannot be found
[2011-04-17 00:46:37.890 'vm:I:\virtual-systems\RedHat5.4\Red Hat Enterprise Linux 5.vmx' 1264 info] Failed to load virtual machine.
[2011-04-17 00:46:37.890 'vm:I:\virtual-systems\RedHat5.4\Red Hat Enterprise Linux 5.vmx' 1264 info] Failed to load virtual machine. Marking as unavailable: vim.fault.NotFound
[2011-04-17 00:46:37.890 'vm:I:\virtual-systems\RedHat5.4\Red Hat Enterprise Linux 5.vmx' 1264 warning] Failed to find activation record, event user unknown.
[2011-04-17 00:46:37.890 'ha-eventmgr' 1264 info] Event 1 : Configuration file for on liuzhenfu in ha-datacenter cannot be found
[2011-04-17 00:46:37.906 'PropertyProvider' 1264 verbose] RecordOp ASSIGN: latestEvent, ha-eventmgr
[2011-04-17 00:46:37.906 'vm:I:\virtual-systems\RedHat5.4\Red Hat Enterprise Linux 5.vmx' 1264 info] State Transition (VM_STATE_INITIALIZING -> VM_STATE_INVALID_LOAD)
[2011-04-17 00:46:37.906 'PropertyProvider' 1264 verbose] RecordOp ASSIGN: disabledMethod, 112
[2011-04-17 00:46:37.906 'vm:I:\virtual-systems\RedHat5.4\Red Hat Enterprise Linux 5.vmx' 1264 info] Marking VirtualMachine invalid
[2011-04-17 00:46:37.906 'PropertyProvider' 1264 verbose] RecordOp ASSIGN: disabledMethod, 112
[2011-04-17 00:46:37.906 'PropertyProvider' 1264 verbose] RecordOp ASSIGN: runtime.connectionState, 112
[2011-04-17 00:46:37.906 'PropertyProvider' 1264 verbose] RecordOp ASSIGN: summary.runtime.connectionState, 112
[2011-04-17 00:46:37.906 'Vmsvc' 1264 info] Loaded virtual machine: I:\virtual-systems\RedHat5.4\Red Hat Enterprise Linux 5.vmx
[2011-04-17 00:46:37.906 'PropertyProvider' 1264 verbose] RecordOp ASSIGN: childConfiguration, ha-root-pool
[2011-04-17 00:46:37.906 'PropertyProvider' 1264 verbose] RecordOp ADD: vm["112"], ha-root-pool
[2011-04-17 00:46:37.906 'PropertyProvider' 2440 verbose] RecordOp ASSIGN: summary.runtime, ha-root-pool
[2011-04-17 00:46:38.203 'Libs' 1848 info] Reloading config state: D:\Virtual Machines\Node1\Windows Server 2003 Enterprise Edition.vmx
[2011-04-17 00:46:38.546 'Libs' 1848 info] VMHS: Transitioned vmx/execState/val to poweredOff
[2011-04-17 00:46:38.687 'Libs' 2128 info] CnxAuthdConnect: Returning false because CnxAuthdConnectPipe failed
[2011-04-17 00:46:38.687 'Libs' 2128 info] CnxConnectAuthd: Returning false because CnxAuthdConnect failed
[2011-04-17 00:46:38.687 'Libs' 2128 info] Cnx_Connect: Returning false because CnxConnectAuthd failed
[2011-04-17 00:46:38.687 'Libs' 2128 info] Cnx_Connect: Error message: Failed to read vmware-authd port number: Cannot connect to VMX: D:\Virtual Machines\Node1\Windows Server 2003 Enterprise Edition.vmx
[2011-04-17 00:46:38.687 'vm:D:\Virtual Machines\Node1\Windows Server 2003 Enterprise Edition.vmx' 1264 info] Upgrade is required for virtual machine, version: 4
[2011-04-17 00:46:38.843 'DiskLib' 1264 info] DISKLIB-SPARSECHK: [D:\Virtual Machines\Node1\Windows Server 2003 Enterprise Edition.vmdk] 16384 excess bytes at end of file
[2011-04-17 00:46:38.875 'DiskLib' 1264 info] DISKLIB-SPARSECHK: [D:\Virtual Machines\Node1\Windows Server 2003 Enterprise Edition.vmdk] GT Error (EE): GT[46][271] = 6814336 / 6814336
[2011-04-17 00:46:38.875 'DiskLib' 1264 info] DISKLIB-SPARSECHK: [D:\Virtual Machines\Node1\Windows Server 2003 Enterprise Edition.vmdk] Resolving GT[46][271] = 0
[2011-04-17 00:46:38.875 'DiskLib' 1264 info] DISKLIB-SPARSECHK: [D:\Virtual Machines\Node1\Windows Server 2003 Enterprise Edition.vmdk] GT Error (EE): GT[46][272] = 6814464 / 6814464
[2011-04-17 00:46:38.875 'DiskLib' 1264 info] DISKLIB-SPARSECHK: [D:\Virtual Machines\Node1\Windows Server 2003 Enterprise Edition.vmdk] Resolving GT[46][272] = 0
[2011-04-17 00:46:38.875 'DiskLib' 1264 info] DISKLIB-SPARSECHK: [D:\Virtual Machines\Node1\Windows Server 2003 Enterprise Edition.vmdk] GT Error (EE): GT[46][273] = 6814592 / 6814592
[2011-04-17 00:46:38.875 'DiskLib' 1264 info] DISKLIB-SPARSECHK: [D:\Virtual Machines\Node1\Windows Server 2003 Enterprise Edition.vmdk] Resolving GT[46][273] = 0
[2011-04-17 00:46:38.875 'DiskLib' 1264 info] DISKLIB-SPARSECHK: [D:\Virtual Machines\Node1\Windows Server 2003 Enterprise Edition.vmdk] GT Error (EE): GT[46][274] = 6814720 / 6814720
[2011-04-17 00:46:38.875 'DiskLib' 1264 info] DISKLIB-SPARSECHK: [D:\Virtual Machines\Node1\Windows Server 2003 Enterprise Edition.vmdk] Resolving GT[46][274] = 0
在另外一个日志文件中发现：
[2011-04-18 13:59:07.281 'Libs' 2204 info] CnxConnectAuthd: Returning false because CnxAuthdConnect failed
[2011-04-18 13:59:07.281 'Libs' 2204 info] Cnx_Connect: Returning false because CnxConnectAuthd failed
[2011-04-18 13:59:07.281 'Libs' 2204 info] Cnx_Connect: Error message: Failed to read vmware-authd port number: Cannot connect to VMX: C:\Virtual Machines\DNServer\Windows Server 2003 Enterprise Edition.vmx
[2011-04-18 13:59:07.281 'vm:C:\Virtual Machines\DNServer\Windows Server 2003 Enterprise Edition.vmx' 2900 info] Upgrade is required for virtual machine, version: 4
[2011-04-18 13:59:07.671 'PropertyProvider' 2900 verbose] RecordOp ASSIGN: summary, 64
[2011-04-18 13:59:07.671 'vm:C:\Virtual Machines\DNServer\Windows Server 2003 Enterprise Edition.vmx' 2900 info] State Transition (VM_STATE_INITIALIZING -> VM_STATE_OFF)
在网上寻找了很久，这篇博文解决VMWare Server 2.0 出现 Cannot Connect to VMX的问题的方法也不奏效。而且，vmware社区http://communities.vmware.com/index.jspa的很多帖子也没有给出有效的方法。这个Cannot connect to VMX问题也是个比较棘手的问题。
解决方法：原来我犯了一个低级的错误。曾经下载了vmware server 1.0.6 和 vmware server 2.0.2这2个版本的安装程序。因为以前的虚拟机环境不存在了，在新的电脑上安装的时候，就选择了vmware server 2.0.2。出现了上面的问题，曾经还重装了两次。 最后看了一篇自己曾经写过的文档《在VMServer1.0.6中配置SQL Server 2005故障转移群集》，才意识到自己选错vmware的版本了。多么低级的一个错误啊，白白折腾了一天多的时间。
曾经还出现过如下的无法登录的问题：The server was unable to process your log in request.Please check with your server administrator.
解决方法：设置使得登录用户对虚拟机文件所在的目录拥有权限。
演示一次sybase ASE15.0.3跨平台备份恢复的过程。
源数据库版本为：
1> select @@version
2> go
-----------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
----------------------------------------------------------------------------
Adaptive Server Enterprise/15.0.3/EBF 16738 ESD#2/P/NT (IX86)/Windows 2003/ase1
503/2708/32-bit/OPT/Mon Jul 27 20:19:56 2009
目的数据库版本为：
1> select @@version
2> go
-----------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
----------------------------------------------------------------------------
Adaptive Server Enterprise/15.0.3/EBF 16548 ESD#1/P/Sun_svr4/OS 5.8/ase1503/268
0/64-bit/FBO/Thu Mar 5 09:39:28 2009
跨平台恢复的详细过程如下：
(1). 检查欲备份的数据库testdb的完整性，利用checkdb和checkalloc
1> dbcc traceon(3604)
2> go
1> dbcc checkdb(testdb)
2> go
1> dbcc checkalloc(testdb)
2> go
(2). 将数据库testdb设置为单用户模式
1> use master
2> go
1> sp_dboption testdb,"single",true
2> go
(3). 清空testdb的日志，并设置检查点
1> dump tran testdb with no_log
2> go
1> checkpoint
2> go
(4). 将属于数据库testdb的脏页写到磁盘上面去，并发出检查点命令。 在发出sp_flushstats系统过程后要等待一段时间，保证数据库有足够的时间把页面信息刷新到磁盘上。
1> sp_flushstats
2> go
1> checkpoint
2> go
(5). 备份数据库testdb
1> dump database testdb to "d:\testdb-20100409.dmp"
2> go
(6). 利用ftp服务器将备份出来的testdb-20100409.dmp文件拷贝到solaris服务器上。
(7).用isql连接到目的数据库服务器上面，执行恢复命令
1> load database testdb from "/app/sybdata/testdb-20100409.dmp"
2> go
(8). 将目的数据库服务器中的testdb调整成在线。
1> online database testdb
2> go
(9). 进入testdb，执行sp_post_xpload系统存储过程来重建用户表的索引。
1> use testdb
2> go
1> sp_post_xpload
2> go
至此，跨平台备份恢复过程完成！
补充：以下为load database过程显示的所有的反馈信息。
1> sp_helpdb testdb
2> go
name db_size owner dbid created status
------ ------------- ----- ---- ------------ -------------
testdb 1300.0 MB sa 4 Apr 09, 2010 don't recover
(1 row affected)
device_fragments size usage
created free kbytes
------------------------------ ------------- --------------------
------------------------- ----------------
testdb_dat 1000.0 MB data only
Apr 9 2010 7:05PM 1016720
testdb_log 300.0 MB log only
Apr 9 2010 7:05PM not applicable
Msg 921, Level 14, State 2:
Server 'SYB_NFJD_TEST', Procedure 'sp_helpdb', Line 588:
Database 'testdb' has not been recovered yet - please wait and try again.
--------------------------------------------------------------
log only free kbytes = 0
(return status = 0)
1> load database testdb from "/app/sybdata/testdb-20100409.dmp"
2> go
Backup Server session id is: 19. 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 'scourt5100990F2E6' section number 1
mounted on disk file '/app/sybdata/testdb-20100409.dmp'
Backup Server: 4.188.1.1: Database testdb: 23940 kilobytes (1%) LOADED.
Backup Server: 4.188.1.1: Database testdb: 58630 kilobytes (4%) LOADED.
Backup Server: 4.188.1.1: Database testdb: 93320 kilobytes (7%) LOADED.
Backup Server: 4.188.1.1: Database testdb: 127118 kilobytes (9%) LOADED.
Backup Server: 4.188.1.1: Database testdb: 151180 kilobytes (11%) LOADED.
Backup Server: 4.188.1.1: Database testdb: 172814 kilobytes (12%) LOADED.
Backup Server: 4.188.1.1: Database testdb: 194448 kilobytes (14%) LOADED.
Backup Server: 4.188.1.1: Database testdb: 216082 kilobytes (16%) LOADED.
Backup Server: 4.188.1.1: Database testdb: 237716 kilobytes (17%) LOADED.
Backup Server: 4.188.1.1: Database testdb: 259350 kilobytes (19%) LOADED.
Backup Server: 4.188.1.1: Database testdb: 280984 kilobytes (21%) LOADED.
Backup Server: 4.188.1.1: Database testdb: 302618 kilobytes (22%) LOADED.
Backup Server: 4.188.1.1: Database testdb: 324252 kilobytes (24%) LOADED.
Backup Server: 4.188.1.1: Database testdb: 345886 kilobytes (25%) LOADED.
Backup Server: 4.188.1.1: Database testdb: 367520 kilobytes (27%) LOADED.
Backup Server: 4.188.1.1: Database testdb: 389154 kilobytes (29%) LOADED.
Backup Server: 4.188.1.1: Database testdb: 409636 kilobytes (30%) LOADED.
Backup Server: 4.188.1.1: Database testdb: 409646 kilobytes (100%) LOADED.
Backup Server: 3.42.1.1: LOAD is complete (database testdb).
Started cross-platform conversion for database testdb.
Started cross-platform conversion for system objects.
Cross-platform conversion for database testdb: 672 pages completed.
Completed cross-platform conversion for system objects.
Started cross-platform conversion for user objects.
Cross-platform conversion for database testdb: 10073 pages completed.
Cross-platform conversion for database testdb: 20273 pages completed.
Cross-platform conversion for database testdb: 30458 pages completed.
Cross-platform conversion for database testdb: 40604 pages completed.
Cross-platform conversion for database testdb: 50796 pages completed.
Cross-platform conversion for database testdb: 60924 pages completed.
Cross-platform conversion for database testdb: 71117 pages completed.
Cross-platform conversion for database testdb: 81317 pages completed.
Cross-platform conversion for database testdb: 91517 pages completed.
Cross-platform conversion for database testdb: 101717 pages completed.
Completed cross-platform conversion for user objects.
Started cross-platform conversion for log records.
Cross-platform conversion for database testdb: 11119 pages completed.
Completed cross-platform conversion for log records.
Completed cross-platform conversion for database testdb.
Caution: You have set up this database to include space on disk 5 for both data
and the transaction log. This can make recovery impossible if that disk fails.
All dumped pages have been loaded. ASE is now clearing pages above page 102400,
which were not present in the database just loaded.
ASE has finished clearing database pages.
Started estimating recovery log boundaries for database 'testdb'.
Database 'testdb', checkpoint=(79128, 0), first=(79128, 0), last=(79128, 0).
Completed estimating recovery log boundaries for database 'testdb'.
Started ANALYSIS pass for database 'testdb'.
Completed ANALYSIS pass for database 'testdb'.
Started REDO pass for database 'testdb'. The total number of log records to
process is 1.
Completed REDO pass for database 'testdb'.
Use the ONLINE DATABASE command to bring this database online; ASE will not
bring it online automatically.
1> online database testdb
2> go
Started estimating recovery log boundaries for database 'testdb'.
Database 'testdb', checkpoint=(79128, 0), first=(79128, 0), last=(79128, 0).
Completed estimating recovery log boundaries for database 'testdb'.
Started ANALYSIS pass for database 'testdb'.
Completed ANALYSIS pass for database 'testdb'.
Recovery of database 'testdb' will undo incomplete nested top actions.
Checking table 'sysstatistics' (object ID 24): Logical page size is 4096 bytes.
Checking partition 'sysstatistics_24' (partition ID 24) of table
'sysstatistics'. The logical page size of this table is 4096 bytes.
The indexes for 'sysstatistics' are already correct. They will not be rebuilt.
(1 row affected)
(1 row affected)
(485 rows affected)
(1 row affected)
(57 rows affected)
(1 row affected)
(16 rows affected)
(1 row affected)
(3 rows affected)
(1 row affected)
(0 rows affected)
(915 rows affected)
Checking table 'sysobjects' (object ID 1): Logical page size is 4096 bytes.
Checking partition 'sysobjects_1' (partition ID 1) of table 'sysobjects'. The
logical page size of this table is 4096 bytes.
The indexes for 'sysobjects' are already correct. They will not be rebuilt.
Checking table 'sysindexes' (object ID 2): Logical page size is 4096 bytes.
Checking partition 'sysindexes_2' (partition ID 2) of table 'sysindexes'. The
logical page size of this table is 4096 bytes.
The indexes for 'sysindexes' are already correct. They will not be rebuilt.
Checking table 'syscolumns' (object ID 3): Logical page size is 4096 bytes.
Checking partition 'syscolumns_3' (partition ID 3) of table 'syscolumns'. The
logical page size of this table is 4096 bytes.
The indexes for 'syscolumns' are already correct. They will not be rebuilt.
Checking table 'systypes' (object ID 4): Logical page size is 4096 bytes.
Checking partition 'systypes_4' (partition ID 4) of table 'systypes'. The
logical page size of this table is 4096 bytes.
The indexes for 'systypes' are already correct. They will not be rebuilt.
Checking table 'sysprocedures' (object ID 5): Logical page size is 4096 bytes.
Checking partition 'sysprocedures_5' (partition ID 5) of table 'sysprocedures'.
The logical page size of this table is 4096 bytes.
The indexes for 'sysprocedures' are already correct. They will not be rebuilt.
Checking table 'syscomments' (object ID 6): Logical page size is 4096 bytes.
Checking partition 'syscomments_6' (partition ID 6) of table 'syscomments'. The
logical page size of this table is 4096 bytes.
The indexes for 'syscomments' are already correct. They will not be rebuilt.
Checking table 'syssegments' (object ID 7): Logical page size is 4096 bytes.
Checking partition 'syssegments_7' (partition ID 7) of table 'syssegments'. The
logical page size of this table is 4096 bytes.
The table 'syssegments' has no indexes.
Checking table 'syslogs' (object ID 8): Logical page size is 4096 bytes.
Checking partition 'syslogs_8' (partition IDof table 'syslogs'. The logical
page size of this table is 4096 bytes.
*** NOTICE: Space used on the log segment is 101 pages (0.39 MB), 0.39%.
*** NOTICE: Space reserved on the log segment is 0 pages (0.00 MB), 0.00%.
*** NOTICE: Space free on the log segment is 25499 pages (99.61 MB), 99.61%.
The table 'syslogs' has no indexes.
Checking table 'sysprotects' (object ID 9): Logical page size is 4096 bytes.
Checking partition 'sysprotects_9' (partition ID 9) of table 'sysprotects'. The
logical page size of this table is 4096 bytes.
The indexes for 'sysprotects' are already correct. They will not be rebuilt.
Checking table 'sysusers' (object ID 10): Logical page size is 4096 bytes.
Checking partition 'sysusers_10' (partition ID 10) of table 'sysusers'. The
logical page size of this table is 4096 bytes.
The indexes for 'sysusers' are already correct. They will not be rebuilt.
Checking table 'sysalternates' (object ID 11): Logical page size is 4096 bytes.
Checking partition 'sysalternates_11' (partition ID 11) of table
'sysalternates'. The logical page size of this table is 4096 bytes.
The indexes for 'sysalternates' are already correct. They will not be rebuilt.
Checking table 'sysdepends' (object ID 12): Logical page size is 4096 bytes.
Checking partition 'sysdepends_12' (partition ID 12) of table 'sysdepends'. The
logical page size of this table is 4096 bytes.
The indexes for 'sysdepends' are already correct. They will not be rebuilt.
Checking table 'syskeys' (object ID 13): Logical page size is 4096 bytes.
Checking partition 'syskeys_13' (partition ID 13) of table 'syskeys'. The
logical page size of this table is 4096 bytes.
The indexes for 'syskeys' are already correct. They will not be rebuilt.
Checking table 'sysgams' (object ID 14): Logical page size is 4096 bytes.
The total number of data pages in this table is 8.
The indexes for 'sysgams' are already correct. They will not be rebuilt.
Checking table 'sysusermessages' (object ID 15): Logical page size is 4096
bytes.
Checking partition 'csysusermessages_15' (partition ID 15) of table
'sysusermessages'. The logical page size of this table is 4096 bytes.
The indexes for 'sysusermessages' are already correct. They will not be
rebuilt.
Checking table 'sysreferences' (object ID 16): Logical page size is 4096 bytes.
Checking partition 'sysreferences_16' (partition ID 16) of table
'sysreferences'. The logical page size of this table is 4096 bytes.
The indexes for 'sysreferences' are already correct. They will not be rebuilt.
Checking table 'sysconstraints' (object ID 17): Logical page size is 4096
bytes.
Checking partition 'sysconstraints_17' (partition ID 17) of table
'sysconstraints'. The logical page size of this table is 4096 bytes.
The indexes for 'sysconstraints' are already correct. They will not be
rebuilt.
Checking table 'systhresholds' (object ID 18): Logical page size is 4096 bytes.
Checking partition 'systhresholds_18' (partition ID 18) of table
'systhresholds'. The logical page size of this table is 4096 bytes.
The table 'systhresholds' has no indexes.
Checking table 'sysroles' (object ID 19): Logical page size is 4096 bytes.
Checking partition 'sysroles_19' (partition ID 19) of table 'sysroles'. The
logical page size of this table is 4096 bytes.
The indexes for 'sysroles' are already correct. They will not be rebuilt.
Checking table 'sysattributes' (object ID 21): Logical page size is 4096 bytes.
Checking partition 'sysattributes_21' (partition ID 21) of table
'sysattributes'. The logical page size of this table is 4096 bytes.
The indexes for 'sysattributes' are already correct. They will not be rebuilt.
Checking table 'sysslices' (object ID 22): Logical page size is 4096 bytes.
Checking partition 'csysslices_22' (partition ID 22) of table 'sysslices'. The
logical page size of this table is 4096 bytes.
The indexes for 'sysslices' are already correct. They will not be rebuilt.
Checking table 'systabstats' (object ID 23): Logical page size is 4096 bytes.
Checking partition 'systabstats_23' (partition ID 23) of table 'systabstats'.
The logical page size of this table is 4096 bytes.
The indexes for 'systabstats' are already correct. They will not be rebuilt.
Checking table 'sysstatistics' (object ID 24): Logical page size is 4096 bytes.
Checking partition 'sysstatistics_24' (partition ID 24) of table
'sysstatistics'. The logical page size of this table is 4096 bytes.
The indexes for 'sysstatistics' are already correct. They will not be rebuilt.
Checking table 'sysxtypes' (object ID 25): Logical page size is 4096 bytes.
Checking partition 'sysxtypes_25' (partition ID 25) of table 'sysxtypes'. The
logical page size of this table is 4096 bytes.
The indexes for 'sysxtypes' are already correct. They will not be rebuilt.
Checking table 'sysjars' (object ID 26): Logical page size is 4096 bytes.
Checking partition 'sysjars_26' (partition ID 26) of table 'sysjars'. The
logical page size of this table is 4096 bytes.
The indexes for 'sysjars' are already correct. They will not be rebuilt.
Checking table 'sysqueryplans' (object ID 27): Logical page size is 4096 bytes.
Checking partition 'sysqueryplans_27' (partition ID 27) of table
'sysqueryplans'. The logical page size of this table is 4096 bytes.
The indexes for 'sysqueryplans' are already correct. They will not be rebuilt.
Checking table 'syspartitions' (object ID 28): Logical page size is 4096 bytes.
Checking partition 'syspartitions_28' (partition ID 28) of table
'syspartitions'. The logical page size of this table is 4096 bytes.
The indexes for 'syspartitions' are already correct. They will not be rebuilt.
Checking table 'syspartitionkeys' (object ID 29): Logical page size is 4096
bytes.
Checking partition 'syspartitionkeys_29' (partition ID 29) of table
'syspartitionkeys'. The logical page size of this table is 4096 bytes.
The indexes for 'syspartitionkeys' are already correct. They will not be
rebuilt.
Checking table 'sysencryptkeys' (object ID 98): Logical page size is 4096
bytes.
Checking partition 'sysencryptkeys_98' (partition ID 98) of table
'sysencryptkeys'. The logical page size of this table is 4096 bytes.
Msg 9989, Level 16, State 1:
Server 'SYB_NFJD_TEST', Line 1:
The number of deleted rows (65535) in the page header of DOL datapage 633,
partition ID 98 is incorrect. This value cannot be negative.
One or more indexes are corrupt. They will be rebuilt.
There are 0.000000 rows in 1 pages for object 'sysencryptkeys'.
Clustered index 'csysencryptkeys' (ID = 2) successfully restored for object
'sysencryptkeys' in 'testdb' database.
(50 rows affected)
(0 rows affected)
(0 rows affected)
WARNING: One or more indexes on user tables may have been marked as 'suspect'
making these indexes unusable. Use the sp_post_xpload stored procedure to check
and rebuild these indexes.
Database 'testdb' is now online.
1> use testdb
2> go
1> sp_indsuspect
2> go
Suspect indexes in database testdb:
Own.Tab.Ind (Obj_ID, Ind_ID)
-----------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
---------------------------------------------------------------
testuser.S_OFFICE.S_OFFICE_IND1_x (395145422, 2)
dbo.office.unique_indx_officeid (439669583, 2)
testuser.S_USER.S_USER_IND1_x (699146505, 2)
testuser.S_USER.S_USER_IND2_x (699146505, 3)
dbo.devices.abc (704002508, 2)
dbo.databases.abc (736002622, 2)
dbo.usages.abcd (768002736, 2)
testuser.LAN_PAGENO_RANGE.aaaaa (788194827, 2)
dbo.pbcattbl.pbcattbl_idx (800002850, 2)
dbo.pbcatcol.pbcatcol_idx (848003021, 2)
dbo.pbcatfmt.pbcatfmt_idx (896003192, 2)
testuser.LAN_SYS_syscolumns.LAN_SYS_syscolumns (1012195625, 2)
testuser.WF_EFORM.PK_WF_EFORM_x (1019147645, 2)
testuser.LAN_SYS_sysobjects.LAN_SYS_sysobjects (1060195796, 2)
testuser.ARCHIVES.PK_ARCHIVES_x (1084527866, 2)
/**************************************************
省略一部分表的索引............
**************************************************/
testuser.LAN_SYS_sysindexes.LAN_SYS_sysindexes (1108195967, 2)
dbo.PartitionTestTable.idx_PartitionTestTable_id (1223672376, 2)
dbo.PartitionTestTable.idx_PartitionTestTable_name (1223672376, 3)
dbo.test3.idx_test3_id (1351672832, 2)
dbo.test4.idx_test4_id (1399673003, 2)
testuser.LAN_sysdevices.namesss (1828198532, 2)
testuser.LAN_sysusages.pk_lan_sysusages (1892198760, 2)
testuser.LAN_sysdatabases.pk_lan_sysdatabases (1940198931, 2)
dbo.lzftest.idx_lzftest_id (1975675055, 2)
testuser.Usertype_relation.Idx_Usertype_relation (2020199216, 2)
Msg 17870, Level 16, State 1:
Server 'SYB_NFJD_TEST', Procedure 'sp_autoformat', Line 267:
Table '#temp1' does not exist in database 'tempdb'.
(return status = 0)
1>
2> sp_post_xpload
3> go
sp_post_xpload: Processing table AIX_PAGENOS (1/419)
sp_post_xpload: Processing table AIX_PAGENO_RANGE (2/419)
sp_post_xpload: Processing table AIX_SYS_syscolumns (3/419)
sp_post_xpload: Processing table AIX_SYS_sysindexes (4/419)
sp_post_xpload: Processing table AIX_SYS_sysobjects (5/419)
sp_post_xpload: Processing table AJDACG (6/419)
sp_post_xpload: Processing table AJDAJY (7/419)
sp_post_xpload: Processing table AJGDB (8/419)
Checking table 'AJGDB' (object ID 796526840): Logical page size is 4096 bytes.
Checking partition 'AJGDB_796526840' (partition ID 796526840) of table 'AJGDB'.
The logical page size of this table is 4096 bytes.
One or more indexes are corrupt. They will be rebuilt.
There are 127204.000000 rows in 10481 pages for object 'AJGDB'.
Non-clustered index 'PK_AJGDB_x' (ID = 2) successfully restored for object
'AJGDB' in 'testdb' database.
There are 127204.000000 rows in 10481 pages for object 'AJGDB'.
Non-clustered index 'INDX_AJGDB_SN_x' (ID = 3) successfully restored for object
'AJGDB' in 'testdb' database.
There are 127204.000000 rows in 10481 pages for object 'AJGDB'.
Non-clustered index 'PK_AJGDB_SN_x' (ID = 4) successfully restored for object
'AJGDB' in 'testdb' database.
sp_post_xpload: Processing table ARCHIVES (15/419)
Checking table 'ARCHIVES' (object ID 1084527866): Logical page size is 4096
bytes.
Checking partition 'ARCHIVES_1084527866' (partition ID 1084527866) of table
'ARCHIVES'. The logical page size of this table is 4096 bytes.
The indexes for 'ARCHIVES' are already correct. They will not be rebuilt.
sp_post_xpload: Processing table CASES_SXGG (118/419)
sp_post_xpload: Processing table CASES_TB (119/419)
sp_post_xpload: Processing table CASES_TJGL (120/419)
sp_post_xpload: Processing table CASES_WS (121/419)
sp_post_xpload: Processing table CASES_XS1 (122/419)
sp_post_xpload: Processing table CASES_XS2 (123/419)
sp_post_xpload: Processing table CASES_XSBHR (124/419)
sp_post_xpload: Processing table CASES_XSFDMSSSZX (125/419)
sp_post_xpload: Processing table CASES_XZ1 (126/419)
sp_post_xpload: Processing table CASES_XZBG (127/419)
sp_post_xpload: Processing table CASES_YS (128/419)
sp_post_xpload: Processing table CASES_ZBGZB (129/419)
sp_post_xpload: Processing table CASES_ZHENGR (130/419)
sp_post_xpload: Processing table CASES_ZHESJG (131/419)
sp_post_xpload: Processing table CASES_ZHESJGYJ (132/419)
sp_post_xpload: Processing table CASES_ZJ (133/419)
sp_post_xpload: Processing table CASES_ZJLT (134/419)
sp_post_xpload: Processing table CASES_ZX1 (135/419)
Checking table 'DATACODE' (object ID 1189576245): Logical page size is 4096
bytes.
Checking partition 'DATACODE_1189576245' (partition ID 1189576245) of table
'DATACODE'. The logical page size of this table is 4096 bytes.
The indexes for 'DATACODE' are already correct. They will not be rebuilt.
sp_post_xpload: Processing table DB_10 (146/419)
sp_post_xpload: Processing table DB_11 (147/419)
sp_post_xpload: Processing table DB_5 (148/419)
sp_post_xpload: Processing table DB_6 (149/419)
sp_post_xpload: Processing table DB_7 (150/419)
sp_post_xpload: Processing table DB_8 (151/419)
sp_post_xpload: Processing table DB_9 (152/419)
sp_post_xpload: Processing table DGKJZMX (153/419)
sp_post_xpload: Processing table DSR (154/419)
.........................
sp_post_xpload: Processing table LAN_PAGENOS (243/419)
sp_post_xpload: Processing table LAN_PAGENOS_DB10 (244/419)
sp_post_xpload: Processing table LAN_PAGENOS_DB11 (245/419)
sp_post_xpload: Processing table LAN_PAGENOS_DB5 (246/419)
sp_post_xpload: Processing table LAN_PAGENOS_DB6 (247/419)
sp_post_xpload: Processing table LAN_PAGENOS_DB7 (248/419)
sp_post_xpload: Processing table LAN_PAGENOS_DB8 (249/419)
sp_post_xpload: Processing table LAN_PAGENOS_DB9 (250/419)
sp_post_xpload: Processing table LAN_PAGENO_RANGE (251/419)
Checking table 'LAN_PAGENO_RANGE' (object ID 788194827): Logical page size is
4096 bytes.
Checking partition 'LAN_PAGENO_RANGE_788194827' (partition ID 788194827) of
table 'LAN_PAGENO_RANGE'. The logical page size of this table is 4096 bytes.
The indexes for 'LAN_PAGENO_RANGE' are already correct. They will not be
rebuilt.
sp_post_xpload: Processing table LAN_PAGENO_RANGE2 (252/419)
sp_post_xpload: Processing table LAN_PAGENO_RANGE3 (253/419)
sp_post_xpload: Processing table LAN_SYS_syscolumns (254/419)
Checking table 'LAN_SYS_syscolumns' (object ID 1012195625): Logical page size is
4096 bytes.
Checking partition 'LAN_SYS_syscolumns_1012195625' (partition ID 1012195625) of
table 'LAN_SYS_syscolumns'. The logical page size of this table is 4096 bytes.
The indexes for 'LAN_SYS_syscolumns' are already correct. They will not be
rebuilt.
sp_post_xpload: Processing table LAN_SYS_sysindexes (255/419)
Checking table 'LAN_SYS_sysindexes' (object ID 1108195967): Logical page size is
4096 bytes.
Checking partition 'LAN_SYS_sysindexes_1108195967' (partition ID 1108195967) of
table 'LAN_SYS_sysindexes'. The logical page size of this table is 4096 bytes.
The indexes for 'LAN_SYS_sysindexes' are already correct. They will not be
rebuilt.
sp_post_xpload: Processing table LAN_SYS_sysobjects (256/419)
Checking table 'LAN_SYS_sysobjects' (object ID 1060195796): Logical page size is
4096 bytes.
Checking partition 'LAN_SYS_sysobjects_1060195796' (partition ID 1060195796) of
table 'LAN_SYS_sysobjects'. The logical page size of this table is 4096 bytes.
The indexes for 'LAN_SYS_sysobjects' are already correct. They will not be
rebuilt.
sp_post_xpload: Processing table LAN_sysdatabases (257/419)
Checking table 'LAN_sysdatabases' (object ID 1940198931): Logical page size is
4096 bytes.
Checking partition 'LAN_sysdatabases_1940198931' (partition ID 1940198931) of
table 'LAN_sysdatabases'. The logical page size of this table is 4096 bytes.
The indexes for 'LAN_sysdatabases' are already correct. They will not be
rebuilt.
sp_post_xpload: Processing table LAN_sysdevices (258/419)
Checking table 'LAN_sysdevices' (object ID 1828198532): Logical page size is
4096 bytes.
Checking partition 'LAN_sysdevices_1828198532' (partition ID 1828198532) of
table 'LAN_sysdevices'. The logical page size of this table is 4096 bytes.
The indexes for 'LAN_sysdevices' are already correct. They will not be
rebuilt.
sp_post_xpload: Processing table LAN_sysusages (259/419)
Checking table 'LAN_sysusages' (object ID 1892198760): Logical page size is 4096
bytes.
Checking partition 'LAN_sysusages_1892198760' (partition ID 1892198760) of table
'LAN_sysusages'. The logical page size of this table is 4096 bytes.
The indexes for 'LAN_sysusages' are already correct. They will not be rebuilt.
sp_post_xpload: Processing table PartitionTestTable (271/419)
Checking table 'PartitionTestTable' (object ID 1223672376): Logical page size is
4096 bytes.
Checking partition 'zhang' (partition ID 1239672433) of table
'PartitionTestTable'. The logical page size of this table is 4096 bytes.
Checking partition 'liu' (partition ID 1255672490) of table
'PartitionTestTable'. The logical page size of this table is 4096 bytes.
Checking partition 'wang' (partition ID 1271672547) of table
'PartitionTestTable'. The logical page size of this table is 4096 bytes.
Checking partition 'li' (partition ID 1287672604) of table 'PartitionTestTable'.
The logical page size of this table is 4096 bytes.
One or more indexes are corrupt. They will be rebuilt.
There are 262144.000000 rows in 1082 pages for object 'PartitionTestTable'.
Non-clustered index 'idx_PartitionTestTable_id' (ID = 2) successfully restored
for object 'PartitionTestTable' in 'testdb' database.
There are 262144.000000 rows in 1082 pages for object 'PartitionTestTable'.
Non-clustered index 'idx_PartitionTestTable_name' (ID = 3) successfully restored
for object 'PartitionTestTable' in 'testdb' database.
sp_post_xpload: Processing table S_OFFICE (346/419)
Checking table 'S_OFFICE' (object ID 395145422): Logical page size is 4096
bytes.
Checking partition 'PK_S_OFFICE_x_395145422' (partition ID 395145422) of table
'S_OFFICE'. The logical page size of this table is 4096 bytes.
The indexes for 'S_OFFICE' are already correct. They will not be rebuilt.
sp_post_xpload: Processing table Usertype_relation (364/419)
Checking table 'Usertype_relation' (object ID 2020199216): Logical page size is
4096 bytes.
Checking partition 'Usertype_relation_2020199216' (partition ID 2020199216) of
table 'Usertype_relation'. The logical page size of this table is 4096 bytes.
The indexes for 'Usertype_relation' are already correct. They will not be
rebuilt.
sp_post_xpload: Processing table databases (395/419)
Checking table 'databases' (object ID 736002622): Logical page size is 4096
bytes.
Checking partition 'databases_736002622' (partition ID 736002622) of table
'databases'. The logical page size of this table is 4096 bytes.
The indexes for 'databases' are already correct. They will not be rebuilt.
sp_post_xpload: Processing table devices (396/419)
Checking table 'devices' (object ID 704002508): Logical page size is 4096
bytes.
Checking partition 'devices_704002508' (partition ID 704002508) of table
'devices'. The logical page size of this table is 4096 bytes.
The indexes for 'devices' are already correct. They will not be rebuilt.
sp_post_xpload: Processing table employee (397/419)
sp_post_xpload: Processing table employee_bak (398/419)
sp_post_xpload: Processing table kqjcb (399/419)
sp_post_xpload: Processing table lzftest (400/419)
Checking table 'lzftest' (object ID 1975675055): Logical page size is 4096
bytes.
/**************************************************
省略一部分表的索引重建过程............
**************************************************/
Checking partition 'li' (partition ID 1991675112) of table 'lzftest'. The
logical page size of this table is 4096 bytes.
Checking partition 'liu' (partition ID 2007675169) of table 'lzftest'. The
logical page size of this table is 4096 bytes.
Checking partition 'wang' (partition ID 2023675226) of table 'lzftest'. The
logical page size of this table is 4096 bytes.
Checking partition 'zhang' (partition ID 2039675283) of table 'lzftest'. The
logical page size of this table is 4096 bytes.
Checking partition 'zhu' (partition ID 2071675397) of table 'lzftest'. The
logical page size of this table is 4096 bytes.
The indexes for 'lzftest' are already correct. They will not be rebuilt.
sp_post_xpload: Processing table office (401/419)
Checking table 'office' (object ID 439669583): Logical page size is 4096 bytes.
Checking partition 'office_439669583' (partition ID 439669583) of table
'office'. The logical page size of this table is 4096 bytes.
The indexes for 'office' are already correct. They will not be rebuilt.
sp_post_xpload: Processing table office_bak (402/419)
sp_post_xpload: Processing table pbcatcol (403/419)
Checking table 'pbcatcol' (object ID 848003021): Logical page size is 4096
bytes.
Checking partition 'pbcatcol_848003021' (partition ID 848003021) of table
'pbcatcol'. The logical page size of this table is 4096 bytes.
The indexes for 'pbcatcol' are already correct. They will not be rebuilt.
sp_post_xpload: Processing table pbcatedt (404/419)
sp_post_xpload: Processing table pbcatfmt (405/419)
Checking table 'pbcatfmt' (object ID 896003192): Logical page size is 4096
bytes.
Checking partition 'pbcatfmt_896003192' (partition ID 896003192) of table
'pbcatfmt'. The logical page size of this table is 4096 bytes.
The indexes for 'pbcatfmt' are already correct. They will not be rebuilt.
sp_post_xpload: Processing table pbcattbl (406/419)
Checking table 'pbcattbl' (object ID 800002850): Logical page size is 4096
bytes.
Checking partition 'pbcattbl_800002850' (partition ID 800002850) of table
'pbcattbl'. The logical page size of this table is 4096 bytes.
The indexes for 'pbcattbl' are already correct. They will not be rebuilt.
sp_post_xpload: Processing table pbcatvld (407/419)
sp_post_xpload: Processing table syscolumn (408/419)
sp_post_xpload: Processing table sysobjects1 (409/419)
sp_post_xpload: Processing table temp2 (410/419)
sp_post_xpload: Processing table temptbl (411/419)
sp_post_xpload: Processing table test (412/419)
sp_post_xpload: Processing table test (413/419)
sp_post_xpload: Processing table test2 (414/419)
sp_post_xpload: Processing table test3 (415/419)
Checking table 'test3' (object ID 1351672832): Logical page size is 4096 bytes.
Checking partition 'pk_test3_id_1351672832' (partition ID 1351672832) of table
'test3'. The logical page size of this table is 4096 bytes.
The indexes for 'test3' are already correct. They will not be rebuilt.
sp_post_xpload: Processing table test4 (416/419)
Checking table 'test4' (object ID 1399673003): Logical page size is 4096 bytes.
Checking partition 'pk_test4_id_1399673003' (partition ID 1399673003) of table
'test4'. The logical page size of this table is 4096 bytes.
The indexes for 'test4' are already correct. They will not be rebuilt.
sp_post_xpload: Processing table testtimestamp (417/419)
sp_post_xpload: Processing table testvarchar (418/419)
sp_post_xpload: Processing table usages (419/419)
Checking table 'usages' (object ID 768002736): Logical page size is 4096 bytes.
Checking partition 'usages_768002736' (partition ID 768002736) of table
'usages'. The logical page size of this table is 4096 bytes.
The indexes for 'usages' are already correct. They will not be rebuilt.
(return status = 0)
1> sp_indsuspect
2> go
There are no suspect indexes in database testdb.
(return status = 0)
1>
————————————————————————————————-
—- 本文为andkylee个人原创，请在尊重作者劳动成果的前提下进行转载；
—- 转载务必注明原始出处 : http://www.dbainfo.net
—- 关键字： ASE 15.0.3 跨平台 备份 恢复 Solaris windows
————————————————————————————————-