用ado对象访问数据库_访问数据库对象

本文研究了使用系统和SQL命名约定运行SQL语句时的不同行为。 本文的第一部分着重于在使用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在带有嵌入式SQLSQL例程或应用程序中大量使用。 静态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语句显示了创建UD_COUNT_NUMBER_OF_ROWS UDFSQL脚本。 使用此功能，可以确定任何表，视图或别名中的行数。 表（或视图或别名）名称以及架构名称作为参数值传递。 调用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命令行处理器（例如IBM Systemi®Navigator Run）运行SQL语句。脚本界面或RUNSQLSTM和RUNSQL命令行命令。 清单3：以交互方式发布的动态SQL语句显示了使用System i Navigator运行脚本界面运行的动态SQL语句。

清单3：交互式发出的动态SQL语句

Select * from Order_Header;

确定默认架构

当SQL语句包含不合格的表，视图或别名引用时，DB2必须确定缺省模式并搜索该模式。 Schema是类似于IBM i库SQL术语。 默认模式的初始值取决于SQL环境中使用的命名约定以及所运行SQL语句是静态还是动态 。

静态SQL语句的默认架构

使用嵌入式SQL时，可以使用DFTRDBCOL（默认集合）参数在编译命令（CRTSQLxxxI）中显式设置静态SQL语句的默认模式。 另外，带有DFTRDBCOL参数的SET OPTION语句也可以包含在源代码中。

在嵌入式SQL程序中，即使源代码由几个独立的（导出）过程组成，也只能指定为单个SET OPTION语句。 SET OPTION语句必须作为第一个SQL语句放在源代码中。

清单4：嵌入在RPG中的SET OPTION语句显示了带有SET OPTION语句的RPG源摘录，该语句将命名约定设置为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;

如果未明确设置默认模式，则在编译时根据命名约定确定默认模式。

• 对于系统命名 ，默认模式是作业库列表 （* LIBL）

使用系统命名时，术语架构可能会产生误导，因为初始值设置为特殊值* LIBL。 此特殊值表示在尝试解析不合格的对象引用时，将使用库列表，并且可以搜索多个模式。 将使用在第一个库中找到的第一个DB2对象，该对象与未限定的指定数据库对象名称和对象类型匹配。 无需任何架构规范，就可以在同一SQL语句中访问位于不同架构中的数据库对象。

• 对于SQL命名，将采用创建SQL例程SQL环境中当前使用的默认架构 。

因为没有通过SQL接口创建具有嵌入式SQL的应用程序，所以将静态SQL语句的默认架构设置为运行时授权ID。 在IBM i上，运行时授权ID是执行编译的作业的用户概要文件。 这意味着SQL命名的默认行为是让DB2尝试在模式中查找与创建者的用户配置文件同名的不合格对象。

解决不合格的DB2对象引用时，SQL命名仅允许搜索单个模式 。

动态SQL语句的默认架构

对于动态SQL语句，默认架构取决于是否已明确指定默认架构值。 如果未显式设置默认模式，则其初始值取决于命名约定。

• 对于系统命名 ，默认模式是作业库列表 （* 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（改变当前库）命令添加到当前库列表列表的用户部分之前。 仅当使用系统命名时才能访问库列表。 因此，只能使用系统命名来搜索当前库 。

当前架构（或默认架构）是当前库列表（系统命名）或单个架构（SQL命名），它可能是也可能不是当前库列表的一部分。

SET SCHEMA语句和动态SQL接口

通过为该动态SQL接口指定默认模式值，许多DB2 for i SQL接口可以代表您自动执行SET SCHEMA语句。 指定默认架构值的机制取决于接口。

• IBM System i Navigator运行SQL脚本工具

可以通过单击“ 连接 JDBC设置”来预设默认模式。 可以在“系统”选项卡上指定默认模式或库列表，如下图所示。

图1：System i Navigator运行SQL脚本–设置默认架构

• 命令行命令：RUNSQLSTM和RUNSQL

可以使用DFTRDBCOL（默认集合）参数在这些SQL命令上指定默认模式。

• ODBC连接

可以使用-ODBC管理定义ODBC连接。 使用ODBC访问表和视图时，可以从IBM i Access for Windows ODBC管理界面或通过设置DefaultLibraries连接关键字以编程方式设置默认模式。

• SQL调用级别接口（CLI）

使用SQL CLI函数时，可以通过设置SQL_ATTR_DEFAULT_LIB或SQL_ATTR_DBC_DEFAULT_LIB环境或连接变量来显式指定要使用的架构。

• Java ™ 数据库连接（JDBC）或Java的结构化查询语言（SQLJ）

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

• 使用IBM i Access系列OLE DB提供程序的OLE DB

可以通过“连接对象属性”中的DefaultCollection显式指定默认模式。

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

可以通过“连接对象属性”中的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语句

在以下示例中，让我们使用System i Navigator Run Script工具作为我们的动态SQL界面，检查以System或SQL命名组合运行动态SQL语句时的不同行为。

使用系统命名进行不合格的数据访问

在使用系统命名且未显式设置默认架构的环境中执行Dynamic SQL语句时，将搜索当前库列表以查找所有不合格的表和视图。

库列表最初是基于作业描述在任何SQL界面中设置的。 但是，可以通过运行诸如CHGLIBL（更改库列表）或ADDLIBLE（添加库列表条目）之类的命令来修改库列表。 通过运行SET SCHEMA语句更改默认架构时，当前库列表将被忽略，而将搜索新设置的（单个）架构。

下面的示例通过执行几个SQL语句来演示此行为。

在运行第一个SELECT语句之前，通过执行CHGLIBL（更改库列表）命令来显式设置库列表。 在SELECT语句中，位于SALESDB01或SALESDB02模式中的ORDER_HEADER表与位于MASTERDB模式中的ADDRESS_MASTER表连接在一起。

SALESDB01和MASTERDB模式都是当前库列表的一部分。 因此，找到两个表并成功执行了SELECT语句。 由于在SALESDB01模式中找到了ORDER_HEADER表，因此返回了公司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);

公司	订单号	订购日期	地址号码	NAME1	市
1个	100	1个	费歇尔 有限公司	瓦尔德·维森格16	迪岑巴赫
1个	110	3	宝华有限公司	诺丁斯417	柏林
1个	120	4	拉特豪斯中心	Hauptstr。 3	汉堡
1个	130	4	拉特豪斯中心	Hauptstr。 3	汉堡
1个	140	4	拉特豪斯中心	Hauptstr。 3	汉堡

要为公司2检索相同的信息，必须将SALESDB02模式添加到库列表中。 可以删除SALESDB01模式，或者必须将其放置在库列表中的SALESDB02模式之后。 必须通过运行命令（例如CHGLIBL或ADDLIBLE）来更改库列表。

如果通过运行SET SCHEMA语句设置了SALESDB02模式，则默认模式值将从* LIBL更改为SALESDB02。 在此更改后重新运行SELECT语句时，执行失败并显示SQLSTATE 42704，因为在SALESDB02模式中找不到位于MASTERDB模式中的ADDRESS_MASTER表。

使用SQL命名进行不合格的数据访问

在使用SQL命名的环境中，在运行时仅搜索一个架构来解析不合格的表，视图和别名。 执行清单6：使用SQL命名约定进行系统命名的多个模式中的数据时 ，SELECT语句将始终失败，SQLSTATE值为42704，因为ADDRESS_MASTER和ORDER_HEADER表位于不同的模式中。

使用SQL命名约定时，必须限定不同模式中的数据库对象，或者必须通过默认模式中的别名或视图来访问它们。 别名或视图可以引用不同架构中的表或视图。

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

嵌入在SQL例程或程序中的动态SQL语句遵循相同的规则来解析不合格的对象引用。 但是，动态SQL语句使用创建 SQL例程或程序时激活的命名约定， 而不使用运行SQL例程或程序时指定的命名约定。

例如，如果使用SQL命名约定创建了SQL存储过程，则该过程中的动态SQL语句将使用SQL命名规则来解析不合格的名称，即使该SQL存储过程是从使用系统命名约定。

可以在编译时通过compile命令中的OPTION参数或通过将SET OPTION语句嵌入源代码中来定义用于具有嵌入式SQL的程序的命名约定。 SQL例程继承了用于创建SQL例程SQL接口的命名约定。 即使可以将SET OPTION语句嵌入SQL例程中，也不允许指定NAMING选项。

通过运行命令（用于修改库列表）或执行源代码中嵌入的SET SCHEMA语句，可以在例程或程序中显式设置运行时用于动态SQL语句的默认模式。

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

以下SQL脚本在使用系统命名约定的环境中创建存储过程ORDERADDRD。 该存储过程接受单个参数（ParAddressNo），并返回所有订单标头和地址信息作为该特定地址的结果集。 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命令显式设置库列表。 因为使用系统命名来创建过程，所以将搜索库列表以查找不合格的引用。 在SALESDB01模式中找到ORDER_HEADER表，而在MASTERDB模式中找到ADDRESS_MASTER表。 结果集包含来自公司1的数据，证明了已使用SALESDB01模式中的ORDER_HEADER表的信息。

若要获取公司2的订单抬头数据，请在调用存储过程之前使用CHGLIBL命令更改库列表 。

清单8：带有系统命名SQL例程中的动态SQL

CL: CHGLIBL LIBL(SALESDB01 MASTERDB SALESPGM QGPL); Call SalesPGM/OrderAddrD(4);

公司	订单号	订购日期	地址号码	NAME1	市
1个	120	2012年4月26日	4	拉特豪斯中心	汉堡
1个	130	2012年4月27日	4	拉特豪斯中心	汉堡
1个	140	2012年4月24日	4	拉特豪斯中心	汉堡

CL: CHGLIBL LIBL(SALESDB02 MASTERDB SALESPGM QGPL); Call SalesPGM/OrderAddrD(4);

公司	订单号	订购日期	地址号码	NAME1	市
2	110	2012年4月25日	4	拉特豪斯中心	汉堡

如果使用SQL命名约定创建了ORDERADDRD例程，则清单8中的存储过程调用将失败。 这是因为，按照SQL命名约定，不会搜索库列表，并且ORDER_HEADER和ADDRESS_MASTER表位于不同的架构中。

运行静态SQL语句

由于静态SQL语句是硬编码的，因此DB2 在编译时会对它们进行分析。 确定有关SQL语句的信息（例如默认架构 ）并将其存储在新创建的程序或例程对象中。 编译时使用的命名约定确定如何为静态SQL语句上的非限定引用计算默认架构。

静态SQL语句的默认架构也可以通过在precompile命令中显式指定DFTRDBCOL（默认集合/架构）参数或在SET OPTION语句或子句中指定DFTRDBCOL参数来手动控制。

如果从以前使用SET SCHEMA语句或接口设置指定默认模式值SQL接口创建了SQL例程，则DB2 for i将自动将带有DFTRDBCOL参数的SET OPTION子句添加到SQL例程定义中。 在这种情况下，SET SCHEMA语句间接影响静态SQL语句上不合格引用的解析。

即使在编译时确定了缺省模式，也不会检查不合格的DB2对象引用是否存在。 即使系统上不存在引用的表或视图，也可以成功生成SQL例程或程序。

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

为了找到嵌入在用系统命名约定创建SQL例程或程序中的静态SQL语句的不合格数据库引用，即使从指定SQL命名的接口调用例程，DB2也会搜索库列表的运行时定义。

清单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语句上不合格的对象引用。

可以使用PRTSQLINF（打印SQL信息）命令或访问QSYS2中的SYSPROGRAMSTAT目录视图来确定有关SQL例程或嵌入式SQL程序的命名约定和其他属性。

清单10：使用系统命名创建SQL例程中的静态SQL显示了对ORDERADDR过程的两次调用以及从使用SQL命名的环境返回的结果集。 请记住，在使用系统命名的环境中调用存储过程将得到相同的结果，因为DB2使用在编译时为SQL例程或程序指定的命名约定。

首先，使用CHGLIBL命令显式设置库列表。 此外，默认架构已分配给值SALESDB02。 调用存储过程时，将搜索库列表，而忽略指定的SALESDB02模式，因为该过程是使用系统命名约定创建的。 在SALESDB01模式中找到ORDER_HEADER表，在MASTERDB模式中找到ADDRESS_MASTER表。 静态Select语句成功运行，并返回公司1的订单抬头数据。

要获取公司2的订单抬头数据，请更改库列表，将SALESDB01模式替换为SALESDB02模式。 从第二次调用过程返回的结果集中可以看到，返回了公司2的订单抬头数据。

清单10：使用系统命名创建SQL例程中的静态SQL

CL: CHGLIBL LIBL(SALESDB01 MASTERDB SALESPGM QGPL); Set Schema SALESDB02; Call SalesPGM.OrderAddr(4);

公司	订单号	订购日期	地址号码	NAME1	市
1个	120	2012年4月26日	4	拉特豪斯中心	汉堡
1个	130	2012年4月27日	4	拉特豪斯中心	汉堡
1个	140	2012年4月24日	4	拉特豪斯中心	汉堡

CL: CHGLIBL LIBL(SALESDB02 MASTERDB SALESPGM QGPL); Call SalesPGM.OrderAddr(4);

公司	订单号	订购日期	地址号码	NAME1	市
2	110	2012年4月25日	4	拉特豪斯中心	汉堡

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

当使用SQL命名约定创建SQL例程时，DB2根据用于创建SQL例程SQL接口确定缺省模式。

清单11：使用SQL命名创建的带有静态SQL的例程ORDERADDR1包含SQL脚本，该脚本在具有SQL命名的SALESPGM模式中创建ORDERADDR1存储过程。 该过程返回与ORDERADDR过程相同的结果（ 清单9：例程 ），但是对源代码进行了一些修改。 使用ORDER_HEADER_JOIN_ADDRESS_MASTER视图代替连接位于不同架构中的ORDER_HEADER表和ADDRESS_MASTER表。

在执行CREATE PROCEDURE语句之前，默认架构已显式设置为SALESDB01。 在这种情况下，SALEDB01用于SQL例程中的DFTRDBCOL选项。 可以通过运行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，并且存储过程的结果集中返回了Company 1的数据，表明：该视图在SALESDB01模式中找到。 此行为表明，使用了存储过程的DFTRDBCOL设置，而忽略了SALESDB02的默认架构值。

清单12：使用SQL命名创建的存储过程中的静态SQL

Set Schema SalesDB02; Call OrderAddr1(4);

公司	订单号	订购日期	地址号码	NAME1	市
1个	120	2012年4月26日	4	拉特豪斯中心	汉堡
1个	130	2012年4月27日	4	拉特豪斯中心	汉堡
1个	140	2012年4月24日	4	拉特豪斯中心	汉堡

由于静态SQL语句的默认架构是在编译时确定的，而动态SQL语句的默认架构是在运行时确定的，因此，如果在同一例程中嵌入的同一SQL语句同时执行，则返回的结果可能不同。和动态SQL请求。

通过将设置为* YES的DYNDFTCOL（动态默认架构）参数添加到SET OPTION语句，动态SQL语句将被迫使用与静态SQL请求相同的默认架构。 在嵌入式SQL程序中，也可以在compile命令中指定DYNDFTCOL参数。

清单13：显示了CREATE PROCEDURE语句的摘录。 使用DFTRDBCOL选项将静态SQL语句的默认模式设置为SALESDB01，通过将DYNDFTCOL选项设置为* YES，将动态SQL语句强制使用与静态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插入触发器之前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 –例程主体

别名和观点

如果您的DB2对象分布在多个模式上，并且您必须使用SQL命名约定，那么您可能需要创建视图或别名来支持不合格的数据访问。

别名是一个永久性数据库对象，它指向表或视图，引用的对象可以在相同模式或不同模式中。 从IBM i 7.1发行版开始，别名可以引用远程服务器上的对象。 别名也可以引用分区表的单个分区或多成员物理文件的成员。 通过运行CREATE ALIAS语句创建别名。 如果CREATE ALIAS语句上的引用对象不合格，则解析该架构并将其存储在别名对象中。 模式解析取决于在接口上活动并运行CREATE ALIAS语句的命名约定。

通过运行CREATE VIEW语句创建一个SQL视图，该视图基于SQL SELECT语句。 视图是一种非常强大的工具，可以简化复杂SQL请求并减少源代码。 当基于带有不合格对象引用的SELECT语句创建视图时，将根据命名约定解析架构并将其存储在视图对象中。 如果找不到或不存在任何不合格的DB2对象，则不会生成该视图。

在下面的示例中，系统命名用于创建ORDER_HEADER_JOIN_ADDRESS_MASTER视图。 First, the library list is explicitly set by executing the CHGLIBL command. In the view definition, the ORDER_HEADER table (which is either located in the SALESDB01 schema or the SALESDB02 schema) is joined with the ADDRESS_MASTER table, which is located in the MASTERDB schema.

Listing 16: Create a view with system naming

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);

The view is created successfully because the ORDER_HEADER table is found in the SALESDB01 schema while the ADDRESS_MASTER table is found in the MASTERDB schema, and both the schemas are included in the current library list.

The resolved schemas are added to the appropriate table references in the SELECT statement and stored in the view object. To prove this behavior, the following figure shows the Query Text, which is part of the System i Navigator View Definition output. Notice how the view definition now contains the schema names of SALESDB01 and MASTERDB on the table references.

Figure 3: View created based on unqualified objects

If the CREATE VIEW statement in Listing 16: Create a view with system naming is executed in an environment where SQL naming is used, it will fail, because only a single schema can be searched at a time to resolve the unqualified specified database objects.

In Listing 17: Unqualified data access view with SQL naming the ORDER_HEADER_JOIN_ADDRESS_MASTER view created previously (in Listing 16: Create a view with system naming ) is accessed with SQL naming. The default schema is explicitly set to SALESDB01 to analyze the data for company 1. Because the ORDER_HEADER_JOIN_ADDRESS_MASTER view is found in this schema and the view object now explicitly references the order header data located in the SALESDB01 schema, the address information located in the MASTERDB schema can be successfully returned.

Listing 17: Unqualified data access view with SQL naming

Set Schema SALESDB01; Select Company, OrderNo, OrderDate, AddressNo, Name1, City from Order_Header_Join_Address_Master;

COMPANY	ORDERNO	ORDERDATE	ADDRESSNO	NAME1	市
1个	100	04/28/2012	1个	Fischer & 有限公司	Dietzenbach
1个	110	04/28/2012	3	Bauer GmbH	柏林
1个	120	04/26/2012	4	Rathaus Center	汉堡
1个	130	04/27/2012	4	Rathaus Center	汉堡
1个	140	04/24/2012	4	Rathaus Center	汉堡

Accessing other database objects

Until now, only unqualified data access has been discussed. However, there are also other objects such as stored procedures and user-defined functions (UDFs) that can be called in an SQL environment with or without explicitly specifying the schema. Similar to tables and views, these objects can be qualified by separating the schema and object, depending on the naming conventions with either a slash (/) for System naming or a period (.) for SQL naming.

When the invocation of procedures and functions do not explicitly specify the schema, DB2 uses the SQL path instead of the default schema to find the procedures and functions.

SQL path

The SQL path is pretty similar to a library list, where multiple schemas can be listed and are searched in the same sequence in which they are specified.

The initial value of the SQL path depends on the naming convention that is used for the first SQL statement within an activation group. If the System naming convention was used for the first SQL statement, the initial value for the SQL path is set to the special value, *LIBL. If the SQL naming convention was used, the SQL path includes the schemas in the following sequence: QSYS, QSYS2, SYSPROC, SYSIBMADM, USER special register.

The SQL path can also be set or changed by executing the SET PATH statement. The SET PATH statement allows multiple schemas to be listed and separated by a comma. The schemas explicitly specified in the SET PATH statement may or may not be part of the current library list. The special value, *LIBL, can be used to set the SQL path to the current library list, even when the SQL naming convention is used.

The default schema setting has no effect on the SQL path, which means that the default schema is not included in the SQL path . This is not an issue because the SQL path is not searched to find unqualified table, view, or alias objects.

In the following example, the current path is first assigned to the current library list and then changed to a list of schemas.

Listing 15: SET PATH

SET PATH = *LIBL; SET PATH = QSYS, QSYS2, SALESPGM, HAUSER, HSCOMMON10;

结论

You should now understand the different behaviors when accessing database objects with either the System or SQL naming convention, especially to identify how unqualified access is handled differently for static and dynamic SQL statements.

Because of these different behaviors, you should decide on a single naming convention method for accessing your database objects with SQL.

• If you are working with typical IBM i applications, where the data is spread over multiple schemas and a library list is always used to resolve unqualified objects, the usage of System naming is probably the best option.
• When working with the System naming convention and dynamic SQL statements, running the SET SCHEMA statement should be avoided. As soon as the SET SCHEMA statement is run, the library list is no longer searched to find unqualified tables, views, or aliases in dynamic SQL statements.
• If System naming is not an option for you, but your data is located in multiple schemas and you want to avoid qualifying database objects, you should create aliases or views located in your primary data schema that point to the tables or views located in other schemas.

• If your data is concentrated in a single schema or your application is developed to use different database systems, using SQL naming will be the best choice.

翻译自: https://www.ibm.com/developerworks/ibmi/library/i-system_sql2/index.html