Power Systems

Size: px

Start display at page:

Download "Power Systems"

楫草孔
5 years ago
Views:

1 DB2 for i 在 7.3 和 7.2 TR 中的最新更新 (March, 2017) Qi Guo Senior Software Engineer guoqi@cn.ibm.com

2 DB2 for i 标准兼容安全性可扩展性先进的功能性极佳的性能易用性易于维护核心价值 IBM i 7.2 Row and Column Access Control XMLTABLE CONNECT BY IBM i 7.1 OLAP Extensions XML Support TRANSFER OWNERSHIP Encryption enhancements (FIELDPROCs) Named arguments and Result set support in embedded SQL defaults for parameters CURRENTLY COMMITTED Obfuscation of SQL routines MERGE Array support in UDFs Global variables Timestamp precision Array support in procedures Multiple-action Triggers Three-part names and aliases Built-in Global Variables SQE Logical file support 1.7 Terabyte Indexes EVI enhancements Navigator Graphing and Charting Inline functions System Limits for IFS CREATE OR REPLACE DECLARE GLOBAL TEMPORARY TABLE Catalog views IBM i 7.3 Temporal Generated columns for auditing OLAP Extensions Regression Functions / Covariance EVI Only Access More Built-in Global Variables More SQL Scalar functions More IBM i Services CREATE OR REPLACE TABLE ATTACH & DETACH PARTITION DBE tooling and automation High priority customer requests 技术更新 (TR) November 2016 April 2016 November 2015 May 2015 SQL 新功能 JSON_TABLE() Fair Lock / No Lock LIMIT & OFFSET CREATE OR REPLACE TABLE 2017 International Business Machines Corporation 2

parameters on functions Built-in Global Variables Many other improvements System Limits Insight Temporary Storage IFS Top consumers 升级到 IBM i 7.

3 版本升级驱动因素数据库升级到 IBM i 7.2 Database performance SQE handles Native DB access New I/O Costing Model EVI Only Access Data-centric security Row & Column Access Control for SQL and DDS files Developer productivity Default parameters on functions Built-in Global Variables Many other improvements System Limits Insight Temporary Storage IFS Top consumers 升级到 IBM i 7.3 Data-centric history System-period Temporal table support for SQL tables and DDS created physical files Data-centric accountability Generated columns for SQL and DDS files Authority Collection to avoid excess authority On-Line Analytical Processing (OLAP) New OLAP built-in functions Improved capabilities for DB2 Web Query, Cognos Analytics and other BI tools Improved value from priced options DB2 SMP Parallel execution of OLAP DB2 Multisystem Attach/Detach partitions 2017 International Business Machines Corporation 3

4 知识中心 (Knowledge Center) 和 IBM i 7.3 请阅读 (PDF 包含活动链接 ) SQL Reference - What's New SQE Optimizer - What's New Temporal Tables - Administration Temporal Tables - Programming Generated Columns for Auditing On-Line Analytical Processing (OLAP) specifications OLAP specifications - Examples IBM i Navigator - database enhancements 2017 International Business Machines Corporation 4

5 DB2 for i 通过 DB2 PTF Groups 提供的新功能 7.2 TR4 7.2 TR5 7.3 GA 7.3 TR TR6 7.3 TR2 SF99702 Level 11 SF99703 Level 1 SF99702 Level 14 SF99703 Level 3 SF99702 Level 16 SF99703 Level 4 TR4 提供的新功能 Inlined UDTFs Trigger (re)deployment More IBM i Services New DB2 built-in Global Variables Enhanced SQL Scalar functions Evaluation option for DB2 SMP & DB2 Multisystem TR1 & TR5 提供的新功能 JSON_TABLE() INCLUDE for SQL Routines Database features in ACS Faster Scalar Functions More IBM i Services New DB2 for i Services And much more TR2 & TR6 提供的新功能 JSON predicates Additional Database features in ACS New and enhanced SQL Scalar Functions New IBM i Services Enhanced DB2 for i Services And more International Business Machines Corporation 5

6 IBM i 7.3 and 7.2 TR6 details Announce: February 14, 2016 Database PTF Group GA: March 31, 2016 IBM Knowledge Center Updates and IBM i developerworks landing pages go live on Announce date. and Point clients here! International Business Machines Corporation 6

7 目标群体谁应该关心这些数据库新功能? 1. 数据库应用开发人员新的 SQL 功能可以支撑现代化的解决方案以及提高程序员生产力 2. 系统管理员和安全控管人员新的 IBM i Services 提供对系统和安全的高级管理功能 3. 数据库管理员新的 ACS 功能提高了数据库管理员的生产力 2017 International Business Machines Corporation 7

8 DB2 for i 的 JSON 支持 DB2 JSON Store JSON_TABLE() JSON Query support: Predicates: IS JSON JSON_EXISTS June 2015 November 2016 March 2017 IBM i 7.1, 7.2, & 7.3 IBM i 7.2 & 7.3 IBM i 7.2 & International Business Machines Corporation 8

9 DB2 JSON Store 在 DB2 中存储 JSON 数据可以通过 command line 或者 Java API 访问 JSON 数据存储在数据库表的 BLOB 字段中目前不支持 Query 和索引 JSON Store: International Business Machines Corporation 9

10 JSON_TABLE 可以通过 SQL 查询以关系型数据方式处理 JSON 数据可以利用 HTTP 函数访问外部 JSON 数据源概念上类似于 XMLTABLE The powerful JSON_TABLE function International Business Machines Corporation 10

11 JSON IS JSON 查询谓词 IS or IS NOT JSON IS JSON 谓词用于判断一个值是否符合 JSON 格式检查 VALUE, ARRAY, OBJECT, 或 SCALAR -- Use a trigger to validate JSON CREATE TRIGGER VALIDATE_JSON BEFORE INSERT OR UPDATE OF JSON_DATA ON MYTABLE REFERENCING NEW AS N FOR EACH ROW MODE DB2ROW IF N.JSON_DATA IS NOT JSON OBJECT THEN IF INSERTING THEN SIGNAL SQLSTATE '75007' ('INSERT failed due to invalid JSON'); ELSE SIGNAL SQLSTATE '75007' ( UPDATE failed due to invalid JSON'); END IF; END IF; 2017 International Business Machines Corporation 11

JSON JSON_EXISTS 查询谓词 JSON_EXISTS JSON_EXISTS 返回真, 如果可以在 JSON 数据中查询到某个值可以使用 COALESCE 或者 IFNULL 处理空值 ( NULL) FALSE ON ERROR, TRUE ON ERROR, UNKNOWN ON ERROR, or ERROR ON ERROR 选项 -- Identify the

12 JSON JSON_EXISTS 查询谓词 JSON_EXISTS JSON_EXISTS 返回真, 如果可以在 JSON 数据中查询到某个值可以使用 COALESCE 或者 IFNULL 处理空值 ( NULL) FALSE ON ERROR, TRUE ON ERROR, UNKNOWN ON ERROR, or ERROR ON ERROR 选项 -- Identify the employees who do not have -- at least one emergency contact. SELECT empno, lastname FROM employee WHERE NOT JSON_EXISTS(COALESCE(json_data, ''), 'strict $.emergency' FALSE ON ERROR);e 2017 International Business Machines Corporation 12

13 新 SQL 内建函数 LISTAGG 将一组字符串聚合成一个字符串, 并用指定分隔符隔开 -- Produce a list of comma-separated names SELECT workdept, LISTAGG(lastname, ',') WITHIN GROUP(ORDER BY lastname) AS employees FROM employee GROUP BY workdept 2017 International Business Machines Corporation 13

14 新 SQL 内建函数 LISTAGG 支持其他产品不具备的溢出控制功能 2017 International Business Machines Corporation 14

新 SQL 内建函数 LISTAGG 溢出处理 : ON OVERFLOW ERROR ( 缺省值 ) 返回错误 ON OVERFLOW TRUNCATE 可选择返回多少字符串值被截断 -- Handle truncated lists SELECT workdept, LISTAGG(CAST(lastname AS

15 新 SQL 内建函数 LISTAGG 溢出处理 : ON OVERFLOW ERROR ( 缺省值 ) 返回错误 ON OVERFLOW TRUNCATE 可选择返回多少字符串值被截断 -- Handle truncated lists SELECT workdept, LISTAGG(CAST(lastname AS VARCHAR(4000), ',' ON OVERFLOW TRUNCATE WITH COUNT) WITHIN GROUP(ORDER BY lastname) AS employees FROM employee GROUP BY workdept 2017 International Business Machines Corporation 15

16 增强的 SQL 内建函数 LTRIM & RTRIM 增加了第二个可选参数 (trim-expression) -- Left Trim more than blanks values LTRIM(' $ 10.40', '$ ') '10.40' -- Remove outer noise values RTRIM( LTRIM('""Snazzy^--"', '"-^'), '-"^'); 'Snazzy' 2017 International Business Machines Corporation 16

17 DB2 for i 其他更新 RPG 改进 RPG 编译器 PTF 现在包含在 DB2 PTF Group 中嵌套数据结构 SQL 编程改进可以在触发器中使用 3-part name 增强了 HTTP Verbose 功能 SQL RPG 预编译器会生成自由格式代码数据库管理员改进索引建议效率提升改进的 ACS 功能 Database 架构极限值增强 SQL 语句中的最大常量个数扩展 4K 32K 2017 International Business Machines Corporation 17

MESSAGE_QUEUE_INFO 从消息队列中获取细节信息, 包括 QSYSOPR -- Examine all inquiry messages with responses SELECT a.message_text AS INQUIRY, b.message_text AS REPLY FROM qsys2.

18 MESSAGE_QUEUE_INFO 从消息队列中获取细节信息, 包括 QSYSOPR -- Examine all inquiry messages with responses SELECT a.message_text AS INQUIRY, b.message_text AS REPLY FROM qsys2.message_queue_info a INNER JOIN qsys2.message_queue_info b ON a.message_key = b.associated_message_key ORDER BY b.message_timestamp DESC; 2017 International Business Machines Corporation 18

19 OBJECT_PRIVILEGES 使用 SQL 获取和 Display Object Authority (DSPOBJAUT) 命令同样的细节信息 -- description: Review authorities for a user SELECT * FROM QSYS2.OBJECT_PRIVILEGES WHERE OBJECT_NAME = 'FRANKDBA' AND SYSTEM_OBJECT_SCHEMA = 'QSYS' AND OBJECT_TYPE = '*USRPRF'; 2017 International Business Machines Corporation 19

AUTHORIZATION_LIST_USER_INFO 使用 SQL 获取和 Display Authorization List (DSPAUTL) 命令相同的细节信息 -- -- description: List the public security settings -- for all

20 AUTHORIZATION_LIST_USER_INFO 使用 SQL 获取和 Display Authorization List (DSPAUTL) 命令相同的细节信息 description: List the public security settings -- for all authorization lists -- SELECT * FROM QSYS2.AUTHORIZATION_LIST_USER_INFO WHERE AUTHORIZATION_NAME = '*PUBLIC'; 2017 International Business Machines Corporation 20

AUTHORIZATION_LIST_INFO 使用 SQL 获取和 Display Authorization List Objects (DSPAUTLOBJ) 命令相同的细节 -- description: Count the types of objects tracked -- by all non-ibm authorization lists -- SELECT

21 AUTHORIZATION_LIST_INFO 使用 SQL 获取和 Display Authorization List Objects (DSPAUTLOBJ) 命令相同的细节 -- description: Count the types of objects tracked -- by all non-ibm authorization lists -- SELECT AUTHORIZATION_LIST, SYSTEM_OBJECT_TYPE, OBJECT_TYPE, COUNT(SYSTEM_OBJECT_TYPE) FROM qsys2.authorization_list_info WHERE AUTH_LIST NOT LIKE 'Q%' GROUP BY AUTHORIZATION_LIST, SYSTEM_OBJECT_TYPE, OBJECT_TYPE ORDER BY 1,4 DESC; 2017 International Business Machines Corporation 21

22 更多 services QSYS2.USER_INFO 增加了 Supplement Group 细节 QSYS2.LICENSE_INFO 增加了安装信息 SYSTOOLS.LICENSE_EXPIRATION_CHECK 自动检查许可证过期情况 2017 International Business Machines Corporation 22

reset the query usage counts in all indexes -- and remove any index advice for

reset_table_index_statistics ('TOYSTORE', '%', 'YES'); -- Reset counts and

23 增强的 DB2 for i Service QSYS2.RESET_TABLE_INDEX_STATISTICS -- For all the tables in the TOYSTORE schema, -- reset the query usage counts in all indexes -- and remove any index advice for the tables CALL qsys2.reset_table_index_statistics ('TOYSTORE', '%', 'YES'); -- Reset counts and remove index advice -- for indexes over the TOYSTORE/EMPLOYEE table CALL qsys2.reset_table_index_statistics ('TOYSTORE', 'EMPLOYEE', 'YES'); 2017 International Business Machines Corporation 23

24 IBM i Access Client Solutions (ACS) 2017 International Business Machines Corporation 24

25 ACS Version ( 计划发布 March, 2017) 执行 SQL 脚本 CL 提示改进的 SQL 高亮显示显示对象参考上传结果更多 Insert from Examples 模式文件夹打印机输出更多用户过滤器 IFS 过滤器 2017 International Business Machines Corporation 25

26 模式文件夹 - ACS 支持的操作 : Definition Description Generate SQL Properties Rename New Database Object Explain Data o View o Clear o Copy o Initialize More to come Actions also enabled in Visual Explain 2017 International Business Machines Corporation 26

DB2 (join) SQL DB2 11 SQL DB2 SQL 9.1 DB2 DB2 ( ) SQL ( ) DB2 SQL DB2 DB2 SQL DB2 DB2 SQL DB2 ( DB2 ) DB2 DB2 DB2 SQL DB2 (1) SQL (2) S

9 DB2 优化器 DB2 SQL select c1 c2 from ( DB2 )??? DB2?!?, no no DB2 I/O ( transrate overhead ) SQL DML (INSERT UPDATE DELETE) DB2 (access plan) DB2 (join) SQL DB2 11 SQL DB2 SQL 9.1 DB2 DB2 ( 728 747 ) SQL