Bringing Home The Data
April 3, 2013 Michael Sansoterra
In IBM i 7.1 Technology Refresh 4 (TR4), IBM gave SQL developers the ability to insert data into a local database table from a remote DB2 for i database table within the comfort of a single SQL statement. (See my article Copy Data From A Remote DB2 Database Using DB2 For i 7.1 for more info).
With Technology Refresh 5, specifically DB2 for i Group PTF Level 18 or later, you can dynamically create a local table and insert data into it directly from a remote DB2 for i database query using the CREATE TABLE … AS statement.
Recall that DB2 for i can access data from a remote DB2 for i machine or partition by using the three-part naming convention: DB2 remote relational database name/schema (a.k.a. library)/object (table, view, etc.). The remote data will be returned to the local session. In this example, the remote relational database name is RMTDB2:
SELECT * FROM RMTDB2/DATALIB/ITEM_MASTER IM
When querying a remote database, only one remote system can be accessed. A single query cannot combine data from multiple remote and local systems unless you resort to something fancy like table functions to handle the remote access. (See my article Accessing Multiple DB2 Relational Databases In A Single Query for more info.)
It was noted with TR4 that IBM allowed the INSERT statement to append rows from a remote database query into a local table:
INSERT INTO QTEMP/ITEM_MASTER -- Local Table SELECT * FROM RMTDB2/DATALIB/ITEM_MASTER IM -- Remote Table, view or query
Best of all, CREATE TABLE AS now supports creating a table based on a remote relational database query that uses the three part naming convention:
CREATE TABLE AS QTEMP/ITEM_MASTER ( SELECT * FROM RMTDB2/DATALIB/ITEM_MASTER -- Remote Table, view or query ) WITH DATA
The attributes of the local QTEMP/ITEM_MASTER table will be defined like the columns in the remote table. This feature opens up new vistas when querying data from multiple systems. Instead of resorting to table functions, PC-based data transfers, DDM, or ugly DRDA programs that constantly swap database connections, a developer can now get the remote data and place it in a local permanent or QTEMP table. From there, the data from the disparate sources can be joined, sorted, and analyzed from the comfort of a single SQL statement.
I hope IBM continues to enhance SQL statements to allow more statements to participate in true heterogeneous queries, where a single statement can combine and even modify data across multiple remote databases.
For more information on configuring remote DB2 database access, please see the Related Stories listed below.