第十四章 使用SQL Shell界面（三）

SQL元数据、查询计划和性能指标

显示元数据

SQL Shell支持M或Metadata命令以显示有关当前查询的元数据信息。

对于每个结果集项目，此命令列出以下元数据：列名称（SQL字段名称），键入（ODBC数据类型整数代码），PRE（精度或最大长度），比例（最大分数数字），NULL（BOOLEAN：1 = NULL允许，0 =不允许空值），标签（标题标签，请参阅列别名），表（SQL表名称），架构（架构名称），CTYPE（客户端数据类型，请参阅％SQL.statementColumn ClientType属性）。

SHOW STATEMENT

可以执行查询，然后发出show语句或显示st以显示准备好的SQL语句。默认情况下，必须执行查询。可以避免通过设置executemode =延迟执行查询，从而发出查询，然后发出show语句sql shell命令。

显示声明信息包含实现类(缓存查询名称),参数(一个以逗号分隔的实际参数值,如上面条款和WHERE子句文字值),和语句文本(文字文本的SQL命令,包括字母大小写和参数值)。

EXPLAIN and Show Plan

有两种方式显示SQL查询的查询计划;
如果需要，两者都可以显示备用的查询计划。

• EXPLAIN：前言用解释命令选择SELECT查询。例如：

SQL]USER>>EXPLAIN SELECT Name FROM Sample.MyTable WHERE Name='Fred Rogers'

• SHOW PLAN:发出查询，然后发出show plan shell命令。例如：

SQL]USER>>SELECT Name FROM Sample.MyTable WHERE Name='Fred Rogers'
SQL]USER>>SHOW PLAN

EXPLAIN SQL命令显示有关指定选择查询的查询计划信息而不执行查询。EXPLAIN Alt允许显示备用查询计划。EXPLAIN Stat返回性能统计信息以及查询计划。EXPLAIN只能用于返回选择查询的查询计划;它不会返回用于执行查询操作的Insert，Update或DELETE语句等其他命令的查询计划。

Show Plan SQL shell命令允许显示SQL Shell成功发布的上次查询的查询计划信息。显示计划可用于执行查询操作的任何SQL命令，包括选择，插入，更新和删除。默认情况下，必须执行查询。可以避免通过设置executemode=deferred,执行查询，发出查询，然后发出以下SQL shell命令之一：

• SHOW PLAN、SHOW PL(或简单的SHOW)显示关于当前查询的查询计划信息。
  查询计划可用于调试和优化查询的性能。
  它指定查询的执行方式，包括索引的使用和查询的成本值。
  可以返回查询计划的语句有:SELECT、DECLARE、non-cursor UPDATE or DELETE、INSERT…SELECT。
  该命令有一个V (VERBOSE)选项。
• 显示PLANALT显示当前查询的备用显示计划。
  该命令有一个V (VERBOSE)选项。

可以使用$SYSTEM.SQL.Explain()方法从ObjectScript生成查询计划。

SQL Shell Performance

成功执行一个SQL语句后，SQL Shell会显示四个语句准备值(times(s)/globals/cmds/disk)和四个语句执行值(times(s)/globals/cmds/disk):

• 语句准备时间是指准备动态语句所花费的时间。
  这包括生成和编译语句所花费的时间。
  它包括在语句缓存中查找语句所花费的时间。
  因此，如果执行了一条语句，然后按编号或名称回收，回收语句的准备时间接近于零。
  如果一条语句已经准备好并执行，然后通过发出GO命令重新执行，那么重新执行时的准备时间为零。
• 经过的执行时间是从调用%execute()到%Display()返回所经过的时间。
  它不包括输入参数值的等待时间。

语句globals是全局引用的计数，cmds是执行的SQL命令的计数，disk是磁盘延迟时间，单位是毫秒。
SQL Shell为准备操作和执行操作保留单独的计数。

这些性能值只在“DISPLAYMODE”设置为“currentdevice”，“MESSAGES”设置为“ON”时显示。
这些是SQL Shell的默认设置。

Transact-SQL支持

默认情况下，SQL Shell执行InterSystems SQL代码。
但是，SQL Shell可以用来执行Sybase或MSSQL代码。

Setting DIALECT

默认情况下，SQL Shell将代码解析为InterSystems SQL。
可以使用SET DIALECT来配置SQL Shell以执行Sybase或MSSQL代码。
若要更改当前方言，请将“方言”设置为Sybase、MSSQL或IRIS。
默认值是Dialect=IRIS。
这些设置的方言选项不区分大小写。

下面是一个从SQL Shell中执行MSSQL程序的例子:

DHC-APP>DO $SYSTEM.SQL.Shell()
SQL Command Line Shell
----------------------------------------------------

The command prefix is currently set to: <<nothing>>.
Enter q to quit, ? for help.
DHC-APP>>SET DIALECT MSSQL

dialect = MSSQL
DHC-APP>>SELECT TOP 5 name + '-' + ssn FROM Sample.Person
1.      SELECT TOP 5 name + '-' + ssn FROM Sample.Person

Expression_1
yaoxin-111-11-1117
xiaoli-111-11-1111
姚鑫-111-11-1112
姚鑫-111-11-1113
姚鑫-111-11-1114

5 Rows(s) Affected
statement prepare time(s)/globals/lines/disk: 0.1989s/46546/257369/114ms
          execute time(s)/globals/lines/disk: 0.0032s/24/676/3ms
---------------------------------------------------------------------------

Sybase和MSSQL方言支持这些方言中有限的SQL语句子集。
它们支持SELECT、INSERT、UPDATE和DELETE语句。
它们对永久表支持CREATE TABLE语句，但对临时表不支持。
支持创建视图。
支持创建触发器和删除触发器。
但是，如果CREATE TRIGGER语句部分成功，但在类编译时失败，则此实现不支持事务回滚。
支持CREATE PROCEDURE和CREATE FUNCTION。

Setting COMMANDPREFIX

可以使用SET COMMANDPREFIX指定必须追加到后续SQL Shell命令的前缀(通常是单个字符)。
在SQL Shell提示符发出的SQL语句中不使用此前缀。
这个前缀的目的是防止SQL Shell命令和SQL代码语句之间的歧义。
例如，SET是一个SQL Shell命令;
SET也是Sybase和MSSQL中的SQL代码语句。

默认情况下，没有命令前缀。
要建立命令前缀，设置COMMANDPREFIX=prefix，指定的前缀不带引号。
要恢复为没有命令前缀，设置COMMANDPREFIX=""。
以下示例显示了命令前缀/(斜杠字符)被设置、使用和恢复的示例:

DHC-APP>>SET COMMANDPREFIX=/

commandprefix = /
DHC-APP>>/SET LOG=ON

log = xsql19388.log
DHC-APP>>  << entering multiline statement mode >>
        1>>SELECT TOP 3 Name,Age
        2>>FROM Sample.Person
        3>>/GO
2.      SELECT TOP 3 Name,Age
        FROM Sample.Person

Name    Age
yaoxin  30
xiaoli
姚鑫    7

3 Rows(s) Affected
statement prepare time(s)/globals/lines/disk: 0.0595s/46282/256257/9ms
          execute time(s)/globals/lines/disk: 0.0003s/3/714/0ms
---------------------------------------------------------------------------
DHC-APP>>/SET COMMANDPREFIX

commandprefix = /
DHC-APP>>/SET COMMANDPREFIX=""

commandprefix = ""
DHC-APP>>SET COMMANDPREFIX

commandprefix =

当设置了命令前缀时，除了?、#和GO之外的所有SQL Shell命令都需要该命令前缀;
可以使用或不使用命令前缀发出这三个SQL Shell命令。

当发出SET或SET COMMANDPREFIX命令时，SQL Shell将显示当前命令前缀，作为SQL Shell初始化的一部分，并且在?
命令选项显示。

运行命令

SQL Shell RUN命令执行SQL脚本文件。
在发出运行命令之前必须设置方言，以指定IRIS (InterSystems SQL)、Sybase (Sybase TSQL)或MSSQL (Microsoft SQL);
默认的方言是IRIS。
可以调用RUN scriptname，也可以只调用RUN，然后提示输入脚本文件名。

RUN加载脚本文件，然后准备并执行文件中包含的每个语句。
脚本文件中的语句必须分隔，通常用GO行或分号(;)分隔。
RUN命令提示指定分隔符。

SQL脚本文件结果显示在当前设备上，也可以显示在日志文件中。
还可以生成一个包含准备失败语句的文件。

RUN命令返回指定这些选项的提示符，示例如下:

[SQL]USER>>SET DIALECT=Sybase

dialect = Sybase
[SQL]USER>>RUN

Enter the name of the SQL script file to run: SybaseTest

Enter the file name that will contain a log of statements, results and errors (.log): SyTest.log
     SyTest.log

Many script files contain statements not supported by IRIS SQL.
Would you like to log the statements not supported to a file so they
can be dealt with manually, if applicable?   Y=> y
Enter the file name in which to record non-supported statements (_Unsupported.log): SyTest_Unsupported.log

Please enter the end-of-statement delimiter (Default is 'GO'):  GO=>

Pause how many seconds after error?   5 => 3

Sybase Conversion Utility (v3)
Reading source from file:
Statements, results and messages will be logged to: SyTest.log
.
.
.

TSQL例子

下面的SQL Shell示例创建了一个Sybase过程AvgAge。
它使用Sybase EXEC命令执行这个过程。
然后，它将方言更改为InterSystems IRIS，并使用InterSystems SQL CALL命令执行相同的过程。

DHC-APP>>SET DIALECT Sybase

dialect = Sybase
DHC-APP>>  << entering multiline statement mode >>
        1>>CREATE PROCEDURE AvgAge
        2>>AS SELECT AVG(Age) FROM Sample.Person
        3>>GO
3.      CREATE PROCEDURE AvgAge
        AS SELECT AVG(Age) FROM Sample.Person


statement prepare time(s)/globals/lines/disk: 0.0173s/8129/22308/4ms
          execute time(s)/globals/lines/disk: 0.0436s/3844/23853/34ms
---------------------------------------------------------------------------
DHC-APP>>EXEC AvgAge
4.      EXEC AvgAge



Dumping result #1
Aggregate_1
50.68137254901960784

1 Rows(s) Affected
statement prepare time(s)/globals/lines/disk: 0.0086s/8096/21623/0ms
          execute time(s)/globals/lines/disk: 0.1131s/90637/458136/19ms
---------------------------------------------------------------------------
DHC-APP>>SET DIALECT=IRIS

Dialect 'iris' is not supported.