ibm red brick warehouse server roadmap/futures fred ho
TRANSCRIPT
IBM Software Group
®
IBM Red Brick Warehouse Server Roadmap/Futures
Fred Ho & Cindy Fung Software Development Managers Information Management Solutions
IBM Software Group | DB2 Information Management Software
Topics
Red Brick Update Red Brick as part of IBM
Red Brick Product Roadmap
Red Brick Versions 6.2 and 6.3 Overview
Red Brick Gold Bundles
Summary Red Brick and DB2 Information Integration 6.3 Features Detail Future considerations Summary
IBM Software Group | DB2 Information Management Software
Quick update on Red Brick @ IBM
Loyalty to Red Brick Warehouse Server Continued strong endorsement by customers
Continued investment & support by IBM
Product roadmap Maintained release timelines as pre-acquisition days
Shipped one major enhancement release (v6.2) & several maintenance releases
Next feature release is on target
Red Brick team Integrated into IBM Data Management and BI group at SVL
Retention still very high
Red Brick partnerships Key part of the acquisition
Strong retention of partners
IBM Software Group | DB2 Information Management Software
2002
2003
Integrated Analytics
2004
InteroperabilityPerformance, Usability
IBM Red Brick Warehouse RoadmapHigh Performance, Easy to Administer Data Mart Engine
IBM Red Brick Warehouse RoadmapHigh Performance, Easy to Administer Data Mart Engine
2005
Red Brickv6.2
Red Brickv6.3
Target: (1H2004)• DB2 BI tools integration• Performance Improvements to
STARJoin and TARGETJoin• Dynamic Smart Scan• Self Tuning TMU Block Cache
Surrogate Key Generation &• Expression support in TMU• Improve performance of
aggregations,deletes, table scans, etc.
• Others .....
GA’ed Oct. ‘02• ANSI SQL/OLAP functions• Integrated Backup/Restore• Query/Load Performance• Load/Export data in XML• Performance monitor• Locally segmented indexes• Others ….
Red Brickv6.4
In Planning: (2H2005)• Increased parallelism
for query performance and loader support
• Additional platform support
• Improved diagnostics
IBM Software Group | DB2 Information Management Software
SQL/OLAPVista Query Rewrite ExtensionsQuery Performance MonitorLocally Segmented IndexBackup & Restore FacilityXML Data Load/UnloadRemote Load TMUSystem Catalog ImprovementsUpgrade to 6.20 (32 bit to 64 bit)Segment DDL EnhancementsConnectivity (ODBC/JDBC) Enhancements
GA - 10/1/02 in Passport Advantage
Red Brick v6.2 Content – Available today
Majority of customers have migrated to v6.2Majority of customers have migrated to v6.2
IBM Software Group | DB2 Information Management Software
Major Performance Improvements • Dynamic Smartscan • Memory mapping of dimension index/tables for STARjoin performance • Target Join improvements for local index • Optimizer hints to specify STARindex for fact-to-fact joins • Table Management Utility (TMU) Memory Tuning for non-optimized loads
Major Usability Improvements• Alter Table with Working Segment • Additional Expression support (e.g. conditional IF's) in the Loader • Additional SQL/OLAP functions, e.g. Round, Median • Shrink System Catalog Utility• Allow 3GB Address Space on Windows Platform • Delimiter enhancements in Loading and Exporting
Port and OS Versions Upgrade• Support HP Itanium Product Family (IPF) (64-bit)• Upgrades for AIX v5.2, SUN Solaris 9, DEC Tru64 5.1A
Planned v6.3 - Themes and Content
GA – 1H2004 in Passport Advantage
Red Brick v6.3 Themes and Content
IBM Software Group | DB2 Information Management Software
IBM, Informix & Red Brick Database evolution
C-ISAMIndexed Record access
SESQL Interface, Stored Procedures
OnlineAvailable, Distributed, Performance
IDS 7Scalable, Parallel
’70s ’80 ’85 ’90 ’95 ’00 ’05 ……
IMS – Hierarchical, Very Fast,Available DB2/390 - SQL Interface, Stored Procedures,
AvailableDB2/UDB - Open, Parallel,Available, Federated,
XPS – DW on MPP, Clusters
Red Brick – Star Schema
IDS 9Extensible, Usable, Secure
DB2 Arrowhead – SMART,Information IntegrationIncludes best of IDS, XPS, Red Brick & DB2
30 years of Innovation and Investment protection
30 years of Innovation and Investment protection
IBM Software Group | DB2 Information Management Software
Can I stay on Red Brick or will IBM make me move to DB2?
• Our Philosophy• "Moves anywhere are difficult. Move only if the result is a
recognizable value advantage for you”
• Our Promise• "We'll continue to support the Red Brick product line for as
long as customers require"
• Our Proof• "100's of IMS* customers still receive regular updates to a
product originally released in 1968"(IMS is a mainframe, hierarchical database, the backbone of the world's financial systems)
IBM Software Group | DB2 Information Management Software
Red Brick Customer Status
IBM’s goal with Red Brick is to continue to sell to and support our customers via direct sales and partner sales world-wide
We have maintained very strong customer loyalty Existing customers growing their Red Brick environments
Our customers are happy with Red Brick, the IBM Data Management strategy and are looking into other DB2 products such as: DB2 Warehouse Manager, DB2 Intelligent Miner, DB2 Information Integrator, etc.
IBM Software Group | DB2 Information Management Software
Customer Facing Events
European Red Brick Users’ Group Meeting Sept. 5th, 2003 in Milan, Italy
IBM Data Management Technical Conference from Oct 27 – 31 2003 in Las Vegas
Numerous Technical sessions on Red Brick by senior members of the Red Brick team
Key Red Brick customer presentations
Joint DB2 and Red Brick sessions also included Red Brick v6.3 Beta Program to start in Oct 2003
Early access to the new features
Ability to work closely w/ R&D to help influence the quality of the release
Will work with other BI vendors to test and support Red Brick v6.3
IBM Software Group | DB2 Information Management Software
Red Brick Integration into DB2
Introduce key Red Brick technologies over next few years into DB2 Lead development of next generation DB2 star join algorithm
Participate in the design of other core technology projects
Data partitioning Load performance and function MQT (Materialized Query Table) enhancements
Champion other DB2 SQL extensions
Assist definition of “ease of administration” requirements
Synchronize with SMART team Provide interoperability support with other DB2 products
DB2 Warehouse Manager
DB2 Information Integrator
Query Monitoring Facility (QMF for Windows) Provide integration with System and Storage Management
Tivoli TSM
IBM Software Group | DB2 Information Management Software
GOLD BUNDLES
WHAT IS A GOLD BUNDLE? Product offering designed to offer a sense of "one" company
"Customer Choice" Database License
Customer can choose database platform as needs dictate, using same license for different products
Single database license offering customers a choice of IBM DM products (i.e. Informix, Red Brick, DB2)
IBM Software Group | DB2 Information Management Software
RED BRICK GOLD BUNDLES
Red Brick Analytic Bundle - Enterprises contains:
IBM Red Brick Warehouse Server v6.2
IBM DB2 ESE v8.1
IBM DB2 Developers Edition v8.1 (5 users)
IBM DB2 Warehouse Manager v8.1
Red Brick Analytic Bundle - SMB contains:
IBM Red Brick Warehouse Server Work Group Edition v6.20
DB2 WorkGroup Server Unlimited Edition WSE v8.1
DB2 Universal Developers Edition v8.1 (One user only)
IBM Software Group
®
DB2 Information Integrator V8.1& Red Brick Warehouse Server
IBM Software Group | DB2 Information Management Software
DB2 Information Integrator
Introducing new integration software : DB2 Information Integrator DB2 Information Integrator 8.1
DB2 Information Integrator for Content 8.2 Announcing beta availability
Access beta through IBM client representative Key business value:
Optimize IT investments given more choice in data access
Integrate data with better productivity and application efficiency
Gain more return from existing assets
IBM Software Group | DB2 Information Management Software
DB2 Information Integrator : Value for Red Brick
Key business value: Optimize IT investments given more choice in data access
Integrate data with better productivity and application efficiency
Gain more return from existing assets
Data Federation (leave your data where it is)• Create new applications via DB2 that integrate data stored in Red
Brick with data from other sources such as DB2, Oracle, Sybase, SQL Server, Teradata, XML files, Flat files, Message Queues, Web Services, ODBC databases…
• Enable existing DB2 applications that use DB2/UDB APIs, e.g. ODBC/JDBC/ESQL to access data in Red Brick
A single API for access to both Red Brick or Informix or DB2 or Oracle or…
IBM Software Group | DB2 Information Management Software
FederationMetadata
DB2 Information Integrator
Federation
DB2 Information Integrator SE
Oracle IDSRedBrickFlat Files
DB2 UDBMetadata
Scenario: Integrate Red Brick with DB2 UDB Your Value
Integrate Red Brick data marts seamlessly with DB2 UDB, Informix, Oracle or Flat files
Enable a unified view of data across multiple Red Brick marts combined with data in DB2 UDB
What You can do
Red Brick + DB2 II (Standard Edition)
Includes a “limited use” license that allows you to federate Red Brick data marts with DB2 UDB and Informix databases (XPS and IDS)
Additional data sources, e.g. Oracle, require a full license
IBM Software Group | DB2 Information Management Software
Scenario: Enable Third Party App Support
FederationMetadata
DB2 Information Integrator
FederationMetadata
DB2 Information Integrator SE
RedBrick
Your Value
Want to use packaged applications (e.g. SAS Campaign Manager) but application does not support Red Brick natively
What You can do
DB2 Information Integrator SE
Build the infrastructure to enable access of Red Brick data via DB2 II to the SAS Campaign Manager
IBM Software Group
®
IBM Red Brick Warehouse Server 6.3 Features
IBM Software Group | DB2 Information Management Software
6.3 Features
Major Performance Improvements Dynamic Smartscan
Memory mapping of dimension index/tables for STARjoin
TARGETjoin improvements for local index
Optimizer hints to specify STARindex for fact-to-fact STARjoin
Table Management Utility (TMU) Memory Tuning
Major Usability Improvements Additional SQL/OLAP functions
Expression support in the Loader
XML Improvements in Loader
Compact System Catalog Utility
IBM Software Group | DB2 Information Management Software
6.3 Features
Major Usability Improvements – cont’d Allow 3GB Address Space on Windows Platform
Delimiter enhancements in Loading and Exporting
Alter Table with Working Segment
Interoperability with DB2 products
System Port Support HP Itanium Product Family (IPF)
OS Versions Upgrade AIX v5.2
Sun Solaris 9
HP-UX IPF 11i
Windows32 on Server 2003
IBM Software Group | DB2 Information Management Software
Dynamic SmartScan
Additional queries could be considered for SmartScan segment elimination
Include constraints not on the segment column and the fact table is segmented by the referenced foreign key
Currently, could eliminate segments only with constraints on the segmenting column
Dynamic segment elimination is possible: When we can evaluate the constraints in prelim plans on dimension-to-
fact join, and
Strategizer chooses Table-Scan OR locally indexed Target-Join plan on the segmented fact table
Improve selectivity estimates to consider outcome of segment elimination, both static and dynamic
more accurate dynamic selection of STARjoin plan choices
IBM Software Group | DB2 Information Management Software
Dynamic Segment Elimination Example
Assume, “Sales” as a Fact table that is segmented by a foreign key “perkey”.
RISQL> Select Sum(Dollars) From Sales, Period
Where Sales.perkey = Period.perkey And
Period.date >= ’01-01-01’ And
Period.date <= ’12-31-01’;
Above mentioned query could potentially eliminate unwanted segments from “Sales” table if it chooses Table Scan OR local target-join on “Sales”.
IBM Software Group | DB2 Information Management Software
MMAP Dimension Table/Index
Reduce CPU overhead and I/O system calls by mmap dimension indexes and tables into shared memory
Apply to STARjoin/TARGETjoin/tablescan plans Typically contain Btree-1-1-Match (B11M) and Functional Join operators
to perform joins and row fetches to dimension tables
High benefit for queries with large number of rows produced from join(s) below the B11M and Functional Join operators
Mmap could potentially improve performance of
B11M when mmap corresponding dimension primary index Functional Join when mmap corresponding table Multiple queries/users sharing the single copy in shared memory
IBM Software Group | DB2 Information Management Software
TARGETjoin Performance
Improve performance of TARGETjoin Particularly for local indexes
More consistent performance between tightly and loosely constraints
More efficient index access for TARGETjoin and Scan operators
Local Index TARGETjoin improvement from 0 - 500% Biggest speedup on poorly performing joins
Particularly helps loose constraints on large dimensions
Preliminary test results approaching STARjoin performance in about 50%
Allow single column B-Tree indexes in TARGETjoin Consider B-Tree index on foreign keys with very large dimensions
Not always a win, particularly with loosely constraints on large dimensions
Could be a big win with tight constraints
IBM Software Group | DB2 Information Management Software
Optimizer Hints STAR indexes can be specified for queries on a per table basis
Must be careful when overriding optimizer selection
A specific STAR indexSET STAR INDEX AVAILABILITY (TABLE1_STAR_IX1) FOR TABLE1;
Multiple STAR indexesSET STAR INDEX AVAILABILITY (TABLE1_STAR_IX1, TABLE1_STAR_IX2) FOR TABLE1;
STAR indexes on a per multi-fact table STARjoin basis
SET STAR INDEX AVAILABILITY (TABLE1_STAR_IX1) FOR TABLE1 WHEN STARJOIN BETWEEN (TABLE1, TABLE2);
SET STAR INDEX AVAILABILITY (TABLE1_STAR_IX2) FOR TABLE1 WHEN STARJOIN BETWEEN (TABLE1, TABLE3);
IBM Software Group | DB2 Information Management Software
Optimizer Hints
STARjoin and TARGETjoin thresholds can be specified on a per table basis
SET STARJOIN THRESHOLD 20 FOR TABLE1;SET STARJOIN THRESHOLD 5 FOR TABLE2;
SET TARGETJOIN THRESHOLD 40 FOR TABLE 1;SET TARGETJOIN THRESHOLD 10 FOR TABLE2;
IBM Software Group | DB2 Information Management Software
TMU Memory Tuning
Better control over TMU memory resource usage
Allows TMU buffer memory to be tuned according to the load job
Introduces memory balancing between parallel loader tasks
Quickly allocate large amounts of buffer memory
Prevents excessive use of system memory by defining a maximum amount of buffer memory that could be used by the load job
Reports on TMU buffer usage: fine tuning for repetitive load jobs
IBM Software Group | DB2 Information Management Software
TMU Memory Tuning
Tuning rule of thumb: more logical I/O requires more buffers
Syntax:
SET TMU MAX BUFFERS number_of_blocks
SET TMU CONVERSION BUFFER PERCENT p
SET TMU OUTPUT BUFFER PERCENT p
SET TMU INDEX BUFFER PERCENT p
Recommend using new tunables over SET TMU BUFFERS approach
IBM Software Group | DB2 Information Management Software
More SQL/OLAP Functions
Distribution FunctionsCUME_DISTPERCENT_RANK
Inverse Distribution Functions (Median)PERCENTILE_CONTPERCENTILE_DISC
Scalar function ROUND
IBM Software Group | DB2 Information Management Software
Distribution Functions CUME_DIST() computes the position of specified row value relative
to the set of values (# of values equal to or less than x) / (total # of values)
PERCENT_RANK() returns the percent rank of a value relative to a group of values
(rank of row in partition –1) / (# of rows in partition –1)
Example
Price Percent_rank
Cume_dist
100,000 0 0.25
220,000 0.33 0.75
220,000 0.33 0.75
230,000 1 1
IBM Software Group | DB2 Information Management Software
Inverse Distribution Functions
Answers question such as “What is the median (50th percentile) value of my data?”
Require a sort specification and a parameter that takes a value between 0 and 1
Use the new WITHIN GROUP clause to specify the data ordering
ExampleSelect Area, Price,
PERCENTILE_CONT(0.5) WITHIN GROUP (Order by Price) OVER (Partition by Area) as Median_cont,
PERCENTILE_DIST(0.5) WITHIN GROUP (Order by Price) OVER (Partition By Area) as Median_disc
From Homes;
IBM Software Group | DB2 Information Management Software
ROUND() scalar function
ROUND() returns rounded number to the integer places left or right of the decimal point
Examples ROUND (864.827, 2) = 864.830
ROUND (864.827, 1) = 864.800
ROUND (864.827, 0) = 865.000
ROUND (864.827, -1) = 860.000
ROUND (864.827, -2) = 900.000
ROUND (864.827, -3) = 1000.000
IBM Software Group | DB2 Information Management Software
Expression Support in Loader
Input data can now be modified while being loaded to a table
Basic arithmetic operations now supported
Modification also possible based on conditions
A pseudo column can now be assigned to a target column
Multiple conditions now possible in ACCEPT/REJECT clause with some limitations
Highly requested functionality
More integrated with the server than ETL tools
IBM Software Group | DB2 Information Management Software
Expression Support in Loader
Syntax: (snippets from TMU control file)
Arithmetic expressions:$A POSITION(2) INTEGER EXTERNAL(10),ColA ($A + 5)/2
Conditions:$A POSITION(2) INTEGER EXTERNAL(10),ColB CASE WHEN $A > 5 THEN $A+3
WHEN $A = 5 THEN $A-1 ELSE $A+1
IBM Software Group | DB2 Information Management Software
Expression Support in Loader
Pseudo column assignment$A POSITION(2) INTEGER EXTERNAL(10),ColC $A
ACCEPT/REJECT clause
ACCEPT ($A > 5 AND $B < 10) OR ($C = 15)
Limitation:If real columns are used, then just a single condition is allowed. With pseudo columns multiple conditions are allowed (example above)
IBM Software Group | DB2 Information Management Software
XML Improvements in Loader
Extends TMU and SQL Export functionality to provide additional XML support
XML multiple namespaces support
Export generates default namespace
Upgrade to the IBM XML4C v5.x parser key performance enhancements as well as critical fixes over 6.2
Xerces version
Seamless upgrade to new parser
IBM Software Group | DB2 Information Management Software
Compact System Catalog
Compacts free space within system catalog Occurs when objects are freed but not at the end of the catalog
Extension of System Catalog enhancement in 6.2 where free space is released at the end of the system catalog
Rb_syscompact Does the compaction
Requires DBA privilege
Creates a backup file
Checks for catalog sanity before compaction
IBM Software Group | DB2 Information Management Software
3GB Address Space on Windows
Extend beyond 32-bit memory access limit
Increase virtual address space from 2GB to 3GB
Feature Advantages More data can be cached in physical memory
Greater scalability and performance
Supported on 32-bit versions of the Windows® 2000 Advanced Server
32-bit versions of Windows.NET Server
Enabled on executables: rb_tmu.exe, rb_ptmu.exe, rbw.exe, rbwtest.exe and risqltty.exe
IBM Software Group | DB2 Information Management Software
Loading with Multiple Characters Separator
Apply to loading and exporting in delimited format
new load format clause syntax: format separated by ‘ <separator> ’ [ enclosed by ‘<string delimiter>’ ]
Separator may consist of 1 to 10 characters may be composed of single or multi-byte characters
Feature Advantages: Data generated by other ETL tools that use multiple characters
separator could be loaded directly without modification
Data containing separator string will be loaded correctly as long as the data is enclosed within the string delimiter
IBM Software Group | DB2 Information Management Software
Export with String Delimiter Support
Adds delimiter support to enclose a string
New export command syntax: export to ‘xxx’ format delimited [by ‘<export delimiter>’ ] [enclosed by ‘<string
delimiter>’ ] (<select query>);
Export delimiter and string delimiter must be one character may be composed of single or multi-byte character
Feature Advantages: May specify a different export delimiter for each export command
Export data may be directly loaded back into a database using the loader
Export delimiter can be part of the data content when string delimiter encloses the data
IBM Software Group | DB2 Information Management Software
ALTER TABLE Using a Working Segment
Provides more reliable recoverability of failed alter operations than existing alter table IN_PLACE
Working segment can be reused after the alter operation is over
The table is still altered “in place”
Syntax:
ALTER TABLE <table_name> [ADD | DROP] COLUMN
IN_PLACE [USING <segment_name>]
New feature is strongly recommended over IN_PLACE alter
IBM Software Group | DB2 Information Management Software
ALTER TABLE enhancements
Much requested feature
Combines nominal space requirements of IN_PLACE alter with reliable recovery characteristic of alter in other segments
As a table segment is altered, its original contents are temporarily stored in a standard, user-defined segment (a ‘working segment’)
If the alter fails (e.g. due to a full disk), original contents of the table segment are available in the working segment for the alter to be resumed and completed successfully
Additional disk space required for the working segment is only as much as the largest segment of the table
IBM Software Group | DB2 Information Management Software
HP-Itanium Porting Project
A native port, not architectural emulation Yields high performance by directly taking advantage of Itanium’s
architecture
No need to convert data Red Brick databases created on PA-RISC will be fully compatible with
Red Brick on HP-Itanium
Currently, no vendors plan to support XBSA Backup/Restore interface on HP-Itanium
BAR to files or UNIX tapes
IBM Software Group | DB2 Information Management Software
DB2 Products that support Red Brick currently
DataJoiner Version 2.1.1Works with ODBC wrapper. See http://www7b.boulder.ibm.com/dmdd/zones/informix/library/techarticle/0302rumsby/0302rumsby.html
for more info on how to use DataJoiner with Red Brick
Warehouse Manager DB2 8.1 FP2
See Warehouse Manager document for more information
QMF for Windows (ODBC only)
Tivoli Storage Manager
IBM Software Group | DB2 Information Management Software
Future DB2 Interoperability Options
Information Integrator Planned for beta in late August
GA in November 2003
Intelligent Miner for Data Under consideration
IBM Software Group
®
Features Being Considered Beyond v6.3
IBM Software Group | DB2 Information Management Software
Features Being Considered
• Windows-64 Support
• Improved Query Parallelism
• Parallel Sort/Improved Order-By
• Starjoin performance
• Incremental Maintenance speedup for Vista
• Partition Parallel Loader
• Backup/Restore Performance
• Compression
• Pipeline parallelism
• Better Diagnostics
• Packaged diagnostics of memory, stack, version log dumps
Features Being Considered
• Windows-64 Support
• Improved Query Parallelism
• Parallel Sort/Improved Order-By
• Starjoin performance
• Incremental Maintenance speedup for Vista
• Partition Parallel Loader
• Backup/Restore Performance
• Compression
• Pipeline parallelism
• Better Diagnostics
• Packaged diagnostics of memory, stack, version log dumps
IBM Software Group | DB2 Information Management Software
Red Brick Futures
IBM committed to supporting Red Brick for the forseeable future
DB2 is the flagship DM product.
Red Brick features being integrated into DB2 over time.
Red Brick feature release approximately every 18 months
Channel/partners remains a key play for IBM
Summary:
IBM Software Group | DB2 Information Management Software
Questions ?