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
 

 

Need Help on DB2 Federation Zos

 
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: Mon Oct 15, 2012 10:58 am    Post subject: Need Help on DB2 Federation Zos
Reply with quote

Hi,

I am trying to setup DB2 Federation between DB2 zOS V9.1 and DB2 LUW on AIX.

My requirement is to create a view on DB2 zOs, which will join tables between DB2 zOS and DB2 LUW AIX.

Example

Code:

CREATE VIEW V1
( COL1, COL2
)
AS SELECT A.COL1 , B.COL2 from LOCAL.SCHEMA.TAB1 A,
REMOTE.SCHEMA.TAB2 B
WHERE A.COL3 = B.COL3 ;


LOCAL = DB2 zOS
REMOTE = DB2 LUW AIX.

I have inserted rows in SYSIBM.LOCATIONS , SYSIBM.IPNAMES , SYSIBM.USERNAMES .

Also bound the SPUFI Packages.

Now in SPUFI - When I give the below parameter to connect to remote server

Code:
CONNECT LOCATION  ===> REMODB2


I am able to run the below query successfully.

Code:
SELECT B.COL2 from REMOTE.SCHEMA.TAB2 B;


But when I run the below Query its failing with the below error.


Code:

SELECT A.COL1 , B.COL2 from LOCAL.SCHEMA.TAB1 A,
REMOTE.SCHEMA.TAB2 B
WHERE A.COL3 = B.COL3 ;


ERROR


Code:

DSNT408I SQLCODE =   -204, SQLSTATE = 42704, SYNTAX ERROR OR ACCESS RULE   
         VIOLATION FROM DB2 UDB FOR AIX,LINUX,HP-UX,SUN AND WINDOWS TOKENS 
         LOCAL.SCHEMA.TAB1


Can anyone please help me and let me know what do I need to do to make this working?
Back to top
View user's profile Send private message

GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Tue Oct 16, 2012 12:34 pm    Post subject:
Reply with quote

I think what you need to do is convince IBM to implement Nicknames on zOS.
Back to top
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Wed Oct 17, 2012 9:50 am    Post subject:
Reply with quote

Hi,

So you mean to say that it is not possible to have this setup done?
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Wed Oct 17, 2012 4:34 pm    Post subject:
Reply with quote

yes, that's what i mean to say.
Just like I told you a month ago you can't even do this across different subsystems on z/OS, you centainly can not do this to other platforms from z/OS.
Back to top
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Wed Oct 17, 2012 4:49 pm    Post subject:
Reply with quote

Oh ok...

Thanks GuyC...

However, I still wonder, how do other shops handle this kind of requirement?
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Wed Oct 17, 2012 5:00 pm    Post subject:
Reply with quote

In programs : you declare a global temporary table, select LUW-table into that, and then join with that DGTT.

In Spufi, you don't. You do the reverse thing : define a nickname on LUW and then do the select on LUW.
Back to top
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Wed Oct 17, 2012 5:13 pm    Post subject:
Reply with quote

Thanks for the information...

In my case there is a Java web based application which will connect to DB2 ZOs using JDBC driver.

Now the solutions I can think of is

1. select rows from LUW in a java resultset variable and then select rows from DB2 in another java resultset variable and do a join in java code.

2. Create a GTT on DB2 Zos and then create a view between DB2 Zos table and DB2 Zos GTT. Java fetches rows from LUW, the rows fetched from LUW will be inserted in GTT and then use the view to fetch the final result set?

Or can Java JDBC create DGTT table and use it for the join?

Can you suggest me what would be the right approach?
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Wed Oct 17, 2012 7:14 pm    Post subject:
Reply with quote

you cannot create a view with a GTT, so that's out (at least IIRC)

It would make more sense to create a nickname on the LUW side.
then JAVA can select from LUW_table, nickname.
That's what we do when we need to compare LUW-tables with z/OS.
Sometimes we even create nicknames (in a LUW db) to 2 different z/OS SSIDs so that we can compare those.

i'm no JAVA expert so I can't answer on joining result sets within JAVA.
Back to top
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Thu Oct 18, 2012 10:33 am    Post subject:
Reply with quote

Thanks GuyC...

We raised a ticket with IBM and found that on zOS, View cannot be created which join both Local and Remote tables, However we can use a sql query which can join both local and remote tables.

We have to bind some packages from LUW side as well to make it work. I don't have much details with me right now, but will update once i get more information.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Thu Oct 18, 2012 3:20 pm    Post subject:
Reply with quote

Yes, please. I am really interested in that one.
Back to top
View user's profile Send private message
Benjamin.L

New User


Joined: 11 Apr 2006
Posts: 17
Location: DL.PRC

PostPosted: Tue Dec 11, 2012 3:16 pm    Post subject:
Reply with quote

gylbharat wrote:


We raised a ticket with IBM and found that on zOS, View cannot be created which join both Local and Remote tables, However we can use a sql query which can join both local and remote tables.


Do you mean you join both local and remote tables in one SQL query?
I think it is not possible for DB2 in Z/OS.

Coud you give me more information?

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



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