In this Gist, I show how you can leverage ACS's jar that is shipped on your IBM i to do your bidding. In this case, we can automate the creation and downloading of a spreadsheet to an IFS file where the contents of the spreadsheet is controlled with an SQL query. After the spreadsheet is created in the IFS, we use SQL once more to email the spre…

Spreadsheets and Emails with SQL & ACS.sql

--
-- Note: 
-- Documentation for ACS's cldownload and other fantastic hidden gems can be found here:
-- ftp://ftp.software.ibm.com/as400/products/clientaccess/solutions/GettingStarted_en.html
--

--
-- Do I have the ACS jar on this IBM i?
--
select create_timestamp, acs.*
  from table (
      qsys2.ifs_object_statistics( start_path_name => '/QIBM/ProdData/Access/ACS/Base/acsbundle.jar')
    ) acs;
stop;

--
-- Is there an update available for the 'IBM HTTP Server for i' PTF Group
--
select *
  from systools.group_ptf_currency
  where ptf_group_title like '%IBM HTTP%';
stop;

--
-- Review the top 10 storage consumers
--
select a.authorization_name,
       sum(a.storage_used) as total_storage_used, b.text_description,
       b.accounting_code, b.maximum_allowed_storage
  from qsys2.user_storage a
       inner join qsys2.user_info b
         on b.user_name = a.authorization_name
  where b.user_creator <> '*IBM'
  group by a.authorization_name, b.text_description,
           b.accounting_code, b.maximum_allowed_storage
  order by total_storage_used desc
  limit 10;
stop;

--
-- Where in the IFS should I create the spreadsheet?
--
select create_timestamp, acs.*
  from table (
      qsys2.ifs_object_statistics( start_path_name => '/home/SCOTTF/')
    ) acs order by create_timestamp desc limit 5;
stop;

--
-- What is the host name of this IBM i?
--
select HOST_NAME from qsys2.system_status_info;

stop;
--
-- Procedure to drive the spreadsheet creation
--
create or replace procedure coolstuff.generate_1_spreadsheet(
   in v_ifs_path varchar(1000) for sbcs data,
   in v_spreadsheet_query varchar(1000) for sbcs data)
begin
  declare cmdtext varchar(2000) for sbcs data;
  declare v_ifs_full_path varchar(2000) for sbcs data;
  set v_ifs_full_path = v_ifs_path concat
        lpad(month(current date), 2, 0) concat
        lpad(day(current date), 2, 0)   concat 
        year(current date) concat
        '.xlsx';
-- Note: more options...
-- Control options exist on CLDOWNLOAD.
-- /colheadings=<1/0>   ====      Include column headings as the first row. When specified, the column names will be the heading.
-- /usecollabels        ====      Use column labels for the heading.
-- Suffixes             ====      .csv, .ods, .xlsx
--
-- Note 2: 
-- Check to see whether your IBM i host name matches the name you specified on line 82...."/system=COMMON1"?
-- 1) execute this query: select HOST_NAME from qsys2.system_status_info;
-- 2) change line 82, the name after /system= should be your IBM i Host name.
--
  set cmdtext =
  'STRQSH CMD(''java -jar /QIBM/ProdData/Access/ACS/Base/acsbundle.jar '
        concat '/plugin=cldownload /system=COMMON1 /clientfile='
        concat v_ifs_full_path concat ' /sql="' concat
        v_spreadsheet_query concat '"'')';
  call qsys2.qcmdexc(cmdtext);
end; 
stop;

--
-- Create a spreadsheet
--
call coolstuff.generate_1_spreadsheet(v_ifs_path => '/home/SCOTTF/theTop10',
                                      v_spreadsheet_query => 'select a.authorization_name,
       sum(a.storage_used) as total_storage_used, b.text_description,
       b.accounting_code, b.maximum_allowed_storage
  from qsys2.user_storage a
       inner join qsys2.user_info b
         on b.user_name = a.authorization_name
  group by a.authorization_name, b.text_description,
           b.accounting_code, b.maximum_allowed_storage
  order by total_storage_used desc
  limit 10');

stop; 


--
-- Did the file get created in the IFS?
--
select create_timestamp, acs.*
  from table (
      qsys2.ifs_object_statistics( start_path_name => '/home/SCOTTF/')
    ) acs 
    where path_name like '/home/SCOTTF/theTop10%'
    order by create_timestamp desc limit 1;
stop;

--
-- Email the spreadsheet 
--

-- One time setup!
cl: STRTCPSVR SERVER(*SMTP)  ;
cl: ADDUSRSMTP USRPRF(SCOTTF);

stop;

--
-- Email an IFS file
-- Note: The SNDSMTPEMM command include more parameter options that could be extended here
--
create or replace procedure coolstuff.email_1_spreadsheet(
   in v_ifs_path varchar(100) for sbcs data,
   in v_email    varchar(100) for sbcs data,
   in v_subject  varchar(100) for sbcs data,
   in v_note     varchar(100) for sbcs data
   )
begin
  declare v_cmdstmt varchar(2000) for sbcs data;
  set v_cmdstmt =
  'SNDSMTPEMM RCP((''' concat v_email concat ''' *pri)) SUBJECT(''' concat v_subject concat ''') NOTE(''' concat v_note concat ''') ATTACH((''' concat v_ifs_path concat '''))'; 
  call qsys2.qcmdexc(v_cmdstmt);
end; 
stop;

--
-- Email an IFS file
--
call coolstuff.email_1_spreadsheet( v_ifs_path => '/home/SCOTTF/theTop1008032020.xlsx',
                                    v_email    => 'forstie@us.ibm.com', 
                                    v_subject  => 'Email sent by coolstuff.email_1_spreadsheet', 
                                    v_note     => 'Here is your top 10 list of storage consumers') ;
stop;


create or replace procedure coolstuff.create_and_email_top10()
begin
declare local_ifs_path_name varchar(100) for sbcs data;
--
-- Create a spreadsheet
--
call coolstuff.generate_1_spreadsheet(v_ifs_path => '/home/SCOTTF/theTop10',
                                      v_spreadsheet_query => 'select a.authorization_name,
       sum(a.storage_used) as total_storage_used, b.text_description,
       b.accounting_code, b.maximum_allowed_storage
  from qsys2.user_storage a
       inner join qsys2.user_info b
         on b.user_name = a.authorization_name
  group by a.authorization_name, b.text_description,
           b.accounting_code, b.maximum_allowed_storage
  order by total_storage_used desc
  limit 10');
  
select path_name into local_ifs_path_name
  from table (
      qsys2.ifs_object_statistics( start_path_name => '/home/SCOTTF/')
    ) acs 
    where path_name like '/home/SCOTTF/theTop10%'
    order by create_timestamp desc limit 1;

call coolstuff.email_1_spreadsheet( v_ifs_path => local_ifs_path_name,
                                    v_email    => 'forstie@us.ibm.com', 
                                    v_subject  => 'Email sent by coolstuff.email_1_spreadsheet', 
                                    v_note     => 'Here is your top 10 list of storage consumers') ;
end;

stop;

-- Create the spreadsheets on a schedule
cl: ADDJOBSCDE JOB(SCOTTSS1) CMD(RUNSQL SQL('call coolstuff.create_and_email_top10()') COMMIT(*NONE) NAMING(*SQL)) FRQ(*WEEKLY) SCDDATE(*NONE) SCDDAY(*ALL) SCDTIME(235500)   ;

Hi.
from my experience of using it you must grant access for the USER under which the task will run to

1. write access to the target folder where the report will be created
2. read access to /QIBM/ProdData/Access/ACS/Base/acsbundle.jar
3. create and grant write access to /home/USER directory
   and of corse access to parrent folders to find the way...

Thank you for your input. I will work on it. Appreciate it.

This stuff is great!
I want to check to see if the file I am creating already exists (and delete it if it does).
Will something like this work?
-- if this file already exists, delete it first
select count(path_name) into IFS_FILE_FOUND
from table(qsys2.IFS_OBJECT_STATISTICS(
start_path_name => V_IFS_FULL_PATH
)) ;
IF (IFS_FILE_FOUND IS NOT NULL) THEN
SET IFS_RMVLNK = 'RMVLNK '' CONCAT V_IFS_FULL_PATH CONCAT ''';
call qcmdexc (IFS_RMVLNK);
END IF;

is there a better way?

Hi,
For IFS file existence, I find it a bit easier to use: QSYS2.IFS_OBJECT_references_INFO.
IFS_OBJECT_STATISTICS can return multiple rows when pointed to a directory. If you're sure that you'll never input a subdirectory for path_name, it could work.

An example can be found in this Gist:
https://gist.github.com/forstie/76885983e437d4d1d0481d4bd846f6cc

Thank you. More testing showed that code wasn't necessary. If the file already exists, it updates/replaces it.

When I added the procedure to RPG and called interactively, it showed me this screen:
Transfer request is complete.
Transfer statistics: 00:00:02
Rows transferred: 1
Press ENTER to end terminal session.

is there a way to avoid the Enter to end?

Hi.
from my experience of using it you must grant access for the USER under which the task will run to

1. write access to the target folder where the report will be created
2. read access to /QIBM/ProdData/Access/ACS/Base/acsbundle.jar
3. create and grant write access to /home/USER directory
   and of corse access to parrent folders to find the way...

I checked al the permission. Everything looks ok but still getting the error below. It works with ACS* thru interactive and sends email. But when scheduled thru batch it fails.

Command Used:

cl: ADDJOBSCDE JOB(SQWERT04) CMD(RUNSQL SQL('call qrste.create_and_email()') COMMIT(*NONE) NAMING(*SQL)) FRQ(*WEEKLY) USER(SQWERTY) JOBD(QSYS/QMSF) SCDDATE(*NONE) SCDDAY(*MON *WED *FRI) SCDTIME(080000) ;

ERROR_______________

File . . . . . : QPRINT Page/Line 1/6
Control . . . . . Columns 1 - 130
Find . . . . . .
*...+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8....+....9....+....0....+....1....+....2....+....
Transfer request is complete.
Transfer statistics: 00:00:01
Rows transferred: 4

QSH0005 Completion 00 05/05/21 16:24:33.258925 QZSHRUNC QSHELL *STMT QSQRUN4 QSYS *STMT
From module . . . . . . . . : QZSHRUNC
From procedure . . . . . . : main
Statement . . . . . . . . . : 149
To module . . . . . . . . . : QSQCALLSP
To procedure . . . . . . . : CALLPROGRAM
Statement . . . . . . . . . : 44502
Message . . . . : Command ended normally with exit status 0.
TCP5302 Escape 40 05/05/21 16:24:33.295346 QTMSCRTSNM QTCP *STMT QTMSSNDEMM QTCP *STMT
From module . . . . . . . . : QTMSCRTSNM
From procedure . . . . . . : sendError__FP13QTMSErrorCodePc
Statement . . . . . . . . . : 3
To module . . . . . . . . . : QTMSSNDEMM
To procedure . . . . . . . : main
Statement . . . . . . . . . : 53
Message . . . . : Value for parameter 6 for API not valid.
Cause . . . . . : You passed an invalid value for parameter 6 on a call to
API QTMSCreateSendEmail. This is not a supported parameter for this api.
Recovery . . . : Change the value passed to API QTMSCreateSendEmail
parameter 6 to a supported format and try the request again.

Let me know if any of you guys come up with any ideas around this error. Thanks much.

An idea.
Add a call to SYSTOOLS.LPRINTF() prior to the call to SNDSMTPEMM, to be certain as to the command being executed.
Scott

Hello Scott,

I added the SYSTOOLS.LPRINTF() line before and after the send. Please find the results below in the comments. The query gets executed and the file is getting created but it doesn't send the file when automated. It does work properly when called from ACS interactively.

create or replace procedure qrst.email_test_spreadsheet(
in v_ifs_path varchar(500) for sbcs data,
in v_email varchar(500) for sbcs data,
in v_subject varchar(100) for sbcs data,
in v_note varchar(500) for sbcs data
)
begin
declare v_cmdstmt varchar(2000) for sbcs data;
set v_cmdstmt =
'SNDSMTPEMM RCP(' concat v_email concat ') SUBJECT(''' concat v_subject concat ''') NOTE(''' concat v_note concat ''') ATTACH((''' concat v_ifs_path concat '''))';

CALL SYSTOOLS.LPRINTF('This is a test Message just before call.. sent on '
CONCAT DAYOFWEEK(CURRENT DATE) CONCAT ' at '
CONCAT CURRENT TIME); ------ This message gets displayed in job log.

call qsys2.qcmdexc(v_cmdstmt); -----This lines should send the email

CALL SYSTOOLS.LPRINTF('This is a test Message after.. sent on '
CONCAT DAYOFWEEK(CURRENT DATE) CONCAT ' at '
CONCAT CURRENT TIME); ------ This message does not get displayed job log.

end;

Hi, Scott.
After creating an XLS report, a QJVAEXEC task (per user) appears in the system and doesn't die after completion. Sys admin is worried a little about it. Is it normal that this job remains in the system? Is it possible to force end this job by providing some parameter?

SvKN0
I was able to get those jobs to go away by appending
/killdaemon
to the STRQSH cmd

Thank you.
The killdaemon option is available only for the maint plugin. It doesn't work with the CLDownload directly but with additional call of the maint plugin works well.

FTP link to the ACS documentation that listed on the top is unavailable for me. I was able to read it only from Google cache...
https://www.ibm.com/support/pages/ibm-i-access-acs-getting-started

Hello Scott,

I think you missed this condition in your original query. " where b.user_creator <> '*IBM' ". I don't see this condition when you call the coolstuff.generate_1_spreadsheet () procedure.

I was able to point this out as I am dealing with a similar case. I have a where predicate where I need to equate it with a character string. But somehow I am not able to concat the strings.

Please can you help. I want to know how would you include this condition while calling coolstuff.generate_1_spreadsheet ().

---where b.user_creator <> '*IBM' -----

I think I have this working. I use RUNSQL to call the stored procedure from a CLLE program that used to create a CSV file. When the program gets to the RUNSQL, it pops to the terminal screen to give me helpful information I really do not want. Is there way to suppress the terminal screen? Also, the stored procedure did not like pulling the data from a file in QTEMP. Is that my error or a "feature."?

RUNSQL SQL(&SQL) COMMIT(*NC) NAMING(*SYS)

Transfer statistics: 00:00:06
Rows transferred: 164
Press ENTER to end terminal session.