Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

How to access different DB2 database in single query.

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Fri Sep 14, 2012 3:36 pm    Post subject: How to access different DB2 database in single query.
Reply with quote

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
View user's profile Send private message

dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Fri Sep 14, 2012 3:49 pm    Post subject:
Reply with quote

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
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Fri Sep 14, 2012 4:17 pm    Post subject:
Reply with quote

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
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10201
Location: italy

PostPosted: Fri Sep 14, 2012 4:18 pm    Post subject: Reply to: How to access different DB2 database in single que
Reply with quote

as I replied already a few times ... DRDA, DRDA, ..., DRDA
Back to top
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Fri Sep 14, 2012 4:41 pm    Post subject:
Reply with quote

Hi,

Can you give me a link to the document which explain how we can setup DRDA...
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10201
Location: italy

PostPosted: Fri Sep 14, 2012 4:54 pm    Post subject: Reply to: How to access different DB2 database in single que
Reply with quote

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
View user's profile Send private message
Gnanas N

Active Member


Joined: 06 Sep 2007
Posts: 785
Location: Chennai, India

PostPosted: Fri Sep 14, 2012 4:58 pm    Post subject:
Reply with quote

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
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10201
Location: italy

PostPosted: Fri Sep 14, 2012 5:37 pm    Post subject: Reply to: How to access different DB2 database in single que
Reply with quote

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
View user's profile Send private message
Gnanas N

Active Member


Joined: 06 Sep 2007
Posts: 785
Location: Chennai, India

PostPosted: Fri Sep 14, 2012 5:46 pm    Post subject: Reply to: How to access different DB2 database in single que
Reply with quote

Hi Enrico, thanks for the explanation.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Fri Sep 14, 2012 7:59 pm    Post subject:
Reply with quote

I don't think you can join across subsystem on DB2 z/OS.
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10201
Location: italy

PostPosted: Fri Sep 14, 2012 8:03 pm    Post subject: Reply to: How to access different DB2 database in single que
Reply with quote

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 icon_smile.gif
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
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Fri Sep 14, 2012 8:06 pm    Post subject:
Reply with quote

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
View user's profile Send private message
don.leahy

Active Member


Joined: 06 Jul 2010
Posts: 641
Location: Whitby, ON, Canada

PostPosted: Sat Sep 15, 2012 1:53 am    Post subject:
Reply with quote

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
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10201
Location: italy

PostPosted: Sat Sep 15, 2012 2:04 am    Post subject: Reply to: How to access different DB2 database in single que
Reply with quote

Quote:
Slightly O/T:


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
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Tue Sep 18, 2012 5:02 pm    Post subject:
Reply with quote

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
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Wed Sep 19, 2012 1:00 pm    Post subject:
Reply with quote

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
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Wed Sep 19, 2012 1:59 pm    Post subject:
Reply with quote

Hi Gylbharat,

Actually, i have done it in other way, accessing mainframe DB2 Data from SQL Server 2005/2008. In SQL server there is a feature called 'Linked Server', using which you can do that. There should be definately something similar in Oracle.

To access oracle data in mainframe, Search for DB2 Federation.

Below are few link which i found interesting,

Connectivity Cheat Sheet for DB2 Universal Database Part 2 - DB2 for z/OS

Data Federation with IBM DB2 Information Integrator V8.1

The Federation: Database Interoperability (Part 1)

Oracle Transparent Gateway for DB2

IDUG : Accessing Oracle data on Unix from DB2 v9 for z/OS

Let us know how it works out.

Thanks,
Sushanth
Back to top
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Wed Sep 19, 2012 3:41 pm    Post subject:
Reply with quote

Thanks Sushanth

I will go over the links...
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Wed Oct 31, 2012 9:54 pm    Post subject:
Reply with quote

of possible interest: http://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
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Fri Nov 02, 2012 10:21 am    Post subject:
Reply with quote

Thanks Dick...
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts IMS DC screen Access sivasaras IMS DB/DC 3 Fri Nov 18, 2016 6:38 pm
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts IMS Database backup info ashek15 IMS DB/DC 14 Wed Nov 16, 2016 5:29 am
No new posts Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm
No new posts Access SQLCA fields inside a stored p... gangapd DB2 4 Thu Oct 27, 2016 10:20 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us