Re: Retrieving numeric partition ID in SQL

i recall doing something similar, but i ran into 2 issues where 
splitting at a "." is not the best.
1- there is a period in the processor capacity (min/max/desired), ie. 
1.50, so the split needs to be ". " for the elements and ".<lf/cr>" for 
the ending of the different sections.
2- this is the real PITA, in the PARTITION INFO section there is no 
ending "."

i had opened a PMR about the missing "." and was told those immortal 
words "Working as designed".
So i opened an idea https://ideas.ibm.com/ideas/IBMI-I-4784

Bryan

Dan Bale via MIDRANGE-L wrote on 7/30/2026 12:44 PM:
> 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. ***

-- 

-- .
Bryan

-- 
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.