Re: Need assistance with External SQL Proc

[Alan Campin <alan0307d@xxxxxxxxx>]

Have you put in debug?

I will include all the instructions but you may already know them

include in source before the BEGIN

SET OPTION DBGVIEW = *SOURCE

You will need the short name. The following SQL will get you that name.

Select ROUTINE_SCHEMA,
       ROUTINE_NAME,
       EXTERNAL_NAME
  From QSYS2/SYSPROCS
  Where ROUTINE_NAME = 'BT_MSGQ_OUT';

STRDBG   using the external name

When in the stored procedure do a SBREAK <line number> USER <YourUserName>
at the command line. User name must be in UPPERCASE and must the user your
running the procedure under.

 Now press enter and call your routine.

It should break into your session. Press F1 on the message and copy the
STRSRVJOB command to another session.

Paste it in and press enter.

Then do a STRDBG of the external name.

It should break into your source at the breakpoint you sent.

Switch back to your original job and press enter until you are back to the
command line.

You can also use RDI.

Now you should be able to step through the source code and see what is
going on.


On Thu, Jun 11, 2020 at 1:33 PM Butala, Nilesh <NButala@xxxxxxxxxxxxxxx>
wrote:

> Hello
> We have external proc which is called by our integration package using DB2
> connector. My job is populating information in data queue and external proc
> is receiving information from data queue and populate information as
> message queue. See below our external proc code:
>
> DROP TYPE &library.QueuedResponses ;
> CREATE TYPE &library.QueuedResponses as VARCHAR(32500) ARRAY[] ;
> CREATE OR REPLACE PROCEDURE &library.BT_MSGQ_OUT
>         DYNAMIC RESULT SETS 1
>         LANGUAGE SQL
>         SPECIFIC &library.BT_MSGQ_OUT
>         NOT DETERMINISTIC
>         MODIFIES SQL DATA
>         CALLED ON NULL INPUT
>         SET OPTION      ALWBLK = *ALLREAD ,
>                         ALWCPYDTA = *OPTIMIZE ,
>                         COMMIT = *NONE ,
>                         DBGVIEW = *SOURCE ,
>                         DECRESULT = (31, 31, 00) ,
>                         DFTRDBCOL = *NONE ,
>                         DLYPRP = *NO ,
>                         DYNDFTCOL = *NO ,
>                         DYNUSRPRF = *USER ,
>                         SRTSEQ = *HEX
> BEGIN
>
> DECLARE DQNAME CHAR ( 10 ) ;
> DECLARE DQLIB CHAR ( 10 ) ;
> DECLARE DQRCDLN DECIMAL ( 5 ) ;
> DECLARE DQDATA CHAR ( 32500 ) ;
> DECLARE DQWAIT DECIMAL ( 5 ) ;
> DECLARE NODATA INT ;
> DECLARE ARRAYCOUNT INT ;
> DECLARE RESPONSES &library.QueuedResponses;
>
> --Cursor is used to execute the data access (Convert array into table
> result set
> DECLARE CR CURSOR WITH RETURN FOR
>      SELECT MESSAGE
>      FROM UNNEST(RESPONSES) as RESPONSETABLE(MESSAGE);
>
> --Load RESPONSES array with DataQueue Messages...
> SET DQNAME = 'BTQUE_OUT' ;
> SET DQLIB = '&library';
> SET DQWAIT = 1 ;
> SET NODATA = 0 ;
> SET ARRAYCOUNT = 0 ;
>
> WHILE NODATA = 0 AND ARRAYCOUNT < 500 DO
>         SET DQRCDLN = 0 ;
>         SET DQDATA = '' ;
>         CALL QSYS.QRCVDTAQ(DQNAME,DQLIB,DQRCDLN,DQDATA,DQWAIT);
>         IF DQRCDLN > 0 THEN
>                 SET ARRAYCOUNT = ARRAYCOUNT+ 1;
>                 SET RESPONSES [ARRAYCOUNT] = LEFT(DQDATA, INT(DQRCDLN));
>         ELSE
>                 SET NODATA = 1 ;
>         END IF ;
> END WHILE ;
>
> --Return the cursor result set and end...
> OPEN CR;
> END  ;
>
> The issue is integration package calls this proc, they are not getting
> data, but when I execute the following command on IBM I, I can dee te data
> in DQDATA.
> CALL QSYS.QRCVDTAQ(DQNAME,DQLIB,DQRCDLN,DQDATA,DQWAIT);
> What am I doing wrong or what could be the issue?
>
> Thanks
>
>
> Nilesh Butala | IT - Enterprise Systems | Sally Beauty Holdings, Inc.
> Address: 3001 Colorado Blvd, Denton, TX 76210
> Work: 940-297-2993 | Email: NButala@xxxxxxxxxxxxxxx<mailto:
> NButala@xxxxxxxxxxxxxxx>
>
> This communication (including any attachments) may contain privileged or
> confidential information intended for a specific individual and purpose,
> and is protected by law. If you are not the intended recipient, you should
> delete this communication and/or shred the materials and any attachments
> and are hereby notified that any disclosure, copying, or distribution of
> this communication, or the taking of any action based on it, is strictly
> prohibited.
>
> --
> This is the RPG programming on IBM i (RPG400-L) mailing list
> To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at https://archive.midrange.com/rpg400-l.
>
> Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
> questions.
>
> Help support midrange.com by shopping at amazon.com with our affiliate
> link: https://amazon.midrange.com