|
On May 29, 2024, at 4:31 PM, Don Brown via MIDRANGE-L <midrange-l@xxxxxxxxxxxxxxxxxx> wrote:--
I look after a number of systems.
I have scheduled procedures to collect required information either
daily or weekly and I write it to either tables for IFS stream files.
This is then saved to a save file and sent to my system.
And a scheduled task then processes the received info.
Yes, this is a little more work to develop the collection but has
worked well for quite some time.
Just my 2 cents
Don
From: "Bryan Dietz" <bdietz400@xxxxxxxxx>
To: midrange-l@xxxxxxxxxxxxxxxxxx
Date: 30/05/2024 04:38 AM
Subject: Re: SQL procedure CONNECT TO remote system then run
command on remote system
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxxxxxxxx>
I've been interested in your journey, I too manage many servers and
often need to run commands everywhere. I would like to get some of
the things to SQL.
*years ago I wrote some CL around SBMRMTCMD and CPYF (to a ddm file)
to accomplish what I need.
looking at your latest....
I tried it out, I got errors on:
EXECUTE IMMEDIATE 'Connect to ' || v_LPSERVER ;
Message . . . . : SQL statement not allowed.
i added this to the read_loop
call systools.lprintf('fetched: ' concat v_lpserver ); to make sure I
got the right record :-)
I added:
call systools.lprintf('SQL Error: ' concat V_LPSERVER concat ' - '
concat v_errormsg );
to the "Begin" so errors would end up in the joblog.
going to keep trying, but the
EXECUTE IMMEDIATE 'Connect to ' || v_LPSERVER ;
seems to be the issue.
Bryan
Dan Bale wrote on 5/28/2024 11:27 PM:
Readers with good memory may recall my earlier post describing myefforts to create an SQL procedure that uses 3-part file names to
query files from different servers. I'm doing something similar, but
using CONNECT TO instead of 3-part names because I need to run a
command on the different servers. The meat of the procedure is:
OUTMBR(*FIRST *REPLACE)');
-- Execute the query for each LPARS row
EXECUTE IMMEDIATE 'Connect to ' || v_LPSERVER ;
EXECUTE IMMEDIATE 'DROP TABLE DBALE/$DSPPRB';
CALL QCMDEXC ('DSPPRB OUTPUT(*OUTFILE) OUTFILE(DBALE/$DSPPRB)
EXECUTE IMMEDIATE 'Disconnect ALL' ;expected via fetching through the LPARS table. Sometimes the CONNECT
I watched through debug to ensure that v_LPSERVER is changing as
TO works, but most times it doesn't. The procedure "completes
successfully", but it only ever drops the table and runs the DSPPRB
command 25 times from the same LPAR.
OUTMBR(*FIRST *REPLACE)');
Is CONNECT TO flaky or am I missing something?
In case anyone is interested, here is the procedure in full:
CREATE or REPLACE PROCEDURE dbale/DSPPRB_ALL() modifies SQL data not
deterministic language SQL specific AC_DSPPRB set option dbgview =
*SOURCE
BEGIN
DECLARE v_LPSERVER VARCHAR(18);
DECLARE v_LPSEQUENCE INT;
DECLARE done INT DEFAULT 0;
DECLARE SQLstatement VARCHAR(1024);
-- Declare a cursor to fetch rows from the LPARS table
DECLARE c_LPARS CURSOR FOR
SELECT LPSERVER, LPSEQUENCE
FROM LPARS
order by LPSEQUENCE;
-- Declare a CONTINUE HANDLER for NOT FOUND condition
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET done = 1;
-- Declare a CONTINUE HANDLER for SQLEXCEPTION condition
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
Begin
DECLARE v_errormsg VARCHAR(70) not NULL Default '';
DECLARE v_sql_stmt VARCHAR(1000);
GET DIAGNOSTICS CONDITION 1 v_errormsg = MESSAGE_TEXT;
End;
-- Ensure there are no active connections
EXECUTE IMMEDIATE 'Disconnect ALL' ;
-- Open the cursor
OPEN c_LPARS;
-- Loop through the LPARS rows
read_loop: LOOP
FETCH c_LPARS INTO v_LPSERVER, v_LPSEQUENCE;
IF done = 1 THEN
LEAVE read_loop;
END IF;
set v_errormsg = '';
-- Execute the query for each LPARS row
EXECUTE IMMEDIATE 'Connect to ' || v_LPSERVER ;
EXECUTE IMMEDIATE 'DROP TABLE DBALE/$DSPPRB';
CALL QCMDEXC ('DSPPRB OUTPUT(*OUTFILE) OUTFILE(DBALE/$DSPPRB)
EXECUTE IMMEDIATE 'Disconnect ALL' ;communication may be confidential, and is intended only for the use of
END LOOP;
-- Close the cursor
CLOSE c_LPARS;
END;
*** CONFIDENTIALITY NOTICE: The information contained in this
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. ***
--
-- .
Bryan
--
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://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.
--
This email has been scanned for computer viruses. Although MSD has taken reasonable precautions to ensure no viruses are present in this email, MSD cannot accept responsibility for any loss or damage arising from the use of this email or attachments..
--
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://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.
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
midrange.com
