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
