Transcript 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?
A software system designed to support interoperable machineto-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
Some sources of Public Web Services
Free and commercial
.net
Publicly Available Commercial Web Services
WSDLL.COM
Web Services – Free and Commercial
Xmethods.net
List of public web services
Seekda.com
Find WSDL for PhoneVerify Service from cdyne.com
Add adapter for Web Services
Add Connection for a Web Service
Add Connection for Web Service
Create Synonym
Open Synonym
Sample Data
Supply values for PHONENUMBER and LICENSEKEY
Sample Data
Show all fields returned by Web Service
Create Data Flow
Add input file
User Defined Function
Cleans up phone number
Add transformation
Clean up phone number and assign name
Add synonym for Web Service
Join input file to Web Service
On PHONENUMBER and LICENSEKEY
Select Columns from Web Service
Test SQL
Shows “join” from input file to Web Service
Change Data Capture
When would you want to capture changes?
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…
Change Data Capture
How 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
Change Data Capture
Data 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
Change Data Capture
Database 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
Create Synonym
For Table Log Records
Create Synonym
for Table Log Records
Synonym created for Log Table
Three CDC columns added
Synonym for Log Table
Layout same as source table… plus three columns
 CDC_OPER – Operation Type
 I = Insert
 U = Update
 D = Delete
 CDC_TID – Transaction ID
 CDC_TIMES – Timestamp
Create Synonym for Log Table
CDC Configuration
 Polling interval
 Timeout
 Checkpoint processing
CDC
Configuration
 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
UDB
Synonym for Log Table
Data Flow
Log table as source
Column Selection
Target Transformations
Enabled for IUD Processing
IUD control column identified
UDB CDC
Start with Source and Target the same
Run program to update source
CDC Flow
Log shows rows inserted / updated / deleted
Change Data Capture
Source and Target now have same rows
Change Data Capture
Reprocessing
 For testing or reprocessing…
Start at specified timestamp
File Listener
Process files as they arrive in a directory…
posted or copied or program output
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 log

could 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
File Listener
Create Sample Data
File Listener Parameters
Synonym - dmordfl
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
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)
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
File Name
Field with Alias of INSTANCE
SQL or Direct Load Flow
… looks like any other flow
File Listener
Create directories listen and archive
File Listener
Submit the Flow – Waits for TIMEOUT interval
File Listener
Drop the Files
File Listener
Drop the Files - within timeout interval (60 seconds)
File Listener
Sample Data
Log shows number of rows processed
For all files
IWAF - iWay Adapter Framework
iWay Adapter Framework
Application Adapters
 Data Adapters for some ERP systems
 New support for Application Adapters including
 Salesforce.com
 MySAP
 Seibel
 Peoplesoft
 So that DataMigrator can use
 Services
 Events
Configure IWAF Adapter
Add IWAF Adapter
Select IWAF Adapter
Add Connection for IWAF
Salesforce Service
Salesforce Adapter
Create Synonym
Salesforce Adapter
Create Synonym
Salesforce Adapter
Create Synonym
Create Synonym for Salesforce Service
Salesforce Service
Sample Data
Salesforce Event
Synonym
Salesforce
Data Flow
Salesforce.com
Salesforce
Output table and log