One Star

Lookup traces

I can't get something as simple as a lookup to work. When I try to debug to see what may be going on, the trace for the lookup does not display. It also shows 2 rows in 0s with 0 rows/s. What the hell do I make of that? Is there some trick or secret that I don't know that I need to use to get the trace for the lookup to display? Look at GroupLkp in my image.
15 REPLIES
Seven Stars

Re: Lookup traces

Traces don't work on lookups. If you want to see what's coming from the lookup, I suggest you add a tLogRow into the flow.
If you explain exactly what's not working and screenprint the tMap settings (including schemas) someone might be able to help you determine the problem.
One Star

Re: Lookup traces

If traces don't work on lookups then the trace icon should not be there! Anyway, when I add a tLogRow into the flow, my lookup works. When I remove it, it stops working.
One Star

Re: Lookup traces

I've done some more testing with this and here is what is happening. When I remove the flow between a tMysqlInput and a tMap and recreate and RENAME that flow then run the job the key lookup will work (sort of*). As soon as I change anything in the tMap relating to that lookup it will no longer work.
*When the lookup does work it does a cartesian join.
One Star

Re: Lookup traces

Have you set up the join details in the tMap. eg inner join and linked the tables with the correct data item?
One Star

Re: Lookup traces

Have you set up the join details in the tMap. eg inner join and linked the tables with the correct data item?

Yes, I have. Thanks. I have wasted two days on this and been through every permutation possible.
One Star

Re: Lookup traces

Should work ok then. This is a very common use of mapping. What version are you using?
Can you post an image of your tMap.
One Star

Re: Lookup traces

I am using Data Integration v5.0.2r78327. I opened a ticket with support and I am awaiting a response. Images attached. I can't fit the entire tMap on my screen so I had to break it up into two images.
EDIT: My images were too big. I'll email them to you.
EDIT #2: The email form won't let me include attachments. I'll put the images on the web and provide links.
One Star

Re: Lookup traces

I can't get something as simple as a lookup to work. When I try to debug to see what may be going on, the trace for the lookup does not display. It also shows 2 rows in 0s with 0 rows/s. What the hell do I make of that? Is there some trick or secret that I don't know that I need to use to get the trace for the lookup to display? Look at GroupLkp in my image.

The data extract time is obviously less than the minimum value it can set. The number of records out is the same as the number in so either your joins are correct or you have the wrong rules set.
One Star

Re: Lookup traces

Images:

One Star

Re: Lookup traces

You've got left outer joins on your input tables. Click the spanner tool to expand the rules box.
One Star

Re: Lookup traces

You've got left outer joins on your input tables. Click the spanner tool to expand the rules box.

The lookup called LocInfoLkp always works the way I expect it to. I know how to do a lookup in a tMap. This second lookup I added is not working properly, though. I don't think I am explaining this very well. Please allow me to try again. Forget the cartesian join for now. The lookup does not return any values in the tMap even though the keys match and I am getting rows out of the tMysqlInput. If I delete the data flow, rename it from the default name and run the job the lookup will work, SOMETIMES. If I then change anything in the tMap relating to that lookup, it will no longer work, even if I change it back.
One Star

Re: Lookup traces

Doesn't seem to be any logical reason for that. Have you tried starting from scratch in another job in case it's got corrupt somehow?
One Star

Re: Lookup traces

I haven't tried starting from scratch but yeah, something is not right. I've put a lot of work into this. Hopefully support can help. My head hurts from banging it against this wall.
One Star

Re: Lookup traces

After 24 hours support never got back to me so I put in a workaround. I don't have time to do all my work over so I put in a second tMap right after the first one and did the lookup on that one and now it works. Defeats the purpose of tMap but the tMap component apparently can't handle more than 2 inputs.
One Star

Re: Lookup traces

tMap can handle lots of lookups if the rules are set up correctly. You're using left outer joins on both lookups so you will never get a sensible result.