DB2 federated procedure

[mitek]

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

[Knut Stolze]

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

[peteh]

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

[Knut Stolze]

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

[peteh]

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

[Knut Stolze]

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

[peteh]

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 "requiremen ts" 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

[Knut Stolze]

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_sourc es"
- 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

[Knut Stolze]

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

[peteh]

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_sourc es"
- 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