DBC Commands in SQL Server?

[majorbroncosfan]

I am trying to integrate a DB2 application into SQL Server, but need to run the REFORMAT command from the DBC on the DB2 server to do so. Is there something in SQL Server 2000 that will do this on the Windows side? Do I need to be running Host Integration Server?

 

[lemosley01]

Do you need to execute the reformat command from a windows/sql server on the DB2 database, or are you looking for the equivalent of the DB2 REFORMAT command in the SQL Server world? What does REFORMAT do? If you could give the breakdown, there probably is an equivalent command.

 

[majorbroncosfan]

I am trying to look into doing both. The REFORMAT command takes a database file (text file in DB2) and puts it into an ASCII format. When looking at the file in UNIX, it looks like a regular fixed-length ASCII file. However, there are things in that file, like logical deletions of records, that DB2 and UNIX do not process. When you port it into a Windows environment, you get characters that look like a y with two dots over it.

The REFORMAT command fixes this issue. After running it, the file looks like an ASCII file and is easily portable into SQL Server. The only problem is that at this point, it is a manual process. I am looking into utilizing Host Integration Server to take care of this issue. I am thinking that the OLE DB Provider for DB2 might be a way to go, but don't know enough about DB2 to provide the right parameters for the necessary .udl file.

Thanks for your response. Please let me know if you have any insight on this.

 

[lemosley01]

The BCP utility may do what you need (see BCP in SQL Server Books online).

You might also look at Data Transformation Services to manipulate the data and import it. You can do a LOT from DTS, but it can require a lot of effort as well. See Data Transformation Services in SQL Books online.

Books Online also has information on adding a linked server that is DB2. On the index tab type Linked Servers and select the DB2 heading. It looks like you have to have Host Integration to do this, but they give some examples that may help you if you decide to go down this path.