Re: Need assistance with External SQL Proc

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