Hi all,


I have a fixedwidth .DAT file and a DB2 table, I have to create a flat
file by pulling data from both file and DB2 table. I tried using
IMPORT/LOAD utilities to load to a global temporary table and thought
of joining it and extracting, but IMPORT/LOAD are not allowed on
temporary table.
Can you please suggest me how to go about it...


thanks in advance,

I'm not going to say this is the best way to do it - in fact, I'm sure it's not - but no one else has responded, so I'll try it.

Is there any chance that you also have access to a SQL Server where you can link to your DB2/400 server? If so, the way I would get around it is to use SQL Server's DTS utility (stands for Data Transformation Services). I'm pretty confident you can accomplish what you need with little trouble if you have access to DTS.

I have used this site before and find it helpful: http://www.sqldts.com/. There is also a guide on MSDN, but it's incredibly hard to find.

Well, you can definetely do an INSERT. When I was on the application development team, before DBA days, we were using an INSERT to populate temp table.

Vijay,

to provide you a clean method:

you can use Websphere Information Integrator (formerly known as DB2 II formerly known as Relational Connect) and map your .DAT file to a table and create a nickname for it (Federation, federated objects).
That can be queried then like any other table.

Two points to keep in mind:
- I am not sure about updates
- If you have Information Integrator not licensed yet this could be for a rather expensive solution for a single file.

I tested a year or two ago and it worked fine.

A workaround would be creating an insert script.

I would not suggest the bypass around to SQLServer and DTS ... that is a little much of processing overhead. But what I think Cindy is suggesting is the equvalent of Federation with II - just in the SQL Server World.