Export data to DB2 fails

[plevey]

I'm trying to use DTS to export data to DB2 but get 'The number of failing rows exceeds the maximum specifed' Not very helpful. The Transform Data task Destination tab correctly retrieves the columns from the DB2 table but a decimal(5,0) column is shown as numeric. I can import data from DB2 without a problem. If I replace the Transform data task with an Execute SQL task I can INSERT a row using constants as Values but not using SELECT from SQL table. DB2 is Version V8.1.6 running under Windows.
Any suggestions gratefully received.


If fishing was easy it would be called catching

 

[mrdenny]

Odds are it's a data type conversion issue. My guess would be that there is data in the SQL server that doesn't fit in the DB2 tables based on there current definition.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[plevey]

mrdenny - thanks for the reply. Been confined to sick bed hence late reply. I'm not sure it a data type conversion issue. I created a simple DB2 table with one column - COLONE. If I use an SQL task and run:

INSERT INTO DB2ADMIN.TEST VALUES 'SOME TEXT'

it works fine. If I try

INSERT INTO DB2ADMIN.TEST(COLONE) select(EFSURN) from dbo.localtest.EMPLOYEES

then I get
HResult of 0x80040e37 (-2147217865) returned
0x80040e37 seems to indicate a missing table but that obviously not the case?

If fishing was easy it would be called catching

 

[plevey]

Ooops I meant
INSERT INTO DB2ADMIN.TEST(COLONE) select(EFSURN) from dbo.EMPLOYEES.

Is the 'can't find table error ' because I can only select one connection in an SQL task?

If so how do I get some more diagnosics on the Transform data task?


If fishing was easy it would be called catching

 

[mrdenny]

There aren't a whole lot more diagnostics on a transform data task. You can turn on logging in the Package Properties and see if anything extra gets logged to the DTS log. It may have something useful.

You could also try increasing the max number of faild rows for the transformation, then figure out which rows are missing and take a look at them and see why they don't fit.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[DrSql]

Here is a suggestion that you can try.
Create a similar table as DB2 in SQL Server with similar column types and names.
Import all data into to the new table.
Then you can export that table into DB2.
Let us know how it goes.

Dr.Sql
Good Luck.

 

[plevey]

Hi DrSql.

Been there - got the T-shirt but no success.
Created the worlds simplest table
CREATE TABLE [TEST] ([COLONE] varchar (40) NULL )

Both SQL Server and DB2 are running on my PC.
In my DTS package I added one connection Microsoft OLE DB Provider for SQL Server. Added another connection IBM DB2 ODBC Driver. Both connections use relevant Admin login. Added Transformer Data Task and pumped data from DB2 to SQL Server. Deleted the Task and added it in the other direction went to the Options tab and unchecked Use fast load and it fails.
I'm tearing hair out.
Do you know if DTS actually supports DB2 as Destination?

If fishing was easy it would be called catching

 

[DrSql]

When you export From SQL Orginal Table to SQL Table what happned?
Create all fields as char and see what happnes, also try to use Query rather than selecting the tables in the source.

select
cast(field1 as field1),
cast(field2 as field2),
cast(field3 as field3)
from Source_table

Try this, I dont have DB2 environment to test it. I have done data transfer from DB2's but lot of data type issues. I assume similar appendig to DB2 also.
If not try to creat a new dummy table in DB2 and see weather you can succesfullt export that data.

Dr.Sql
Good Luck.

 

[plevey]

Dr.Sql - Found it !!!
Need to add DISABLEKEYSETCURSOR=1 to the db2cli.ini file
How obtuse is that?


If fishing was easy it would be called catching

 

[DrSql]

Good work...I will guarantee that you will never use this again in your life. Something that you spend substantial amount of time researching end as one and done process.

Dr.Sql
Good Luck.