Stored procedure question

[Juan Terrazas]

From inside a stored procedure written in SQL PL, I need to insert a row in
a table in a different database which is in the same instance and on the
same server.

I have tried to use a federated connection and although I'm able to query
the table it does not allow me to insert anything in it. I get an sql30090
error with code 21.

I have seen a workaround which is a call to another stored procedure written
in C and which calls the DB2 CLP using the system function. I want to avoid
using C.

Does anyone have a better solution or idea?

Thanks,

J. Terrazas

[Knut Stolze]

Juan Terrazas wrote:

From inside a stored procedure written in SQL PL, I need to insert a row
in a table in a different database which is in the same instance and on
the same server.

I have tried to use a federated connection and although I'm able to query
the table it does not allow me to insert anything in it. I get an sql30090
error with code 21.

I have seen a workaround which is a call to another stored procedure
written in C and which calls the DB2 CLP using the system function. I want
to avoid using C.

Does anyone have a better solution or idea?

What version of DB2 are you using?

Insert operations are supported with Version 8. So if you run on V8, it
might be a good idea to present some code that shows the failure.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

[Juan Terrazas]

On 8/12/04 11:13 AM, in article cf**********@fsuj29.rz.uni-jena.de, "Knut
Stolze" <st****@de.ibm.com> wrote:

Juan Terrazas wrote:

From inside a stored procedure written in SQL PL, I need to insert a row
in a table in a different database which is in the same instance and on
the same server.

I have tried to use a federated connection and although I'm able to query
the table it does not allow me to insert anything in it. I get an sql30090
error with code 21.

I have seen a workaround which is a call to another stored procedure
written in C and which calls the DB2 CLP using the system function. I want
to avoid using C.

Does anyone have a better solution or idea?

What version of DB2 are you using?

Insert operations are supported with Version 8. So if you run on V8, it
might be a good idea to present some code that shows the failure.

Unfortunately I'm using v7.2

[Knut Stolze]

Juan Terrazas wrote:

On 8/12/04 11:13 AM, in article cf**********@fsuj29.rz.uni-jena.de, "Knut
Stolze" <st****@de.ibm.com> wrote:

Juan Terrazas wrote:

From inside a stored procedure written in SQL PL, I need to insert a row
in a table in a different database which is in the same instance and on
the same server.

I have tried to use a federated connection and although I'm able to
query the table it does not allow me to insert anything in it. I get an
sql30090 error with code 21.

I have seen a workaround which is a call to another stored procedure
written in C and which calls the DB2 CLP using the system function. I
want to avoid using C.

Does anyone have a better solution or idea?

What version of DB2 are you using?

Insert operations are supported with Version 8. So if you run on V8, it
might be a good idea to present some code that shows the failure.

Unfortunately I'm using v7.2

Support for INSERT/DELETE/UPDATE operations on nicknames was added for V8.
So I see the following options for you:

(1) move to V8
(2) write an external UDF (C or Java code comes to mind) that connects to
the other database and executes your SQL statement there - or it goes via
the "db2" clp route that you used.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena