1 / 72

DataMigrator 7.7 in Real Time

DataMigrator 7.7 in Real Time. Chris Bevilacqua iWay Solutions Architect. Real Time ETL with DataMigator Agenda. Web Services Change Data Capture File Listener IWAF Adapters. Web Services. What are Web Services for?. What is a Web Service?.

angeni
Download Presentation

DataMigrator 7.7 in Real Time

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. DataMigrator 7.7 in Real Time Chris Bevilacqua iWay Solutions Architect

  2. Real Time ETL with DataMigatorAgenda • Web Services • Change Data Capture • File Listener • IWAF Adapters

  3. Web Services What are Web Services for?

  4. What is a Web Service? A software system designed to support interoperable machine-to-machine interaction over a network. It has an interface described in a machine-processable format (specifically Web Services Description Language WSDL). – World Wide Web Consortium

  5. Some sources of Public Web ServicesFree and commercial .net Publicly Available Commercial Web Services

  6. WSDLL.COMWeb Services – Free and Commercial

  7. Xmethods.net List of public web services

  8. Seekda.com Find WSDL for PhoneVerify Service from cdyne.com

  9. Add adapter for Web Services

  10. Add Connection for a Web Service

  11. Add Connection for Web Service

  12. Create Synonym

  13. Open Synonym

  14. Sample DataSupply values for PHONENUMBER and LICENSEKEY

  15. Sample DataShow all fields returned by Web Service

  16. Create Data FlowAdd input file

  17. User Defined FunctionCleans up phone number

  18. Add transformation Clean up phone number and assign name

  19. Add synonym for Web Service

  20. Join input file to Web ServiceOn PHONENUMBER and LICENSEKEY

  21. Select Columns from Web Service

  22. Test SQLShows “join” from input file to Web Service

  23. Change Data Capture When would you want to capture changes?

  24. Why Change Data Capture? • Large source databases, small batch window • Want to only copy the changes • Want near real-time updates • Need some way to detect what has changed…

  25. Change Data CaptureHow to detect changes to database tables • Change the application? • Track timestamp of changes, flag deletes • Write changes to a separate table • Compare source to target? • Effective for small tables • Poll the database for changes periodically? • Detects additions only • Use database triggers? • Developer adds to database…. or automatically generated • Requires staging table

  26. Change Data CaptureData Adapter to read from database logs • Read the database log… • Proprietary formats • Every database is different • New Data Adapter for database log tables • No actual staging table required • Synonym describes a view of database log for one table • Provides automatic checkpoint processing

  27. Change Data CaptureDatabase configuration • Platforms and databases supported • ORACLE 10g and11g on UNIX and Windows • UDB on UNIX and Windows • DB2 on IBM zSeries and iSeries • MS SQL Server 2008 • DBA issues database specific commands to • Configure database to use archiving or enable logging • Add logging or enable capture mode • Create additional synonym for table log records

  28. Create Synonym For Table Log Records

  29. Create Synonym for Table Log Records

  30. Synonym created for Log TableThree CDC columns added

  31. Synonym for Log TableLayout same as source table… plus three columns • CDC_OPER – Operation Type • I = Insert • U = Update • D = Delete • CDC_TID – Transaction ID • CDC_TIMES – Timestamp

  32. Create Synonym for Log TableCDC Configuration • Polling interval • Timeout • Checkpoint processing

  33. CDCConfiguration • POLLING – Log polling interval for LUW • TIMEOUT– Timeout interval polling for LUW • START – CHKPT after last checkpoint saved • CHECKPT_SAVE [YES|NO] – save last checkpoint in file • CHECKPT_FILE– Location and name of file • MAX_LUWS– Maximum number of LUWs processed

  34. UDBSynonym for Log Table

  35. Data FlowLog table as source

  36. Column Selection

  37. Target Transformations

  38. Enabled for IUD ProcessingIUD control column identified

  39. UDB CDCStart with Source and Target the same Run program to update source

  40. CDC Flow Log shows rows inserted / updated / deleted

  41. Change Data CaptureSource and Target now have same rows

  42. Change Data CaptureReprocessing • For testing or reprocessing…Start at specified timestamp

  43. File Listener Process files as they arrive in a directory… posted or copied or program output

  44. Refactoring • 7.6 Listener based • Required creating and managing Listener • Only Direct Load flows • Only fixed length files, only character data • Flow never completed… could not view logcould not send email or run process flow • Could not schedule flows • 7.7 Adapter based • All specifications in synonym, simpler to set up • Either SQL or Direct Load flows • Any file type, any data type • Flow completes when file or time limit reached…Can send email or run flow or procedure on completion • Can schedule flows as needed

  45. File ListenerCreate Sample Data

  46. File Listener ParametersSynonym - dmordfl

  47. Data Allocation Parameters • Connection – Name of a connection for an FTP Server • Data Origin • FILE – one time read • LISTENER – process files as they arrive • TAIL – future development • Identify File(s) • Directory – Application Directory to poll for files • Name – file name, wildcards allowed • Extension – file type or extension

  48. Listening Parameters and Read Limits • Listening • Polling – interval in seconds • Timeout – time in seconds. If no new files arrive end job • Read Limits • Maxfiles – Maximum number of data files • Maxrecs – Maximum number of records (only for TAIL)

  49. Pre and Post Processing • Pre Processing - Pickup • IMMEDIATE – As soon as files arrives • TRIGGER – Wait for trigger file with • EXTENSION • Post Processing - Discard • DELETE • ARCHIVE – Copy files to • DIRECTORY • KEEP – Delete trigger only and keep file

  50. File Name Field with Alias of INSTANCE

More Related