系统命名与 SQL 命名之争 — 第 2 部分

访问数据库对象

执行 SQL 语句时,您可以使用系统命名规范或者 SQL 命名规范来运行它们。上一篇文章重点探讨了使用 SQL 和系统命名规范创建数据库对象时,所有权和访问权限之间的差异。本文将介绍系统命名与 SQL 命名在访问表和视图以及存储过程和用户定义的函数 (UDF) 时的行为差异,重点关注使用这些对象的非限定引用时的不同行为。

Birgitta Hauser, 软件工程师, Toolmaker Advanced Efficiency GmbH

Brigitta_imageBirgitta Hauser 自 2008 年以来一直担任软件工程师一职,在位于德国的 Toolmaker Advanced Efficiency GmbH 从事关于 System i 和 IBM i 的 RPG、SQL 和 Web 开发工作。她拥有商业经济学文凭,并于 1992 年开始在 IBM AS/400 上进行编程。她还是 RPG 和 SQL 开发人员的培训师,并从事相关的咨询和教育工作。自 2002 以来,她经常在德国、其他欧洲国家和美国的 COMMON User Groups 中发表演讲。此外,她是两本 IBM 红皮书的合著者,还在一家德国杂志上撰写了许多关于 RPG 和 SQL 的文章和论文。



2013 年 3 月 04 日

本文介绍了使用系统和 SQL 命名规范运行 SQL 语句时的不同行为。本系列的 第 1 部分 主要关注在使用 SQL 创建 IBM® DB2® 对象时,系统命名规范和 SQL 命名规范如何导致对象所有权和访问权限差异。

命名规范还会决定在使用模式显式限定 DB2 对象引用时,使用哪个字符来分隔模式与对象名称,系统命名 (*SYS) 使用的是斜杠 (/),而 SQL 命名 (*SQL) 使用的是圆点 (.)。

不过,IBM i 应用程序很少会通过显式指定模式名称的方式来访问 DB2 对象。这些应用程序依靠搜索库列表来查找恰当的对象。它们将使用在库列表中找到的第一个具有指定名称和适当对象类型的对象。在测试应用程序时,包含新程序和数据集的库会直接插入库列表的顶部。通过这种方式,即可轻松处理新旧程序的混合,轻松处理生产数据和测试数据。具有显式限定的模式引用的应用程序必须手动更改,然后才能在不同的环境中运行。

对于典型的 IBM i 应用程序,下面我们将根据访问 SQL 语句中指定的非限定数据库对象的命名规范来分析不同的行为。

访问数据

持久用户数据仅存储在表中。表中的数据可直接访问,也可通过别名或视图间接访问。要在 SQL 语句中访问表、视图或别名,可以通过模式名称显式限定对象,也可以根据命名规范隐式解析模式名称。

数据访问方法

IBM DB2 for i 对象中的数据可通过某些高级语言(例如 RPG 或 COBOL)中使用的记录级访问接口进行访问和维护。

不过,SQL 是新近开发的 IBM i 应用软件和程序中访问数据的最常用接口。SQL 语句可作为 静态动态 SQL 运行。静态 SQL 与动态 SQL 之间的主要区别取决于 SQL 语句本身的生成方式。

  • 静态 SQL 语句

包含嵌入式 SQL 的 SQL 例程或应用程序多使用静态 SQL 语句。静态 SQL 语句在程序或例程源代码中采用硬编码的形式。对于静态 SQL 语句来说,SQL 预编译器将检查 SQL 语法,评估对表和列的引用,并声明所有宿主变量的数据类型。SQL 预编译器也会根据 编译时使用的命名规范,确定运行时用于解析非限定数据库对象的模式。从性能的角度来看,使用静态 SQL 是最佳选择,因为有些步骤(例如,语法检查)是在编译时完成的。

清单 1. 静态 SQL 语句 展示了一个 RPG 程序内嵌入的静态 SQL 语句,该语句用于确定特定年份的订单数量。年份值将作为参数值 (ParYear) 传递给该过程。

清单 1.静态 SQL 语句
	 D GetNbrOfOrders... 
	 D                 PI            10I 0 
	 D ParYear                        4P 0  Const 
	  ... 
	 D NbrOfOrders     S             10I 0 
	 /Free 
	  ... 
	    Exec SQL  Select  Count(*)        Into :NbrOfOrders 
	                from  Order_Header 
	                Where Year(OrderDate) = :ParYear;
  • 动态 SQL 语句

动态 SQL 语句是在程序的运行时生成的。在构建完成后,动态 SQL 语句的语法会被检查,随后将其转为可执行的 SQL 语句,以便运行。

清单 2. 一个 SQL 例程中的动态 SQL 语句 展示了创建 UDF COUNT_NUMBER_OF_ROWS 的 SQL 脚本。使用这个函数,即可确定任意表、视图或别名中的行数。表(或者视图和别名)名称以及模式名称将作为参数值传递。调用该 UDF 时,会以字符串的形式生成所执行的 SQL 语句,其中包含传递的参数值。运行 PREPARE 语句将其转为可执行 SQL 语句,随后会检查该字符串的语法,最后使用 EXECUTE 语句执行该语句。由于编译时并不了解运行时会访问哪些表或模式,因此需要使用动态 SQL。

清单 2. 一个 SQL 例程中的动态 SQL 语句
 	 Create Function Count_Number_Of_Rows 
	      (ParTable  VarChar(128), 
	       ParSchema VarChar(128)) 
	       Returns Integer 
	       Language SQL 
	       Not Fenced 
	   Begin 
	      Declare RtnNbrRows Integer; 
	      Declare String     VarChar(256); 
	      
	      Set String = 'Values(Select Count(*) From '; 
	      
	      If ParSchema > ' ' Then 
	         Set String = String CONCAT ParSchema CONCAT '/'; 
	      End If; 
	      
	      Set String = String CONCAT ParTable CONCAT ') into ?'; 
	
	      PREPARE DynSQL From String; 
	      EXECUTE DynSQL Using RtnNbrRows; 
	      Return RtnNbrRows; 
	   End;

动态 SQL 语句可用于 SQL 例程之中,也可嵌入程序,但最常用于通过 ODBC 或 DB2 Web Query 等接口运行 SQL 语句;以及通过 SQL 命令行处理程序来运行 SQL 语句,例如 IBM System i® Navigator Run Script Interface 或 RUNSQLSTM 和 RUNSQL 命令行命令。清单 3. 采用交互的方式发出的动态 SQL 语句 展示了一条通过 System i Navigator Run Script Interface 运行的动态 SQL 语句。

清单 3. 采用交互的方式发出的动态 SQL 语句
 	 Select * from Order_Header;

确定默认模式

如果一条 SQL 语句包含非限定表、视图或别名引用,那么 DB2 必须确定 默认模式,并搜索该模式。模式 是 SQL 中类似于 IBM i 库的术语。默认模式的初始值取决于 SQL 环境中使用的命名规范,以及运行的是 静态 SQL 语句,还是 动态 SQL 语句。

静态 SQL 语句的默认模式

使用嵌入式 SQL 时,静态 SQL 语句的默认模式可以在编译命令 (CRTSQLxxxI) 中使用 DFTRDBCOL(默认集合)参数 显式设置。此外,也可以在源代码中包含一条使用 DFTRDBCOL 参数的 SET OPTION 语句

在嵌入式 SQL 程序中,只能指定单独一条 SET OPTION 语句,即便源代码包含多个独立的(导出的)过程也是如此。SET OPTION 语句必须是源代码中的第一条 SQL 语句。

清单 4:RPG 中嵌入的 SET OPTION 语句 展示了一个 RPG 源代码的部分摘录,其中使用了一条 SET OPTION 语句,将命名规范设置为 SQL 命名,并将静态 SQL 语句的默认模式设置为 SALESDB01.SET OPTION 语句紧接全局 D 规范之后,这意味着它成为了 C 规范中的第一条语句。

清单 4. RPG 中嵌入的 SET OPTION 语句
 D* Global D Specifications 
 /Free 
     EXEC SQL Set Option  Naming   = *SQL,DFTRDBCOL = SALESDB01; 

   // RPG code and other embedded SQL Statements go here

对于 SQL 例程,默认模式也可通过包含一条带有 DFTRDBCOL 参数的 SET OPTION 语句 来显式设置。在 清单 5:SQL 例程中的 SET OPTION 语句 中,MyProcedure 例程内静态 SQL 语句的默认模式显式设置为 SALESDB01。

清单 5. SQL 例程中的 SET OPTION 语句
 Create Procedure MyProcedure () 
       Language SQL 
       Set Option DFTRDBCOL   = SALESDB01   
 Begin 
   -- SQL Routine Body – Source code 
 End;

如果未显式设置默认模式,那么会 在编译时根据命名规范来确定默认模式

  • 对于 系统命名,默认模式是作业 Library List (*LIBL)

    对于系统命名,模式 这个术语可能会被误解,因为初始值被设置为特殊值 *LIBL。这个特殊值表示使用了库列表,在尝试解析非限定对象引用时,可能会搜索到多个模式。我们会使用在第一个库中找到的与特定的非限定数据库对象名称和对象类型匹配的第一个 DB2 对象。可以在相同的 SQL 语句中访问位于不同模式中的数据库对象,无需指定模式。

  • 对于 SQL 命名,目前将使用创建 SQL 例程的 SQL 环境内的 默认模式

采用嵌入式 SQL 的应用程序不是通过 SQL 接口创建的,因此静态 SQL 语句的默认模式设置为 运行时授权 ID。在 IBM i 上,运行时授权 ID 就是执行编译的作业的用户配置文件。这意味着 SQL 命名的默认行为是 DB2 尝试在与创建者的用户配置文件同名的模式中查找非限定对象。

在解析非限定 DB2 对象引用时,SQL 命名仅允许搜索 单一模式

动态 SQL 语句的默认模式

对于动态 SQL 语句,默认模式取决于是否显式指定了默认模式值。如果未显式设置默认模式,那么其 初始值 取决于命名规范。

  • 对于 系统命名,默认模式是作业 Library List (*LIBL)。
  • 对于 SQL 命名,默认模式是 运行时授权 ID(当前用户配置文件)。如前所述,SQL 命名的默认行为是 DB2 尝试在与当前用户配置文件同名的模式中查找非限定对象。

SET SCHEMA 语句

所有接口上的默认模式值均可通过运行 SET SCHEMA 语句进行更改。SET SCHEMA 语句提供的新默认模式值 动态 SQL 语句 用于解析非限定数据库对象。如果在运行时为 静态 SQL 语句 解析非限定对象,该语句 不会产生任何效果

在 SET SCHEMA 语句中,也可指定特殊寄存器,例如 USER、SESSION_USER 或 SYSTEM_USER。不允许使用特殊值 *LIBL,即便在使用了系统命名规范的环境中也是如此。

如果在使用 系统命名 规范的环境中运行 SET SCHEMA 语句,则不会在 库列表 中搜索动态 SQL 语句。此时会在 SET SCHEMA 语句中制定的 单个模式 内搜索非限定数据库对象。

当前模式

运行时在动态 SQL 语句中搜索非限定数据访问的默认模式也称为 当前模式。CURRENT_SCHEMA 特殊寄存器将返回当前用于解析动态 SQL 语句中非限定数据访问的模式值。

应该注意,当前模式当前库并非 完全相同。当前库 将通过运行 CHGCURLIB (change current library) 命令添加到当前库列表中,位置在用户库列表之前。只有在使用系统命名的情况下,才会访问库列表。因此,只有在使用系统命名时才能搜索 当前库

当前模式(或默认模式)即当前库列表(系统命名)或单独一个模式(SQL 命名),这个模式可能属于当前库列表,也可能不属于当前库列表。

SET SCHEMA 语句和动态 SQL 接口

许多 DB2 for i SQL 接口都具备为动态 SQL 接口指定默认模式值的能力,因此能自动代您执行 SET SCHEMA 语句。指定默认模式值的机制取决于接口。

  • IBM System i Navigator Run SQL Scripts 工具

    单击 ConnectionJDBC Settings 即可预设默认模式。可以在 System 选项卡中指定默认模式或者库列表,如下图所示。

    图 1. System i Navigator Run SQL Scripts — 设置默认模式
    图 1:System i Navigator Run SQL Scripts — 设置默认模式
  • 命令行命令:RUNSQLSTM 和 RUNSQL

    加上命令行,命令中使用 DFTRDBCOL(默认集合)参数指定默认模式。

  • ODBC 连接

    ODBC 连接可使用 - ODBC Administration 来定义。使用 ODBC 访问您的表和视图时,可以通过 IBM i Access for Windows ODBC Administration 界面设置默认模式,也可以通过编程的方式设置 DefaultLibraries 连接关键字来设置默认模式。

    >
  • SQL 调用级接口 (CLI)

    使用 SQL CLI 函数时,可以设置 SQL_ATTR_DEFAULT_LIB 或 SQL_ATTR_DBC_DEFAULT_LIB 环境变量或连接变量,以显式指定要使用的模式。

  • Java™数据库连接 (JDBC) 或 Java 结构化查询语言 (SQLJ)

    可以通过库的属性对象设置默认模式。

  • 使用 IBM i Access Family OLE DB Provider 的 OLE DB

    可以通过 Connection Object Properties 中的 DefaultCollection 显式指定默认模式。

  • 使用 IBM i Access Family ADO .NET Provider 的 ADO .NET

    可以通过 Connection Object Properties 中的 DefaultCollection 显式指定默认模式。

注意:

其中部分接口允许设置默认模式和默认库列表。如果指定了默认模式,并使用了系统命名规范,那么 DB2 在解析非限定 DB2 对象引用时有可能仅使用指定的默认模式,而忽略库列表。由于存在这样的行为,因此在使用系统命名规范时,最好避免指定默认模式值。


测试环境

为了检查访问数据对象时(使用系统命名或 SQL 命名)的不同行为,我创建了一个测试环境,以展示一个典型的 IBM i 应用程序。该测试环境包含四个模式:

  • 模式 MASTERDB — 主信息

    会计、购买、销售、ERP 等多种应用程序需要特定客户的地址、供应商或某种商品等信息。

    模式 MASTERDB 包含以下几个表:

    ADDRESS_MASTER、ITEM_MASTER 和 ORDER_SUMMARY

  • 模式 SALESDB01 和模式 SALESDB02 — 销售数据

    SALESDB01 和 SALESDB02 模式分别包含企业 1 和企业 2 的必要销售信息。

    两个模式均包含 ORDER_HEADER 和 ORDER_DETAIL 表。

  • 模式 SALESPGM — 程序模式

    模式 SALESPGM 不包含任何数据,用作所有(服务)程序、存储过程和用户定义的函数的容器。


执行动态 SQL 语句

在下面的例子中,我们将观察运行采用系统命名或 SQL 命名的 SQL 语句时的不同行为,我们将使用 System i Navigator Run Script 工具作为动态 SQL 接口。

使用系统命名的非限定数据访问

在采用系统命名、未显式设置默认模式的环境中执行动态 SQL 语句时,将搜索当前 库列表,以查找所有非限定表和视图。

最初,以作业描述为依据,在任何 SQL 接口中都设置了库列表。不过,您可以运行 CHGLIBL(更改库列表)或 ADDLIBLE(添加库列表条目)等命令来修改库列表。如果运行 SQL SCHEMA 语句更改默认模式,则会忽略当前库列表,搜索时会使用新设置的(单独一个)模式。

下面的示例执行了几条 SQL 语句,演示了这种行为。

在运行第一条 SELECT 语句之前,执行 CHGLIBL(更改库列表)命令显式设置库列表。在 SELECT 语句中,位于 SALESDB01 或 SALESDB02 模式中的 ORDER_HEADER 表和位于 MASTERDB 模式中的 ADDRESS_MASTER 表彼此连接。

SALESDB01 和 MASTERDB 模式都是当前库列表的一部分。因此将成功找到表,SELECT 语句将成功执行。由于 ORDER_HEADER 表是在 SALESDB01 模式中找到的,所以返回了企业 1 请求的数据。

清单 6:访问使用系统命名的多个模式中的数据
 CL: CHGLIBL LIBL(SALESDB01 MASTERDB QGPL); 

 Select  h.Company, h.OrderNo, 
         AddressNo, a.Name1,   a.Address, a.City 
    From Order_Header h join Address_Master a 
         Using(AddressNo);
COMPANYORDERNOADDRESSNONAME1ADDRESSCITY
1 100 1 Fischer & Co Wald- und Wiesenweg 16 Dietzenbach
1 110 3 Bauer GmbH Nordring 417 Berlin
1 120 4 Rathaus Center Hauptstr. 3 Hamburg
1 130 4 Rathaus Center Hauptstr. 3 Hamburg
1 140 4 Rathaus Center Hauptstr. 3 Hamburg

为了检索企业 2 需要的同样信息,必须将 SALESDB02 模式添加到库列表中。SALESDB01 模式可以删除,否则必须将它放在库列表中 SALESDB02 模式之后。库列表必须通过运行 CHGLIBL 或 ADDLIBLE 等命令加以更改。

如果 SALESDB02 模式是通过运行 SET SCHEMA 语句设置的,那么默认模式值会从 *LIBL 更改为 SALESDB02。执行这项更改后,再次运行 SELECT 语句时,执行会出错,提示 SQLSTATE 42704,原因就是没有在 SALESDB02 模式中找到位于 MASTERDB 模式内的 ADDRESS_MASTER 表。

使用 SQL 命名的非限定数据访问

在采用 SQL 命名的环境中,运行时仅搜索单独一个模式来解析非限定表、视图和别名。使用 SQL 命名规范执行 清单 6:访问使用系统命名的多个模式中的数据 中的 SELECT 语句时,SELECT 语句始终出错,显示 SQLSTATE 值为 42704,这是因为 ADDRESS_MASTER 和 ORDER_HEADER 表位于不同的模式之中。

在使用 SQL 命名规范时,不同模式内的数据库对象必须加以限定,否则只能通过默认模式内的别名或视图对它们进行访问。别名或视图可以引用不同模式中的表或视图。

在 SQL 例程或程序中执行动态 SQL

嵌入 SQL 例程或程序的动态 SQL 语句沿用解析非限定对象引用时的相同规则。然而,动态 SQL 语句使用了在 SQL 例程或程序 创建 时采用的命名规范,而非 运行 SQL 例程或者程序时指定的命名规范。

举例来说,如果使用 SQL 命名规范创建了一个 SQL 存储过程,那么该过程中的动态 SQL 语句将会使用 SQL 命名规则来解析非限定名称,即便通过使用系统命名规范的 SQL 接口调用该 SQL 存储过程时也是如此。

具有嵌入式 SQL 的程序使用的命名规范可以在编译时使用编译命令的 OPTION 参数指定,也可在源代码中嵌入 SET OPTION 语句来指定。SQL 例程将继承用于创建该 SQL 例程的 SQL 接口的命名规范。尽管 SET OPTION 语句可以嵌入 SQL 例程中,但不允许指定 NAMING 选项。

动态 SQL 语句在运行时使用的默认模式可以在例程或程序内 显式设置,设置方法可以是运行一条命令(以修改库列表),也可以是执行源代码内嵌的一条 SET SCHEMA 语句。

修改 SQL 例程或应用程序内的库列表时,相同作业内运行的所有程序和过程都将使用修改后的库列表。在 SQL 例程或嵌入式 SQL 程序内运行 SET SCHEMA 语句时,仅有该例程或程序内的动态 SQL 语句会使用 SET SCHEMA 设置。调用 SQL 例程或程序的接口默认模式值将保持不变。

以下 SQL 脚本在使用系统命名的环境中创建了存储过程 ORDERADDRD。存储过程接受一个参数 (ParAddressNo),并返回包含针对这个具体地址的所有 Order Header 和 Address 信息的结果集。ORDER_HEADER 表与 ADDRESS_MASTER 表连接。这些表均未使用模式限定。SQL SELECT 语句将动态准备和执行。

清单 7:使用动态 SQL 的 ORDERADDRD 例程
 Create Procedure SALESPGM/OrderAddrD 
          (In ParAddressNo Integer) 
       Dynamic Result Sets 1 
       Language SQL 
 Begin 			
    Declare StringSQL01 VarChar(1024); 	
    Declare CsrC01 Cursor For DynSQLC02;              
    
    Set StringSQL01 = 'Select Company,   OrderNo, OrderDate, 
                              AddressNo, Name1,   City  
                         From Order_Header Join Address_Master 
                              Using (AddressNo) 
                       Where AddressNo = ?'; 	
    
    Prepare DynSQLC01 From StringSQL01; 
    Open CsrC01 Using ParAddressNo; 			
 End;

清单 8 展示了 ORDERADDR 过程的两个成功调用,以及各过程调用返回的结果集的内容。首先,使用 CHGLIBL 命令显式设置库列表。由于过程的创建采用了 系统命名,因此将搜索 库列表 来查找非限定引用。ORDER_HEADER 表是在 SALESDB01 模式中找到的,而 ADDRESS_MASTER 表是在 MASTERDB 模式中找到的。结果集包含来自企业 1 的数据,提供了使用 SALESDB01 模式中 ORDER_HEADER 表的信息。

为了获得企业 2 的其他标头数据,应在调用存储过程之前使用 CHGLIBL 命令 更改库列表

清单 8:使用系统命名的 SQL 例程内的动态 SQL
  CL: CHGLIBL LIBL(SALESDB01 MASTERDB SALESPGM QGPL); 
 Call SalesPGM/OrderAddrD(4);
COMPANYORDERNOORDERDATEADDRESSNONAME1CITY
1 120 04/26/2012 4 Rathaus Center Hamburg
1 130 04/27/2012 4 Rathaus Center Hamburg
1 140 04/24/2012 4 Rathaus Center Hamburg

  CL: CHGLIBL LIBL(SALESDB02 MASTERDB SALESPGM QGPL);  Call SalesPGM/OrderAddrD(4);
COMPANYORDERNOORDERDATEADDRESSNONAME1CITY
2 110 04/25/2012 4 Rathaus Center Hamburg

如果已经使用 SQL 命名 规范创建了 ORDERADDRD 例程,那么清单 8 中的存储过程调用将会失败。原因在于,使用 SQL 命名规范时,系统不会搜索库列表,而 ORDER_HEADER 和 ADDRESS_MASTER 表则分别位于不同的模式之中。


运行静态 SQL 语句

静态 SQL 语句是硬编码的,因此 DB2 会在 编译时 分析此类语句。新创建的程序或例程对象将确定和存储 默认模式 等关于 SQL 语句的信息。编译时使用的命名规范决定了如何为静态 SQL 语句中的非限定引用计算默认模式。

静态 SQL 语句的默认模式可以通过显式指定预编译命令的 DFTRDBCOL(默认集合 / 模式)参数来手动控制,指定 SET OPTION 语句或子句的 DFTRDBCOL 参数也能达到同样的效果。

如果通过此前已使用 SET SCHEMA 语句或者接口设置指定了默认模式值的 SQL 接口来一个创建 SQL 例程,那么 DB2 for i 将自动为 SQL 例程的定义添加一个带有 DFTRDBCOL 参数的 SET OPTION 子句。在这种情况下,静态 SQL 语句中非限定引用的解析会间接受到 SET SCHEMA 语句的影响。

尽管默认模式是在编译时确定的,但系统不会检查非限定 DB2 对象引用是否存在。即便系统中并不存在引用的表或视图,也会成功生成 SQL 例程或程序。

运行使用系统命名的静态 SQL 语句

对于使用系统命名规范创建的 SQL 例程或程序中内嵌的静态 SQL 语句,为查找非限定数据库引用,DB2 将搜索 库列表 的运行时定义列表,即便通过指定 SQL 命名的接口调用该例程时也是如此。

清单 9 展示了在使用系统命名的 SALESPGM 模式中创建 ORDERADDR 过程的 SQL 脚本。ORDERADDR 过程运行与 ORDERADDRD 例程中相同的 SQL 语句(如 清单 7:使用动态 SQL 的 ORDERADDRD 例程 中所示),但这一次使用的是静态 SQL 语句,而非动态 SQL。

清单 9:包含使用系统命名创建的静态 SQL 语句的 ORDERADDR 例程
 Create Procedure SalesPGM/OrderAddr 
          (In ParAddressNo Integer) 
       Dynamic Result Sets 1 
       Language SQL 
 Begin 			
    Declare CsrC01 Cursor For 
              Select Company,   OrderNo, OrderDate, 
                     AddressNo, Name1,   City 
                 From Order_Header Join Address_Master 
                      using(AddressNo) 
                 Where AddressNo = ParAddressNo; 			
    
    Open CsrC01 ; 			
 End;

OrderAddr 存储过程是在使用 系统命名 的环境中创建的,因此运行时将搜索库列表,以解析 SELECT 语句中的非限定对象引用。

命名规范以及有关 SQL 例程或嵌入式 SQL 程序的其他属性可使用 PRTSQLINF(打印 SQL 信息)命令或通过访问 QSYS2 中的 SYSPROGRAMSTAT 目录视图来确定。

清单 10:使用系统该命名创建的 SQL 例程内的静态 SQL 展示了 ORDERADDR 过程的两个调用,以及在使用 SQL 命名的环境中返回的结果集。请务必牢记,在使用系统该命名的环境中调用该存储过程将会得到相同的结果,因为 DB2 使用了在编译时为 SQL 例程或程序指定的命名规范。

首先,使用 CHGLIBL 命令显式设置库列表。另外指定默认模式值 SALESDB02。调用存储过程时,系统会搜索库列表,同时忽略指定的 SALESDB02 模式,因为该过程在创建时使用的是系统命名规范。ORDER_HEADER 表是在 SALESDB01 模式中找到的,而 ADDRESS_MASTER 表是在 MASTERDB 模式中找到的。静态 Select 语句将成功运行,返回企业 1 的订单标头数据。

为了获得企业 2 的订单标头数据,需要更改库列表,将 SALESDB01 模式更换为 SALESDB02 模式。可以看到,在第二次过程调用的返回结果集中包含企业 2 的订单标头数据。

清单 10:使用系统命名创建的 SQL 例程内的静态 SQL
  CL: CHGLIBL LIBL(SALESDB01 MASTERDB SALESPGM QGPL); 
 Set Schema SALESDB02; 
 Call SalesPGM.OrderAddr(4);
COMPANYORDERNOORDERDATEADDRESSNONAME1CITY
1 120 04/26/2012 4 Rathaus Center Hamburg
1 130 04/27/2012 4 Rathaus Center Hamburg
1 140 04/24/2012 4 Rathaus Center Hamburg

  CL: CHGLIBL LIBL(SALESDB02 MASTERDB SALESPGM QGPL);  Call SalesPGM.OrderAddr(4);
COMPANYORDERNOORDERDATEADDRESSNONAME1CITY
2 110 04/25/2012 4 Rathaus Center Hamburg

运行使用 SQL 命名的静态 SQL 语句

用 SQL 命名规范创建 SQL 例程时,DB2 会根据创建 SQL 例程所用的 SQL 接口来确定默认模式。

清单 11:包含使用 SQL 命名创建的静态 SQL 的 ORDERADDR1 例程 中包含使用 SQL 命名在 SALESPGM 模式中创建 ORDERADDR1 存储过程的 SQL 脚本。这个过程会返回与 ORDERADDR 过程(清单 9:例程)相同的结果集,但源代码略有修改。这里不再连接 ORDER_HEADER 表与不同模式中的 ADDRESS_MASTER 表,而是使用 ORDER_HEADER_JOIN_ADDRESS_MASTER 视图。

在执行 CREATE PROCEDURE 语句之前,将默认模式显式设置为 SALESDB01。在本例中为 SQL 例程的 DFTRDBCOL 选项使用 SALEDB01。可通过运行 PRTSQLINF 命令或访问 QSYS2 中的 SYSPROGRAMSTAT 目录视图检查 DFTRDBCOL 参数的值。

清单 11:包含使用 SQL 命名创建的静态 SQL 的 ORDERADDR1 例程
 Set Schema SALESDB01; 

 Create Procedure SALESPGM.OrderAddr1 (In ParAddressNo Integer) 
       Dynamic Result Sets 1 
       Language SQL        
 Begin 			
    Declare CsrC01 Cursor For 
              Select Company, OrderNo, OrderDate, 
                     AddressNo, Name1, City 
                 From Order_Header_Join_Address_Master 
                 Where AddressNo = ParAddressNo; 			
    Open CsrC01 ; 			
 End;

设置 DFTRDBCOL 参数后,会使用 SALESDB01 模式 来解析 ORDERADDR1 存储过程内嵌的静态 SQL 语句中的任何非限定 DB2 对象引用。

使用系统命名规范或者 SQL 命名规范运行 ORDERADDR1 存储过程时,始终从 SALESDB01 模式中检索 ORDER_HEADER_JOIN_ADDRESS_MASTER 视图,并返回企业 1 的订单标头数据。

请注意,在 清单 12:使用 SQL 命名的存储过程内的静态 SQL 中,在调用 ORDERADDR1 存储过程之前将默认模式显式设置为 SALESDB02,该存储过程的结果集内会返回企业 1 的相关数据,这表明视图是在 SALESDB01 模式中发现的。这种行为表明,使用的是存储过程的 DFTRDBCOL 设置,而忽略了默认模式值 SALESDB02。

清单 12:使用 SQL 命名创建的存储过程内的静态 SQL
  Set Schema SalesDB02; 
 Call OrderAddr1(4);
COMPANYORDERNOORDERDATEADDRESSNONAME1CITY
1 120 04/26/2012 4 Rathaus Center Hamburg
1 130 04/27/2012 4 Rathaus Center Hamburg
1 140 04/24/2012 4 Rathaus Center Hamburg

静态 SQL 语句的默认模式是在编译时确定的,动态 SQL 语句的默认模式是在运行时确定的,因此如果同时出现静态和动态 SQL 请求,相同例程内嵌入的相同 SQL 语句可能会返回不同的执行结果。

通过向 SET OPTION 语句添加 DYNDFTCOL(动态默认模式)参数,并将其值设置为 *YES,可以强制动态 SQL 语句使用与静态 SQL 请求相同的默认模式。在嵌入式 SQL 程序中,还可以在编译命令中指定 DYNDFTCOL 参数。

清单 13 显示了 CREATE PROCEDURE 语句的摘录。静态 SQL 语句的默认模式通过 DFTRDBCOL 选项设置为 SALESDB01, 动态 SQL 语句的 DYNDFTCOL 选项设置为 *YES,强制使用与静态 SQL 语句相同的默认模式。

清单 13.带有 DYNDFTCOL 选项的 SET OPTION 语句
 Create Procedure SALESPGM.OrderAddrX (In ParAddressNo Integer) 
       Dynamic Result Sets 1 
       Language SQL        
       Set Option  DYNRDBCOL = SALESDB01,                  DYNDFTCOL = *YES       
 Begin 			
    -- Routine Body – Source Code 
 End;

SQL 触发器中的非限定数据访问

SQL 触发器是一种特殊的 SQL 例程,它与表、物理文件或视图相连接。只要在相关表或视图中插入、更新或删除了行,DB2 就会激活触发器程序。创建触发器程序时,并不一定要在静态 SQL 语句中限定 DB2 对象引用,但在创建触发器时,将会解析所有非限定 DB2 对象的模式。因此,在运行时使用不同的库列表或者默认模式设置均无法更改触发器行为。

同样,命名规范决定了 SQL 触发器内非限定表、视图或别名引用的解析方式。与其他 SQL 例程相比,如果任何引用的 DB2 表、视图或别名不存在,或者在默认模式中未找到这些表、视图或别名,则无法创建触发器。

在模式完成解析并包含于触发器程序对象中之后,就可以激活触发器并在任何环境中正确执行它,即便在库列表或默认模式未正确设置的情况下也是如此。清单 14. 使用系统命名创建的触发器 展示了 NEXT_POSITION Before Insert 触发器的 SQL 脚本,它会将 ORDER_DETAIL 表中当前订单的最大订单位置 (OrderPos) 加 10,以确定下一个订单位置。如果这是该订单的第一个位置行,则将订单位置编号设置为 10。

清单 14. 使用系统命名创建的触发器
 CL: CHGLIBL LIBL(SALESDB01 MASTERDB SALESPGM QGPL); 

 Create Trigger SALESDB01/Next_Position 
        Before INSERT on SALESDB01/ORDER_DETAIL 
        Referencing NEW as N 
        For Each Row 
        Mode DB2ROW 
    Select Coalesce(Max(OrderPos) + 10, 10) 
       into N.OrderPos 
       From Order_Detail 
       where OrderNo = N.OrderNo;

ORDER_DETAIL 表位于 SALESDB01 模式和 SALESDB02 模式中。由于当前库列表包含 SALESDB01 模式,并在该模式中找到了 ORDER_DETAIL 表,因此触发程序会成功创建,解析后的 SALESDB01 模式名称存储在触发程序对象之中。下图显示了 System i Navigator 中的触发程序定义任务为 NEXT_POSITION 触发程序返回的 SQL 语句。原始非限定表引用与解析后的模式存储在一起。

图 2:触发程序 NEXT_POSITION — 例程主体
图 2:触发程序 NEXT_POSITION — 例程主体

别名和视图

如果您的 DB2 对象分散在多个模式中,而且您必须使用 SQL 命名规范,那么有可能需要创建视图或别名来支持非限定数据访问。

别名就是一个指向表或视图的持久数据库对象,引用的对象可以处于相同模式内,也可以处于其他模式中。从 IBM i 7.1 版本开始,别名可以引用远程服务器上的对象。别名还可以引用分区表中的个别分区,或者多成员物理文件中的个别成员。别名是通过运行 CREATE ALIAS 语句创建的。如果 CREATE ALIAS 语句中引用的对象未限定,在解析模式后会将它存储在别名对象中。模式解析依赖于接口的活动命名规范,通过运行 CREATE ALIAS 语句进行解析。

SQL 视图是通过运行 CREATE VIEW 语句创建的,基于 SQL SELECT 语句。视图是一种非常强大的工具,能够简化复杂的 SQL 请求,精简源代码。基于使用非限定对象引用的 SELECT 语句创建视图时,会根据视图对象的命名规范来解析模式,并将它存储在视图对象中。如果未找到或不存在任何非限定 DB2 对象,则无法生成视图。

下面的示例中使用系统命名来创建 ORDER_HEADER_JOIN_ADDRESS_MASTER 视图。首先,执行 CHGLIBL 命令显式设置库列表。在视图定义中,ORDER_HEADER 表(位于 SALESDB01 模式或 SALESDB02 模式内)与 MASTERDB 模式内的 ADDRESS_MASTER 表连接。

清单 16. 使用系统命名创建视图
 CL: CHGLIBL LIBL(SALESDB01 MASTERDB QGPL); 

 Create View SALESDB01/Order_Header_Join_Address_Master 
    as Select OrderNo, Company, OrderType, OrderDate, 
              DelDate, DelType, AddressNo 
              a.* 
         from Order_Header h Join Address_Master a 
              using(AddressNo);

视图将会成功创建,因为在 SALESDB01 模式中找到了 ORDER_HEADER 表,而在 MASTERDB 模式中找到了 ADDRESS_MASTER 表,两个模式均包含于当前库列表之中。

解析后的模式会添加到 SELECT 语句中的适当表引用中,并存储在视图对象内。为了验证这种行为,下图展示了 Query Text,它是 System i Navigator View Definition 输出的一部分。请注意,视图定义的表引用中现在包含名为 SALESDB01 和 MASTERDB 的模式。

图 3:基于非限定对象创建的视图
图 3:基于非限定对象创建的视图

如果在使用 SQL 命名的环境中执行 清单 16. 使用系统名称创建视图 中的 CREATE VIEW 语句,那么执行将会失败,因为在解析非限定指定数据库对象时每次只能搜索一个模式。

清单 17:使用 SQL 命名的非限定数据访问视图 中,之前(在 清单 16:使用系统命名创建视图 中)创建好的 ORDER_HEADER_JOIN_ADDRESS_MASTER 视图是通过 SQL 命名进行访问的。默认模式被显式设置为 SALESDB01,以分析企业 1 的数据。由于 ORDER_HEADER_JOIN_ADDRESS_MASTER 视图是在这个模式中找到的,而视图对象现在显式引用了 SALESDB01 模式中的订单标头数据,因此 MASTERDB 模式中的地址信息将会成功返回。

清单 17. 使用 SQL 命名的非限定数据访问视图
 Set Schema SALESDB01; 

 Select  Company, OrderNo, OrderDate, 
         AddressNo, Name1, City 
    from Order_Header_Join_Address_Master;
COMPANYORDERNOORDERDATEADDRESSNONAME1CITY
1 100 04/28/2012 1 Fischer & Co Dietzenbach
1 110 04/28/2012 3 Bauer GmbH Berlin
1 120 04/26/2012 4 Rathaus Center Hamburg
1 130 04/27/2012 4 Rathaus Center Hamburg
1 140 04/24/2012 4 Rathaus Center Hamburg


访问其他数据库对象

至此,我们仅讨论了非限定数据访问。不过,在 SQL 环境中还可以调用其他一些对象,例如存储过程和用户定义的函数 (UDF) 等,而调用时可能会显式指定模式,也可能不显式指定模式。与表和视图类似,这些对象可通过分隔模式和对象加以限定,具体分隔方法取决于命名规范,系统命名会采用斜杠 (/) 进行分隔,而 SQL 命名采用圆点 (.) 进行分隔。

调用过程和函数时,若未显式指定模式,DB2 会使用 SQL 路径而非默认模式来查找过程和函数。

SQL 路径

SQL 路径是一个列表,可以包含多个模式,搜索 SQL 路径时,搜索顺序跟列表中的模式定义顺序一致。

SQL 路径的初始值取决于激活组内第一条 SQL 语句使用的命名规范。如果第一条 SQL 语句采用系统命名规范,那么 SQL 路径的初始值设置是特殊值 *LIBL。如果采用了 SQL 命名规范,那么 SQL 路径会按照以下顺序包含模式:QSYS、QSYS2、SYSPROC、SYSIBMADM 和 USER 特殊寄存器。

SQL 路径也可以通过执行 SET PATH 语句进行设置或更改。SET PATH 语句允许列举多个模式,并使用逗号作为分隔符。SET PATH 语句内显式指定的模式可能是当前库列表的一部分,也可能不是。特殊值 *LIBL 可用于将 SQL 路径设置为当前库列表,即便使用 SQL 命名规范时也是如此。

默认模式设置对 SQL 路径无任何影响,也就是说 SQL 路径中不包含默认模式。这并不会造成什么问题,因为在查找非限定表、视图或别名对象时 不会 搜索 SQL 路径。

在下面的示例中,先将当前路径指定为库列表,随后将其更改为模式列表。

清单 15. SET PATH
 SET PATH = *LIBL; 
 SET PATH = QSYS, QSYS2, SALESPGM, HAUSER, HSCOMMON10;

结束语

现在,您应该已经了解了使用系统命名规范或 SQL 命名规范访问数据库对象时的不同行为,特别是静态 SQL 语句与动态 SQL 语句对于非限定访问的不同处理方式。

由于存在这些不同的行为,因此在使用 SQL 访问数据库对象时,您应该选定一种命名规范方法。

  • 如果您处理的是典型的 IBM i 应用程序,其数据分散在多个模式中,而且总是使用库列表来解析非限定对象,那么使用系统命名或许是最佳选择。
  • 在使用系统命名规范和动态 SQL 语句时,应该避免运行 SET SCHEMA 语句。只要运行了 SET SCHEMA 语句,系统就不会再搜索库列表来查找动态 SQL 语句中的非限定表、视图或别名。
  • 如果您不愿意使用系统命名,但您的数据位于多个模式中,需要避免使用限定数据库对象,那么应该在主数据模式中创建别名或视图,将其指向其他模式中的表或视图。
  • 如果您的数据集中在单独一个模式之中,或者您的应用程序在开发时旨在使用不同的数据库系统,那么使用 SQL 命名或许是最佳的选择。

参考资料

学习

获得产品和技术

讨论

条评论

developerWorks: 登录

标有星(*)号的字段是必填字段。


需要一个 IBM ID?
忘记 IBM ID?


忘记密码?
更改您的密码

单击提交则表示您同意developerWorks 的条款和条件。 查看条款和条件

 


在您首次登录 developerWorks 时,会为您创建一份个人概要。您的个人概要中的信息(您的姓名、国家/地区,以及公司名称)是公开显示的,而且会随着您发布的任何内容一起显示,除非您选择隐藏您的公司名称。您可以随时更新您的 IBM 帐户。

所有提交的信息确保安全。

选择您的昵称



当您初次登录到 developerWorks 时,将会为您创建一份概要信息,您需要指定一个昵称。您的昵称将和您在 developerWorks 发布的内容显示在一起。

昵称长度在 3 至 31 个字符之间。 您的昵称在 developerWorks 社区中必须是唯一的,并且出于隐私保护的原因,不能是您的电子邮件地址。

标有星(*)号的字段是必填字段。

(昵称长度在 3 至 31 个字符之间)

单击提交则表示您同意developerWorks 的条款和条件。 查看条款和条件.

 


所有提交的信息确保安全。


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=10
Zone=IBM i
ArticleID=856202
ArticleTitle=系统命名与 SQL 命名之争 — 第 2 部分
publish-date=03042013