Re: How to retrieve second level text from message in SQL stored procedure -- MIDRANGE-L

Hi Marco

I do wonder if you are coming to a general conclusion based on a single example. In your email below you give the documented definition of DB2_TOKEN_STRING - that is the following -

DB2_TOKEN_STRING
Returns a X'FF' delimited string of the tokens for the specified diagnostic.

Your statement -

So, once you have the single tokens in an array just split the single
tokens and use them in reverse order.

is not from IBM, right? It is a discovery you made, in this 1 case. You had to use more than 1 separator to split the elements further - how is one to know to split things into 3 parts when the DB2_TOKEN_COUNT is 2?

It will be very cool if you have discovered a general way to handle these things.

Then there are already diagnostic elements that have split DB2_TOKEN_STRING - the DB2_ORDINAL_TOKEN_n array, and in the test you have 2 elements, DB2_ORDINAL_TOKEN_1 and DB2_ORDINAL_TOKEN_2. Again, I do not see anything to tell me what separators to use.

There was a MIDRANGE thread from 2013 that I found interesting, it includes a comment from Jon Paris - here's the link - https://archive.midrange.com/rpg400-l/201311/msg00099.html

And here's Jon's comment showing that DB2_TOKEN_STRING didn't give everything in MSGDTA - at least, that's how I read it - Jon seems to say that part of the MSGDTA is there but not all.

The MESSAGE_TEXT appears to only include the level 1 text. The actual
cause of the error (which is in level 2) is not apparently supplied.

However - by the "highly scientific" method of looking at all the other
diagnostic values I discovered that _part_ of the second level text appears
to be placed in DB2_TOKEN_STRING. No way of knowing if this is always true
- but in this instance it has what I need - which is the reason why an XML
validation failed.

Sadly it is still not as good as the job log info. It only includes the
latter part of the second level text, not the really useful stuff like the
position of the error in the source.

*Regards*

*Vern Hamberg*

IBM Champion 2025 <cid:part1.M41dIrGD.FZ22FX5x@centurylink.net> CAAC (COMMON Americas Advisory Council) IBM Influencer 2023

On 6/14/2025 12:30 PM, Marco Facchinetti wrote:

There was a mistake:

AND MESSAGE_ID = :wMsg;

About the token string, this is from IBM docs:

DB2_TOKEN_STRING
Returns a X'FF' delimited string of the tokens for the specified diagnostic.

So, once you have the single tokens in an array just split the single
tokens and use them in reverse order.

I don't think IBM is doing something very diffrent when populating job log.

HTH
--
Marco Facchinetti

Mr S.r.l.

Tel. 035 962885
Cel. 393 9620498

Skype: facchinettimarco

Il giorno sab 14 giu 2025 alle ore 00:33 Vern Hamberg via MIDRANGE-L <
midrange-l@xxxxxxxxxxxxxxxxxx> ha scritto:

Agreed, Peter. Still, I'm not convinced that the DB2_TOKEN_STRING or
even the DB2_ORDINAL_TOKEN_n array (my interpretation) map reliably to
MSGDTA. I mean, one of the tokens is the qualified file name. I might
play with Marco's approach, with more substitution variables. I don't
know if I can reproduce Dan's scenario.

*Regards*

*Vern Hamberg*

IBM Champion 2025<cid:part1.IHYe2CSl.KtB0ualw@centurylink.net> CAAC
(COMMON Americas Advisory Council) IBM Influencer 2023

On 6/13/2025 4:45 PM, Peter Dow wrote:

Re (1), RTVMSG

"is used in a CL program or REXX procedure to retrieve a specified
predefined message from a message file and to copy it into CL
variables. Substitution values can be specified in the MSGDTA
parameter (as a single character string containing one or more
concatenated message data fields) to replace the substitution
variables in the predefined message text."

--
*Peter Dow* /
909 793-9050
petercdow@xxxxxxxxx
/

On 6/13/2025 8:15 AM, James H. H. Lampert via MIDRANGE-L wrote:

I may be sucking antimatter on this, but as I recall,

(1) there are CL commands and/or system APIs to retrieve messages
from the job's message queue, including the second-level text, and

(2) SQL stored procedures don't *have* to be pure SQL.

--
JHHL

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email:MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit:https://lists.midrange.com/mailman/listinfo/midrange-l
or email:MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
athttps://archive.midrange.com/midrange-l.

Please contactsupport@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.