1 / 34

SQData Enterprise Information Management Product Version 1 Release 3 Executive Briefing

SQData Enterprise Information Management Product Version 1 Release 3 Executive Briefing. Topics. Objectives SQData Overview SQData Components SQData in Action Q&A. Briefing Objectives. Provide a high level overview of SQData V1R3 Describe the SQData components

brita
Download Presentation

SQData Enterprise Information Management Product Version 1 Release 3 Executive Briefing

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. SQData Enterprise Information Management Product Version 1 Release 3 Executive Briefing

  2. Topics • Objectives • SQData Overview • SQData Components • SQData in Action • Q&A

  3. Briefing Objectives • Provide a high level overview of SQData V1R3 • Describe the SQData components • Illustrate some SQData deployment scenarios • Address Questions

  4. SQData – Company Background • Founded in 1999 • Mission: • To provide our customers with outstanding technology and services in an efficient and cost effective manner • Focus: • Integrating legacy data with newer technologies • Growth through channel partnerships • Outperform the competition (functionality and CPU cycles) • Re-Marketing Agreement with IBM • Middleware offering (Santa Teresa Lab) • IMS near-real-time changed data capture • Integration with MQSeries • Multi-Platform full data refresh

  5. What is SQData? • Multi-Purpose Data Management Utility • High Performance • MQSeries Enabled • Relational & Non-Relational Databases/Files • Cross Platform Operation • What SQData Has/Can Been Used For: • Bulk Data Extraction and Transformation • Near-Real-Time Changed Data Propagation – IMS and DB2 • VSAM Changed Data Propagation (asynchronous) • Integration with 3rd Party COTS Packages • Lookups to External Data Sources • XML Message Formatting • Workload Generation for Testing • Data Cleansing & Filtering

  6. SQData Evolution • June 2000 – Development Started • Version 1, Release 1 (V1R1) • Available: 4Q 2000 • Focus: • Data Extraction and Transformation • Support for Multiple Database/File Formats • Data Type Translation • Cross Platform Operation • Version 1, Release 2 (V1R2) • Available: 3Q 2001 • Focus: • Near-Real-Time IMS Data Propagation • Integration with MQSeries • Enhanced XML Message Formatting • Complex Data Structures • Additional Commands & Functions

  7. SQData Evolution… • Version 1, Release 3 (V1R3) • Available: 1Q 2003 • Focus: • Script Encapsulation through Mapping Procedures (PROCS) • Process Monitor for Multiplatforms Product • Normalization for Repeating Groups • Smart Apply’ for Propagating Data to Target Relational Databases • IMS Change Data Capture Enhancements (auto-commit, exclude pgm) • Additional Commands, Functions and Performance Enhancements • Version 2, Release 1 (V2R1) • Planned Availability: 4Q 2003 • Focus: • Integrated Graphical User Interface (GUI) • Subsystem Framework (MQ Broker) • Support for Additional Non-Relational Databases • Tighter Integration with COTS Packages (Peoplesoft, SAP, etc.)

  8. Capabilities: Supported Platforms/Databases • SQData for OS/390 and z/OS • Mainframe • IMS V6 and Higher • DB2 V6 and Higher • MQSeries V5.2 and Higher • VSAM • Flat Files • SQData for Multiplatforms • UNIX, Linux, Windows 2000/XP • Oracle 8.1 and Higher (native support) • DB2 UDB V7 and Higher (native support) • SQL Server, MS Access and other Relational Databases via ODBC • AS/400 and IBM iSeries Servers

  9. Capabilities: Data Formatting and Transport • Supported Data Formats • XML • Delimited • Binary (COBOL copybooks , C headers) • Relational Tables • IMS Segments • DB2 Load Utility Format • Supported Transport Mechanisms • Websphere MQ Messaging (MQSeries) • TCP/IP

  10. Topics • Objectives • SQData Overview • SQData Components • SQData in Action • Q&A

  11. Terminology • Datastore – Any Object that Contains Data • Databases • Files • MQ Queues • Structure/Description – Defines the Format of Data • Script – Contains SQData Commands and Functions • Data Structure Definitions • Data Store Descriptions • Mapping Scripts • Commands – Direct SQData Operation • Functions – Augment SQData Commands • Data Transformation • Conditional Logic • Table/Segment Lookups

  12. Terminology… • Queue – An MQSeries Queue • CDC – Change data capture (IMS or DB2) • Source Datastore – an input database, file or queue • Target Datastore – an output database, file or queue • Field/column – used interchangeably • Parser – the SQData Parser component • Engine – the SQData Integration Engine component • Distributed – Server based platforms (UNIX, Windows)

  13. SQData Command Script SQData Product Architecture Parsed Command Script SQData Parser Target Databases & Files Source Databases & Files SQData Integration Engine Target MQ Queues Source MQ Queues Lookup Databases & Files

  14. SQData Components • Command Script (Script) • Contains the Definitions, Commands and Functions that Control the Operation of the SQData Integration Engine • May Contain Imbedded Scripts (Datastores, Propagation, Lookups, etc.) • Parser • SQDPARSE (program name) • Prepares/Compiles the Command Script for Execution by the SQData Integration Engine • Integration Engine • SQDATA (program name) • Execution Component • Controlled by the Contents of a Parsed Command Script

  15. SQData Utilities • SQDCOPYQ– Copy to and from MQ Queue • Copies the Contents of an MQ Queue to a File • Copies the Contents of a File to an MQ Queue • SQDEMPTQ– Empty MQ Queue • Clears the Contents of an MQSeries Queue • Terminates when Queue is Empty • SQDQTERM – Shuts down SQData Integration Engine • Used if the input datastore is an MQ queue • Commonly used for the SQData propagation engine started task • Force or quiece option

  16. Topics • Objectives • SQData Overview • SQData Components • SQData in Action • Q&A

  17. Data Extraction • Highly flexible • Configuration options • To/from any SQData supported operating system platform • To/from any SQData supported database/file • Target apply via DBMS load or through propagation framework • Transformation options • Filter out unwanted data • Translate data to/from any supported SQData format • Include data from other sources via LOOKUP and JOIN

  18. Data Extraction Scenarios • IMS Segment Extract – Load to Distributed DBMS IMS Database or SQData Integration Engine Load Utility Formatted Data IMS Unload File FTP OS/390 and z/OS UNIX DBMS Load Utility Load Utility Formatted Data Distributed DBMS

  19. Data Extraction Scenarios… • DB2 Table Extract – Load to Distributed DBMS SQData Integration Engine DB2 Table Load Utility Formatted Data FTP OS/390 and z/OS UNIX DBMS Load Utility Load Utility Formatted Data Distributed DBMS

  20. Data Extraction Scenarios… • Low Volume Extract – IMS or DB2 to Distributed IMS Database SQData Integration Engine or DB2 MQ OS/390 and z/OS UNIX Apply Distributed DBMS SQData Integration Engine

  21. Data Extraction Scenarios… • Flat File to XML Formatted File and Distributed DBMS SQData Integration Engine Flat File MQ OS/390 and z/OS UNIX Apply Distributed DBMS SQData Integration Engine XML Formatted Data

  22. SQData ‘Power Loader’ • Fast Data Load of UNIX Distributed Databases • Speeds the database load process by 30 to 40 percent • Loads extracted data directly into a distributed database without landing the data on the UNIX instance • Eliminates the need for DASD required for traditional loads • Used in Conjunction with SQData for Multiplatforms • More Enhancements in the Works with SQData V2R1 • FTP as a supported datastore • Provides for true straight through processing • Legacy data extracts directly into the target distributed database(s) • Eliminates the need for auxiliary DASD • Much higher performance expected

  23. IMS Near-Real-Time Data Propagation • Uses an IMS Segment Capture Exit • Receives Changed Data from IMS • Creates an SQData IMS CDC Message with a before and after image • ‘Puts’ the SQData IMS CDC Message to an MQSeries Queue • Maintains Commit Scope Integrity with Application • IMS Online and BMPs – Two-Phase Commit • DLI Batch – Single Phase Commit • SQData Exit Features • Assign Certain Segments to Specific Queues • Include/Exclude Specific Programs for/from Data Capture • Include/Exclude Different IMS Region Types for/from Data Capture • Auto-Commit for DLI Batch Programs

  24. IMS Near-Real-Time Data Propagation… • Propagation Features/Functions • SQData Engine Sends Only Changes to Mapped Fields • If at least one of the mapped fields does not change, the changed data record is discarded. • Obtain Certain IMS Information for Changed Segments • Database Name • PSB Name • IMS Region Type • Time of Update (STCK and Timestamp) • IMS Change Operation (I – ISRT, R- REPL, D-DLET) • Userid of Transaction Making Data Change • Determine Whether or Not a Source Field Has Changed • FLDCHG Function

  25. IMS Applications MQ Applications Distributed Databases & Files Legacy Databases & Files IMS Near-Real-Time Data Propagation Scenario IMS IMS Databases MQ MQ MQ SQData IMS Change Data Capture Component Integration Broker XML Formatted Messages SQData Integration Engine

  26. IMS IMS Applications IMS Databases Integration Broker IMS Near-Real-Time Data Propagation Scenario… • Multi-platform IMS near-real-time data propagation MQ SQData IMS Change Data Capture Component MQ OS/390 and z/OS UNIX SQData Integration Engine Distributed Tables

  27. IMS IMS Applications IMS Databases IMS Trigger Monitor IMS Near-Real-Time Data Propagation Scenario… • Triggering a process based on the occurrence of an event Initiates IMS Trx MQ Message Triggers A Process SQData IMS Change Data Capture Component MQ

  28. IMS DB2 IMS Applications DB2 Applications IMS Databases DB2 Databases DOPAD ORD WR ITEM ORD WR ITEM SQData – Sample Customer Implementation Solaris WFADO DOPAD DB2 Trigger with DB2/MQSend SQData IMS Data Capture SQData Z/OS MQ Client VTS Oracle DBs SQData Runtime Engine DOPAD XML DOPAD SQData Runtime Engine(s) XML DOPAD ORD WR ITEM MQI XML ORD

  29. DB2 Near-Real-Time Propagation • Integration with IBM’s DataPropagator Relational (DPropR) • Works in conjunction with the DPropR Capture component • Provides high performance near-real-time DB2 changed data capture • Engineered to work seamlessly with future releases of DPropR • Extends the Database Apply Options • Non-relational databases/files (IMS, VSAM, etc.) • Relational databases not supported by DPropR • Utilizes MQSeries (WMQI) as the Transport Method • Allows for integration into an enterprise application framework • Common format for all changed data • Enabled Through an SQData ‘Active Listener’ Component • Watches for changes to the DPropR staging tables • Immediately propagates the changes to any SQData supported database

  30. DB2 Near-Real-Time Data Propagation… • Propagation Features/Functions • Allows for complex data transformations prior to apply • Provides the ability to combine (join) data from other sources • SQData Engine sends only changes to mapped fields • If at least one of the mapped fields does not change, the changed data record is discarded. • Obtain Certain DB2 information for changed table segments • Database name • Table name • Time of Update (STCK and Timestamp) • DB2 Change Operation (I – ISRT, R- REPL, D-DLET) • Userid of Transaction Making Data Change • Determine Whether or Not a Source Field Has Changed • FLDCHG Function

  31. MQ Applications Integration Broker DataPropagator Relational DPropR Distributed Databases & Files Legacy Databases & Files DB2 Near-Real-Time Data Propagation Scenario • DB2 Propagation with IBM’s Data Propagator Relational Product DB2 DB2 Tables DB2 Applications MQ MQ XML Formatted Messages SQData Integration Engine DPropR Staging Tables SQData Active Listener

  32. ‘Smart Apply’ Feature of SQData • Used for Relational Database target datastores • Ensures that only latest changes are propagated • Presence of a control column (SQDSTCK) • No script changes required • Eases timing restrictions on full data refresh process • Continue propagating while conducting full data refresh • Make sure to shut down SQData Engine (apply) during refresh

  33. Topics • Objectives • SQData Overview • SQData Components • SQData in Action • Q&A

  34. SQData Enterprise Information Management Product Version 1 Release 3 Executive Briefing

More Related