Informatica PowerExchange 9.X

1. InformaticaPowerExchange 9.X Training | CIF CONSULT | Redouane BELBAHRI

2. Agenda • Objectives • What is PowerExchange • How Does the Team typically use PWX • Power Exchange Batch Architecture • Real Examples

3. Agenda Objectives 1

4. Agenda • This course explains what PowerExchange is and how it is used. We will learn the Architetue (batch) of PowerEXchange, and the key components • Undestand the PowerExchange Architecture • Describe How PWX Datamaps are used and where they are stored • Create a basic Datamap in the PWX Navigator GUI

5. Agenda What is PowerExchange 2

6. Agenda • Power Exchange (PWX) is a tool we use to parse complex files and pull them into Designer. • Power Exchange (PWX) is comprised of 3 main components • Change Data Capture – Allows you to monitor and retrieve changes that have occurred to data in which you have expressed an interest. • Data Maps – Used to access data sources like IMS databases, VSAM files and flat files. • Personal Metadata - Allows you to browse meta data from remote databases. Remark: Change Data Capture (CDC) and Personal Metadata are out of scope in this presentation

7. PowerExchange Batch Platforms

8. PowerExchangeChange Capture (CDC)

9. Agenda How Does the Team typically use PWX 3

10. How Does the Team typically use PWX • Complex Flat File Parsing • Complex copybooks with multiple record types • Creating multiple records out of occurs depending on or blowing them out to the full range of the occurs • Verifying that the layout is correct with a row test • Data from the row test can be copied into a text file and then imported into Excel as a comma delimited file. • Making sure that the input data can be seen correctly by PWX • Interaction with Power Center • Pulling Sources and Targets set up with PWX into Informatica Designer

11. When to use PWX When to Use PowerCenter When to Use PowerExchange • PowerCenter supports only fixed width files,Informatica VSAM reader does not support variable length file. • This file is read using a PowerCenter Normalizer transformation. • PowerCenter does not support the Occurs Depending On clause. • PowerCenter supports comp, comp-3, comp-6 and comp-x fields. • However, PowerExchange has a capability of reading the variable length VSAM file. • In PowerExchange create a data map for the VSAM file by importing the COBOL copybook.

12. Agenda Power Exchange Batch Architecture 4 • How Does PWX work?

13. Where PWX is installed ?

14. Informatica PowerExchangeDesign Time - Batch Architecture

15. PowerExchange CDC ArchitectureFor Oracle (option available at AXA) • PowerExchange Change Data Capture (CDC) works in conjunction with PowerCenter to capture changes to data in source tables and replicate those changes to target tables and files.

16. Traditional Methods for Moving Data Multiple manual steps for each source database!

17. PowerExchange Approach • No Programming, No Intermediate Files • Data extracted with SQL, converted and filtered • Loaded to target in memory • Restart and Recovery integration with PowerCenter

18. What is a Datamap ? • A Datamap defines metadata • For non-relational databases, need to define metadata • Contains all information regarding the underlying dataset • Created from existing information • VSAM – copybooks • IMS – DBDs, then overlay segment copybooks • ADABAS – FDT • IDMS – schemas • Flattens out hierarchical or multi-record type data • Stored on the same platform as the data • Not needed for relational sources • Provides the information to convert an SQL statement to actual data base requests

19. Where is the Datamap stored? • Source version of datamap is stored on windows machine where created • Datamaps folder, (dmp file on windows) • Run time version is stored with the PWX software on the source data machine • Datamaps folder, (dmp file type on windows) • Install.lib.DATAMAPS sequential file on MVS • Stdatamaps lib on AS400 • Run time version can be imported into any windows session of PWX Navigator to pull in most recent copy • Refer to the Navigator User Guide for more information

20. Different types of data and where they are stored • The 'compiled' version of the data map, in this case ‘test.EBCDIC.dmx' is stored in the 'datamaps' folder directly under the product install directory on the system where it will be used by the product to access the data. It is sent when you see a message in Navigator that says that information must be sent to the 'remote system' in order to retrieve data. • There is also a lock file for the customer data map. It is 'customer.lck' and is also stored in the Shared folder. It is used to serialize access to the data map source.

21. Sample dbmover.cfg file Configuration File • Configuration File • Sets many defaults parameters for PowerExchange • Provide security settings • Communication Settings • Config File Communication Params: • LISTENER • Node Name • TCPIP (standard parm) • Port Number • NODE • Node name • TCPIP (standard parm • Host Name or IP address • Port Number

22. What is a Listener? • A PowerExchange task that accept requests for data • Uses an IP address and Port Number • Can listen on multiple Port numbers at the same time • Can have multiple listeners running (on unique IP and Ports) • Only needed on remote platforms for access to batch data • Also used for connecting to remote PowerExchange Nodes for creating datamaps and Capture Registrations

23. Listener = Phone lineEasy way to remember • The Listener is the equivalent of the phone number that defines a number, whitch is the IP address and Port, once turned on can be called from other places.

24. Node = Address Book NumbersEasy way to remember Node statement gives all the available numbers we can call from this location

25. Listener and Node Example

26. Agenda Real Examples 5

27. Examples • Fixed Length File • Variable Length File • Variable Length File with multiple record types

28. Connection to PWX Navigator Demonstration Of PWX From the Taskbar 1 Connection to PowerExchange client tool OR 1 Note: No need to use a Login and password to access Desktop icon 2 Acces Homepage

29. Example 1 – Fixed length VSAM file Demonstration Of PWX • Create Data Map that convert an EBCDIC file to ASCII file format: • Add a Data Map • Fill in the Schema name and the Data Map Name • Insert the Input File name and it’s location on the server, Select the fixed option, & change the default line to EBCDIC & IBM-1047 • Enter the name of the source file copybook

30. Prepare the Cobol file structure Demonstration Of PWX Remove this section The Cobol file structure you are being to use should not contains the header informations, if not later you will have this error

31. Add a data map Demonstration Of PWX • Right click on the data map folder to add a data map.

32. Fill in the Schema Name and the Data Map Name Demonstration Of PWX Fill in the Schema Name and the Data Map Name Access Method: SEQ (for Flat file) Click next

33. Data Map PropertiesLocation and type of copybook that you want to import. Demonstration Of PWX Insert the file name (data file) that the map will need to read in Informatica (this file will be on the server). Select fixed or variable depending on the type of file. In the Size box, enter the record length (if not known give 1 as size, will correct later). The screen print above is for a fixed file only. Click Finish

34. Import Copybook – Source Details Demonstration Of PWX Column Range: Enter the start and end column range for the copybook: Source: Local. The copybook is located on the PowerExchange Navigator system. Remote. The copybook is located on a remote system. Enter information about the location and type of metadata that you want to import. Click Next

35. Import Copybook – Local Cobol Details Demonstration Of PWX Tell PowerExchange where you have your copybook located on your PC Click next.

36. Import Copybook – Configuration Details Demonstration Of PWX Click Finish

37. Import Copybook Information Demonstration Of PWX Click OK

38. Record Definition Demonstration Of PWX Click OK

39. Add a data map Physical definition mapped to the relational definition Demonstration Of PWX Table Record Click OK

40. Add a data map (EBCDIC) Demonstration Of PWX Click Close Make a note of the file actual length Click yes

41. Add a data map (EBCDIC) Demonstration Of PWX Right click on Test.EBCDIC in the resource explorer (Data Map) and select properties. Select the SEQ Access Method Tab and change the length to 350.

42. Check the Created Data mapEdit Resource Configuration Demonstration Of PWX • A PowerExchange Navigator resource configuration defines the location of data maps, personal metadata profiles,and any locally stored capture registrations and extraction maps. • Note: You cannot edit the resource configuration if a PowerExchange resource is open.   

43. Add a data map (EBCDIC)Example with Occurs Demonstration Of PWX

44. Send data map to Node Demonstration Of PWX  See dbmover.cfg on the local machine  

45. Perform a Row Test (1/3) Demonstration Of PWX  Select the Table and Click Row Test In the Database Row Test dialog box select NRDB (Nonrelational data source ) Enter the name of the file in the Override File Name box (file should be in the server) Click Go

46. Perform a Row Test (2/3) Demonstration Of PWX

47. Perform a Row TestExample with Occurs (3/3) Demonstration Of PWX

48. Created datamap on the integration Server Demonstration Of PWX

49. Import the created dmx as datamap Demonstration Of PWX   

50. Import the source into PowerCenter Designer Demonstration Of PWX Go into Informatica designer and open the Source Analyzer. Select Sources and then Select Import from PowerExchange.