4 Replies Latest reply on Oct 8, 2013 6:56 AM by 1045808

    ODI Interface is getting hang everytime executed on the same step

    1045808

      Hi,

       

      One of the ODI Interface is getting hang every time when am executing and that too for large volume of data, for small data it is working fine. The LKM, IKM and CKM am using is LKM SQL to SQL, IKM SQL Increamental Update with Truncate True, CKM SQL. The Source and Target is on same database Oracle. The step on which interface it is hanging is Insert PK errors in which it is scanning I$ table in the database and taking so much time and not getting completed in even 12 hours.

      Tried bouncing database and executed same query directly on database also but same result.

       

      Thanks in Advance...

        • 1. Re: ODI Interface is getting hang everytime executed on the same step
          RAM_STUDENT

          Make Sure you have Chosen correct PK as your step may be like this

          i$.pk_col

              ) in  

          (

                  select    pk_col

                  from     i$.pk_col

                  group by     pk_col

                  having     count(1) > 1

                  )

           

          which may incurred more time.

          Check for Index creation step as well.

          If possible then send your code.

          • 2. Re: ODI Interface is getting hang everytime executed on the same step
            1045808

            Yes, I have chosen the correct Pk, in this step it inserting the error records into the E$ table. It is not inserting any row in the E$ table for the same set of few number of records around 10,000. But for higher number it is hanging...

            I checked for Index creation step, it is not available in CKM SQL, it is there in CKM Oracle... Does it make any difference in execution?

            • 3. Re: ODI Interface is getting hang everytime executed on the same step
              RAM_STUDENT

              Hi

              post step name and corresponding code for the same for debugging with sample table.

              • 4. Re: ODI Interface is getting hang everytime executed on the same step
                1045808

                Hi,

                 

                Step name is insert PK errors of CKM SQL

                I modified the query with test table name, target table have two columns TEST_ID and APPLICATION_CODE and one primary constraint created on target table as TEST_TABLE_PK in which TEST_ID defined as constraint.

                 

                insert into TEST.E$_TEST_TABLE

                (

                    ODI_SESS_NO,

                    ODI_ERR_TYPE,

                    ODI_ERR_MESS,

                    ODI_ORIGIN,

                    ODI_CHECK_DATE,

                    ODI_CONS_NAME,

                    ODI_CONS_TYPE,

                    TEST_ID,

                    APPLICATION_CODE

                )

                select     109333,

                    'F', 

                    'ODI-15064: The primary key TEST_TABLE_PK is not unique.',

                    '(45001)ETL.INT_TEST',

                    sysdate,

                    'TEST_TABLE_PK',

                    'PK',    

                    TEST_ID,

                    APPLICATION_CODE

                from    TEST.I$_TEST_TABLE    TEST_TABLE 

                where    exists     (

                        select    'X'

                        from    TEST.I$_TEST_TABLE    E

                        where    TEST_TABLE.TEST_ID    = E.TEST_ID

                        group by    E.TEST_ID    

                        having    count(*) > 1

                        )