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

how to use sql to query from different database

P: n/a
hi, we use db2 udb v8.1 on windows, i am trying to use federated
database objects to create wrapper, even though i have update dbm cfg
using federated yes, i still get error "the instance for the db is not
enable for the specified action", do i miss sth else?
Nov 12 '05 #1
Share this Question
Share on Google+
20 Replies


P: n/a
Did you stop and restart the database manager? The federated parameter
is not configurable online (that is, it is not dynamic).

xixi wrote:
hi, we use db2 udb v8.1 on windows, i am trying to use federated
database objects to create wrapper, even though i have update dbm cfg
using federated yes, i still get error "the instance for the db is not
enable for the specified action", do i miss sth else?


Nov 12 '05 #2

P: n/a
Maybe you also need do enable the db for multi-site-update.
(not sure though)
Check the cc/gui, you should find it pretty quickly.

PM

"xixi" <da****@yahoo.com> a écrit dans le message de
news:c0**************************@posting.google.c om...
hi, we use db2 udb v8.1 on windows, i am trying to use federated
database objects to create wrapper, even though i have update dbm cfg
using federated yes, i still get error "the instance for the db is not
enable for the specified action", do i miss sth else?

Nov 12 '05 #3

P: n/a
i need to query from different database on the same server, so after i
create db2 wrapper ( i created under one of the database), should i do
the same thing to the other database? as you instructed , i need to
create the server, so when i do that, it asks me the remote server
info, what should i fill out? thanks
Nov 12 '05 #4

P: n/a
Xixi, there are tons of examples on developerWorks DB2.
Just search for federated.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #5

P: n/a
i have successfully create a nick name when two databases exist on the
same server, now when i try to create a wrapper, then create a server
with remote database exists on different server, i have error , how to
do that? thanks
Nov 12 '05 #6

P: n/a
aka

"xixi" schrieb im Newsbeitrag
news:c0**************************@posting.google.c om...
i have successfully create a nick name when two databases exist on the
same server, now when i try to create a wrapper, then create a server
with remote database exists on different server, i have error , how to
do that? thanks


can you post at least the sql error code...

aka
Nov 12 '05 #7

P: n/a
i have one server installed db2 udb v8.1 on 32 bit windows , since i
need to compare two different database exist on different server (the
remote server is db2 udb v8.1 on 64 bit windows), so i use the
federated object to create a wrapper, then i create a server (i have
one existing already with local database mapped successfully and
created the nicknames), so here is the sql

CREATE SERVER REMOTETEST TYPE DB2/UDB VERSION '8.1' WRAPPER "NJIPD"
AUTHID "xxxx" PASSWORD "*****" OPTIONS( ADD DBNAME 'TEST', PASSWORD
'Y');

here is the error code

IBM][CLI Driver][DB2/NT] SQL1822N Unexpected error code
"-30082" received from data source "REMOTEGN". Associated text
and tokens are "func="DriverConnect" msg=" SQL30082N Attempt
to estab". SQLSTATE=560BD
i give the remote server userid and password.

i catalog the remote server on the local server on control center
Nov 12 '05 #8

P: n/a
aka

"xixi" schrieb im Newsbeitrag
news:c0**************************@posting.google.c om...
i have one server installed db2 udb v8.1 on 32 bit windows , since i
need to compare two different database exist on different server (the
remote server is db2 udb v8.1 on 64 bit windows), so i use the
federated object to create a wrapper, then i create a server (i have
one existing already with local database mapped successfully and
created the nicknames), so here is the sql

CREATE SERVER REMOTETEST TYPE DB2/UDB VERSION '8.1' WRAPPER "NJIPD"
AUTHID "xxxx" PASSWORD "*****" OPTIONS( ADD DBNAME 'TEST', PASSWORD
'Y');

here is the error code

IBM][CLI Driver][DB2/NT] SQL1822N Unexpected error code
"-30082" received from data source "REMOTEGN". Associated text
and tokens are "func="DriverConnect" msg=" SQL30082N Attempt
to estab". SQLSTATE=560BD
i give the remote server userid and password.

i catalog the remote server on the local server on control center


you get sql1822n:

D:\aka\sqlprofiler>db2 ? sql1822n
SQL1822N Unexpected error code "<error-code>" received from data
source "<data-source-name>". Associated text and tokens
are "<tokens>".

Explanation:

While referencing a data source, the federated server received an
unexpected error code from the data source that does not map to a
DB2 equivalent.

Possible error codes include:

o 4901 Trying to open more than 15 cursors

o 4902 Row size exceeds 32K limit

This error might also be returned if the data source is not
available.

User Response:

Ensure the data source is available, then identify and correct
the root cause of the problem by locating the appropriate message
text and corrective action for the specified error at the data
source.

sqlcode : -1822

sqlstate : 560BD
so the advice is to see at the remote server what is the reason for the
sql1822n. from remote server you get an sql30082n obviously while trying to
issue a connect against the remote db. this fails with some security reason.
look at the output from:

D:\aka\sqlprofiler>db2 ? sql30082n

i learned that the AUTHENTICATION setting could be an issue at the server.

good luck

aka
Nov 12 '05 #9

P: n/a
aka
xixi, forget about AUTHENTICATION setting.....in your options string, the
userid is missing

cheers
aka
"xixi" <da****@yahoo.com> schrieb im Newsbeitrag
news:c0**************************@posting.google.c om...
i have one server installed db2 udb v8.1 on 32 bit windows , since i
need to compare two different database exist on different server (the
remote server is db2 udb v8.1 on 64 bit windows), so i use the
federated object to create a wrapper, then i create a server (i have
one existing already with local database mapped successfully and
created the nicknames), so here is the sql

CREATE SERVER REMOTETEST TYPE DB2/UDB VERSION '8.1' WRAPPER "NJIPD"
AUTHID "xxxx" PASSWORD "*****" OPTIONS( ADD DBNAME 'TEST', PASSWORD
'Y');

here is the error code

IBM][CLI Driver][DB2/NT] SQL1822N Unexpected error code
"-30082" received from data source "REMOTEGN". Associated text
and tokens are "func="DriverConnect" msg=" SQL30082N Attempt
to estab". SQLSTATE=560BD
i give the remote server userid and password.

i catalog the remote server on the local server on control center

Nov 12 '05 #10

P: n/a
when i create the server, on the server tab, i create a name for
remote server , then select type, version, for the user id and
password, i use the local server one, on the settings tab, i input
dbname for remote server's database name, password is Y, which is
default, since i have a same database exist on the local server, so
how am i going to differente i want the database from remote server to
map , not the local database from local server?
Nov 12 '05 #11

P: n/a
aka
you created the server so it has a name. with that name you can "create
nickname" referencing remote object. or you can use "set passthru" with
server name to switch context to remote objects.

see for the syntax of the commands in the manuals.

good luck
aka.
"xixi" schrieb im Newsbeitrag
news:c0**************************@posting.google.c om...
when i create the server, on the server tab, i create a name for
remote server , then select type, version, for the user id and
password, i use the local server one, on the settings tab, i input
dbname for remote server's database name, password is Y, which is
default, since i have a same database exist on the local server, so
how am i going to differente i want the database from remote server to
map , not the local database from local server?

Nov 12 '05 #12

P: n/a
i still confusing what should i do to map with a remote database,
first i am on local server, with current database , under control
center federated database objects, i create a wrapper, i have one
server created under it already, can i create another one under the
same wrapper? the one i created is map with local different database
on the same server. so i create another server, put dbname is the
remote database name (which is the same database name as local
database i haved mapped to the current one, only it exists on remote
server), then create user mapping, put remote_authid and password as
the remote server password and id, then create nickname, i want to map
with remote server remote database sysibm.systables, but it gives me
the error IBM][CLI Driver][DB2/NT] SQL1822N Unexpected error code
"-30082" received from data source "REMOTEGN". Associated text and
tokens are "func="DriverConnect" msg=" SQL30082N Attempt to estab".
SQLSTATE=560BD, i still don't know at which step it asks me for the
remote server ip address or something else, i have a same database
exist on the local server successfully mapped to , so i want to map to
remote database on remote server.
Nov 12 '05 #13

P: n/a
aka
"xixi" schrieb im Newsbeitrag
news:c0**************************@posting.google.c om...
i still confusing what should i do to map with a remote database,
first i am on local server, with current database , under control
center federated database objects, i create a wrapper, i have one
server created under it already, can i create another one under the
same wrapper? the one i created is map with local different database
on the same server. so i create another server, put dbname is the
remote database name (which is the same database name as local
database i haved mapped to the current one, only it exists on remote
server), then create user mapping, put remote_authid and password as
the remote server password and id, then create nickname, i want to map
with remote server remote database sysibm.systables, but it gives me
the error IBM][CLI Driver][DB2/NT] SQL1822N Unexpected error code
"-30082" received from data source "REMOTEGN". Associated text and
tokens are "func="DriverConnect" msg=" SQL30082N Attempt to estab".
SQLSTATE=560BD, i still don't know at which step it asks me for the
remote server ip address or something else, i have a same database
exist on the local server successfully mapped to , so i want to map to
remote database on remote server.


since I'm not familiar with this hazardous gui I can explain the steps you
would take on the command line in a db2 cmd window. in this cmd window you
have a db2 environment which is determined by the environment variable
DB2INSTANCE. by default this would be named DB2. in this instance you
created a local db let's call it X. so you can connect to db X. next you
need to a catalog a node, pointing to the remote machine R. this could be
done with "catalog tcpip node R remote R server 50000" (or whatever the port
number on the remote server for the instance in which the db that you want
to access resides is). let's assume the name of the db on the remote server
is also X. so next you would catalog the remote db in your current
environment. since you already have a local db called X you must choose
another name to refer to remote db X. you can issue a command like "catalog
db X as Y at node R". now you can "connect to Y user xixi using ...". gues
what? you are connected to db X residing at server R.
so you already created a wrapper, one is ok per DB2 family or so. next
create another server which uses the protocol defined with the wrapper.
first connect to your local db X again, because this is the db from "inside"
which you will access objects from the remote db.

db2 connect to X
db2 create server S type DB2/NT version 8.1.5 wrapper DRDA authorization
"xixi" password "..." options (node 'R', dbname 'Y')
db2 create user mapping for xixi server S options (remote_authid 'xixi',
remote_password '...')

the last one ensures your authentication at the remote server whe using
federated objects. now you can "select * from sysibm.systables" and you
should see your local db X. use the statement "set passthru S" and then
"select * from sysibm.systables" and you get the catalog from remote db X.
"set passthru reset" works as expected. to create the nickname(s) should be
straightforward (?)

good luck
aka
Nov 12 '05 #14

P: n/a
hi, i have three db2 server running on three machine, one is my local
(a) , the other two is on differet server (node b and node c), i have
cataloged b and c database to my local a. b and c has same database
sets x, y, so i try to create a wrapper on b machine's x database,
create a server , which points to b machine database Y, then create
user mapping and nicknames successfully. then i try to do the same
thing from b machine x database to c machine y database (which has
alias name GN), so i still use the wrapper i created, try to do create
server command as you instructed, but gives me error SQL0204N "DRDA"
is an undefined name. SQLSTATE=42704

then i change to this

D:\Program Files\IBM\SQLLIB\BIN>db2 create server S type DB2/UDB
version 8.1 wra
pper "NJIPD" authid "admin" password "diamond" options (node
'WEBDEMO', dbname '
GN')

the authid is remote server c's authid and password, node 'WEBDEMO" is
the remote server c 's node name i cataloged on my local machine a ,
but gives me error SQL1013N The database alias name or database name
"GN" could not be found. SQLSTATE=42705

please help.
Nov 12 '05 #15

P: n/a
hi, i have three db2 server running on three machine, one is my local
(a) , the other two is on differet server (node b and node c), i have
cataloged b and c database to my local a. b and c has same database
sets x, y, so i try to create a wrapper on b machine's x database,
create a server , which points to b machine database Y, then create
user mapping and nicknames successfully. then i try to do the same
thing from b machine x database to c machine y database (which has
alias name GN), so i still use the wrapper i created, try to do create
server command as you instructed, but gives me error SQL0204N "DRDA"
is an undefined name. SQLSTATE=42704

then i change to this

D:\Program Files\IBM\SQLLIB\BIN>db2 create server S type DB2/UDB
version 8.1 wra
pper "NJIPD" authid "admin" password "diamond" options (node
'WEBDEMO', dbname '
GN')

the authid is remote server c's authid and password, node 'WEBDEMO" is
the remote server c 's node name i cataloged on my local machine a ,
but gives me error SQL1013N The database alias name or database name
"GN" could not be found. SQLSTATE=42705

please help.
Nov 12 '05 #16

P: n/a
hi, i have three db2 server running on three machine, one is my local
(a) , the other two is on differet server (node b and node c), i have
cataloged b and c database to my local a. b and c has same database
sets x, y, so i try to create a wrapper on b machine's x database,
create a server , which points to b machine database Y, then create
user mapping and nicknames successfully. then i try to do the same
thing from b machine x database to c machine y database (which has
alias name GN), so i still use the wrapper i created, try to do create
server command as you instructed, but gives me error SQL0204N "DRDA"
is an undefined name. SQLSTATE=42704

then i change to this

D:\Program Files\IBM\SQLLIB\BIN>db2 create server S type DB2/UDB
version 8.1 wra
pper "NJIPD" authid "admin" password "diamond" options (node
'WEBDEMO', dbname '
GN')

the authid is remote server c's authid and password, node 'WEBDEMO" is
the remote server c 's node name i cataloged on my local machine a ,
but gives me error SQL1013N The database alias name or database name
"GN" could not be found. SQLSTATE=42705

please help.
Nov 12 '05 #17

P: n/a
hi, i have three db2 server running on three machine, one is my local
(a) , the other two is on differet server (node b and node c), i have
cataloged b and c database to my local a. b and c has same database
sets x, y, so i try to create a wrapper on b machine's x database,
create a server , which points to b machine database Y, then create
user mapping and nicknames successfully. then i try to do the same
thing from b machine x database to c machine y database (which has
alias name GN), so i still use the wrapper i created, try to do create
server command as you instructed, but gives me error SQL0204N "DRDA"
is an undefined name. SQLSTATE=42704

then i change to this

D:\Program Files\IBM\SQLLIB\BIN>db2 create server S type DB2/UDB
version 8.1 wra
pper "NJIPD" authid "admin" password "diamond" options (node
'WEBDEMO', dbname '
GN')

the authid is remote server c's authid and password, node 'WEBDEMO" is
the remote server c 's node name i cataloged on my local machine a ,
but gives me error SQL1013N The database alias name or database name
"GN" could not be found. SQLSTATE=42705

please help.
Nov 12 '05 #18

P: n/a
aka

"xixi" schrieb im Newsbeitrag
news:c0**************************@posting.google.c om...
hi, i have three db2 server running on three machine, one is my local
(a) , the other two is on differet server (node b and node c), i have
cataloged b and c database to my local a. b and c has same database
sets x, y, so i try to create a wrapper on b machine's x database,
create a server , which points to b machine database Y, then create
user mapping and nicknames successfully. then i try to do the same
thing from b machine x database to c machine y database (which has
alias name GN), so i still use the wrapper i created, try to do create
server command as you instructed, but gives me error SQL0204N "DRDA"
ok, DRDA was just an example, you did just right when changing this to the
name of the wrapper that you created
is an undefined name. SQLSTATE=42704

then i change to this

D:\Program Files\IBM\SQLLIB\BIN>db2 create server S type DB2/UDB
version 8.1 wra
pper "NJIPD" authid "admin" password "diamond" options (node
'WEBDEMO', dbname '
GN')

the authid is remote server c's authid and password, node 'WEBDEMO" is
the remote server c 's node name i cataloged on my local machine a ,
but gives me error SQL1013N The database alias name or database name
"GN" could not be found. SQLSTATE=42705

please help.


as I understand you are now dealing with 3 nodes :) I'm also quite happy
that you obviously made your wrapper and server and nicknames on machine (a)
successfully, right?
what I stated about the number of wrappers you should create was not very
accurate. you have to create one wrapper on each database in which you will
create servers. as long as you have only db2 family databases to access only
the wrapper of type drda is needed.
now, with 3 node environment you potentiate your possibilities of mixing and
matching. I see no problem in creating another server on your machine (a)
wich wraps a database on machine (c), since this would be analog to what you
already have done with a database on machine (b). so in my example you
change S to S2, R to R2 and Y to Y2 and of course the correct name of your
wrapper. should work the same as with machine (b).
another possibility would be to create a wrapper, server, user mapping and
nicknames from machine (b) to machine (c). you could achieve this easy by
running the same steps as before in a db2 cmd window on machine (b). catalog
machine (c) and the remote database, this time with db2 instance on machine
(b) acting as client against (c).
as an excercise to you I let you figure out by yourself how to create a
wrapper on machine (a) that accesses an object on machine (b) which in turn
is only another wrapper of an object from machine (c)...

cheers
aka
Nov 12 '05 #19

P: n/a
ok, i have my local machine a and catalog remote server b, and c on
it, then i successfully create a server, user mapping and nickname
from database x on b (which i worked on my machine with database Y on
b machines(in order to select from different database at the same
time), now i try to compare two different database exist on different
machine (database X on machine b and database Y on machine c), so i
create a new wrapper under database x on b, try to create server ,
user mapping on database Y on c machine, when i say create server
command, with the new wrapper name i created, what authid and password
i should put, is remote server c's or remote server b's?, and for the
dbname, is remote server c's database Y name or alias name i cataloged
under my machine a?

"aka" <ak*@aka-soft.de> wrote in message news:<ca**********@online.de>...
"xixi" schrieb im Newsbeitrag
news:c0**************************@posting.google.c om...
hi, i have three db2 server running on three machine, one is my local
(a) , the other two is on differet server (node b and node c), i have
cataloged b and c database to my local a. b and c has same database
sets x, y, so i try to create a wrapper on b machine's x database,
create a server , which points to b machine database Y, then create
user mapping and nicknames successfully. then i try to do the same
thing from b machine x database to c machine y database (which has
alias name GN), so i still use the wrapper i created, try to do create
server command as you instructed, but gives me error SQL0204N "DRDA"


ok, DRDA was just an example, you did just right when changing this to the
name of the wrapper that you created
is an undefined name. SQLSTATE=42704

then i change to this

D:\Program Files\IBM\SQLLIB\BIN>db2 create server S type DB2/UDB
version 8.1 wra
pper "NJIPD" authid "admin" password "diamond" options (node
'WEBDEMO', dbname '
GN')

the authid is remote server c's authid and password, node 'WEBDEMO" is
the remote server c 's node name i cataloged on my local machine a ,
but gives me error SQL1013N The database alias name or database name
"GN" could not be found. SQLSTATE=42705

please help.


as I understand you are now dealing with 3 nodes :) I'm also quite happy
that you obviously made your wrapper and server and nicknames on machine (a)
successfully, right?
what I stated about the number of wrappers you should create was not very
accurate. you have to create one wrapper on each database in which you will
create servers. as long as you have only db2 family databases to access only
the wrapper of type drda is needed.
now, with 3 node environment you potentiate your possibilities of mixing and
matching. I see no problem in creating another server on your machine (a)
wich wraps a database on machine (c), since this would be analog to what you
already have done with a database on machine (b). so in my example you
change S to S2, R to R2 and Y to Y2 and of course the correct name of your
wrapper. should work the same as with machine (b).
another possibility would be to create a wrapper, server, user mapping and
nicknames from machine (b) to machine (c). you could achieve this easy by
running the same steps as before in a db2 cmd window on machine (b). catalog
machine (c) and the remote database, this time with db2 instance on machine
(b) acting as client against (c).
as an excercise to you I let you figure out by yourself how to create a
wrapper on machine (a) that accesses an object on machine (b) which in turn
is only another wrapper of an object from machine (c)...

cheers
aka

Nov 12 '05 #20

P: n/a
aka
I think this is gonna be about naming conventions...was that you with
assembler programming language background?

Here we go:

machine A, db X, user xixi, password one
machine B, db X, user xixi, password two
machine C, db X, user xixi, password three

X := database X on machine A
XB := database X on machine B
XC := database X on machine C

you sit at machine A:

db2 catalog tcpip node B remote B server 50000
db2 catalog tcpip node C remote C server 50000

db2 catalog db X as XB at node B
db2 catalog db X as XC at node C

db2 connect to X user xixi using one
db2 create wrapper NJIPD
db2 create server SB type DB2/NT version 8.1 wrapper NJIPD authorization
"xixi" password "two" options (node 'B', dbname 'XB')
db2 create server SC type DB2/NT version 8.1 wrapper NJIPD authorization
"xixi" password "three" options (node 'C', dbname 'XC')

db2 create user mapping for xixi server SB options (remote_authid 'xixi',
remote_password 'two')
db2 create user mapping for xixi server SC options (remote_authid 'xixi',
remote_password 'three')

db2 create nickname SB.sysdummy1 for SB.sysibm.sysdummy1
db2 create nickname SC.sysdummy1 for SC.sysibm.sysdummy1

db2 create view xixi (c1,c2) as select SB.ibmreqd, SC.ibmreqd from
SB.sysdummy1 SB, SC.sysdummy1 SC
db2 select * from xixi

compare the output.

aka.

"xixi" schrieb im Newsbeitrag
news:c0**************************@posting.google.c om...
ok, i have my local machine a and catalog remote server b, and c on
it, then i successfully create a server, user mapping and nickname
from database x on b (which i worked on my machine with database Y on
b machines(in order to select from different database at the same
time), now i try to compare two different database exist on different
machine (database X on machine b and database Y on machine c), so i
create a new wrapper under database x on b, try to create server ,
user mapping on database Y on c machine, when i say create server
command, with the new wrapper name i created, what authid and password
i should put, is remote server c's or remote server b's?, and for the
dbname, is remote server c's database Y name or alias name i cataloged
under my machine a?

"aka" <ak*@aka-soft.de> wrote in message news:<ca**********@online.de>...
"xixi" schrieb im Newsbeitrag
news:c0**************************@posting.google.c om...
hi, i have three db2 server running on three machine, one is my local
(a) , the other two is on differet server (node b and node c), i have
cataloged b and c database to my local a. b and c has same database
sets x, y, so i try to create a wrapper on b machine's x database,
create a server , which points to b machine database Y, then create
user mapping and nicknames successfully. then i try to do the same
thing from b machine x database to c machine y database (which has
alias name GN), so i still use the wrapper i created, try to do create
server command as you instructed, but gives me error SQL0204N "DRDA"


ok, DRDA was just an example, you did just right when changing this to the name of the wrapper that you created
is an undefined name. SQLSTATE=42704

then i change to this

D:\Program Files\IBM\SQLLIB\BIN>db2 create server S type DB2/UDB
version 8.1 wra
pper "NJIPD" authid "admin" password "diamond" options (node
'WEBDEMO', dbname '
GN')

the authid is remote server c's authid and password, node 'WEBDEMO" is
the remote server c 's node name i cataloged on my local machine a ,
but gives me error SQL1013N The database alias name or database name
"GN" could not be found. SQLSTATE=42705

please help.


as I understand you are now dealing with 3 nodes :) I'm also quite happy
that you obviously made your wrapper and server and nicknames on machine (a) successfully, right?
what I stated about the number of wrappers you should create was not very accurate. you have to create one wrapper on each database in which you will create servers. as long as you have only db2 family databases to access only the wrapper of type drda is needed.
now, with 3 node environment you potentiate your possibilities of mixing and matching. I see no problem in creating another server on your machine (a) wich wraps a database on machine (c), since this would be analog to what you already have done with a database on machine (b). so in my example you
change S to S2, R to R2 and Y to Y2 and of course the correct name of your wrapper. should work the same as with machine (b).
another possibility would be to create a wrapper, server, user mapping and nicknames from machine (b) to machine (c). you could achieve this easy by running the same steps as before in a db2 cmd window on machine (b). catalog machine (c) and the remote database, this time with db2 instance on machine (b) acting as client against (c).
as an excercise to you I let you figure out by yourself how to create a
wrapper on machine (a) that accesses an object on machine (b) which in turn is only another wrapper of an object from machine (c)...

cheers
aka

Nov 12 '05 #21

This discussion thread is closed

Replies have been disabled for this discussion.