Need Help on DB2 Federation Zos

gylbharat · Posted: Mon Oct 15, 2012 10:58 am

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

GuyC · Posted: Tue Oct 16, 2012 12:34 pm

I think what you need to do is convince IBM to implement Nicknames on zOS.

gylbharat · Posted: Wed Oct 17, 2012 9:50 am

Hi,

So you mean to say that it is not possible to have this setup done?

GuyC · Posted: Wed Oct 17, 2012 4:34 pm

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.

gylbharat · Posted: Wed Oct 17, 2012 4:49 pm

Oh ok...

Thanks GuyC...

However, I still wonder, how do other shops handle this kind of requirement?

GuyC · Posted: Wed Oct 17, 2012 5:00 pm

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.

gylbharat · Posted: Wed Oct 17, 2012 5:13 pm

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?

GuyC · Posted: Wed Oct 17, 2012 7:14 pm

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.

gylbharat · Posted: Thu Oct 18, 2012 10:33 am

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.

GuyC · Posted: Thu Oct 18, 2012 3:20 pm

Yes, please. I am really interested in that one.

Benjamin.L · New User Joined: 11 Apr 2006 Posts: 17 Location: DL.PRC