1 / 53

DB2 Version 9.1 Overview

DB2 Version 9.1 Overview. Keith E. Gardenhire keithgar@us.ibm.com. Objectives. DB2 Version 9.1 Packaging Partitioning Autonomic Computing Throttling Utilities XML LBAC. Oracle Oracle Lite Windows CE, Palm, EPOC, Windows 95/98/NT Oracle Personal Edition Windows

winston
Download Presentation

DB2 Version 9.1 Overview

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. DB2 Version 9.1 Overview Keith E. Gardenhire keithgar@us.ibm.com

  2. Objectives • DB2 Version 9.1 Packaging • Partitioning • Autonomic Computing • Throttling Utilities • XML • LBAC IBM Confidential

  3. Oracle Oracle Lite Windows CE, Palm, EPOC, Windows 95/98/NT Oracle Personal Edition Windows Oracle Standard Edition Windows CE, LINUX (2 dist.), AIX, HP-UX, HP Compaq, Solaris, Tru64 Oracle Enterprise Edition Windows CE, LINUX (2 dist.), AIX, HP-UX, HP Compaq, Solaris, Tru64 Oracle Real Application Clusters Windows CE, LINUX (2 dist.), AIX, HP-UX, HP Compaq, Solaris, Tru64 DB2 DB2 Everyplace Windows CE, Palm, EPOC, Windows XP/Tablet PC, QNX Neutrino, Symbian, Embedded LINUX DB2 Personal Edition Windows, LINUX (200 dist.) DB2 Express-C Windows, Linux DB2 Workgroup Server Edition Windows, LINUX (200 dist.), AIX, HP-UX, Solaris DB2 Enterprise Server Edition Windows, LINUX (200 dist.), AIX, HP-UX, Solaris, LINUX/390 Database Partitioning Feature optional purchase DPF Packaged FeaturesCompared IBM Confidential

  4. Partitioning

  5. Table Partitioning in DB2 LUW • Partition a table by range • Each range can be in a different tablespace • Ranges can be scanned independently • Use new ALTER ATTACH/DETACH statements for roll-in/roll-out JAN FEB MAR APR MAY JUN JUL AUG SEP IBM Confidential

  6. Jan Feb Jan Feb Jan Feb North South North South North South North South North South North South East West East West East West East West East West East West Data Placement - Grand Unification • Three ways to spread data • DISTRIBUTE BY HASH - currently in EEE/DPF • PARTITION BY RANGE – aka table partitioning • ORGANIZE BY DIMENSIONS – aka MDC Node 1 Node 2 Node 3 T1 Distributed across 3 database partitions Distribute TS1 TS2 TS2 TS1 TS1 TS2 Partition Organize IBM Confidential

  7. Defining Ranges (Long Syntax) • Use STARTING … ENDING … to specify ranges CREATE TABLE sales(sale_date DATE, customer INT, …) PARTITION BY RANGE(sale_date) (Partition Sales1Q2000 STARTING ‘1/1/2000’ in DMS01, Partition Sales2Q2000 STARTING ‘4/1/2000’ in DMS02, Partition Sales3Q2000 STARTING ‘7/1/2000’ in DMS03, Partition SalesEND STARTING ‘10/1/2000’ ENDING ’12/31/2004’ in DMS04); • Creates 4 ranges IBM Confidential

  8. Creating a Range Partitioned Table • Short and Long Forms • Partitioning column(s) • Must be base types (eg. No LOBS, LONG VARCHARS) • Can specify multiple columns • Can specify generated columns • Notes • Special values, MINVALUE, MAXVALUE can be used to specify open ended ranges, eg: CREATE TABLE t1 … (STARTING(MINVALUE) ENDING(MAXVALUE) … tbsp1 tbsp2 tbsp3 1 <= c1 < 34 34 <= c1 < 67 67 <= c1 <= 100 t1.p1 t1.p2 t1.p3 Short Form CREATE TABLE t1(c1 INT) IN tbsp1, tbsp2, tbsp3 PARTITION BY RANGE(c1) (STARTING FROM (1) ENDING( 100) EVERY (33)) Long Form CREATE TABLE t1(c1 INT) PARTITION BY RANGE(a) (STARTING FROM (1) ENDING(34)IN tbsp1, ENDING(67) IN tbsp2, ENDING(100) IN tbsp3) IBM Confidential

  9. Storage Mapping: Indexes are Global in DB2 9 • Indexes are global (in DB2 9) • Each index is in a separate storage object • By default, in the same tablespace as the first data partition • Can be created in different tablespaces, via • INDEX IN clause on CREATE TABLE (default is tablespace of first partition) • New IN clause on CREATE INDEX • Recommendation • Place indexes in LARGE tablespaces tbsp4 tbsp5 i1 i2 tbsp1 tbsp2 tbsp3 t1.p1 t1.p2 t1.p3 CREATE TABLE t1(c1 INT, c2 INT, …) IN tbsp1, tbsp2, tbsp3INDEX IN tbsp4PARTITION BY RANGE(a) (STARTING FROM (1) ENDING (100) EVERY (33))CREATE INDEX i1(c1)CREATE INDEX i2 (c2) IN tbsp5 IBM Confidential

  10. Adding New Ranges • Use ALTER ADD to add new ranges to an existing partitioned table ALTER TABLE sales ADD PARTITION STARTING ‘1/1/2001’ ENDING ‘3/31/2001’ IN TBSPACE1; • Creates a new empty range in TBSPACE1 IBM Confidential

  11. Roll-In • Load data into a separate table • Perform any data transformation, cleansing • ATTACH it to partitioned table • Use SET INTEGRITY to accomplish • Index maintenance • Checking of range and other constraints • MQT maintenance • Generated column maintenance • Table is online through out process except for ATTACH • New data becomes visible at end of SET INTEGRITY IBM Confidential

  12. Roll-Out • Use DETACH to roll-out a range of data • Rolled-out data is available in a new, separate table • Data disappears from view immediately upon DETACH • Rolled-out data can be dropped, archived, moved to HSM • Queries are drained and table locked by DETACH • Dependent MQTs go offline and need to be refreshed via SET INTEGRITY IBM Confidential

  13. A Partition Table • Table may have 32767 partitions • Backup and Restore individual partitions • Indexes may be placed in separate tablespaces IBM Confidential

  14. Autonomic Computing

  15. Autonomic Management Automatic Database Setup Wizard • Creates a New database on Disk • Configures new database for performance • Turns on automatic maintenance and health monitoring • Configures notification by e-mail or pager IBM Confidential

  16. Control Center Support Throttle Utilities • Allows DBA to reduce impact of running resource-intensive utilities on operational workload • Backup, Rebalance, Runstats • Support settling and display for utility execution priority for the supported utilities: • backup, rebalance (and reorg, load and runstats) • set priority in utility dialogs • set priority in SHOW COMMAND • Display priority of executing utilities in Task Center and the current priority set for the session IBM Confidential

  17. Autonomic Technology Thoughts • Automatic configuration of Databases • Invoke configuration advisors on CREATE DATABASE • Make the defaults intelligent • Based on environment • “Good” DBAs can override values • Adaptive Self Tuning • Memory • Sort heaps, bufferpools, package cache, lock list • Maximize usage of resources to • achieveoptimal performance IBM Confidential

  18. DB2’s database memory model – shared memory • All memory heaps are contained within the database shared memory set • On non-Windows platforms the set memory is all allocated at database startup and can not grow beyond its allocated size • On Windows the memory is allocated at startup but can grow or shrink as needed • On 32 bit platforms set size is limited • On 64 bit platforms set size virtually unlimited IBM Confidential

  19. Autonomic Technology – Additional Thoughts Automatic Storage Provisioning • Greatly simplify the task of apportioning storage for DB2 logs and data • Administrator control, if desired • Dynamic allocation • Policy specification allows refinement of behaviour Progressive Re-optimization • Evolve from statistical profiles (V8.2) to adaptive runtime learning and improvement • “The learning optimizer” (LEO) IBM Confidential

  20. STMM and the buffer pools • Trades memory between buffer pools based on relative need • New metrics determine where memory is most needed such that total system time is reduced • Zero, one or more buffer pools can be set to AUTOMATIC • In newly created Viper databases, all buffer pools default to AUTOMATIC • Works with buffer pools of any page size • Transfers from a buffer pool with 8 k pages to one with 4 k are 1:2 • Decreasing the buffer pools can take a lot of time • Must write out all dirty pages in memory being freed • If pages are in use the resize may wait on locks IBM Confidential

  21. STMM and DATABASE_MEMORY • STMM tunes DATABASE_MEMORY if it is set to AUTOMATIC or a numeric value • If set to AUTOMATIC, memory is taken from, and returned to, the OS if required by the database • DBA need not know how much memory to allocate to DB2 • This is the default for newly created Viper databases • If set to a numeric value, memory is given to AUTOMATIC heaps up to the numeric value • Allows DBA to set total memory consumption for the database • DB2 will then distribute the memory to optimize performance • If set to COMPUTED, no DATABASE_MEMORY tuning will occur • When database starts, memory requirements are computed based on the heap configuration • Once the database starts, the database shared memory set is allocated based on the computation • Version 8 AUTOMATIC behavior IBM Confidential

  22. Tailoring STMM with DPF • If SELF_TUNING_MEMORY is off at a particular node, no tuning will occur • Tuning should be turned off for atypical nodes • Catalog nodes with no data • Coordinator nodes that don’t directly process queries • Tuning can be turned off for one or more parameters on any given node • If STMM configuration update arrives at a node and that parameter isn’t set to AUTOMATIC at that node, nothing changes • Only parameters set to AUTOMATIC on the tuning node will generate configuration updates IBM Confidential

  23. V8.2.2 Single Point of Storage Management (SPSM) • A concept that is made up of three distinct features available in DB2 V8.2.2 • Storage paths that are pre-defined for a database • Automatic storage table spaces whereby DB2 will create the table space containers on the storage paths defined for the database • The ability for DB2 to automatically extend existing containers (or create new ones) as the table space fills up • What DBAs are going to like? • The ability to auto-extend a DMS file table space • The simplicity of SMS and the power and flexibility on DMS • SAP uses DMS table spaces but want to offer users more “ease of use” characteristics • The ability to provide a single point of storage management for the database IBM Confidential

  24. V8.2.2 Automatic Storage Examples • Single point of storage • For existing database • auto grow tablespacesALTER TABLESPACE TOR#BTABD AUTORESIZE YES INCREASESIZE 50 M • For new databases • Specify storage areas for DB2 to automatically create tablespace containers - paths • Ability to specify initialize and growth sizeCREATE DATABASE TOR AUTOMATIC STORAGE YES ON /db2/TOR/storagepath001, /db2/TOR/storagepath002, /db2/TOR/storagepath003 AUTORESIZE YES INITIALSIZE 5 G INCREASESIZE 100 M MAXSIZE NONE IBM Confidential

  25. XML

  26. What is XML? <? xml version=“1.0” ?> <purchaseOrder id=‘12345” secretKey=‘4x%$^’> <customer id=“A6789”> <name>John Smith Co</name> <address> <street>1234 W. Main St</street> <city>Toledo</city> <state>OH</state> <zip>95141</zip> </address> </customer> <itemList> <item> <partNo>A54</partNo> <quantity>12</quantity> </item> <item> <partNo>985</partno> <quantity>1</quantity> </item> </itemList> </purchaseOrder> XML Technology XML = Extensible Markup Language Self-describing data structures XML Tags describe each element and their attributes IBM Confidential

  27. Relational Integration of XML & Relational Capabilities • Applications combine XML & relational data • Native XML data type (server & client side) • XML Capabilities in all DB2 components DB2 SERVER CLIENT SQL/XML DB2 Storage: Relational Interface DB2 Client / Customer Client Application DB2 Engine XQuery XML Interface XML

  28. Native XML Storage • DB2 will store XML in parsed hierarchical format (similar to the DOM representation) • “Native” = the best-suited on-disk representation of XML create table dept (deptID char(8),…, doc xml); • Relational columnsare stored in relationalformat • XML columns arestored natively • All XML data is storedin XML-typed columns IBM Confidential

  29. XMLTable: make table from XML SELECT X.* from XMLTABLE (‘db2-fn:xmlcolumn(“PORDERS.PO”)//customer’COLUMNS “CID” INTEGER PATH ‘@id’, “Name” VARCHAR(30) PATH ‘name’, “ZipType” CHAR(2) PATH ‘zip/@type’, “Zip” XML PATH ‘zip’ ) AS “X” IBM Confidential

  30. The FLWOR Expression • FOR: iterates through a sequence, binding variable to items • LET: binds a variable to a sequence • WHERE: eliminates items of the iteration • ORDER: reorders items of the iteration • RETURN: constructs query results FOR $movie in db2-fn:xmlcolumn(‘table1.movies’) LET $actors := $movie//actor WHERE $movie/duration > 90 ORDER by $movie/@year RETURN <movie> {$movie/title, $actors} </movie> <movie> <title>Chicago</title> <actor>Renee Zellweger</actor> <actor>Richard Gere</actor> <actor>Catherine Zeta-Jones</actor> </movie> IBM Confidential

  31. Objective • Data Row Compression Concepts • DDL • Creating Compression Dictionary table IBM Confidential

  32. Data Row Compression Concepts Dictionary Based Data compressed: Disk, Buffer pools and logs IBM Confidential

  33. Row Compression Using a Compression Dictionary • 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 Dictionary IBM Confidential

  34. Row Compression Dictionary Data Page Dictionary • Compression dictionary • Stores common sequences of consecutive bytes in a row • Such sequences can span consecutive columns • A table must have a compression dictionary before rows can be compressed • Compression dictionary storage • Directly in table partition • In special, internal, non-selectable rows which are linked together • Typically on the order of 100KB • Compression dictionary creation • In initial release, requires non-inplace REORG Data Page Dictionary Data Page Dictionary Data Page IBM Confidential

  35. Table DDL for Compression CREATE TABLE tablename ( col1 datatype, …) COMPRESS NO COMPRESS YES ALTER TABLE tablename COMPRESS NO COMPRESS YES IBM Confidential

  36. Dictionary Building using Offline Reorg REORG < table name > INDEX < index name > ALLOW READ ACCESS ALLOW NO ACCESS USE <tablespace name > KEEPDICTIONARY LONGLOBDATA RESETDICTIONARY IBM Confidential

  37. Compression Estimation Utility INSPECT ROWCOMPESTIMATE TABLE NAME table-name RESULTS KEEP file-name IBM Confidential

  38. DB2 Compression Estimator • Product used to estimate compression savings. IBM Confidential

  39. DB2 Compression Estimator the Results IBM Confidential

  40. Compression Facts DPF supports compression Replication does not support compression XML column data is not compressed. IBM Confidential

  41. Security

  42. Security - Label Based Access Control • Label Based Access Control (LBAC) • A “label” is associated with both user sessions and data rows • 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, group or tree 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 DB2 for z/OS v8 IBM Confidential

  43. LBAC Query SELECT * FROM EMP WHERE SALARY >= 50000 IBM Confidential

  44. Database Design

  45. Large Row Identifiers • Increase In table size limits and rows per page • Tablespace level definition • DMS Tablespace only • ALTER TABLESPACE <name> CONVERT TO LARGE • Tablespace is locked, definition is modified and catalogues are updated • Every index for every table will be marked bad • Indices will have will be rebuilt on first table access IBM Confidential

  46. Current Tablespace Design 4 64G 8 128G 16 256G 32 512G Tablespace Size Pagesize 255 16M 4x109 Rows Row ID (RID) 4 Bytes IBM Confidential

  47. New Tablespace Design 4 2T 8 4T 16 8T 32 16T Tablespace Size Pagesize 3K 512M 1.5x1012 Rows Row ID (RID) 6 Bytes IBM Confidential

  48. Rows on a Page IBM Confidential

  49. Miscellaneous

  50. Copy Schema • Stored Procedure ADMIN_COPY_SCHEMA • Copy and Create Schema • Stored Procedure ADMIN_DROP_SCHEMA IBM Confidential

More Related