2014 IBM i 实验室之旅北京站 DB2 for IBM i 7.2 与 7.1 TR7 概览.

Presentation on theme: "2014 IBM i 实验室之旅北京站 DB2 for IBM i 7.2 与 7.1 TR7 概览."— Presentation transcript:

1 2014 IBM i 实验室之旅北京站 DB2 for IBM i 7.2 与 7.1 TR7 概览

2 © 2014 IBM Corporation 22 Source:If applicable, describe source origin 日程 DB2 for IBM i 7.2 预览 7.1 TR7 中的 DB2 功能更新

3 © 2014 IBM Corporation 33 Source:If applicable, describe source origin 日程 DB2 for IBM i 7.2 预览 7.1 TR7 中的 DB2 功能更新

4 © 2014 IBM Corporation 44 Source:If applicable, describe source origin 7.1 XML Support Encryption enhancements (FIELDPROCs) Result set support in embedded SQL CURRENTLY COMMITTED MERGE MQ Functions Global variables Array support in procedures Partition table enhancements Three-part names and aliases SQE Logical file support SQE Adaptive Query Processing EVI enhancements Inline functions CREATE OR REPLACE V5R4 WebQuery SSD Memory Preference On Demand Performance Center Health Center Completion of SQL Core Scalar fullselect Recursive CTE INSTEAD OF triggers Descriptor area XA over DRDA DDM 2-phase Scrollable cursor 2M SQL statement 1000 tables in a query SQE Stage 5 Implicit journaling enhancements V5R3 Partitioned tables UFT-8 and UTF-16 ICU sort sequence MQTs Sequences Implicit char/numeric BINARY/VARBINARY GET DIAGNOSTICS DRDA Alias DECIMAL(63) SQE Stage 3 Ragged SWA QDBRPLAY Online Reorganize 6.1 Omnifind MySQL storage engine DECFLOAT Grouping sets /supergroups INSERT in FROM VALUES in FROM Extended Indicator Variables Expression in Indexes ROW CHANGE TIMESTAMP Statistics catalog views CLIENT special registers SQE Stage 6 DDM and DRDA IPv6 Deferred Restore of MQT and Logicals Environmental limits V5R2 SQE Stage 1 IASPs Identity columns Savepoints UNION in views Scalar subselect UDTFs DECLARE GLOBAL TEMPORARY TABLE Catalog views JDBC V3.0 DRDA Kerberos Journal Standby V5R1 SQL triggers Java Functions DRDA DUW TCP/IP 2 GB LOBs 1 Terabyte Table Journal Minimal Data Two-phase over TCP/IP DDL Journaling Database Navigator Generate SQL Continual Investment and Innovation 7.2 Row and Column Access Control XMLTABLE CONNECT BY OLAP Extensions Regression Functions/Covarianc e/Correlation TRANSFER OWNERSHIP Named arguments and defaults for parameters Obfuscation of SQL routines Array support in UDFs Timestamp precision Multiple-action Triggers Built-in Global Variables Record movement between partitions on UPDATE 1.7 Terabyte Indexes Health Center – Non-database limits Navigator Graphing and Charting DB2 for i 标准兼容性 安全性 可扩展性 领先的功能 优异的性能 易于使用 易于维护 客户价值

5 © 2014 IBM Corporation 55 Source:If applicable, describe source origin DB2 for i 7.2 新功能要点 数据安全 –Row and Column Access Control (RCAC) –TRANSFER OWNERSHIP –GRANT TO GROUP / USER –Remote journal and SSL (possibly PTF to 7.1) 应用开发 –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

6 © 2014 IBM Corporation 66 Source:If applicable, describe source origin 行列访问控制 Row and Column Access Control (RCAC) IBM Advanced Data Security for i (Boss option 47) 行级别访问控制 – 根据用户所在的组（或其他条件）控制 用户是否能看到表中的所有或者部分记 录 列级别访问控制 – 根据用户所在的组（或其他条件）控制 用户是否能看到列中的真实数据或者经 过掩码后的数据 PO #Customer #Class Credit Card # 12468 Common 3702 0001 0002 21357 VIP 6222 6001 9999 销售经理 Security Admin 定义 RCAC 销售代表 PO #Customer #Class Credit Card # 12468 Common #### 0002 PO #Customer #Class Credit Card # 12468 Common 3702 0001 0002 21357VIP6222 6001 9999 同一查询

7 © 2014 IBM Corporation 77 Source:If applicable, describe source origin 行列访问控制 Row and Column Access Control (RCAC) 由 Security Admin 使 用 SQL 语法定义 – 用户功能组 QIBM_DB_SECADM 对所有数据存取接口都 有效 (SQL, RPG Native, JDBC, CPYF, etc) 可以定义在物理文件上 面，原有应用无需改动 CURRENT_USER 特殊 寄存器 (special register) 可以获取当前 用于验证权限的用户 ID –Adopted authority 被考 虑在内 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; ALTER TABLE EMPLOYEE ACTIVATE COLUMN ACCESS CONTROL; CREATE PERMISSION NETHMO.ROW_ACCESS ON HOSPITAL.PATIENT FOR ROWS WHERE(VERIFY_GROUP_FOR_USER(CURRENT_USER,'PATIENT' ) = 1 AND HOSPITAL.PATIENT.USERID = CURRENT_USER) OR (VERIFY_GROUP_FOR_USER(CURRENT_USER,'PCP') = 1 AND HOSPITAL.PATIENT.PCP_ID = CURRENT_USER) OR (VERIFY_GROUP_FOR_USER(CURRENT_USER,'MEMBERSHI P') = 1 ENFORCED FOR ALL ACCESS ENABLE; ALTER TABLE HOSPITAL.PATIENT ACTIVATE ROW ACCESS CONTROL;

8 © 2014 IBM Corporation 88 Source:If applicable, describe source origin 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 是用户还是用户组

9 © 2014 IBM Corporation 99 Source:If applicable, describe source origin Timestamp 精度提升 目前 7.1 及之前的版本的 TIMESTAMP 类型仅支持小数点后 6 位精度（微秒） 对某些类型的应用精度不够，对另外一些则不需要如此高精度 7.2 里用户可以指定 TIMESTAMP 使用 0 – 12 位精度 支持 TIMESTAMP 数据类型的地方都可以指定 CREATE TABLE x (C1 TIMESTAMP(12), -- 需要额外的 12 位精度 C2 TIMESTAMP(0)) -- 不需要非整数的精度

10 © 2014 IBM Corporation 10 Source:If applicable, describe source origin 命名参数和参数缺省值 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)

11 © 2014 IBM Corporation 11 Source:If applicable, describe source origin 自治存储过程 Autonomous Procedures 自治存储过程是运行在一个独立的事务中的存储过程 ，与调用它的事务完全隔离。 自治存储过程内部可以执行 Commit 或者 rollback Commit 和 rollback 独立于外部事务 自治存储过程使用 activation group QSQAUTOAG. CREATE PROCEDURE writelog (loginfo VARCHAR(1000) AUTONOMOUS BEGIN … INSERT INTO MJATST.T1 VALUES(LOGINFO); … END

12 © 2014 IBM Corporation 12 Source:If applicable, describe source origin 对 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

13 © 2014 IBM Corporation 13 Source:If applicable, describe source origin 在单一 SQL 触发器中支持多种事件类型 可以使用单个 SQL 触发器处理 INSERT ， UPDATE 和 DELETE 事件。 易于管理，安装和维护。 下面的例子中，单一触发器可以： a) 插入新员工记录的时候递增员工号 b) 删除员工记录时递减员工号 c) 更新薪酬超限的时候出发警告 PTFed back to 7.1 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; END

14 © 2014 IBM Corporation 14 Source:If applicable, describe source origin 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; 动态复合（ DYNAMIC COMPOUND ） 语句 支持对一个动态的复合语句进行 PREPARE 和 EXECUTE COMPOUND 语句指的是 BEGIN 和 END 包围起来的语句集合 之前只能通过存储过程实现 可以使用变量，错误处理和所有的控制语句 支持 ATOMIC 和 NOT ATOMIC

15 © 2014 IBM Corporation 15 Source:If applicable, describe source origin 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

16 © 2014 IBM Corporation 16 Source:If applicable, describe source origin 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;

17 © 2014 IBM Corporation 17 Source:If applicable, describe source origin SQE 对原生数据访问的支持 SQE = Performance! 从 7.2 开始，下面的数据访问接口会使用 SQE 作为缺省引擎 OPNQRYF QUERY/400 此外，如果被访问的文件或表上定义有 RCAC 的话，对它的所有打开操作 都将由 SQE 处理。例如： RPG open of PF, LF CPYF, CPYFRMIMP, … Any database file open operation 此行为可以使用 QAQQINI 配置文件的中的 SQE_NATIVE_ACCESS 参数控制 。

18 © 2014 IBM Corporation 18 Source:If applicable, describe source origin 数据库管理 Support of all new SQL features -Permissions -Masks -Named arguments and parameter defaults -Obfuscation of SQL routines -Arrays in user-defined functions Display Journal GUI (PTF back to 7.1) Graphing and Charting Multi-member Support (PTF back to 7.1) 健康中心 非数据库极限值追踪 On Demand 性能中心 Advanced Compare Run SQL Scripts in IBM Navigator for i (Browser) Enhanced Show Statements (PTF back to 6.1) Enhanced Plan Cache Filtering (For Index Advice) Filter Database monitor on SQLCODE and CQE IBM i Navigator 更新

19 © 2014 IBM Corporation 19 Source:If applicable, describe source origin 日程 DB2 for IBM i 7.2 预览 7.1 TR7 中的 DB2 功能更新

20 © 2014 IBM Corporation 20 Source:If applicable, describe source origin Technology Refresh 7 VLDB - IBM i 支持更大数据 SQL Indexes 最大支持 1.7TB 自动跟踪 DB2 表和索引的增长 实时移动 DB2 表和索引到 SSD DB2 for i 支持解决方案 使用动态复合语句进行高级 SQL 编程 远程 3-part name – 通过 RDB 别名访问 RPG 完全自由格式和嵌入式 SQL 现代化 – 从 DDS 到 SQL DDL 更加简便易行 改进了 SQL 生成 DDL 支持，更好的支持 Keyed DDS 文件 CREATE TABLE AS 添加了字段引用信息 DB2 for i 系统管理 catalogs – a new breed of data DB2 for I 易用性 使用 SQL 查询 Audit 和 IFS Journals Restore 支持延迟日志恢复

21 © 2014 IBM Corporation 21 Source:If applicable, describe source origin 跟踪系统极限值 – 功能要点 IBM i 操作系统极限值 记载在 Availability Maximum Capacities 这本书中 http://publib.boulder.ibm.com/infocenter/iseries/v7r1m0/topic/rzamp/rzam p.pdf http://publib.boulder.ibm.com/infocenter/iseries/v7r1m0/topic/rzamp/rzam p.pdf 通过安装 DB2 PTF Group (6.1) SF99601 Level 22 and (7.1) SF99701 Level 29, 可以自动跟踪系统中的极限值 当某一种资源消耗到超过了一个预定义的下限时，或者达到了某一个预定 义的增量的时候，系统会自动追踪并记录 追踪数据储存在 DB2 表中，通过监控此表用户可以立即采取行动或进行事 后分析

22 © 2014 IBM Corporation 22 Source:If applicable, describe source origin 跟踪系统极限值 – 第一阶段支持 极限值根据下面进行分组： 数据库 安全 工作管理 文件系统 备份 / 恢复 日志 等等 … 极限值有下列分类： 1) 系统极限值 2)ASP 极限值 3) 作业极限值 4) 目标极限值 6.17.1 DB2 PTF Group SF99601 Level 29 DB2 PTF Group SF99701 Level 22 作业极限值 ( 阶段 1) 一个事务中被锁定的最大记录数 一个事务中被更改的最大记录数 系统极限值 ( 阶段 1) 最大活动作业数 ASP 极限值 ( 阶段 1) 最大 spool 文件数 目标极限值 ( 阶段 1) 最大文件成员数

23 © 2014 IBM Corporation 23 Source:If applicable, describe source origin 跟踪系统极限值 – 第二阶段支持（ TR8 ） 第二阶段 2 – 支持更多的极限值，同时集成到 IBM i Navigator’s DB2 Health Center Database category: 1.Maximum number of all rows in a partition 2.Maximum number of valid rows in a partition 3.Maximum number of deleted rows in a partition 4.Maximum number of overflow rows in a partition 5.Maximum number of variable-length segments 6.Maximum number of indexes over a partition Journal category: 1.Maximum size of a 1TB journal receiver 2.Maximum number of objects that can be associated with a *MAX10M journal 3.Maximum number of objects that can be associated with a *MAX250K journal 4.Maximum sequence number for a *MAXOPT3 journal 5.Maximum sequence number for a *MAXOPT1 or *MAXOPT2 journal File System category: Maximum number of object description entries in a library 6.17.1 DB2 PTF Group SF99601 Level 31 DB2 PTF Group SF99701 Level 26 Take a look at the data: SELECT * FROM QSYS2/SYSLIMITS IBM i Navigator (installed client) IBM i Access Windows TR7-timed Service Pack for 7.1 IBM Navigator for i (browser) IBM HTTP SERVER FOR i PTF Group (7.1) - SF99368 Level 22

24 © 2014 IBM Corporation 24 Source:If applicable, describe source origin 3-part name 的 RDB 别名支持 在 3-part name 中可以使用 RDB Alias 指定 RDB 名字 例如 – 从不同数据库中取得当天的销售数据 ADDRDBDIRE RDB(X1423P2 MYALIAS) RMTLOCNAME(X1423P2 *IP) INSERT INTO WORKTABLE SELECT * FROM MYALIAS.SALESLIB.DAILY_SALES CHGRDBDIRE RDB(LP13UT16 MYALIAS) RMTLOCNAME(LP13UT16 *IP) INSERT INTO WORKTABLE SELECT * FROM MYALIAS.SALESLIB.DAILY_SALES WorkTable X1423P2 LP13UT16 好处 : SQL 语句不 需要改变 7.1 DB2 PTF Group SF99701 Level 26

25 © 2014 IBM Corporation 25 Source:If applicable, describe source origin 实时将 DB2 表和索引移动到 SSD 上 TR7 中， DB2 表、视图和索引的媒体选项可以实时更改，不再需要文件上 的完全排他锁。 用户可以更改媒体选项，即使文件上有 shared read 锁 (*SHRRD) 存在. 当用户指定 *SSD 后，系统会异步的将文件数据移动到 SSD 上。 此功能支持 SQL 语句和 IBM i CL 命令： oALTER TABLE STORE123.EMPLOYEE ALTER UNIT SSD oCHGPF FILE(STORE123/EMPLOYEE) UNIT(*SSD) oCHGLF FILE(STORE123/XEMP2) UNIT(*SSD) 如果您还未使用 SSD ，请参考 : http://www.ibm.com/developerworks/ibmi/library/i-ssdperformance/index.html & http://db2fori.blogspot.com/2013/08/db2-for-i-hitting-mark-again.html http://www.ibm.com/developerworks/ibmi/library/i-ssdperformance/index.html http://db2fori.blogspot.com/2013/08/db2-for-i-hitting-mark-again.html 6.17.1 DB2 PTF Group SF99601 Level 31 DB2 PTF Group SF99701 Level 26 增强对 SSD 的支持

26 © 2014 IBM Corporation 26 Source:If applicable, describe source origin DB2 for i – IBM i Services 对于非数据库 任务，也可以 使用 SQL 快速 获取信息 安全配置，系 统健康，系统 数据分析及更 多

27 © 2014 IBM Corporation 27 Source:If applicable, describe source origin QSYS2/USER_STORAGE 视图 SQL 视图，基于 Retrieve User Information (QSYRUSRI) API. 用户可以看到其他用户的信息，只要拥有对其他用户账户的 *READ 权限 用户存储按照 SYSBAS 和 iASP 分类 例子 : -- 用户 SCOTTF 使用了多少存储空间？ SELECT * FROM QSYS2/USER_STORAGE WHERE USER_NAME = ‘SCOTTF’ IBM i 6.1IBM i 7.1 DB2 PTF Group SF99601 Level 31 DB2 PTF Group SF99701 Level 26 New with TR7

28 © 2014 IBM Corporation 28 Source:If applicable, describe source origin DB2 for i – Explaining the technology Enhancements delivered by PTF are documented here: www.ibm.com/developerworks/ibmi/techupdates/db2 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 http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/topic/db2/rbafz.pdf The “IBM i 7.1 Technical Overview” Redbook gets updated with database enhancements: http://www.redbooks.ibm.com/abstracts/sg247858.html 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/XMLonDB2forihttp://bit.ly/httpAndDB2forihttp://bit.ly/XMLonDB2fori We explore some Technology topics with articles: www.ibm.com/developerworks/ibmi/library www.ibm.com/developerworks/ibmi/library http://ibm.com/systems/i/db2

29 © 2014 IBM Corporation 29 Source:If applicable, describe source origin Backup

30 © 2014 IBM Corporation 30 Source:If applicable, describe source origin SYSCATALOGS INFORMATION_SCHEMA_CATALOG_NAME Catalogs SQLSCHEMAS SCHEMATA Schemas SYSSCHEMAS SYSCHKCST SYSCST SYSCSTCOL SYSCSTDEP SYSKEYCST SYSREFCST SQLFOREIGNKEYS SQLPRIMARYKEYS CHECK_CONSTRAINTS REFERENTIAL_CONSTRAINTS TABLE_CONSTRAINTS Constraints SYSCOLUMNS SYSCOLUMNS2 SYSFIELDS SYSINDEXES SYSKEYS SYSTABLEDEP SYSTABLES SYSVIEWDEP SYSVIEWS SQLCOLUMNS SQLSPECIALCOLUMNS SQLTABLES COLUMNS TABLES VIEWS Tables Views Indexes SYSTRIGCOL SYSTRIGDEP SYSTRIGGERS SYSTRIGUPD Triggers SYSPACKAGE SYSSEQUENCES SYSTYPES SYSVARIABLEDEP SYSVARIABLES SQLTYPEINFO SQLUDTS USER_DEFINED_TYPES Miscellaneous Objects SYSFUNCS SYSJARCONTENTS SYSJAROBJECTS SYSPARMS SYSPROCS SYSROUTINEDEP SYSROUTINES SQLFUNCTIONCOLS SQLFUNCTIONS SQLPROCEDURECOLS SQLPROCEDURES PARAMETERS ROUTINES RoutinesStatistics SYSCOLUMNSTAT SYSINDEXSTAT SYSMQTSTAT SYSPACKAGESTAT SYSPARTITIONDISK SYSPARTITIONINDEXES SYSPARTITIONINDEXDISK SYSPARTITIONINDEXSTAT SYSPARTITIONMQTS SYSPARTITIONSTAT SYSPROGRAMSTAT SYSTABLEINDEXSTAT SYSTABLESTAT SQLSTATISTICS DB2 for i catalog views (QSYS2) ODBC and JDBC TM catalog views (SYSIBM) ANS and ISO catalog views (QSYS2) SQL_FEATURES SQL_LANGUAGES SQL_SIZING CHARACTER_SETS Database Support XSRANNOTATIONINFO XSROBJECTCOMPONENTS XSROBJECTHIERARCHIES XSROBJECTS XML Schemas Privileges SQLCOLPRIVILEGES SQLTABLEPRIVILEGES AUTHORIZATIONS ROUTINE_PRIVILEGES UDT_PRIVILEGES USAGE_PRIVILEGES VARIABLE_PRIVILEGES SYSCOLAUTH SYSPACKAGEAUTH SYSROUTINEAUTH SYSSCHEMAAUTH SYSSEQUENCEAUTH SYSTABAUTH SYSUDTAUTH SYSVARIABLEAUTH SYSXSROBJECTAUTH 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. http://www.ibm.com/systems/i/software/db2/ IBM ® DB2 ® for i Catalogs

31 © 2014 IBM Corporation 31 Source:If applicable, describe source origin 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 Performance Services QSYS2.HEALTH_ACTIVITY QSYS2.HEALTH_DATABASE_OVERVIEW QSYS2.HEALTH_DESIGN_LIMITS QSYS2.HEALTH_ENVIRONMENTAL_LIMITS QSYS2.HEALTH_SIZE_LIMITS QSYS2.RESET_ENVIRONMENTAL_LIMITS Health Center ProceduresPTF Services QSYS2.PTF_INFO - VIEW QSYS2.GROUP_PTF_INFO - VIEW DB2 for i Services IBM i Services http://www.developerworks.com/ibmi/db2 QSYS2.CANCEL_SQL QSYS2.DUMP_SQL_CURSORS QSYS2.FIND_AND_CANCEL_QSQSRVR_SQL QSYS2.FIND_QSQSRVR_JOBS SYSTOOLS.CHECK_CST SYSTOOLS.CHECK_SYSROUTINE Utility Procedures 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.CHANGE_PLAN_CACHE_SIZE QSYS2.DUMP_PLAN_CACHE QSYS2.DUMP_PLAN_CACHE_PROPERTIES QSYS2.END_ALL_PLAN_CACHE_EVENT_MONITORS QSYS2.END_PLAN_CACHE_EVENT_MONITOR QSYS2.START_PLAN_CACHE_EVENT_MONITOR (2) Plan Cache Procedures System Health Services QSYS2.SYSLIMTBL - TABLE QSYS2.SYSLIMITS – VIEW QSYS2.OVERRIDE_TABLE - PROCEDURE SYSPROC.WLM_SET_CLIENT_INFO – PROCEDURE Application Services Journal Services QSYS2.DISPLAY_JOURNAL - UDTF Storage Services QSYS2.SYSDISKSTAT – VIEW QSYS2.USER_STORAGE – VIEW Object Services QSYS2.OBJECT_STATISTICS – UDTF IBM ® DB2 ® for i Services QSYS2.QCMDEXC - PROCEDURE