I have a need to bring over data from a legacy Access database to UDB. I
have tried a variety of methods to get the data over, many of which were
successful. But I have at least one table with one column that is defined
with a data type of Memo, that has data in it that is longer than 32k, so a
CLOB is my only real option. This is purchase order/contract information,
so I can't just lop off the last bit and go on.

With the db2 table column defined as a CLOB, as a linked table in Access, it
appears as a 255 byte text field. This copies the data, but truncates it.
When I define the db2 table as a long varchar, it defines it as a Memo type.
When I try to do any kind of append query in Access on that column, I get an
invalid conversion error, CLI102E.

My next step was to try a delimited export file. This got the data out of
the table just fine, but then another problem surfaced. The problem lies in
the data itself. It contains CR/LF characters so that it formats nicely on
the Access application. When I try to do any kind of text/delimited import,
DB2 interprets those CR/LF characters as new records, which plays hob with
my data.

Using DTS, I have been able to put the data into SQL Server from the Access
DB. However, when I try to use DTS to get the data into DB2, I get
'CLI0150E Driver not capable. SQLSTATE=S1C00'. I am having no luck adding
my db2 database as a linked server to SQL Server, and I can't buy the
federated database tool.

Can this be done, within the limitations I am working under? If not, then
I can build a business case for purchasing a tool, so I would also
appreciate any input as to which tool would be best.

Thanx,

Mark McNary
NPPC MES Database support
(w) 314-982-1389
(m) 314-497-5850




DB2 & UDB email list listserv db2-l LazyDBA home page