RE: Retrieving numeric partition ID in SQL

Excellent!  Nice example of using SYSTOOLS.SPLIT for this.  This is what I used initially:

select qsys2.qcmdexc('call qsys/QLZARCAPI') as callresult from SYSIBM.sysdummy1;

SELECT ELEMENT FROM TABLE(QSYS2.JOBLOG_INFO('*'))
        CROSS JOIN LATERAL (SELECT * FROM TABLE(SYSTOOLS.SPLIT(MESSAGE_TEXT, '.')))
   WHERE FROM_PROGRAM = 'QLZARCAPI'
     and (    Element like 'SYSTEM INFO -> SYSTEM SERIAL NUMBER: %'
           or Element like ' SYSTEM TYPE-MODEL: %'
           or Element like ' PROCESSOR FEATURE CODE: %'
           or Element like ' PARTITION ID: %'
           or Element like 'PARTITION INFO -> NETWORK NAME: %'
           or Element like ' PARTITION NAME: %'
         )
   order by MESSAGE_TIMESTAMP;

I had Copilot generate the SQL to use the above query as the basis to return one row with 7 columns.  One for each Element in the WHERE clause, except for SYSTEM TYPE-MODEL which has two values, Type and Model.  I can easily adapt this to use 3-part naming to pull this information for all systems from one system.  For those interested, I've posted it below.

SELECT
   MAX(
       CASE
           WHEN
               ELEMENT LIKE 'PARTITION INFO -> NETWORK NAME: %'
               THEN SUBSTR(ELEMENT, LENGTH('PARTITION INFO -> NETWORK NAME: ') + 1)
       END) AS Network_Name,
   MAX(
       CASE
           WHEN
               ELEMENT LIKE ' PARTITION NAME: %'
               THEN SUBSTR(ELEMENT, LENGTH(' PARTITION NAME: ') + 1)
       END) AS Partition_Name,
   MAX(
       CASE
           WHEN
               ELEMENT LIKE 'SYSTEM INFO -> SYSTEM SERIAL NUMBER: %'
               THEN SUBSTR(ELEMENT, LENGTH('SYSTEM INFO -> SYSTEM SERIAL NUMBER: ') + 1)
       END) AS Serial_Number,
   MAX(
       CASE
           WHEN
               ELEMENT LIKE ' SYSTEM TYPE-MODEL: %'
               THEN
                   SUBSTR(
                       SUBSTR(ELEMENT, LENGTH(' SYSTEM TYPE-MODEL: ') + 1), POSSTR(
                           SUBSTR(ELEMENT, LENGTH(' SYSTEM TYPE-MODEL: ') + 1), '- ') + 2, 3)
       END) AS System_Model,
   MAX(
       CASE
           WHEN
               ELEMENT LIKE ' PROCESSOR FEATURE CODE: %'
               THEN SUBSTR(ELEMENT, LENGTH(' PROCESSOR FEATURE CODE: ') + 1)
       END) AS Proc_Feat_Code,
   MAX(
       CASE
           WHEN
               ELEMENT LIKE ' PARTITION ID: %'
               THEN SUBSTR(ELEMENT, LENGTH(' PARTITION ID: ') + 1)
       END) AS Partition_ID,
   MAX(
       CASE
           WHEN
               ELEMENT LIKE ' SYSTEM TYPE-MODEL: %'
               THEN SUBSTR(SUBSTR(ELEMENT, LENGTH(' SYSTEM TYPE-MODEL: ') + 1), 1, 4)
       END) AS System_Type
    FROM TABLE ( QSYS2.JOBLOG_INFO('*') )
         CROSS JOIN LATERAL ( SELECT * FROM TABLE ( SYSTOOLS.SPLIT(MESSAGE_TEXT, '.') ) ) S
    WHERE FROM_PROGRAM = 'QLZARCAPI'
      AND (   Element LIKE 'SYSTEM INFO -> SYSTEM SERIAL NUMBER: %'
           OR Element LIKE ' SYSTEM TYPE-MODEL: %'
           OR Element LIKE ' PROCESSOR FEATURE CODE: %'
           OR Element LIKE ' PARTITION ID: %'
           OR Element LIKE 'PARTITION INFO -> NETWORK NAME: %'
           OR Element LIKE ' PARTITION NAME: %');

- Dan Bale

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces-+hD5IHI5Xscn3HwCXmMcX9BPR1lH4CV8@public.gmane.org> On Behalf Of cesco via MIDRANGE-L
Sent: Thursday, July 30, 2026 8:59 AM
To: Dan Bale via MIDRANGE-L <[email protected]>
Cc: cesco <emaxt6-/[email protected]>
Subject: Re: Retrieving numeric partition ID in SQL

With pure SQL you can try this one to get relevant text, yep, not super formal, but anyway...

CALL QSYS/QLZARCAPI;
SELECT ELEMENT FROM TABLE(QSYS2.JOBLOG_INFO('*')) CROSS JOIN LATERAL (SELECT * FROM TABLE(SYSTOOLS.SPLIT(MESSAGE_TEXT, '.'))) WHERE FROM_PROGRAM = 'QLZARCAPI';
*** CONFIDENTIALITY NOTICE: The information contained in this communication may be confidential, and is intended only for the use of the recipients named above. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution, or copying of this communication, or any of its contents, is strictly prohibited. If you have received this communication in error, please return it to the sender immediately and delete the original message and any copy of it from your computer system. If you have any questions concerning this message, please contact the sender. ***
-- 
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: [email protected]
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request-+hD5IHI5Xscn3HwCXmMcX9BPR1lH4CV8@public.gmane.org
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support-FMtJrHiV//lnDLsaKlm4mFaTQe2KTcn/@public.gmane.org for any subscription related questions.

You can help support midrange.com by visiting https://donate.midrange.com and making a contribution.