V5R4新功能(more)

V5R4新功能:
 
一、For Query,
1)Indexes Advised; 
2)SQE Plan Cache;
– No direct index advice
– Index advice via Snapshot data or Visual Explain
3)SQE Plan Cache Snapshots;
– Enhanced SQE index advised
– “3020” records to show multiple indexes for same table
– Temporary index created
4)Detailed Database Monitor
– Enhanced SQE index advised
– “3020” records to show multiple indexes for same table
– Temporary index created
 
Other（query 2）：

System wide index advice
–Data is placed into a DB2 table (QSYS2/SYSIXADV)
–Autonomic & No Overhead
? CQE and SQE support
–CQE only provides basic advice based on local selection predicates
–SQE provides complex advice based on all parts of the query
? Not complete, but much better
? GUI interface via iSeries Navigator
–Advice for System, or Schema, or Table
? System only adds (summary) rows, user must manage the data
–Options to clear or prune
? Can create indexes directly from GUI
–Additional indexing analysis might be required to determine the optimal index
–*NEW* - Index Advice Condenser now available
? http://www.ibm.com/developerworks/db2/library/techarticle/dm-0701forstie/
? Need iSeries Access for Windows Dec 2006 service pack &
& i5/OS PTFs: SI25391, SI25469 and SI25470
 
二、For SQL，
– 2-MB SQL statement maximum
– 1,000 tables per query
– 128-byte column names
– 1024 parameters for stored procedure
– 32-KB index keys and ORDER BY statement
 
三、For MQT:
1）支持分区MQT共享；
2）支持多个，或全部MQT query（v5r3下，一个query最多只允许一个MQT);
 
四、For RPGIV 编程：
1）集合RPGIV自由格式与嵌入SQL编程；
 
五、iSeries Access for Windows
1）.NET provider enhancements
– LOB column support**
– System naming and library list support**
– MS FW 2.0 compatibility**
– Boost Intellisense support to aid programmers
– Multiple active result sets on a connection
– Customizable string processing **Available with latest i5/OS V5R3 service pack
2）JDBC (Version 3 currency and performance)
– Optimization goal connection attribute
3）IBM Enterprise Workload Management (EWLM) support added to:
– CLI, DRDA, .NET, ODBC, and JDBC
4）Driver support for Windows Vista
– Depending on availability
5）OLE database driver
– System naming and library list support
6）ODBC driver
– Optimization goal connection attribute
 
六、DB2 Connect Unlimited Edition for iSeries
1）New customized package for iSeries developer community (available since 3rd quarter 2005)
2）Affordable access to:
– DB2 UDB add-ins for Visual Studio
– Federated Data access
   ? Joining of DB2 UDB tables in different iSeries partitions
   ? Joining of DB2 UDB tables across iSeries and Linux, UNIX, and Windows servers
– Extend iSeries data to mobile devices: DB2 Mobility on Demand
 
七、Application development
1）CLI enhancements
– Maximum handles limit doubled to 160,000
– SQLFetchScroll, block-fetch and column-wise binding
– Column-wise blocked insert binding
– Optimization goal connection attribute
– Cursor sensitivity statement attribute
– Improved XA documentation
– New SQLGetInfo and SQLColAttributes options
– Max rows attribute (SQL_ATTR_MAX_ROWS)
2）XA over DRDA
3）Redesigned SQL descriptor area (SQLDA)
– Support for longer column names
– Faster internal processing
4）New SET SESSION authorization statement
– Better SQL auditing with the ability to supply actual user of remote connections
– New SESSION USER and SYSTEM USER special registers
– Considerations:
   ? Requires *ALLOBJ authority to execute
   ? Frees DB2 UDB for iSeries resources, which can impact performance
   ? Might require resetting other settings (such as SQL Path)
 
八、Stored procedures
1）DB2 UDB expression evaluator for faster SQL procedural language (existing objects must be recreated)
2）Easier authority setup with support for DFTRDBCOL and DYNDFTCOL attributes
– Useful when porting from other database management systems
– Also available on OS/400 V5R2 and i5/OS V5R3 with latest database group PTF
? Simpler maintenance with new ALTER PROCEDURE statement ALTER PROCEDURE Increase_Level
REPLACE
BEGIN
   DECLARE CurLvl INT;
   SELECT edlevel INTO CurLvl FROM emptbl
      WHERE empno=Emp#;
   IF NwLvl > CurLvl THEN
      UPDATE emptbl SET edlevel=NwLvl, salary=salary + (salary*0.10)
         WHERE empno=Emp#;
   END IF
END
3）Plans to support debug environment for DB2 UDB Development Center
 
九、OLAP
 
1)OLAP expressions (ROW_NUMBER)
? ROW_NUMBER — use to arbitrarily assign a number to rows in the final result set

SELECT ROW_NUMBER() OVER (ORDER BY workdept, lastname)
   AS Nbr, lastname, salary
      FROM employee
         ORDER BY workdept, lastname
 
SELECT workdept, lastname, hiredate,ROW_NUMBER()
   OVER (PARTITION BY workdept ORDER BY hiredate)
      AS Nbr
         FROM employee
            ORDER BY workdept, hiredate
 
2)OLAP expressions (RANK and DENSE_RANK)
? RANK and DENSE_RANK for highlighting a data attribute (independent of the result set sorting)

SELECT empno, lastname, salary+bonus
   AS TOTAL_SALARY,RANK() OVER (ORDER BY salary+bonus DESC)
      AS Salary_Rank
         FROM employee WHERE salary+bonus > 30000 ORDER BY lastname
 
十、Recursive SQL
? Recursive common table expressions
– Useful for navigating tables where rows are inherently related to other rows in same table (bills of material, organizational hierarchies, and so forth)
– Example:
WITH emp_list (level, empid, name)
   AS (SELECT 1, empid, name
     FROM emp
        WHERE name = 'Carfino‘
           UNION ALL
           SELECT o.level + 1, next_layer.empid, next_layer.name
              FROM emp as next_layer, emp_list o
                 WHERE o.empid = next_layer.mgrid )
                    SELECT level, name FROM emp_list
 
十一、SELECT advancements
1）Subquery and scalar fullselect
UPDATE MyExchangeRates m SET conversion_rate=
(SELECT rate FROM EuropeRates e WHERE e.countryid=m.country_id
UNION
SELECT rate FROM AsiaRates a WHERE a.countrycode=m.country_id)/100
– Scalar fullselect is allowed anywhere an expression is supported
(for example, Col1+50)
2）CASE, CAST, BETWEEN predicate, column function, and so forth
3）Multicolumn predicates
   –…WHERE (c1,c2) IN (SELECT a,b FROM…)
   –…WHERE (c1,c2) = (SELECT a,b FROM…)
4）Exclusive lock option (use with caution)
SELECT * FROM orders
   WHERE order_id = ‘E5100’
     WITH RS USE AND KEEP EXCLUSIVE LOCKS
 
十二、Richer toolbox of SQL functions
? Triple DES data encryption (ENCRYPT_TDES)
– Will be available across the DB2 UDB family
– Data encryption white paper
ibm.com/servers/enable/site/education/abstracts/4682_abs.html
? Date and time processing
– LAST_DAY, NEXT_DAY, ADD_MONTHS, VARCHAR_FORMAT
? Statistical processing
– STDDEV_SAMP and VARIANCE_SAMP
? Miscellaneous
– GENERATE_UNIQUE
– TABLE_NAME
– RAISE_ERROR
SELECT emp_name,
CASE job_type
WHEN 1 THEN ‘Programmer’
WHEN 2 THEN ‘Administrator’
WHEN 3 THEN ‘Project Manager’
WHEN 4 THEN ‘Manager’
ELSE RAISE_ERROR(‘70001’, ‘Invalid JobType’) END
FROM employee
 
十三、INSTEAD OF triggers
1)New trigger type changes semantics of INSERTs, UPDATEs, and DELETE
operations against a view (must be defined over an SQL view)
– Many views are read-only because of derivations, joins, grouping, and so forth.
– INSTEAD OF triggers are useful in setting up encryption to be semi-transparent.
– New support builds on base INSTEAD OF trigger support delivered via i5/OS V5R3 PTF (ibm.com/iseries/db2/iot.html) by providing support for join views, and others.

CREATE VIEW empdept AS
  SELECT empno, firstname, lastname, deptname FROM employee, department
    WHERE workdept=deptno

CREATE TRIGGER UpdateJoin
  INSTEAD OF UPDATE ON empdept
    REFERENCING OLD ROW AS o NEW ROW AS n
      FOR EACH ROW
BEGIN
  UPDATE employee
    SET empno=n.empno, firstname=n.firstname, lastname=n.lastname
    WHERE empno=o.empno;
  UPDATE department SET deptname = n.deptnameWHERE deptname=o.deptname;
END

十四、Miscellaneous SQL and DB2 enhancements
1)ANS timestamp format (‘2005-06-13 01:22:03.000444‘)
2)Support for LABEL ON INDEX
3)Optional journaling for SQL tables (NOT LOGGED INITIALLY)
  CREATE TABLE new_materials
    AS (SELECT * FROM materials WHERE YEAR(item_date)=2006)
      WITH DATA NOT LOGGED INITIALLY
4)Simpler SQL and command line integration with new qcmdexc stored procedure
  CALL qcmdexc(‘ADDLIBLE DBLIB2’, 15)
    instead of CALL qcmdexc ('ADDLIBLE DBLIB2', 0000000015.00000)
5)Syntax flagger for SQL core standard
 
（to be continued）