z/OS : MESSAGE_TEXT formatting in a Trigger

John Kliewe

z/OS : MESSAGE_TEXT formatting in a Trigger

I have a trigger that sets the MESSAGE_TEXT and issues a SIGNAL.

When running an INSERT statement from DSNUTILB using EXEC SQL,  I am seeing the message_text in the job output.  But I am not understanding how it formats that messaage_text.  The SQL Ref manual says that the first 70 bytes are returned in the SQLCA, which appears to be the case in my job output.

 

But how does DSNUTILB format those 70 bytes?  My output shows the first 21 bytes of my message_text written after the DSNU1188I error message,  then a skip to the next line, then 24 blanks, and then the remaining 49 bytes immediately after.

I would like to understand how and why DSNUTILB does this.  Does anyone know?

Roy Boxwell

z/OS : MESSAGE_TEXT formatting in a Trigger
(in response to John Kliewe)
Are you looking at the log output?? If so this is formatted “after the fact”... A screen shot of exactly how, and where, your message looks would help a lot.



Roy Boxwell

SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: <mailto:[login to unmask email]> [login to unmask email]
Web http://www.seg.de http://www.seg.de

https://www.seg.de/corporate/rechtliche-hinweise/datenschutz Link zur Datenschutzerklärung


Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Ulf Heinrich



From: John Kliewe [mailto:[login to unmask email]
Sent: Thursday, December 27, 2018 3:18 PM
To: [login to unmask email]
Subject: [DB2-L] - z/OS : MESSAGE_TEXT formatting in a Trigger



I have a trigger that sets the MESSAGE_TEXT and issues a SIGNAL.

When running an INSERT statement from DSNUTILB using EXEC SQL, I am seeing the message_text in the job output. But I am not understanding how it formats that messaage_text. The SQL Ref manual says that the first 70 bytes are returned in the SQLCA, which appears to be the case in my job output.



But how does DSNUTILB format those 70 bytes? My output shows the first 21 bytes of my message_text written after the DSNU1188I error message, then a skip to the next line, then 24 blanks, and then the remaining 49 bytes immediately after.

I would like to understand how and why DSNUTILB does this. Does anyone know?



-----End Original Message-----

Attachments

  • smime.p7s (5.1k)

John Kliewe

RE: z/OS : MESSAGE_TEXT formatting in a Trigger
(in response to Roy Boxwell)

This is my trigger :

CREATE TRIGGER FMT_MSG
NO CASCADE BEFORE INSERT ON REQT02
REFERENCING NEW AS TNEW
FOR EACH ROW MODE DB2SQL
WHEN (TNEW.REQTYPE = 'TEXT')
SIGNAL SQLSTATE '70001' SET MESSAGE_TEXT =
'TO BE OR NOT TO BE THAT IS THE '
|| 'QUESTION. WHETHER TIS NOBLER '
|| 'IN THE MIND TO SUFFER THE SLINGS'
`

 

This is the job that fires it :

//GO EXEC PGM=DSNUTILB,PARM=('DSN'),COND=(4,LT)
//SYSIN DD *,SYMBOLS=JCLONLY
EXEC SQL
INSERT INTO ICFSSP00.REQT02(REQTYPE)
VALUES('TEXT')
ENDEXEC
//UTPRINT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*

 

And I've attached a screen capture of the SYSPRINT file that gets created.  My question is why are there so many blanks between the words 'THAT' and 'IS' ?

 

The message does look better when I run it from DSNTEP2, but my requirement is to use DSNUTILB.

Attachments

  • Capture.PNG (42.7k)

Roy Boxwell

z/OS : MESSAGE_TEXT formatting in a Trigger
(in response to John Kliewe)
You are being “caught” by DSNUTILBs friendly auto-correct message handling...



If you just define “solid” text (no spaces!) then you get:



DSNU000I 362 07:31:36.08 DSNUGUTC - OUTPUT START FOR UTILITY, UTILID = BOXWELL.BOXWELL$

DSNU1044I 362 07:31:36.10 DSNUGTIS - PROCESSING SYSIN AS EBCDIC

DSNU050I 362 07:31:36.11 DSNUGUTC - EXEC SQL INSERT INTO BOXWELL.REQT02(REQTYPE) VALUES('TEXT') ENDEXEC

DSNU1188I 362 07:31:36.16 DSNUGSQL - SQLCODE = -438, ERROR: APPLICATION RAISED ERROR WITH DIAGNOSTIC TEXT:

12345678901234567890123-

45678901234567890123456789012345678901234567890

DSNU1198I 362 07:31:36.16 DSNUGSQL - SQLSTATE = 70001 SQLSTATE RETURN CODE

DSNU1195I 362 07:31:36.16 DSNUGSQL - SQLERRP = DSNXRTYP SQL PROCEDURE DETECTING ERROR

DSNU1196I 362 07:31:36.17 DSNUGSQL - SQLERRD = 1 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION

DSNU1196I 362 07:31:36.17 DSNUGSQL - SQLERRD = X'00000001' X'00000000' X'00000000' X'FFFFFFFF' X'00000000'

X'00000000' SQL

DIAGNOSTIC INFORMATION



Here you can see that you get 23 characters of message text on the next line then a Continuation “hyphen” and then the rest of the text. However if you add a Space between the first characters DSNUTILB then adds it to the end of the first text (if there is room)



Put a space anywhere from 12 to 23 and it breaks on the space like this:

DSNU1188I 362 07:39:30.82 DSNUGSQL - SQLCODE = -438, ERROR: APPLICATION RAISED ERROR WITH DIAGNOSTIC TEXT:

1234567890

23456789012345678901234567890123456789012345678901234567890

DSNU1198I 362 07:39:30.83 DSNUGSQL - SQLSTATE = 70001 SQLSTATE RETURN CODE



Note there is no continuation as it broke on a space not in the middle of a word!

Put a space before position 12 and it breaks, again with a continuation character:



DSNU1188I 362 07:39:56.73 DSNUGSQL - SQLCODE = -438, ERROR: APPLICATION RAISED ERROR WITH DIAGNOSTIC TEXT:

123456789 1234567890123-

45678901234567890123456789012345678901234567890

DSNU1198I 362 07:39:56.73 DSNUGSQL - SQLSTATE = 70001 SQLSTATE RETURN CODE



Finally, if there is a space before position 10 it adds this first text to the end of the first line:

DSNU1188I 362 07:42:47.08 DSNUGSQL - SQLCODE = -438, ERROR: APPLICATION RAISED ERROR WITH DIAGNOSTIC TEXT: 12345678

01234567890123-

45678901234567890123456789012345678901234567890

DSNU1198I 362 07:42:47.08 DSNUGSQL - SQLSTATE = 70001 SQLSTATE RETURN CODE



Note how the continued text is always in the same position – at least that is always the same!



I hope that helps explain it...



Roy Boxwell

SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: <mailto:[login to unmask email]> [login to unmask email]
Web http://www.seg.de http://www.seg.de

https://www.seg.de/corporate/rechtliche-hinweise/datenschutz Link zur Datenschutzerklärung


Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Ulf Heinrich



From: John Kliewe [mailto:[login to unmask email]
Sent: Thursday, December 27, 2018 4:38 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: z/OS : MESSAGE_TEXT formatting in a Trigger



This is my trigger :

CREATE TRIGGER FMT_MSG
NO CASCADE BEFORE INSERT ON REQT02
REFERENCING NEW AS TNEW
FOR EACH ROW MODE DB2SQL
WHEN (TNEW.REQTYPE = 'TEXT')
SIGNAL SQLSTATE '70001' SET MESSAGE_TEXT =
'TO BE OR NOT TO BE THAT IS THE '
|| 'QUESTION. WHETHER TIS NOBLER '
|| 'IN THE MIND TO SUFFER THE SLINGS'
`



This is the job that fires it :

//GO EXEC PGM=DSNUTILB,PARM=('DSN'),COND=(4,LT)
//SYSIN DD *,SYMBOLS=JCLONLY
EXEC SQL
INSERT INTO ICFSSP00.REQT02(REQTYPE)
VALUES('TEXT')
ENDEXEC
//UTPRINT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*



And I've attached a screen capture of the SYSPRINT file that gets created. My question is why are there so many blanks between the words 'THAT' and 'IS' ?



The message does look better when I run it from DSNTEP2, but my requirement is to use DSNUTILB.



-----End Original Message-----

Attachments

  • smime.p7s (5.1k)

John Kliewe

RE: z/OS : MESSAGE_TEXT formatting in a Trigger
(in response to Roy Boxwell)

Wow!

 

Crazy, right?  That is some pretty wacky formatting.  I wonder what it is really meant for?

Thanks for your help.  

Roy Boxwell

z/OS : MESSAGE_TEXT formatting in a Trigger
(in response to John Kliewe)
Well it is all down to the “normal” record length of SYSOUT files... IBM love 121 with FBA but there are exceptions... VBA 121 or FBA 133 can happen!

Roy Boxwell
SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-
Heinrichstrasse 83-85
40239 Düsseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]
http://www.seg.de
Link zur Datenschutzerklärung

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Ulf Heinrich

> On 4 Jan 2019, at 20:31, John Kliewe <[login to unmask email]> wrote:
>
> Wow!
>
>
>
> Crazy, right? That is some pretty wacky formatting. I wonder what it is really meant for?
>
> Thanks for your help.
>
>
> Site Links: View post online View mailing list online Start new thread via email Unsubscribe from this mailing list Manage your subscription
>
> This email has been sent to: [login to unmask email]
> ESAi has well-regarded tools for Fast Cloning, Buffer Pool Tuning, Log Analysis, TDM & more.
> BCV4, BCV5, BPA4DB2, ULT4DB2... modern power tools to get the job done faster & easier than ever.
> http://www.ESAIGroup.com/idug
>
>
> Use of this email content is governed by the terms of service at:
> http://www.idug.org/p/cm/ld/fid=2
>
Attachments

  • smime.p7s (3.9k)