1. technology and computing
2. software
3. databases

IBM中国系统与科技研发中心 --- IBM i 实验室之旅深圳站
DB2 for IBM i – 7.2 新功能和最新TR更新
郭琦([email protected])
IBM中国系统与科技研发中心
© 2014 IBM Corporation
IBM中国系统与科技研发中心 --- IBM i 实验室之旅深圳站
日程
• DB2 7.2 新功能简介
• 7.1 TR8, TR9 和 7.2 TR1 新功能简介
2
© 2014 IBM Corporation
IBM中国系统与科技研发中心 --- IBM i 实验室之旅深圳站
日程
• DB2 7.2 新功能简介
• 7.1 TR8, TR9 和 7.2 TR1 新功能简介
3
© 2014 IBM Corporation
IBM中国系统与科技研发中心 --- IBM i 实验室之旅深圳站
DB2 for i
• 标准兼容
• 安全性
• 可扩展性
• 功能强大
• 优异性能
• 易用性
• 易于维护
7.2
Row and Column
Access Control
XMLTABLE
7.1
核心价值
6.1
Omnifind
V5R4
WebQuery
V5R2
SQE Stage 1
V5R1
SQL triggers
Java Functions
DRDA DUW TCP/IP
2 GB LOBs
1 Terabyte Table
Journal Minimal Data
Two-phase over
TCP/IP
4
CONNECT BY
XML Support
IASPs
Identity columns
Savepoints
UNION in views
Scalar subselect
UDTFs
DECLARE GLOBAL
TEMPORARY
TABLE
Catalog views
V5R3
SSD Memory
Preference
Partitioned tables
On Demand
Performance Center
UFT-8 and UTF-16
ICU sort sequence
Health Center
MQTs
Completion of SQL
Core
Sequences
Scalar fullselect
Implicit char/numeric
Recursive CTE
BINARY/VARBINARY INSTEAD OF triggers
GET DIAGNOSTICS Descriptor area
DRDA Alias
XA over DRDA
DECIMAL(63)
DDM 2-phase
DECFLOAT
Grouping sets
/super groups
Named arguments
Result set support in and defaults for
embedded SQL
parameters
CURRENTLY
COMMITTED
INSERT in FROM
Global variables
VALUES in FROM Array support in
Extended Indicator procedures
Variables
Partition table
enhancements
Expression in
Indexes
Three-part names
and aliases
ROW CHANGE
TIMESTAMP
SQE Logical file
Statistics catalog support
views
SQE Adaptive Query
Processing
CLIENT special
registers
EVI enhancements
2M SQL statement
DDM and DRDA
IPv6
CREATE OR
REPLACE
1000 tables in a
query
Deferred Restore of
MQT and Logicals
Environmental limits
Scrollable cursor
QDBRPLAY
DDL Journaling
JDBC V3.0
Database Navigator
DRDA Kerberos
SQE Stage 5
Generate SQL
Journal Standby
Implicit journaling
enhancements
Array support in
UDFs
MQ Functions
Inline functions
Ragged SWA
Obfuscation of SQL
routines & triggers
MERGE
SQE Stage 6
SQE Stage 3
Online Reorganize
MySQL storage
engine
TRANSFER
OWNERSHIP
Encryption
enhancements
(FIELDPROCs)
Timestamp precision
Multiple-action
Triggers
Built-in Global
Variables
Record movement
between partitions on
UPDATE
1.7 Terabyte Indexes
Health Center – Nondatabase limits
Navigator Graphing
and Charting
SQE I/O Costing
model improvement
TRUNCATE
© 2014 IBM Corporation
IBM中国系统与科技研发中心 --- IBM i 实验室之旅深圳站
DB2 for i 7.2 新功能要点
•
•
•
数据安全
–
–
–
–
Row and Column Access Control (RCAC)
TRANSFER OWNERSHIP
GRANT TO GROUP / USER
Remote journal and SSL
应用开发
–
–
–
–
–
–
–
–
Named augment and default
Autonomous Procedures
Obfuscation of SQL routines
Timestamp precision (0 – 12)
Record movement between partitions
Multiple-action Triggers ( PTFed to 7.1 )
Dynamic Compound Statement ( PTFed to 7.1 )
TRUNCATE TABLE
数据库性能
– SQE support of native (QRY/400 and OPNQRYF and
RCAC)
– Use Maintained Temporary Indexes (MTIs) for
Statistics (PTFed to 6.1)
– 数据库可用性和备份/恢复
– Deferred journal restore
5
© 2014 IBM Corporation
IBM中国系统与科技研发中心 --- IBM i 实验室之旅深圳站
行列访问控制 Row and Column Access Control (RCAC)
• IBM Advanced Data Security for i (Boss option
47)
• 行级别访问控制
– 根据用户所在的组（或其他条件）控制用户是否能看到表
中的所有或者部分记录
• 列级别访问控制
– 根据用户所在的组（或其他条件）控制用户是否能看到列
中的真实数据或者经过掩码后的数据
6
© 2014 IBM Corporation
IBM中国系统与科技研发中心 --- IBM i 实验室之旅深圳站
RCAC 示例
•
医疗保险行业示例
费用清算 (会计)
结算中心
数据库
管理用
户
管理部门
保险公司
数据源
医院 (医生、病人)
政府及研究机构
工作人员
• 确保健康状况信息的私密性
• 保证电子数据传输中的机密性
数据库
访问用
户
• 控制数据只能被有相应权限的用户访问
• 记录对受保护的数据的访问
7
© 2014 IBM Corporation
IBM中国系统与科技研发中心 --- IBM i 实验室之旅深圳站
Row Permission & Mask 示例
•
8
创建 Permission
© 2014 IBM Corporation
IBM中国系统与科技研发中心 --- IBM i 实验室之旅深圳站
Row Permission & Mask 示例
•
医生 Lee 在 Permission 规则上更新数据
UPDATE patient SET pharmacy = ‘codeine’ WHERE name = ‘Sam’
UPDATE patient SET pharmacy = ‘codeine’ WHERE name = ‘Doug’
9
© 2014 IBM Corporation
IBM中国系统与科技研发中心 --- IBM i 实验室之旅深圳站
Row Permission & Mask 示例
在 Permission 规则上访问数据
•
select * from patient
医生Lee
select * from patient
select * from patient
会计与市场分析人员
10
© 2014 IBM Corporation
IBM中国系统与科技研发中心 --- IBM i 实验室之旅深圳站
Row Permission & Mask 示例
•
创建 Mask
CREATE MASK pid_mask ON patient FOR
COLUMN pid RETURN
CASE
WHEN
VERIFY_GROUP_FOR_USER (SESSION_USER, ‘PATIENT’ ) = 1
THEN pid
ELSE
‘XXX XXX ‘ || SUBSTR (pid, 8, 3)
END
ENABLE;
ALTER TABLE patient ACTIVATE COLUMN ACCESS CONTROL;
11
© 2014 IBM Corporation
IBM中国系统与科技研发中心 --- IBM i 实验室之旅深圳站
Row Permission & Mask 示例
•
在 Permission & Mask 规则上访问数据
select * from patient
医生Lee
select * from patient
12
病人Bob
© 2014 IBM Corporation
IBM中国系统与科技研发中心 --- IBM i 实验室之旅深圳站
行列访问控制 Row and Column Access Control (RCAC)
•
由 Security Admin 使用 SQL 语法定义
–
–
•
对所有数据存取接口都有效 (SQL, RPG Native, JDBC, CPYF, etc)
•
RCAC 保存在数据表内部 (*FILE)，并且RCAC的相关数据访问运行在 SQE
•
可以直接应用在物理文件上，对现有应用无需任何改动
•
CURRENT_USER 特殊寄存器(special register)可以获取当前用于验证权限的
用户ID
–
•
Adopted authority被考虑在内
可以通过使用Client Special Register将应用级别用户信息传至数据库服务器端
用于RCAC规则验证
–
–
13
用户功能组 QIBM_DB_SECADM
*ALLOBJ权限无法更改RCAC
CURRENT CLIENT_APPLNAME ， CURRENT CLIENT_USERID …
http://ibmsystemsmag.com/Blogs/i-Can/Archive/i-can-use-client-special-registers/
© 2014 IBM Corporation
IBM中国系统与科技研发中心 --- IBM i 实验室之旅深圳站
CURRENT_USER 特殊寄存器(special register)
CURRENT USER特殊寄存器保存了用于验证当前执行语句权限的用户名。
换言之，如何当前程序继承了所有者权限，它返回被继承的用户名。
如果当前线程有多个被继承的用户名，它返回最近一次被继承权限的用户名
SELECT CURRENT_USER FROM SYSIBM.SYSDUMMY1
CREATE MASK SSN_MASK ON EMPLOYEE
FOR COLUMN SSN RETURN
CASE
WHEN (VERIFY_GROUP_FOR_USER(CURRENT_USER,'PAYROLL') = 1)
THEN SSN
WHEN (VERIFY_GROUP_FOR_USER(CURRENT_USER,'MGR') = 1)
THEN 'XXX-XX-' CONCAT SUBSTR(SSN,8,4)
ELSE NULL
END
ENABLE;
14
© 2014 IBM Corporation
IBM中国系统与科技研发中心 --- IBM i 实验室之旅深圳站
TRANSFER OWNERSHIP
SQL 语法对于 CL 命令CHGOBJOWN 的实现
TRANSFER OWNERSHIP OF TABLE mjatst.t1 TO USER paul
PRESERVE PRIVILEGES
操作可以是事务的一部分，可以被提交/回滚
Grant to GROUP and USER
对于DB2 用户组功能的支持
GRANT ALL ON council TO USER frank WITH GRANT OPTION
GRANT ALL ON council TO GROUP marketing WITH GRANT OPTION
表明ID是用户还是用户组
15
© 2014 IBM Corporation
IBM中国系统与科技研发中心 --- IBM i 实验室之旅深圳站
Timestamp 精度提升
•
•
•
•
目前7.1及之前的版本的TIMESTAMP类型仅支持小数点后6位精度（微秒）
对某些类型的应用精度不够，对另外一些则不需要如此高精度
7.2里用户可以指定TIMESTAMP使用0 – 12位精度
支持TIMESTAMP数据类型的地方都可以指定 （包括RPG程序）
CREATE TABLE x
(C1 TIMESTAMP(12), -- 需要额外的12位精度
C2 TIMESTAMP(0)) -- 不需要非整数的精度
16
© 2014 IBM Corporation
IBM中国系统与科技研发中心 --- IBM i 实验室之旅深圳站
命名参数和参数缺省值 Named Arguments and
Defaults for Parameters



存储过程支持 7.1 – 函数支持 7.2
支持 SQL 和外部存储过程和函数 – making it possible to run more
SQL applications on IBM i.
可以用于扩展存储过程/函数的参数列表，而不用担心对现存应用的
功能更改。只需要简单的对新参数指定缺省值即可。
此功能可以实现：
• 1) 定义了缺省值的参数可以在调用时省略
• 2) 可以在调用时以任意顺序指定参数
• 3) 支持LANGUAGE SQL 和 EXTERNAL 存储过程/函数
CREATE PROCEDURE p1 (i1 INT, i2 INT DEFAULT 0, i3 INT
DEFAULT -1)...
CALL p1(55)
CALL p1(55, i3=>33)
PTFed back to 7.1
17
© 2014 IBM Corporation
IBM中国系统与科技研发中心 --- IBM i 实验室之旅深圳站
自治存储过程 Autonomous Procedures
自治存储过程是运行在一个独立的事务中的存储过程，与
调用它的事务完全隔离。
•自治存储过程内部可以执行Commit或者rollback
•Commit和rollback独立于外部事务
•自治存储过程使用activation group QSQAUTOAG.
CREATE PROCEDURE writelog (loginfo VARCHAR(1000)
AUTONOMOUS
BEGIN
…
INSERT INTO MJATST.T1 VALUES(LOGINFO);
…
END
PTFed back to 7.1
18
© 2014 IBM Corporation
IBM中国系统与科技研发中心 --- IBM i 实验室之旅深圳站
对SQL例程加密
•
使用下列的实用程序，应用可以对有知识产权的SQL例程（存储过程，函
数，触发器）代码进行加密。最终用户将无法看到例程的具体语句。但是
DB2仍旧可以识别和执行这些SQL例程。
•
CREATE_WRAPPED存储过程可以创建一个加密的SQL例程。加密过的
SQL语句会被保存在catalog和程序对象中.
– 例如： CALL CREATE_WRAPPED(‘CREATE FUNCTION salary(wage
DECFLOAT) RETURNS BIGINT RETURN wage * 40 * 52’)
•
WRAP函数读取一个创建SQL例程的SQL语句（如CREATE
PROCEDURE），返回一个加密后的语句
– 例如：WRAP(‘CREATE FUNCTION salary(wage DECFLOAT) RETURNS
DECFLOAT RETURN wage * 40 * 52’)
– 返回如下结果: CREATE FUNCTION salary(wage DECFLOAT) WRAPPED
SQL09073ablGWmdiWmtaTmtaTmteTmtuUmtaUmJqUodaYmdaXidaWmdaW
mdaWmZG1mIaGicaGDwrKycCQn0cnVDazWFkrtEgG1bLP5trZHfmwhQr5nI
PrzhaQzfzWkGaa
PTFed back to 7.1
19
© 2014 IBM Corporation
IBM中国系统与科技研发中心 --- IBM i 实验室之旅深圳站
在单一SQL触发器中支持多种事件类型
• 可以使用单个SQL触发器处理INSERT，UPDATE和DELETE事件。
• 易于管理，安装和维护。
下面的例子中，单一触发器可以：
a) 插入新员工记录的时候递增员工号
b) 删除员工记录时递减员工号
c) 更新薪酬超限的时候出发警告
CREATE TRIGGER HIRED
AFTER INSERT OR DELETE OR UPDATE OF SALARY ON EMPLOYEE
REFERENCING NEW AS N OLD AS O FOR EACH ROW
BEGIN
IF INSERTING
THEN UPDATE COMPANY_STATS SET NBREMP = NBREMP + 1;
END IF;
IF DELETING
THEN UPDATE COMPANY_STATS SET NBREMP = NBREMP - 1;
END IF;
IF UPDATING AND (N.SALARY > 1.1 * O.SALARY)
THEN SIGNAL SQLSTATE '75000'
SET MESSAGE_TEXT = 'Salary increase > 10%'
END IF;
PTFed back to 7.1
END
20
© 2014 IBM Corporation
IBM中国系统与科技研发中心 --- IBM i 实验室之旅深圳站
动态复合（DYNAMIC COMPOUND） 语句
•
•
•
•
•
支持对一个动态的复合语句进行PREPARE和EXECUTE
COMPOUND语句指的是BEGIN和END包围起来的语句集合
之前只能通过存储过程实现
可以使用变量，错误处理和所有的控制语句
支持ATOMIC和NOT ATOMIC
BEGIN
DECLARE V_ERROR BIGINT DEFAULT 0;
DECLARE V_HOW_MANY BIGINT;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
SET V_ERROR = 1;
SELECT COUNT(*) INTO V_HOW_MANY FROM STAFF
WHERE JOB = 'Clerk' AND SALARY < 15000;
IF (V_ERROR = 1 OR V_HOW_MANY = 0)
THEN RETURN;
END IF;
UPDATE STAFF SET SALARY = SALARY * 1.1
WHERE JOB = 'Clerk';
END;
21
PTFed back to 7.1
© 2014 IBM Corporation
IBM中国系统与科技研发中心 --- IBM i 实验室之旅深圳站
TRUNCATE TABLE
•
•
类似“fast delete”
可以指定
–
–
–
–
IGNORE or RESTRICT when delete triggers are present
CONTINUE or RESTART identity values
DROP or REUSE storage
IMMEDIATE performs the operation without commit even if running under
commit
TRUNCATE x IGNORE DELETE TRIGGERS
TRUNCATE x RESTRICT WHEN DELETE TRIGGERS IMMEDIATE
TRUNCATE x CONTINUE IDENTITY
TRUNCATE x RESTART IDENTITY IMMEDIATE
22
© 2014 IBM Corporation
IBM中国系统与科技研发中心 --- IBM i 实验室之旅深圳站
SQE对原生数据访问的支持
• 从7.2开始，下面的数据访问接口会使用SQE作为缺省引擎
• OPNQRYF
• QUERY/400
• 此外，如果被访问的文件或表上定义有RCAC的话，对它的所有打开操作
都将由SQE处理。例如：
• RPG open of PF, LF
• CPYF, CPYFRMIMP, …
SQE = Performance!
• Any database file open operation
• 此行为可以使用QAQQINI配置文件的中的SQE_NATIVE_ACCESS 参数控制
。
23
© 2014 IBM Corporation
IBM中国系统与科技研发中心 --- IBM i 实验室之旅深圳站
日程
• DB2 7.2 新功能简介
• 7.1 TR8, TR9 和 7.2 TR1 新功能简介
24
© 2014 IBM Corporation
IBM中国系统与科技研发中心 --- IBM i 实验室之旅深圳站
DB2 for i – 通过 DB2 PTF Group发布的新功能
IBM i 7.1 & 7.2
TR7
7.1 - TR9
7.2 – TR1
7.2 - GA 7.1 - TR8
SF99701
Level 26
7.2 - GA
•
SF99702 SF99701
Level 1 Level 29
7.1 - TR8 timed Enhancements:
First DB2 PTF Group •
for 7.2
•
All IBM i 7.1
Enhancements
•
thru TR8
•
•
7.2 – TR2
7.2 – TR3
SF99701
Level ??
SF99702
Level ??
SF99701
Level ??
SF99702
Level ??
2015
2014
•
Faster JDBC Toolbox
SF99701
Level 32
SF99702
Level 3
7.1 – TR9 timed Enhancements:
7.2 – TR1 timed Enhancements:
•
•
Everything in TR9. and…
•
System Limits Phase 3 - IFS
•
CREATE OR REPLACE
support on MASK and
PERMISSION
Pipelined Functions
Use SQL to programmatically
analyze performance
•
Generate SQL procedure
•
New IBM i Services
Program & Package statement
level statistical catalogs
•
Guardium and Performance
Monitoring improvements
•
And more…
•
Database Navigator
Enhancements
DB2 Built-in Global Variables
for job name
•
And more…
•
And more…
Regular Expression Scalar
Functions
Enhancements delivered by PTF are documented here:
www.ibm.com/developerworks/ibmi/techupdates/db2
25
© 2014 IBM Corporation
IBM中国系统与科技研发中心 --- IBM i 实验室之旅深圳站
DB2 for i – 通过 DB2 PTF Group发布的新功能
•
通过浏览developerWorks IBM i Technology Updates wiki可以了解DB2
PTF Group的发布计划，状态和新功能列表。
www.ibm.com/developerworks/ibmi/techupdates/db2/groupptf
•
26
© 2014 IBM Corporation
IBM中国系统与科技研发中心 --- IBM i 实验室之旅深圳站
DB2 for i: 7.1 TR8, TR9 and IBM i 7.2 TR1中提供的新功能
• 查找和处理数据
– 正则表达式Regular Expressions
– 新的字符串填充函数（LPAD/RPAD)
• 通过SQL部署复杂的用户自定义表函数(UDTF)
– 管道化表函数Pipelined Table Functions
• 更容易的获取环境参数细节
– 新的内建全局变量New DB2 built-in Global Variables
– 通过API访问DB2内建全局变量
SQL 应用开发
IBM i Services
系统/DB 管理
安全
性能
• 更好的SQL编程支持和体验
–
–
–
–
–
–
–
27
用系统提供的存储过程为SQL对象生成DDL语句
用于生成引号隔开的SQL对象名字的工具
复制表格数据后同步目标表的标识列
乱序创建表分区
IBM i 调试器支持SQL程序和触发器（绿屏和GUI）
SQL消息返回SQL列名字(而不是系统名字)
RUNSQL支持假脱机打印队列输出
© 2014 IBM Corporation
IBM中国系统与科技研发中心 --- IBM i 实验室之旅深圳站
DB2 for i: 7.1 TR8, TR9 and IBM i 7.2 TR1中提供的新功能
• 日志相关的编目表(Catalog Table)
– 可以了解本地和远程日志的详细信息
– 包含环境和统计信息
• 库列表编目表Library List Catalog
– 可以返回当前连接的库列表（包括对于的模式名称）
• 应答列表编目表Reply List Catalog
– 适用SQL查询应答列表
– 通过编程和以实现不同机器间的应答列表比对
SQL 应用开发
IBM i Services
系统/DB 管理
安全
性能
• 作业日志表函数
– 应用可以通过SQL表函数访问当前或其他作业的日志
– 例如可用于处理作业中的错误信息
28
© 2014 IBM Corporation
IBM中国系统与科技研发中心 --- IBM i 实验室之旅深圳站
DB2 for i: 7.1 TR8, TR9 and IBM i 7.2 TR1中提供的新功能
• Navigator for i
– 改进了DB相关的对话框
– 大幅提升了性能
• System Limits 阶段 3 – IFS
SQL 应用开发
IBM i Services
– 发现大的IFS对象
– 发现IFS对象的不断增长趋势
– 应对可能的IFS对象超限情况
系统/DB 管理
安全
性能
• Database Monitor用户过滤器增强
– 选择 1-10个需要监控的用户
– Navigator和绿屏命令都可以支持
– Guardium Database Activity Monitor使用这一新功能
• PTF Group “currency” 视图
– 了解当前系统安装的PTF Group与IBM发布的相比是否是最新的
•
•
•
29
Consume a live XML feed from IBM PSP Group information
Access the current partition and extract the PTF Group information
Use the SQL Query Engine to perform a live comparison
© 2014 IBM Corporation
IBM中国系统与科技研发中心 --- IBM i 实验室之旅深圳站
DB2 for i: 7.1 TR8, TR9 and IBM i 7.2 TR1中提供的新功能
• 使用DB2性能工具时保护敏感数据
– Database monitor 支持安全主机变量
– Navigator和命令行支持
• Row & Column Access Control
– 更便捷的部署
OR REPLACE子句
SQL 应用开发
IBM i Services
系统/DB 管理
安全
性能
30
© 2014 IBM Corporation
IBM中国系统与科技研发中心 --- IBM i 实验室之旅深圳站
DB2 for i: 7.1 TR8, TR9 and IBM i 7.2 TR1中提供的新功能
• SQL 性能分析
– Visual Explain 支持更详细的 UDTF信息
•
Drill down to see program statements
– DRDA连接会标记特定的信息岛客户端特殊寄存器
– 受控的复杂查询优化
– 使用程序访问查询计划缓存快照
• “In Memory” 改进
SQL 应用开发
IBM i Services
系统/DB 管理
安全
性能
– 使用SQL语言可以设定表或者索引的In Memory属性
– 完整的Navigator支持
• 更多关于Index的信息
– 更易于评估索引的价值
– 可以通过编目表和Navigator获取数据
• Database monitor性能改进
– 精减主机变量的值的收集方式
– 减少I/O和CPU资源消耗
31
© 2014 IBM Corporation
IBM中国系统与科技研发中心 --- IBM i 实验室之旅深圳站
使用SQL生成SQL DDL源代码
SQL存储过程qsys2.generate_sql可以用来生成DDL源代码
call qsys.create_sql_sample('SAMPLE_CORPORATE_DB'); -- creates library SAMPL00001
call qsys2.generate_sql ('%', 'SAMPLE_%', 'TABLE', REPLACE_OPTION => '0');
(these calls generate the same detail)
call qsys2.generate_sql ('%', 'SAMPL00001', 'TABLE', REPLACE_OPTION => '0');
32
© 2014 IBM Corporation
IBM中国系统与科技研发中心 --- IBM i 实验室之旅深圳站
乱序创建表分区
• 使用CREATE TABLE创建分区表时，现在可以乱序指定分区区间了。添加分区也可以添
加在已有分区中间。
With TR8, out of order ranges are tolerated
Example:
create table corpdb.sales_table(trans_date
timestamp) partition by range(trans_date)
(starting('2013-01-01 00:00:00.000000')
ending ('2013-03-31 23:59:59.999999'),
starting('2013-07-01 00:00:00.000000')
ending ('2013-09-30 23:59:59.999999'));
-- Oops, forgot a partition, add it in
alter table corpdb.sales_table add partition
starting('2013-04-01 00:00:00.000000')
ending ('2013-06-30 23:59:59.999999');
33
© 2014 IBM Corporation
IBM中国系统与科技研发中心 --- IBM i 实验室之旅深圳站
正则表达式支持
正则表达式是强大的字符串搜索和匹配工具。现在可以在查询语句中使用下列正则表达式
谓词或者SQL函数.
函数或谓词
描述
REGEXP_LIKE 谓词
Searches for a regular expression pattern in a string and returns True or False
REGEXP_COUNT
Returns a count of the number of times that a pattern is matched in a string
REGEXP_INSTR
Returns the starting or ending position of the matched substring
REGEXP_SUBSTR
Returns one occurrence of a substring of a string that matches the pattern
REGEXP_REPLACE
Returns a modified version of the source string where occurrences of the pattern
found in the source string are replaced with the specified replacement string
Select the employee number where the last name is spelled LUCCHESSI, LUCHESSI, or
LUCHESI from the EMPLOYEE table without considering upper or lower case letters.
SELECT empno
FROM employee
WHERE REGEXP_LIKE(lastname,'luc+?hes+?i','i')
34
© 2014 IBM Corporation
IBM中国系统与科技研发中心 --- IBM i 实验室之旅深圳站
使用 LPAD & RPAD进行字符串补齐
• 使用LPAD/RPAD函数向左或者向右填充字符串
SELECT
RPAD(FIRSTNME, B.LENGTH, '.') CONCAT
CASE WHEN MIDINIT IS NULL OR
MIDINIT = ' ' THEN '?' ELSE
RPAD(MIDINIT, C.LENGTH, '.') END
CONCAT '.‘ CONCAT
RPAD(LASTNAME, D.LENGTH, '.')
AS Employee_Names
FROM toystore5.employee A,
(SELECT LENGTH FROM QSYS2.SYSCOLUMNS WHERE
TABLE_NAME = 'EMPLOYEE' AND COLUMN_NAME = 'FIRSTNME'
AND TABLE_SCHEMA = 'TOYSTORE5') B,
(SELECT LENGTH FROM QSYS2.SYSCOLUMNS WHERE
TABLE_NAME = 'EMPLOYEE' AND COLUMN_NAME = 'MIDINIT'
AND TABLE_SCHEMA = 'TOYSTORE5') C,
(SELECT LENGTH FROM QSYS2.SYSCOLUMNS WHERE
TABLE_NAME = 'EMPLOYEE' AND COLUMN_NAME = 'LASTNAME‘
AND TABLE_SCHEMA = 'TOYSTORE5') D
35
© 2014 IBM Corporation
IBM中国系统与科技研发中心 --- IBM i 实验室之旅深圳站
IBM i Debugger 对SQL的进一步支持
• STRDBG (绿屏) & IBM i Debugger (GUI界面) 可以用于调试SQL procedures,
functions and triggers
• 之前调试器只能显示编译好的SQL变量对应的高级语言变量或数据结构成员，且
无法单步执行SQL语句，不够友好。
• 在TR7中，调试器可以在SQL语句上中单步执行了。
• 在 TR9中，调试器可以支持EVAL SQL变量
New…
36
© 2014 IBM Corporation
IBM中国系统与科技研发中心 --- IBM i 实验室之旅深圳站
SQE – 受控的复杂查询的优化
•
SQL Query Engine (SQE) 现在可以检测盒控制复杂查询的优化过程，带来查询
性能的提高和系统资源消耗的降低。
1. SQE可以识别出当前有同样的查询同时进入优化阶段。
2. SQE可以识别出查询时复杂查询 (>20 表引用)
3. 1 & 2 可以阻止超过3个相同查询的实例进入优化阶段。同样的查询情况会被暂
挂，直到现有优化过程完成。
这提高了SQE查询计划缓存的效率。原理是同样的查询有更高的机会可以重用
已经存在的计划，避免重复的优化过程。
7.1
DB2 PTF Group
SF99701 Level 29
37
© 2014 IBM Corporation
IBM中国系统与科技研发中心 --- IBM i 实验室之旅深圳站
SQE – 受控的复杂查询的优化
New with TR8…
Is the query complex?
Job 1
Select …
Job 2
Select …
Job 3
Select …
Optimize
Open
Run
SQE Plan
Cache
Match a query
already in
optimization
phase?
No
System
resources
System
resources
No
Yes
# of matches > 3?
Yes
38
Wait for
optimization
to complete
DB Open Exit
Query Governors
Adaptive Query Processor
© 2014 IBM Corporation
IBM中国系统与科技研发中心 --- IBM i 实验室之旅深圳站
跟踪系统极限值 – 阶段3
阶段 3 – 跟踪IFS文件系统中的目标极限值
被监控的文件系统：
39
•
“root” (/) 文件系统
•
Open systems 文件系统 (QOpenSys)
•
Document library services file system (QDLS)
•
用户定义文件系统 (UDFSs)
Discover trends,
high consumers
and more
© 2014 IBM Corporation
IBM中国系统与科技研发中心 --- IBM i 实验室之旅深圳站
跟踪系统极限值 – 阶段3
Limit ID
Limit value
阈值
增量
Number of objects linked in a directory
18402
0
100,000
10,000
Maximum number of directories linked in a
directory
18403
1,000,000
1,000
1,000
Maximum number of file system objects in
*SYSBAS ASPs
18404
2,147,483,647
100,000
10,000
Maximum number of file system objects in an
independent ASP
18405
2,147,483,647
100,000
10,000
Maximum number of document library objects in
a folder
18406
65510
1,000
500
Number of document library objects in the
system ASP
18407
0
100,000
10,000
Maximum number of document library objects in
a user ASP
18408
349,000
100,000
10,000
Maximum number of bytes in a stream file
18409
1,099,511,627,776
16,777,216
1,048,576
Maximum number of bytes in a document
18409
2,147,483,647
16,777,216
1,048,576
极限值描述
http://iprodeveloper.com/systems-management/ondemand-tracking-important-system-limits-ibm-i
http://iprodeveloper.com/systems-management/gain-big-insights-db2-i-system-limits-phase-2
40
© 2014 IBM Corporation
IBM中国系统与科技研发中心 --- IBM i 实验室之旅深圳站
跟踪系统极限值 – 阶段3
-- 检查系统中最大的IFS对象，返回按大小排序的列表
SELECT SIZING_NAME, IFS_PATH_NAME, OBJECT_TYPE,
CURRENT_VALUE, USER_NAME, LASTCHG
FROM QSYS2.SYSLIMITS
WHERE LIMIT_CATEGORY = 'FILE SYSTEM'
ORDER BY CURRENT_VALUE DESC
41
© 2014 IBM Corporation
IBM中国系统与科技研发中心 --- IBM i 实验室之旅深圳站
QSYS2.JOBLOG_INFO表函数 – 获取作业日志
•
作业日志包含大量关键信息
• 作业出现错误时，可以浏览作业日志获取错误信息
• 日志中的REQUEST消息可以用于了解执行过的CL命令
• 即使trace和其他错误记录手段存在， IBM服务部门通常都会要求获得作业日志
以帮助定位问题或异常
新的JOBLOG_INFO()表函数提供了一种全新的获取作业日志的方式，可以帮助应
用开发人员和系统管理者更方便的获取和处理作业日志
唯一的参数就是希望检查的作业名字
• ‘*’ 表示当前作业
• 或者指定另一作业的完整限定名
•
•
Example 1: Find the most recently executed command in a target job
SELECT MESSAGE_TEXT FROM
TABLE(QSYS2.JOBLOG_INFO('706721/SCOTTF/QPADEV0006')) A
WHERE A.MESSAGE_TYPE = 'REQUEST'
ORDER BY ORDINAL_POSITION DESC
FETCH FIRST 1 ROW ONLY
42
© 2014 IBM Corporation
IBM中国系统与科技研发中心 --- IBM i 实验室之旅深圳站
DB2 for i – 获得进一步的技术信息
•
Enhancements delivered by PTF:
www.ibm.com/developerworks/ibmi/techupdates/db2
•
Some InfoCenter books, including the 7.1 SQL Reference, are updated with TRs:
http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/topic/db2/rbafz.pdf
•
The “IBM i 7.1 Technical Overview” Redbook:
http://www.redbooks.ibm.com/abstracts/sg247858.html
•
We publish deep dive White Papers on some topics:
HTTP functions & DB2 for i - http://bit.ly/httpAndDB2fori
XML on DB2 for i - http://bit.ly/XMLonDB2fori
•
We explore some Technology topics with articles:
www.ibm.com/developerworks/ibmi/library
•
DB2 for i – Power page
http://ibm.com/systems/i/db2
43
© 2014 IBM Corporation
IBM中国系统与科技研发中心 --- IBM i 实验室之旅深圳站
DB2 for i – 获得进一步的技术信息
•
developerWorks (re-designed)
–
–
–
•
Blog
–
•
Mike Cain:
http://db2fori.blogspot.ca/2013/10/seven.html
Twitter
–
44
DB2 for i enhancements: https://www.ibm.com/developerworks/ibmi/techupdates/db2
DB2 for i forum: http://www.ibm.com/developerworks/forums/forum.jspa?forumID=292
Technical articles, and more… https://www.ibm.com/developerworks/ibmi/
Scott Forstie:
https://twitter.com/Forstie_IBMi
© 2014 IBM Corporation
IBM中国系统与科技研发中心 --- IBM i 实验室之旅深圳站
Backup
45
© 2014 IBM Corporation
IBM中国系统与科技研发中心 --- IBM i 实验室之旅深圳站
使用SQL生成SQL DDL源代码
• Today, SQL DDL can be generated for existing objects using IBM i Navigator’s
Generate SQL… or via the Generate Data Definition Language (QSQGNDDL) API.
With IBM i 7.1 in the TR8 timeframe, an SQL approach is now possible!
• The QSYS2/GENERATE_SQL() procedure can be called to generate DDL for one
or more objects.
• The procedure has many parameters (33 of them), but leverages DB2 for i support
for defaults on parameters.
• Use the API documentation to understand the required privileges and procedure
parameters: http://bit.ly/QSQGNDDL
•The generated DDL can be written to a user specified output file or consumed as a
result set.
• Since this is an SQL procedure, SQLCODE=-443 is a generic indicator of failure.
• If the object name is delimited, the delimited name needs to be input.
• Either the SQL name or System name is permitted on input.
• The procedure handles generic names, something unique to this interface.
SQL interfaces are easy to use and powerful
46
© 2014 IBM Corporation
IBM中国系统与科技研发中心 --- IBM i 实验室之旅深圳站
管道表函数
管道表函数允许单纯使用SQL语言更灵活的创建复杂的UDTF，避免用户需要创建程序或者服务程序。
增强了UDTF的可移植性，因为不在需要依赖于特定的高级语言程序。
Traditional External UDTF
Pipelined SQL UDTF
CRTSQLCI OBJ(applib/producer) SRCFILE(appsrc/c80)
COMMIT(*NONE) OUTPUT(*PRINT)
OPTION(*NOGEN)
CREATE FUNCTION producer()
RETURNS TABLE ( largest_table_sizes INTEGER )
LANGUAGE SQL
BEGIN
CRTCMOD MODULE(applib/producer)
SRCFILE(qtemp/qsqltemp)
TERASPACE(*YES) STGMDL(*INHERIT)
FOR LimitCursor CURSOR FOR
SELECT CURRENT_VALUE FROM
QSYS2.SYSLIMITS WHERE
SIZING_NAME = 'MAXIMUM NUMBER OF ALL ROWS'
ORDER BY CURRENT_VALUE DESC
DO
PIPE CURRENT_VALUE;
END FOR;
CRTSRVPGM SRVPGM(applib/udfs)
MODULE(applib/producer)
EXPORT(*SRCFILE) SRCFILE(BUILDLIB/APPEXP)
SRCMBR(PRODUCER) ACTGRP(*CALLER)
RETURN;
END;
CREATE FUNCTION producer()
RETURNS TABLE (largest_table_sizes INTEGER )
EXTERNAL NAME applib.udfs(producer)
LANGUAGE C PARAMETER STYLE SQL
47
© 2014 IBM Corporation
IBM中国系统与科技研发中心 --- IBM i 实验室之旅深圳站
管道表函数
Ease of Implementation
• Pipelined table functions are easy to implement. While “set-at-a-time” processing
remains the best practice, pipelined functions provide a user friendly alternative.
Greater runtime flexibility
• Pipelined table functions allow the flexibility to programmatically create 'virtual'
tables with greater control than SELECT or CREATE VIEW can provide.
 UDTF input parameters
 Ability to handle errors and warnings
 Application logging
 References to multiple databases in a single query
 Customized join behavior
Big Data / Analytics / Performance
• In those scenarios where only a subset of the result table is consumed, pipelined
functions are preferred.
Interoperability / Portability
• Compatible with DB2 for LUW support
48
© 2014 IBM Corporation
IBM中国系统与科技研发中心 --- IBM i 实验室之旅深圳站
DB2 for i – Name delimiting function
• SQL Catalogs store names in un-delimited form
• Most SQL statements and services require that input names be properly
delimited.
• The QSYS2/DELIMIT_NAME() User Defined Function (UDF) is a new DB2 for i
supplied service that can be used to insure a name is ready for use.
• Delimiters are only added if needed.
• Input InName VARCHAR(128) & RETURNS VARCHAR(258)
Example:
SELECT TABLE_NAME , QSYS2.DELIMIT_NAME(TABLE_NAME) AS
TABLE_NAME_DELIMITED FROM QSYS2.SYSTABLES ORDER BY TABLE_NAME
49
© 2014 IBM Corporation
IBM中国系统与科技研发中心 --- IBM i 实验室之旅深圳站
Enhanced ability to copy tables with identity columns
• Identity value columns are guaranteed to contain a unique value.
• When tables are copied with INCLUDING IDENTITY COLUMN ATTRIBUTES or via
CL commands like Copy File (CPYF), the next value for the identity column is the
defined first value
• In many cases, the Database Engineer (DBE) would prefer to have the new table use
the same “next value” as the source table.
• Now, you can easily reestablish the identity value using the
QSYS2/RESTART_IDENTITY() procedure. The procedure calculates the NEXT
VALUE of the source file’s identity value and alters the target file to match.
Procedure signature:
CALL QSYS2.RESTART_IDENTITY(<source-library-name>, <source-file-name>,
<target-library-name>, <target-file-name>);
50
© 2014 IBM Corporation
IBM中国系统与科技研发中心 --- IBM i 实验室之旅深圳站
Regular Expressions
-- 7.1 version
CREATE OR REPLACE FUNCTION FindHits(v_search_string CLOB(1M), v_pattern
varchar(32000) )
RETURNS TABLE (website_reference varchar(512))
LANGUAGE SQL
Regular expression & Pipeline
BEGIN
DECLARE V_Count INTEGER;
function example for IBM i 7.1
DECLARE LOOPVAR INTEGER DEFAULT 0;
SET V_Count = REGEXP_COUNT(v_search_string, v_pattern,1,'i');
IF v_pattern IS NULL OR LENGTH(v_pattern) = 0 THEN
SET v_pattern = '(\w+\.)+((org)|(com)|(gov)|(edu))';
END IF;
WHILE LOOPVAR < V_Count DO
SET LOOPVAR = LOOPVAR + 1;
PIPE( REGEXP_SUBSTR(v_search_string,v_pattern, 1, LOOPVAR, 'i') );
END WHILE;
RETURN;
END;
SELECT * FROM TABLE(FindHits('Are you interested in any of these colleges:
isu.EDU or www.umn.Edu? We could even visit WWW.wisc.edu if we have time.'))
A;
51
© 2014 IBM Corporation
IBM中国系统与科技研发中心 --- IBM i 实验室之旅深圳站
Regular Expressions
-- 7.2 version
CREATE OR REPLACE FUNCTION FindHits(v_search_string CLOB(1M), v_pattern
varchar(32000) DEFAULT '(\w+\.)+((org)|(com)|(gov)|(edu))' )
RETURNS TABLE (website_reference varchar(512))
LANGUAGE SQL
Regular expression & Pipeline
BEGIN
DECLARE V_Count INTEGER;
function example for IBM i 7.2
DECLARE LOOPVAR INTEGER DEFAULT 0;
SET V_Count = REGEXP_COUNT(v_search_string, v_pattern,1,'i');
WHILE LOOPVAR < V_Count DO
SET LOOPVAR = LOOPVAR + 1;
PIPE( REGEXP_SUBSTR(v_search_string,v_pattern, 1, LOOPVAR, 'i') );
END WHILE;
RETURN;
END;
SELECT * FROM TABLE(FindHits('Are you interested in any of these colleges:
isu.EDU or www.umn.Edu? We could even visit WWW.wisc.edu if we have time.'))
A;
52
© 2014 IBM Corporation
IBM中国系统与科技研发中心 --- IBM i 实验室之旅深圳站
Regular Expressions – understand the choices
Query Choice
Considerations
Regular Expressions
OmniFind Text Search Server for
DB2 for i
Enablement
7.1 - TR9
7.2 - TR1
No charge option (5733OMF)
available at 6.1 and higher
Finding matches
REGEXP_LIKE predicate
CONTAINS function
Search Target
Expression that returns a
character string, graphic string,
numeric, or datetime
Column within a table, Spool files,
IFS stream files, and more
Search mechanism
Pattern matching
(grep, awk, etc…)
Linguistic Variations
Keyword, phrase matching, keyword
variations (“mice” for “mouse”)
Performance consideration
No ability to use indexes for
implementation
Text search index
Maintenance consideration
None
Index build & update(s)
Complementary services
REGEXP_COUNT,
REGEXP_INSTR,
REGEXP_SUBSTR &
REGEXP_REPLACE
SCORE
Resources
SQL Reference
https://ibm.biz/IBMi_OMF
53
© 2014 IBM Corporation
IBM中国系统与科技研发中心 --- IBM i 实验室之旅深圳站
Controlled padding using LPAD & RPAD
• Works with many data types
SELECT
LPAD(EMPNO, 10, '0')
as employee_number,
LPAD(salary + bonus + comm, 12, '*')
as total_compensation
FROM toystore5.employee
ORDER BY total_compensation;
54
© 2014 IBM Corporation
IBM中国系统与科技研发中心 --- IBM i 实验室之旅深圳站
QSYS2.JOBLOG_INFO表函数 – 获取作业日志
Example 2: 在应用中自动处理作业日志
CREATE OR REPLACE PROCEDURE TOYSTORE.UPDATE_SALES
(IN P_NEW_SALES INTEGER, IN P_SALES_PERSON VARCHAR(100),
IN P_SALES_DATE DATE)
DYNAMIC RESULT SETS 1
LANGUAGE SQL MODIFIES SQL DATA SET OPTION COMMIT = *CHG
BEGIN
DECLARE C_RESULT_SET1 CURSOR WITH RETURN TO CALLER FOR SELECT MESSAGE_ID, MESSAGE_TEXT,
MESSAGE_TYPE FROM SESSION.My_Joblog;
Mainline: BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
DECLARE GLOBAL TEMPORARY TABLE My_Joblog AS
(SELECT * FROM TABLE(QSYS2.JOBLOG_INFO('*')) A)
WITH DATA WITH REPLACE;
OPEN C_RESULT_SET1;
END;
UPDATE TOYSTORE.SALES
SET SALES = SALES + P_NEW_SALES
WHERE SALES_PERSON = P_SALES_PERSON
AND SALES_DATE = P_SALES_DATE;
END Mainline;
END ;
CALL TOYSTORE.UPDATE_SALES(3, 'LUCCHESSI', '1995-12-31')
55
© 2014 IBM Corporation
IBM中国系统与科技研发中心 --- IBM i 实验室之旅深圳站
SQL column names in messages
• SQL Column names are much easier to understand than Field names
• SQL Language constructs like FOR SYSTEM NAME and FOR COLUMN
make it easier to recognize and understand failures, because the name is
chosen instead of system generated
• A behavior change has been made, to give preference to SQL column names
in failure messages. The list of SQL messages changed to return SQL column
names is:
SQL0190, SQL0196, SQL0197, SQL0404, SQL0406, SQL0407, SQL0415
Now…
Before…
56
© 2014 IBM Corporation
IBM中国系统与科技研发中心 --- IBM i 实验室之旅深圳站
Programmatic consumption of Snapshots
SQL Performance Monitors and SQL Plan Cache snapshots contain extensive detail
about database activity. Today, the primary benefactor of this detail is the DBA or DB
performance analyst who is using IBM i Navigator to graphically analyze the activity.
The QSYS2/EXTRACT_STATEMENTS procedure provides an SQL interface to pull out the
important / deep details.
Example – Extract the 100 most recent statements
CALL QSYS2.DUMP_PLAN_CACHE('SNAPSHOTS', 'SNAP020114');
CALL QSYS2.EXTRACT_STATEMENTS('SNAPSHOTS', 'SNAP020114', '*AUDIT',
'AND QQC21 NOT IN
(''CH'', ''CL'', ''CN'', ''DE'', ''DI'', ''DM'', ''HC'', ''HH'', ''JR'', ''FE'', ''PD'', ''PR'', ''PD'')',
' ORDER BY QQSTIM DESC FETCH FIRST 100 ROWS ONLY ',
null, null);
57
Parameters:
1) MONITOR_SCHEMA varchar(10) - required
2) MONITOR_NAME varchar(10) - required
Optional parameters…
3) ADDITIONAL_SELECT_COLUMNS
varchar(5000). A value of *AUDIT will cause
the procedure to return the merged
statement and columns that are normally
interesting to auditing
4) ADDITIONAL_PREDICATES varchar(5000)
5) ORDER_BY varchar(5000)
6) OUTPUT_SCHEMA varchar(258)
7) OUTPUT_TABLE varchar(258)
If parameters 6&7 are not specified, a result set is
returned
© 2014 IBM Corporation
IBM中国系统与科技研发中心 --- IBM i 实验室之旅深圳站
Programmatic consumption of Snapshots
Example – Extract the 10 queries with the longest runtime, where the query took > 1 second
CALL QSYS2.DUMP_PLAN_CACHE('SNAPSHOTS', 'SNAP020114');
CALL QSYS2.EXTRACT_STATEMENTS('SNAPSHOTS', 'SNAP020114',
ADDITIONAL_SELECT_COLUMNS => ‘DEC(QQI6)/1000000.0 as Total_time, QVC102 as Current_User_Profile ',
ADDITIONAL_PREDICATES => ' AND QQI6 > 1000000 ',
ORDER_BY => ' ORDER BY QQI6 DESC ');
Note: Extraction takes time, this is not a quick operation
58
Coding guide:
http://bit.ly/1000rcdFields
© 2014 IBM Corporation
IBM中国系统与科技研发中心 --- IBM i 实验室之旅深圳站
Finding the most expensive queries
Everyone sees value in reviewing the most expensive queries. While “most expensive” can be
interpreted in many ways, the total elapsed time needed to execute the query is the best place
to start. The QSYS2.DUMP_PLAN_CACHE_topN procedure provides an SQL interface to
programmatically mine the live SQL Plan Cache.
Automate
Example:
CALL QSYS2.DUMP_PLAN_CACHE_topN('SNAPSHOTS', 'TOPN020214', 20);
select qqc21,DEC(QQI6)/1000000.0 as Total_time,
QVC102 as Current_User_Profile,
qq1000 from SNAPSHOTS.TOPN020214 where qqrid=1000 order by qqi6 desc;
using
SQL
Parameters:
1) OUTPUT_LIBRARY varchar(10)
2) OUTPUT_TABLE varchar(10)
3) Top_N_Choice INTEGER
7.1
DB2 PTF Group
SF99701 Level 29
59
© 2014 IBM Corporation
IBM中国系统与科技研发中心 --- IBM i 实验室之旅深圳站
QSYS2/GET_JOB_INFO()  enhanced
•
•
GET_JOB_INFO() enhanced to accept ‘*’ to indicate that the current job as the
target
The UDTF table is enhanced to include the most recently executed (or
currently executing) SQL statement text
Example 1) Get job information for the current job
select a.* from table(qsys2.get_job_info('*')) a;
Example 2) Get job information for the current job
select a.* from table(qsys2.get_job_info('803868/Quser/Qzdasoinit')) a;
60
IBM i 6.1
IBM i 7.1
DB2 PTF Group
SF99601 Level 32
DB2 PTF Group
SF99701 Level 29
© 2014 IBM Corporation
IBM中国系统与科技研发中心 --- IBM i 实验室之旅深圳站
DB2 for i Built-in Global Variables
•
•
•
The qualified job name of the current connection is easily accessed
When SQL Server Mode is used, the job name of the application instance
which owns the connection is accessed through SERVER_MODE_JOB_NAME
Use these variables to deploy advanced logic in triggers, RCAC rules, and
more
New with
IBM i 7.2
SF99702
Level 3
Available
with
base
IBM i 7.2
61
Variable name
Schema
Data Type
Size
JOB_NAME
QSYS2
VARCHAR
28
SERVER_MODE_JOB_NAME
QSYS2
VARCHAR
28
CLIENT_IPADDR
SYSIBM
VARCHAR
128
CLIENT_HOST
SYSIBM
VARCHAR
255
CLIENT_PORT
SYSIBM
INTEGER
-
PACKAGE_NAME
SYSIBM
VARCHAR
128
PACKAGE_SCHEMA
SYSIBM
VARCHAR
128
PACKAGE_VERSION
SYSIBM
VARCHAR
64
ROUTINE_SCHEMA
SYSIBM
VARCHAR
128
ROUTINE_SPECIFIC_NAME
SYSIBM
VARCHAR
128
ROUTINE_TYPE
SYSIBM
CHAR
1
© 2014 IBM Corporation
IBM中国系统与科技研发中心 --- IBM i 实验室之旅深圳站
QSYS2.JOURNAL_INFO – View
• Use SQL to retrieve detail for local & remote journals
• Information from QjoRetrieveJournalInformation() API, RJRN0100 format.
• The view returns Key 1 & 3 information, one row == information about one journal.
-- Which remote journals are the most heavily used?
SELECT JOURNALED_OBJECTS, A.* FROM QSYS2.JOURNAL_INFO A WHERE
NUMBER_REMOTE_JOURNALS > 0 AND JOURNALED_OBJECTS IS NOT NULL
ORDER BY JOURNALED_OBJECTS DESC
-- Find journals that are nearing the limit of journaled
objects:
SELECT * FROM QSYS2.JOURNAL_INFO WHERE
JOURNALED_OBJECT_LIMIT = '*MAX250K' AND
JOURNALED_OBJECTS > 200000
62
© 2014 IBM Corporation
IBM中国系统与科技研发中心 --- IBM i 实验室之旅深圳站
QSYS2.JOURNAL_INFO – View
• Use this new source of information to achieve better journal management
• Recognize conditions that require attention
-- Which remote journals fell the farthest behind this week?
SELECT MAXIMUM_TIME_BEHIND, MAXIMUM_BEHIND_TIMESTAMP,
ESTIMATED_TIME_BEHIND, TOTAL_SIZE_JOURNAL_RECEIVERS,
RTRIM(ATTACHED_JOURNAL_RECEIVER_LIBRARY) CONCAT '/' CONCAT
RTRIM(ATTACHED_JOURNAL_RECEIVER_NAME) AS JrnName, A.*
FROM QSYS2.JOURNAL_INFO A WHERE
MAXIMUM_BEHIND_TIMESTAMP > CURRENT TIMESTAMP - 7 DAYS AND
MAXIMUM_TIME_BEHIND > 0 AND MAXIMUM_TIME_BEHIND IS NOT NULL
ORDER BY MAXIMUM_TIME_BEHIND DESC FETCH FIRST 10 ROWS ONLY
63
© 2014 IBM Corporation
IBM中国系统与科技研发中心 --- IBM i 实验室之旅深圳站
QSYS2.REPLY_LIST_INFO –
view
•
•
One reply list handles system wide automatic response to messages
Now, SQL can be used to:
 Compare (exception join) the configuration of two machines
 Determine whether a specific sequence number is already in use
 Confirm whether setup is complete
REPLY_LIST_INFO matches the Work Reply List Entry (WRKRPYLE) command
behavior of allowing *PUBLIC users to view the reply list information
•
Example:
SELECT * FROM QSYS2.REPLY_LIST_INFO
WHERE message_ID like 'CPA%'
64
© 2014 IBM Corporation
IBM中国系统与科技研发中心 --- IBM i 实验室之旅深圳站
QSYS2.LIBRARY_LIST_INFO –
view
•
With direct access to the library list, SQL users can tap into the library list detail to:
• Programmatically review the environment
• Know when the library list needs to be adjusted
• Derive information about the libraries
 IASP Number
 Schema vs Library name mapping
 User vs System vs Product libraries
 Above all else, order of libraries searched for unqualified objects
Example:
SELECT * FROM QSYS2.LIBRARY_LIST_INFO
65
© 2014 IBM Corporation
IBM中国系统与科技研发中心 --- IBM i 实验室之旅深圳站
SYSTOOLS.GROUP_PTF_CURRENCY View
SELECT * from SYSTOOLS.GROUP_PTF_CURRENCY
WHERE PTF_GROUP_RELEASE = ‘R720’
ORDER BY ptf_group_level_available ptf_group_level_installed DESC
Current or
behind on
service?
66
PTF Group
Info
Level
installed
on this
partition
Level
available
from IBM
Date that
IBM last
updated
this group
© 2014 IBM Corporation
IBM中国系统与科技研发中心 --- IBM i 实验室之旅深圳站
SYSTOOLS.GROUP_PTF_CURRENCY View
http://www912.ibm.com/s_dir/sline003.nsf/PSPbyNumL.xml?OpenView&count=500
XML
namespace
&
structure
67
© 2014 IBM Corporation
IBM中国系统与科技研发中心 --- IBM i 实验室之旅深圳站
SYSTOOLS.GROUP_PTF_CURRENCY View
Study the XML structure to define the data to the HTTP function.
HTTPXML document
structure
TCP/IP Enablement:
‘www-912.ibm.com’ maps to 129.42.160.32
IBM i TCP/IP configuration Technote:
Enablement
http://www-01.ibm.com/support/docview.wss?uid=nas8N1018980
White papers:
• https://ibm.biz/XMLandDB2fori
• 68https://ibm.biz/HTTPandDB2fori
Developer
resources
© 2014 IBM Corporation
IBM中国系统与科技研发中心 --- IBM i 实验室之旅深圳站
SYSTOOLS.GROUP_PTF_CURRENCY View
CREATE OR REPLACE VIEW SYSTOOLS/GROUP_PTF_CURRENCY FOR SYSTEM NAME GRPPTFCUR (
PTF_GROUP_CURRENCY FOR COLUMN GRP_CRNCY ,
SQL Source
PTF_GROUP_ID FOR COLUMN GRP_ID
,
PTF_GROUP_TITLE FOR COLUMN GRP_TITLE ,
PTF_GROUP_LEVEL_INSTALLED FOR COLUMN GRP_LVL
,
PTF_GROUP_LEVEL_AVAILABLE FOR COLUMN GRP_IBMLVL ,
PTF_GROUP_LAST_UPDATED_BY_IBM FOR COLUMN GRP_LSTUPD ,
PTF_GROUP_RELEASE FOR COLUMN GRP_RLS
,
PTF_GROUP_STATUS_ON_SYSTEM FOR COLUMN GRP_SYSSTS )
AS
SELECT CASE WHEN ACTUAL.GRPPTF IS NULL THEN 'PTF GROUP DOES NOT EXIST ON ' CONCAT CURRENT
SERVER WHEN PSPS.PSP_NUMBER IS NULL THEN 'PSP INFORMATION NOT AVAILABLE' WHEN
ACTUAL.GRPPTF = PSPS.PSP_NUMBER AND ACTUAL.PTF_GROUP_LEVEL = PSPS.PSP_LEVEL THEN
'INSTALLED LEVEL IS CURRENT' WHEN ACTUAL.GRPPTF = PSPS.PSP_NUMBER AND
ACTUAL.PTF_GROUP_LEVEL < PSPS.PSP_LEVEL THEN 'UPDATE AVAILABLE' WHEN ACTUAL.GRPPTF =
PSPS.PSP_NUMBER AND ACTUAL.PTF_GROUP_LEVEL > PSPS.PSP_LEVEL THEN 'PSP IS DOWNLEVEL - '
CONCAT ACTUAL.PTF_GROUP_STATUS END PTF_GROUP_CURRENCY, COALESCE(PSPS.PSP_NUMBER,
ACTUAL.GRPPTF) PTF_GROUP_ID, COALESCE(PSPS.PSP_TITLE, ACTUAL.PTF_GROUP_DESCRIPTION)
PTF_GROUP_TITLE, ACTUAL.PTF_GROUP_LEVEL PTF_GROUP_LEVEL_INSTALLED, PSPS.PSP_LEVEL
PTF_GROUP_LEVEL_AVAILABLE, PSPS.PSP_DATE AS PTF_GROUP_LAST_UPDATED_BY_IBM,
COALESCE(PSPS.PSP_RELEASE, ACTUAL.PTF_GROUP_TARGET_RELEASE) PTF_GROUP_RELEASE,
ACTUAL.PTF_GROUP_STATUS PTF_GROUP_STATUS_ON_SYSTEM FROM
XMLTABLE('/all_psps/psp' PASSING XMLPARSE(DOCUMENT
HTTPGETBLOB('http://www-912.ibm.com/s_dir/sline003.nsf/PSPbyNumL.xml?OpenView&count=500'
, ''))
COLUMNS PSP_RELEASE CHAR(5) PATH 'release', PSP_NUMBER CHAR(7) PATH 'number', PSP_TITLE
© 2014 IBM Corporation
69
VARCHAR(1000) PATH 'title', PSP_LEVEL INTEGER PATH 'level', PSP_DATE CHAR(10) PATH
'date'
IBM中国系统与科技研发中心 --- IBM i 实验室之旅深圳站
Automated management of Plan Cache detail
More procedures added to the roster…
• QSYS2/IMPORT_PLAN_CACHE() procedure
• QSYS2/REMOVE_PLAN_CACHE() procedure
• QSYS2/IMPORT_EVENT_MONITOR() procedure
• QSYS2/REMOVE_EVENT_MONITOR() procedure
Example usage:
CREATE OR REPLACE PROCEDURE SNAP_AND_IMPORT()
LANGUAGE SQL
BEGIN
DECLARE SNAP_NAME CHAR(10);
DECLARE OLDEST_SNAP_NAME CHAR(10);
DECLARE SNAP_COMMENT VARCHAR(100);
SET SNAP_NAME = 'SNAP' CONCAT DAYOFYEAR(current date) CONCAT
SUBSTR(YEAR(current date),3,2);
SET OLDEST_SNAP_NAME = 'SNAP' CONCAT
DAYOFYEAR(current date - 60 days) CONCAT
SUBSTR(YEAR(current date - 60 days),3,2);
CALL QSYS2.DUMP_PLAN_CACHE_topN('SNAPSHOTS', SNAP_NAME, 100);
CALL QSYS2.IMPORT_PC_SNAPSHOT('SNAPSHOTS',
SNAP_NAME, 'Top 100 Queries-' CONCAT CHAR(CURRENT DATE));
CALL QSYS2.REMOVE_PC_SNAPSHOT('SNAPSHOTS', OLDEST_SNAP_NAME);
END;
CALL SNAP_AND_IMPORT();
70
Automated
capture & import
Automated
removal of oldest
detail
© 2014 IBM Corporation
IBM中国系统与科技研发中心 --- IBM i 实验室之旅深圳站
DRDA connections imprinted with client special register values
•
•
•
By branding DRDA connections with client special register default values, it
becomes easier to recognize and understand workload identity.
When using an IBM i Application Requestor (AR), the values will be imprinted
and flow to the AS.
When any AR connects to an IBM i Application Server (AS), these values will
appear if the register does not already contain a value
CLIENT PROGRAMID  'DRDA‘
CLIENT APPLNAME  pppvvrrm (for example 'QSQ07010‘) where:
• ppp identifies the AR product signature as follows:
 ‘ARI’ - DB2 for VM and VSE
 ‘DSN’ - DB2 for z/OS
 ‘QSQ’ - DB2 for i
 ‘SQL’ - all other DB2 products
• vv is a two-digit version identifier
• rr is a two-digit release identifier
• m is a one-character modification level
CLIENT USERID  The user ID on the AR
CLIENT WRKSTNNAME  The DB2 for i system name on the AR
CLIENT ACCTNG  The user's accounting code on the AR
71
7.1
DB2 PTF Group
SF99701 Level 29
© 2014 IBM Corporation
IBM中国系统与科技研发中心 --- IBM i 实验室之旅深圳站
DRDA connections imprinted with client special register values
•
Where does this information surface?
Answer: Pervasively throughout IBM i Navigator
For example… within Visual Explain
72
© 2014 IBM Corporation
®
IBM DB2
IBM中国系统与科技研发中心 --- IBM i 实验室之旅深圳站
®
for i Catalogs
Privileges
Catalogs
Routines
Statistics
SYSCATALOGS
INFORMATION_SCHEMA_CATALOG_NAME
Schemas
SYSSCHEMAS
SQLSCHEMAS
SCHEMATA
Database Support
SQL_FEATURES
SQL_LANGUAGES
SQL_SIZING
CHARACTER_SETS
SQLCOLPRIVILEGES
SQLTABLEPRIVILEGES
Tables Views Indexes
SYSCOLUMNS
SYSCOLUMNS2
SYSFIELDS
SYSINDEXES
SYSKEYS
SYSTABLEDEP
SYSTABLES
SYSVIEWDEP
SYSVIEWS
SYSCOLAUTH
SYSPACKAGEAUTH
SYSROUTINEAUTH
SYSSCHEMAAUTH
SYSSEQUENCEAUTH
SYSTABAUTH
SYSUDTAUTH
SYSVARIABLEAUTH
SYSXSROBJECTAUTH
Constraints
SYSCHKCST
SYSCST
SYSCSTCOL
SYSCSTDEP
SYSKEYCST
SYSREFCST
AUTHORIZATIONS
ROUTINE_PRIVILEGES
UDT_PRIVILEGES
USAGE_PRIVILEGES
VARIABLE_PRIVILEGES
SYSTRIGCOL
SYSTRIGDEP
SYSTRIGGERS
SYSTRIGUPD
SQLFOREIGNKEYS
SQLPRIMARYKEYS
COLUMNS
TABLES
VIEWS
CHECK_CONSTRAINTS
REFERENTIAL_CONSTRAINTS
TABLE_CONSTRAINTS
73
SQLFUNCTIONCOLS
SQLFUNCTIONS
SQLPROCEDURECOLS
SQLPROCEDURES
PARAMETERS
ROUTINES
SQLSTATISTICS
Miscellaneous Objects
SYSPACKAGE
SYSSEQUENCES
SYSTYPES
SYSVARIABLEDEP
SYSVARIABLES
XML Schemas
Triggers
SQLCOLUMNS
SQLSPECIALCOLUMNS
SQLTABLES
SYSCOLUMNSTAT
SYSINDEXSTAT
SYSMQTSTAT
SYSPACKAGESTAT
SYSPACKAGESTMTSTAT
SYSPARTITIONDISK
SYSPARTITIONINDEXES
SYSPARTITIONINDEXDISK
SYSPARTITIONINDEXSTAT
SYSPARTITIONMQTS
SYSPARTITIONSTAT
SYSPROGRAMSTAT
SYSPROGRAMSTMTSTAT
SYSTABLEINDEXSTAT
SYSTABLESTAT
SYSFUNCS
SYSJARCONTENTS
SYSJAROBJECTS
SYSPARMS
SYSPROCS
SYSROUTINEDEP
SYSROUTINES
XSRANNOTATIONINFO
XSROBJECTCOMPONENTS
XSROBJECTHIERARCHIES
XSROBJECTS
SQLTYPEINFO
SQLUDTS
DB2 for i catalog views (QSYS2)
ODBC and JDBC
TM
catalog views (SYSIBM)
ANS and ISO catalog views (QSYS2)
Java and all Java-based trademarks are trademarks of Sun Microsystems, Inc. in the United States, other countries, or both.
Other company, product or service names may be trademarks or service marks of others.
USER_DEFINED_TYPES
http://www.ibm.com/systems/i/software/db2/
© 2014 IBM Corporation
®
IBM DB2
IBM中国系统与科技研发中心 --- IBM i 实验室之旅深圳站
Health Center Procedures
QSYS2.HEALTH_ACTIVITY
QSYS2.HEALTH_DATABASE_OVERVIEW
QSYS2.HEALTH_DESIGN_LIMITS
QSYS2.HEALTH_ENVIRONMENTAL_LIMITS
QSYS2.HEALTH_SIZE_LIMITS
QSYS2.RESET_ENVIRONMENTAL_LIMITS
Utility Procedures
QSYS2.CANCEL_SQL
QSYS2.DUMP_SQL_CURSORS
QSYS2.EXTRACT_STATEMENTS
QSYS2.FIND_AND_CANCEL_QSQSRVR_SQL
QSYS2.FIND_QSQSRVR_JOBS
QSYS2.GENERATE_SQL
QSYS2.RESTART_IDENTITY
SYSTOOLS.CHECK_CST
SYSTOOLS.CHECK_SYSROUTINE
®
for i Services
Application Services
QSYS2.QCMDEXC - PROCEDURE
QSYS2.OVERRIDE_TABLE – PROCEDURE
QSYS2.DELIMIT_NAME – UDF
SYSPROC.WLM_SET_CLIENT_INFO – PROCEDURE
Security Services
QSYS2.FUNCTION_INFO – VIEW
QSYS2.FUNCTION_USAGE – VIEW
QSYS2.GROUP_PROFILE_ENTRIES – VIEW
SYSPROC.SET_COLUMN_ATTRIBUTE - PROCEDURE
QSYS2.SQL_CHECK_AUTHORITY - UDF
QSYS2.USER_INFO – VIEW
TCP/IP Services
QSYS2.TCPIP_INFO - VIEW
SYSIBMADM.ENV_SYS_INFO - VIEW
Work Management Services
QSYS2.SYSTEM_VALUE_INFO - VIEW
QSYS2.GET_JOB_INFO - UDTF
QSYS2.OBJECT_STATISTICS – UDTF
Storage Services
QSYS2.SYSDISKSTAT – VIEW
QSYS2.USER_STORAGE – VIEW
QSYS2.CHANGE_PLAN_CACHE_SIZE
QSYS2.DUMP_PLAN_CACHE
QSYS2.DUMP_PLAN_CACHE_PROPERTIES
QSYS2.DUMP_PLAN_CACHE_topN
QSYS2.END_ALL_PLAN_CACHE_EVENT_MONITORS
QSYS2.END_PLAN_CACHE_EVENT_MONITOR
QSYS2.START_PLAN_CACHE_EVENT_MONITOR (2)
Journal Services
QSYS2.DISPLAY_JOURNAL - UDTF
System Health Services
Performance Services
74
QSYS2.PTF_INFO - VIEW
QSYS2.GROUP_PTF_INFO - VIEW
Object Services
Plan Cache Procedures
SYSTOOLS.ACT_ON_INDEX_ADVICE - PROCEDURE
SYSTOOLS.HARVEST_INDEX_ADVICE - PROCEDURE
QSYS2.OVERRIDE_QAQQINI - PROCEDURE
QSYS2.RESET_TABLE_INDEX_STATISTICS PROCEDURE
QSYS2.SYSIXADV - TABLE
SYSTOOLS.REMOVE_INDEXES - PROCEDURE
PTF Services
DB2 for i Services
QSYS2.SYSLIMTBL - TABLE
QSYS2.SYSLIMITS – VIEW
IBM i Services
http://www.ibm.com/developerworks/ibmi/db2
© 2014 IBM Corporation
IBM中国系统与科技研发中心 --- IBM i 实验室之旅深圳站
DB2 for i – Navigator Enhancements
75
© 2014 IBM Corporation
IBM中国系统与科技研发中心 --- IBM i 实验室之旅深圳站
Navigator – what database users need to know
What are the
choices?
iAccess for Windows
(aka System i Navigator)
IBM Navigator for i
Where does it run?
Windows PC Install
Browser
Served from IBM i 6.1, 7.1 & 7.2
Recent service
level?
IBM i Access Windows Service Pack
IBM HTTP SERVER FOR i PTF Group:
7.2 - SF99713 Level 3  TR8
7.1 - SF99368 Level 29  TR8
6.1 - SF99115 Level 40
Database
commonality
Most features are identical, including
IBM i TR8 and IBM i 7.2
enhancements
Most features are identical, including
TR8 enhancements
Database
differences
Run SQL Scripts
Visual explain
PDI Perspectives
OmniFind administration
Webpage to watch
www03.ibm.com/systems/power/software/i/access/windows_sp.html
www-912.ibm.com/s_dir/SLINE003.NSF/PTFbyNumber/SF99368
www-912.ibm.com/s_dir/SLINE003.NSF/PTFbyNumber/SF99115
www-912.ibm.com/s_dir/SLINE003.NSF/PTFbyNumber/SF99713
Next (planned)
Update
December 31, 2014 
IBM i 7.1 TR9 & IBM i 7.2 TR1
December 31, 2014 
IBM i 7.1 TR9 & IBM i 7.2 TR1
76
7.1 – SI53584  TR8
© 2014 IBM Corporation
IBM中国系统与科技研发中心 --- IBM i 实验室之旅深圳站
DB2 for i – Navigator Enhancements in IBM i 7.1 TR8
Navigator feature
Improvement
Database folders
Preference control to
‘Retrieve all rows when populating the list’
Run SQL Scripts
Options  ‘Allow Save Results’ will enable result sets from
CALL statements to be saved
Visual Explain
Ability to launch multiple explains in one action
77
© 2014 IBM Corporation
IBM中国系统与科技研发中心 --- IBM i 实验室之旅深圳站
Preference control to
‘Retrieve all rows when populating the list’
Control how Schema lists
are populated
78
© 2014 IBM Corporation
IBM中国系统与科技研发中心 --- IBM i 实验室之旅深圳站
Visual Explain
Ability to launch multiple explains in one action
Power launch VE enmass
79
© 2014 IBM Corporation
IBM中国系统与科技研发中心 --- IBM i 实验室之旅深圳站
Options  ‘Allow Save Results’ enables ability to
save multiple result sets from a procedure CALL
Save secondary result sets
using one of 5 file types
80
© 2014 IBM Corporation
IBM中国系统与科技研发中心 --- IBM i 实验室之旅深圳站
DB2 for i – Navigator Enhancements in IBM i 7.1 TR9
Navigator feature
Improvement
Performance Monitor
New host variable control
Filter up to 10 user profiles or group names
Visual Explain
Visual Explain of UDTFs
Schema folder ->
Indexes
Ability to view index create timestamp and usage statistics
Schema folder -> Object View and change Media preference and Memory Preference of
table, partition and indexes
definition
SQL Detail for Jobs
81
View client host name and client port
© 2014 IBM Corporation
IBM中国系统与科技研发中心 --- IBM i 实验室之旅深圳站
Navigator  SQL Performance Monitors
• SQL Performance Monitors
 New HOSTVAR (*BASIC / *CONDENSED / *SECURE) control
 Enhanced ‘Filter by User’ control, support for 1-10 user or group names
82
© 2014 IBM Corporation
IBM中国系统与科技研发中心 --- IBM i 实验室之旅深圳站
Navigator  SQL Performance Monitors
• Enhancement requires both Navigator update and DB2 PTF Group.
• If you have an up level client and down level IBM i, you will see the
following…
Monitoring improvements appear, but are disabled when
working with IBM i partitions running older releases or
with older PTF Group levels
83
© 2014 IBM Corporation
IBM中国系统与科技研发中心 --- IBM i 实验室之旅深圳站
Navigator  Visual Explain of UDTFs
Visual Explain is enhanced to allow you to “drill down” into activity within UDTFs
• Works against Performance Monitors, Snapshots or the live SQL Plan Cache
• Returns statements executed within the UDTF, after the start time of the VE’d query
84
© 2014 IBM Corporation
IBM中国系统与科技研发中心 --- IBM i 实验室之旅深圳站
Navigator  Schemas Folder and Indexes
Working with existing indexes via Navigator includes usage detail, making it
possible to quickly gauge the value of an existing index.
• The addition of the ‘Date Created’ column provides more context for the usage
statistics
85
© 2014 IBM Corporation
IBM中国系统与科技研发中心 --- IBM i 实验室之旅深圳站
Navigator  Schemas Folder and Indexes
If you have ever customized this dialog, you have to use the Columns… control to
add the new column
Date Created column is empty when
working with IBM i partitions running older
releases or with older PTF Group levels
86
© 2014 IBM Corporation
IBM中国系统与科技研发中心 --- IBM i 实验室之旅深圳站
Navigator  Schemas Folder and Object Definition
Whether the object is a table, index, or partition, Navigator can be used to assess
the Media Preference & Memory Preference.
ON SSD
IN MEMORY
87
© 2014 IBM Corporation
IBM中国系统与科技研发中心 --- IBM i 实验室之旅深圳站
Navigator  Schemas Folder and Object Description
Use the Description action to see the Media and Memory Preference
You can change the settings and either:
• Complete the change by selecting ‘Ok’
• Select the ‘Show SQL’ button to see the equivalent SQL
IBM i 7.1
IBM i 7.2
88
© 2014 IBM Corporation
IBM中国系统与科技研发中心 --- IBM i 实验室之旅深圳站
Navigator  SQL Details for Jobs
SQL Details for Jobs is enhanced to show more insight into the environment of the
target job.
The Client port and Client host name values match what that job would see if it used
the DB2 Built-in Global variables SYSIBM.CLIENT_PORT & SYSIBM.CLIENT_HOST
Note: Right-click on the statement text window to see the Refresh and other actions
89
© 2014 IBM Corporation