RE: Retrieving numeric partition ID in SQL

If you use the '. ' separator (i.e. Period + Blank) in the SPLIT Table
Function the result should return the numbers correctly.
If you do not want a period at the end of an element, just RTrim it off.

If you only want the value, try something link this:
With x as (Select Distinct Try_Cast(RTrim(Element, '. ') as VarChar(256))
Element, =

                                             Message_text
                        from Table(QSYS2.Joblog_Info('*')) a =

                                  cross join Lateral (Select * =

                                                                      from
Table(systools.Split(Message_Text, '. '))) b
                        Where From_Program =3D 'QLZARCAPI')  =

Select Trim(Substr(Element, Locate_in_String(Element, ':', -1) + 1)) Vaue,
             x.* =

   from x;              =


Mit freundlichen Gr=FC=DFen / Best regards

Birgitta Hauser
Modernization =96 Education =96 Consulting on IBM i
Database and Software Architect  =

IBM Champion since 2020

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
"Train people well enough so they can leave, treat them well enough so they
don't want to. " (Richard Branson)
"Learning is experience =85 everything else is only information!" (Albert
Einstein)


-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces-+hD5IHI5Xscn3HwCXmMcX9BPR1lH4CV8@public.gmane.org> On Behalf Of Bryan
Dietz
Sent: Saturday, 1 August 2026 02:14
To: [email protected]
Subject: 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 =3D '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 =3D '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 =3D '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.

-- =

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 qu=
estions.

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