My Table Comparison causing job to hang!

dataservices job design
1

Hello,

My job is just stuck at a dataflow it says in the trace log:

DF “ABC” is using IN-MEMORY cache.

I have had to physically kill the job.

I am using sorted input and checking “use input keys” as well. There are no duplicates in source. Earlier this was working fine.

Design is as follows:

Source----Query-----TC(sorted input)--------Map(Updates–updates, discard others)-------Target.

Your help is very much appreciated.

Thanks.

dialltheway (BOB member since 2007-09-26)

2

Why you’re using ‘use input keys’ ?
Don’t you have keys n target table ?

gssharma :india: (BOB member since 2006-10-30)

3

Hi,

Thanks for the info. Actullay, I changed the primary key data type from decimal (28,7) to int. Actually, this is INT in Oracle databse but DI was taking and converting it to decimal. I did a cast and made it int type.

That has worked.

Also, I did not realize that if target has PK defined, we do not need to check that box “use i/p keys” Thank you for bringing that to my notice.

I also changed the dataflow property to pageable rather than in-memory.

dialltheway (BOB member since 2007-09-26)

4

Nothing in the monitor log?
Can you post the last five lines of the trace log?
Is it possible the dataflow starts and is waiting for the source database to retrieve the ordered dataset? Or from the comparison table and that takes so long?

Werner Daehn :de: (BOB member since 2004-12-17)

5

Is that job working fine now ? or still persists?[/url]

gssharma :india: (BOB member since 2006-10-30)

6

Hi,

I have attcahed the atl for table comparison and a screenshot of the trace log.

I am using sorted mode yet the job is taking too long to complete for just 82,000 rows. Please take a look and advise

Your help is very much appreciated.

Thanks
Table_Comparison.zip (131.0 KB)

dialltheway (BOB member since 2007-09-26)

7

Can you send DF design images, i can’t import 12.0 version. Mine is older version of DI.

Can someone check the attached atl file and suggest the flow?

gssharma :india: (BOB member since 2006-10-30)

8

The order-by of the query transform is pushed into the source database and for some reason it takes the source long time to sort - probably performing the sort via reading the primary key index. Split the existing query into two, the first remains as is only without the order by, the second query behind does the order-by. Now add a Map_Operation between the two queries to prevent the two queries being merged into one with the result of not having changed anything.

Werner Daehn :de: (BOB member since 2004-12-17)

9

Thank you wdaehn and Sudheer.

I realized my target (and also my comparison table) were not indexed on the primary key. Someone had dropped the indexes sometime back when they had dropped the table. I recreated the unique indexes and it works like charm just 10 seconds for around 100,000 records!

The importance of indexes to enforce primary keys cannot be overemphasized.

Thank you!

dialltheway (BOB member since 2007-09-26)