400对系统外应用SQL调用的优化技术

400对系统外应用SQL调用的优化技术

 

目标：本文只对AS400 DB2为数据服务器的应用制定优化。

一. 400 外部SQL调用处理分析 1. 400/DB2 Engine的区分和性能差别

在OS/400 V5R3下，包括V5R3，400/DB2的Engine都是CQE。OS/400 V5R4以后的版本，DB2/400的Engine都是采用SQC类型，兼容CQE。

对相同pf记录数据，对SQL外部调用，如果用CQE Engine，需36.37秒，而采用SQE Engine 则只需0.07秒。

2. SQL调用耗费400资源，影响应用效率之处分析

在400环境下，在一个大数据记录的pf下，首次建立LF，或关联文件（join file），400系统要耗费一定的时间进行创建。经验表明，一个10G左右的数据量的pf下，建立一个LF（index）需要花费系统需10多分钟的时间。如果，sql存在复杂的临时创建关联文件，这个创建过程就需要更长时间。

外部应用使用SQL 语句调用400数据库时，如java前端应用，必须占用400的运行资源。下面外部应用sql对400数据库进行操作时，有两种影响应用效率的类型：

1)       关联文件

如果sql语句中包含创建关联文件，无论是左关联，还是右关联，400系统首先必须耗费时间创建这个临时文件。创建这个临时关联文件，需要遍历pf的所有记录。这样对大数据记录的pf创建关联文件，就要较长时间占用一定的400系统资源，对终端操作用户会造成明显的延时效应。

2)       PF数据文件

外部应用直接对400下PF数据文件进行操作，如果sql语句中存在where条件判断筛选键字，相当于执行这条sql语句时，临时创建一个index文件，如果PF不带key。同样创建index过程需要占用一定的时间。如果PF已经存在大数据记录，就会严重影响应用的运行效率。

二. 400/DB2改善SQL调用策略和实现方法 1. 400环境下对外部SQL调用性能改善策略

1）  OS/400系统性能提升

 400硬件平台的提升和DB2/400 Engine的改善会大大提高外部应用的SQL调用运行性能。

2）  充分利用400存在的index文件，预先创建条件筛选的数据集

通过存在的index文件（如LF、JFile、400/QueryFile），或预先创建条件筛选的数据集，跳过外部应用SQL调用时进行创建index的过程，从而提高外部应用的运行效率。

3）  利用400环境下ILE（集成化语言环境）的优势

在400 ILE环境下，可以利用每一种ile开发语言的特点，做成UDF，User Defined Function。采用UDF有如下优点：

l        UDF可以直接用在sql脚本中进行调用处理；

l        如果对相对固定的输入参数的UDF，400只对这个UDF的第一次调用进行运算。如果外部应用再次用sql进行相同输入参数的调用，OS/400就自动跳过对这个UDF的执行，而直接采用上次调用的结果，从而提高应用运行效率。另一方面，UDF可以用400服务程序实现。OS/400系统首次对服务程序的调用就装入物理内存，接下去对这个UDF调用，就无需调入物理内存，而直接运行。

2. 实现方法（待续）

1）  转化方法

在进行基于400/DB2为数据库的外部应用sql优化中，我们应该首先考虑如何在外部应用SQL调用语句中充分采用400下已经存在的LF、JFile、QueryFile。把外部应用SQL调用对不带索引键字的PF操作，转换成对400已经存在的上述三类文件的操作。

2）  存储过程

 经典的存储过程调用，采用利用400环境下已经存在的query文件，即已经存在的index（LF、query）文件，忽略sql调用时创建临时筛选文件，如上述的两种类型的文件，进行操作，用外部sql对数据库的操作，转化为存储过程对数据库的操作，从而提高外部应用的运行效率。

采用存储过程的优点：

l        容易编码；

l        能够在传参列表中返回结果数据。对基于ODBC、JDBC、CLI模式调用，可以返回结果数据集。

采用存储过程的弊端：

如果大量采用存储过程，经过项目的重叠开发，不断积累的存储过程，交叉度不断增大、复杂化。对新项目，新增和更改存储过程脚本，会增加开发分析和实施的难度。

3）  调用RPGLE程序

调用RPGLE程序，作为应用后端，即400端的应用逻辑处理。

把前端应用sql调用后端400数据操作的处理部分，由400端的RPGLE程序完成，返回处理结果数据到前端400外部应用。

采用这种策略原理是利用RPGLE程序中定义已经存在的LFs和JFiles（逻辑文件、联接文件），通过直接定位（chain，或reade等）到操作记录位置。这些LFs和JFiles都是OS/400系统自动实时维护的。所以不必再需要对数据库表（PFs）进行关联，或索引临时创建，从而提高应用的运行效率。

对应SQL语句的动态where筛选条件，相应的400 就要创建一个where筛选条件的LF文件。最好充分利用400环境中已经存在的LF或JFfile，或利用存在的LF创建JFile。

才用这种方法的弊端：

l        如果要改动sql筛选数据的处理逻辑，RPGLE程序要做相应的修改操作。

l        如果前端应用是并发处理，后台的RPGLE程序就要做相应的编程处理。

l        在400环境下，基于一个PF的LF数不宜过多，最好不要超过10个。

注：利用400已经存在的LF创建JFile，对系统维护不会较大影响系统性能。

4）  UDF（User Defined Function）

通常情况下，UDF作为sql语句对筛选记录字段算法处理最适用。复杂情况下，也可用UDF做RPGLE程序直接做后台数据处理。

采用UDF的弊端：

如果sql对筛选记录有字段数据处理逻辑的更新要求，替代这些处理的UDF也要做相应的更新。

5）  MQT(Materialized Query Tables)

MQT是OS/400 v5r3下DB2/400的新功能。在v5r3版下，如果要用MQT组成较复杂的关联表，在这个关联表中只能用一个MQT。V5r4以上的版本，这类操作，允许多个MQT用在关联表中。

使用MQT的主要原理和优点：

把SQL中复杂的关联关系预先从数据库表（Table/PF）中筛选出数据形成数据集存放在存储空间上，以便外部应用SQL调用400数据库查询操作时，可以快速地从这个数据集中找到匹配记录，或匹配记录集。即，预先生成筛选的数据集操作省略了sql检索操作时间。

在OS/400 V6版本以下，MQT数据集与筛选生成这个MQT的数据库表的数据同步，必须在SQL操作调用时，进行人为的sql命令驱使同步。

MQT不足之处：

l        只能用于数据快速检索，不能用于记录数据的改动类操作，如update、insert/write、delete。

l        对sql语句中带有where筛选条件，即动态的筛选条件，不适合采用MQT技术。

l        采用MQT的快速检索的代价是额外生成数据集，多占用存储空间。