Normalizer transformation: Normalizer transformation is used with COBOL sources, which are often stored in a denormalized format. The OCCURS statement in a COBOL file nests multiple records of information in a single record. We can use Normalizer transformation, to break out repeated data within a record into separate records.
For each new record it creates, the Normalizer transformation generates a unique identifier. Step 1: Create the Copybook for COBOL source First Step is to get the copybook from Mainframe Team and convert that Informatica Compliant format It will look like Normally Highlighted section is provided by Mainframe team convert it into format required by format by adding line above that code (From identification to fd FNAME) and below that code (starting from working storage division).
After changes save the file as.cbl file Point to be taken care while editing.cbl File You might get following error identification division. Environment division. Select Error at line 6: parse error Things to be taken care of 1.Line Select FNAME should not start before column position 12 2.Other line which have been added above and below should not start before column 9 3.All the line in structure (Highlighted above) should end with Dot. Once Cobol Source is imported successfully you can drag Normalizer source into mapping Step 2: Set Workflow Properties Properly for VSAM Source One you have successfully imported the COBOL copybook then you can create your mapping using VSAM Source. After creating mapping you can create your workflow Please take care of following properties in session containing VSAM source In Source Advance File properties set the following options (Highlighted one) Imp: Always ask for COBOL source file to be in Binary Format, Otherwise you will face lot of problems with COMP-3 Fields Once you have set these properties you can run your workflow.
COMP3 FIELDS: COBOL Comp-3 is a binary field type that puts ('packs') two digits into each byte, using a notation called Binary Coded Decimal, or BCD. This halves the storage requirements compared to a character, or COBOL 'display', field. Comp-3 is a common data type, even outside of COBOL Common issues faced while working with Comp-3 Fields: If you have created your created cobol source definition with comp-3 fields (Packed Data) but actual data in source file is not packed.So Make sure that in both the definition and source file date is in same format Check whether COMP-3 fields are signed or unsigned.
For creating definations manually, please select 'create' option available in Source/target desinger window and select 'VSAM'. Or if you have a datamap ready for your VSAM file, please import it from PowerExchange. Please refer the PowerEchange documention Help for steps tocreate VSAm data maps.
This mapping extracts multiple record types, such as Header, Detail, and Trailer records, from a VSAM source when the copybook contains OCCURS and REDEFINES statements. VSAM sources often de-normalize data and compact the equivalent of separate table records into a single record. OCCURS statements, for example, define repeated information in the same record. REDEFINES statements build the description of one record based on the definition of another record. Processing VSAM sources with OCCURS and REDEFINES statements requires knowledge of how the PowerCenter Normalizer transformation object handles VSAM data. The VSAM source in this example includes data about four different suppliers, each stored in the same record.
The source file contains both OCCURS and REDEFINES statements, as shown below in bold: 03 BODY-DATA. 05 BODY-STORE-NAME PIC X(30). 05 BODY-STORE-ADDR1 PIC X(30). 05 BODY-STORE-CITY PIC X(30). 03 DETAIL-DATA REDEFINES BODY-DATA. 05 DETAIL-ITEM PIC 9(9). 05 DETAIL-DESC PIC X(30).
05 DETAIL-PRICE PIC 9(4)V99. 05 DETAIL-QTY PIC 9(5).
05 SUPPLIER-INFO OCCURS 4 TIMES. 10 SUPPLIER-CODE PIC XX. Prophet Rise Again Riddim Rar there. 10 SUPPLIER-NAME PIC X(8). The mapping in this template illustrates how to use Normalizer and Router transformation objects to extract data from this VSAM file and send that data to multiple targets. Implementation Guidelines Part of the implementation for this mapping template requires an understanding of how the Source Analyzer and Target Designer treat VSAM files. When the Source Analyzer utility analyzes this VSAM file, it creates a different column for each OCCURS in the COBOL file.
The OCCURS statements define repeated information in the same record. A Normalizer transformation will be used in the mapping to normalize this information. Once the source definition is created, the Warehouse Designer utility does the following: • Creates one target table (SUPPLIER_INFO) for each OCCURS statement when you drag the COBOL source definition into the Warehouse Designer.
• Creates a primary-foreign key relationship between those tables. • Creates a generated column ID (GCID). The REDEFINES statement allows you to specify multiple PICTURE clauses for the sample physical data location. Therefore, Filter or Router transformations are required to separate data into the multiple tables created for REDEFINES statements.
For each REDEFINES statement, the Warehouse Designer utility does the following: • Creates a target table (DETAIL_DATA) when the COBOL source definition is dragged into the Warehouse Designer. • Creates one primary-foreign key relationship. The following target tables show that the Warehouse Designer creates a target table called DETAIL_DATA for the REDEFINES in the source file, as well as a target table called SUPPLIER_INFO because of the OCCURS clause. The mapping will contain logic for separating BODY_DATA, DETAIL_DATA and SUPPLIER_INFO into three target tables. BODY_DATA contains store information such as store name, store address and store city. However, the REDEFINES clause indicates that detail information about merchandise is in the same physical location as BODY_DATA. Detail information about the store's merchandise includes item, description, price and quantity.
Finally, the OCCURS clause tells you that there can be four suppliers to each item. Information about the store, the items and the suppliers are all in the same physical location in the data file. Therefore, a Router transformation is used to separate information about the store, the items and the suppliers. Notice that at the beginning of the COBOL file, header information for a store is defined with a value of H and detail information about items is defined with a value of D. Detail information about items also includes supplier information, and supplier information has a supplier code that is not null. Using this information, the following logic is used in the Router transformation to separate records: Records in the STORE_INFO_GRP are indicated by: HDR_REC_TYPE='H' Records in the ITEM_INFO_GRP are indicated by: HDR_REC_TYPS='D' Records in the SUPPLIER_INFO_GRP are indicated by: HDR_REC_TYPE='D' AND NOT ISNULL(SUPPLIER_CODE) AND SUPPLIER_CODE!=' Please refer to the metadata descriptions in the mapping m_VSAM_Sales_Data for more details on this mapping's functionality. More Information.