Matt Seeberger

Compare System Values Between Systems or LPARs

I am always looking for easier ways of accomplishing things and with less manual effort. Less manual effort, in my mind, equals less mistakes. Recently, I was setting up a new LPAR on one of our systems and I wanted to compare the system values between it and another hosted LPAR. I decided the best place to start was in Run SQL Scripts -> Insert from Examples. Sure enough, Scott Forstie and his team at IBM had an example that was close to what I needed. In this post I want to highlight their example and show how I changed the query to run against two hosted LPARs — I ran this query from the host looking at 2 hosted LPARs.

Both examples use the 3 part naming convention; RemoteRDB/MySchema/MyTable. In order for this to work properly you will need to create an entry in the Work With Relational Database Entries screen (WRKRDBDIRE). You can read more about this at the following links: IBM: Three-Part Names, IBM: DRDA Security, IBM: Relational Database Directory, IT Jungle: Copy Data from Remote Database, RPGPGM.com: Database Connection Between IBM i, and RPGPGM.com: SQL Between Two Systems.

The Original Example – Compare Remote System with Current System

This example creates a temporary table for a remote system and then lets you query between it and the current system you are connected to.

-- category:  IBM i Services
-- description:  Work Management - System Values
-- Note: replace REMOTEPART with the name of the remote partition
--       (WRKRDBDIRE or QSYS2.SYSCATALOGS)
-- Compare System Values across two partitions
DECLARE GLOBAL TEMPORARY TABLE SESSION.Remote_System_Values (
            SYSTEM_VALUE_NAME,
            CURRENT_NUMERIC_VALUE,
            CURRENT_CHARACTER_VALUE
        ) AS
            (SELECT *
                    FROM REMOTEPART.QSYS2.SYSTEM_VALUE_INFO)
            WITH DATA
    WITH REPLACE;

-- Use exception join to reveal any differences
SELECT 'REMOTEPART' AS "System Name",
       A.SYSTEM_VALUE_NAME,
       A.CURRENT_NUMERIC_VALUE,
       A.CURRENT_CHARACTER_VALUE
    FROM QSYS2.SYSTEM_VALUE_INFO A
         LEFT EXCEPTION JOIN SESSION.Remote_System_Values B
             ON A.SYSTEM_VALUE_NAME = B.SYSTEM_VALUE_NAME
                 AND A.CURRENT_NUMERIC_VALUE IS NOT DISTINCT FROM B.CURRENT_NUMERIC_VALUE
                 AND A.CURRENT_CHARACTER_VALUE IS NOT DISTINCT FROM B.CURRENT_CHARACTER_VALUE
UNION ALL
SELECT 'LOCALPART' AS "System Name",
       B.SYSTEM_VALUE_NAME,
       B.CURRENT_NUMERIC_VALUE,
       B.CURRENT_CHARACTER_VALUE
    FROM QSYS2.SYSTEM_VALUE_INFO A
         RIGHT EXCEPTION JOIN SESSION.Remote_System_Values B
             ON A.SYSTEM_VALUE_NAME = B.SYSTEM_VALUE_NAME
                 AND A.CURRENT_NUMERIC_VALUE IS NOT DISTINCT FROM B.CURRENT_NUMERIC_VALUE
                 AND A.CURRENT_CHARACTER_VALUE IS NOT DISTINCT FROM B.CURRENT_CHARACTER_VALUE
    ORDER BY SYSTEM_VALUE_NAME;

My Modified Query – Compare Two Remote Systems

This example creates a temporary table for remote system 1 and then creates a temporary table for remote system 2. Then you can query the two systems and compare. You will want to change SYSTEM1 and SYSTEM2 to match the entries created in the Relational Database Entries screen and you might want to give each system a more distinct name in the second query; where we perform the compare.

DECLARE GLOBAL TEMPORARY TABLE SESSION.Remote_System_Values (
            SYSTEM_VALUE_NAME,
            CURRENT_NUMERIC_VALUE,
            CURRENT_CHARACTER_VALUE
        ) AS
            (SELECT *
                    FROM SYSTEM2.QSYS2.SYSTEM_VALUE_INFO)
            WITH DATA
    WITH REPLACE;

DECLARE GLOBAL TEMPORARY TABLE SESSION.Remote_System2_Values (
            SYSTEM_VALUE_NAME,
            CURRENT_NUMERIC_VALUE,
            CURRENT_CHARACTER_VALUE
        ) AS
            (SELECT *
                    FROM SYSTEM1.QSYS2.SYSTEM_VALUE_INFO)
            WITH DATA
    WITH REPLACE;

-- Use exception join to reveal any differences
SELECT 'SYSTEM2' AS "System Name",
       A.SYSTEM_VALUE_NAME,
       A.CURRENT_NUMERIC_VALUE,
       A.CURRENT_CHARACTER_VALUE
    FROM SESSION.Remote_System2_Values A
         LEFT EXCEPTION JOIN SESSION.Remote_System_Values B
             ON A.SYSTEM_VALUE_NAME = B.SYSTEM_VALUE_NAME
                 AND A.CURRENT_NUMERIC_VALUE IS NOT DISTINCT FROM B.CURRENT_NUMERIC_VALUE
                 AND A.CURRENT_CHARACTER_VALUE IS NOT DISTINCT FROM B.CURRENT_CHARACTER_VALUE
UNION ALL
SELECT 'SYSTEM1' AS "System Name",
       B.SYSTEM_VALUE_NAME,
       B.CURRENT_NUMERIC_VALUE,
       B.CURRENT_CHARACTER_VALUE
    FROM SESSION.Remote_System2_Values A
         RIGHT EXCEPTION JOIN SESSION.Remote_System_Values B
             ON A.SYSTEM_VALUE_NAME = B.SYSTEM_VALUE_NAME
                 AND A.CURRENT_NUMERIC_VALUE IS NOT DISTINCT FROM B.CURRENT_NUMERIC_VALUE
                 AND A.CURRENT_CHARACTER_VALUE IS NOT DISTINCT FROM B.CURRENT_CHARACTER_VALUE
    ORDER BY SYSTEM_VALUE_NAME;

Example of the results:

With this information you can easily see what values are different and make whatever changes from the desired system.

As always I welcome your comments, questions, and feedback. Thank you for reading.

Related