Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
In this working example, we establish an SQL table which contains the spreadsheets we'd like to have generated. Each row in the table includes the query that will supply the data for the spreadsheet, and the location in the IFS where the spreadsheet should reside. Further, a different procedure emails the spreadsheets to an interested consumer. …
-- =================================================================
-- Author: Scott Forstie
-- Email : forstie@us.ibm.com
-- Date : January 10, 2020
-- =================================================================
--
-- Setup:
-- 1) create and populate the spreadsheet generator table
-- 2) change the procedure source code:
-- 2a) Use ACS to upload acsbundle.jar to /home/acs/acsbundle.jar
-- 2b) Change forstie@us.ibm.com to your email address. <==== Please don't email me your spreadsheets :)
-- 2c) Change /system=COMMON1 to use the name of your IBM i
-- 2d) Change the ADDUSRSMTP user profile to be your user profile
--
cl: STRTCPSVR SERVER(*SMTP) ;
cl: ADDUSRSMTP USRPRF(SCOTTF);
create schema coolstuff;
create table coolstuff.spreadsheet_generator
(IFS_PATH varchar(1000),
Spreadsheet_query varchar(10000)
);
truncate coolstuff.spreadsheet_generator;
insert into coolstuff.spreadsheet_generator
values ('/home/SCOTTF/systemnameGroupPTFCurrency',
'SELECT * FROM systools.group_ptf_currency');
insert into coolstuff.spreadsheet_generator
values ('/home/SCOTTF/top10spool',
'select user_name, sum(size) as total_spool_space from qsys2.output_queue_entries_basic group by user_name order by total_spool_space desc limit 10');
insert into coolstuff.spreadsheet_generator
values ('/home/SCOTTF/ALLOBJusers',
'select authorization_name,status,no_password_indicator,previous_signon,text_description
from qsys2.user_info where special_authorities like ''''%*ALLOBJ%'''' or authorization_name in (select user_profile_name
from qsys2.group_profile_entries where group_profile_name in (select authorization_name
from qsys2.user_info where special_authorities like ''''%*ALLOBJ%'''')) order by authorization_name');
select * from coolstuff.spreadsheet_generator;
stop;
--
-- 1 time setup:
-- =============
-- a) Locate ACS (acsbundle.jar) in the IFS at /home/acs/acsbundle.jar
-- b) Update the code below to use the name of your system
-- change COMMON1 to your system name
-- If you're on IBM i 7.3 or higher, with a current Db2 PTF group, just query QSYS2.SYSTEM_STATUS_INFO.
-- select partition_name from qsys2.system_status_info
--
create or replace procedure coolstuff.generate_spreadsheets()
begin
declare cmdtext clob(2k);
declare v_cmdstmt varchar(2000);
declare v_ifs_path varchar(1000);
declare v_ifs_full_path varchar(2000);
declare v_spreadsheet_query varchar(10000);
declare not_found condition for '02000';
declare at_end integer default 0;
declare spreadsheets cursor for
select ifs_path, spreadsheet_query
from coolstuff.spreadsheet_generator;
declare continue handler for sqlexception set at_end = 1;
declare continue handler for not_found set at_end = 1;
open spreadsheets;
fetch from spreadsheets into v_ifs_path, v_spreadsheet_query;
while (at_end = 0) do
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';
set cmdtext =
'STRQSH CMD(''java -Dcom.ibm.iaccess.ActLikeExternal=true -jar /home/acs/acsbundle.jar '
concat '/plugin=cldownload /system=COMMON1 /clientfile=' concat v_ifs_full_path
concat ' /sql="' concat v_spreadsheet_query concat '"'')';
-- call systools.lprintf('Speadsheet being generated: ' concat v_ifs_full_path);
call qsys2.qcmdexc(cmdtext);
set v_cmdstmt =
'SNDSMTPEMM RCP((''forstie@us.ibm.com'' *pri)) SUBJECT(''COMMON1 report:' concat
v_ifs_full_path concat ''') NOTE(''Common1 report ' concat
lpad(month(current date), 2, 0) concat lpad(day(current date), 2, 0) concat
year(current date) concat ''') ATTACH(''' concat v_ifs_full_path concat ''')';
-- call systools.lprintf('Speadsheet being emailed: ' concat v_ifs_full_path);
call qsys2.qcmdexc(v_cmdstmt);
fetch from spreadsheets into v_ifs_path, v_spreadsheet_query;
end while;
close spreadsheets;
end;
stop;
call coolstuff.generate_spreadsheets();
stop;
create or replace procedure coolstuff.email_spreadsheets ()
begin
declare cmdtext clob(2k);
declare v_cmdstmt varchar(2000);
declare v_ifs_path varchar(1000);
declare v_ifs_full_path varchar(2000);
declare v_spreadsheet_query varchar(10000);
declare not_found condition for '02000';
declare at_end integer default 0;
declare spreadsheets cursor for
select ifs_path, spreadsheet_query
from coolstuff.spreadsheet_generator;
declare continue handler for sqlexception set at_end = 1;
declare continue handler for not_found set at_end = 1;
open spreadsheets;
fetch from spreadsheets into v_ifs_path, v_spreadsheet_query;
while (at_end = 0) do
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';
call systools.lprintf('Speadsheet being emailed: ' concat v_ifs_full_path);
set v_cmdstmt =
'SNDSMTPEMM RCP((''forstie@us.ibm.com'' *pri)) SUBJECT(''COMMON1 report'') NOTE(''Common1 report '
concat lpad(month(current date), 2, 0) concat
lpad(day(current date), 2, 0) concat year(current date) concat
''') ATTACH(''' concat v_ifs_full_path concat ''')';
call qsys2.qcmdexc(v_cmdstmt);
fetch from spreadsheets into v_ifs_path, v_spreadsheet_query;
end while;
close spreadsheets;
end;
stop;
call coolstuff.email_spreadsheets();
stop;
-- Create the spreadsheets on a schedule
cl: ADDJOBSCDE JOB(SSHEETS) CMD(RUNSQL SQL('call coolstuff.generate_spreadsheets()') COMMIT(*NONE) NAMING(*SQL)) FRQ(*WEEKLY) SCDDATE(*NONE) SCDDAY(*ALL) SCDTIME(235500) ;
stop;
--
-- description: Review the setup
--
select *
from qsys2.scheduled_job_info
where scheduled_job_name = 'SSHEETS';
@carlosir

This comment has been minimized.

Copy link

@carlosir carlosir commented Jan 12, 2020

Wow, thanks Scott

@forstie

This comment has been minimized.

Copy link
Owner Author

@forstie forstie commented Jan 13, 2020

You're welcome. Glad that you got the gist!

@msddcb

This comment has been minimized.

Copy link

@msddcb msddcb commented Jul 8, 2020

Where do I find documentation on /plugin=cldownload - when I try to run this I get an error that the plugin is not found.. I have version 1.1.8.4 of the acsbundle.jar ?

@forstie

This comment has been minimized.

Copy link
Owner Author

@forstie forstie commented Jul 8, 2020

Hi.
Have you located the ACS jar onto the IBM i you're using?
This Gist has a pre-req of doing so and the jar should be in this path: /home/acs/acsbundle.jar

If you have the latest HTTP PTF Group level installed, you have the option of changing this example to use this path, because the ACS team is shipping the ACS jar on your IBM i via this PTF Group.

/QIBM/proddata/Access/ACS/Base/acsbundle.jar

The cldownload documentation is found in the ACS Getting started document.
Here is one place to find this document:
ftp://ftp.software.ibm.com/as400/products/clientaccess/solutions/GettingStarted_en.html

hth...Scott

@msddcb

This comment has been minimized.

Copy link

@msddcb msddcb commented Jul 9, 2020

@forstie

This comment has been minimized.

Copy link
Owner Author

@forstie forstie commented Jul 9, 2020

You're missing the double quotes (") which should appear at the start and end of the SQL statement text.

Change this: /sql=SELECT * FROM systools.group_ptf_currency
to this: /sql="SELECT * FROM systools.group_ptf_currency"

@msddcb

This comment has been minimized.

Copy link

@msddcb msddcb commented Jul 9, 2020

@Faq400Git

This comment has been minimized.

Copy link

@Faq400Git Faq400Git commented Nov 10, 2020

Very useful Scott, thank you.
I replaced some of my CPYTOSTMF and CSV with this stored procedure and XLSX. Great! I just have a little problem ... an annoying problem ... when my SQL statement return 0 records ... ACS do not create an empty XLSX (only with column headers) and I can't find a parameter or something else to tell ACS "Hey guy, don't worry about rows ... could you please give me back an xlsx with my columns' headers?, thank you!" ... maybe in a word only parameter!)

@forstie

This comment has been minimized.

Copy link
Owner Author

@forstie forstie commented Nov 10, 2020

Hello Roberto.
The cldownload plugin currently does not include an option to generate an empty spreadsheet. The team requests that you open an RFE if you'd like the support added.

For reference, there are some controls for column headers.

9.1.17 CLDownload
/PLUGIN=cldownload /system=
[/userid=]
{/hostfile=<library/filename> | /sql="statement"}
{/clientfile=. | /display}
[/]

/userid     - user id to use when connecting to the target system
/hostfile   - Source library and file on the IBM i system for the download
              e.g. /hostfile=QIWS/QCUSTCDT
/sql        - specify an SQL statement
              e.g. /sql="select CUSNUM,LSTNAM,INIT,ZIPCOD from QIWS/QCUSTCDT"
/clientfile - Target file location for the download.
              The format of this file will be determined by the specified
              extension (for example, .csv .ods .xlsx .xlsx)
              If the file extension is not specified or is of a type
              not supported, the data will be formatted as a .csv file
/display    - write the output to the terminal

Valid options are:
   /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.

Regards, Scott

@Faq400Git

This comment has been minimized.

Copy link

@Faq400Git Faq400Git commented Nov 10, 2020

Thank you, Scott, I will try some workaround now and I'll open a new RFE for this case.
Thanks, Roberto

@ajut400

This comment has been minimized.

Copy link

@ajut400 ajut400 commented Jan 25, 2021

Hi Scott,
Its a very usefull tool
Is there an option for CLDOWNLOAD plugin to change the date format of the generated file? I have not found a way to change the date format with any environment variable or some property when running the acsbundle.jar
Date fields its always generated in M/D/Y and I need ISO or EUR format

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment