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    [SOLVED] bulkload gives errors due to tab characters in source data
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] bulkload gives errors due to tab characters in source data
 Login/Join
 
Platinum Member
posted
Hi

We are migrating from DB2 to Azure SQL. For this I am writing several data flows to transfer the data using extended bulk load as that provides best performance. I now note that several flows fail giving an error on the data. The failing records are shown in the log, so I examined those records and it seems clear it is caused by tab characters (x'09') within character fields.

After further analysis I see that the intermediate files that iDM is creating are tab-delimited files with unquoted strings, so it seems Obvious that if a string contains a tab character that will mess up the record.

By the way, I am on iDM version 8.2.02M (latest) and source is DB2 tables and target is Azure SQL for which we use the SQL Server ODBC adapter.

Is there any way to get around this? I have tried both a normal data flow and a direct load flow, both giving the same results. Is there some parameter to set so it will use quoted string in the tab-delimited file?

I do not have these problems using a bulk load with DB2 as target, although I must say that a few years back I did have similar problems, but after some cases I opened they adjusted the DB2 bulk load in making it use tab delimited intermediate files with quoted strings. I guess they only did it for DB2 and not yet for SQL Server ODBC adapter, so maybe I have to open case a case again to get it adjusted.

But that will take some time before there will be a better version, so any suggestion to get around this?

The only way I can now think about is to add source transformations for suspicious columns to get rid of the tab characters, but you never know if next week there will be a tab character in a column I did not yet suspect, and it is not realistic just to add this kind of source transformation on every character column.

Martin.

This message has been edited. Last edited by: FP Mod Chuck,


WebFocus 7.6.9/7.7.06M, iWay DataMigrator, ISM, Windows, DB2 Windows V10.5, MS SQL Server, Hyperstage
 
Posts: 141 | Registered: March 29, 2013Reply With QuoteReport This Post
Platinum Member
posted Hide Post
I also noted another issue with the unqouted strings in the tab-delimited files used in the bulkload. If I have an empty string " " in my source table it ends up as a null value in my target, and that is certainly not the same.

Martin


WebFocus 7.6.9/7.7.06M, iWay DataMigrator, ISM, Windows, DB2 Windows V10.5, MS SQL Server, Hyperstage
 
Posts: 141 | Registered: March 29, 2013Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Have you tried a source transformation using DB_EXPR to use the DB2 REPLACE function to convert tabs to spaces?


"There is no limit to what you can achieve ... if you don’t care who gets the credit." Roger Abbott
 
Posts: 1073 | Location: Toronto, Ontario | Registered: May 26, 2004Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Hi,

I did try this last week: DB_EXPR(REPLACE("FIELD_WITH_TAB" , CHR(9) , ' ' ))
And it indeed works.
Next week there could however be a tab character in another column, and I do not want to use this source transformation for any character column.
Neither does it solve the problem that a space value " " in the source is converted into a null value in the target, which is not the same.
I did find out that using "insert from memory" does not have the same problem as the bulkload as it apparantly does not use an intermediate hold file. I opened a case, and they indicate that this should have the same performance, but I doubt that with large files. Nevertheless if they offer a bulkload option it should work properly or it should be fixed or not offered.


WebFocus 7.6.9/7.7.06M, iWay DataMigrator, ISM, Windows, DB2 Windows V10.5, MS SQL Server, Hyperstage
 
Posts: 141 | Registered: March 29, 2013Reply With QuoteReport This Post
Guru
posted Hide Post
In the current production Release 8.2.03 (aka 7.7.08) when using Extended Bulk Load to MS SQL Server (and selected other databases) there is an option to specify the column delimiter to something other than the default TAB character.

Also there is a new adapter specifically for MS Azure SQL Data Warehouse.

 
Posts: 362 | Location: New York City | Registered: May 03, 2007Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Hi Cliff,

That is good news. I will certainly download and test this.
I just looked at our download page, but there 82M is still the latest version. When will 8.2.03 / 7.7.08 be available for download?

Martin.


WebFocus 7.6.9/7.7.06M, iWay DataMigrator, ISM, Windows, DB2 Windows V10.5, MS SQL Server, Hyperstage
 
Posts: 141 | Registered: March 29, 2013Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Martin vK

It is my understanding that 8203 will be generally available by the end of April. If you are on InfoResponse an e-mail is usually sent announcing the availability when it is officially released. On your download it will still be listed as 82M but the gen number will be different.


Thank you for using Focal Point!

Chuck Wolff - Focal Point Moderator
WebFOCUS 7x and 8x, Windows, Linux All output Formats
 
Posts: 1091 | Location: Customer Support | Registered: April 12, 2005Reply With QuoteReport This Post
Virtuoso
posted Hide Post
WebFOCUS 8203 is now available for download.


Thank you for using Focal Point!

Chuck Wolff - Focal Point Moderator
WebFOCUS 7x and 8x, Windows, Linux All output Formats
 
Posts: 1091 | Location: Customer Support | Registered: April 12, 2005Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Hi Chuck, Clif,

I read the release notes for 8.2.03 but concerning the adapter for Azure SQL database it refers to SQL Server ODBC adapter (MSODBC) to use. I have been using this specific adapter already for several months, even within our 7.7.06M version this already worked fine for Azure SQL database. So what is new or improved on this adapter concerning Azure SQL databases? Or is it now officially announced that it can be used?

Martin.


WebFocus 7.6.9/7.7.06M, iWay DataMigrator, ISM, Windows, DB2 Windows V10.5, MS SQL Server, Hyperstage
 
Posts: 141 | Registered: March 29, 2013Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  iWay Software Product Forum on Focal Point    [SOLVED] bulkload gives errors due to tab characters in source data

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