IBM Software Group DB 2 data server technology

SWG At A Glance SWG Delivers the Highest Gross Profit to the IBM Business Ø SWG Contributed 20% Of IBM’s 2006 Revenue, $18. 2 B Ø SWG Contributed 40. 5% Of IBM’s Gross Profits Ø Middleware Is Made Up Of Five Key Brands Which Provide An Integrated Suite For IBM Customers “ The Company’s Information On Demand Portfolio Builds Value Around The Company’s Core Database Business. These Offerings Grew Over 50 Percent Year-to-Year In The Third Quarter. ” Source: SEC FORM 10 – Q Period Ending September 30 th, 2006 2 IBM Confidential © 2007 IBM Corporation

IBM SWG at a Glance - Our Five Brands IBM Ranked #1 Or #2 In All Middleware Segments Ø Information On Demand § Over 450, 000 Information Management Customers § 13, 000 Business Partners; 40, 000 ISV Applications Ø SOA / Business Flexibility § 103, 000 + Web. Sphere Customers § 1. 1 + Million Web. Sphere Developers Ø People Productivity § Over 125 Million Lotus Notes Client Licenses Sold § Over 15 Million Business Instant Messaging Users § 2. 5 Million Workplace Software Users Ø IT Service Management § 22, 000 Tivoli Systems Management Customers § 60 Million User Licenses Of Tivoli Access Manager Ø Business Driven Development § 600, 000+ Users Of Rational Software § More Than 18 Million Eclipse Downloads 3 IBM Confidential © 2007 IBM Corporation

The Need for Information On Demand Complexity demands a dynamic architecture Applications People and Processes Applications Open Standards Flexible Architecture Warehouses Database 4 Database IBM Confidential Documents © 2007 IBM Corporation

DB 2 Data Servers Best of breed database for all of your needs DB 2 Simplified and automated operational management Secure Hands Free Rapid Development Lowered TCO Securely store and govern data for compliance Let DB 2 dynamically manage resources for optimal performance Leverage the best languages for your Web Services & applications Automation and Deep Compression help you save time and money DB 2 helps you regain control of all your costs – Hardware, Storage and Staff Time Optimized for all Operating Systems System z 5 System i AIX Linux IBM Confidential HP Solaris Windows © 2007 IBM Corporation

Why Build your Solution with DB 2? § Fast and Flexible Development – Industry leading support for development tools – Application development freedom with Open Standards – Power of pure. XML § Simple integration – Broadest platform support – Easy embedding – Hands off administration § Great customer value – Deep Compression – Integrated workload management 6 IBM Confidential © 2007 IBM Corporation

A New Model is Emerging Familiar Programming, Optimized Storage, Mature Services Familiar Programming Models XML Developer “I see a sophisticated XML repository that also supports SQL. " Mature Services SQL Developer "I see a sophisticated RDBMS that also supports XML. " DB 2 Optimized Performance & Scale Optimized Storage Models 7 Familiar Tooling IBM Confidential © 2007 IBM Corporation

XML in DB 2 § "Feels" relational and/or XML § Both SQL flavor and fully XML flavor § XML *is* DB 2 internals - XML Extender becomes one with the data engine SERVER CLIENT Data management client Customer client application 8 SQL(X) XQuery Relational Storage Relational Interface DB 2 Server XML Interface IBM Confidential XML Storage © 2007 IBM Corporation

DB 2 9: Early Adoption Feedback: Storebrand Financial services business results with XML-base SOA § Mortgage application process that took 4 weeks now takes 1 minute § In 2006 will process over 10 x the amount of client requests as in 2005 Prototype results using DB 2 9 pure. XML data server Development and app. performance re: XML data with DB 2 9. 5 hybrid data server Development of search & retrieval business processes CLOB: 8 hrs 30 min. Add field to schema 1 week 5 min. Relative lines of I/O code (65% reduction) 100 35 Queries 24 - 36 hrs 20 sec - 10 min Query non-shredded XML element 9 with relational data server 1 week ½ day Shred: 2 hrs IBM Confidential © 2007 IBM Corporation

Information Fidelity Integration Schema Flexibility Performance/Scale Programming Models Manageability 10 IBM Confidential Hybrid Stand-alone CLOB Shred DB 2 9 pure. XML: Powering an XML based SOA = = © 2007 IBM Corporation

Software as a Service with DB 2 “IBM Moves the Database Goalposts…”, Phil Howard, Bloor Research § Lower Development Costs – – – Reduce code and development complexity Improve developer productivity Leverage new technologies such as JLINQ or ARROW for quick development Hybrid Capabilities Flexible Infrastructure § XQuery and/or SQL § XML and/or Relational Data Speed solution development and gain cost savings § Performance & Scale, Availability, & Security… Greater Business Agility – – Easily accommodate changes to data and schemas Update applications rapidly and reduce maintenance costs XML Databases Respond quickly to dynamic conditions XML RDB Columns and get faster time to value § Improved Business Insight – – Access “hidden gems” (data) in unexploited documents Realize unprecedented application performance Query Flexibility Gain competitive advantage via higher quality information delivered at higher speeds 11 Complex Schema Evolution Sequences & Hierarchies Content Repositories Relational Databases Hierarchical Application Databases Independence Digitized Content XML Documents, Interchange Files IBM Confidential © 2007 IBM Corporation

Application Development Freedom: Choose your environment and language § Create applications and Web services with ease § Superior application development capabilities § Comprehensive tools integration § Updated! Application portability Updated! New JLINQ 12 IBM Confidential © 2007 IBM Corporation

pure. Query – Agile Java development § Increase developer productivity – Integrated content assist • Java and Queries – Query results as object collections § Flexible data access – programming styles • Inline or annotated – data access - queries • SQL or XQuery – deployment and runtime • dynamic or static 13 IBM Confidential © 2007 IBM Corporation

Java Application Development Enablement § Description: – Support application development and execution for the Java platform § Provide the ability to … – Update JDBC standard level – Improve on XML and SOA tooling support – Improve Java capabilities for all servers § Key technology components: – Python server support and IDS integration – JDBC 4 Compliance – Integrated Xquery builder and SOA web services tooling support – Integrate new unified Visual Explain 14 IBM Confidential © 2007 IBM Corporation

Non-Java Application Development Enablement § Description: – Support application development and execution for the Microsoft and Open Source specific models § Provide the ability to … – Extend support for Open Source programming languages – Improve. NET capabilities for all servers § Key technology components: – 64 bit. NET enablement and support for all servers – Support new server data types – Ruby on Rails support and PHP enhancements – Python server support – Integrated Xquery builder and support for SOA 15 IBM Confidential © 2007 IBM Corporation

DB 2 is everywhere… § DB 2 supports more operating systems and hardware platforms then any other vendor The freedom of Linux System z Don’t let your database partner limit what you can offer your customer 16 IBM Confidential © 2007 IBM Corporation

Simple to Install and Embed § Installs in a few minutes § Customizable install image to ensure DB 2 is setup and tuned perfectly out of the box for your application § Great for embedding – Silent install using response file – Deploy with your optimized configuration – Windows Installer Merge Modules for point-and-click embedding 17 IBM Confidential © 2007 IBM Corporation

Free yourself from the Daily Grind of Maintaining a Database § Let DB 2 tune itself so you don’t have to worry about the database § Easily tunes for optimal configuration – Self-tuning Memory – Configuration Assistant – Wizards and Advisors § Simple to maintain and manage – On-demand Storage – Automated Maintenance Tasks – Health Monitor Less Skill, Less Resource, Better System Utilization 18 IBM Confidential © 2007 IBM Corporation

Adaptive Self-Tuning Memory § DB 2 9 will introduce a revolutionary memory tuning system called the Self Tuning Memory Manager (STMM) – Works on main database memory parameters • Sort, locklist, package cache, buffer pools, and total database memory – Hands-off online memory tuning • Requires no DBA intervention – Senses the underlying workload and tunes the memory based on need – Can adapt quickly to workload shifts that require memory redistribution – Adapts tuning frequency based on workload 19 IBM Confidential © 2007 IBM Corporation

STMM in Action – Dropping an Important Index TPCH Query 21 - After drop index - Average times for the 10 streams 7000 Avg = 6205 Time in seconds 6000 Reduced 63% 5000 Indexes Dropped 4000 Avg = 2285 3000 2000 Avg = 959 1000 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 Order of execution 20 IBM Confidential © 2007 IBM Corporation

STMM in Action – Comparing Different Configurations 70000 STMM also edges 63302 out benchmark 63796 tuned system Transactions Per Minute 60000 50000 40000 30000 20000 STMM beats default configuration by nearly 4 x 16713 10000 0 Default (No tuning) 21 Benchmark Tuned Configuration IBM Confidential STMM Tuned © 2007 IBM Corporation

DB 2 Keeps Running Under any Workload Second database stopped § Let DB 2 Tune Itself - Configuration Advisor and the Design Second database started Advisor sets up DB 2 for you based on simple questions - Self Tuning Memory Manager (STMM) keeps you running at peak performance § Let DB 2 react for you DB 2 adapts to its environment to ensure your application is always running at its best Without DBA intervention it can reduce or add memory as needed 22 IBM Confidential © 2007 IBM Corporation

Integrated Workload Management § DB 2 9. 5 2 introduces Workload Management in the database engine – DBAs can now classify and control priority and resource usage of applications and users – Controls can be created on the fly or planned ahead of time § Request management – Manage work based on its business priority, that is its urgency and importance – Support progress indication and interruption for long running operations § Resource management – Allocate limited resources (CPU, IO, etc. ) among multiple requestors based on business need – Limit excessive resource consumption by individual requestors 23 IBM Confidential © 2007 IBM Corporation

Workload Management and Monitoring Foundation § Query Patroller supported as in DB 2 9 but evolving to integrated Work Load Management § Introduce concept of a Service Class, Workload, Work Action Set, Concurrency and Database Activity Limits, into DB 2 – Service Class • User defined entity that acts as a point of resource control and activity monitoring for a set of database activities – Workload • User defined entity used to identify and manage a set of work – Work Action Sets • A user defined entity that is used to discriminate between database activities forservice class mapping and activity limit assignment – Thresholds • Control can be handled both proactively and reactively – Concurrency Thresholds – Database Activity Thresholds 24 IBM Confidential © 2007 IBM Corporation

Customized Execution Environment DB 2 AIX WLM Service Classes User Requests Superclass 1 Workload A Subclass A _DB 2_SUPERCLASS 1 Workload B Subclass B _DB 2_SUBCLASSA Workload C _DB 2_SUBCLASSB Workload D Default User Class _DB 2_DEF_USER Default workload System Requests 25 Default System Class IBM Confidential _DB 2_DEF_SYSTEM © 2007 IBM Corporation

Workload Management and Monitoring Foundation § Monitoring and Control – New table functions to allow quick ad hoc access to internal information via SQL from service classes and workload occurrences • Can provide statistics and current on-going activities from one or more database partitions with one invocation – New event monitors • to allow capture of detailed information of SQL statements from database or service classes • to allow capture of detailed information of SQL statements from activity limits (via CAPTURE key word) • to allow capture of service class statistical information at regular intervals – New stored procedures • To cancel a database activity • To capture detailed information on a database activity (i. e. SQL statement) 26 IBM Confidential © 2007 IBM Corporation

Examples of New Monitoring Capabilities DB 2 User Requests Superclass 1 Workload A Subclass A SQL using Table Functions Workload B Subclass B Workload C Workload D Default workload System Requests 27 Default User Class Aggregate Activity Information Default System Class Activity Information IBM Confidential © 2007 IBM Corporation

Use DB 2 to reduce your Total Cost of Ownership § Keep your Development Costs Low – DB 2 tooling and flexibility reduces your development costs and its automation helps reduce your customers’ costs – Keep your time to release low with DB 2’s flexibility • Biggest range of programming language supports allows you to use the one that is right for the job, not what you’re restricted to § Keep your Administration Costs Low – Leverage DB 2’s wizards and automation to get even a rookie employee working like a pro in a day – Stable, secure database keeps your staff and your customers working on your application – rather than hunting down vendor security issues § Keep your TCO to the Customer Low – Leverage Deep Compression • • 28 Reduce the total cost of your application by minimizing storage costs No work is needed in the application to leverage DB 2 compression No one matches DB 2’s level of compression “With DB 2 9, we’re seeing compression rates up to 83% on the Data Warehouse. The projected cost savings are more than $2 million initially with ongoing savings of $500, 000 a year. Michael Henson, DB 2 Unix Team Lead, Database Delivery Services, Sun. Trust Banks, Inc. IBM Confidential © 2007 IBM Corporation

Dictionary Compression Concepts § Dictionary-based symbol table for compressing/decompressing data records – Lempel-Ziv (LZ) based algorithm (static dictionary) utilizing 12 bit symbols – Dictionary per table stored within the permanent table object (~75 KB in size; disk+memory) § Data resides compressed on pages (both on-disk and in bufferpool) – Significant I/O bandwidth savings – Significant memory (bufferpool) savings – CPU costs • Rows must be decompressed before being processed for evaluation § Log data from compressed records in compressed format § Rows where no storage saving is realized do not get compressed. 29 IBM Confidential © 2007 IBM Corporation

Dictionary Compression Concepts cont … § Repeating patterns within the data (and just within each row) is the key to good compression. § Text data tends to compress well because of reoccurring strings as well as data with lots of repeating characters, leading or trailing blanks Fred 500 10000 Plano TX 24385 John 500 20000 Plano TX 24385 01 Fred (01) 10000 (02) John (01) 20000 (02) … 02 … 30 IBM Confidential … Dept 500 Plano, TX 24385 … © 2007 IBM Corporation

Automatic Dictionary Creation in DB 2 9. 5 31 IBM Confidential © 2007 IBM

Performance – Sample Warehouse Queries (I/O Bound System) Overall 29% performance improvement. 32 IBM

More Compression Ratios (Customer Data) Compression Type 32 KB Page Count Space Required on Disk No compression 5893888 179. 9 GB Row compression 1392446 42. 5 GB % Pages Saved: 76. 4% 33 IBM Confidential © 2007 IBM Corporation

Table Partitioning § What is Table (Range) Partitioning ? – Storing a table in more than one physical object, across one or more table spaces – Each table space contains a range of the data that can be found very efficiently § Why? – Increase table capacity limit – Increase large table manageability – Improve SQL performance through partition elimination – Provide fast & online data roll-in and roll-out – Family compatibility with DB 2 on z. OS and IDS 34 IBM Confidential © 2007 IBM Corporation

Table Partitioning Backup Load Recover 64 G 64 G A-Z 1 64 G A-C Backup Load Recover D-M Backup Load Recover N-Q Backup Load Recover R-Z Backup Load Recover Backup, Load, Recover 2 Detach Attach 64 G 1 Q 2006 DETACH 64 G 64 G 2 Q 2006 3 Q 2003 4 Q 2006 1 Q 2007 ATTACH LOAD 35 IBM Confidential © 2007 IBM Corporation

Hybrid Partitioning Database Partitioning 999 Machines 32 K Partitions 64 G A-C 64 G

Integrated Audit and Security § Let DB 2 take care of the compliance and security needs of both You and Your Customers § Enhancements to Auditing and Security – DB 2 give You and Your Customers control over critical information of who, when and from where are people accessing DB 2 • • • Who: tracking of all connections and authorizations What: statement text and DDL tracking Where: application id and tcpip of originating request When: timestamp capture of key events How: record all authorities checks for database actions Why: that’s for the auditors to find out… § Identity Assertion and Roles Big Brother is Watching Executive Board Customers Government Financial Commissions Consumer Groups – Easily group and track your users through an N-tier architecture – Keep your application architecture simple and secure without having to re-write your code 37 IBM Confidential © 2007 IBM Corporation

Security - Label Based Access Control § Label Based Access Control (LBAC) – A “label” is associated with both user sessions and data rows or columns – Rules for comparing users and data labels provide allow access controls to be applied at the row level § Labels may consist of multiple components – Hierarchical, set or array or types – Row labels appear as a single additional column in a protected table, regardless of the number of label components – User labels are granted by a security administrator § Similar to the label security support in DB 2 for z/OS v 8 38 IBM Confidential © 2007 IBM Corporation

Granular Protection – Complete Control! 39 IBM Confidential © 2007 IBM Corporation

Security - Roles § Definition and benefits § Role – A role is a database object that may group together one or more privileges and can be assigned to users, groups, PUBLIC or to other roles via a GRANT statement. § Benefits – DBAs can control access to their database at a level of abstraction that is close to the structure of the organization. (ie. Manager, HR, Employee) – Users are granted membership in roles based on job responsibilities. As responsibilities change the users are granted different roles. – The assignment and maintenance of privileges is simplified. • • • 40 Role increased responsibility User role changes New hires, employees leaving IBM Confidential © 2007 IBM Corporation

Simple High Availability § Add High Availability to your Legacy applications • • Easy to configure High Availability and Disaster Recovery without having to re-write your application with DB 2 HADR Easy to add AFTER you write your application without any coding changes Let DB 2 worry about a customer’s High Availability needs No specialized hardware / storage – minimal cost of only one additional CPU Setup in minutes – Graphical Wizard Allows ultra-fast failover (seconds) Systems can be in different georgraphic locations § What do I get with HADR? • • Ability to manage planned and unplanned outages Seamless DB 2 and system upgrades, just swap the primary and standby, then swap back with a click of a mouse t x Multiple levels of HA to match your customers HA needs – without any extra complexity Automatic client re-routing t x Network Connection HADR Keeps the two servers in sync Primary Server 41 IBM Confidential t x Standby Server © 2007 IBM Corporation

High Availability Disaster Recovery (HADR) § Target Market Portland Toronto 4 Online commercial applications § Challenge HADR 4 24 x 7 Availability 4 Failover in seconds 4 Disaster recovery HTTP & App. Servers § Solution : HADR 4 Business continuation Standby Database Server Standby HTTP & App. Servers Offsite Disaster Recovery 4 Single solution handles • Ultra-fast failover • Local and remote site recovery § Value Primary Database Server Automatic Reroute HAD Client Application Server 4 Tight integration; Very simple to use R Standby Database Server Primary Database Server Onsite Hot Standby 42 IBM Confidential © 2007 IBM Corporation

IBM Data Studio A Complete End to End Solution Application Developer Database Developer Deploy Develop Other Develop • Coding • Debugging • Teaming • Testing • Tuning Design • Data Modeling • Process Modeling • Business Modeling Eclipse Shared Database Administrator Manage • Database Administration • Data Management • Change Management • Recovery Management • Storage Management • Performance Management Web Manage Design IBM Govern • Security Access • Security Analysis • Data Auditing • Data Archiving • Data Masking • Data Encryption Govern Business Analyst 43 Data Architect Security Administrator IBM Confidential © 2007 IBM Corporation

IBM Data Studio Administration Console § What is it ? – – A web based interface to perform operational database administration tasks Immediate access to critical data server information and functions from anywhere, anytime § What’s available in the first release – – Health and availability monitoring • DB 2 for LUW V 9. 5 – Problem determination – Problem recommendations – 72 Hours of History – Ability to monitor up to 100 data servers Q Replication monitoring and administration § What will be available in the future? – – 44 Support for all IBM data servers – IDS and DB 2 on all platforms The future home for commonly performed operational data management functions • Performance Monitoring • Performance Tuning • Configuration Management • Storage Management • Advanced Deployment IBM Confidential © 2007 IBM Corporation

Quick & Easy Problem Determination Dashboard – Adhoc Investigation Heatchart – Overall Health Status Where are the most important hotspots that need my attention? Something doesn‘t seem quite right. I wonder what‘s happening? Administrator Recommendations – Root Cause Analysis Alert List – Historical Investigation „What happened when I was out for lunch? . . . Away for weekend? 45 Guide me to the root cause and help me fix it properly; I need to know all the revelant info to make the best decision. IBM Confidential © 2007 IBM Corporation

DB 2: Then, now, and what’s to come Agile Develop & Manage with Ease DB 2 9 2006 § Agile development with pure. XML § Scalability enhancements with range partitioning § Maximizing ROI with Deep Compression § OLTP performance leadership 46 DB 2 9. 5 2007 § Industry leading Deep Compression technology § Integrated Workload Management § Simplified memory and storage management § Easiest to use and most flexible XML support and XML performance § Advanced application development with support for virtually all programming languages § Flexible High Availability and Disaster Recovery IBM Confidential Development DB 2 Cobra 2009 § Fastest and most flexible XML support § Increasing Autonomics and System Automation § Easy database growth § Advanced application development § Dynamic database control driven by business needs * Subject to change © 2007 IBM Corporation

Opportunities § Try out DB 2 Express-C and the pure. XML and pure. Query technology on your machine ! ( http: //www. ibm. com/db 2/express ) § Check out the DB 2 tutorials on IBM developerworks (http: //www. ibm. com/developerworks/db 2 ) § Use DB 2 in your next project or study ! 48 IBM Confidential © 2007 IBM Corporation

IBM Software Group DB 2 Education and Resources IBM Confidential © 2007 IBM Corporation

Individual Reading § Reading Materials – online – www. ibm. com/software/data/db 2/udb/support/manualsv 9. html § Reading Materials – printed – (35% off voucher or online coupon, free shipping in Europe) – www. ibm. com/software/data/education/bookstore § Tutorials/White Papers – www. ibm. com/software/data/education/selfstudy. html § Porting to DB 2 – www. ibm. com/developerworks/db 2/zones/porting/ § Certification – www. ibm. com/software/data/education/certprep. html 51 IBM Confidential © 2007 IBM Corporation

Interactive Websites § IBMTV – www. ibm. com/software/info/television/en_us/index. jsp § Discussion/ Help forums – www. ibm. com/developerworks/forums § DB 2 Wiki – www. ibm. com/developerworks/wikis/display/DB 2+wiki § DB 2 9 Webcasts – www. ibm. com/software/data/db 2/9/webcasts. html § Voice of the DB 2 Community Podcast series – www. ibm. com/software/data/db 2/podcast/ § DB 2 Chat with the lab Series – www. ibm. com/software/data/db 2/9/labchats. html 52 IBM Confidential © 2007 IBM Corporation

Self Guided Hands on Learning – FREE Software!! Production Ready Innovative Technology Developer Community § Free to develop, deploy and redistribute* § pure. XML™ part of DB 2 Express-C 9 § Dedicated team for DB 2 community participation and development § No database size limit § No limit on number of instances per server § No user limit § No system memory limit ! § Same superior data server software technology and programming support as DB 2 Express Edition § Industry’s first hybrid relational and XML data server software § Skills and applications applicable to all editions of DB 2 § Download footprint ~275 MB www. ibm. com/software/data/db 2/express/ *Free registration required 53 § Free online community assistance IBM Confidential © 2007 IBM Corporation

Self Guided Hands on Learning – FREE Software!! § Application Development Tools – DB 2 Developer Workbench – DB 2 Information Center – DB 2 Client – DB 2 Net Search – Zend Core for IBM § www. ibm. com/software/data/db 2/ad/ 54 IBM Confidential © 2007 IBM Corporation