Not sure if this will help but thought I'd throw it out there.
https://www.mcpressonline.com/programming/sql/techtip-unraveling-sql-error-messages-using-self
https://www.ibm.com/docs/en/i/7.5?topic=tools-sql-error-logging-facility-self
Respectfully,
Michael Mayer
IBM i on Power System Admin / VMO Specialist
ERMCO-ECI
2225 Industrial Rd
Dyersburg, Tennessee 38024
Cell, Office and Text Messages: 518.641.8906
Email: michael.mayer@xxxxxxxxxxxxx
https://www.ermco-eci.com/
https://ermcopower.com/
IBM i Award Winning Personal Blog Site:
https://ibmireference.blogspot.com/
Awarded IBM 2024 Advocate, Contributor, Influencer and IBM i Ready Badges.
2. Re: Monitor for SQL error in ACS Run SQL Scripts (Buck Calabro)
3. RE: Monitor for SQL error in ACS Run SQL Scripts (Dan Bale)
4. Re: Monitor for SQL error in ACS Run SQL Scripts
(Glenn Gundermann)
----------------------------------------------------------------------
message: 1
date: Mon, 30 Sep 2024 17:13:50 +0000
from: Michael Mayer <michael.mayer@xxxxxxxxxxxxx>
subject: IBM Guided Tours (Rob Berendt)
Thanks Rob. I noticed that as well. I had a link to the Guided Tours sign up page on my blog. I reached out to Carmelita asking how I would list out the upcoming Guided Tours Have yet to hear back.
Carmelita Ruvalcaba Cevallos
IBM i Product Manager
IBM Power
carmelit@xxxxxxxxxxx
@carmelit
Respectfully,
Michael Mayer
IBM i on Power System Admin / VMO Specialist ERMCO-ECI
2225 Industrial Rd
Dyersburg, Tennessee 38024
Cell, Office and Text Messages: 518.641.8906
Email: michael.mayer@xxxxxxxxxxxxx
https://www.ermco-eci.com/
https://ermcopower.com/
IBM i Award Winning Personal Blog Site:
https://ibmireference.blogspot.com/
Awarded IBM 2024 Advocate, Contributor, Influencer and IBM i Ready Badges.
2. IBM Guided Tours (Rob Berendt)
----------------------------------------------------------------------
message: 2
date: Mon, 30 Sep 2024 07:43:49 -0400
from: Rob Berendt <robertowenberendt@xxxxxxxxx>
subject: IBM Guided Tours
IBM i Guided Tours has changed their format from WebEx to Teams. The list of Guided Tours used to be on the IBM i home page at ibm dot com. It is no longer there. Late Friday I received an email announcing four upcoming Guided Tours. In case you are not on that email list here they are:
October 2, 2024
9:00 CT Db2 for i - SQL Error Logging Facility (SELF)
https://events.teams.microsoft.com/event/7852ab3e-dc0e-4e2a-91ff-dd0d966dfe8c@fcf67057-50c9-4ad4-98f3-ffca64add9e9
October 30, 2024
9:00 CT Db2 for i - Query Supervisor
https://events.teams.microsoft.com/event/78fa8e62-d974-4790-8238-d9b66ce136e3@fcf67057-50c9-4ad4-98f3-ffca64add9e9
November 6, 2024
9:00 CT PowerHA and BRMS updates
https://events.teams.microsoft.com/event/bc9ac59e-948a-43a8-83b8-bf193aebcff8@fcf67057-50c9-4ad4-98f3-ffca64add9e9
November 13, 2024
9:00 CT IBM i Authority Collection made usable with SQL
https://events.teams.microsoft.com/event/1275cbea-7cdf-4afa-afab-b4d6dd6f3616@fcf67057-50c9-4ad4-98f3-ffca64add9e9
------------------------------
------------------------------
message: 2
date: Mon, 30 Sep 2024 14:02:59 -0400
from: Buck Calabro <kc2hiz@xxxxxxxxx>
subject: Re: Monitor for SQL error in ACS Run SQL Scripts
On 9/30/2024 12:15 PM, Steve McKay wrote:
I have an ACS SQL script that is run against multiple partitions. The
script consists of SELECTs against various files, the
QSYS2.DATA_AREA_INFO table, and QSYS2.LICENSE_INFO table.
Because it's running against multiple partitions, the file, data area,
or license program may not exist on every system. This is expected.
When this happens, I receive an SQL error message (SQLstate: 42704,
vendor code: -443 or other). If this were a CL program, I would be
able to MONMSG each error and continue with the program. Is there a
way within ACS Run SQL Scripts to monitor for an error message and
continue the script without showing the error message? Or to display the error message in the bottom results panel?
I never use the interactive scripting tool this way.
If I have a repetitive task like that, I put the individual SQL statements into a program; one can pass the remote database/schema/etc as parameters.
Once the code is in a program, you can trap and respond to exceptions as needed. If RPG isn't your favourite (or there's no compiler on that
machine) you can do this in an SQL Procedure, ReXX program, or even Python.
--
--buck
http://wiki.midrange.com/
Your updates make it better!
------------------------------
message: 3
date: Mon, 30 Sep 2024 18:14:07 +0000
from: Dan Bale <dan.bale@xxxxxxxxxxxxxxxxxxxxx>
subject: RE: Monitor for SQL error in ACS Run SQL Scripts
AFAIK, the only way to monitor for SQL errors is via a stored procedure which has:
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION This allows the procedure to continue on its merry way if you do nothing to trap an error and respond to it in some way. Beyond that, I've not yet figured out the "respond to it in some way" part. I'm hoping the SELF guided tour Scott Fortsie is doing on Wednesday will shed some light on what I'm missing.
- Dan
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Steve McKay
Sent: Monday, September 30, 2024 12:15 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Monitor for SQL error in ACS Run SQL Scripts
I have an ACS SQL script that is run against multiple partitions. The script consists of SELECTs against various files, the QSYS2.DATA_AREA_INFO table, and QSYS2.LICENSE_INFO table.
Because it's running against multiple partitions, the file, data area, or license program may not exist on every system. This is expected. When this happens, I receive an SQL error message (SQLstate: 42704, vendor code: -443 or other). If this were a CL program, I would be able to MONMSG each error and continue with the program. Is there a way within ACS Run SQL Scripts to monitor for an error message and continue the script without showing the error message? Or to display the error message in the bottom results panel?
Thanks,
Steve McKay
*** CONFIDENTIALITY NOTICE: The information contained in this communication may be confidential, and is intended only for the use of the recipients named above. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution, or copying of this communication, or any of its contents, is strictly prohibited. If you have received this communication in error, please return it to the sender immediately and delete the original message and any copy of it from your computer system. If you have any questions concerning this message, please contact the sender. ***
------------------------------
message: 4
date: Mon, 30 Sep 2024 17:41:53 -0400
from: Glenn Gundermann <glenn.gundermann@xxxxxxxxx>
subject: Re: Monitor for SQL error in ACS Run SQL Scripts
This will run without error when the table doesn't exist.
BEGIN
DECLARE not_found CONDITION FOR '02000';
DECLARE at_end *INTEGER* DEFAULT *0*;
DECLARE if_exists *INTEGER* DEFAULT *0*;
DECLARE CONTINUE HANDLER FOR *sqlexception* SET at_end = *1*;
DECLARE CONTINUE HANDLER FOR not_found SET at_end = *1*;
SELECT *
INTO if_exists
FROM server_name.schema_name.table_name;
END;
Yours truly,
Glenn Gundermann
Email: glenn.gundermann@xxxxxxxxx
Cell: (416) 317-3144
------------------------------
Subject: Digest Footer
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) digest list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.
------------------------------
End of MIDRANGE-L Digest, Vol 23, Issue 832
*******************************************
midrange.com
