RE: Hooray!!! DB2 for i adds remote & local support in a single SQL statement!

Interesting.

What profile is used to log onto the remote system? Are any authorization list entries required?

Thanks,

Gary Monnier

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Charles Wilt
Sent: Friday, July 13, 2012 6:38 AM
To: Midrange Systems Technical Discussion
Subject: Hooray!!! DB2 for i adds remote & local support in a single SQL statement!

http://www.mcpressonline.com/database/techtip-easier-db2-for-i-data-transfers-with-new-technology-refresh.html

he latest IBM i Technology Refresh for the 7.1 release lifts this restriction for INSERT statements to simplify data transfer operations involving three-part names. This new capability makes it easy for a table on your local system to be populated with data from another system, as the following INSERT statement demonstrates.

INSERT INTO locallib.customers
SELECT lastname, address, city
FROM rmtsystem.datalib.customer WHERE state='IA'

The embedded SELECT statement uses a three-part name to reference data on a remote system, and the result of that SELECT statement is inserted into the customer table on the local system. The SELECT statement also could reference an alias that points a remote DB2 object. This ability to transfer data directly into a table eliminates the need to first copy the data into some type of temporary staging area like a data structure or array.

Only IBM i 7.1 systems that have Level #14 of the Database Group PTF installed can run an INSERT statement that references DB2 objects from more than one system.

Of course it's limited to INSERT, so we don't have true federation functionality...but this is at least a step in the right direction...

Charles
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l.