• No replies
61 Posts

Pinned topic How to execute a SQL Server stored procedure from DB2 database

‏2009-04-15T20:13:49Z |
Hello to everyone, I'm not a DB2 guy so I do not know if this is the right place for asking this.

The scenario

SQL Server 2000 Database on Windows Server 2000
DB2 8.2 Database on HP-UX

The problem

Mi company developed an application based on SQL Server 2000 some years ago. Our customer also uses DB2 for other areas of their compañy. Nowadays, they need to access some of the data stored in SQL Server in real time (no way to copy all the data from SQL Server to DB2 at nights, for example)

This data needs to be inserted in the DB2 database without any transformation.

What I need
I need to know if there is an easy way to do one of these things (both are possible using SQL Server to SQL Server, but don't know if can be done from DB2)

a) Create a stored procedure in SQL Server that returns the data needed. Execute that SP from DB2 and insert the resulting data.

something like:

INSERT into DB2Database.Sales ( InvoiceNr, CustomerID, Date, Amount)
EXEC RemoteSqlServer.dbo.sp_get_data(parameter1, parameter2, parameter3)

This can be done in SQL Server, I do not know if DB2 allow to insert into a table the results of a SP.

b) Execute an insert sentence that uses the results of a select from the remote SQL Server database

something like:

INSERT into DB2Database.Sales ( InvoiceNr, CustomerID, Date, Amount)
SELECT i.Invoice, i.CustomerNumber, i.InvoiceDate, i.Total
FROM RemoteSqlServer.dbo.t_invoices i
WHERE i.salesman = parameter1
AND = parameter2

this can be done in SQL Server and Oracle, so it seems that DB2 should have a way to do this.

I prefer solution "a", since it does not reveals the SQL Server data structure to the DB2 database, it's easiest to mantain, can be used from diferente sources, etc etc, but both solutions may be acceptable.
I would like to know what kind of IBM products should I use to access a SQL Server database (I´ve read that I need WebSphere Information Integrator but this product does not seem to be available in the IBM Website), and if someone can guide me in preparing a small test case, I´ll ve very gratefull.

thanks in advance,

Lisandro Fernigrini