Re: SQL procedure that will have server name passed to it to run query on 3-part name

Well, it's not hard to do in SQL...

But you still need to build and run the statement dynamically..
Here's something I put together a few years ago. Some things to note
- the use of a table, production_servers, that contains a list of our
production servers
- the use of a FOR DO to loop through the entries in that list of servers,
running the dynamic SQL statement against each


create or replace function cwilt.cool__metrics (forDate date default
current_date)
returns table (
SERVER_NAME VARCHAR(18),
LOG_PROGRAM_NAME CHAR(10),
NBR_ENTRIES INTEGER,
NBR_SUCCESS INTEGER,
NBR_SKIPPED INTEGER,
<...>
)
modifies SQL data
external action
not deterministic
language SQL
specific RTNCOOLMTR
set option dbgview = *SOURCE

begin
declare insertStmt varchar(5000);

declare global temporary table
GLOBAL_TEMP_COOL__METRICS (
SERVER_NAME VARCHAR(18),
LOG_PROGRAM_NAME CHAR(10),
NBR_ENTRIES INTEGER,
NBR_SUCCESS INTEGER,
NBR_SKIPPED INTEGER,
<...>
) with replace;

for systemLoop as systemsCursor cursor for
select * from cwilt.production_servers
do
set insertStmt =
' insert into GLOBAL_TEMP_COOL__METRICS
select current_server as server_name, log_program_name
, count(*) as nbr_entries
, sum(case when log_process_name = ''SUCCESS'' then
1 else 0 end) as nbr_success
, sum(case when log_process_name = ''SKIPPED'' then
1 else 0 end) as nbr_skipped
<...>
) as metric
from ' concat trim(server_name) concat '.loglib.logtable
L
where log_program_name in (''PGM1'',''PGM2'',''PGM3'')
and log_time between timestamp(''' concat
char(forDate, ISO) concat ''', time(''04:00:00''))
and timestamp(''' concat
char(forDate, ISO) concat ''', time(''23:59:59''))
group by log_program_name';

execute immediate InsertStmt;
end for;

return select * from GLOBAL_TEMP_COOL__METRICS;
end;

HTH,
Charles

On Mon, May 6, 2024 at 5:30 PM Dan Bale <dan.bale@xxxxxxxxxxxxxxxxxxxxx>
wrote:

Given my current script:

-- BEGIN
Create or Replace table qtemp/WRKASP as (
select 1 as Seq, Current Server as Server, ASP_NUMBER, DEVD_NAME,
100 - ( Round( DEC(DEC(TOTAL_CAPACITY_AVAILABLE, 21, 3) /
DEC(TOTAL_CAPACITY, 21, 3) * 100, 21, 3), 2) ) as Pct_Used
from qsys2.asp_info ORDER BY Asp_Number
) WITH NO DATA;
Delete from qtemp/WRKASP ;

Insert into qtemp/WRKASP (
select 1 as Seq, Current Server as Server, ASP_NUMBER, DEVD_NAME,
100 - ( Round( DEC(DEC(TOTAL_CAPACITY_AVAILABLE, 21, 3) /
DEC(TOTAL_CAPACITY, 21, 3) * 100, 21, 3), 2) ) as Pct_Used
from ABCAA1.qsys2.asp_info ORDER BY Asp_Number);

Insert into qtemp/WRKASP (
select 2 as Seq, Current Server as Server, ASP_NUMBER, DEVD_NAME,
100 - ( Round( DEC(DEC(TOTAL_CAPACITY_AVAILABLE, 21, 3) /
DEC(TOTAL_CAPACITY, 21, 3) * 100, 21, 3), 2) ) as Pct_Used
from TAA01B.qsys2.asp_info ORDER BY Asp_Number);

(... repeat for 28 more servers.)
-- END

I would like to replace the Insert statements in the script to procedure
calls:
Call RetrieveASP (1, 'ABCAA1');
Call RetrieveASP (2, 'TAA01B');
...
Call RetrieveASP (30, 'FWC03A');

The question is, can the procedure take the second parameter and use it in
a 3-part file name. E.g., 'ABCAA1' > 'FROM ABCAA1.qsys2.asp_info'.
(The first parameter is the Seq value.)

I know I could do this in CL, building the SQL statement dynamically, but
I'd like to avoid CL if the SQL solution isn't too complicated. I have
several more scripts in the pipeline that will utilize the 3-part naming
for thirty servers.
*** 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. ***
--
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.