By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,963 Members | 1,749 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,963 IT Pros & Developers. It's quick & easy.

DB2 federated procedure

P: n/a
Hi, All.

Is federated procedures between DB2 databases currently not supported
in WebSphere Federation Server 9.1 ???
I recieve next error when trying to create DB2 federated procedure :

SQL30090N Operation invalid for application execution environment.
Reason
code = "21". LINE NUMBER=1.

My environment is Windows 2003 SP1+DB2 9.1 ESE + WS FS 9.1

Dec 19 '06 #1
Share this Question
Share on Google+
9 Replies


P: n/a
mitek wrote:
Hi, All.

Is federated procedures between DB2 databases currently not supported
in WebSphere Federation Server 9.1 ???
I recieve next error when trying to create DB2 federated procedure :

SQL30090N Operation invalid for application execution environment.
Reason
code = "21". LINE NUMBER=1.

My environment is Windows 2003 SP1+DB2 9.1 ESE + WS FS 9.1
DRDA data sources do not (yet) support federated stored procedures:

http://publib.boulder.ibm.com/infoce...iyfafsp01.html

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany
Dec 19 '06 #2

P: n/a

Knut Stolze wrote:
>
DRDA data sources do not (yet) support federated stored procedures:

http://publib.boulder.ibm.com/infoce...iyfafsp01.html

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany
Hello Knut;
Can you clarify? Given two DB2 LUW v9.1 ESE servers, I am interested in
doing the following:
* Define a stored proc on SERVER B that uses the ADMIN_CMD() to load
from a cursor selecting data from SERVER A (which has a nickname to it
defined on SERVER B)
* Call the procedure that resides on SERVER B from SERVER A
(referencing it as a "federated stored procedure" nickname)

In essence, I want to work around the limitation that prevents me from
doing a "load from cursor" to a federated target. Is this possible?
Seems like it should be - especially when the servers are both DRDA.
Thanks for any insight.

Pete H

Jan 16 '07 #3

P: n/a
peteh wrote:
>
Knut Stolze wrote:
>>
DRDA data sources do not (yet) support federated stored procedures:

http://publib.boulder.ibm.com/infoce...iyfafsp01.html
>>

Can you clarify? Given two DB2 LUW v9.1 ESE servers, I am interested in
doing the following:
* Define a stored proc on SERVER B that uses the ADMIN_CMD() to load
from a cursor selecting data from SERVER A (which has a nickname to it
defined on SERVER B)
I don't see a problem with that, unless there are some restrictions on LOAD
in doing that.
* Call the procedure that resides on SERVER B from SERVER A
(referencing it as a "federated stored procedure" nickname)
That is not _yet_ supported.
In essence, I want to work around the limitation that prevents me from
doing a "load from cursor" to a federated target.
IMPORT is not an option, is it?

If I got this right, you would have a two-way federated setup. Server A
accesses Server B as data source while Server B also accesses Server A as
source. And you want to LOAD data into a foreign/remote table based on
data in a local table, right?

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany
Jan 16 '07 #4

P: n/a

Knut Stolze wrote:
* Call the procedure that resides on SERVER B from SERVER A
(referencing it as a "federated stored procedure" nickname)

That is not _yet_ supported.
Any general timeframe? I won't hold you to it - I just could use some
guidance for planning purposes.
IMPORT is not an option, is it?
No - we need to be SQL based. "Insert into ...Select... from..." syntax
is an option, but we like the speed of LOAD.
If I got this right, you would have a two-way federated setup. Server A
accesses Server B as data source while Server B also accesses Server A as
source. And you want to LOAD data into a foreign/remote table based on
data in a local table, right?
Yes, we want Server A to be able to call a proc that executes on Server
B. The proc running on Server B loads to a table from a cursor that
selects from a nickname that points to a table on Server A.

We do "load from cursor" when the cursor selects from a nickname with
no problems. The twist we are adding here is that we want to call that
procedure from Server A (via federated proc nickname).

In simplest terms, we want to work around the limitation that prevents
us from LOADing (via stored proc) to a federated target. If there is a
better way, I am all ears. Thanks very much for your response.

Pete H

Jan 16 '07 #5

P: n/a
peteh wrote:
>
Knut Stolze wrote:
* Call the procedure that resides on SERVER B from SERVER A
(referencing it as a "federated stored procedure" nickname)

That is not _yet_ supported.
Any general timeframe? I won't hold you to it - I just could use some
guidance for planning purposes.
Sometime in the future. (You don't really expect my to give you more
information, do you?)
>IMPORT is not an option, is it?
No - we need to be SQL based. "Insert into ...Select... from..." syntax
is an option, but we like the speed of LOAD.
But LOAD is not SQL either. It is a DB2 command.
>If I got this right, you would have a two-way federated setup. Server A
accesses Server B as data source while Server B also accesses Server A as
source. And you want to LOAD data into a foreign/remote table based on
data in a local table, right?
Yes, we want Server A to be able to call a proc that executes on Server
B. The proc running on Server B loads to a table from a cursor that
selects from a nickname that points to a table on Server A.

We do "load from cursor" when the cursor selects from a nickname with
no problems. The twist we are adding here is that we want to call that
procedure from Server A (via federated proc nickname).

In simplest terms, we want to work around the limitation that prevents
us from LOADing (via stored proc) to a federated target. If there is a
better way, I am all ears. Thanks very much for your response.
Here are a few ideas that I have to circumvent this issue:

(1) Create a table in Server B like this:

CREATE TABLE load_sources (
nickname VARCHAR(261),
target VARCHAR(261)
)@

Then put a trigger on that table, which invokes ADMIN_CMD and loads from the
nickname (pointing to Server A) into a local target table.

(The length of 261 originates from 128 bytes schema name, 128 bytes table
name, and 4 bytes for potential double-quotes for delimited schema/table
names, and 1 byte for the dot '.' separating schema/table names.)

(2) Use an external function that invokes the procedure (or even better yet
directly the LOAD API) on Server B. (See here: http://tinyurl.com/j5mxs
and http://tinyurl.com/2j79nk). Then invoke the function through DB2
federation.

I'm not sure if that would work reliably, because Server A may decide not to
push-down the function call.

(3) Could you establish a connection from your application directly to
Server B? If so, you could LOAD from a pipe, and the pipe is populated
with data from Server A. Thus, you wouldn't need a temp file to hold the
data to be loaded.

There are probably some other approaches, but I would have to think about
them a bit longer...

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany
Jan 17 '07 #6

P: n/a

Knut Stolze wrote:
Sometime in the future. (You don't really expect my to give you more
information, do you?)
Can't blame a guy for trying, can you? :-)
No - we need to be SQL based. "Insert into ...Select... from..." syntax
is an option, but we like the speed of LOAD.

But LOAD is not SQL either. It is a DB2 command.
True enough, but rhough the ADMIN_CMD interface and load from cursor,
it looks like SQL from a calling application...
Here are a few ideas that I have to circumvent this issue:

(1) Create a table in Server B like this:

CREATE TABLE load_sources (
nickname VARCHAR(261),
target VARCHAR(261)
)@

Then put a trigger on that table, which invokes ADMIN_CMD and loads from the
nickname (pointing to Server A) into a local target table.
This looks like it may work for us, but I need to think through how I
might surface errors back to the calling procedure (executing on Server
A) that inserts to the table on Server B triggering the load. It may be
a little too asynchronous. Definitely worth looking at though.
(2) Use an external function that invokes the procedure (or even better yet
directly the LOAD API) on Server B. (See here: http://tinyurl.com/j5mxs
and http://tinyurl.com/2j79nk). Then invoke the function through DB2
federation.
I'm trying to avoid external procedures. My goal is a "code free"
solution using SQL and native DBMS capabilities wherever possible.
(3) Could you establish a connection from your application directly to
Server B? If so, you could LOAD from a pipe, and the pipe is populated
with data from Server A. Thus, you wouldn't need a temp file to hold the
data to be loaded.
The application I'm using (on Server A) is a 3rd party component - with
a requirement of a stored proc interface. Anything I do has to be
encapsulated in a single stored procedure call. I can specify a proc
name and code the proc to accomplish what I want, but can't change the
calling application.
There are probably some other approaches, but I would have to think about
them a bit longer...
Let me know if the additional "requirements" generate any other
creative approaches.

Knut - thanks so much for your thoughtful solution candidates. 1 looks
like it might be work-able, 2 would work if I bit the bullet on
external routines, 3 won't fit within my current constraints.
Pete H

Jan 17 '07 #7

P: n/a
peteh wrote:
>
Knut Stolze wrote:
>Sometime in the future. (You don't really expect my to give you more
information, do you?)

Can't blame a guy for trying, can you? :-)
No, of course not. ;-)

No - we need to be SQL based. "Insert into ...Select... from..." syntax
is an option, but we like the speed of LOAD.

But LOAD is not SQL either. It is a DB2 command.

True enough, but rhough the ADMIN_CMD interface and load from cursor,
it looks like SQL from a calling application...
All right. You got a point there.
>Here are a few ideas that I have to circumvent this issue:

(1) Create a table in Server B like this:

CREATE TABLE load_sources (
nickname VARCHAR(261),
target VARCHAR(261)
)@

Then put a trigger on that table, which invokes ADMIN_CMD and loads from
the nickname (pointing to Server A) into a local target table.

This looks like it may work for us, but I need to think through how I
might surface errors back to the calling procedure (executing on Server
A) that inserts to the table on Server B triggering the load. It may be
a little too asynchronous. Definitely worth looking at though.
I forgot to add the missing pieces.

Server B:
- table + trigger exists
- an error from the LOAD is returned by the trigger, preventing the
INSERT operation

Server A:
- nickname to "load_sources"
- an INSERT into the nickname is passed on to Server B
- if an error comes back from the INSERT, you know that the LOAD failed
>(2) Use an external function that invokes the procedure (or even better
yet
directly the LOAD API) on Server B. (See here: http://tinyurl.com/j5mxs
and http://tinyurl.com/2j79nk). Then invoke the function through DB2
federation.

I'm trying to avoid external procedures. My goal is a "code free"
solution using SQL and native DBMS capabilities wherever possible.
That narrows things down quite a bit...
>(3) Could you establish a connection from your application directly to
Server B? If so, you could LOAD from a pipe, and the pipe is populated
with data from Server A. Thus, you wouldn't need a temp file to hold the
data to be loaded.

The application I'm using (on Server A) is a 3rd party component - with
a requirement of a stored proc interface. Anything I do has to be
encapsulated in a single stored procedure call. I can specify a proc
name and code the proc to accomplish what I want, but can't change the
calling application.
Ugh. So we would be back to external procedures on Server A and/or Server
B.

One more idea: DB2 V9 supports IMPORT in the ADMIN_CMD procedure. Maybe
this is a way to go?

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany
Jan 17 '07 #8

P: n/a
Knut Stolze wrote:
One more idea: DB2 V9 supports IMPORT in the ADMIN_CMD procedure. Maybe
this is a way to go?
http://publib.boulder.ibm.com/infoce...c/r0012547.htm

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany
Jan 17 '07 #9

P: n/a

Knut Stolze wrote:
I forgot to add the missing pieces.

Server B:
- table + trigger exists
- an error from the LOAD is returned by the trigger, preventing the
INSERT operation

Server A:
- nickname to "load_sources"
- an INSERT into the nickname is passed on to Server B
- if an error comes back from the INSERT, you know that the LOAD failed
Thanks a million Knut! This looks like a solid solution that addresses
my error-handling concerns, supplies a "code free", SQL-based solution
callable from Server A. I really appreciate you closing the loop on
this.

Pete H

Jan 17 '07 #10

This discussion thread is closed

Replies have been disabled for this discussion.