Sybase数据库技术，数据库恢复专家

存档

文章标签 ‘ASE’,文章数:63

ASE12.x与15.x中的字符串替换功能的实现

在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
————————————————————————————————-

阅读全文…

标签: ASE, str_replace, stuff

ASE中的命令行实用工具isql的几个参数的使用方法

在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
————————————————————————————————-

阅读全文…

标签: ASE, isql

如何配置监控表MonTables来实现ASE的监控功能

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
————————————————————————————————-

阅读全文…

标签: ASE, MDA, 监控

Sybase15.0.3跨平台恢复过程

演示一次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 ID of 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
————————————————————————————————-

阅读全文…

标签: ASE, 恢复, 跨平台

Sybase发行版本（Release）术语

EBF

官方软件发行版本。EBF号唯一性地标志了在某个OS平台上的发行版本。

ESD

官方软件发行版本，它解决了当前发行版本中的一些Bug。ESD（ESD #）通常是跨平台的，并且不包含新的特性或功能。ESD是征对某一主版本（major version）的累积软件包，它必须在GA发行版本上安装。

GA（General Availability）是一个SYBASE软件版本全新的、初始听发行版本。除Bug修复之外，它通常包含一些新特性。

IR（Interim Release－中间版本）包含了在此之前的所有ESD发行版本的Bug修复，以及其它Bug修复。IR发行版本必须基于与之对应的GA发行版本的基础上安装。比如，要安装12.5.0.3，就必须先安装12.5GA（ESD）。

MR（Maintenance Release－维护版本）是GA发行版本的完整替换包。由于它是完整的发行包，因此可以直接安装。通常此种类型的发行版本包含一些新特性以及Bug修复。

阅读全文…

标签: ASE, EBF, 补丁

sybase中的聚簇索引与count（*）的关系

在sybase表上建立聚集索引可以提高键列的检索速度。这是索引的主要功能所在。

可是，聚集索引对于统计表上的行数count（*）有没有改善呢？答案是否定的。

请看我下面的测试代码！

建立一张临时表test3

1	create table test3(id int not null,name varchar(30) null)

向表中插入测试数据

insert into test3
select 1,'liu'
go
insert into test3
select 2,'zhang'
go
insert into test3
select 3,'wang'
go
insert into test3
select 4,'li'
go
循环插入
insert into test4
select count(*)+id,name from test4
go 18
1> select count(*) from  test4
2> go
-----------
524288
(1 row affected)
循环插入了524288条记录!

打开查询计划和统计查询计划时间的选项

set showplan on
go
set statistics time on
go

表上没有加任何索引的情况下。

select count(*) from test4 的查询计划为:

1> select count(*) from test4
2> go
QUERY PLAN FOR STATEMENT 1 (at line 1).
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
|   |   |  test4
|   |   |  Table Scan.
|   |   |  Forward Scan.
|   |   |  Positioning at start of table.
|   |   |  Using I/O Size 32 Kbytes for data pages.
|   |   |  With MRU Buffer Replacement Strategy for data pages.
Parse and Compile Time 0.
Adaptive Server cpu time: 0 ms.
-----------
524288
Execution Time 1.
Adaptive Server cpu time: 100 ms.  Adaptive Server elapsed time: 156 ms.
(1 row affected)

select count(1) from test4 的查询计划为:

1> select count(1) from test4
2> go
QUERY PLAN FOR STATEMENT 1 (at line 1).
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
|   |   |  test4
|   |   |  Table Scan.
|   |   |  Forward Scan.
|   |   |  Positioning at start of table.
|   |   |  Using I/O Size 32 Kbytes for data pages.
|   |   |  With MRU Buffer Replacement Strategy for data pages.
Parse and Compile Time 0.
Adaptive Server cpu time: 0 ms.
-----------
524288
Execution Time 1.
Adaptive Server cpu time: 100 ms.  Adaptive Server elapsed time: 220 ms.

可以看出，count（*）和count（1）的执行计划是相同的。都执行了表扫描。

由于表上没有任何索引可供使用，select count(id) 和 select count(name) 都是执行了表扫描。

1> select count(id) from test4
2> go
QUERY PLAN FOR STATEMENT 1 (at line 1).
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
|   |   |  test4
|   |   |  Table Scan.
|   |   |  Forward Scan.
|   |   |  Positioning at start of table.
|   |   |  Using I/O Size 32 Kbytes for data pages.
|   |   |  With MRU Buffer Replacement Strategy for data pages.
Parse and Compile Time 0.
Adaptive Server cpu time: 0 ms.
-----------
524288
Execution Time 1.
Adaptive Server cpu time: 100 ms.  Adaptive Server elapsed time: 140 ms.
(1 row affected)
1> select count(name) from test4
2> go
QUERY PLAN FOR STATEMENT 1 (at line 1).
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
|   |   |  test4
|   |   |  Table Scan.
|   |   |  Forward Scan.
|   |   |  Positioning at start of table.
|   |   |  Using I/O Size 32 Kbytes for data pages.
|   |   |  With MRU Buffer Replacement Strategy for data pages.
Parse and Compile Time 0.
Adaptive Server cpu time: 0 ms.
-----------
524288
Execution Time 1.
Adaptive Server cpu time: 100 ms.  Adaptive Server elapsed time: 236 ms.
(1 row affected)
1>

下面考虑加入主键（聚集索引）pk_test4_id

1 2	alter table test4 add constraint pk_test4_id primary key (id) go

再次执行select count(*) from test4 和 select count(1) from test4

1> select count(*) from test4
2> go
QUERY PLAN FOR STATEMENT 1 (at line 1).
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
|   |   |  test4
|   |   |  Table Scan.
|   |   |  Forward Scan.
|   |   |  Positioning at start of table.
|   |   |  Using I/O Size 32 Kbytes for data pages.
|   |   |  With MRU Buffer Replacement Strategy for data pages.
Parse and Compile Time 0.
Adaptive Server cpu time: 0 ms.
-----------
524288
Execution Time 2.
Adaptive Server cpu time: 200 ms.  Adaptive Server elapsed time: 736 ms.
(1 row affected)
1> select count(1) from test4
2> go
QUERY PLAN FOR STATEMENT 1 (at line 1).
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
|   |   |  test4
|   |   |  Table Scan.
|   |   |  Forward Scan.
|   |   |  Positioning at start of table.
|   |   |  Using I/O Size 32 Kbytes for data pages.
|   |   |  With MRU Buffer Replacement Strategy for data pages.
Parse and Compile Time 0.
Adaptive Server cpu time: 0 ms.
-----------
524288
Execution Time 0.
Adaptive Server cpu time: 0 ms.  Adaptive Server elapsed time: 500 ms.
(1 row affected)
1>

由上可以看出，聚集索引对于select count(*) 几乎没有扫描影响。堆表和聚集索引表上的count是没有什么区别的，甚至于聚集索引表上的IO还要多2（这是因为多了两个聚集索引的数据块造成的）。其实聚集索引并没有单独的保留所有索引列的信息，而只是将表中的行的物理顺序按照聚集索引列的顺序整理了一下，因此对聚集索引的扫描和对堆表的扫描是一样的，没有什么本质上的区别。

添加id列上的非聚集索引idx_test4_id

1> create index idx_test4_id on test4(id)
2> go
1> sp_help test4
2> go
Name  Owner Object_type Create_date
----- ----- ----------- -------------------
test4 dbo   user table  Feb 25 2010  3:44PM
(1 row affected)
Column_name Type    Length Prec Scale Nulls Default_name Rule_name
Access_Rule_name Computed_Column_object Identity
----------- ------- ------ ---- ----- ----- ------------ ---------
---------------- ---------------------- ----------
id          int          4 NULL  NULL     0 NULL         NULL
NULL             NULL                            0
name        varchar     30 NULL  NULL     1 NULL         NULL
NULL             NULL                            0
Object has the following indexes
index_name   index_keys index_description index_max_rows_per_page
index_fillfactor index_reservepagegap index_created       index_local
------------ ---------- ----------------- -----------------------
---------------- -------------------- ------------------- ------------
pk_test4_id   id        clustered, unique                       0
0                    0 Feb 25 2010  4:04PM Global Index
idx_test4_id  id        nonclustered                            0
0                    0 Feb 25 2010  4:52PM Global Index
(2 rows affected)
index_ptn_name          index_ptn_seg
----------------------- -------------
pk_test4_id_1399673003  default
idx_test4_id_1399673003 default
(2 rows affected)
No defined keys for this object.
name  type       partition_type partitions partition_keys
----- ---------- -------------- ---------- --------------
test4 base table roundrobin              1 NULL
(1 row affected)
partition_name   partition_id pages row_count segment create_date
---------------- ------------ ----- --------- ------- -------------------
test4_1399673003   1399673003  2132    524288 default Feb 25 2010  4:04PM
Partition_Conditions
--------------------
NULL
Avg_pages   Max_pages   Min_pages   Ratio(Max/Avg)
Ratio(Min/Avg)
----------- ----------- ----------- ---------------------------
---------------------------
2132        2132        2132                    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
------------ -------------- ---------- ----------------- ------------
-----------
0              0          0                 0            0
0
(1 row affected)
concurrency_opt_threshold optimistic_index_lock dealloc_first_txtpg
------------------------- --------------------- -------------------
0                     0                   0
(return status = 0)

此时再次执行select count(*) 和select count(1)。查询计划如下：

1> select count(*) from test4
2> go
QUERY PLAN FOR STATEMENT 1 (at line 1).
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
|   |   |  test4
|   |   |  Index : idx_test4_id
|   |   |  Forward Scan.
|   |   |  Positioning at index start.
|   |   |  Index contains all needed columns. Base table will not be
read.
|   |   |  Using I/O Size 32 Kbytes for index leaf pages.
|   |   |  With MRU Buffer Replacement Strategy for index leaf pages.
Parse and Compile Time 0.
Adaptive Server cpu time: 0 ms.
-----------
524288
Execution Time 0.
Adaptive Server cpu time: 0 ms.  Adaptive Server elapsed time: 703 ms.
(1 row affected)
1> select count(1) from test4
2> go
QUERY PLAN FOR STATEMENT 1 (at line 1).
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
|   |   |  test4
|   |   |  Index : idx_test4_id
|   |   |  Forward Scan.
|   |   |  Positioning at index start.
|   |   |  Index contains all needed columns. Base table will not be
read.
|   |   |  Using I/O Size 32 Kbytes for index leaf pages.
|   |   |  With MRU Buffer Replacement Strategy for index leaf pages.
Parse and Compile Time 0.
Adaptive Server cpu time: 0 ms.
-----------
524288
Execution Time 1.
Adaptive Server cpu time: 100 ms.  Adaptive Server elapsed time: 93 ms.
(1 row affected)

可以看出查询引擎使用了非聚集索引idx_test4_id ,执行时间明显减少。因为计算行数这个操作对于全表扫描或是非聚集索引的扫描结果是一样的，而相对来说非聚集索引的数据量是肯定会比表的数据量小很多的，同样的做一次全部扫描所花费的IO也就要少很多了。

select count(id) 也是利用了非聚集索引 idx_test4_id。

结论：

count(*)和count(1)执行的效率是完全一样的。
如果是对特定的列做count的话建立这个列的非聚集索引能对count有很大的帮助。

————————————————————————————————————
——— 本文为andkylee个人原创，请在尊重作者劳动成果的前提下进行转载；
——— 转载务必注明原始出处 : http://www.dbainfo.net
——— 关键字： sybase 聚簇索引非聚簇索引查询计划行数 count
————————————————————————————————————

阅读全文…

标签: ASE, 索引

ASE12.5全局变量[转]

ASE12.5的全局环境变量，来自：http://bbs.chinaunix.net/thread-533042-1-1.html

@@authmech

,@@bootcount

,@@boottime

,@@bulkarraysize

,@@bulkbatchsize

,@@char_convert

,@@cis_rpc_handling

,@@cis_version

,@@client_csexpansion

,@@client_csid

,@@client_csname

,@@cmpstate

,@@connections

,@@cpu_busy

,@@crthaproxy

,@@curloid

,@@datefirst

,@@dbts

,@@error

,@@errorlog

,@@failedoverconn

,@@guestuserid

,@@ha_local_nodeid

,@@hacmpservername

,@@haconnection

,@@heapmemsize

,@@identity

,@@idle

,@@invalidsuid

,@@invaliduserid

,@@io_busy

,@@isolation

,@@kernel_addr

,@@kernel_size

,@@langid

,@@language

,@@localstate

,@@lock_timeout

,@@max_connections

,@@max_precision

,@@maxcharlen

,@@maxgroupid

,@@maxpagesize

,@@maxspid

,@@maxsuid

,@@maxuserid

,@@mempool_addr

,@@min_poolsize

,@@mingroupid

,@@minspid

,@@minsuid

,@@minuserid

,@@monitors_active

,@@msgheader

,@@msgid

,@@msgproperties

,@@msgreplytoinfo

,@@msgschema

,@@msgstatus

,@@msgstatusinfo

,@@msgtimestamp

,@@ncharsize

,@@nestlevel

,@@nodeid

,@@options

,@@pack_received

,@@pack_sent

,@@packet_errors

,@@pagesize

,@@parallel_degree

,@@probesuid

,@@procid

,@@recovery_state

,@@remotestate

,@@rowcount

,@@scan_parallel_degree

,@@servername

,@@shmem_flags

,@@spid

,@@sqlstatus

,@@stringsize

,@@tempdbid

,@@textcolid

,@@textdbid

,@@textobjid

,@@textptr

,@@textptr_parameters

,@@textsize

,@@textts

,@@thresh_hysteresis

,@@timeticks

,@@total_errors

,@@total_read

,@@total_write

,@@tranchained

,@@trancount

,@@transactional_rpc

,@@transtate

,@@unicharsize

,@@version

,@@version_as_integer

,@@version_number

阅读全文…

标签: ASE, 全局环境变量

通过ASE本身的命令重新启动ASE[转]

sybase在自动管理方面真的很弱很弱，不管windows还是unix平台上都是一样的不好用。不过，我现在都已经习惯了sybase的不好用。

很早以前自己也想到了如何在sybase ase内部来实现自动启动ase服务器。今天看到echoaix写的这篇文档，和我的思路差不多。自己正好也懒得写了。

通过ASE本身重新启动ASE

ase本身没有重启命令，如何让ASE自己重启是一个问题，记得当时负责将近100个ase server，分布在全国各地，服务器还大多是PC server，windows环境，管理员口令经常不知道，远程登陆也很慢，可是当时ase版本较低，改个参数有的就要重启
要ase重启并不困难，还是要调用系统的东西，说白了使用的工具就是ase的扩展存储过程的xp_cmdshell,熟悉mssql server的人都知道xp_cmdshell，其实mssql server最早就是sybase买给微软的源码，不多说，看看如何实现吧。

前提：
1 XP server，一般windows上的ase创建都会带着。
2 XP server的条号(interfaces和sql.ini)
3 XP server在sysservers中的记录
select upper(@@servername)+'_XP'，假定结果为TEST_XP,即为需要添加的XP sever信息注意一定要全部大写，要不到时候回报”XP Server must be up for ESP to execute“ .
sp_addserver TEST_XP,NULL 添加XP server
4 "xp_cmdshell context"配置参数的设置，此参数为使用 xp_cmdshell 系统 ESP 执行的操作系统命令设置安全性环境，缺省值为1。如果 xp_cmdshell context 设置为 1，在Windows NT下，那么只有当 Adaptive Server 的登录用户的用户名是有效的 Windows NT 用户名（在运行 Adaptive Server 的系统上具有 Windows NT 系统管理权限）时，xp_cmdshell 才能成功。设置为0，sa就可执行。
sp_configure "xp_cmdshell context",0
5 调度服务运行（不运行也没关系，用xp_cmdshell启动）

思路：
ase在windows上大多都是以服务的形式存在，服务名固定为SYBSQL_@@servername(假定为SYBSQL_test),停止ase 可以用net shop SYBSQL_test,启动ase用net start SYBSQL_test,当然也可以在isql中shutdown停ase和直接执行ase启动bat文件来启动ase。xp_cmdshell可以用来执行系统命令，可以创建一个关闭和启动ase的bat文件，然后再用xp_cmdshell把这个bat写入调度任务中就可以完成ase重启。
declare @todo varchar(250)
select @todo = "echo net stop SYBSQL_" + @@servername + " > c:\restartase.bat" + "&" + "echo net start SYBSQL_" + @@servername + " >> c:\restartase.bat"
exec xp_cmdshell @todo,no_output \\不要输出结果
go
这样在c:\生成一个restartase.bat文件，内容为net shop SYBSQL_test和net start SYBSQL_test。

declare @todo varchar(250)
select @todo = 'at ' + convert(char(5),dateadd(ss, 120, getdate()),8) + ' cmd /c "c:\restartase.bat"' \\在两分钟之后重启ase
exec xp_cmdshell @todo,no_output
go

总结：
原理很简单，最好在执行之前checkpoint，commit，总之还是shutdown然后登陆系统重启好。
UNIX上的应该差不多，用的较少，就先不写了。

可以转载请注明作者 echoaix

阅读全文…

标签: ASE, isql

第 7 页，共 8 页« 最新...«4 5 678 »

© 2009-2021 Sybase数据库技术，数据库恢复专家 - 提供Sybase ASE及Sybase SQL Anywhere数据库修复服务，电话：13811580958(微信)，QQ：289965371！We have many years of experience in recovering data from damanged Sybase devices. Contact us by Phone: +86 13811580958 Wechat: 13811580958 Email: 289965371@qq.com｜京ICP备2020043699号-2
Powered by WordPress | Theme by NeoEase | Valid XHTML 1.1 and CSS 3
SiteMap | Baidu Knows | Google Knows | About

Sybase数据库技术，数据库恢复专家

存档

ASE12.x与15.x中的字符串替换功能的实现

ASE中的命令行实用工具isql的几个参数的使用方法

如何配置监控表MonTables来实现ASE的监控功能

Sybase15.0.3跨平台恢复过程

Sybase发行版本（Release）术语

sybase中的聚簇索引与count（*）的关系

ASE12.5全局变量[转]

通过ASE本身的命令重新启动ASE[转]

Sybase数据库修复服务

分类目录

最新文章

标签

热文排行

Linux-Links

Sites

SYBASE-Links

文章归档

Meta

Sybase数据库技术咨询