DB2 11 for z/OS Overview and Latest News Jeff Josten
by user
Comments
Transcript
DB2 11 for z/OS Overview and Latest News Jeff Josten
DB2 11 for z/OS Overview and Latest News Jeff Josten IBM Silicon Valley Lab © 1 2015 IBM Corporation © 2012 IBM Corporation 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. 2 2 © 2015 IBM Corporation Agenda • Intro - What we are up to • DB2 11 • Key Challenges in Database Technology • Next wave of applications • Analytics and OLTP integration • Performance and scalability • Continuous availability, security • Autonomics and simplification • Wrap up with Q&A 3 © 2015 IBM Corporation DB2 for z/OS strategic roadmap Support the next wave of applications …. Analytics Real time analytics integrated with OLTP Mobile Easy app development, Enterprise mobile scale Cloud Self provisioning, Multi-tenancy, Self managing, guaranteed SLAs while enhancing the unique value of System z Scalable Reliable Efficient Secure 4 © 2015 IBM Corporation DB2 for z/OS Purpose 5 © 2015 IBM Corporation DB2 for z/OS: the Engine that Drives the World 6 6 © 2015 IBM Corporation DB2 11 for z/OS Strong uptake out of the gate Over 150 customers* Faster migration success 2x faster adoption Out-of-the-box quality/stability 68% fewer PMRs 35% fewer APARs Fastest SAP certification in history! DB2 10 Withdraw from Marketing: July 6, 2015 End of Service: Sept 30, 2017 7 * As of Dec 2014 Latest product Information at World of DB2 © 2015 IBM Corporation Licenses Strong DB2 11 Adoption Quarters post GA 8 8 © 2015 IBM Corporation DB2 11 APAR Trend 9 © 2015 IBM Corporation DB2 11 PMR Trend 10 © 2015 IBM Corporation DB2 11: The Database for Enterprise OLTP and Analytics Affordable for every workload with out-of-the-box savings – Up to10% for complex OLTP – Up to15% for update intensive batch – Up to 40% for queries Business critical analytics GA Oct. 25, 2014 – Expanded SQL, XML and analytics capabilities – Hadoop integration, NoSQL/JSON support – In-transaction real-time scoring – Advanced QMF analytic capabilities with mobile support Enhanced Resiliency and Continuous Availability – Fewer planned outages, fewer REORGs, faster recovery – Transparent archiving, access warm/cold data in single query Simpler, faster upgrades for faster ROI – 16x faster catalog migration – No application changes required for DB2 upgrade – Product quality and stability – raised the bar 11 © 2015 IBM Corporation DB2 11 Planning Dual mode migration (CM, ENFM, NFM) DB2 10 is the platform for migration z/OS 1.13 or above. z10 or above. No pre-V9 bound packages DB2 Connect V10.5 FP2 is the recommended level – This level is required to exploit most new features – Any in-service level DB2 Connect supports V11 Sysplex query parallelism support is removed DB2 11 Migration Planning Workshop (MPW) – Free, 1-day education – DB2 11 MPW Community on DeveloperWorks, http://ibm.co/IIJxw8 12 © 2015 IBM Corporation DB2 for z/OS Timeline DB2 Cypress Planning • DB2 11 NFM is the prereq for migration • z/OS 2.1 or above. z196 hw or above • No pre-V10 bound packages • Get rid of 31-bit runtime for code simplification and performance • More memory = more performance 201x 2013 2010 2007 2004 V9 DB2 10 DB2 Cypress V10 EOS DB2 11 9/2017 V9 EOS 4/2014 V8 13 © 2015 IBM Corporation Extend the Core Values of DB2 for z/OS Parallel Sysplex and DB2 data sharing Availability and scalability leadership Multi-site DR and continuous availability leadership High Performance In-memory database evolution Reliability, quality, security leadership System z hw/sw integration 14 © 2015 IBM Corporation Impressive DB2 11 Performance Results! Query Batch OLTP XML 15 © 2015 IBM Corporation DB2 11 query performance Results show 10-46% CPU saving for DB2 measured workloads 16 © 2015 IBM Corporation Performance Improvements no REBIND needed – Partial List DDF performance improvements – Reduced SRB scheduling on tcp/ip receive using new CommServer capabilities – Improved autocommit OLTP performance INSERT performance – Latch contention reduction – CPU reduction for Insert column processing and log record creation – Data sharing LRSN spin avoidance – Page fix/free avoidance in GBP write Automatic index pseudo delete cleanup IFI 306 filtering capabilities to improve Replication capture performance DGTT performance improvements – Avoid incremental binds for reduced cpu overhead Utilities performance improvements Java stored procedures: multi threaded JVMs, 64-bit JVM – more efficient 17 © 2015 IBM Corporation Performance Improvements REBIND required – Partial List Query transformation improvements – less expertise required for performant SQL Enhanced duplicate removal – Lots of queries require duplicate removal: e.g. DISTINCT, GROUP BY, etc. – Dup elimination via sorting can be expensive – New techniques: Index duplicate removal, early out In-memory techniques – In-memory, reusable workfile – Sparse index (limited hash join support) – Non-correlated subquery using MXDTCACH – Correlated subquery caching Select list do-once – Non column expressions in the select list can be executed once rather than per-row Column processing improvements – Xproc (generated machine code) for column processing DPSI performance improvements Data de-compression optimizations Optimizer CPU and I/O cost balancing improvements DRDA package based continuous block fetch 18 © 2015 IBM Corporation Performance Improvements Sysprog, DBA, or application effort required – Partial List Suppress-null indexes – Index entries not created when all values for indexed columns are NULL – Reduced index size, improved insert/update/delete performance, compatibility with other DBMSes – Improved utility and CREATE INDEX performance New PCTFREE FOR UPDATE attribute to reduce indirect references DGTT performance improvements – Non logged DGTTs Extended optimization - selectivity overrides (filter factor hints) – Improve optimizer’s ability to find the cheapest access path – Collect filter factors for predicates in a Selectivity Profile Open dataset limit raised to 200K 19 © 2015 IBM Corporation DB2 11 enhancements applicable to analytics Improved predicate filtering – filtering rows earlier Sparse index (in-memory data cache) Index skipping and Early-out Page range performance improvements Sort / Workfile performance improvements DPSI query performance improvements –DPSI can benefit from page range screening from join –Improved DPSI Join Performance (using parallelism) –Sort avoidance for DPSIs (also known as DPSI merge) –Straw-model parallelism support for DPSI 20 © 2015 IBM Corporation Hardware Trends Leveraged by DB2 for z/OS 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) Hybrid systems, accelerators – Use cores that are more specialized to their purpose – 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 21 © 2015 IBM Corporation zHyperWrite for z/OS, DB2 and DS8870 New zHyperWrite function for DB2, z/OS and DS8870 with GDPS or TPC-R HyperSwap – Delivered year end 2014 – Leverages synergy of z/OS and DS8870 replication technologies – Designed to accelerate DB2 Log Writes in Metro Mirror environment • Benefits include: – Improved DB2 transactional latency – Log throughput improvement – Additional headroom for growth – Improved resilience for workload spikes – Potential cost savings from workload consolidation – DB2 commit response time reduced up to 40% • Benefit percentage varies with distance • Requires: – zHyperWrite function in z/OS 2.1, with the PTF for APAR OA45662 – DB2 10 and DB2 11 with PTF for APAR PI25747 – IBM DS8870 Storage Subsystem MCL 22 © 2015 IBM Corporation Milliseconds Average commit time Single log page writes Log replication at zero distance 0.8 0.6 0.4 0.2 0 No zHyperWrite zHyperWrite # Log CI Created Per Commit When writing a single log CI, IBM zHyperWrite saves 300 microseconds per Commit. 40% reduction in Commit response time. IBM Confidential 23 © 2015 IBM Corporation HyperWrite with DB2 Batch Updates • A customer running 2.7 millions update (commit every 3 updates) • 28% DB2 elapsed time improvement by reducing log write wait during update commit, 26% Job elapsed time improvement. HypweWrite - ON CL2 CPU -28% Update Commit Lock latch Log write Not account HyperWrite - OFF Others 0 24 1000 2000 3000 4000 Time (second) 5000 6000 © 2015 IBM Corporation DB2 11 RAS and Usability Improvement Highlights Logging capacity and performance: RBA/LRSN optionally expands to 10 bytes BIND / DDL / Online REORG break-in for persistent threads – Avoid having to shut down apps to get a REBIND through, e.g. for application upgrades More online schema changes – Alter partitioning limit keys – DROP column – Point in time recovery support for deferred schema changes Autonomics improvements – Automatic index pseudo delete cleanup – Overflow row reduction – Optimizer externalizes missing stats to enable automated RUNSTATS Data sharing improvements – – – – Group buffer pool write-around Restart light enhancements Index split performance and other indexing improvements Full LRSN spin avoidance Plan management improvements - APREUSE(WARN) support ACCESS DATABASE … MODE(STATS) option to externalize RTS statistics 26 © 2015 IBM Corporation Security Enhancements Remove inconsistencies between DB2 and RACF access controls – Automatic DB2 cache refresh when RACF changes are made • Package auth cache, dynamic statement cache, user authentication cache – Support BIND OWNER when using RACF exit – Support auto REBIND using owner’s authid when using RACF exit – Dynamic SQL authorisation checking improvements • Honor DYNAMICRULES(BIND) rules Bind plan option to ensure the program is authorized to use the plan – New PROGAUTH bind option Remove column masking restrictions for GROUP BY and DISTINCT 27 © 2015 IBM Corporation Summary of Utilities Improvements Over 40 new enhancements! Availability – Online data repartitioning • REORG REBALANCE SHRLEVEL(CHANGE) • Online ALTER of limit keys – Online REORG availability improvements • SWITCH phase reduction • Improved drain processing – Part level inline image copies for REORG Usability – Online REORG automated mapping tables – REORG delete unused PBG datasets – System cloning improvements CPU reduction – More zIIP offload for LOAD and RUNSTATS Performance – – – – – 28 Faster LOAD processing via increased parallelism Inline statistics improvements, reduced need for RUNSTATS Optimizer input to statistics collection REORG option to avoid sorting data for clustering Improved buffer pool efficiency © 2015 IBM Corporation DB2 for z/OS: Empowering the Future Simplification and autonomics Analytics and Hybrid Transaction and Analytics Processing (HTAP) Support the next wave of applications Mobile and Internet of Things (IoT) Cloud and developer self-service DevOps: continuous application delivery 29 © 2015 IBM Corporation Example benefit of DB2 11 performance & pseudo delete cleanup Websphere Portal internal measurement –At day 1 – 18% CPU saving • Due to various DB2 11 performance improvements –At day 5 – 39% CPU saving • Due to 93% reduction in pseudo-deleted index entries WAS Portal Workload 5 Days Performance 0.0035 2500000 2000000 CPU time (sec) 0.0025 1500000 0.002 0.0015 1000000 0.001 500000 #of pseudo_deleted entries 0.003 V10 Total CPU time V11 Total CPU time 0.0005 0 30 0 Day1 Day2 Day3 Day4 Day5 © 2015 IBM Corporation Easier DB2 Version Upgrade Application Compatibility (APPLCOMPAT) – New feature to ease DB2 version upgrades – avoid impact to applications – New mechanism to identify applications affected by SQL changes in the new release – Seamless mechanism to make changes at an application (package) level or system level Faster ENFM processing – Lab measurement showed 18x faster in V11 vs. V10 using a large customer catalog Access path stability improvements Higher code quality stability levels SQL Capture/Replay tooling can help testing of DB2 version upgrades Migration Planning Workshops (MPW) – See the DB2 11 MPW community in DeveloperWorks for latest info 31 © 2015 IBM Corporation DB2 Drivers – Panoramic View PhP Javascript node.js Python/Jython Ruby/JRuby Scala Java JDBC API Zend framework adapters node-odbc SqlAlchemy/ Django Adapter node.js JSON driver Rails Adapter Lift pureQuery API JSON API Python interpreter c java Ruby interpreter c Hibernate java JPA DB2 CLI and ODBC driver DB2 JCC JDBC driver DB2 32 © 2015 IBM Corporation DB2 11 Expanded Application Capabilities Global variables SQLPL improvements: array data type, autonomous transactions Alias support for sequence objects Temporal data enhancements – Support for views – Special register support – Integrated auditing support (planned) Transparent archive query SQL Grouping Sets, including Rollup, Cube Unicode column support for EBCDIC tables Hadoop access via table UDF JSON support 33 © 2015 IBM Corporation Transparent Archive Query Cheaper storage High performance, availability storage Current data ■ ■ ■ 34 Archive data Applications can query current + archive with no SQL changes ■ By default, data is retrieved from base table only, as usual ■ Set a new global variable when archive data is desired ■ DB2 automatically converts SQL to UNION ALL via dynamic plan switching technique (high performance) Archiving process is user-controlled Move_To_Archive global variable allows DELETEs to be automatically archived © 2015 IBM Corporation Mobile workloads Impact Systems differently than web workloads. Increase in peak and off-peak transactions. Increased query or “read-only” transactions. Unanticipated spikes in workload due to popular apps, features or special offers. Mobile applications often change more frequently Mobile workloads require an agile, scalable and robust DBMS like DB2 for z/OS 35 © 2015 IBM Corporation Mobile/Cloud integration simple and secure z/OS Connect – Enabling Hybrid Cloud for z/OS IBM WebSphere Liberty z/OS Connect – Shipped with WAS, CICS, and IMS Unifies z/OS connectors – a common solutions for mobile, cloud, and web Simplified integration – discover, manage, securely access z/OS assets Batch, WAS Mobile-Optimized APIs Cloud APIs Cloud-based Services Enterprise Systems Integration On-Premise Enterprise APIs Systems of Engagement 36 Enterprise Applications IBM WebSphere Liberty z/OS Connect Enterprise Data Enterprise Transaction Processing CICS, IMS Systems of Record © 2015 IBM Corporation Coming: DB2 Adapter for z/OS Connect * Config.xml Deploy to bundled zOS Connect Data Web Services Test Client Data Web Services of Data Studio or RDz WSDL Database operations ZOS Connect artifacts * Planned GA June, 2015 37 Deploy to remote zOS Connect z/OS Connect CICS IMS WAS DB2 ZOS Connect on LPAR © 2015 IBM Corporation Cloud: Challenge for Fast Provisioning of a DB2 Environment Fast provisioning a DB2 environment as part of provisioning a software stack – DB2 environment scope can be • DB2 system • Migrate to a new version of a DB2 system • Database in an existing DB2 system • Access to an existing database in an existing DB2 system • Copy of an existing database • …. zSystem is a highly virtualized shared environment and many people need to be involved for the provisioning – SME (division of responsibilities) for different aspects of system management like storage, network, security, … and DB2 system management – DB2 Installations are highly customized across different companies Easier DB2 migration or provisioning would greatly help sysprog’s today and can also form the basis for cloud based solutions 38 © 2015 IBM Corporation z/OS Management Facility Can Help IBM z/OS Management Facility (z/OSMF) delivers on the IBM’s strategy for mainframe simplification and modernization Is a free feature of z/OS Provides a modern browser based interface for managing the z/OS system. z/OSMF workflows can be customized once, executed multiple times Extended to simplify management of z/OS subsystems like DB2 z/OSMF workflows can help DB2 staff to – Migration multiple DB2 subsystems easily – Deploy a new DB2 subsystem quickly 39 © 2015 IBM Corporation Coming Soon - Default DB2 Migration Workflow z/OS cloud provisioning demo planned for upcoming zBLC DB2 will generate the default workflow artifacts through an updated DB2 migration CLIST – Workflow definition file – Set of new JCL templates as the z/OSMF artifacts used for DB2 11 migration – Workflow variable file DB2 users can edit the sample workflow input variable file with the input for a specific DB2 subsystem Workflow can be customized by adding/removing/changing steps zOSMF to support REST calls over workflows to enable cloud-based solutions such as automated test/dev configuration 40 © 2015 IBM Corporation DevOps: Continuous delivery for the mainframe New capabilities speed delivery of interdependent, multi-platform applications DB2 Plugin, available Dec. 2014 IBM UrbanCode Deploy for z/OS Deploy Test Environment – RD&T Application under test • Download build output from artifact repository on z/OS • Deploy to z/OS or RD&T to test application changes 41 • Unified solution for continuous delivery of heterogeneous enterprise applications • Accelerate delivery and reduces cycle time to develop/test multi-tier applications across heterogeneous environments and platforms • Reduce costs and eliminate delays for delivering mainframe applications • Minimize risk and improve productivity across disparate teams with cross-platform release planning © 2015 IBM Corporation Bring Analytics to the Data, Reduce ETL System z Host: Analytics: Predictive & Descriptive Orchestration System z network Converge analytics with data and transaction environments where data exists on the platform, and actively encourage the growth of data hosted on System z. network DATA Transaction Data Business Rules Customer Data Account Data Payment Data Claims Data WHY? • System z already hosts a majority of the systems of record data that feeds business analytics System z Host: System z Key Characteristics Orchestration • Unparalleled, proven performance execution of combining transactional data environments, analytics & rules DATA Analytics: Descriptive & Predictive • Extensive logging for data and transaction governance Transaction Data Business Rules • Existing comprehensive, tested HA / DR capabilities Customer Data • Cost savings from reduced systems, software & people Account Data • Best of breed security for sensitive data 42 • Plug and play capacity extensions and migrations enabling quick production application deployment with no downtime Payment Data Claims Data © 2015 IBM Corporation In-Database Analytics in DB2 for z/OS SPSS Modeler Server Scoring Adapter for z Systems Execute predictive models inside DB2 for z/OS, with little data movement – Scores executed 10-50x faster than making calls to remote scoring engines – Achieve huge scale of execution without performance degradation – Leverage historical and current transaction data to produce most accurate results – Plans to exploit z13 SIMD for higher performance – Exploits DB2 UDF enhancements Co-locate analytics with data! System z SPSS Scoring Performance System z models Available System z business solutions that use this technology: IBM System z Smarter Analytics for Banking anti-fraud and anti-money laundering focus IBM Signature Solution anti-fraud, waste and abuse for Healthcare and Insurance IBM Signature Solution anti-fraud, waste and abuse for Tax IBM Signature Solution for Next Best Action 43 Execute thousands of model scores per second with very high scale © 2015 IBM Corporation DB2 Analytics Accelerator Roadmap • • • • • IDAA VNext •Advanced Analytics •HA/DR Support Enhancing current capabilities Enabling more query acceleration Increasing transparency Supporting new use cases Utilizing PDA technology advantages IDAA VNext •In-database transformation •Acceleration of Predictive Analytics applications •Online Schema change IDAA V2.1 (11/2011 •Netezza acceleration Consolidation and unification of transactional and analytical data stores 2015 IDAA V4.1 (11/2013) •Static SQL •Workload balance IDAA V3.1 (11/2012) •Incremental Update •Storage Saver Advanced Analytics Unified Store Modeling, batch scoring, Hadoop, Streams integration ELT Accelerator 2012/2013/2014 Storage Saver Accelerating indatabase transformation IDAA V4.1 PTF 5 •In-database transformation •Incremental Update Turbo IDAA V4.1 PTF 4 (10/2014) •Encryption of data at rest •Call Home ISAO V1 2011 Sophisticated multi-temperature data and archiving solution Query Accelerator 44 © 2015 IBM Corporation Operations and analytics coexistence: results Thousands of complex, analytical queries now integrated with operational workload First use case: periodic data synchronization – End-ofBusiness Day data access Second use case: (near-) real time data access LPAR 2 LPAR 1 Accelerator Baseline w/Analytics Operational throughput maintained with no additional mainframe capacity 45 Baseline w/Analytics Data kept in sync real-time with minimal degradation in transaction ITR (3%) © 2015 IBM Corporation DB2 11 Expanded Analytics Capabilities Significant query performance improvements – Without accelerator SQL Grouping Sets, including Rollup, Cube – Rollup is helpful in providing subtotaling along a hierarchical dimension such as time or geography – CUBE is helpful in queries that aggregate based on columns from multiple dimensions High performance SPSS in-database scoring via PACK/UNPACK (rolled back to v10) Hadoop access via table UDF – UDFs shipped with BigInsights – Uses new V11 generic table UDF capability JSON support 46 © 2015 IBM Corporation Relative performance Upgrade to DB2 11 for z/OS to achieve more operational analytics throughput for the same cost 3,326 Reports per Minute 4,267 Reports per Minute DB2 10 DB2 11 zEC12 zEC12 IBM internal analytics workload (BI Day) Workload consisted of 160,860 Cognos BI Day simple reports. Both tests used 10 CPs and ran at 100% utilization. Results may vary based on customer workload profiles/characteristics. 47 © 2015 IBM Corporation DB2 11 and Analytics What makes DB2 11 enhancements Analytics specific? –Analytics queries often involve • • • • More tables in a query – joins, subqueries, table expressions etc More complex expressions (in WHERE clause or select list) More rows being processed when compared with OLTP More rows joined, sorted etc Does DB2 11 only benefit analytics? –No, performance improvements are relevant to all workloads • Although greater opportunities for performance improvements exist in more complex workloads 48 © 2015 IBM Corporation Integrating Big Data Analytics with DB2 for z/OS • Much of the world’s operational data resides on z/OS Unstructured data sources are growing fast Two significant needs: 1. Merge this data with trusted OLTP data from zEnterprise data sources 2. Integrate this data so that insights from Big Data sources can drive business actions New V11 features enable this Connectors to allow BigInsights to easily & efficiently access DB2 data DB2 is providing the connectors & the DB capability to allow DB2 apps to easily and efficiently access hadoop data sources Integrate IBM BigInsights • New user-defined functions and V11 generic table UDF capability • IMS and DB2 JDBC connectors Merge 49 © 2015 IBM Corporation InfoSphere BigInsights for Linux on System z - 2.1.2 50 Secure Perimeter © 2015 IBM Corporation InfoSphere System z Connector for Hadoop Easy to use ingestion engine Native data collectors accessed via graphical interface Light-weight; no programming required Multiple z/OS data sources Conversions handled automatically Streaming technology leverages USS (no z/OS engines) with no DASD required for staging A secure pipe for data RACF integration – no need for separate or special credentials Data streamed over secure channel using hardware crypto Combining with BigInsights for Linux on System z means data never leaves the box Mainframe efficiencies 51 © 2015 IBM Corporation DB2 Cypress Themes Performance Expanded in-memory processing CPU reductions expected for most workloads Faster insert and easier insert performance mgmt DBA productivity, autonomics More schema and partition flexibility Stay ahead of mobile, internet-of-things: Extreme scale tables, indexes Streamlined and less disruptive migrations Self-optimizing system – More transparent SQL optimization – Easier management, higher availability for very large tables/indexes Application enablement Cloud-based data or database as-a-Service provisioning SQL improvements for improved analytics, application porting, and developer productivity 52 © 2015 IBM Corporation Jeff Josten ([email protected]) 53 © 2015 IBM Corporation