top button
Flag Notify
Site Registration

Informatica: Which gives more performance when compare to fixed width and delimited file ? and why?

+1 vote
389 views
Informatica: Which gives more performance when compare to fixed width and delimited file ? and why?
posted Sep 16, 2015 by Amit Sharma

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

1 Answer

0 votes

Fixed width,because there are no delimiters to check so the performance will increase.

It all depends on data and what you are ultimately doing with that data. Any consumer of that data will ultimately want to break down that data into fields. During the break down of those fields, you will need to check for a maximum field size as you read through the data stream.

So if you have an 80 character fixed width field, you will ultimately have 80 comparisons to see if you have reached the field max.

Delimited break down of data requires not only a check for the field max, but also a check for the end of field delimiter.

So if your delimited field is only 20 characters long, you will have only 40 comparisons made across those 20 characters you just processed.

So, the more padding you have in your fields, the less efficient fixed width becomes when processing.

An additional factor is actual bytes that are necessary to be transmitted. In almost all cases, delimited will require less character having to be transmitted.

answer Sep 16, 2015 by Manikandan J
Similar Questions
0 votes

I have simple informatica(9.1) mapping(one to one) which loads data from flat file to RDBMS

it take 5 mins to load to Oracle db and 20 mins to load same file in SQL Server 2008 R2.

Can there be any source/pointers for performance improvement

+1 vote

I am using COBOL file as source where 01 level groups are two. Following are the details.

In Output file first and last row are required and middle two rows are extra. Am I missing any setting or there is some other error.

Input data File:

2014001100450005000000001141107TD2798600000200120011201400090029+000000000024850+000000000000000+000000000000000000CATALOG    SCTEST TEST                     12 MAIN ST                         HINGHAM                  MA **********-111-111100000000000000000000000000000000000000000000000040000000000000001        0000 00000002786354800000000000000064486448
2015001000440007123456789123456789ABCD301088+123456789+1234567891234123456789ABCDEZZ1234ABCD12341234567890ABCDEFBCD**********ABCDEF12341234

OutPut file:

2014,11,45,5,2014001,100450005,1,141107TD27986,14,1107T,D27986,141107,TD,279,86,000,002,12,11,2014,9,29,248.50,0.00,0.000000000,CATALOG, , ,  ,SC,TEST TEST ,                    ,12 MAIN ST                         ,HINGHAM                  ,MA ,020430000,2043,0,020430,000,111-111-1111,0,0,0,0,0,0,4,0,0,0,1,        ,0, ,27863548,0,6448,6448
,2014,11,45,5,2014001,100450005,1,141107TD27986,14,1107T,D27986,141107,TD,279,86,000,002,120011.20,14000900.29,0,24,24,24,24,24,850+0,0,0,00000000,0,+000000000000000,000CATALOG    SCTES,,
2015,10,44,7,2015001,440007,123456789,123456789ABCD,12,34567,89ABCD,123456,78,9AB,CD,301,088,123,4567,8901,2345,6789,,,,1234567,8,9,0A,BC,DEF1234123,4,,,,,,,,,,,,,,,,,,,,,,,,,,,,
2015,10,44,7,2015001,440007,123456789,123456789ABCD,12,34567,89ABCD,123456,78,9AB,CD,301,088,1234567.89,1234567.89,1234,123456789,123456789,123456789,123456789,123456789,ABCDE,Z,Z,1234ABCD,1234,**********ABCDEF,BCD**********ABCDEF,1234,1234

Desired Output:

2014,11,45,5,2014001,100450005,1,141107TD27986,14,1107T,D27986,141107,TD,279,86,000,002,12,11,2014,9,29,248.50,0.00,0.000000000,CATALOG, , ,  ,SC,TEST TEST ,                    ,12 MAIN ST                         ,HINGHAM                  ,MA ,020430000,2043,0,020430,000,111-111-1111,0,0,0,0,0,0,4,0,0,0,1,        ,0, ,27863548,0,6448,6448
2015,10,44,7,2015001,440007,123456789,123456789ABCD,12,34567,89ABCD,123456,78,9AB,CD,301,088,1234567.89,1234567.89,1234,123456789,123456789,123456789,123456789,123456789,ABCDE,Z,Z,1234ABCD,1234,**********ABCDEF,BCD**********ABCDEF,1234,1234

 Source File:
        environment division.
        select SAHDR-SAADMIN assign to "fname".
        data division.
        file section.
        fd  SAHDR-SAADMIN.
        01  STSHDR-RECORD.
        05  SAHDR-KEY.
        10  SAHDR-FISCAL-POSTING-DATE.
            15  SAHDR-FISCAL-YEAR            PIC  9(04).
            15  SAHDR-FISCAL-MONTH           PIC  9(04).
            15  SAHDR-FISCAL-WEEK            PIC  9(04).
            15  SAHDR-FISCAL-DAY             PIC  9(04).
        10  SAHDR-RELATIVE-DATE
                REDEFINES SAHDR-FISCAL-POSTING-DATE.
            15  SAHDR-DAY-IDNT               PIC  9(07).
            15  SAHDR-FILLER                 PIC  9(09).
        10  SAHDR-STORE-NUMBER               PIC  9(09).
        10  SAHDR-TRANSACTION-NUMBER-KEY     PIC  X(13).
        10  SAHDR-TRANSACTION-NUMBER
                REDEFINES SAHDR-TRANSACTION-NUMBER-KEY.
            15  SAHDR-REGISTER-NUMBER        PIC  X(02).
            15  SAHDR-TRANS-NUMBER           PIC  X(05).
            15  SAHDR-TRANS-SORT             PIC  X(06).
        10  SAHDR-MO-TRANS-NUMBER
                REDEFINES SAHDR-TRANSACTION-NUMBER-KEY.
            15  SAHDR-MO-ORDER-DATE          PIC  X(06).
            15  SAHDR-MO-DEPT-CLERK          PIC  X(02).
            15  SAHDR-MO-ORDER-BATCH         PIC  X(03).
            15  SAHDR-MO-ORDER-SEQ-NUMBER    PIC  X(02).
        10  SAHDR-RECORD-TYPE                PIC  X(03).
            88  SAHDR-HEADER-RECORD            VALUE '000'.
        10  SAHDR-TRANS-TYPE                 PIC  X(03).
            88  SAHDR-SALE-TRANSACTION         VALUE '001'.
        05  SAHDR-DATA.
        10  SAHDR-TRANSACTION-DATE.
            15  SAHDR-CALENDAR-MONTH         PIC  9(04).
            15  SAHDR-CALENDAR-DAY           PIC  9(04).
            15  SAHDR-CALENDAR-YEAR          PIC  9(04).
        10  SAHDR-TRANSACTION-TIME.
            15  SAHDR-REG-TRANS-HOUR         PIC  9(04).
            15  SAHDR-REG-TRANS-MINUTE       PIC  9(04).
        10  SAHDR-TOTAL-TRANS-AMOUNT         PIC +9(13)V99.
        10  SAHDR-CONVERSION-TRANS-AMOUNT    PIC +9(13)V99.
        10  SAHDR-CONVERSION-RATE            PIC +9(09)V9(9).
        10  SAHDR-TRANS-ORIGIN               PIC  X(07).
            88  SAHDR-POINT-OF-SALE            VALUE 'POS    '.
            88  SAHDR-MAILORDER                VALUE 'CATALOG'.
        10  SAHDR-TRANS-VOID-DURING-FLAG     PIC  X(01).
        10  SAHDR-TRANS-POST-VOID-FLAG       PIC  X(01).
        10  SAHDR-TRANS-ERROR-CODE           PIC  X(02).
            88  SAHDR-NO-ERRORS                VALUE '00'.
        10  SAHDR-RFS-LOCATION-TYPE          PIC  X(02).
        10  SAHDR-CUSTOMER-NAME.
            15  SAHDR-CUSTOMER-FNAME         PIC  X(10).
            15  SAHDR-CUSTOMER-LNAME         PIC  X(20).
        10  SAHDR-CUSTOMER-ADDRESS           PIC  X(35).
        10  SAHDR-CUSTOMER-CITY              PIC  X(25).
        10  SAHDR-CUSTOMER-STATE             PIC  X(03).
        10  SAHDR-CUSTOMER-ZIPCODE           PIC  X(09).
        10  SAHDR-USA-ZIPCODE
                REDEFINES SAHDR-CUSTOMER-ZIPCODE.
            15  SAHDR-CUSTOMER-FIRST-FIVE    PIC  9(05).
            15  SAHDR-CUSTOMER-LAST-FOUR     PIC  9(04).
        10  SAHDR-CANADA-ZIPCODE
                REDEFINES SAHDR-CUSTOMER-ZIPCODE.
            15  SAHDR-CANADA-CUST-ZIP        PIC  X(06).
            15  FILLER                       PIC  X(03).
        10  SAHDR-CUSTOMER-PHONE             PIC  X(12).
        10  SAHDR-SALESPERSON.
            15  SAHDR-CASHIER                PIC  9(07).
            15  SAHDR-HEADER-SALESPERSON     PIC  9(07).
        10  SAHDR-EMPLOYEE-SELLING-NUMBER    PIC  9(09).
        10  SAHDR-EMPLOYEE-PURCHASE-NUMBER   PIC  9(09).
        10  SAHDR-SHIPPING-RECORD-NUMBER     PIC  9(09).
        10  SAHDR-ADMIN-COUNTER              PIC  9(04).
        10  SAHDR-ITEM-COUNTER               PIC  9(04).
        10  SAHDR-REGTOT-COUNTER             PIC  9(04).
        10  SAHDR-STRTOT-COUNTER             PIC  9(04).
        10  SAHDR-TAXRCD-COUNTER             PIC  9(04).
        10  SAHDR-TENDER-COUNTER             PIC  9(04).
        10  SAHDR-USERID                     PIC  X(08).
        10  SAHDR-EMP-DEPT                   PIC  9(04).
        10  SAHDR-ERROR-CODE                 PIC  X(01).
        10  SAHDR-CUSTOMER-ID                PIC  9(15).
        10  SAHDR-LOYALTY-ID                 PIC  9(15).
        10  SAHDR-TRANS-TIME.
            15  SAHDR-REG-TRANS-HR           PIC  9(04).
            15  SAHDR-REG-TRANS-MIN          PIC  9(04).
        01  STSADMIN-RECORD.
        05  SAADMIN-KEY.
        10  SAADMIN-FISCAL-POSTING-DATE.
            15  SAADMIN-FISCAL-YEAR          PIC  9(04).
            15  SAADMIN-FISCAL-MONTH         PIC  9(04).
            15  SAADMIN-FISCAL-WEEK          PIC  9(04).
            15  SAADMIN-FISCAL-DAY           PIC  9(04).
        10  SAADMIN-RELATIVE-DATE
                REDEFINES SAADMIN-FISCAL-POSTING-DATE.
            15  SAADMIN-DAY-IDNT             PIC  9(07).
            15  SAADMIN-FILLER               PIC  9(09).
        10  SAADMIN-STORE-NUMBER             PIC  9(09).
        10  SAADMIN-TRANSACTION-NUMBER-KEY   PIC  X(13).
        10  SAADMIN-TRANSACTION-NUMBER
                REDEFINES SAADMIN-TRANSACTION-NUMBER-KEY.
            15  SAADMIN-REGISTER-NUMBER      PIC  X(02).
            15  SAADMIN-TRANS-NUMBER         PIC  X(05).
            15  SAADMIN-TRANS-SORT           PIC  X(06).
        10  SAADMIN-MO-TRANSACTION-NUMBER
                REDEFINES SAADMIN-TRANSACTION-NUMBER-KEY.
            15  SAADMIN-MO-ORDER-DATE        PIC  X(06).
            15  SAADMIN-MO-DEPT-CLERK        PIC  X(02).
            15  SAADMIN-MO-ORDER-BATCH       PIC  X(03).
            15  SAADMIN-MO-ORDER-SEQ-NUMBER  PIC  X(02).
        10  SAADMIN-RECORD-TYPE              PIC  X(03).
            88  SAADMIN-ADMINISTRATIVE         VALUE '301'.
        10  SAADMIN-TRANSACTION-TYPE         PIC  X(03).
            88  SAADMIN-POST-VOID              VALUE '088'.
        05  SAADMIN-DATA.
        10  SAADMIN-AMOUNT                   PIC +9(07)V99.
        10  SAADMIN-CONVERSION-AMOUNT        PIC +9(07)V99.
        10  SAADMIN-POS-EXPENSE-NUMBER       PIC  9(04).
        10  SAADMIN-TRANS-ID-NUMBER          PIC  9(09).
        10  SAADMIN-CHARGE-ACCOUNT-NUMBER      REDEFINES
            SAADMIN-TRANS-ID-NUMBER          PIC  9(09).
        10  SAADMIN-GIFT-CERTIFICATE           REDEFINES
            SAADMIN-TRANS-ID-NUMBER          PIC  9(09).
        10  SAADMIN-MDSE-CREDIT-NUMBER         REDEFINES
            SAADMIN-TRANS-ID-NUMBER          PIC  9(09).
        10  SAADMIN-COUPON-NUMBER              REDEFINES
            SAADMIN-TRANS-ID-NUMBER          PIC  9(09).
        10  SAADMIN-ORIGINAL-TRAN-NUMBER     PIC  X(05).
        10  SAADMIN-VOID-DURING-FLAG         PIC  X(01).
        10  SAADMIN-POST-VOID-FLAG           PIC  X(01).
        10  SAADMIN-USERID                   PIC  X(08).
        10  SAADMIN-EMP-DEPT                 PIC  9(04).
        10  SAADMIN-GIFT-CARD-NUMBER         PIC  X(16).
        10  SAADMIN-TOKEN-NUMBER             PIC  X(19).
        10  SAADMIN-TRANS-TIME.
            15  SAADMIN-REG-TRANS-HR         PIC  9(04).
            15  SAADMIN-REG-TRANS-MIN        PIC  9(04).
        working-storage section.
        procedure division.
        stop run.
+1 vote

I have developed an Informatica PowerDesigner 9.1 ETL Job which uses lookup and an update transform to detect if the target table has the the incoming rows from the source or not. I have set for the Update transform a condition

IIF(ISNULL(target_table_surrogate_id), DD_INSERT, DD_REJECT)

Now, when the incoming row is already in the target table, the row is rejected. Informatica writes these rejected rows into a .bad file. How to prevent this? Is there a way to determine that the rejected rows are not written into a .bad file? Or should I use e.g. a router insted of an update transform to determine if the row is insert row an then discard the other rows?

...