Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  iWay Software Product Forum on Focal Point    Data Migrator -- Defined-Based Join Sometimes Works, Sometimes Doesn't. Any Insight?

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Data Migrator -- Defined-Based Join Sometimes Works, Sometimes Doesn't. Any Insight?
 Login/Join
 
John_Edwards
Virtuoso
posted
Behold my showstopper in Data Migrator:

(FOC14006) SQL TRANSLATOR ERROR NULL DBE hTblDBE

Here's my situation. I have a process that takes material from one table to another (all SQLServer). After the move I go back and delete from the source if the transformation to the destination succeeds. To do this, I need to match up the key field in the source to the key field in the destination by adding "DC" to the front of it. The define is very simple --

'DC' | FORM_NUMBER

Make the longer key, then use that define in a join to see if there is a matching record in the destination table.

I do this for three different sets of tables. For two of those sets, all runs smoothly. For the third, I get the error I show above. The code and master files are otherwise identical.

When I remove the concatenation from the define all works correctly, although the keys don't match.

I'm very confused as to why it would work in two places and not the other.

Any insight?



 
Posts: 1012 | Location: At the Mast | Registered: May 17, 2007Report This Post
Jessica Bottone
Platinum Member
posted Hide Post
Although the masters are identical, are the SQL Server table defintions identical? I'm going to assume the synonym builder was used to create the master so at the time the master was originally created, it matched the SQL Server table. Is it possible that since the master was originally created, it was manually updated, or that the SQL Server table were modified and the master not refreshed?

My thought is that although the master indicates these fields should match in type and size that maybe they really don't.


Data Migrator 5.3, 7.1, 7.6
WebFOCUS 7.1, 7.6, 7.7
SQL Server, Oracle, DB2
Windows
 
Posts: 126 | Registered: January 18, 2007Report This Post
Clif
Guru
posted Hide Post
I'm sure you are famililar with the concept of Automatic Pass-Thru (APT) where if all the source tables are in the same database iWay will if possible simply "pass through" your query to the RDBMS and let it do any JOIN processing.
However if you reference any DEFINEd fields in the synonym, then APT cannot be used, and the join is done using FOCUS instead.
I think that's why you are seeing a difference in behaviour, APT vs NON-APT processing. If so then you might try doing the concatenation IN THE JOIN OBJECT itself, for example T1.KEYFLD = 'DC' || T2.KEYFLD.
Also, another customer reported the same sympton (error message) as you, altho with a somewhat different cause. Their issue was addressed in the latest maintenance release 7.6.7. If you haven't applied this maintenance you may want to do so and see if resolves your issue too.


N/A
 
Posts: 397 | Location: New York City | Registered: May 03, 2007Report This Post
John_Edwards
Virtuoso
posted Hide Post
You da man, Clif.

In the past I've had a lot of trouble with concatenating in the join criteria itself, but based upon your reply I gave it a shot and tried this --

T1.ENCOUNTER_ID = CONCAT('DC', T3.FORM_NUMBER)

It worked without a hitch. Pushing the conversion into the SQL cleared the problem. Please forward me your address so that I can send pie.

J.



 
Posts: 1012 | Location: At the Mast | Registered: May 17, 2007Report This Post
John_Edwards
Virtuoso
posted Hide Post
Nahh, now I'm getting it on a plain-old join. What a hassle. So much for making a quick change.

J.



 
Posts: 1012 | Location: At the Mast | Registered: May 17, 2007Report This Post
Jessica Bottone
Platinum Member
posted Hide Post
John, I take it you're saying you're getting the FOC14006 on something else. Can you post the SQL and the synonyms, as well as any source transformations? Do you get the FOC14006 when you do a check SQL or does it show up in the log? Or both?


Data Migrator 5.3, 7.1, 7.6
WebFOCUS 7.1, 7.6, 7.7
SQL Server, Oracle, DB2
Windows
 
Posts: 126 | Registered: January 18, 2007Report This Post
John_Edwards
Virtuoso
posted Hide Post
It seems to be a by-product of stacking too many files into a single flow. I'm using it as a marker that my code is getting too complicated and that I need to break it into multiple passes.

Likely this will make the code more maintainable, so DM's inability to understand the code is a blessing in disguise -- the problem magically melts away when I pull the exact same source out into it's own smaller flow.

Always nice to know someone is reading this stuff though -- thanks for tuning in.

J.



 
Posts: 1012 | Location: At the Mast | Registered: May 17, 2007Report This Post
John_Edwards
Virtuoso
posted Hide Post
And . . . it's back. On a small file. This thing is costing me a LOT of hours and I have no clue what the problem is. This time it's not a defined-based join.

I think it's the "all other" error that gets thrown whenever something goes wrong. What a hassle.

Installing 7.6.7 at this time is NOT going to be a popular request.

Still looking for anyone that can give me insight on this ugly thing.

Sag.

This message has been edited. Last edited by: John_Edwards,



 
Posts: 1012 | Location: At the Mast | Registered: May 17, 2007Report This Post
Clif
Guru
posted Hide Post
I'm not sure what you mean by "a small file." A small data flow? A small source file?

In any case, until you are able to apply the current maintenance to 7.6 (that would now be 7.6.8) please ensure that your select statement can use APT. The run time log will have a message if it can't.

If that doesn't resolve the problem, please open a hottrack case and upload:
* All source & target synonyms (mas/acx files)
* Data Flow (fex/etg files)

This message has been edited. Last edited by: Clif,


N/A
 
Posts: 397 | Location: New York City | Registered: May 03, 2007Report This Post
John_Edwards
Virtuoso
posted Hide Post
Arrrrrrrrrrrrrrrrrgggggggggggggggggghhhhhhhhhhhhhh!!!



 
Posts: 1012 | Location: At the Mast | Registered: May 17, 2007Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  iWay Software Product Forum on Focal Point    Data Migrator -- Defined-Based Join Sometimes Works, Sometimes Doesn't. Any Insight?

Copyright © 1996-2020 Information Builders