IBM Power Systems

DB2 for i 7.1

- Hot Off the Grill

Kent Milligan
IBM STG Lab Services – DB2 for i CoE

© 2010 IBM Corporation

IBM Power Systems

DB2 for i Focus Areas

ƒ The Self Managing Database
– Reduced TCO thru automation
– Integration: Built-in Security and Auditing

ƒ Trusted Reliability & Scalability

– Simplified, best of breed scaling
– Integrated transaction management
– Advanced, flexible logging facilities

ƒ Open for Business IBM SOA Foundation

– SQL, the strategic interface Software
– Latest de facto standards

ƒ Innovative Applications
– SQL & Data-centric programming Skills &
Support

– Move to SOA over time

ƒ Business Intelligence
– Store, manage, and ANALYZE data!
– End user query and reporting to large scale
data warehousing
2 © 2010 IBM Corporation

1
IBM Power Systems

DB2 for i 7.1 Enhancements

Rapid Application Development Performance & Self-Tuning Enhancements
ƒ SQL & RPG Integration ƒ SQL Query Engine (SQE) enhancements
ƒ Stored procedure Result Set consumption – Adaptive Query Processing
ƒ FIELDPROC for transparent – Self-Learning Optimization
column-level encryption – Inline UDF query rewrite
ƒ XML Integration – Logical File on FROM support
– XML data type ƒ Indexing Advancements
– Annotated XML Decomposition – SQL Select/Omit Indexes
– SQL XML Publishing functions – EVI Aggregates
ƒ Three-part Aliases ƒ CPYFRMIMPF performance
ƒ Compatibility with DB2 Family & Oracle ƒ SSD & In-Memory Database Enablement
– MERGE statement ƒ OmniFind Text Search Server enhancements
– Array support & Global Variables
– REPLACE option on CREATEs Simplified Management
– Currently Committed supported ƒ IBM i Navigator Enhancements
ƒ JDBC & .NET enhancements – Progress Monitors – Alter Table, Index Build
– Index Advisor improvements
Trusted Reliability – Enhanced Generate SQL capability
ƒ Enhanced Remote Journal filtering – Object Folder content saves
ƒ Library-level Journaling filtering
ƒ IASP spanning transactions Data Intelligence & Interoperability
ƒ DB2 Web Query for System i
– Excel client support
– Microsoft SQL Server adapter
3 © 2010 IBM Corporation

IBM Power Systems

XML Integration

4 © 2010 IBM Corporation

2
 IBM Power Systems

XML Integration with DB2

ƒ Rich XML Support within DB2 for i –
integrated solution that replaces DB2 XML
Extender product
– New XML data type to simplify storage and retrieval
of XML documents
ƒ XML data access protected with rock-solid DB2
security
ƒ XML covered by Database Backup and
Recovery processes
– Annotated decomposition of XML documents into
DB2 columns
– Generate XML document with SQL-XML publishing
functions

ƒ IBM OmniFind Text Search Server provides

advanced, high-speed search capabilities for
stored XML documents
– Scope searches to specific elements of an XML
document: /book/title[. contains(“winning”) ]
– XQuery interface not yet supported
5 © 2010 IBM Corporation

IBM Power Systems

XML Data Type

<booking unitCharge="50" units="2" ƒ New XML data type
currency="USD"
status="confirmed"> – Support XML values up to 2 GB
<item>
<room hotelName="White Palace" – Type can be used for column, parameter, and
type="suite"
bookedFrom="2011-05-25" host variable values
bookedTo="2011-05-29“ />
ƒ XML Schema-based validation supported
CREATE TABLE INSERT INTO reservations(resdoc)
reservations VALUES(XMLVALIDATE(
XMLPARSE(DOCUMENT
( resID INTEGER
GET_XML_FILE('/dir1/[Link]'))
GENERATED
ACCORDING TO XMLSCHEMA
ALWAYS ID [Link]) )
AS IDENTITY,
resDoc XML )
ƒ XML File Reference variables enable simple
export XML documents to IFS
DB2 D MY_XMLFILE S SQLTYPE(XML_CLOB_FILE)
/free
MY_XMLFILE_NAME= '/[Link]';
MY_XMLFILE_NL = 9;
MY_XMLFILE_FO = SQFCRT;
exec sql SELECT resDOC INTO :MY_XMLFILE
FROM reservations WHERE resID=1;
6
/end-free © 2010 IBM Corporation

3
IBM Power Systems

Annotated XML Document Decomposition

1) Map the DB2 and Authors
XML document Books
relationships
XML Doc
…
<author id=22>
<name>Tony Dungy</name>
<book
isbn="1414318014" > 2) Define mapping in XSD file
<title>Quiet Strength</title> 3) Register and stored XSD
<publisher>
Tyndale House XSD mapping within DB2
</publisher> XML Schema Repository (XSR)
</book>
…

4) Decompose/Shred Authors
the XML document Books

7 © 2010 IBM Corporation

IBM Power Systems

SQL XML Publishing Example – XMLELEMENT & XMLATTRIBUTE

ƒ Generate XML values for employees celebrating 25th anniversary:
SELECT XMLSERIALIZE(
XMLELEMENT(NAME "employee", XMLATTRIBUTES([Link] as "id"),
XMLELEMENT(NAME "Name", [Link] ||' '|| [Link]),
XMLELEMENT (NAME "Extension",[Link]),
XMLELEMENT (NAME "DeptNo", [Link])) AS CLOB(100) ) as “XMLResult“
FROM employee e, department d
WHERE [Link] = [Link] AND Output for XMLResult:
YEAR(CURRENT DATE) – <employee id="000010">
YEAR(hiredate) = 25 <Name>JENNA HAAS</Name>
<Extension>0420</Extension>
<DeptNo>A00</DeptNo>
</employee>
-----------------------------
<employee id="000050">
<Name>JOSH GEYER</Name>
<Extension>1103</Extension>
<DeptNo>E01</DeptNo>
8
</employee> © 2010 IBM Corporation

4
IBM Power Systems

SQL XML Publishing Example - XMLAGG

ƒ Generate count and XML value for parts with specified type:
SELECT COUNT(*) AS PartCnt,
XMLSERIALIZE(
XMLELEMENT(NAME "Parts", XMLATTRIBUTES(parttype AS "type"),
XMLAGG(
XMLELEMENT(NAME "pid", partid) ORDER BY partid)
) AS CLOB(130)) AS PartList
FROM parts WHERE parttype IN ('C01', 'E21')
GROUP BY parttype

PartCnt PartList
2 <Parts type="C01"><pid>000130</pid><pid>200140</pid> </Parts>

3 <Parts type="E21"><pid>000320</pid><pid>100330</pid>
<pid>200340</pid></Parts>

9 © 2010 IBM Corporation

IBM Power Systems

SQL Enhancements

10 © 2010 IBM Corporation

5
 IBM Power Systems

Result Set Integration – Embedded SQL & SQL Routines

ƒ Programmers can now directly integrate stored procedure result sets with
embedded SQL & SQL Routines
– Key Enabler Statements: ASSOCIATE LOCATOR & ALLOCATE CURSOR
– Optionally, DESCRIBE PROCEDURE & DESCRIBE CURSOR statements can be used
to dynamically determine the number and contents of a result set
…
DECLARE sprs1 RESULT_SET_LOCATOR VARYING;
CALL GetProjs(projdept);
ASSOCIATE LOCATOR (sprs1) WITH PROCEDURE GetProjs;
ALLOCATE mycur CURSOR FOR RESULT SET sprs1;
SET totstaff=0;
myloop: LOOP
FETCH mycur INTO prname, prstaff;
Easy
IF row_not_found=1 THEN Integration!
LEAVE fetch_loop;
END IF;
SET totstaff= totstaff + prstaff;
IF prstaff > moststaff THEN
SET bigproj = prname;
SET moststaff= prstaff;
END IF;
END LOOP;
CLOSE mycur;
…
11 © 2010 IBM Corporation

IBM Power Systems

Stored Procedure Enhancements

ƒ Expressions on CALL statement

CALL myprocedure ( 1, UPPER(company_name), company_discountrate*100 )

ƒ ARRAY support for SQL Routines

– Enables exchange of data collections
– ARRAY element limited to simple data types
– ARRAY type can be used as parameter for SQL Routine or a local variable
– Interfaces supporting SQL Routine ARRAY parameters:
ƒ JDBC
ƒ SQL Routines
– Examples:

CREATE TYPE partids AS CHAR(3) ARRAY[10];

CREATE TYPE intarray AS INTEGER ARRAY[5];

12 © 2010 IBM Corporation

6
IBM Power Systems

Stored Procedure Enhancements – ARRAY Example

ƒ Return part type and quantity for the specified collection of parts
CREATE OR REPLACE PROCEDURE list_parts
(IN inparts partids, OUT part_qty intarray)
DYNAMIC RESULT SETS 1
LANGUAGE SQL
BEGIN
DECLARE cur1 CURSOR FOR SELECT [Link],part_qty,part_type
FROM parts, UNNEST(inparts) AS t(id) WHERE [Link] = part_id;
IF CARDINALITY( inparts )>5 THEN
SIGNAL SQLSTATE '38003'
SET MESSAGE_TEXT='Too many parts';
END IF;

SET part_qty = (SELECT ARRAY_AGG(part_qty)

FROM parts,UNNEST(inparts) AS t2(id)
WHERE [Link] = part_id);
OPEN cur1; OUTPUT
END; out_qty Array:
...
[1] = 25
[2] = 124
SET myparts = ARRAY[‘W12’,’S55’,’M22’];
[3] = 125
CALL list_parts(myparts, outqty);
13
... © 2010 IBM Corporation

IBM Power Systems

SQL Global Variables

ƒ Enables simpler sharing of values between SQL statements and SQL

objects (Triggers, Views, etc) across the life of a job/database connection

ƒ Example – Cache User Information

CREATE VARIABLE gvdept INTEGER DEFAULT
(SELECT deptno FROM employee WHERE empuserID = USER);
CREATE VIEW filtered_employee AS (
SELECT firstname, lastname, phoneno FROM employee WHERE deptno = gvdept);

SELECT firstname, phoneno FROM filtered_employee;

14 © 2010 IBM Corporation

7
IBM Power Systems

MERGE Statement
ƒ Allows application to use a single SQL statement to Update,
Delete, or Insert into a table based on values from a source
table/query

ƒ Simplifies applications trying to merge detailed transaction

data into a summary file
– Typical processing…
ƒ Perform INSERT when transaction type does NOT yet
exist in summary file
ƒ Perform UPDATE when transaction type does exist in
summary file to add to the total for that type

15 © 2010 IBM Corporation

IBM Power Systems

MERGE Statement – Syntax Details

ƒ Merge rows into the Account table. Updating the balance from the set of
transactions against an account ID and Inserting new accounts from that do not
already exist

MERGE INTO account AS a

USING
(SELECT id, SUM(amount) sum_amount FROM trans GROUP BY id) AS t
ON [Link] = [Link]
WHEN MATCHED THEN UPDATE SET balance = [Link] + t.sum_amount
WHEN NOT MATCHED THEN
INSERT (id, balance) VALUES ([Link], t.sum_amount)

16 © 2010 IBM Corporation

8
IBM Power Systems

Additional SQL Enhancements

ƒ REPLACE Option for CREATE statements
– Eliminates need for the Drop statement
– Preserves existing object dependencies & privileges!
– Supported objects: Alias, Function, Procedure, Sequence, Trigger, Variable, View
CREATE OR REPLACE ALIAS myAlias FOR schema.tab1
ƒ ALTER TABLE Enhancements
– ADD BEFORE column
– Identity Column support for existing columns

ƒ MQ Integration Functions
– MQSEND, MQRECEIVE,

ƒ RPG & SQL Long Name Integration

– New ALIAS keyword on externally described
data structures enables direct usage of
long SQL column names!
17 © 2010 IBM Corporation

IBM Power Systems

Performance Enhancements

18 © 2010 IBM Corporation

9
IBM Power Systems

DB2 Performance Enhancements

ƒ SQL Query Engine (SQE) Enhancements
– Support for Logical File on FROM clause
– Performance advancements
ƒ Background Self-Learning Query Optimization
ƒ Adaptive Query Processing
ƒ Global Statistics Cache
ƒ Inline User-Defined Function rewrite
ƒ SQE Indexing Advancements
– Optimizer awareness of SQL Select/Omit Indexes
– Encoded Vector Index Aggregate support
ƒ Improved CPYFRMIMPF performance (6.1 & 5.4 PTFs)
ƒ DB2 Object-level performance
– SSD Media Preference and Random/Sequential Usage Statistics
– OVRDBF … REUSEDLT(*NO) for faster Inserts/Writes
– In-Memory Database Enablements
CHGPF … KEEPINMEM(*YES)
CHGLF … KEEPINMEM(*YES)
19 © 2010 IBM Corporation

IBM Power Systems

SQE Adaptive Query Processing

ƒ Real-time self-learning query optimization

– Enables query plan to be changed while query is running
– Plan adjustments & query restart completely transparent
to the application
ƒ Intelligent monitor agents automatically assigned to
each query by SQE
– Monitoring starts after 2 seconds
– Periodically polling measures progress against estimates
and other plan assumptions
ƒ Real-time plan adjustments can include
– Change in join order
– Utilization of a new index
– …

No user
interaction
required!

20 © 2010 IBM Corporation

10
IBM Power Systems

DB2 SSD (Solid State Disks) Enablement

ƒ SSD can improve performance for some DB2 objects
– Large amount of random data access and…
– Data that is read many times, but written less
frequently
ƒ DB2 interfaces enhanced to allows a user to indicate an
SSD media preference on table, index, physical file, and
logical file
– SQL: UNIT SSD clause for object and partition
ƒ CREATE/ALTER TABLE
Associated Bank
ƒ CREATE INDEX
– CL: UNIT(*SSD) parameter Moving DB2 tables to SSD
ƒ CRTPF, CRTLF, and CRTSRCPF reduced month end batch run time
ƒ CHGPF, CHGLF, and CHGSRCPF by 40%! *
5.4 - Database Group SF99504 #23
# of SAS # of Batch
6.1 - Database Group SF99601 #10 Disk SSDs Run
Drives Time
ƒ ALTER and CHGPF/LF interfaces support asynchronous
movement of data and indexes 72 0 4:22

ƒ Key DB2 7.1 Addition - New random and sequential 72 8 2:43

statistics for tables and indexes 60 4 2:48
*[Link]
21 © 2010 IBM Corporation

IBM Power Systems

IBM OmniFind Text Search Server Enhancements

ƒ Product (5733-OMF) originally released after GA of IBM i 6.1
– Common DB2 Family text search support
– Supports text columns and text documents (PDF, DOC, PPT, …)
– High-speed, advanced linguistic searches
CONTAINS( feedDoc, ‘California insurance settlement’) = 1
CONTAINS( textFld, ‘Man wins claim’) = 1

ƒ OmniFind 7.1 Enhancements

– Enhanced XML search support
ƒ Date and Date Time comparisons:
/Book[pubDate > xs:date(“2005-04-15”)]
ƒ Numeric comparisons:
/Book[Cost <= 59.95]
ƒ Namespace specific searches
– Enhanced Save / Restore capabilities
– Graphical text index management

22 © 2010 IBM Corporation

11
IBM Power Systems

Column-Level Encryption

23 © 2010 IBM Corporation

IBM Power Systems

FIELDPROC - Seamless Column-Level Encoding and Decoding

Data Access
New Data

1111 2222 3333 4444

Decrypt
1111 2222 3333 4444

Transparent FIELDPROC Encoding & Decoding

En
cr
yp
t

r3vS#45zt!J9*m$p6

24 © 2010 IBM Corporation

12
IBM Power Systems

FIELDPROC Programs – What can they do?

ƒ Developers have freedom to implement virtually any column

encoding & decoding scheme
– Encryption (3rd party solutions: Linoma Software and others)
– Data compression
– Text normalization
– …

ƒ FieldProc program requirements

– Program must be an ILE program object & contain no SQL
– Handle 3 different events:
ƒ Fieldproc registration to define encoded attributes
ƒ Write operations to encode data
ƒ Read operations to decode data

25 © 2010 IBM Corporation

IBM Power Systems

FIELDPROC Programs – When are they used?

ƒ FieldProc Write/Encode Events

– SQL Insert, Update, & Merge statements
– Native record-level writes
– Query searches: WHERE cardnumber=‘1111222233334444’
– “Writing” CL Commands: CPYF, RGZPFM, STRDFU, …
– Trigger Processing
ƒ Fieldproc processing occurs after BEFORE triggers
ƒ Fieldproc processing occurs before AFTER triggers

ƒ FieldProc Read/Decode Events

– SQL Select & Fetch
– Native record-level writes
– “Reading” CL commands: CPYF, RGZPFM, DSPPFM, …
– Trigger processing
26 © 2010 IBM Corporation

13
IBM Power Systems

FIELDPROC Programs – How do they get called?

ƒ FieldProc Registration interface - SQL !

CREATE TABLE ccstore(
custid CHAR(5),
cardnum CHAR(16) FIELDPROC mylib/ccpgm,
cardexp DATE )

ALTER TABLE orders ALTER COLUMN cardnum

SET FIELDPROC mylib/ccpgm

ƒ FieldProc Removal
ALTER TABLE orders ALTER COLUMN cardnum
DROP FIELDPROC
27 © 2010 IBM Corporation

IBM Power Systems

FIELDPROC - Implementation Considerations

ƒ Consider utilization of 3rd party encryption solutions for complete

implementation

ƒ Consider converting physical file DDS definitions to SQL before using

SQL ALTER TABLE for Fieldproc registration
– CHGPF can accidentally remove Fieldproc registrations!
– Techniques exist for avoidin program recompiles after SQL conversion

ƒ Consider pitfalls of conditional decoding (ie, masking) solutions

28 © 2010 IBM Corporation

14
IBM Power Systems

Additional Information
ƒ DB2 for i Websites
– Home Page: [Link]/systems/i/db2
– DeveloperWorks Zone: [Link]/developerworks/db2/products/db2i5OS
– Porting Zone: [Link]/partnerworld/i/db2porting
ƒ Newsgroups & Forums
– USENET: [Link], [Link]-db2
– DeveloperWorks: [Link]
– System i Network DB2 Forum: [Link]
ƒ Education Resources - Classroom & Online
– [Link]/systemi/db2/[Link]
– [Link]/partnerworld/wps/training/i5os/courses
ƒ DB2 for i Publications
– White Papers: [Link]/partnerworld/wps/whitepaper/i5os
– Online Manuals: [Link]/systems/i/db2/[Link]
– DB2 for i Redbooks ([Link]
ƒ Getting Started with DB2 Web Query for System i (SG24-7214)
ƒ OnDemand SQL Performance Analysis … in V5R4 (SG24-7326)
ƒ Preparing for and Tuning the SQL Query Engine on DB2 for i5/OS (SG24-6598)
ƒ Modernizing iSeries Application Data Access (SG24-6393)
29 © 2010 IBM Corporation

IBM Power Systems

·Are you experiencing performance problems?

·Are you using SQL?
·Are you getting the most out DB2 for i?
SL
O W IBM DB2 for i Center of Excellence

9 Database modernization
9 DB2 WebQuery
9 Database design, features and functions
9 DB2 SQL performance analysis and tuning
9 Data warehousing and Business Intelligence
9 DB2 for i education and training
·Need help?
Contact: Mike Cain mcain@[Link]
IBM Systems and Technology Group
Rochester, MN USA

30 © 2010 IBM Corporation

15