Guru: Beyond Three-Part Naming – Running SQL Across Remot...

August 3, 2026

In my article Guru: Finding Data in The Forest – Exploring Three-Part Naming In SQL, I helped you to get started with three-part naming. If you have a network of IBM i systems and haven’t availed yourself of this capability, you may be surprised at just how powerful it can be. Instead of relying on data transfers, replication, or intermediate files, SQL can reach directly into remote systems as though the data were local.

That works well when you know exactly which remote system contains the data you need. But what happens when your network grows from a handful of systems to dozens spread across the country? Suddenly, manually writing queries against each remote database becomes tedious and difficult to maintain.

In today’s example, I want to take the next step by demonstrating a reusable SQL procedure that can execute the same query across every relational database directory entry on your IBM i partition. Rather than manually querying each system one at a time, we can dynamically loop through the network, execute the SQL remotely, and consolidate the results into a single table for analysis. For example:

1  CREATE OR REPLACE PROCEDURE RN_MLT_QRY
2  (
3      IN p_query        VARCHAR(10000),
4      IN p_output_table VARCHAR(10)
5  )
6  LANGUAGE SQL
7  MODIFIES SQL DATA
8  SET OPTION
9    COMMIT = *NONE,
10   DBGVIEW = *SOURCE
    
11 BEGIN
12   DECLARE v_system     VARCHAR(50) DEFAULT '';
13   DECLARE v_sql        VARCHAR(10000);
14   DECLARE v_first      SMALLINT DEFAULT 1;
15   DECLARE v_full_table VARCHAR(300);
16   DECLARE v_system_sql VARCHAR(10000);
17   DECLARE v_loop_query VARCHAR(10000);
18   DECLARE SQLState     CHAR(5);
19   DECLARE SQLCode      INT;

20   DECLARE cur_systems CURSOR FOR 
21     select distinct RDB_NAME 
22     from qsys2.RDB_ENTRY_INFO 
23     order by RDB_NAME;

24   SET v_full_table = 'QTEMP.' || TRIM(p_output_table);

25   OPEN cur_systems;

26   FETCH_LOOP:
27   LOOP

28     FETCH cur_systems INTO v_system;

29     IF SQLSTATE <> '00000' THEN
30       LEAVE FETCH_LOOP;
31     END IF;

32     SET v_loop_query = REPLACE(p_query, '{{SYSTEM}}', v_system);

33     IF v_first = 1 THEN
34       SET v_sql = 'CREATE TABLE ' || v_full_table ||
35           ' AS (SELECT T.*, ''' || v_system || ''' AS SOURCE_SYSTEM ' ||
36           'FROM (' || v_loop_query || ') T) WITH DATA';
                
37       EXECUTE IMMEDIATE v_sql;
38       set v_first = 0;
39     ELSE
40       SET v_sql = 'INSERT INTO ' || v_full_table ||
41           ' SELECT T.*, ''' || v_system || ''' AS SOURCE_SYSTEM ' ||
42           'FROM (' || v_loop_query || ') T';

43       EXECUTE IMMEDIATE v_sql;
44     END IF;

45   END LOOP;

46   CLOSE cur_systems;
47 END;

The procedure begins on lines 20 through 23 by declaring a cursor over QSYS2.RDB_ENTRY_INFO. This system catalog contains the relational database directory entries configured on the partition. In other words, this is the list of remote systems we want to query.

Line 24 builds the fully qualified QTEMP table name that will ultimately hold the consolidated results from all systems.

Lines 25 through 45 form the heart of the procedure. As the cursor iterates through each relational database entry, line 32 replaces the {{SYSTEM}} substitution variable in the incoming SQL statement with the current relational database name. This allows the same query template to be dynamically redirected to each remote system during execution.

The first iteration through the loop behaves slightly differently than the rest. On lines 33 through 38, the procedure creates the output table using CREATE TABLE AS. This establishes the structure of the final result set while also loading the first system’s data.

Every iteration afterward uses the INSERT statement on lines 40 through 43 to append additional rows into the same table. Notice that each query also adds a SOURCE_SYSTEM column. This makes it easy to identify which IBM i partition supplied each row in the final results.

The dynamic SQL itself is executed using EXECUTE IMMEDIATE, allowing the procedure to construct and run SQL statements at runtime based on the current relational database entry being processed. Using the above procedure is as follows:

1 Drop Table qtemp.local_log; 

2 CALL RN_MLT_QRY('select *                                                  
3                  from {{SYSTEM}}.MUSHLIB.FORAGE_LOG 
4                  Limit 100’,
5                 'LOCAL_LOG'); 

6 Select * From qtemp.local_log
7 Order by SOURCE_SYSTEM;

On line 1, I’m simply dropping the table because the SQL procedure will attempt to create it during the first iteration through the cursor. Yes, you can (and probably should) include that logic directly within the SQL procedure itself to make the interface cleaner. There are many additional “nice to haves” that could improve this procedure considerably, but for purposes of this demonstration, I intentionally kept the example short and focused.

Lines 2 through 5 demonstrate the call to RN_MLT_QRY. Notice that the first parameter contains the SQL statement we want executed across all remote databases. The FROM clause contains the {{SYSTEM}} substitution variable, which will be replaced with the current relational database name as the procedure iterates through the cursor.

And finally, lines 6 and 7 are where you can, at last, find out how the mushroom foraging is happening across all 50 states.

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