ibm db2 for z/os trends and directions
of 36
/36
© 2011 IBM Corporation IBM DB2 for z/OS Trends and Directions Jeff Josten Distinguished Engineer DB2 for z/OS Development IBM Silicon Valley Lab
Embed Size (px)
DESCRIPTION
IBM DB2 for z/OS Trends and Directions. Jeff Josten Distinguished Engineer DB2 for z/OS Development IBM Silicon Valley Lab. Please note. IBM ’ s statements regarding its plans, directions, and intent are subject to change or withdrawal without notice at IBM ’ s sole discretion. - PowerPoint PPT PresentationTRANSCRIPT
2007 zSummit Initial ReviewJeff Josten
Distinguished Engineer
Please note
IBM’s statements regarding its plans, directions, and intent are subject to change or withdrawal without notice at IBM’s sole discretion.
Information regarding potential future products is intended to outline our general product direction and it should not be relied on in making a purchasing decision.
The information mentioned regarding potential future products is not a commitment, promise, or legal obligation to deliver any material, code or functionality. Information about potential future products may not be incorporated into any contract. The development, release, and timing of any future features or functionality described for our products remains at our sole discretion.
Performance is based on measurements and projections using standard IBM benchmarks in a controlled environment. The actual throughput or performance that any user will experience will vary depending upon many factors, including considerations such as the amount of multiprogramming in the user’s job stream, the I/O configuration, the storage configuration, and the workload processed. Therefore, no assurance can be given that an individual user will achieve results similar to those stated here.
IBM IOD 2011
DB2 and the new zEnterprise EC12 machine
DB2 11 (next release)
Fastest uptake
Adoption Driven by:
Virtual Storage Constraint relief for more threads
Security, RAS improvements
As of March 2012
Over two times as many customers –and– over three times as many licenses as what we had with DB2 V8 or DB2 9 at the same point in time. With about 25% of the customers migrating from DB2 V8.
From a quick survey with a set of 500 customers on a DB2 10 for z/OS migration call, a number of the respondents said they are targeting to be in production by YE2011
Customers have been enthusiastic about DB2 10 for z/OS performance and scalability. Some customers are able to simplify their structure and many are seeing better productivity. The security and temporal function are seeing strong early acceptance. A safer infrastructure with better audit function can help customers avoid the need for a new security structure. Customers are signing up for the V8 to DB2 10 migration to save time and to get these improvements faster.
*
© 2012 IBM Corporation
Creating the Hybrid Data Server - Netezza and Z Combine DB2 for z/OS with Netezza to provide an industry exclusive
Transaction Processing Systems (OLTP)
and recoverability
Netezza Accelerator
Data Mart
Data Mart
Data Mart
Best in Consolidation
Best in OLTP and Transactional Analytics
Industry recognized leader in mission critical transaction systems
Together:
Destroying the myth that transactional and decision support workloads have to be on separate platforms
Transactional Analytics
What’s New?
High Performance Storage Saver
Store a DB2 table or partition of data solely on the Accelerator. Removes the requirement for the data to be replicated on both DB2 and the Accelerator
Incremental Update
Enables tables within the Accelerator to be continually updated throughout the day.
zEnterprise EC12 Support
Version 3 will support the zEnterprise EC12, z196 and z114 System z platforms
Query Prioritization
Brings System z workload management down to the individual query being routed to the Accelerator
High Capacity
Support has been extended to include the entire Netezza 1000 line (1.28 PB)
UNLOAD Lite
*
*
*
*
DB2 Analytics Accelerator for DB2 z/OS is the next generation accelerator for System z
Tightly integrated with DB2 for z/OS
Transparent to DB2 applications
Capitalizes on the availability of System z
Minimizes data movement
Leverages Netezza Technology
FPGA for hardware query acceleration (These FPGAs are used for data decompression, data filtering and early SQL projections and restriction)
No need for indexes. MQTs, or query plans
Disk mirroring and blade failover
Box can be remissioned when new technology is introduced
Offloads long running queries from System z
Created for mixed workload of operational transaction systems, data warehouse, operational data stores, and consolidated data marts
The IBM DB2 Analytics Accelerator is:
An integration of IBM hardware, software, and storage and advanced technologies focused on business analytics, that combine to give IBM clients the industry's most high performance analytic capability, to extract business insight from information assets, providing the right answers to the right questions at the right time. .
It removes the costs of traditional performance tuning such as indices, materialized query tables (MQTs) and query plan tuning that required extensive time and expensive resources.
*
Customer interaction
Multiple infrastructures required
Copy
ETL
/ OLTP
Using web service calls to score new transactional data as it is coming into the business is currently very network intensive and can be costly when trying to ensure SLAs.
An alternative to using a web services call is to score the transactional data after it is moved into the data warehouse/data mart/ODS. Because we are now reliant on the ETL schedule, the score is only as current as the last refresh and scoring of the data. This makes it difficult if not impossible to provide the highest level of business insight at the point of customer interaction
Score is static and does not change based on new customer interactions
Score is only as current as the ETL/batch copy/model schedule
High Networking Traffic
As volume increases so do the costs
SLA’s = Real-time Scoring needs to provide the same levels of service as the OLTP application, as our performance, availability, scalability requirements grow as do our costs & complexity, with the needs for more hardware and software to support demand.
Applications may be built off the System z platform , increasing the cost & complexity of scoring
© 2012 IBM Corporation
Predictive
SPSS Modeler for Linux on System z
Version: Modeler v15 Data mining tool used for generating hypotheses and scoring Text analysis for unstructured data to model consumer behavior In-Transaction Scoring with DB2 z/OS: Embeds the Scoring Algorithm Directly within the Transactional Application
IBM SPSS Modeler 14
IBM SPSS Statistics 19
Ad-hoc analysis,
IBM SPSS Decision Management
A decision process framework that uses predictive analytics to optimize the outcomes of the thousands of operational decisions
IBM SPSS Collaboration and Deployment Services
A platform for the management and deployment of analytical assets.
It supports predictive models as they are prepared, validated and integrated with operational systems.
© 2012 IBM Corporation
Customer Interaction
Reduced Networking
in-database scoring on the same platform
Consolidates Resources
Scoring Algorithm
Provide the same qualities of service as the business system
Improve the accuracy of our decision because we are scoring the most current data
Can deliver both in-transaction and in-database scoring on the same platform for greater economies of scale & expertise
Potential to reduce ETL requirements
Co-locate OLTP, DW and Modeler, reducing the copy/movement
Reducing the network band width required for scoring because all functionality is internal to System z
Improves the speed and accuracy of building & repeating models
With System z’s proven performance we can define new patterns faster & more frequently
Capacity to scale to build models off larger data sets
Consolidates resources
Reduce floor space, heating, cooling required
We can further accelerate IBM DB2® queries with in-memory processing, massively parallel architecture, row and columnar store technologies, highly compressed data and compressed data operations
© 2012 IBM Corporation
Meets most demanding workload
Meets stringent SLA requirement
Provides best value
Most accurate score is calculated in real time
Can you really scale to support the volume of transactions a OLTP application processes without impacting performance?
Workload runs for 14 minutes
At 8 CP, windows server to small to driver sufficient workload
HS = Historical Score RT = Real Time Score
Workload is single select that gathers input data for the scoring model, executes that model and returns inputs and scoring results. Typical application will include more logic than this.
SELECT
T0.C0 AS C0, T0.C1 AS C1, T0.C2 AS C2, T0.C3 AS C3,
T0.C4 AS C4, T0.C5 AS C5,
UNPACK(
HUMSPSS.SCORE_COMPONENT('H','ce815fe5b47e83cc548b65dcf5785eb5',
FROM (
SELECT
T0.SALARY AS C3,T0."TRANS_SPEND_1" AS C4,
T0."TRANS_POINTS_1" AS C5
FROM "data_1_by_20" T0)
Dramatically different than prior releases
We keep innovations coming
Existing customers can modernize and consolidate their BA environments and extend their current QMF investment at low cost
Workstation and WebSphere environments let you create and deploy visually rich reports, interactive dashboards, and much more
TCO is based on an enterprise-wide model. The greater the number of users and databases accessed, the lower the cost
IBM DB2 Analytics Accelerator creates significant performance improvements and savings for QMF
Faster responses to queries
Act faster on results, dig deeper, generate personalized reports and dashboards
Make more timely, informed decisions
Provides a lower cost option for massive batch operations, one of QMF’s known strengths
What’s new?
New QMF redbook: Complete Analytics with IBM DB2 Query Management Facility http://www.redbooks.ibm.com/redbooks/pdfs/sg248012.pdf
Join our next beta program!
Ask your IBM representative to visit the following web page for information:
*
75-90% less usage in DB2 10 compared to DB2 9
Some of working storage (stack, xproc storage) stays below 2GB
Larger number of threads
Improve CPU with storage
DB2 10
Thread / Stack/ working
Laboratory measurements and early customer experience have shown substantial savings in the primary constrained address space, DBM1. Most measurements have shown 75% to 90% savings for the virtual storage in that address space below the 2 GB bar. Some EDMPOOL and some working storage remains below the bar.
This storage relief allows many more threads or concurrent users in a DB2 subsystem, allowing new possibilities for optimization.
Some customers will be able to consolidate data sharing members, saving on memory, CPU and administration time.
Other customers will be able to use the storage to improve service or to reduce CPU time more. Some common examples are expected to be use of RELEASE(DEALLOCATE) and larger amounts of dynamic statement cache.
© 2012 IBM Corporation
DB2 10 Performance
Most customers can see a 5% - 10% out-of-the-box CPU reduction (transactions and batch) after rebind
Some workloads and customer situations can see a CPU reduction of up to 20%
Synergistic operation with latest System z hardware
Sample: Preliminary Measurements of IBM Relational Warehouse Workload (IRWW) with Data Sharing
Base: DB2 9 NFM REBIND with PLANMGMT EXTENDED
DB2 9 NFM DB2 10 CM without REBIND showed 1.3% CPU reduction
DB2 10 CM REBIND with same access path showed 4.8% CPU reduction
DB2 10 NFM brought 5.1% CPU reduction
DB2 10 CM or NFM with RELEASE DEALLOCATE 12.6% CPU reduction from DB2 9
CPU Percent reduced from DB2 9
© 2012 IBM Corporation
Avoids processing to go inactive and then back to active
Enabling High Performance DBAT, e.g. for WebSphere
BIND client packages into different collection coll2 with RELEASE(DEALLOCATE)
BIND other frequently executed packages with RELEASE(DEALLOCATE)
Set -MODIFY DDF PKGREL(BNDOPT) to enable
In WAS datasource property definition point to new collection
E.g. jdbcCollection=coll2
© 2012 IBM Corporation
Easier scaling, simpler memory management
Reduce contention, more online processing
Reduced need for REORG
Index list prefetch enhancements
Statement level monitoring
DDF thread management enhancements
REORG
Customers appreciate the improved performance and scalability. Virtual storage management almost disappears. DB2 has a strong focus on making DB2 easier to use by automating tasks and eliminating tasks where possible. Avoiding the manual invocations can also help avoid problems for running the function too often or not often enough. Where the task cannot be eliminated, the frequency and monitoring can be reduced, such as the need to reorganize. The improvements for virtual storage and for availability also help administrator productivity.
Some of the improvements come within DB2 for z/OS. Improvements in SQL and XML improve productivity for those who develop new applications and for those who are porting from other platforms. Some of the improvements remove complexity from application tasks.
Some of the improvements come with Data Studio for application programming and administration – stronger cross-platform graphical interfaces, better integration with Java, improvements in the ability to develop and debug.
Allowing tailored names for DSNHDECP will permit many subsystems to share the SDSNEXIT data set.
© 2012 IBM Corporation
Problem: For distributed workloads, low priority or poorly behaving client applications may monopolize DB2 resources and prevent high-priority applications from executing.
Solution: Increased granularity of monitoring for system level activities
Number of connections
Number of threads
Idle thread timeout
Profiles specified in SYSIBM.DSN_PROFILE_TABLE
DB2 10 for z/OS adds support for filtering and threshold monitoring of system related activities via new keywords
Number of threads
Number of connections
Idle thread timeout
New scope filters
Product-specific identifier
© 2012 IBM Corporation
Page size
Hashed
DSSIZE
Improve availability and productivity
Increase maximum size substantially
DDL concurrency also improved from removal of
DBD01 hash anchor locks
Many more table spaces, partition by growth
Row level locking, reordered row format
CLOB and BLOB columns for long strings
Inline for performance
Online reorganization and check
More automatic: DB2-managed SMS-controlled
Allow query of SYSLGRNX
*
The DB2 catalog and directory are restructured in DB2 10 ENFM to improve productivity and availability. You’ll see these improvements in NFM. The current size limits are increased substantially and contention among process like BIND, dynamic SQL, data definition and utilities is reduced. With more table spaces and more structures, more work is required for some process, such as BIND.
The primary techniques are changes in the DB2 catalog to remove links and the special structures for the catalog. These table spaces change from many tables to one table per table space in a partition by growth table space defined as DSSIZE 64 GB and MAXPART 1. Row level locking is used in place of page level locking. The new catalog tables use a partition by growth universal table space structure. Each table space holds a single table, so many more table spaces are needed. Rather than repeating columns with parts of long strings, the catalog will use CLOB and BLOB columns to store the data, expanding maximum sizes. Inline LOBs are used for the performance improvements. The new structure allows more standard processes, so that all catalog tables can be reorganized and checked online.
The DB2 catalog changes from using manual definition and extension to DB2 managed data sets under SMS control. The changes improve productivity and availability, but take time to set up.
© 2012 IBM Corporation
Protect sensitive data from privileged users and improve productivity
Separation of duties
More granular control
New SECADM authority for security administration
System level DB admin with/without access control and with/without data access privileges
Usability: DBADM for all DB
New Explain privilege
Audit admin authority usage
Support distributed identities introduced in z/OS 1.11
Support client certificate authentication in z/OS 1.10
Support password phrases in z/OS 1.10
Connection level security enforcement using strong authentication
Row and column access control
Allow masking of value
Monitor
Tasks
Customers are being pressed for a wide range of improved security and compliance. Data retention is a growing need. Protecting sensitive data from the privileged users and administrators is required. Separation of authority for security, access, and some common tasks, like EXPLAIN will help. Auditing for privileged users can also make compliance simpler.
Access control is refined in several ways with better granularity for the administrative privileges and with finer grained access control at the row and column level, including the ability to mask access to some fields. Auditing policies: new IFCID for auditing admin authority usage. Dynamic table auditing (all access, stmt id level).
Granular admin authorities, separation of duties: sysadm: system level dbadm with/without accessctrl, with/without dataaccess. System level SECADM (separation of dutes, shift from dba to security group, zparm control to remove privileges from sysadm). System level SQLADM. App level explain privileges.
Distributed identities: supported in v9 via EIM, but not much used due to complexity. V10 allows for use of new RACF feature to map distributed identities passed in from app server to a RACF id. Ap server passes authenticated distributed end user identify using trusted context.
Client certificate: Eliminates need to manage RACF ids on DB2 clients. Used to be supported only via trusted context, but now also outside trusted context.
Connection level security enforcement: Zparm TCPLVER value SERVER_ENCRYPT
© 2012 IBM Corporation
Temporal Based Analysis
System-maintained temporal tables
DB2 generated history
AS OF query
User-maintained temporal tables
Automatic business time key enforcement.
Query over any current, any prior, future point/period in business time.
New time range update/delete statements support automatic row splitting, exploited by the merge statements.
Bi-temporal, combination of the above two
© 2012 IBM Corporation
System Time Query
SELECT Dept
FROM employees
SELECT Dept
WHERE EmpID=12345
SELECT count(distinct Dept)
WHERE EmpID=12345
EmpID
Dept
System_start
System_end
12345
M15
05/31/2000
12/31/9999
EmpID
Dept
System_start
System_end
12345
J13
11/15/1995
01/31/1998
12345
M24
01/31/1998
05/31/2000
67890
K25
11/15/1995
03/31/2000
DB2 Family Row and Column Access Control
A table level authorization function fully implemented in the database that provides:
Row level access control based on customer-supplied rules
A doctor can see rows representing his patients only
A manager can see rows representing his employees only
Column level access control based on customer-supplied rules
This is also called data masking
A teller can see only the last 4 digits of the credit card number column
Defined and managed by SECADM
© 2012 IBM Corporation
Prerequisites: migrate from DB2 9 NFM or DB2 V8 NFM
z/OS V1.10 SMS-controlled DB2-managed DB2 catalog
System z z196, z10, z9, z890, z990, and above (no z800, z900)
DB2 Connect 9 FP1, 9.7 FP3a for 10 new function
Premigration check DSNTIJPA PM04968
Items deprecated in earlier versions eliminated: more for V8 mig.
Private protocol DRDA
Plans containing DBRMs packages
*
Here are a couple of thoughts about what might be required in hardware and software to run DB2 10. Much will depend upon the timing of the deliveries and market acceptance. Moving forward as quickly as possible means that some of the past must be left behind. See the list of deprecated functions from prior versions.
The above functions are still included in DB2 9, but are generally deprecated and may be removed from future versions. Note the direction indicated to the right of the arrows, as these are the functions provided to replace the existing function. If you are using any of these functions, you are advised to move to the new function.
See the Installation Guide section, “Functions that are deprecated” and the announcement material for more information on these changes.
http://www.ibm.com/common/ssi/rep_ca/8/897/ENUS206-098/ENUS206-098.PDF
Embedded xQuery support – PM47618
Needed for SPSS in-database analytics
Stored procedure monitoring improvements – PM53243
More easily identify a problematic stored procedure or a statement within that stored procedure
New zparm for OPTIMIZE FOR 1 ROW to allow sort access plans – PM56845
V10 migration issue for some customers
GBP Delete Name enhancement – PM67544
Performance enhancement for sysplexes that span longer distances
© 2012 IBM Corporation
New DEL_CFSTRUCTS_ON_RESTART ZPARM for auto-delete of CF structures on restart if no active connections exist
Useful for DR environments
ALTER MAXROWS to set AREO* rather than AREOR
PM43597 (V10)
Ability to SELECT from SYSLGRNX
PM35190 & PM42331 (V10)
PM31641 (V10)
5X increase in recommended limit on concurrent RECOVER jobs per DB2
LOB pageset support for RECOVER BACKOUT YES
PM45650 (V10)
Latest z servers can support TB main memory sizes
A single DB2 member can currently support up to 1 TB of BP space
DB2 10 has improvements for large BP scalability/performance
1MB page frame support
“In Memory” BP attribute
Avoid BP scans for data sharing TS/part/index inter-DB2 interest level changes
Internal BP hashing and latching improvements
VSCR improvements for 5-10x more active threads per DB2 member/subsys
Opportunities to tradeoff increased memory usage for reduced CPU
DB2 10 new RTS column to indicate SSD usage
New zEC12 machine introduces flash memory
Future: continue to leverage large main memory and automatic storage hierarchy management for performance advantages
© 2012 IBM Corporation
Multi core, future slowing growth in single thread performance
Higher n-ways, more parallelism bring potential latching bottlenecks, memory cache thrashing, …
S/W techniques for single threaded performance growth
Clustered systems for massive scale out and continuous availability
Specialty engines (price / performance)
New performance opportunities
Memory hierarchy design
Higher CPU frequencies, n-ways make cache utilization a critical factor
Translation lookaside buffer design, large System z page sizes
Solid state disk ( and other disk related improvements)
Performance, energy consumption, reliability benefits of HDD
Large and rapidly growing main memory sizes along with falling prices
Performance improvements through more memory use
DB2 10 enables more persistent threads with RELEASE(DEALLOCATE)
Larger buffer pools, in-memory workfiles, I/O avoidance
Multi-core processors, alone, will be insufficient to enable application-level systems price & performance improvements at historical rates. Semiconductor scaling, which has provided the foundation for system-level improvements in cost and performance, is slowing down. While densities are expected to continue to improve, power efficiency is expected to lag. Transistor performance at constant power density has been close to constant since the 90nm node. The resulting lack of processor frequency growth has forced the industry to seek performance improvements through the introduction of multiple cores on a processor, and the parallel software to exploit them. In contrast to nodes prior to 32nm and 22nm, power density at constant frequency (and constant switching factor) will increase node-to-node past 22nm. This means that if a core was migrated from 22nm to 15nm and replicated to fill a chip of similar size, the operating frequency of these cores would have to be reduced in order to use the chip in a system of similar power supply and cooling capacity.
© 2012 IBM Corporation
Hardware-based Coupling Facility for efficient
DB2 data sharing (availability and scale out)
zIIP engines to offload certain DB2 workloads
Hardware instructions for Unicode conversion
Hardware data compression & encryption
Cross-memory, memory protection keys
with multi-track data transfer and FICON Express 8
AIX / Linux / Win (SOD)
Availability - Resiliency management and fewer points of failure. Centralized workload management aligned to business priorities.
Integration and stack integration.
Scalability - Ability to meet massive demands from users and data.Unmatched scalability with the highest transaction processing capacity.
Affordability - workloads on zEnterprise have new solution price points, that looks like a Unix/Intel solution price.
And delivers also:
Flexibility - Horizontal integration with SAP on IBM blades improves application deployment choices.
Virtualization - Centralize Management of virtual servers across a heterogeneous pool
Enable thousands of virtual servers within a single integrated system.
Efficiency - Reduced infrastructure complexity through consolidation, automation and virtualization; saving on software and environmental costs, reduce labor, energy, and development costs.
© 2012 IBM Corporation
20-28% CPU reduction measured with DB2 OLTP workloads
25% reduction measured with DB2 query and utilities workloads
Less compression overhead with DB2 data (1-15%)
50% More System Capacity to help consolidation
Excellent synergy with DB2 10 scalability
New Features DB2 plans to exploit
FLASH memory and pageable 1MB frames
Enables larger DB2 Buffer Pools
2GB frame support
Enable even larger DB2 Buffer Pools
Larger frames expected to provide additional CPU savings, especially for very large memory
DB2 code backed by large frames for CPU reductions
Enhanced prefetch instruction for CPU reductions
Transactional Memory provides further possibilities for performance gains
Our official numbers for z196->zEC12 are,
1. 20 to 28% CPU reduction in OLTP workload (28% with TPC-E, 21% with SAP DP)
2. 25% CPU reduction in Query and Utility
1M pageable requires the latest z/OS 1.13, 2G support requires z/OS 2.1. 1M page sizes for code requires z/OS 2.1.
Improved efficiency for CMPSC instruction.
Decimal Floating Point Zoned Conversion Facility: allows us to push string to DECFLOAT conversions to hw.
*
Everyone wants to be like DB2 for z/OS!
DB2 and z/OS have had advanced cloud and multitenancy capabilities for years
Many DB2 for z/OS customers are already running advanced multi tenant configurations in large scale production
Deep integration between DB2, z/OS, System z hw, and other subsystems gives unparalleled support for OLTP and mixed workloads with low TCO
IDAA delivers another level of system integration and optimization for running analytics workloads alongside OLTP with DB2 for z/OS
DB2 10 and DB2 11 extend these capabilities
© 2012 IBM Corporation
Proliferation of mobile and other network-connected devices is driving increases in:
transaction workloads
data volumes
24x7 requirements
Competitive pressures continue to drive an increasing need for innovation, analytics, and data integration
DB2 for z/OS has leading edge capabilities to support these requirements and DB2 11 makes important improvements
Everyone is talking about „cloud“ and „multitenancy“ these days with regards to satisfying these requirements. DB2 for z/OS is already very well positioned, espeically on the latest z machines, with its scalability, high availability, multitenancy, and ability to run mixed workloads. DB2 11 adds further to these capabilties.
© 2012 IBM Corporation
0-5% for OLTP
Exploitation of new zEC12 hardware features
Continuous Availability Features
Online REORG improvements, less disruption
DROP COLUMN, online change of partition limit keys
Extended log record addressing capacity (1 yottabyte)
BIND/REBIND break into persistent threads
Enhanced business analytics
Temporal and SQLPL enhancements
No application changes required for DB2 upgrade
Access path stability improvements
© 2012 IBM Corporation
Extended RBA Problem Statement
DB2’s Relative Byte Address (RBA) for logging is 6 bytes
Gives 256TB of log record addressing capacity per DB2 subsystem/member
With heavy sustained logging rates, DB2 can exhaust the 6-byte RBA
DSNJ032I and DSNJ033E warning messages
Alert-level = ‘WARNING’ when RBA reaches x’F00000000000’
Alert-level = ‘CRITCIAL’ when RBA reaches x’FFFF00000000’
Manual recovery actions are needed
Data Sharing: shut down the affected member and start a new member in its place
Non Data Sharing: reset all PGLOGRBA values back to zero (extended outage)
Documented in the DB2 Administration Guide
If alert-level reaches ‘CRITICAL’ then DB2 terminates to protect data integrity and force recovery actions
Reason code 00D10251
© 2012 IBM Corporation
Extended LRSN Problem Statement
The data sharing Log Record Sequence Number (LRSN) is derived from the 8-byte time-of-day clock which hits end of range in 2042
However, some data sharing groups have a non-zero LRSN “delta” which gets added to the TOD clock
If a non-zero “delta” exists, then the LRSN will hit end of range prior to 2042
Use DSNJU004 to determine if you have a non-zero LRSN delta value
A “delta” value could be set when data sharing is enabled or re-enabled
Whenever the end-of-log RBA of the enabling member is past the TOD clock
Some non data sharing customers have enabled data sharing to circumvent RBA nearing end-of-range
This would cause a non-zero LRSN delta, so LRSN hits end of range before 2042
6-byte LRSN value has precision to only 16 microseconds
Can cause LRSN ‘spinning’ which burns extra cpu and aggravated log latch contention
V9 NFM addresses most LRSN spin situations, and V10 NFM enhances further. But some spins still exist due to the 16 usec granularity (log latch not held, page latches are)
© 2012 IBM Corporation
Expand the RBA and LRSN to 10 bytes
RBA addressing capacity of 1 yottabyte (2**80)
LRSN extended on left by 1 byte, on the right by 3 bytes
>30,000 years and 16Mx more precision
8 bytes is not sufficient to solve LRSN issues and may not give sufficient capacity for the longer term
NFM only (6 byte RBA/LRSN continues to be used in CM)
Once in NFM, DB2 continues to use 6-byte values until you take action to convert
Two conversion tasks:
Convert BSDSes to new format to enable logging with larger RBAs/LRSNs
Convert pagesets to new page format
These tasks are optional
If you don’t care about larger RBAs/LRSNs then you don’t have to convert
But performance will be better if you convert BSDSes (avoid internal conversion overhead on log write)
BSDSes can be converted without converting pagesets
Pagesets can be converted in a piecemeal fashion
Expectation is that most customers will roll the conversion over a period of days/weeks/months
© 2012 IBM Corporation
Some Planned DB2 11 RAS Improvements
BIND / DDL concurrency with persistent threads
Use of persistent threads likely will increase in V10 with vstor relief
More online schema changes
Alter partitioning limit keys
Cancel DDF Threads – new FORCE option
Open data set limit raised to 200K
Workfile space shortage warning new instrumentation and messages
REORG avoidance
Automatic cleanup of index pseudo deleted entries
Online REORG improvements – if a REORG is needed, then the goal is non-disruptive
SWITCH phase performance improvements
Drain improvements to improve concurrency of log apply and switch phases
REORG REBALANCE SHRLEVEL(CHANGE)
Easier query performance management
Optimizer input to Runstats
DB2/RACF authorization control enhancements
Buffer pool management improvements
New FRAMESIZE BP attribute for direct control of z/OS large page frame usage
Max/min size for WLM system-managed BPs
Cancel DDF thread FORCE option. The FORCE keyword is only allowed after a non force cancel event has occurred, such as via the Cancel Thread command. The FORCE keyword is useful in rare situations, such as executing in relatively tight loop logic where no cancel detection points exist, where the DDF DBAT does not terminate after a standard cancel event. A new z/OS CALLRTM TYPE=SRBTERM service is being provided in z/OS 1.13 to allow DB2 CANCEL THREAD command processing to proactively cancel the thread when it's executing under an SRB.
© 2012 IBM Corporation
Global variables
Autonomous transactions
Array data type support
Grouping sets
New DDL to relate the current table to the archive
Applications can query current + archive with no SQL changes
Archiving process is user-controlled
© 2012 IBM Corporation
Limit SQL incompatibilities when possible
Provide mechanism to identify applications affected by SQL changes
Provide seamless mechanism to make changes at an application (package) level
This mechanism will enable support for up to two back level releases (N-2)
DB2 11 will be the initial deployment of this capability
DB2 10 will be the lowest level of compatibility supported
Access path stability improvements
Migration from DB2 10 only (no skip)
z/OS 1.13 or above. z10 or above.
No pre-V9 bound packages
© 2012 IBM Corporation
Windows: 5-10% Unix: 10-20% System z: 85-100%
System z can help reduce your floor space up to 75%-85% in the data center
System z can lower your total cost of ownership, requiring as little as 30% of the power of a distributed server farm running equivalent workloads
*
Distinguished Engineer
Please note
IBM’s statements regarding its plans, directions, and intent are subject to change or withdrawal without notice at IBM’s sole discretion.
Information regarding potential future products is intended to outline our general product direction and it should not be relied on in making a purchasing decision.
The information mentioned regarding potential future products is not a commitment, promise, or legal obligation to deliver any material, code or functionality. Information about potential future products may not be incorporated into any contract. The development, release, and timing of any future features or functionality described for our products remains at our sole discretion.
Performance is based on measurements and projections using standard IBM benchmarks in a controlled environment. The actual throughput or performance that any user will experience will vary depending upon many factors, including considerations such as the amount of multiprogramming in the user’s job stream, the I/O configuration, the storage configuration, and the workload processed. Therefore, no assurance can be given that an individual user will achieve results similar to those stated here.
IBM IOD 2011
DB2 and the new zEnterprise EC12 machine
DB2 11 (next release)
Fastest uptake
Adoption Driven by:
Virtual Storage Constraint relief for more threads
Security, RAS improvements
As of March 2012
Over two times as many customers –and– over three times as many licenses as what we had with DB2 V8 or DB2 9 at the same point in time. With about 25% of the customers migrating from DB2 V8.
From a quick survey with a set of 500 customers on a DB2 10 for z/OS migration call, a number of the respondents said they are targeting to be in production by YE2011
Customers have been enthusiastic about DB2 10 for z/OS performance and scalability. Some customers are able to simplify their structure and many are seeing better productivity. The security and temporal function are seeing strong early acceptance. A safer infrastructure with better audit function can help customers avoid the need for a new security structure. Customers are signing up for the V8 to DB2 10 migration to save time and to get these improvements faster.
*
© 2012 IBM Corporation
Creating the Hybrid Data Server - Netezza and Z Combine DB2 for z/OS with Netezza to provide an industry exclusive
Transaction Processing Systems (OLTP)
and recoverability
Netezza Accelerator
Data Mart
Data Mart
Data Mart
Best in Consolidation
Best in OLTP and Transactional Analytics
Industry recognized leader in mission critical transaction systems
Together:
Destroying the myth that transactional and decision support workloads have to be on separate platforms
Transactional Analytics
What’s New?
High Performance Storage Saver
Store a DB2 table or partition of data solely on the Accelerator. Removes the requirement for the data to be replicated on both DB2 and the Accelerator
Incremental Update
Enables tables within the Accelerator to be continually updated throughout the day.
zEnterprise EC12 Support
Version 3 will support the zEnterprise EC12, z196 and z114 System z platforms
Query Prioritization
Brings System z workload management down to the individual query being routed to the Accelerator
High Capacity
Support has been extended to include the entire Netezza 1000 line (1.28 PB)
UNLOAD Lite
*
*
*
*
DB2 Analytics Accelerator for DB2 z/OS is the next generation accelerator for System z
Tightly integrated with DB2 for z/OS
Transparent to DB2 applications
Capitalizes on the availability of System z
Minimizes data movement
Leverages Netezza Technology
FPGA for hardware query acceleration (These FPGAs are used for data decompression, data filtering and early SQL projections and restriction)
No need for indexes. MQTs, or query plans
Disk mirroring and blade failover
Box can be remissioned when new technology is introduced
Offloads long running queries from System z
Created for mixed workload of operational transaction systems, data warehouse, operational data stores, and consolidated data marts
The IBM DB2 Analytics Accelerator is:
An integration of IBM hardware, software, and storage and advanced technologies focused on business analytics, that combine to give IBM clients the industry's most high performance analytic capability, to extract business insight from information assets, providing the right answers to the right questions at the right time. .
It removes the costs of traditional performance tuning such as indices, materialized query tables (MQTs) and query plan tuning that required extensive time and expensive resources.
*
Customer interaction
Multiple infrastructures required
Copy
ETL
/ OLTP
Using web service calls to score new transactional data as it is coming into the business is currently very network intensive and can be costly when trying to ensure SLAs.
An alternative to using a web services call is to score the transactional data after it is moved into the data warehouse/data mart/ODS. Because we are now reliant on the ETL schedule, the score is only as current as the last refresh and scoring of the data. This makes it difficult if not impossible to provide the highest level of business insight at the point of customer interaction
Score is static and does not change based on new customer interactions
Score is only as current as the ETL/batch copy/model schedule
High Networking Traffic
As volume increases so do the costs
SLA’s = Real-time Scoring needs to provide the same levels of service as the OLTP application, as our performance, availability, scalability requirements grow as do our costs & complexity, with the needs for more hardware and software to support demand.
Applications may be built off the System z platform , increasing the cost & complexity of scoring
© 2012 IBM Corporation
Predictive
SPSS Modeler for Linux on System z
Version: Modeler v15 Data mining tool used for generating hypotheses and scoring Text analysis for unstructured data to model consumer behavior In-Transaction Scoring with DB2 z/OS: Embeds the Scoring Algorithm Directly within the Transactional Application
IBM SPSS Modeler 14
IBM SPSS Statistics 19
Ad-hoc analysis,
IBM SPSS Decision Management
A decision process framework that uses predictive analytics to optimize the outcomes of the thousands of operational decisions
IBM SPSS Collaboration and Deployment Services
A platform for the management and deployment of analytical assets.
It supports predictive models as they are prepared, validated and integrated with operational systems.
© 2012 IBM Corporation
Customer Interaction
Reduced Networking
in-database scoring on the same platform
Consolidates Resources
Scoring Algorithm
Provide the same qualities of service as the business system
Improve the accuracy of our decision because we are scoring the most current data
Can deliver both in-transaction and in-database scoring on the same platform for greater economies of scale & expertise
Potential to reduce ETL requirements
Co-locate OLTP, DW and Modeler, reducing the copy/movement
Reducing the network band width required for scoring because all functionality is internal to System z
Improves the speed and accuracy of building & repeating models
With System z’s proven performance we can define new patterns faster & more frequently
Capacity to scale to build models off larger data sets
Consolidates resources
Reduce floor space, heating, cooling required
We can further accelerate IBM DB2® queries with in-memory processing, massively parallel architecture, row and columnar store technologies, highly compressed data and compressed data operations
© 2012 IBM Corporation
Meets most demanding workload
Meets stringent SLA requirement
Provides best value
Most accurate score is calculated in real time
Can you really scale to support the volume of transactions a OLTP application processes without impacting performance?
Workload runs for 14 minutes
At 8 CP, windows server to small to driver sufficient workload
HS = Historical Score RT = Real Time Score
Workload is single select that gathers input data for the scoring model, executes that model and returns inputs and scoring results. Typical application will include more logic than this.
SELECT
T0.C0 AS C0, T0.C1 AS C1, T0.C2 AS C2, T0.C3 AS C3,
T0.C4 AS C4, T0.C5 AS C5,
UNPACK(
HUMSPSS.SCORE_COMPONENT('H','ce815fe5b47e83cc548b65dcf5785eb5',
FROM (
SELECT
T0.SALARY AS C3,T0."TRANS_SPEND_1" AS C4,
T0."TRANS_POINTS_1" AS C5
FROM "data_1_by_20" T0)
Dramatically different than prior releases
We keep innovations coming
Existing customers can modernize and consolidate their BA environments and extend their current QMF investment at low cost
Workstation and WebSphere environments let you create and deploy visually rich reports, interactive dashboards, and much more
TCO is based on an enterprise-wide model. The greater the number of users and databases accessed, the lower the cost
IBM DB2 Analytics Accelerator creates significant performance improvements and savings for QMF
Faster responses to queries
Act faster on results, dig deeper, generate personalized reports and dashboards
Make more timely, informed decisions
Provides a lower cost option for massive batch operations, one of QMF’s known strengths
What’s new?
New QMF redbook: Complete Analytics with IBM DB2 Query Management Facility http://www.redbooks.ibm.com/redbooks/pdfs/sg248012.pdf
Join our next beta program!
Ask your IBM representative to visit the following web page for information:
*
75-90% less usage in DB2 10 compared to DB2 9
Some of working storage (stack, xproc storage) stays below 2GB
Larger number of threads
Improve CPU with storage
DB2 10
Thread / Stack/ working
Laboratory measurements and early customer experience have shown substantial savings in the primary constrained address space, DBM1. Most measurements have shown 75% to 90% savings for the virtual storage in that address space below the 2 GB bar. Some EDMPOOL and some working storage remains below the bar.
This storage relief allows many more threads or concurrent users in a DB2 subsystem, allowing new possibilities for optimization.
Some customers will be able to consolidate data sharing members, saving on memory, CPU and administration time.
Other customers will be able to use the storage to improve service or to reduce CPU time more. Some common examples are expected to be use of RELEASE(DEALLOCATE) and larger amounts of dynamic statement cache.
© 2012 IBM Corporation
DB2 10 Performance
Most customers can see a 5% - 10% out-of-the-box CPU reduction (transactions and batch) after rebind
Some workloads and customer situations can see a CPU reduction of up to 20%
Synergistic operation with latest System z hardware
Sample: Preliminary Measurements of IBM Relational Warehouse Workload (IRWW) with Data Sharing
Base: DB2 9 NFM REBIND with PLANMGMT EXTENDED
DB2 9 NFM DB2 10 CM without REBIND showed 1.3% CPU reduction
DB2 10 CM REBIND with same access path showed 4.8% CPU reduction
DB2 10 NFM brought 5.1% CPU reduction
DB2 10 CM or NFM with RELEASE DEALLOCATE 12.6% CPU reduction from DB2 9
CPU Percent reduced from DB2 9
© 2012 IBM Corporation
Avoids processing to go inactive and then back to active
Enabling High Performance DBAT, e.g. for WebSphere
BIND client packages into different collection coll2 with RELEASE(DEALLOCATE)
BIND other frequently executed packages with RELEASE(DEALLOCATE)
Set -MODIFY DDF PKGREL(BNDOPT) to enable
In WAS datasource property definition point to new collection
E.g. jdbcCollection=coll2
© 2012 IBM Corporation
Easier scaling, simpler memory management
Reduce contention, more online processing
Reduced need for REORG
Index list prefetch enhancements
Statement level monitoring
DDF thread management enhancements
REORG
Customers appreciate the improved performance and scalability. Virtual storage management almost disappears. DB2 has a strong focus on making DB2 easier to use by automating tasks and eliminating tasks where possible. Avoiding the manual invocations can also help avoid problems for running the function too often or not often enough. Where the task cannot be eliminated, the frequency and monitoring can be reduced, such as the need to reorganize. The improvements for virtual storage and for availability also help administrator productivity.
Some of the improvements come within DB2 for z/OS. Improvements in SQL and XML improve productivity for those who develop new applications and for those who are porting from other platforms. Some of the improvements remove complexity from application tasks.
Some of the improvements come with Data Studio for application programming and administration – stronger cross-platform graphical interfaces, better integration with Java, improvements in the ability to develop and debug.
Allowing tailored names for DSNHDECP will permit many subsystems to share the SDSNEXIT data set.
© 2012 IBM Corporation
Problem: For distributed workloads, low priority or poorly behaving client applications may monopolize DB2 resources and prevent high-priority applications from executing.
Solution: Increased granularity of monitoring for system level activities
Number of connections
Number of threads
Idle thread timeout
Profiles specified in SYSIBM.DSN_PROFILE_TABLE
DB2 10 for z/OS adds support for filtering and threshold monitoring of system related activities via new keywords
Number of threads
Number of connections
Idle thread timeout
New scope filters
Product-specific identifier
© 2012 IBM Corporation
Page size
Hashed
DSSIZE
Improve availability and productivity
Increase maximum size substantially
DDL concurrency also improved from removal of
DBD01 hash anchor locks
Many more table spaces, partition by growth
Row level locking, reordered row format
CLOB and BLOB columns for long strings
Inline for performance
Online reorganization and check
More automatic: DB2-managed SMS-controlled
Allow query of SYSLGRNX
*
The DB2 catalog and directory are restructured in DB2 10 ENFM to improve productivity and availability. You’ll see these improvements in NFM. The current size limits are increased substantially and contention among process like BIND, dynamic SQL, data definition and utilities is reduced. With more table spaces and more structures, more work is required for some process, such as BIND.
The primary techniques are changes in the DB2 catalog to remove links and the special structures for the catalog. These table spaces change from many tables to one table per table space in a partition by growth table space defined as DSSIZE 64 GB and MAXPART 1. Row level locking is used in place of page level locking. The new catalog tables use a partition by growth universal table space structure. Each table space holds a single table, so many more table spaces are needed. Rather than repeating columns with parts of long strings, the catalog will use CLOB and BLOB columns to store the data, expanding maximum sizes. Inline LOBs are used for the performance improvements. The new structure allows more standard processes, so that all catalog tables can be reorganized and checked online.
The DB2 catalog changes from using manual definition and extension to DB2 managed data sets under SMS control. The changes improve productivity and availability, but take time to set up.
© 2012 IBM Corporation
Protect sensitive data from privileged users and improve productivity
Separation of duties
More granular control
New SECADM authority for security administration
System level DB admin with/without access control and with/without data access privileges
Usability: DBADM for all DB
New Explain privilege
Audit admin authority usage
Support distributed identities introduced in z/OS 1.11
Support client certificate authentication in z/OS 1.10
Support password phrases in z/OS 1.10
Connection level security enforcement using strong authentication
Row and column access control
Allow masking of value
Monitor
Tasks
Customers are being pressed for a wide range of improved security and compliance. Data retention is a growing need. Protecting sensitive data from the privileged users and administrators is required. Separation of authority for security, access, and some common tasks, like EXPLAIN will help. Auditing for privileged users can also make compliance simpler.
Access control is refined in several ways with better granularity for the administrative privileges and with finer grained access control at the row and column level, including the ability to mask access to some fields. Auditing policies: new IFCID for auditing admin authority usage. Dynamic table auditing (all access, stmt id level).
Granular admin authorities, separation of duties: sysadm: system level dbadm with/without accessctrl, with/without dataaccess. System level SECADM (separation of dutes, shift from dba to security group, zparm control to remove privileges from sysadm). System level SQLADM. App level explain privileges.
Distributed identities: supported in v9 via EIM, but not much used due to complexity. V10 allows for use of new RACF feature to map distributed identities passed in from app server to a RACF id. Ap server passes authenticated distributed end user identify using trusted context.
Client certificate: Eliminates need to manage RACF ids on DB2 clients. Used to be supported only via trusted context, but now also outside trusted context.
Connection level security enforcement: Zparm TCPLVER value SERVER_ENCRYPT
© 2012 IBM Corporation
Temporal Based Analysis
System-maintained temporal tables
DB2 generated history
AS OF query
User-maintained temporal tables
Automatic business time key enforcement.
Query over any current, any prior, future point/period in business time.
New time range update/delete statements support automatic row splitting, exploited by the merge statements.
Bi-temporal, combination of the above two
© 2012 IBM Corporation
System Time Query
SELECT Dept
FROM employees
SELECT Dept
WHERE EmpID=12345
SELECT count(distinct Dept)
WHERE EmpID=12345
EmpID
Dept
System_start
System_end
12345
M15
05/31/2000
12/31/9999
EmpID
Dept
System_start
System_end
12345
J13
11/15/1995
01/31/1998
12345
M24
01/31/1998
05/31/2000
67890
K25
11/15/1995
03/31/2000
DB2 Family Row and Column Access Control
A table level authorization function fully implemented in the database that provides:
Row level access control based on customer-supplied rules
A doctor can see rows representing his patients only
A manager can see rows representing his employees only
Column level access control based on customer-supplied rules
This is also called data masking
A teller can see only the last 4 digits of the credit card number column
Defined and managed by SECADM
© 2012 IBM Corporation
Prerequisites: migrate from DB2 9 NFM or DB2 V8 NFM
z/OS V1.10 SMS-controlled DB2-managed DB2 catalog
System z z196, z10, z9, z890, z990, and above (no z800, z900)
DB2 Connect 9 FP1, 9.7 FP3a for 10 new function
Premigration check DSNTIJPA PM04968
Items deprecated in earlier versions eliminated: more for V8 mig.
Private protocol DRDA
Plans containing DBRMs packages
*
Here are a couple of thoughts about what might be required in hardware and software to run DB2 10. Much will depend upon the timing of the deliveries and market acceptance. Moving forward as quickly as possible means that some of the past must be left behind. See the list of deprecated functions from prior versions.
The above functions are still included in DB2 9, but are generally deprecated and may be removed from future versions. Note the direction indicated to the right of the arrows, as these are the functions provided to replace the existing function. If you are using any of these functions, you are advised to move to the new function.
See the Installation Guide section, “Functions that are deprecated” and the announcement material for more information on these changes.
http://www.ibm.com/common/ssi/rep_ca/8/897/ENUS206-098/ENUS206-098.PDF
Embedded xQuery support – PM47618
Needed for SPSS in-database analytics
Stored procedure monitoring improvements – PM53243
More easily identify a problematic stored procedure or a statement within that stored procedure
New zparm for OPTIMIZE FOR 1 ROW to allow sort access plans – PM56845
V10 migration issue for some customers
GBP Delete Name enhancement – PM67544
Performance enhancement for sysplexes that span longer distances
© 2012 IBM Corporation
New DEL_CFSTRUCTS_ON_RESTART ZPARM for auto-delete of CF structures on restart if no active connections exist
Useful for DR environments
ALTER MAXROWS to set AREO* rather than AREOR
PM43597 (V10)
Ability to SELECT from SYSLGRNX
PM35190 & PM42331 (V10)
PM31641 (V10)
5X increase in recommended limit on concurrent RECOVER jobs per DB2
LOB pageset support for RECOVER BACKOUT YES
PM45650 (V10)
Latest z servers can support TB main memory sizes
A single DB2 member can currently support up to 1 TB of BP space
DB2 10 has improvements for large BP scalability/performance
1MB page frame support
“In Memory” BP attribute
Avoid BP scans for data sharing TS/part/index inter-DB2 interest level changes
Internal BP hashing and latching improvements
VSCR improvements for 5-10x more active threads per DB2 member/subsys
Opportunities to tradeoff increased memory usage for reduced CPU
DB2 10 new RTS column to indicate SSD usage
New zEC12 machine introduces flash memory
Future: continue to leverage large main memory and automatic storage hierarchy management for performance advantages
© 2012 IBM Corporation
Multi core, future slowing growth in single thread performance
Higher n-ways, more parallelism bring potential latching bottlenecks, memory cache thrashing, …
S/W techniques for single threaded performance growth
Clustered systems for massive scale out and continuous availability
Specialty engines (price / performance)
New performance opportunities
Memory hierarchy design
Higher CPU frequencies, n-ways make cache utilization a critical factor
Translation lookaside buffer design, large System z page sizes
Solid state disk ( and other disk related improvements)
Performance, energy consumption, reliability benefits of HDD
Large and rapidly growing main memory sizes along with falling prices
Performance improvements through more memory use
DB2 10 enables more persistent threads with RELEASE(DEALLOCATE)
Larger buffer pools, in-memory workfiles, I/O avoidance
Multi-core processors, alone, will be insufficient to enable application-level systems price & performance improvements at historical rates. Semiconductor scaling, which has provided the foundation for system-level improvements in cost and performance, is slowing down. While densities are expected to continue to improve, power efficiency is expected to lag. Transistor performance at constant power density has been close to constant since the 90nm node. The resulting lack of processor frequency growth has forced the industry to seek performance improvements through the introduction of multiple cores on a processor, and the parallel software to exploit them. In contrast to nodes prior to 32nm and 22nm, power density at constant frequency (and constant switching factor) will increase node-to-node past 22nm. This means that if a core was migrated from 22nm to 15nm and replicated to fill a chip of similar size, the operating frequency of these cores would have to be reduced in order to use the chip in a system of similar power supply and cooling capacity.
© 2012 IBM Corporation
Hardware-based Coupling Facility for efficient
DB2 data sharing (availability and scale out)
zIIP engines to offload certain DB2 workloads
Hardware instructions for Unicode conversion
Hardware data compression & encryption
Cross-memory, memory protection keys
with multi-track data transfer and FICON Express 8
AIX / Linux / Win (SOD)
Availability - Resiliency management and fewer points of failure. Centralized workload management aligned to business priorities.
Integration and stack integration.
Scalability - Ability to meet massive demands from users and data.Unmatched scalability with the highest transaction processing capacity.
Affordability - workloads on zEnterprise have new solution price points, that looks like a Unix/Intel solution price.
And delivers also:
Flexibility - Horizontal integration with SAP on IBM blades improves application deployment choices.
Virtualization - Centralize Management of virtual servers across a heterogeneous pool
Enable thousands of virtual servers within a single integrated system.
Efficiency - Reduced infrastructure complexity through consolidation, automation and virtualization; saving on software and environmental costs, reduce labor, energy, and development costs.
© 2012 IBM Corporation
20-28% CPU reduction measured with DB2 OLTP workloads
25% reduction measured with DB2 query and utilities workloads
Less compression overhead with DB2 data (1-15%)
50% More System Capacity to help consolidation
Excellent synergy with DB2 10 scalability
New Features DB2 plans to exploit
FLASH memory and pageable 1MB frames
Enables larger DB2 Buffer Pools
2GB frame support
Enable even larger DB2 Buffer Pools
Larger frames expected to provide additional CPU savings, especially for very large memory
DB2 code backed by large frames for CPU reductions
Enhanced prefetch instruction for CPU reductions
Transactional Memory provides further possibilities for performance gains
Our official numbers for z196->zEC12 are,
1. 20 to 28% CPU reduction in OLTP workload (28% with TPC-E, 21% with SAP DP)
2. 25% CPU reduction in Query and Utility
1M pageable requires the latest z/OS 1.13, 2G support requires z/OS 2.1. 1M page sizes for code requires z/OS 2.1.
Improved efficiency for CMPSC instruction.
Decimal Floating Point Zoned Conversion Facility: allows us to push string to DECFLOAT conversions to hw.
*
Everyone wants to be like DB2 for z/OS!
DB2 and z/OS have had advanced cloud and multitenancy capabilities for years
Many DB2 for z/OS customers are already running advanced multi tenant configurations in large scale production
Deep integration between DB2, z/OS, System z hw, and other subsystems gives unparalleled support for OLTP and mixed workloads with low TCO
IDAA delivers another level of system integration and optimization for running analytics workloads alongside OLTP with DB2 for z/OS
DB2 10 and DB2 11 extend these capabilities
© 2012 IBM Corporation
Proliferation of mobile and other network-connected devices is driving increases in:
transaction workloads
data volumes
24x7 requirements
Competitive pressures continue to drive an increasing need for innovation, analytics, and data integration
DB2 for z/OS has leading edge capabilities to support these requirements and DB2 11 makes important improvements
Everyone is talking about „cloud“ and „multitenancy“ these days with regards to satisfying these requirements. DB2 for z/OS is already very well positioned, espeically on the latest z machines, with its scalability, high availability, multitenancy, and ability to run mixed workloads. DB2 11 adds further to these capabilties.
© 2012 IBM Corporation
0-5% for OLTP
Exploitation of new zEC12 hardware features
Continuous Availability Features
Online REORG improvements, less disruption
DROP COLUMN, online change of partition limit keys
Extended log record addressing capacity (1 yottabyte)
BIND/REBIND break into persistent threads
Enhanced business analytics
Temporal and SQLPL enhancements
No application changes required for DB2 upgrade
Access path stability improvements
© 2012 IBM Corporation
Extended RBA Problem Statement
DB2’s Relative Byte Address (RBA) for logging is 6 bytes
Gives 256TB of log record addressing capacity per DB2 subsystem/member
With heavy sustained logging rates, DB2 can exhaust the 6-byte RBA
DSNJ032I and DSNJ033E warning messages
Alert-level = ‘WARNING’ when RBA reaches x’F00000000000’
Alert-level = ‘CRITCIAL’ when RBA reaches x’FFFF00000000’
Manual recovery actions are needed
Data Sharing: shut down the affected member and start a new member in its place
Non Data Sharing: reset all PGLOGRBA values back to zero (extended outage)
Documented in the DB2 Administration Guide
If alert-level reaches ‘CRITICAL’ then DB2 terminates to protect data integrity and force recovery actions
Reason code 00D10251
© 2012 IBM Corporation
Extended LRSN Problem Statement
The data sharing Log Record Sequence Number (LRSN) is derived from the 8-byte time-of-day clock which hits end of range in 2042
However, some data sharing groups have a non-zero LRSN “delta” which gets added to the TOD clock
If a non-zero “delta” exists, then the LRSN will hit end of range prior to 2042
Use DSNJU004 to determine if you have a non-zero LRSN delta value
A “delta” value could be set when data sharing is enabled or re-enabled
Whenever the end-of-log RBA of the enabling member is past the TOD clock
Some non data sharing customers have enabled data sharing to circumvent RBA nearing end-of-range
This would cause a non-zero LRSN delta, so LRSN hits end of range before 2042
6-byte LRSN value has precision to only 16 microseconds
Can cause LRSN ‘spinning’ which burns extra cpu and aggravated log latch contention
V9 NFM addresses most LRSN spin situations, and V10 NFM enhances further. But some spins still exist due to the 16 usec granularity (log latch not held, page latches are)
© 2012 IBM Corporation
Expand the RBA and LRSN to 10 bytes
RBA addressing capacity of 1 yottabyte (2**80)
LRSN extended on left by 1 byte, on the right by 3 bytes
>30,000 years and 16Mx more precision
8 bytes is not sufficient to solve LRSN issues and may not give sufficient capacity for the longer term
NFM only (6 byte RBA/LRSN continues to be used in CM)
Once in NFM, DB2 continues to use 6-byte values until you take action to convert
Two conversion tasks:
Convert BSDSes to new format to enable logging with larger RBAs/LRSNs
Convert pagesets to new page format
These tasks are optional
If you don’t care about larger RBAs/LRSNs then you don’t have to convert
But performance will be better if you convert BSDSes (avoid internal conversion overhead on log write)
BSDSes can be converted without converting pagesets
Pagesets can be converted in a piecemeal fashion
Expectation is that most customers will roll the conversion over a period of days/weeks/months
© 2012 IBM Corporation
Some Planned DB2 11 RAS Improvements
BIND / DDL concurrency with persistent threads
Use of persistent threads likely will increase in V10 with vstor relief
More online schema changes
Alter partitioning limit keys
Cancel DDF Threads – new FORCE option
Open data set limit raised to 200K
Workfile space shortage warning new instrumentation and messages
REORG avoidance
Automatic cleanup of index pseudo deleted entries
Online REORG improvements – if a REORG is needed, then the goal is non-disruptive
SWITCH phase performance improvements
Drain improvements to improve concurrency of log apply and switch phases
REORG REBALANCE SHRLEVEL(CHANGE)
Easier query performance management
Optimizer input to Runstats
DB2/RACF authorization control enhancements
Buffer pool management improvements
New FRAMESIZE BP attribute for direct control of z/OS large page frame usage
Max/min size for WLM system-managed BPs
Cancel DDF thread FORCE option. The FORCE keyword is only allowed after a non force cancel event has occurred, such as via the Cancel Thread command. The FORCE keyword is useful in rare situations, such as executing in relatively tight loop logic where no cancel detection points exist, where the DDF DBAT does not terminate after a standard cancel event. A new z/OS CALLRTM TYPE=SRBTERM service is being provided in z/OS 1.13 to allow DB2 CANCEL THREAD command processing to proactively cancel the thread when it's executing under an SRB.
© 2012 IBM Corporation
Global variables
Autonomous transactions
Array data type support
Grouping sets
New DDL to relate the current table to the archive
Applications can query current + archive with no SQL changes
Archiving process is user-controlled
© 2012 IBM Corporation
Limit SQL incompatibilities when possible
Provide mechanism to identify applications affected by SQL changes
Provide seamless mechanism to make changes at an application (package) level
This mechanism will enable support for up to two back level releases (N-2)
DB2 11 will be the initial deployment of this capability
DB2 10 will be the lowest level of compatibility supported
Access path stability improvements
Migration from DB2 10 only (no skip)
z/OS 1.13 or above. z10 or above.
No pre-V9 bound packages
© 2012 IBM Corporation
Windows: 5-10% Unix: 10-20% System z: 85-100%
System z can help reduce your floor space up to 75%-85% in the data center
System z can lower your total cost of ownership, requiring as little as 30% of the power of a distributed server farm running equivalent workloads
*