Unanswered: accessing tables from two databases in DB2 federated Database System
I have two DB2 databases in my DB2 server running on NT server.
I have one table in DB2 <Database1>. I have to create another table in DB2 <Database2> in the same server on the fly and insert some rows from table1 of Database1 into temptable of Database2.
I successfully created one federated system. I created Wrappers,Servers, then User Mapping.
Then NickNames is bit confusing, I found it has two check boxes i.e. Remote Schema, Remote Table name with operator and corresponding value box against them.
I defined these to some value.
But the problem lies elsewhere. I do not know what my table name would be until runtime.
So how federated nickname would solve my problem.
and if it does what is the SQL syntax for inserting the records in table2
i.e. create table <database2>.table2 as select * from <database1>.table1.
In SQL server accessing two database in a single SQL statement is somewhat like above.
So I just want to know if any such syntax is available in DB2 as well.
Re: accessing tables from two databases in DB2 federated Database System
In v7 one could not insert data through the nicknames
In your case, I recommend you connect to Database2, create nickname for the table1 from Database1. After that you can execute your create as select code.
Also you can use "create nickname" sql statement for dynamic nickname creation for the tables from Database1