Hi,
I'm triggering a DB2 SP but the diagnostic msg that I provide in the SP is not displayed when an error is encountered. Below is the structure I have in the Linkage section (I've cut and paste from the manual). The parameter at the bottom (varchar 70) is the one I populate but that value is not getting displayed, only a couple of tokens, both having the same value (the name of the SP) are displayed. Does anyone have any idea how to display that diagnostic msg properly?



DB2SQL: Like GENERAL WITH NULLS, option DB2SQL lets you supply a null value for any parameter that is passed to the stored procedure. In addition, DB2 passes input and output parameters to the stored procedure that contain this information:

The SQLSTATE that is to be returned to DB2. This is a CHAR(5) parameter that can have the same values as those that are returned from a user-defined function. See "Passing parameter values to and from a user-defined function" in topic 3.3.3.1.4 for valid SQLSTATE values.

The qualified name of the stored procedure. This is a VARCHAR(27) value.

The specific name of the stored procedure. The specific name is a VARCHAR(18) value that is the same as the unqualified name.

The SQL diagnostic string that is to be returned to DB2. This is a VARCHAR(70) value. Use this area to pass descriptive information about an error or warning to the caller.

Hi,

what do you mean - triggering? That means displaying a message from a stored procedure from within a trigger?

To the best of my knowledge, triggers cannot be used to display messages - it just makes no sense if you are trying to do that.

Do you mind pasting your SP and your trigger? It would be easier to help you then.

Johann

Johann, I'm pasting all the relevant pieces of info:

From the SP
-------------

01 HV-TBL-LOC SQL TYPE IS TABLE LIKE CX_ALERT_QUEUE AS LOCATOR.


EXEC SQL
DECLARE C1 CURSOR FOR
SELECT *
FROM TABLE(:HV-TBL-LOC LIKE CX_ALERT_QUEUE)
END-EXEC.

************************************************** **********
LINKAGE SECTION.

01 LK-TABLE-LOCATOR-IN PIC S9(9) COMP.
01 LK-P-NULL-INDICATOR PIC S9(4) COMP.
01 LK-P-SQLSTATE-OUT PIC X(05).
01 LK-P-QUAL-PROC-OUT.
49 LK-P-QUAL-PROC-LEN-OUT PIC S9(04) USAGE BINARY.
49 LK-P-QUAL-PROC-TEXT-OUT PIC X(27).
49 LK-P-QUAL-PROC-TEXT-OUT PIC X(110).
01 LK-P-SPEC-PROC-OUT.
49 LK-P-SPEC-PROC-LEN-OUT PIC S9(04) USAGE BINARY.
49 LK-P-SPEC-PROC-TEXT-OUT PIC X(18).
49 LK-P-SPEC-PROC-TEXT-OUT PIC X(30).
01 LK-P-DIAG-MSG-OUT.
49 LK-P-DIAG-MSG-LEN-OUT PIC S9(04) USAGE BINARY.
49 LK-P-DIAG-MSG-TEXT-OUT PIC X(70).
49 LK-P-DIAG-MSG-TEXT-OUT PIC X(30).

************************************************** **********

PROCEDURE DIVISION
USING
LK-TABLE-LOCATOR-IN
, LK-P-NULL-INDICATOR
, LK-P-SQLSTATE-OUT
, LK-P-QUAL-PROC-OUT
, LK-P-SPEC-PROC-OUT
, LK-P-DIAG-MSG-OUT.

************************************************** **********
And finally, a sample of how I'm populating the diagnostics (SQLSTATE, etc)

EXEC SQL
SELECT RUN_PARM_1
,RUN_PARM_2
,RUN_PARM_3
INTO :WS-RUN-PARM1
,:WS-RUN-PARM2
,:WS-RUN-PARM3
FROM CX_ONLINE_DRIVER
WHERE PROCESS_ID = 'KTCSP216'
END-EXEC

IF SQLCODE NOT EQUAL +0
MOVE '38E01' TO LK-P-SQLSTATE-OUT
MOVE 12 TO LK-P-DIAG-MSG-LEN-OUT
MOVE 'DRVR SELECT ' TO LK-P-DIAG-MSG-TEXT-OUT
PERFORM W999-SHOW-ERROR THRU W999-EXIT
END-IF.

The problem here is that the 'DRVR SELECT' is not getting displayed, i need this bcos it adds value to the error being returned.

Hi,

unfortunately, I am completely at loss here - this looks like host db2 stuff and has, I believe, nothing to do with triggers. Can anybody else more knowledgable share his or her thoughts on this?

Johann