DB2 11 for z/OS Highlights DB2 for z/OS Smart Seminar Nashville

IBM Software Group
DB2 11 for z/OS
Highlights
DB2 for z/OS Smart Seminar
Nashville
March 18, 2014
Robert Catterall, IBM
[email protected]
© 2015 IBM Corporation
Migration Planning Workshop
DB2 11 for z/OS
Agenda
 Connectivity enhancements
 Storage enhancements
 Serviceability
 SQL enhancements
 SQL optimization
 Temporal update and DB2-managed data archiving
 XML
 Index enhancements
 Security enhancements
 Advanced design options
2
© 2014
IBM
Corporation
© 2015
IBM
Corporation
Migration Planning Workshop
DB2 11 for z/OS
Connectivity enhancements
 Enhanced client info fields
 Cancel thread
 Bind/DDL/utility break-in
3
© 2014
IBM
Corporation
© 2015
IBM
Corporation
Migration Planning Workshop
DB2 11 for z/OS
Enhanced client information fields
 Longer client information fields for distributed applications
– Pre-DB2 11: long value set by client API is truncated
– DB2 11 (NFM): value set by the API supported by extended lengths
• Tolerated in CM, but still truncated to 16 bytes (same as pre-V11)
– Support for longer client information fields requires APPLCOMPAT(V11R1)
 Longer field support for these special registers:
– CLIENT_USERID supports up to 128 bytes
– CLIENT_WRKSTNNAME supports up to 255 bytes
– CLIENT_APPLNAME supports up to 255 bytes
– CLIENT_ACCTNG supports up to 255 bytes
 Longer fields supported in DDF traces, messages and displays in CM
4
© 2014
IBM
Corporation
© 2015
IBM
Corporation
Migration Planning Workshop
DB2 11 for z/OS
Exploiting enhanced client information fields
 Resource Limit Facility (RLF) can govern SQL
statement activity based on longer client information
fields in NFM
 System profile monitoring supports longer client
information fields in NFM
 WLM classification rules can utilize longer client
information fields when running z/OS V2R1
5
© 2014
IBM
Corporation
© 2015
IBM
Corporation
Migration Planning Workshop
DB2 11 for z/OS
Cancel thread enhancement
 Pre DB2 11 CANCEL DDF THREAD may not work in some cases
– Historically "soft", or reactive in nature
– Cancel detection point may not be encountered in a timely manner
 DB2 11 New FORCE keyword on CANCEL THREAD command
– Purges thread of a remote connection in the DB2
– FORCE option accepted only after CANCEL THREAD issued without
the FORCE option
– Available in CM
 Purge running DDF SRB using new z/OS purge SRB function
– Requires new z/OS CALLRTM TYPE=SRBTERM service
• Available in z/OS 1.13 or later via APAR OA39392
6
© 2014
IBM
Corporation
© 2015
IBM
Corporation
Migration Planning Workshop
DB2 11 for z/OS
BIND/DDL break-in
 RELEASE(DEALLOCATE)
– RELEASE is a BIND /REBIND option to handle table space locks
and package sections allocated to a thread
• Default is COMMIT – free on commit
• DEALLOCATE – retain until thread deallocation
– Opportunity for performance benefit for:
• Long running batch with frequent COMMIT
• Transactional thread reuse (e.g., CICS protected threads, highperformance DBATs)
– Historically, what has restrained use of RELEASE(DEALLOCATE)?
• Virtual storage constraints (pre V10)
• DDF workload (pre V10)
• The need to break in for BIND/DDL/utility operations
7
© 2014
IBM
Corporation
© 2015
IBM
Corporation
Migration Planning Workshop
DB2 11 for z/OS
BIND/DDL/utility break-in
 DB2 10 DDF threads
– To enable high-performance DBATs:
• Rebind packages with RELEASE(DEALLOCATE)
• Issue command -MODIFY DDF PKGREL(BINDOPT)
– To disable
• –MODIFY DDF PKGREL(COMMIT)
 No good solution for non-DDF thread in DB2 10
 DB2 11 DDF threads
– High-performance DBAT enablement same as for DB2 10
– To disable
• Automatically done on next COMMIT if BIND/REBIND, DDL, or utility is blocked
 DB2 11 non-DDF threads
– Automatically done on next COMMIT if BIND/REBIND, DDL, or utility is blocked
– Idle threads break in via PM95929, PM96001, PM96004
8
© 2014
IBM
Corporation
© 2015
IBM
Corporation
Migration Planning Workshop
DB2 11 for z/OS
Storage enhancements
 Hardware-related storage exploitation
 Buffer pool enhancements
9
© 2014
IBM
Corporation
© 2015
IBM
Corporation
Migration Planning Workshop
DB2 11 for z/OS
Hardware-related storage exploitation

1 MB page frames
– Can boost performance by making virtual storage to real
storage address translation more CPU-efficient (thanks to
reduction in translation lookaside buffer “misses”)
– DB2 11: log buffers backed by 1 MB frames
• Allocation precedence over buffer pools
• May want to increase LFAREA value (in IEASYSxx member of
PARMLIB) to accommodate log buffers
– Exploit pageable 1 MB frames
• Buffer pool control blocks (not data pages)
• Z 13 or EC12 or BC12 server required
• Flash Express memory required
10
© 2014
IBM
Corporation
© 2015
IBM
Corporation
Migration Planning Workshop
DB2 11 for z/OS
Buffer pool enhancements

FRAMESIZE attribute of ALTER BUFFERPOOL
–
DB2 11 allows FRAMESIZE(2G)
•
•
•
–
PGFIX(YES) required
z13 or EC12 server required
If PGFIX(NO), then buffer pool allocated using 4K pages
DB2 11: buffer pool control blocks can go in pageable 1 MB frames
•
Buffer pool control blocks (also known as page manipulation blocks, or PMBs)
typically occupy 4-5% of the size of the corresponding buffer pool
– Heavily accessed (so it’s good to have them in 1MB page frames, for CPU
efficiency)
– Not the object of read or write I/O operations (so no need to page-fix them)
• Requires z13 or EC12 server and Flash Express
11
© 2014
IBM
Corporation
© 2015
IBM
Corporation
Migration Planning Workshop
DB2 11 for z/OS
More buffer pool enhancements
 Attributes VPSIZEMIN, VPSIZEMAX (WLM-managed buffer pool sizing)
– Applies to AUTOSIZE(YES) pools
– VPSIZEMIN(integer | *)
• Integer is minimum number of buffers to allocate
• * is default, indicates DB2 will set the minimum value to 75% of current size
– VPSIZEMAX(integer | *)
• Integer is maximum number of buffers to allocate
• * is default, indicates DB2 will set the maximum value to 125% of current size
 Buffer pool GETPAGE reclassification
– DB2 9/DB2 10 : GETPAGEs associated with dynamic and list prefetch were
classified as random
– DB2 11: GETPAGEs associated with any type of prefetch (sequential or dynamic
or list) are classified as sequential
12
© 2014
IBM
Corporation
© 2015
IBM
Corporation
Migration Planning Workshop
DB2 11 for z/OS
Serviceability
 Work file space instrumentation
 ACCESS DB
 Client special registers and global variable
 zIIP offload expansion
13
© 2014
IBM
Corporation
© 2015
IBM
Corporation
Migration Planning Workshop
DB2 11 for z/OS
Work file table space instrumentation
 New system parameters for work file alerts
– WFSTGUSE_AGENT_THRESHOLD
• Agent-level warning
• Percentage of work file space in use by an agent
• Message DSNI052I issued when exceeded
– Once per unit of work
– WFSTGUSE_SYSTEM_THRESHOLD
• System-level warning
• Percentage of work file space in use by the system
• Message DSNI053I issues when exceeded
– If WFDBSEP=YES
• The percentages are of the segregated temporary spaces
• Messages indicate if due to DGTTs, work files, or both
14
© 2014
IBM
Corporation
© 2015
IBM
Corporation
Migration Planning Workshop
DB2 11 for z/OS
New option for ACCESS DATABASE command
 Externalize real-time statistics to the catalog tables
– ACCESS DB(*) SP(*) MODE(STATS)
• Retrofitted to DB2 10
• Wildcarding is supported, but can impact performance
• Wildcard options:
–
–
–
–
15
DB(*) SP(*) for subsystem level
DB(db-name) SP(*) for database level
DB(db-name) SP(space-name) for table space level
DB(db-name) SP(space-name) PART(part-#) for partition level
SVC
© 2014
IBM
Corporation
© 2015
IBM
Corporation
Migration Planning Workshop
DB2 11 for z/OS
Client special register and global variable
 CLIENT_CORR_TOKEN special register
– Client specified correlation information
– 255 bytes
– Defaults to the generated client Logical Unit of Work ID (LUWID)
– Reported in DISPLAY THREAD
• DSNV402I message V442 section
– Can be SET via various APIs:
•
•
•
•
sqleseti
JDBC
SET_CLIENT_ID function
SIGNON
 CLIENT_IPADDR global variable
– Set to IP address obtained from the network
– Currently requires the 10.5 level IBM Data Server driver
16
© 2014
IBM
Corporation
© 2015
IBM
Corporation
Migration Planning Workshop
DB2 11 for z/OS
zIIP offload expansion
 zIIP for all asynchronous processing executed as SRB mode system agents
(except for P-lock negotiation in data sharing mode)
 Not expected to assist as much as V10, but can provide increase zIIP use
 Up to 100% of processing for asynchronous enclave service request blocks
that execute in the DB2 MSTR, DIST, and DBM1 address spaces
– Log write and read
– Pseudo-deleted index entry clean-up
– XML multi-version clean up
 Expanded RUNSTATS and LOAD redirect
– Enclave SRB tasks for LOAD, REORG, and REBUILD INDEX inline stats
– Enclave SRB tasks for RUNSTATS column group distribution stats
– Enclave SRB tasks for LOAD REPLACE index management
17
© 2014
IBM
Corporation
© 2015
IBM
Corporation
Migration Planning Workshop
DB2 11 for z/OS
SQL Enhancements
 Application compatibility
 Global variables
 SQL PL enhancements
– Arrays
– Autonomous transactions
 GROUP BY extensions
– Grouping sets
– Super groups
 Hadoop Integration
 JSON support
18
© 2014
IBM
Corporation
© 2015
IBM
Corporation
Migration Planning Workshop
DB2 11 for z/OS
Application compatibility
 Occasionally SQL functions change behavior
– Usually in support of family compatibility and SQL standards
– This introduces an application incompatibility which can
• Delay version migrations
• Potentially create a single version charge issue
V9
App
V10
App
– These are documented in the Release Incompatibilities
 Examples of DB2 10 incompatibilities and resolutions
– CHAR function results with decimal argument
• Leading zeroes no longer returned when there is a decimal point
• ZPARM BIF_COMPATIBILITY was introduced to reverse the V10 behavior
– Strong data typing for non-Java stored procedures
• ZPARM DDF_COMPATIBILITY reversed this behavior
19
© 2014
IBM
Corporation
© 2015
IBM
Corporation
Migration Planning Workshop
DB2 11 for z/OS
Application compatibility
 DB2 11 fences DML behavior change in CM with APPLCOMPAT
– Does not fence DDL or DCL
– Separates application migration from the system migration
• Application migration can begin after the system migration is complete
• Or the application migration can be delayed for up to 2 future DB2 versions
 APPLCOMPAT ZPARM and bind parameter
– V10R1
• DML behaves as it did for DB2 10
• Must use V10R1 until NFM
• Attempting to use new features under V10R1 results in SQLCODE -4743
– V11R1
• Requires the subsystem / group to be in NFM
• New DML behavior is introduced
• Also required for new features of V11
20
© 2014
IBM
Corporation
© 2015
IBM
Corporation
Migration Planning Workshop
DB2 11 for z/OS
Application compatibility
 ZPARM APPLCOMPAT is default value for BIND / REBIND parameter
– No effect on existing packages (or REBIND PACKAGE w/ APPLCOMPAT specified)
– Defaults to V11R1 for new installations and to V10R1 for migrations
 BIND / REBIND parameter
– Defaults to ZPARM APPLCOMPAT value
– V11R1 will cause an error before NFM
• Once NFM, can BIND / REBIND with V10R1 or V11R1
– REBIND existing application when ready for new behavior
– REBIND existing application when wanting to use new features
• WARNING: Must also be ready for potential behavioral changes
 CURRENT APPLICATION COMPATIBILITY special register for dynamic SQL
– Defaults to package APPLCOMPAT
– Cannot be set to V11R1 until NFM
• Once NFM, can bet set to V10R1 or V11R1
21
© 2014
IBM
Corporation
© 2015
IBM
Corporation
DB2 11 for z/OS
Migration Planning Workshop
Global variables
 The need: how can you pass data values from one
SQL statement to another in the context of a thread?
– Before DB2 11:
• Do it with application code
• Want a trigger to be able to access those values? Not easy…
– DB2 11: use global variables
 You can create your own global variables using the
new CREATE VARIABLE statement
– DB2 11 also provides a few built-in global variables:
• SYSIBM.CLIENT_IPADDR
More on this archive stuff to come
• SYSIBMADM.GET_ARCHIVE
• SYSIBMADM.MOVE_TO_ARCHIVE
}
22
© 2014
IBM
Corporation
© 2015
IBM
Corporation
Migration Planning Workshop
DB2 11 for z/OS
Global variables
example
23
© 2014 IBM Corporation
Migration Planning Workshop
DB2 11 for z/OS
Array parameters and variables
 DB2 11: an array-type parameter can be passed as input to
(or received as output from from) a native SQL procedure
– CALL can come from another SQL PL routine, or from Java or .NET
client (if Java or .NET, need IBM Data Server Driver type 4 driver)
– SQL UDFs can also receive or return arrays
For .NET client, support is
for array as input parameter
 Arrays can also be variables in SQL PL routines
 Array first created via DDL as user-defined data type (UDT)
 Built-in functions are provided to:
– Construct arrays
– Derive tables from arrays
– Obtain information about arrays
– Navigate array elements
24
© 2014
IBM
Corporation
© 2015
IBM
Corporation
Migration Planning Workshop
DB2 11 for z/OS
More on array parameters
 2 types:
– Ordinary
• User-defined limit on # of elements (default: INTEGER high value)
• Elements referenced by their ordinal position in the array
– Associative
• No user-defined upper bound on number of elements
• Elements ordered by and can be referenced via an array index value
• Values in a given array index are INTEGER or VARCHAR, are
unique, and don’t have to be contiguous
 CREATE examples:
Ordinary
Associative
25
© 2014
IBM
Corporation
© 2015
IBM
Corporation
Migration Planning Workshop
DB2 11 for z/OS
Autonomous transactions
 Refers to native SQL procedures that can commit
work outside the commit scope of the calling program
 Enabled via the AUTONOMOUS keyword in CREATE
PROCEDURE and ALTER PROCEDURE statements
– An alternative to the COMMIT ON RETURN option
26
© 2014
IBM
Corporation
© 2015
IBM
Corporation
Migration Planning Workshop
DB2 11 for z/OS
More on autonomous transactions
 An autonomous procedure always commits before
returning to the caller
– This will NOT commit changes made by the calling application
process
 An autonomous procedure’s unit of work is independent
from that of the calling application
– Does not share locks with the caller (and so could conceivably
contend with the caller)
– Can be canceled by terminating the invoker
 An autonomous procedure cannot be called by another
autonomous procedure
27
© 2014
IBM
Corporation
© 2015
IBM
Corporation
Migration Planning Workshop
DB2 11 for z/OS
New grouping option: GROUPING SETS
Example: determine average total compensation for WorkDept, Job, and EdLevel sets
28
© 2014 IBM Corporation
Migration Planning Workshop
DB2 11 for z/OS
New grouping option: ROLLUP
 Example: determine average total compensation for the various
hierarchies of workdept, job, and edlevel, and for overall set
– Column order in GROUP BY expression matters
– ORDER BY helps with readability
29
© 2014 IBM Corporation
Migration Planning Workshop
DB2 11 for z/OS
New grouping option: CUBE
 Example: determine average total compensation for various
combinations of workdept, job, and edlevel
– Column order in GROUP BY expression doesn’t matter
– ORDER BY helps with readability
30
© 2014 IBM Corporation
Migration Planning Workshop
DB2 11 for z/OS
Hadoop integration with DB2 11 for z/OS
A new user-defined function (UDF)
allows a data analytics job,
specified in JAQL, to be
submitted to a BigInsights system
IBM BigInsights
(now available for Linux on System z)
A new table UDF reads the
output of the analytics job and
returns it in relational form
31
© 2014
IBM
Corporation
© 2015
IBM
Corporation
Migration Planning Workshop
DB2 11 for z/OS
JSON support
 PI05250 & PI10521 delivers JSON in DB2 11
 Available via IBM DB2 Accessories Suite for z/OS, V3.1
– PID 5697-Q04, Announcement 213-395
 See informational APAR II14727
– 10.5 FP3 Data Server Client
 developerWorks documentation
32
© 2014
IBM
Corporation
© 2015
IBM
Corporation
Migration Planning Workshop
DB2 11 for z/OS
SQL optimization
 APREUSE
 Statistics feedback
 Real-time stats changes
 Query transformations
 Runtime optimizations
 DPSI improvements
33
© 2014 IBM Corporation
Migration Planning Workshop
DB2 11 for z/OS
APREUSE
 DB2 10 APCOMPARE / APREUSE stability across BIND and REBIND
– APREUSE options were NONE/NO or ERROR
 DB2 11 provides APREUSE(WARN)
– Attempts to hint the same access path for matched statements
– When the access path cannot be maintained, a new one is calculated for those
specific matching statements
– APREUSE(WARN) will allow the BIND / REBIND to continue
 DB2 10 APREUSE(ERROR) ... EXPLAIN(ONLY) may represent invalid plan
– In DB2 11, this will rolled backed
34
© 2014
IBM
Corporation
© 2015
IBM
Corporation
DB2 11 for z/OS
Migration Planning Workshop
Statistics feedback
 Optimizer can provide feedback on missing statistics during access
path selection and EXPLAIN processing
 Access path selection (BIND / REBIND / PREPARE)
– Externalized to new catalog table SYSSTATFEEDBACK
• Table added in CM
• Feedback is externalized asynchronously starting in NFM
– At interval indicated by STATSINT in ZPARM, or
– With ACCESS DB command
– Controlled by ZPARM STATFDBK_SCOPE
• DYNAMIC, STATIC, NONE, ALL
Default
– Additional control at the table level
• SYSTABLES column STATS_FEEDBACK can be set to ‘Y’ (default) or ‘N’
– RUNSTATS for an object removes these associated recommendations
35
© 2014
IBM
Corporation
© 2015
IBM
Corporation
Migration Planning Workshop
DB2 11 for z/OS
Statistics feedback
 NUMCOLUMNS
– # of columns in COLGROUP
 COLGROUPCOLNO
– Hex array of column numbers for tooling use
 TYPE
– ‘C’ardinality
• Use the COLUMN or COLGROUP option
– ‘F’requency
• Use FREQVAL option (COUNT = Floor(10, COLCARF-1))
– ‘H’istogram
• Use HISTOGRAM option
– ‘I’ndex
• Collect basic index stats
– ‘T’able
• Collect basic table stats
36
© 2014
IBM
Corporation
© 2015
IBM
Corporation
Migration Planning Workshop
 REASON
DB2 11 for z/OS
Statistics feedback
– BASIC
• A basic statistical value is missing
– KEYCARD
• Index column cardinalities missing
– LOWCARD
• Low cardinality, likely skew
– NULLABLE
• No distribution stats available for a nullable column
– DEFAULT
• Predicate likely on a default value
– RANGEPRD
• No histogram on a range predicate
– PARALLEL
• Could be improved by balancing data
– CONFLICT
• Collect consistent stats at the same point in time
– COMPFFIX
• Multi-column cardinality stats are needed
37
© 2014
IBM
Corporation
© 2015
IBM
Corporation
Migration Planning Workshop
DB2 11 for z/OS
Real-time statistics changes, resetting catalog statistics
 Catalog changes
– New SYSTABLESPACESTATS columns
• UPDATESIZE (BIGINT): bytes added / removed via UPDATE operations since
creation, REORG, or LOAD REPLACE
• LASTDATACHANGE (TIMESTAMP): last update time for this row in RTS
 ACCESS DB ... MODE(STATS)
– Externalizes in-memory statistics to real-time statistics tables
• Also externalizes optimizer feedback information
– Wildcarding is supported
 New RESET ACCESSPATH option for RUNSTATS
– Removes collected statistics
38
© 2014
IBM
Corporation
© 2015
IBM
Corporation
Migration Planning Workshop
DB2 11 for z/OS
Query transformation
 Common stage 2 predicates transformed to indexable...
– Requires REBIND
– Column expression predicates (where no index on expression exists)
• YEAR(date column) op :hv or ? or literal
• DATE(timestamp column)
• SUBSTR(left most portion)
39
© 2014
IBM
Corporation
© 2015
IBM
Corporation
Migration Planning Workshop
DB2 11 for z/OS
Query transformation
 Common stage 2 predicates transformed to indexable
– x BETWEEN col1 AND col2
40
© 2014
IBM
Corporation
© 2015
IBM
Corporation
Migration Planning Workshop
DB2 11 for z/OS
Query transformation
 Common stage 2 predicates transformed to indexable
– OR and IN on different columns
• Rewritten to ORs for multi-index access
• WHERE C1 = ? OR C2 IN (1,2)
– becomes WHERE C1 = ? OR C2 = 1 OR C2 = 2
 Always-true (e.g., 1=1) and always-false (e.g., 1=2) predicates
– Some tools and ISV applications use them to turn predicates “on” and “off”
– In DB2 10 they are evaluated for each row
– DB2 11 prunes them to avoid this redundant checking
• DB2 10 already prunes some always false predicates (“=“, or “IN” with Ors)
– Example: WHERE C1 = ? OR ‘A’ = ‘B’
• DB2 11 extends always-false pruning to predicates appearing under a “parent AND”
– Example: WHERE (1=1 AND T1.C1 = T2.C1)
OR (1=2 AND T1.C2 = T2.C2)
– OR 0=1 is not pruned (a recognized DB2 technique – used to enforce row
permissions)
41
© 2014
IBM
Corporation
© 2015
IBM
Corporation
Migration Planning Workshop
DB2 11 for z/OS
Query transformation
 More correlated-to-non-correlated subquery transformation
– Example:
42
© 2014
IBM
Corporation
© 2015
IBM
Corporation
Migration Planning Workshop
DB2 11 for z/OS
Predicate pushdown
 DB2 11 supports pushdown into materialized views/TEs of:
– Non-boolean term (OR) predicate
SELECT EMPNO, SALARY, DEPTCOUNT
FROM
EMP A ,
(SELECT WORKDEPT, COUNT(*)
FROM
EMP
GROUP BY WORKDEPT) AS B(WORKDEPT, DEPTCOUNT)
WHERE A.WORKDEPT = B.WORKDEPT
AND (B.WORKDEPT LIKE 'C%' OR B.WORKDEPT LIKE 'A%‘);
– Stage 2 predicates (expressions)
SELECT EMPNO, SALARY, DEPTCOUNT
FROM
EMP A ,
(SELECT WORKDEPT, COUNT(*)
FROM
EMP
GROUP BY WORKDEPT)
AS B(WORKDEPT, DEPTCOUNT)
WHERE A.WORKDEPT = B.WORKDEPT
AND UPPER(B.WORKDEPT) = 'C01'
43
© 2014
IBM
Corporation
© 2015
IBM
Corporation
Migration Planning Workshop
DB2 11 for z/OS
Predicate pushdown (continued)
 DB2 11 supports pushdown into materialized views/TEs of:
– Predicate in the ON clause of an outer join
SELECT EMPNO, SALARY, DEPTCOUNT
FROM
EMP A
LEFT OUTER JOIN
(SELECT WORKDEPT, COUNT(*)
FROM
EMP
GROUP BY WORKDEPT)
AS B(WORKDEPT, DEPTCOUNT)
ON A.WORKDEPT = B.WORKDEPT
AND B.WORKDEPT = 'C01';
44
© 2014
IBM
Corporation
© 2015
IBM
Corporation
Migration Planning Workshop
DB2 11 for z/OS
Runtime optimizations
 CASE expression is indexable
– For a local predicate
– For a join predicate
 Non-column expressions processed once
– Before V11 these were assessed for each row
– Examples:
• :hv + 5
• CURRENT TIMESTAMP – 1 MONTH
45
© 2014
IBM
Corporation
© 2015
IBM
Corporation
Migration Planning Workshop
DB2 11 for z/OS
Runtime optimizations
 Index skipping / early out
– Duplicate removal
– Benefits GROUP BY and DISTINCT functions
– Upward probe to non-leaf page to find the next unique value
 DECFLOAT
– EC12 improvements
• CPU reductions, primarily for INSERT and FETCH
 RID Pool
– RID spill-over to work file extended to all scenarios when RID limit is hit
(e.g., queries involving column functions)
– Hybrid join limited to 80% of the RID pool
46
© 2014
IBM
Corporation
© 2015
IBM
Corporation
DB2 11 for z/OS
Migration Planning Workshop
Data-partitioned secondary indexes (DPSIs)
 Join probing
– Before DB2 11
YEAR
C1
2009
1
2010
2011
2
3
• All partitions accessed for each composite row
– DB2 11 can eliminate parts when
2009 2010 2011 2012 2013
• Join predicate matches inner table partitioning key
– Must be equal (“=“) with same data type / length
47
© 2014
IBM
Corporation
© 2015
IBM
Corporation
Migration Planning Workshop
DB2 11 for z/OS
Temporal update and DB2-managed data archiving
 Temporal special registers
 Temporal view enhancements
 DB2-managed data archiving
48
© 2014
IBM
Corporation
© 2015
IBM
Corporation
Migration Planning Workshop
DB2 11 for z/OS
Temporal special registers
 The need: what if you want a program (or just a SQL statement) to
have an other-than-current view of temporal data, but you don’t
want to change the program’s code?
 Solution: two new special registers delivered with DB2 11
– CURRENT TEMPORAL SYSTEM_TIME
– CURRENT TEMPORAL BUSINESS_TIME
 When set to a non-null value, has the effect of adding the following
to a SELECT statement that targets a temporal-enabled table (in this
case, use of system time is assumed):
– FOR SYSTEM_TIME AS OF CURRENT TEMPORAL SYSTEM_TIME
 Example of setting special register’s value:
SET CURRENT TEMPORAL SYSTEM_TIME = CURRENT TIMESTAMP – 1 YEAR;
49
© 2014
IBM
Corporation
© 2015
IBM
Corporation
Migration Planning Workshop
DB2 11 for z/OS
More on temporal special registers
 A special register non-null value, once set, remains in effect for
that particular session (thread) until it’s changed (setting to null
has the effect of “turning the special register off”)
– But if set within a routine, the new value is not passed back to the invoking
application
 SYSTIMESENSITIVE, BUSTIMESENSITIVE bind options
determine whether or not SQL statements (static or dynamic)
issued through a package will be affected by special registers
– Default value is YES
 If CURRENT TEMPORAL SYSTEM_TIME is set to non-null value
for a thread, data modification statements targeting system timeenabled tables are not allowed
50
© 2014
IBM
Corporation
© 2015
IBM
Corporation
Migration Planning Workshop
DB2 11 for z/OS
A temporal data enhancement for views
 With DB2 11, you can use temporal predicates when referring to a view
defined on a temporal table
– But you can’t use a temporal predicates in defining a view
SQLCODE -4736
Base table
51
View
© 2014 IBM Corporation
Migration Planning Workshop
DB2 11 for z/OS
DB2-managed data archiving
 The need: get old and “cold” data out of a table (for better SQL and
utility performance), but retain deleted rows in an archive table and
transparently enable retrieval of archived rows
DB2 11 will do this for you
 What a DBA does (suppose that table TAB_A is to be archive-enabled):
– Create an archive table that looks just like TAB_A (same number of columns,
same order of columns, same column names and definitions)
– Tell DB2 to associate the archive table with TAB_A (assume that you named the
archive table TAB_A_ARCHIVE):
– ALTER TABLE TAB_A ENABLE ARCHIVE USE TAB_A_ARCHIVE;
52
© 2014
IBM
Corporation
© 2015
IBM
Corporation
Migration Planning Workshop
DB2 11 for z/OS
More on DB2-managed data archiving
 Temporal and archive tables are mutually exclusive
 New built-in global variables affect interaction with archive-enabled
tables (default value for both is ‘N’):
– SYSIBMADM.GET_ARCHIVE – if ‘Y’ then SELECT targeting archive-enabled
table will automatically include UNION ALL with archive table
– SYSIBMADM.MOVE_TO_ARCHIVE – if ‘Y’ or ‘E’ then rows deleted from
archive-enabled table will be inserted into archive table (if ‘Y’ then
INSERT/UPDATE/MERGE disabled for base table)
 ARCHIVESENSITIVE bind option determines whether statements
(static or dynamic) will be affected by value of
SYSIBMADM.GET_ARCHIVE global variable (default is YES)
– ARCHIVE SENSITIVE option on create of native SQL procedure or UDF does
the same thing
53
© 2014
IBM
Corporation
© 2015
IBM
Corporation
Migration Planning Workshop
DB2 11 for z/OS
XML
 XQUERY support
 Cross loader support
 XML query rewrite
54
© 2014 IBM Corporation
Migration Planning Workshop
DB2 11 for z/OS
XQuery support for XML data
 Before: XPath expressions used to navigate XML
documents and to address parts of XML documents
– A subset XQuery, which is a richer language for accessing XML documents
– XPath limitations often necessitated using a mixture of XPath and SQL,
and that could make query coding more difficult
 DB2 11 includes XQuery support, providing a richer set of
XML expressions that can be used with the built-in
functions XMLQUERY, XMLEXISTS, and XMLTABLE
– Queries can be expressed purely using XQuery, versus a mixture of XPath
and SQL, and that can boost programmer productivity
 XQuery support was retrofitted to DB2 10 via APARS
PM47617 and PM47618
© 2014
IBM
Corporation
© 2015
IBM
Corporation
Migration Planning Workshop
DB2 11 for z/OS
Cross-loader support
 Tables with XML columns can now be the target of the
cross-loader utility
56
© 2014
IBM
Corporation
© 2015
IBM
Corporation
Migration Planning Workshop
DB2 11 for z/OS
XML query rewrite
Transforming the XML query into a better performing query
Original Query
Original Query
Price > 100 predicate is
pushed down into the “for”
clause for better performance
Simple query is converted to
XPath expression for better
performance
57
© 2014
IBM
Corporation
© 2015
IBM
Corporation
Migration Planning Workshop
DB2 11 for z/OS
Index Enhancements
 Suppress-null indexes
 Auto clean-up of pseudo-deleted index entries
58
© 2014
IBM
Corporation
© 2015
IBM
Corporation
Migration Planning Workshop
DB2 11 for z/OS
Suppress-null indexes
 Support creation of indexes where the default NULL values are
excluded from the index
– Reduced index size, improved insert/update/delete performance, 3rd party
DBMS compatibility
 New CREATE INDEX keywords
– INCLUDE NULL KEYS (default)
• DB2 will create an index entry even if every key column contains the NULL value
– EXCLUDE NULL KEYS
• DB2 will not create an index entry when every key column contains the NULL
value. If any key column is not null the index entry will be indexed
 Conditions:
– All columns of EXCLUDE NULL KEYS must be NULLable
– Not supported if the index is defined UNIQUE, BTWO, XML, IOE,
INCLUDE
59
© 2014
IBM
Corporation
© 2015
IBM
Corporation
Migration Planning Workshop
DB2 11 for z/OS
Auto cleanup of pseudo-deleted index entries
 DB2 11 automatically cleans up pseudo deleted entries
– zIIP eligible and runs in the background
– Designed to have minimal or no disruption to applications
– New ZParm INDEX_CLEANUP_THREADS (0-128) to control number of
concurrent cleanup tasks
–
–
–
–
Default is 10
0 disables index cleanup
Value can vary between members of a data sharing group
Online changeable
– New SYSIBM.SYSINDEXCLEANUP Catalog table to control auto cleanup
at index level
• Day of week/month, start/end time.
• By default cleanup is enabled for all indexes
– IFCID 377 written when index is cleaned up
 -DIS THREAD TYPE(SYSTEM) to see threads
– Correlation id in the output shows: 014.IDAEMKxx
– xx indicates thread number (01, 02…)
60
© 2014
IBM
Corporation
© 2015
IBM
Corporation
DB2 11 for z/OS
Migration Planning Workshop
Pseudo-deleted index cleanup
 Cleanup done under system tasks, run as enclave SRBs and zIIP eligible
• Parent thread (one per DB2 member) reads through RTS to find candidates
– Runs every 5 minutes
• Eligible indexes sorted based on number of pseudo deleted rids to delete highest first
SYSIBM.SYSINDEXSPACESTATS
NAME
…
IX1
n
100 X
5000
IX2
n
1000 X
20000
IX3
n
500 X
100000
IX4
n
2000 X
75000
IX3
Child cleanup
thread IX3
IX4
NPAGES
…
REORGPSEUDODELETES
– Child cleanup thread only started
if Index already open for INSERT,
UPDATE or DELETE
61
SELECT FROM… ORDER BY
Child cleanup
thread IX4
Parent
thread
Index
In
Memory
IX2
IX1
© 2014
IBM
Corporation
© 2015
IBM
Corporation
DB2 11 for z/OS
Migration Planning Workshop
SYSIBM.SYSINDEXCLEANUP example
 All index spaces in DB_1234 are enabled for cleanup on Sundays from 4:30
until noon, except
– Index space IX_9876 is always disabled for cleanup
SYSIBM.SYSINDEXCLEANUP
DBNAME
INDEXSPACE
ENABLE_
DISABLE
MONTH MONTH
_WEEK
DAY
START
_TIME
END
_TIME
DB_1234
NULL
E
W
NULL
7
043000
120000
DB_1234
IX_9876
D
NULL
NULL
NULL
NULL
NULL
 DB2 checks SYSIBM.SYSINDEXCLEANUP table at 10 min intervals
– Enforcement of new row may be delayed up to 10 min
 RECOMMENDATION: Use rows in SYSIBM.SYSINDEXCLEANUP only to
define exceptions to default index cleanup behavior
– Define time windows at system or database levels, rather than specific indexes
when possible
– Remove unneeded or conflicting rows
62
© 2014
IBM
Corporation
© 2015
IBM
Corporation
Migration Planning Workshop
DB2 11 for z/OS
Security enhancements
 Improved RACF integration
 RACF synchronization of authorization caches
 Plan program authorization
63
© 2014
IBM
Corporation
© 2015
IBM
Corporation
Migration Planning Workshop
DB2 11 for z/OS
Packages and RACF
 Before DB2 11 the Primary AUTHID was used for
authorization checking of
– BIND, REBIND, Autobind, PREPARE
 DB2 11 ZParm AUTHEXIT_CHECK (PRIMARY | DB2)
available in CM
– Not online changeable
– Defaults to PRIMARY for DB2 10 behavior
• The Primary Authorization ID is used
– “DB2” will honor Package OWNER and DYNAMICRULES
• Package OWNER used for authorization checking
– Can be AUTHID, RACF Group, or Role
– Which must be permitted access to RACF resources
– A RACF Group requires access to the resource associated with the connection in the RACF DSNR
class
• DYNAMICRULES
– Defines the Authorization ID to be used for Dynamic SQL authorization
> Could be the INVOKER, BINDER, or DEFINER
– DB2 11 can now honor the BINDER or DEFINER authorization checking
64
© 2014
IBM
Corporation
© 2015
IBM
Corporation
DB2 11 for z/OS
Migration Planning Workshop
RACF synchronization of security caches ...
 z/OS Event Notification Facility (ENF) Signals notify DB2 of changes in RACF
– 62: Options refreshed
– 71: User permission changes
– 79: User access to a resource
RACF
User & Group Definitions
DSNX@XAC
ENF
Refreshes Caches
Invalidates Packages
DB2
Package
Authorization
Cache
65
Routine
Authorization
Cache
Dynamic
Statement
Cache
DDF
User
Cache
© 2014
IBM
Corporation
© 2015
IBM
Corporation
Migration Planning Workshop
DB2 11 for z/OS
Plan program authorization
 REBIND and Run with no DSNPROGAUTH entry
EIUPROG
EIUPLAN
Y
 INSERT DSNPROGAUTH entry
– Note defaults to ENABLED=N
 Program EIUPROG now executes the Plan
 Program DSN8MCG still not allowed to use EIUPLAN
66
© 2014
IBM
Corporation
© 2015
IBM
Corporation
Migration Planning Workshop
DB2 11 for z/OS
Advanced design options
 Online schema evolution
 PIT recovery with pending ALTER
 DGTT improvements
 Reserved space for update
 Compression improvements
67
© 2014 IBM Corporation
Migration Planning Workshop
DB2 11 for z/OS
Online schema evolution …
 More pending ALTERs:

DROP column
sc1.table1 in DB1.TS1
Col_1
Col_2
Col_3
Data_1
Data_2
Data_3
Data_1
Data_2
Data_3
68
Reorg SHRLEVEL
CHANGE/REFERENCE required to
materialize DDL
Multiple DROP COLUMNs can be
executed before materializing Reorg
© 2014 IBM Corporation
Migration Planning Workshop
DB2 11 for z/OS
Online schema evolution...
 Drop column restrictions:
– A column cannot be dropped if
• The containing table space is NOT a universal table space
• The table is a created global temporary table, a system-period temporal table, a
history table, MQT or table referenced by MQT plus others....
• Tables with EDITPROC or VALIDPROC
• There are row permissions or column masks dependent on the table
• There are triggers defined on the table
• The column is an XML column, is part of the table partitioning key, RI
• Hash Key column
• DOCID / ROWID column
• More restrictions apply, see SQL Reference ALTER TABLE
– No immediate alters (e.g. Can‘t change data type, add column, add/rotate parts) combined
with pending ALTER
• Option remains to DROP PENDING CHANGES, then do immediate alters
69
© 2014
IBM
Corporation
© 2015
IBM
Corporation
DB2 11 for z/OS
Migration Planning Workshop
Online Schema Evolution…
 ALTER partitioning limit key
Part #
Limit
key
Access
Part #
Limit
key
Access
1
100
R/W
1
100
R/W
2
200
AREOR
2
250
R/W
3
300
AREOR
3
300
R/W
70
© 2014 IBM Corporation
Migration Planning Workshop
DB2 11 for z/OS
Online schema evolution…
 Online ALTER of partition limit keys
– DB2 11: alter limit key is treated as a pending alter (NFM)
– The affected partitions are set to AREOR
– Online REORG is executed to materialize the pending
changes
– Supported table spaces types are:
• UTS – partitioned by range (PBR)
• Classic partitioned table spaces (table-controlled
partitioning)
– The new limit keys are reflected in SYSTABLEPART in the
SWITCH phase of online REORG
71
© 2014
IBM
Corporation
© 2015
IBM
Corporation
Migration Planning Workshop
DB2 11 for z/OS
Online schema evolution
 ALTER TABLESPACE ... DROP PENDING CHANGES
– Pre DB2 11: AREOR setting is not removed
– DB2 11: AREOR setting is removed
• Exception: AREOR is still kept if transition to hash organization is
in progress
72
© 2014
IBM
Corporation
© 2015
IBM
Corporation
DB2 11 for z/OS
Migration Planning Workshop
PIT Recovery with Pending ALTER …
 Pre DB2 11: Restrict RECOVER across materializing REORGs

PIT t2
3
RECOVER to PIT issued
Pending changes
1 Pending ALTER
73
2
Materializing REORG
© 2014
IBM
Corporation
© 2015
IBM
Corporation
DB2 11 for z/OS
Migration Planning Workshop
PIT Recovery with Pending ALTER …
 DB2 11: Allow RECOVER across materializing REORGs

PIT t0
logs

PIT t1
3
logs
RECOVER to PIT issued
PIT t2
Pending changes
1 ALTER..pending
74
2
Materializing REORG
© 2014
IBM
Corporation
© 2015
IBM
Corporation
Migration Planning Workshop
DB2 11 for z/OS
PIT Recovery with Pending ALTER …
 DB2 11 PITR with pending ALTER
– Support in NFM only
– Recovery of data only, not schema definition
– A new record is inserted into SYSPENDINGDDL table after RECOVER
 Table spaces supported:
– LOB
– XML
– Partition by Range (PBR) Universal Table Space (no PBG support)
 At end of RECOVER to PIT prior to the materializing REORG:
– Table space is in restrictive status: REORG-pending (REORP)
– REORG to finalize the PIT recovery process is MANDATORY
– No other PITR during this period prior to subsequent REORG
75
© 2014
IBM
Corporation
© 2015
IBM
Corporation
Migration Planning Workshop
DB2 11 for z/OS
PIT Recovery with Pending ALTER…
 The subsequent REORG is required
– Must on be on ENTIRE table space
– SHRLEVEL NONE is not supported
– SHRLEVEL CHANGE is overridden by SHRLEVEL REFERENCE
 Before the subsequent REORG to materialize the
schema
– No CREATE/ALTER/RENAME/DROP TABLE on the TS or AUX objects
– Most other utility jobs fail: DSNU933I (REORG required)
 REPAIR DBD and REPORT RECOVERY allowed
 Where there were pending changes on LOB table space:
– First REORG the LOB table space, then REORG the base table space
76
© 2014
IBM
Corporation
© 2015
IBM
Corporation
DB2 11 for z/OS
Migration Planning Workshop
PIT Recovery with Pending ALTER
Supported ALTERs:
Attribute
LOB Table space
SEGSIZE
N/A
DSSIZE


Table space page
size
MEMBER
CLUSTER
N/A
Partitioning Limit
Key
N/A
PBR UTS





XML Table space


N/A
N/A
* Not all pending ALTERs supported
77
© 2014 IBM Corporation
Migration Planning Workshop
DB2 11 for z/OS
DGTT Improvements …
 DB2 11: support for NOT LOGGED syntax for DGTTs
– Reduce logging overhead/volumes
– Less CPU from log processing
– Faster rollback/error processing after large insert into
DGTTs
 DB2 11: hold DGTT-based statements across COMMIT
– Reduce incremental binds and full prepares
– Extended to non-cursor SQL statements
– RELEASE (DEALLOCATE) required
78
© 2014
IBM
Corporation
© 2015
IBM
Corporation
DB2 11 for z/OS
Migration Planning Workshop
Reserved space for update ...
 DB2 11 and prior: indirect references (overflow records)
–
Created during UPDATE against variable length rows or compressed rows
Update row-2
with larger data
Data Page 1
Data Page n
row-1
Pointer
to row-2
row-2
row-100
row-101
row-3
row-2
 Impact caused by indirect references
–
Additional getpages, potentially additional I/Os to the overflow pages
– Lower clustering
– REORG TABLESPACE necessary to remove indirect references
79
© 2014
IBM
Corporation
© 2015
IBM
Corporation
Migration Planning Workshop
DB2 11 for z/OS
Reserved Space for Update
 DB2 11:
– INSERT will reserve space for updaters
– New DDL keyword for CREATE TABLESPACE on PCTFREE clause:
• FOR UPDATE n
– Where n is % of free space to leave in each data page by INSERT,
LOAD or REORG
– New ZParm: PCTFREE_UPD
• System default for FOR UPDATE value when it is not specified
• If not specified, same behavior as DB2 10
80
© 2014
IBM
Corporation
© 2015
IBM
Corporation
Migration Planning Workshop
DB2 11 for z/OS
Compression improvements
 Decompression can be an expensive operation
– Executed during evaluating and retrieving records from compressed table space
– Higher impact with simple queries scanning large number of row
 DB2 11 Optimization in decompression process
– Software based decompression on top of today’s H/W compression
– New decompression routine provides:
– Column level decompression
– Special optimization to speed up the decompression
– Efficient processor cache utilization when handling dictionary
– No change on compression ratio
– Only for data compression, not for index page
– Compatible with existing compression, no user action necessary
 Another enhancement: when compression dictionary is regenerated, the old
dictionary is copied to the log for log-reading data replication tools
81
© 2014
IBM
Corporation
© 2015
IBM
Corporation
Migration Planning Workshop
DB2 11 for z/OS
Thanks for your time
Robert Catterall
[email protected]
82
© 2014
IBM
Corporation
© 2015
IBM
Corporation