Guru: Finding Data In The Forest – Exploring Three-Part Naming In SQL - IT Jungle

June 22, 2026

Anyone who has spent time foraging for mushrooms knows that location matters. A chanterelle found in the Pacific Northwest is not the same as one discovered in the hardwood forests of the Midwest. Experienced foragers do not simply note what they found. They record exactly where they found it: the region, the forest, and the specific location. Context matters.

The same is true in SQL. Most IBM i developers are comfortable with two-part naming. A reference like MUSHLIB.FORAGE_LOG identifies both the schema and the object. It tells SQL where to look within a single system, much like noting the forest and the trail where a mushroom was discovered. But sometimes, that is not enough.

As IBM i environments became more interconnected, Db2 for i expanded its reach. Three-part naming, introduced with IBM i 7.1, gave developers a straightforward way to reference objects across relational databases using standard SQL syntax. This approach specifies the relational database, the schema, and the object name. In other words, it records the full path to your data. For example: RelationalDatabase.Schema.Object.

Think of it as the database equivalent of a detailed field log. The relational database identifies the broader region. The schema identifies the forest. The object identifies the exact specimen you are after.

For example:

SELECT TimeZone, SpeciesName,
       FindDate, LocationName,
       WeightOunces  
FROM MIDWEST.MUSHLIB.FORAGE_LOG;

Here, MIDWEST is the relational database, MUSHLIB is the schema, and FORAGE_LOG is the table. SQL knows exactly where to look, even if that table resides on another IBM i partition. This precision becomes especially valuable when multiple systems contain similarly named objects. After all, FORAGE_LOG could exist in development, testing, and production environments. Without clear qualification, SQL might wander into the wrong patch of woods.

Imagine you have built a network of fellow mushroom enthusiasts across the United States. One lives in each of the five U.S. time zones: Eastern, Central, Mountain, Pacific, and Alaska. Each of them runs an IBM i system. Each maintains a library named MUSHLIB and, within that library, a table named FORAGE_LOG.

The structure is identical on every system. The only difference is where the data resides. Without three-part naming, consolidating those logs would be cumbersome. You would need separate connections, data transfers, or perhaps intermediate files. With three-part naming, SQL can reach directly into each remote system.

A query against one friend’s system might look like this:

SELECT TimeZone, SpeciesName,
       FindDate, LocationName,
       WeightOunces  
FROM EASTCOAST.MUSHLIB.FORAGE_LOG;

Here, EASTCOAST is the relational database, MUSHLIB is the schema, and FORAGE_LOG is the table.

Now imagine wanting to create a unified local log containing all recent finds from every forager. A global mushroom census, if you will.

At first glance, you might be tempted to write a single statement that unions all five systems together. Attempting to do so will result in an error:

Unfortunately, Db2 for i does not allow a single statement to reference multiple relational databases at once. Each statement must operate against one relational database at a time. There are a couple ways around this obstacle.

The approach I will explore in this article, is to begin by creating a temporary table in QTEMP:

CREATE TABLE QTEMP.LOCAL_LOG
(
    TimeZone     VARCHAR(10),
    SpeciesName  VARCHAR(50),
    FindDate     DATE,
    LocationName VARCHAR(100),
    WeightOunces DECIMAL(7,2)
);

Then we can gather the data from each system individually:

INSERT INTO QTEMP.LOCAL_LOG
SELECT 'Eastern' as TimeZone, SpeciesName, FindDate,
       LocationName, WeightOunces
  FROM EASTCOAST.MUSHLIB.FORAGE_LOG;

INSERT INTO QTEMP.LOCAL_LOG
SELECT 'Central' as TimeZone, SpeciesName, FindDate,
       LocationName, WeightOunces
  FROM MIDWEST.MUSHLIB.FORAGE_LOG;

INSERT INTO QTEMP.LOCAL_LOG
SELECT 'Mountain' as TimeZone, SpeciesName, FindDate,
       LocationName, WeightOunces
  FROM ROCKIES.MUSHLIB.FORAGE_LOG;

INSERT INTO QTEMP.LOCAL_LOG
SELECT 'Pacific' as TimeZone, SpeciesName, FindDate,
       LocationName, WeightOunces
  FROM WESTCOAST.MUSHLIB.FORAGE_LOG;

INSERT INTO QTEMP.LOCAL_LOG
SELECT 'Alaska' as TimeZone, SpeciesName, FindDate,
       LocationName, WeightOunces
  FROM LSTFRNTIER.MUSHLIB.FORAGE_LOG;

Each statement accesses exactly one relational database, keeping everything valid while still allowing us to consolidate the results locally.

From there, analysis is a simple query on the QTEMP log file we created in the first step:

SELECT TimeZone,
       COUNT(*) AS TotalFinds,
       SUM(WeightOunces) AS TotalHarvest
  FROM QTEMP.LOCAL_LOG
 GROUP BY TimeZone
 ORDER BY TimeZone;

This example highlights one of the greatest strengths of three-part naming. It allows distributed data to be queried as though it were all local. SQL handles the complexity, leaving you free to focus on the insights. For a mushroom enthusiast, that might mean identifying which regions are producing the earliest morels or the largest chanterelle harvests. For an IBM i developer, it demonstrates just how seamlessly Db2 for i can bridge multiple systems.

Three-part naming (e.g., MIDWEST.MUSHLIB.FORAGE_LOG) relies on DDM/DRDA connections to access tables on remote systems. Both protocols run over port 446, with DRDA built on top of the older DDM protocol.

Setting up your system to use three-part naming is as easy as 1-2-3:

The target system’s DDM TCP/IP attributes must be configured via the CHGDDMTCPA command.
The source system needs a corresponding entry in its Remote Database Directory (WRKRDBDIRE) with matching security settings. It is possible to establish this DB-to-DB connection securely, in which case a different port will be used. Checkout the SECCNN(*SSL) parameter of the ADDRDBDIRE command.
Authentication is handled through server authentication entries (ADDSVRAUTE) on the source system, which map a local user profile to credentials on the target system.

Of course, once you begin querying remote systems regularly, you may find yourself wanting a more reusable approach. Imagine, for example, that you grow your network of foragers to one per state? The above approach starts to get unwieldy. In a future article, we will explore how SQL routines can help encapsulate distributed queries, making remote data access as easy as calling a local function.

Until next time, happy coding.

Gregory Simmons is a Project Manager with PC Richard & Son. He started on the IBM i platform in 1994, graduated with a degree in Computer Information Systems in 1997 and has been working on the OS/400 and IBM i platform ever since. He has been a registered instructor with the IBM Academic Initiative since 2007, an IBM Champion and holds a COMMON Application Developer certification. When he’s not trying to figure out how to speed up legacy programs, he enjoys speaking at technical conferences, running, backpacking, hunting, and fishing.

推荐订阅源

IT Jungle

RELATED STORIES