473,320 Members | 1,868 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

DB2 federated procedure

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
9 5715
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

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
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

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
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

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
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
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

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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Terry | last post by:
Hi All, Im trying to run db2expln over some sql in order to find out why its so slow. I receive the following error message. LINE MESSAGES FOR DYNEXPLN.sqc ------ ...
1
by: RdR | last post by:
Hi, I have a table in SQL Server with no keys, I point this as a federated table in DB2 but in DB2 I have defined keys against this federated table in SQL. Where will the key constraints be...
4
by: uthuras | last post by:
Greetings all, Is it possible to have federated db feature implemented among DB2 family? I intend to create federated within DB2 databases. I have 2 databases TestA and TestB. I have some base...
2
by: vrbala | last post by:
Hi All, I have a issue in federated database. I will explain the scenario I have a table T1 in database called offlinedb. It has one column F1 I have an another table T2 in database uatdb. I...
5
by: Klemens | last post by:
I get SQL30090 reason 18 by trying to do an insert in a federated table and an update in a local table in one transaction Do I have to change some settings to get done or ist this not possible by...
0
by: Antanas | last post by:
I want to insert in federated db TABLE1, when records get inserted into DSY.TABLEA table. CREATE TRIGGER DSY.TRIGA AFTER INSERT ON DSY.TABLEA REFERENCING NEW AS TriggeredRow FOR EACH ROW MODE...
1
by: Antanas | last post by:
I want to insert in federated db TABLE1, when records get inserted into DSY.TABLEA table. CREATE TRIGGER DSY.TRIGA AFTER INSERT ON DSY.TABLEA REFERENCING NEW AS TriggeredRow FOR EACH ROW MODE...
2
by: mas5 | last post by:
Hello, Sorry if my question is elementary. I've searched google, but can't seem to find exactly what I'm looking for. My client is using db2 8.2. They have 2 companies and each company has a...
0
by: And | last post by:
Hello everybody I’m facing the current challenge! I have an intranet built in federated search that allows me to search in different subscribed databases and at the same time I have a folder...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.