5 Replies Latest reply: Jul 18, 2013 4:46 AM by _Phil RSS

    Data Loss in DB to DB Transformation in ODI

    Anindya Gayen

      Hi,

      I am facing data loss when I am trying a transformation for a DB to DB mapping in ODI.

       

      I have two tables in two different schemas with the following specifications. In ODI designer model of i have put the type of place as number in target and place as varchar2 for source and accordingly done the mapping.It works successfully when i am putting the data as ('12', 'ani', '12000', '55').

      Now for testing I am giving the datas as ('1', 'ani', '12000', '55') and ('2', 'priya', '15000', '65t') and when I am executing it is giving the error as expected(ORA-01722: invalid number) in the task (Insert flow into I$ table). My C$ table is populated with the datas from source. But E$,I$ and target tables are not populated with the data.

       

      Now when I am puttting data in source as ('3', 'shubham', '12000', '56') and ('4', 'shan', '12000', '59') it is getting completed successfully , datas from C$ tables are deleted and data is inserted into the target table.

       

      Now my question is where are the datas ('1', 'ani', '12000', '55') and ('2', 'priya', '15000', '65t') gone. If they are lost what is the recoverable table so that no data loss takes place.

       

      The codes for source and target tables are as follows:

       

       

       

      source table code:

       

      CREATE TABLE "DEF"."SOURCE_TEST"

       

        (

       

          "EMP_ID"   NUMBER(9,0),

       

          "EMP_NAME" VARCHAR2(20 BYTE),

       

          "SAL"      NUMBER(9,0),

       

          "PLACE"    VARCHAR2(10 BYTE),

       

          PRIMARY KEY ("EMP_ID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" ENABLE

       

        )

       

      inserted data:

       

      INSERT INTO "DEF"."SOURCE_TEST" (EMP_ID, EMP_NAME, SAL, PLACE) VALUES ('1', 'ani', '12000', '55')

       

      INSERT INTO "DEF"."SOURCE_TEST" (EMP_ID, EMP_NAME, SAL, PLACE) VALUES ('2', 'priya', '15000', '65t')

       

      Target table code:

       

      CREATE TABLE "ABC"."TARGET_TEST"

       

        (

       

          "EMP_ID"     NUMBER(9,0),

       

          "EMP_NAME"   VARCHAR2(20 BYTE),

       

          "YEARLY_SAL" NUMBER(9,0),

       

          "BONUS"      NUMBER(9,0),

       

          "PLACE"      NUMBER(9,0),

       

          PRIMARY KEY ("EMP_ID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" ENABLE

       

      )

       

       

      Thanks.

        • 1. Re: Data Loss in DB to DB Transformation in ODI
          Yogesh_RAJ

          you should change IKM option 'Delete All' to false. It will keep all the data in C$ table.

           

          Thanks

          • 2. Re: Data Loss in DB to DB Transformation in ODI
            Anindya Gayen

            Hi Yogesh,

             

            Thank you for your reply.

            But my problem still persists.

            Am trying to clear out my question again..

             

            Suppose there are two datas in source and when the interface is ran, it got errored out and is stored in C$ table. Now in midst of this the source table is populated with new data and the interface is executed and gets finished successfully. When this thing happens the data that were previously present in source table ( which is inserted in C$ table due to error), gets lost.

            So is there any way out so that this data loss can be handled?

            • 3. Re: Data Loss in DB to DB Transformation in ODI
              _Phil

              Rows don't get loaded into the C$ table because they contain errors - they get loaded to the E$ table.  Check the E$ table for your errors.

               

              The C$ table is the "work" table - records from your source table ("DEF"."SOURCE_TEST") are loaded here first before checks and flows are carried out.  If an interface completes successfully there is no reason for data to be persisted in the C$ table.

              • 4. Re: Data Loss in DB to DB Transformation in ODI
                Anindya Gayen

                Hi,

                 

                Thank you for the reply.

                On successful completion of the interface, the data will move on from C$ table, then where will be my old data present.(The source is populated with new data).

                So we having data loss over here. How to recover this data loss.

                • 5. Re: Data Loss in DB to DB Transformation in ODI
                  _Phil

                  So, first you have data in "DEF"."SOURCE_TEST".

                  You then run your interface, and the data is moved into "ABC"."TARGET_TEST" if the interface executes successfully with no errors.

                   

                  Correct? - no data loss

                   

                  But if you're saying that you need to handle records which are going to cause the "invalid number" error, then you should read up on 'flow' and 'static' control and how to flag errors before loading them. Flow and Static Control allows ODI to identify erroneous records prior to loading - they'll be put in the E$ table for you to deal with later.

                   

                  If you haven't already, I'd encourage you to take a look at the documentation on this:

                   

                  Implementing Data Quality Control