Bringing Home The Data

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.

Note:

• CREATE TABLE can only create a local table.
• Only one remote relational database can be referenced in the statement (unless you use something like a table function).
• Propagated attributes from the remote query columns include long column name, short (system) column name, data type, length, precision and scale (for numeric columns), CCSID (character), null capable, column heading, and column text.
• When creating a temporary table in QTEMP, the DECLARE GLOBAL TEMPORARY TABLE statement can be used instead of CREATE TABLE AS.
• Other column attributes such as the default value, hidden, identity and row change timestamp can be propagated when specified on the copy-option portion of the CREATE TABLE AS 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.

Michael Sansoterra is a DBA for Broadway Systems in Grand Rapids, Michigan. Send your questions or comments for Mike via the IT Jungle Contact page.

RELATED STORIES

Accessing Multiple DB2 Relational Databases In A Single Query

Copy Data From A Remote DB2 Database Using DB2 For i 7.1

Execute SQL Statements on DB2 UDB for Windows from the iSeries

Using DRDA to Execute SQL Statements on Another iSeries

                     Post this story to del.icio.us
               Post this story to Digg
    Post this story to Slashdot