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 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
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
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
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
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
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
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
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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
------ ...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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
|
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...
| |