RDBMS Enterprise Features

1. RDBMS Enterprise Features Jacques Roy jacquesr@us.ibm.com

2. Why Enterprise Features? • Provides general capabilities independent from specific applications • Improved performance for data centric processing • Enables enterprise integration • Reduces application complexity

3. Informix Enterprise Features1 • Backup/Restore • Load/unload of data • High-availability • Replication • Clustering • Storage optimization • MQ integration • Change Data Capture 1 Partial list, other features could be considered enterprise features

4. Backup/Restore • Traditional feature • Online backup • Multiple levels of backup • Full, incremental • Point-in-time table-level restore • Benefits: • Data protection for disk failure • Archiving of older data • Protection from user errors (table-level restore)

5. Load/unload of Data • Raw tables • High-performance loader (HPL) • Simple dbaccess load/unload commands • Benefits: • Quickly make data available for processing • Exchange data between organizations • Provide some transformation capabilities (HPL) • Better integration with existing data structure • See also: • Informix warehouse for data transformation • Extract-Load-Transform

6. Continuous Log Restore (CLR) • Also known as “Log Shipping”. • Allows logical recovery to span multiple ‘ontape/onbar’ commands. • Provides a secondary instance with ‘log file granularity’. • Does not impact the primary server. • Can co-exist with “the cluster” (HDR/RSS/SDS) as well as ER • Can be automated by scripting the log backup alarms • Useful when backup site is totally isolated (i.e. no network) Primary CLR1 CLR3 CLR2

7. HDR Secondary Blade Server A <New Orleans> Building-A HDR Traffic Primary Client Apps Blade Server B <Memphis> High-Availability Data Replication (HDR) • Two identical servers on two identical machines • Optionally encrypted communication • Primary server: • Fully functional server (all DDLs and DMLs) • Sends logs to secondary server • Secondary server: • Can be local or remote • Replays logs to keep in sync with primary • Optionally updatable server – DML statements (UPDATABLE_SECONDARY parameter on both machines) • Benefits: • Recovery from site failure • Offload processing from primary When Primary server goes down, secondary server takes over as Standard server.

8. Primary RSS RSS RSS Remote Secondary Server (RSS) • Primary can support any number of RSS nodes • Maintains full disk copy of the database server • Optionally updatable server(DELETE, INSERT, SELECT, UPDATE) • Uses asynchronous full-duplex communication with primary • Can be promoted to an HDR secondary • Benefits: • Multi-site failure recovery (with HDR) • Availability over unstable networks • Offload processing

9. Germany Italy Spain ER Topology Enhanced Enterprise Replication (ER) • Peer-to-peer, asynchronous replication • Replicates rows and columns • Primary-target and update anywhere support • High performance, enhanced parallelism • Supports multiple topologies, large number of nodes • Supports network encryption • Supports heterogeneous hardware • Benefits: • Data distribution • Workload distribution • Data consolidation • High performance due to parallelism

10. Germany Spain Italy France USA USA Europe Germany France California Asia Fully Meshed–Update Anywhere China Kansas Alameda Guangzhou Santa Clara Japan Pleasanton Contra Costa Beijing Hierarchical – Push down, Consolidate up Shanghai Snowflake / Forest of Trees Nodes share more with closest nodes, less with nodes farther away Enterprise Replication : Schema Examples Hong Kong

11. LSN ACK LSN SDS SDS Primary Shared Disk Hardware Mirror Shared Disk Secondary (SDS) • Shared disks between multiple instances • Support for multiple SDS in a cluster • Encrypted communication available • Optionally updatable (UPDATABLE_SECONDARY parameter) • Primary transmits the current Log Sequence Number (LSN) as it is flushing logs. • SDS instance(s) receives the LSN from the primary and reads the logs from the shared disks. • SDS instance(s) applies log changes to its buffer cache • SDS instance(s) resync processed LSN to primary • Benefits: • Protection from machine(s) failure (failover) • Horizontal scalability

12. Primary SDS 1 HDR SDS 2 SDS 3 RSS What is Continuous Availability Feature (CAF)? • IDS 11 option • Feature: • Shared Disk Secondary (SDS) • Complementary features: • Remote Secondary Server (RSS) • High-availability Replication (HDR) • Enterprise Replication (ER) • Connection Manager/Failover arbitrator • All features can be mixed and matched • Benefits: • Resilience to multiple types of failures • Horizontal scalability

13. India Cluster HDR Traffic Primary Secondary US Cluster SDS HDR Traffic Shared Disk Blade Server D BOM-A Primary Secondary Shared Disk Blade Server E PNQ RSS Traffic SDS RSS Shared Disk SDS Blade Server A SFO-A Shared Disk Blade Server B ATL Shared Disk Mirror Optional RSS Traffic Shared Disk Blade Server G BOM-B Blade Server F CCU RSS SDS HDR Traffic Shared Disk Mirror Optional Primary Secondary Shared Disk Blade Server D SFO-B Blade Server C DEN SDS Shared Disk Blade Server H LHR-A Shared Disk Blade Server H BAA RSS Traffic RSS SDS Shared Disk Mirror Optional Shared Disk Blade Server J LHR-B Blade Server I MUC Europe Cluster Combination of the Availability Features • Any Node within the ER domain can also be an IDS GA cluster • ER can be used to replicate complete or partial (schema based) cluster data • ER relieves the dependency with the Primary in situations such as network outages

14. Client Apps HDR Traffic Primary Offline Shared Disk Blade Server A <New Orleans> Building-A Shared Disk Blade Server C <Memphis> HDR Traffic RSS Traffic Shared Disk Mirror DBA HDR Secondary Offline Blade Server B <New Orleans> Building-B OAT Blade Server D <Denver> Shared Disk Client Apps IDS Global Availability Fabric Full Suite of High Availability Options to Lower Costs HDR Secondary Primary CAF Client Apps CAF RSS The Initial System Disaster Strikes New Orleans Add Capacity Local Clients Loose Connectivity The Rest of the Clients Continue HDR Secondary and RSS Nodes Promoted HDR/RSS Replication Stops Connect New Clients Add a Local Copy in Denver Reduce hardware costs with blade servers and no application changes Add a Failover Node HDR Traffic Resumes, Local Clients Continue

15. Connection Manager (CM) • Separate daemon program distributed with client bundle • Performs node failover based on failover config parameter • Reconfiguration of parameters can be performed dynamically • Multiple CM can be used to avoid single point of failure • Re-route connections to the best-fit nodes • Monitors nodes for failure • Benefits: • Automates node failover • Automatic connection re-routing (during node failure) based on Service Level Agreement W h e r e ' P s r i t m h e a r y Connection Manager Primary SDS 1 HDR SDS 2 SDS 3 RSS

16. Is Primary Really Down? Primary Down? HDR Traffic HDR secondary Primary RSS Traffic RSS Failover Arbitrator • Part of the Connection Manager (CM) • Uses multiple checks to insure failure • Performs failover when primary is down • Order of failover defined in the CM Configuration File

17. Availability Decision Tree Do you need to protection from node failure? Yes Use HDR No Do you need multilevel site failure protection? Yes Do you need to protection from site failure? Use RSS Yes Use SDS No No Do you need geographically disperse processing? END Yes Use ER No

18. Compressed Compressed Uncompressed Compression and Storage Optimization • Row base compression • Estimation tools helps in finding compression ratio • Compression can save 40-50% of the DB storage requirements • For IO-bound workloads compression also improves performance • Significant memory savings – more efficient memory utilization • Also save on backup storage and disaster recovery storage Dictionary Multiple Compressed Pages compress repack shrink Data Page Compression On Data Page With Multiple Rows Reusable Empty Data Pages

19. MQ Integration • Access WebSphere Message Queue (WMQ): • Function interface. • Table interface. • Transactional (must be used within a transaction). • Benefits: • Reduced learning curve: • It’s only SQL • Messages sending tied to transaction success • Not left to the application logic • De-couple application with business rule • Could be transparent to the application (triggers)

20. MQ Functions

21. Change Data Capture • Documented SQL-based API • Log-based • Benefits: • Enables low-overhead event monitoring • Enables heterogeneous replication • Enables other enterprise integration • See also: • IBM InfoSphere Change Data Capture

22. How to get customers started with DataMirror CDC for Integration, Replication, HA Replication • Challenges • Need to synchronize data across 1 or more DBs for query workload distribution. Operational reporting or business continuity during application migrations /consolidations /upgrades • Requirements • Heterogeneous DBs/OSs • Topologies: mixed (1-> many, many -> many, many->1) • Solution • Transformation Server • Benefits • Automatic data distribution • Reduces dev costs and effort • Low impact on source systems High Availability • Challenges • Need to provide 24x7 access to systems, processes, applications • Reduce risk and minimize resources to maintain back-ups • Requirements • Homogeneous DBs • Topologies: 1->1 • Solution • iCluster (iSeries) • iReflect (Oracle) • WebSphere Replication Server • Benefits • Data and applications are protected in the event of downtime • Manage back-up and recovery in-house Real-Time Integration • Challenges • Need up-to-date information delivered to eCommerce, data warehouses and other downstream processes • Requirements • Heterogeneous DBs/OSs • Topologies: usually many -> 1 • Solution • Transformation Server • Benefits • Accurate up-to-date information available 24x7 without impact to source systems • Low impact on source systems • Transactional Integrity

23. CDC with SOA • CDC allows source data changes to trigger events in WebSphere ESB • Changed data is delivered to the ESB for downstream processing • Without impacting source system performance LegacyDatabase LegacyDatabase LegacyDatabase .. ..