Active-Active Zero Downtime and Unlimited Scalability with DB

Active-Active Zero Downtime and Unlimited Scalability with DB 2 pure. Scale Daniel Ling Senior Solution Specialist Information Management Software, IBM HK

Now everything Oracle got on database, DB 2 has it and works better Oracle DB 2 Oracle Enterprise Edition DB 2 Enterprise Edition Oracle Range Partitioning DB 2 Table Partitioning Oracle Standard Edition DB 2 Workgroup Edition No Offer DB 2 Database Partitioning Oracle Standard One Edition DB 2 Express Edition Oracle Advance Compression DB 2 Row Compression Oracle Times Ten In. Memory database DB 2 Solid DB In-Memory database Oracle Label Security DB 2 Label Security Oracle XML DB 2 Pure XML database Oracle Database and Audit Vault DB 2 Guardium database security monitoring Oracle Lite DB 2 Everyplace Oracle Enterprise Manager DB 2 Performance Optimization Feature Oracle Automatic Memory Management DB 2 Self-Tuning Memory Manager Oracle Automatic Storage Management DB 2 Automatic Storage Oracle Data Masking DB 2 Optim Data Privacy Oracle Real Application Testing DB 2 Optim Test Data Management No Offer DB 2 Optim Data Archiving Resilience and HA Oracle RAC (active-active share disk) DB 2 Pure. Scale (active-active share disk) Oracle Data Guard DB 2 HADR (read on standby) Oracle Stream DB 2 Queue Replication …. . …… Tools

Database High Availability Options

Server Based Failover - i. e. most OS clustering tx tx Integrated with Tivoli System Automation cluster manager (included in both DB 2 Enterprise and DB 2 Workgroup without charge ) Active Server - Node Failure Detection - Disk takeover - IP takeover - Restart DB 2

DB 2 (HADR) – database log shipping HA Redundant copy of the database to protect against site or storage failure Support for Rolling Upgrades Failover in under 15 seconds – Real SAP workload with 600 SAP users – database available in 11 sec. 100% performance after primary failure Included in DB 2 Enterprise and DB 2 Workgroup without charge Automatic Client Reroute Client application automatically resumes on Standby • tx tx Network Connection tx tx • • HADR – Syn and Asyn • Keeps the two servers in sync Standby Server Primary Server DB 2 High Availability Disaster Recovery (HADR) enables highly available database standby Fail over in minute DB 2 & MSSQL (2 modes) Standby Server DB 2 ( 3 modes ) – Semi syn – Min delay in performance – Assure integrity

Critical IT Applications Need Reliability and Scalability Local Databases are Becoming Global – Successful global businesses must deal with exploding data and server needs – Competitive IT organizations need to handle rapid change Customers need a highly scalable, flexible solution for the growth of their information with the ability to easily grow existing applications Down-time is Not Acceptable – Any outage means lost revenue and permanent customer loss – Today’s distributed systems need reliability

Introduce DB 2 pure. Scale (Active-Active share disk) • Unlimited Capacity – Buy only what you need, add capacity as your needs grow • Application Transparency – Avoid the risk and cost of application changes • Continuous Availability – Deliver uninterrupted access to your data with consistent performance

DB 2 pure. Scale Architecture Automatic workload balancing Cluster of DB 2 nodes running on Power servers Leverages the global lock and memory manager technology from z/OS Integrated Cluster Manager Infini. Band network & DB 2 Cluster Services Shared Data Now available on -AIX Infini. Band - Intel Infini. Band -Intel Ethernet - AIX Ethernet target 2 Q 11

The Key to Scalability and High Availability • Efficient Centralized Locking and Caching – As the cluster grows, DB 2 maintains one place to go for locking information and shared pages – Optimized for very high speed access • DB 2 pure. Scale uses Remote Direct Memory Access (RDMA) to communicate with the power. HA pure. Scale server • No IP socket calls, no interrupts, no context switching • Results – Near Linear Scalability to large Member 1 numbers of servers – Constant awareness of what each member is doing • If one member fails, no need to block I/O from other members • Recovery runs at memory speeds CF Group Buffer Pool Group Lock Manager Power. HA pure. Scale

Recover Instantaneously From Node Failure - Using RDMA (Remote Direct Memory Access) Protect from infrastructure related outages – Redistribute workload to surviving nodes immediately – Completely redundant architecture – Recover in-flight transactions on failing node in as little as 15 seconds including detection of the problem Application Servers and DB 2 Clients

Minimize the Impact of Planned Outages • Keep your system up – During OS fixes – HW updates – Administration Bring node Do Maintenance Identify Member back online

Online Recovery • • DB 2 pure. Scale design point is to maximize availability during failure recovery processing When a database member fails, only inflight data remains locked until member recovery completes – In-flight = data being updated on the failed member at the time it failed Time to row availability – <20 seconds DB 2 Log CF DB 2 Log Shared Data DB 2 Log CF Database member failure Only data in-flight updates locked during recovery % of Data Available • 100 50 Time (~seconds)

Compare to traditional technology – data availability DB 2 pure. Scale 1 Oracle RAC Shared Disk 1 Member failure In-flight Transactions Survivors can get locks throughout 2 2 a Node failure In-flight Transactions Global Lock State 3 a Global Lock State CF Hot pages 2 Recovery performed on another host Database member failure This example assumes about 5% of the database data was being updated on the database node that failed, at the time of the failure. % of Data Available 2 a CF services most page requests from memory 2 3 3 2 Lock remaster/rebuild Lock requests frozen until lock state ‘rediscovered’ from log of failed node Another machine performs recovery 3 a More random disk I/Os needed pure. Scale: only data that was being updated on failed database member is (temporarily) locked 100 Oracle RAC Shared Disk: partial or full “freeze” for global lock state re-master/re-build Time (~seconds)

Automatic Workload Balancing and Routing • Run-time load information used to automatically balance load across the cluster (as in Z sysplex) – – Load information of all members kept on each member Piggy-backed to clients regularly Used to route next connection or optionally next transaction to least loaded member Routing occurs automatically (transparent to application) • Failover: load of failed member evenly distributed to surviving members automatically – Once the failed member is back online, fallback does the reverse • Affinity-based routing, and failover and fallback also possible Clients Log Transaction logs Shared Data

Reduce System Overhead by Minimizing Inter-node Communication DB 2 pure. Scale’s central locking and memory manager minimizes communication traffic Other database software require CPU intensive communication between all servers in a cluster DB 2 pure. Scale grows efficiently as servers are added Other database software waste more and more CPU as they grow

The Result 128 Members 84% Scalability 112 Members 89% Scalability 2, 4 and 8 Members Over 95% Scalability 16 Members Over 95% Scalability 88 Members 90% Scalability 64 Members 95% Scalability 32 Members Over 95% Scalability Validation testing includes capabilities to be available in future releases.

IT Needs to Adapt in Hours…Not Months Handling Change is a Competitive Advantage Dynamic Capacity is not the Exception – Over-provisioning to handle critical business spikes is inefficient – IT must respond to changing capacity demand in days, not months Businesses need to be able grow their infrastructure without adding risk Application Changes are Expensive – Changes to handle more workload volume can be costly and risky – Developers rarely design with scaling in mind – Adding capacity should be stress free

DB 2 now has built in Oracle Compatibility Oracle DBMS DB 2 9. 7 Oracle Concurrency Control No change Oracle SQL No change Oracle PL/SQL No Change Oracle Packages No Change Oracle Built-in packages No Change Oracle JDBC No Change Oracle SQL*Plus Scripts No Changes are the exception. Not the rule. THIS IS WHY WE CALL IT ENABLEMENT AND NOT PORT ! PL/SQL = Procedural Language/Structured Query Language

Concurrency prior to DB 2 v 9. 7 • Oracle default / DB 2 9. 7 default – Statement level snapshot blocks Reader Writer Reader No No Writer No Yes • DB 2 before 9. 7 – Cursor stability blocks Reader Writer Reader No No* Writer Yes * In default isolation DB 2 keeps no rows locked while scanning Enabling Oracle application to DB 2 required significant effort to re-order table access to avoid deadlocks

SQL Procedure Language (SQL PL) enhancements • Advancements in DB 2 PL/SQL • New SQL, stored procedures and triggers PL/SQL COMPILER PL/SQL (Procedural Language/Structured Query Language) is Oracle Corporation's procedural extension language for SQL and the Oracle relational database. PL/SQL's general syntax resembles that of Ada. (extended from Pascal ) : source: www. wikipedia. org 21

DB 2 now allow both Shared-disk or Shared-Nothing scale out design -Shared-Nothing DB 2 Database Partitioning Feature Best for Data Warehouse Balance each node with dedicated CPU, memory and storage -Shared-Disk Best DB 2 pre. Scale Feature for Transaction Balance CPU node with shared disk and memory

Parallel Processing Across Data Modules Partitioned Database Model Database is divided into multiple partitions Partitions are spread across data modules Each Partition has dedicated resources – cpu, memory, disk Parallel Processing occurs on all partitions and is coordinated by the DBMS Single system image to user and application Corporate network SQL Foundation Small tables part part large tables part part part 10 GB Ethernet

Parallel Query Processing select sum(x) from table_a, table_b where a = b connect 46 Sum Optimize Join Read A Coord sum(…) Catalog Read B sum=10 Agent sum=12 Agent Sum Part 1 table_a table_b sum=13 sum=11 Agent Sum Join A Get statistics Join B Part 2 A Agent Sum Join B Part 3 A Join B Part. N A B

Automatic Data Distribution Insert/Load DISTRIBUTE BY HASH (trans_id) CREATE TABLE sales(trans_id, col 2, col 3, …) DISTRIBUTE BY (trans_id) Table Partition 1 Partition 2 Database Partition 3

Hash Partitioning – “Divide and Conquer” • With IBM’s DB 2 w/Data Partitioning Feature (DPF), the query may still read most of the data, but now this work can be attacked on all nodes in parallel. P 1 P 2 P 3 26

Range (Table) Partitioning Reduces I/O • SELECT NAME, TOTAL_SPEND, LOYALTY_TIER from CUSTOMERS where REGION= MONTH=‘Mar’ P 1 P 2 and P 3 Jan Feb Mar 27

Multi Dimensional Clustering Reduces I/O • I/O problem solver • With MDC, data is further clustered by multiple attributes • Now even less I/O is done to retrieve the records of interest • Less I/O per query leads to more concurrency P 1 P 2 P 3 Jan Feb Mar 28