8 Replies Latest reply on Nov 1, 2019 10:05 AM by Prasanth G

    TT_11019 TRANSFORMATION ERROR FIX IT

    Prasanth G New Member

      INFORMATICA TRANSFORMATION ISSUE (PROD)
      My Mapping is going down with the error message code :TT_11019" and the Mapping logic is as stated below

      #

      1. Source1(DB2)------>   SOURCE QUALIFIER--->Expression_Trsnfm->TGT_Flat_file
      2. Source2(DB2)------>

       

      As of Now I have verified the source dataSTEP1: DATA looks good from the two DB2 SOURCESSTEP2: Found that The data from Source qualifier to Expression transformation populating NULL records DO know HOW Till the source qualifier data looks perfectSTEP3: Research on session properties looks GOOD I HOPE

       

      1.     Severity    Timestamp   Node    Thread  Message Code Message    
      2.     ERROR   10/21/2019 9:37:05 PM   car_nod_exyz124i    TRANSF_1_1_1    
      3.     TT_11019    There is an error in the port [EMP_ID]: The default
      4.     value for the port is set to: ERROR(Transformation error:
      5.     exp_INSTANCE).
      6.     ERROR   10/21/2019 9:37:05 PM   car_nod_exyz124i    TRANSF_1_1_1    
      7.     TT_11023    An error occurred converting data in the port
      8.     [EMP_ID]. The row was dropped.
      9.     ERROR   10/21/2019 9:37:05 PM   car_nod_exyz124i    TRANSF_1_1_1    
      10.     TT_11021    An error occurred moving data from the transformation
      11.     exp_INSTANCE: to the transformation EX655_KEY_FILE.
      12.     ERROR   10/21/2019 9:37:05 PM   car_nod_exyz124i    TRANSF_1_1_1    
      13.     CMN_1086    exp_INSTANCE: Number of errors exceeded threshold [1].
      14.     INFO    10/21/2019 9:37:05 PM   car_nod_exyz124i    TRANSF_1_1_1    
      15.     CMN_1053    : Rowdata: ( RowType=-4(error but dropped due to
      16.     abort) Src Rowid=3 Targ Rowid=3
      17.      EMP_ID (EMP_ID:Int:): "(NULL)"
      18.      MANGER_ID (MANAGER_ID:UniChar.17:): "20190917174013514"
      19.      DEPT_ID (DEPT_ID:Int:): "(NULL)"
      20.      OUT_XYZ_ID (XYZ_ID:UniChar.44:): "  "
      21.      OUT_MU_PARTY_BUSN_ID (MU_PARTY_BUSN_ID:Int:): "0"
      22.      BUSN_ID (BUSN_ID:Int:): "2435126"
      23.      OUT_MOBILE_NUM (MOBILE_NUM:UniChar.30:): "  "
      24.      OUT_STATE_CD (STATE_CD:UniChar.6:): "  "
      25.      HIRE_DT (HIRE_DT:UniChar.10:): "(NULL)"
      26.      OUT_DIST_TYP_CD (DIST_TYP_CD:UniChar.1:): " (TRUNCATED)"
      27.      STATE_NO (STATE_NO:UniChar.2:): "16"
      28.      STATUS_ID (QUOTE_APP_STATUS_ID:UniChar.2:): "CQ"
      29.      SECTOR_TYP_CD(APP_TYPE_CD:UniChar.1:): "N"
      30.      VACANCY_IND (PLCY_INQ_QUOTE_IND:UniChar.1:): "U"
      31.      OUT_JOIN_DT (JOIN_DT:UniChar.10:): "01/01/0001(TRUNCATED)"
      32.      OUT_EFF_DT (EFF_DT:UniChar.10:): "(NULL)"
      33.      REGION_ID(REGION_ID:Int:): "2430919"
      34.      )
      35.     ERROR   10/21/2019 9:37:05 PM   car_nod_exyz124i    TRANSF_1_1_1    
      36.     TM_6085 A fatal error occurred at  transformation [exp_INSTANCE], and
      37.     the session is terminating.
      38.     ERROR   10/21/2019 9:37:05 PM   car_nod_exyz124i    TRANSF_1_1_1    
      39.     TM_6085 A fatal error occurred at  transformation
      40.     [SQ_DB2SRC1_DB2SRC2], and the session is terminating.
      41.     ERROR   10/21/2019 9:37:05 PM   car_nod_exyz124i    TRANSF_1_1_1    
      42.     TM_6085 A fatal error occurred at  transformation
      43.     [SQ_DB2SRC1_DB2SRC2], and the session is terminating.
      44.     INFO    10/21/2019 9:37:05 PM   car_nod_exyz124i    WRITER_1_*_1    
      45.     WRT_8333    Rolling back all the targets due to fatal session
      46.     error.
      47.     ERROR   10/21/2019 9:37:05 PM   car_nod_exyz124i    WRT_8170    
      48.     Writer run terminated: Abort Session request received from the DTM*

       

      "I would like to resolve this issue and want to know the root cause"

       

      NOTE::::: FOR THE FIELD EMP_ID IN Expression transformation. I dint mention default value for this field as ERROR('transformation error')

      then why its going down for that particular field with the default value error which I didnt even mention in the expression transformation. Please I would like to know your VALUABLE THOUGHTS

       

       

        • 1. Re: TT_11019 TRANSFORMATION ERROR FIX IT
          Vlad Ponomarenko Guru

          Hi,

          I suppose that issue in EMP_ID field due to

          ...There is an error in the port [EMP_ID]...

           

          Is it really integer in all rows?

          Regards, Vlad

          • 2. Re: TT_11019 TRANSFORMATION ERROR FIX IT
            Prasanth G New Member

              Hi Vladislav,

            yes all the mapping level the incoming data is integer only.Verified the data by sql tuning at source level and source qualifier level data is passing integer only.please have a close look into the details below and let me know your valuable thoughts    

            DB2 source 1
            PortNameDataTypePrecScaleIO
            EMP_IDbigint19001

             

            DB2source2
            PortNameDataTypePrecScaleIO
            EMP_IDbigint19001

             

             

            source qualifier
            PortNameDataTypePrecScaleIO
            EMP_IDbigint19011

             

                  

                    

             

            Expression Transformation

            PortNameDataTypePrecScaleIOVEXPRESSION
            EMP_IDbigint190110EMP_ID

             

              

            TGT_FLAT_FILE
            PortNameDataTypePrecScaleIO
            EMP_IDINT15010
            • 3. Re: TT_11019 TRANSFORMATION ERROR FIX IT
              Nico Heinze Guru

              One potential reason for such a mapping breaking down is that the EMP_ID attribute in DB2 is of some character type and does contain only numeric values, but one or more of these numeric vallues are too large to fit into an Integer port in PowerCenter.

              An integer value may range from -2,147,483,648 to +2,147,483,647, no less, no more. If, for example, the EMP_ID field contains one value 2,245,177,690, then this value woud be simply too large for an Integer port, causing this kind of trouble.

               

              Regards,

              Nico

              • 4. Re: TT_11019 TRANSFORMATION ERROR FIX IT
                Nico Heinze Guru

                Hi Prasanth,

                 

                I just read your reply the other second, so please disregard my first reply.

                It may be (as the target flat file has only a precision of 15 digits whereas a BIGINT has a precision of app. 19 digits) that some of the EMP_ID values from the source are too large to fit into the target field EMP_ID.

                Can you please check whether there are any source EMP_ID values larger than 999,999,999,999,999? That would explain this problem.

                 

                Regards,

                Nico

                1 of 1 people found this helpful
                • 5. Re: TT_11019 TRANSFORMATION ERROR FIX IT
                  Prasanth G New Member

                  Hello Nico Heinze,

                       Good morning. I will agree with your statements.But to find the potential impacts and analysis I would like share the below details  with the data  and let me know your insights

                   

                  EMPID at (DB2 Source level:Datatype: BigInt): 2151981217

                   

                  EMPID at (Target Flatfile:Datatype: int prec(15)scale(0)): rejecting at the mapping level with TT_11019

                   

                  So as you said at the target the int EMP_ID is greater than 2147483647 THATS WHY ITS ABENDING RIGHT?

                   

                  NOTE: EMPID at (DB2 Source level:Datatype: BigInt): 2151981217 tHIS IS FIRST TIME Ever it qualified at the SOurce qualifier (SQL OVERRIDE) condition and reached to the Target

                   

                  The HIGHEST VALUES in my prod table till now is 2139483647 and the Data type of the target EMPID id DECIMAL(15,0)

                  • 6. Re: TT_11019 TRANSFORMATION ERROR FIX IT
                    Nico Heinze Guru

                    To be honest I don't think that this value 2,151,981,217 is the cause of trouble. As far as I can see, everything looks good so far. BIGINT is more than capable to handle numbers like this, DECIMAL (15,0) should be large enough as well. There's simply nothing I can see here which looks odd.

                     

                    Is this value set up as a pass-through port in the EXP? What data type does it have in the EXP?

                     

                    One thing which MAY have gone wrong (such things do happen from time to time) is that the flat-file target definition has become corrupted. If this is the case, then deleting (and, if the repository is versioned, purging it with all its previous versions) it from the repository and manually(!) re-creating it would help.

                     

                    Regards,

                    Nico

                    • 7. Re: TT_11019 TRANSFORMATION ERROR FIX IT
                      Prasanth G New Member

                      Hello Nico,

                           You are obsolulety correct the field (EMP_ID) from DB2 source is 2151981217 and in the Mapping the Target Flat file Field (EMP_ID) is INT datatype

                      and its not able to accept such bigvalue 2151981217,Hence Mapping is failing with the Transformation Error for that particular Field

                      Great Thanks a LOTT NICHO

                      • 8. Re: TT_11019 TRANSFORMATION ERROR FIX IT
                        Prasanth G New Member

                        Thanks a lot Nicho, You are absolutely correct