Hi,

Could someone please advice on the correct syntax for creating a View in DB2 that will access other db2 databases in the same server.

i have tried the following:

CREATE VIEW testview AS
SELECT
table1.col2,
table3.col6
FROM
Database.Schema.table AS mytable;

this code returns an error "undefined name", any assistance in this regard would be greatly appreciated.

Thank you in advance.

Shai.

google for "federated". In the from-clause you must code nicknames instead of tablenames.
(db2girl wrote a good piece in this forum a few weeks ago)

Hi dr te z,

I was hoping you could supply some sample code on how to go about this or perhaps send me a link to a thread that db2girl posted a few weeks ago.

thank you though, i did google federated and i'm going through some of the info i got.

thanx!

If you are using DB2 z/OS, you don't need federation because there a database is pretty much only a qualifier for tablespaces.

Hi Stolze,

could you please supply sample code that will allow the achievement of this, if possible.

thanx!

shai.

Just use a regular CREATE VIEW statement. There is nothing special about it. Of course, that's only the case for DB2 z/OS and if the table spaces reside in the same DB2 subsystem. If you have DB2 LUW, you'd need federation.