Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2003
    Posts
    11

    Unanswered: Diagnostic msgs not displayed for triggered SP!

    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.

  2. #2
    Join Date
    Apr 2003
    Posts
    191

    Re: Diagnostic msgs not displayed for triggered SP!

    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

    Originally posted by maxdp
    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.

  3. #3
    Join Date
    Dec 2003
    Posts
    11
    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.

  4. #4
    Join Date
    Apr 2003
    Posts
    191
    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

    Originally posted by maxdp
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •