Okay two things, I meant to type SNDMSG, sorry and what release are you on? QCMDEXC() was overloaded later on and no longer requires the length parameter. If you check the schema QSYS2 in the procedures section you should see two QCMDEXC entries.
-Matt
-----Original Message-----
From: Matt Olson <Matt.Olson@xxxxxxxx>
Sent: Wednesday, August 11, 2021 1:52 PM
To: Tyler, Matt <matt.tyler@xxxxxxxxxxxxxx>; Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: Printing messages in a DB2 for IBMi stored procedure
Matt, tried this and get the following error:
Message: [CPF0006] Errors occurred in command. Cause . . . . . : If the wrong length was passed on one part of the command, other messages are issued because the wrong part of the command is being analyzed. Recovery . . . : See the previously listed messages in the job log. Correct the errors and then try the command again.
Command SNDPGMMSG not allowed in this setting.
Here is my procedure:
CREATE OR REPLACE PROCEDURE
ARRTFLIB.Detect_Identity_Counter_Issues
(
IN peSchemaName VARCHAR(128)
)
LANGUAGE SQL
MODIFIES SQL DATA
BEGIN
DECLARE sqlcode INTEGER;
DECLARE maxid BIGINT;
DECLARE idcolname VARCHAR(128);
DECLARE stmttxt VARCHAR(1000);
DECLARE stmttxtmain VARCHAR(1000);
declare tablename varchar(1000);
DECLARE at_end INTEGER DEFAULT 0;
DECLARE not_found CONDITION FOR SQLSTATE '02000';
DECLARE msgvar varchar(100);
DECLARE curmain CURSOR FOR
SELECT sc.column_name, sc.table_name FROM
qsys2.syscolumns AS sc WHERE sc.table_schema = peSchemaName AND sc.is_identity = 'YES';
OPEN curmain;
main_loop:
LOOP
FETCH curmain INTO idcolname, tablename;
IF at_end = 1 THEN
LEAVE main_loop;
END IF;
SET msgvar = 'Table: ' || tablename || ' Column: ' || idcolname;
Call QSYS2.QCMDEXC('SNDPGMMSG MSGID(TAA9871) MSG(' ||msgvar||')');
END LOOP;
CLOSE curmain;
END
;
-----Original Message-----
From: Tyler, Matt <matt.tyler@xxxxxxxxxxxxxx>
Sent: Wednesday, August 11, 2021 2:38 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Cc: Matt Olson <Matt.Olson@xxxxxxxx>
Subject: RE: Printing messages in a DB2 for IBMi stored procedure
CAUTION: This email originated from outside of the ARRT. Do not click links or open attachments unless you recognize the sender and know the content is safe.
Call QSYS2.QCMDEXC( 'SNDPGMMSG ...');
A real world example (using a TAATOOL command).
set msgvar = '''Cleaning up from prior PCE loads''';
call qcmdexc('SNDJLGMSG MSGID(TAA9871) MSG(' ||msgvar||')');
set msgvar = '''SQL Code / SQL STATE for ABR history load of 8,9,15 '
||SQLCODE ||'/' ||SQLSTATE ||'''';
call qcmdexc('SNDJLGMSG MSGID(TAA9871) MSG(' ||msgvar||')');
-Matt
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Matt Olson via MIDRANGE-L
Sent: Wednesday, August 11, 2021 1:35 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Cc: Matt Olson <Matt.Olson@xxxxxxxx>
Subject: Printing messages in a DB2 for IBMi stored procedure
DB2 LUW has this so you can put in debug output statements in a stored procedure:
https://urldefense.com/v3/__https://www.ibm.com/support/producthub/db2/docs/content/SSEPGG_11.5.0/com.ibm.db2.luw.apdv.sqlpl.doc/doc/r0053551.html__;!!O6xM9Yim9Yk!r63cPT9qvVeRIKfhWpNUerLEonQknSWiFETbloCR2vD63QizqADk6HkXSjfqCd_JozVn$
SQL Server has "Print" commands where you can output messages in a stored procedure.
Where is the printing capability in the IBMi for stored procedures so I can output some result information as the procedure is looping through the data?
Matt
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
https://urldefense.com/v3/__https://lists.midrange.com/mailman/listinfo/midrange-l__;!!O6xM9Yim9Yk!r63cPT9qvVeRIKfhWpNUerLEonQknSWiFETbloCR2vD63QizqADk6HkXSjfqCd1WqtDi$
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://urldefense.com/v3/__https://archive.midrange.com/midrange-l__;!!O6xM9Yim9Yk!r63cPT9qvVeRIKfhWpNUerLEonQknSWiFETbloCR2vD63QizqADk6HkXSjfqCfuIwKPq$ .
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
https://urldefense.com/v3/__https://amazon.midrange.com__;!!O6xM9Yim9Yk!r63cPT9qvVeRIKfhWpNUerLEonQknSWiFETbloCR2vD63QizqADk6HkXSjfqCVnTE-gj$
midrange.com
