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

[Charles Wilt <charles.wilt@xxxxxxxxx>]

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.