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 Table Unions (row limit?)

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Data Migrator Table Unions (row limit?)
 Login/Join
 
cs_source
Gold member
posted
I have a flow created which has data set which has 2 fairly large tables and i have the flow built as follows:

source1 -> sql (select *) -> union->sql->target
source2 -> sql (select *) -> union->sql->taget

and when i try to test the union i get the following error:

(FOC010) THE NUMBER OF SORT FIELDS EXCEEDS THE MAXIMUM: XXXXX(columnname)
(FOC009) INCOMPLETE REQUEST STATEMENT
BYPASSING TO END OF COMMAND
there are about 176 rows and when i cut the columns down to where the the error says (its the 128th row) then it works

Is the best way to go about this to union and pick and choose or am i missing something else, this is raw data and i would prefer to capture it all?
 
Posts: 51 | Registered: November 30, 2012Report This Post
Martin vK
Platinum Member
posted Hide Post
A 'normal' Union returns a result that contains all rows from both sets with duplicate rows removed. In order to remove the duplicate rows Data Migrator must SORT both input sources, and it is here where you encounter the DM has a maximum of 128 sort fields.

The easiest is if you are sure that the 2 sources do not have duplicate rows, or you do not mind to have those duplicates. In that case change the Union properties to Union Type = Union All

If you do need to remove the duplicate rows there are 2 solutions:
If the Target is loaded as Insert/Update with Update if the record exists, this update will remove the duplicates.

Otherwise I suggest not to use the DataMigrator Union, but make a DBMS SQL flow by which you let the DBMS do the UNION (and remove the duplicates) in stead of DataMigrator.


WebFocus 8206M, iWay DataMigrator, Windows, DB2 Windows V10.5, MS SQL Server, Azure SQL, Hyperstage, ReportCaster
 
Posts: 168 | Registered: March 29, 2013Report This Post
Clif
Guru
posted Hide Post
There is a DataMigrator UNION object but
DM generates SQL SELECT statements and if possible passes them to the underlying relational database for execution.

That would require that all sources are in the same relational database and the same connection.

In this particular instance hotline learned that the two source tables were two different MS SQL Server databases accessed through different connections. Thus the UNION operation is done in FOCUS and hits the limit.

This can be avoided by creating a Remote View in MS SQL Server in one database for the table in the other. Then from the DMC create a synonym for the VIEW. Now the UNION can be passed to MS SQL Server.


N/A
 
Posts: 397 | Location: New York City | Registered: May 03, 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 Table Unions (row limit?)

Copyright © 1996-2020 Information Builders