Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  iWay Software Product Forum on Focal Point    Data Migrator Table Unions (row limit?)
Go
New
Search
Notify
Tools
Reply
  
Data Migrator Table Unions (row limit?)
 Login/Join
 
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, 2012Reply With QuoteReport This Post
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 7.7.06M/8.2.03M, iWay DataMigrator, Windows, DB2 Windows V10.5, MS SQL Server, Azure SQL, Hyperstage, ReportCaster
 
Posts: 149 | Registered: March 29, 2013Reply With QuoteReport This Post
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.
 
Posts: 373 | Location: New York City | Registered: May 03, 2007Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  iWay Software Product Forum on Focal Point    Data Migrator Table Unions (row limit?)

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.