|
View previous topic :: View next topic
|
| Author |
Message |
gylbharat
Active Member
.jpg)
Joined: 31 Jul 2009
Posts: 565
Location: Bangalore
|
|
|
|
Hi,
I just want to know how can I run a query which access both Test and Prod database in the same query.
Example - Say a table - SYSIBM.SYSTABLES,
I just want to run a query to check which tables are extra in Test system and not in prod system.
Both Test and Prod databases are defined on different servers. I tried using 3 part name but it says object does not exist.
Please let me know how can I do that? |
|
| Back to top |
|
 |
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
even though db2 has not been my forte this week
i will sport a suggestion:
are you not actually trying to connect to 2 different db2's?
you say server. do you mean pc stuff
or are you talking about 2 mainframe lpars or 2 different mainframe machines?
most shops, because of security concerns,
normally do not provide the 'gateways'
necessary to connect 'prod' with 'test' (actually prod with anything)
That said,
you could spend whatever time is necessary to create some connection,
or
(my suggestion)
simply spufi each separately,
and then use some tool to compare the two outputs. |
|
| Back to top |
|
|
gylbharat
Active Member
Joined: 31 Jul 2009
Posts: 565
Location: Bangalore
|
|
|
|
Its two different mainframe machines... One for test and one for Prod.
I can use spufi... But was just curious how It can be done. |
|
| Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10873
Location: italy
|
|
|
|
| as I replied already a few times ... DRDA, DRDA, ..., DRDA |
|
| Back to top |
|
|
gylbharat
Active Member
Joined: 31 Jul 2009
Posts: 565
Location: Bangalore
|
|
|
|
Hi,
Can you give me a link to the document which explain how we can setup DRDA... |
|
| Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10873
Location: italy
|
|
|
|
Your DB2 and network support should know...
if You are support You should know how to look for the documentation Yourself
what happened when You googled for DB2 DRDA
or searched the IBM DB2 manuals for it
( PC version of STFM )
read my signature please! |
|
| Back to top |
|
|
Gnanas N
Active Member
Joined: 06 Sep 2007
Posts: 792
Location: Chennai, India
|
|
|
|
I think if your DB2 sub systems are on mainframes(LPARs or machines), they all should be there in data-sharing group.
DRDA setup is for accessing the data base residing on z/OS from other platforms, for example, distributed systems.
Perhaps, am I missing something?
Again, this set up and access are the standards of a particluar site. so, please talk with your support group to know what is allowed at your site. |
|
| Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10873
Location: italy
|
|
|
|
| Quote: |
| DRDA setup is for accessing the data base residing on z/OS from other platforms, for example, distributed systems. |
NO DRDA does not put any constraint on the connected/connecting partner
( only DRDA support is needed )
setting up a DB2 data-sharing environment is a bit more complicated than setting up a DRDA environment
what if the two<lpar/machines> are not in <full> sysplex mode ?
to use DRDA a plain network connection is all that is needed |
|
| Back to top |
|
|
Gnanas N
Active Member
Joined: 06 Sep 2007
Posts: 792
Location: Chennai, India
|
|
|
|
| Hi Enrico, thanks for the explanation. |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| I don't think you can join across subsystem on DB2 z/OS. |
|
| Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10873
Location: italy
|
|
|
|
| Quote: |
| I don't think you can join across subsystem on DB2 z/OS. |
IIRC I agree,
my concern was not about the SQL facilities available 
just a suggestion on how to retrieve data from a DB2 substem on a <different> machine without having to be logged on and run the queries on <that> machine |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| I understand, I just want to avoid setting the thing up. and then discover it still doesn't allow what was expected. |
|
| Back to top |
|
|
don.leahy
Active Member
Joined: 06 Jul 2010
Posts: 765
Location: Whitby, ON, Canada
|
|
|
|
Slightly O/T: in our shop application programmers are not allowed to query the production DB2 catalog. Not so much for security reasons as for performance; no one wants table space scans running against the production catalog in the middle of the day.
Our DBAs provide a set of 'shadow catalog' tables on the development subsystem that reflects the production catalog. Very convenient. |
|
| Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10873
Location: italy
|
|
|
|
not at all
too often on these forums people are concerned only about the lowly technicalities
completely overlooking standards, good practices and good sense approach
sometimes working around the problem provides more effective results
( like using the suggestion of <shadowing> the catalog tables ) |
|
| Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
gylbharat,
In my previous shop, as a work around i used SAS for cross subsystem joins. Luckily, prod and test were in the same lpar.
Below is a sample
| Code: |
//STEP1 EXEC SAS9
//OUTFILE DD DSN=HXSULL.SAS.TEST,
// DISP=(NEW,CATLG,DELETE),
// UNIT=DASD,SPACE=(TRK,(20,5),RLSE)
//SYSIN DD *
OPTIONS NOCENTER;
PROC SQL;
CONNECT TO DB2 (SSID=DB2D);
CREATE TABLE DB2DB2D AS
SELECT * FROM CONNECTION TO DB2
(
SELECT NAME FROM SYSIBM.SYSDATABASE
);
%PUT SQLXMSG=&SQLXMSG;
QUIT;
PROC PRINT DATA=DB2DB2D;
PROC SQL;
CONNECT TO DB2 (SSID=DB2P);
CREATE TABLE DB2DB2P AS
SELECT * FROM CONNECTION TO DB2
(
SELECT NAME FROM SYSIBM.SYSDATABASE
);
%PUT SQLXMSG=&SQLXMSG;
QUIT;
PROC PRINT DATA=DB2DB2P;
PROC SQL;
CREATE TABLE JOIN AS
SELECT NAME FROM DB2DB2D WHERE NAME NOT IN
(SELECT NAME FROM DB2DB2P );
QUIT;
PROC PRINT DATA=JOIN;
DATA OFILE;
SET JOIN;
FILE OUTFILE;
PUT @1 NAME
;
|
If its on different plexes,
1. PROD : run the query in spufi
2. Store the result in shared dasd
3. TEST : Read the file & Join using SAS
This is the easiest way i can think of.
Thanks,
Sushanth |
|
| Back to top |
|
|
gylbharat
Active Member
Joined: 31 Jul 2009
Posts: 565
Location: Bangalore
|
|
|
|
Thanks Sushanth...
I wanted to know how we can set it up in the single query... We have a product which will be installed on AIX and it will have some tables in AIX (Oracle Tables) and some on DB2 Zos...
So wanted to know how we can access both AIX tables in Oracle and Tables on DB2 ZOs in a single Query... through Java application? |
|
| Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
| Back to top |
|
|
gylbharat
Active Member
Joined: 31 Jul 2009
Posts: 565
Location: Bangalore
|
|
|
|
Thanks Sushanth
I will go over the links... |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
of possible interest: www.itechp2pexchange.com/content/db2-what-distributed-operation
In essence, you can not have 2 (or more) active 'connects' to db2 in one thread.......
So, based on a one thread solution (such as SAS),
Connect to the 1st DB, do your SELECT and
save the results (either in memory or write it to dasd.
DECONNECT
Connect to the 2nd DB, do our SELECT and
save the results (either in memory or write it to dasd.
DECONNECT
then play with the data
and generate your output.
Now, if you are OO literate,
you could try starting two new tasks,
one would connect to the 1st DB and store the data and then signal finish.
the other would connect to the 2nd DB ......
then play with the data
and generate your output.
You will never beable to execute (what I would call) a-real-time-JOIN on tables in different z/OS db2 sub-systems.
what you would need is an 'super agent' in some db2 subsystem
supervising both of the 1st DB and 2nd DB agents.
happy Halloween! |
|
| Back to top |
|
|
gylbharat
Active Member
Joined: 31 Jul 2009
Posts: 565
Location: Bangalore
|
|
|
|
| Thanks Dick... |
|
| Back to top |
|
|
|
|
