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. …

Generating spreadsheets with SQL.sql

-- =================================================================
-- 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';

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 ?

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

Hi Scott, Thanks you for responding. I have the acsbundle.jar in /home/acs - See image below. I am getting closer - I am running; java -Dcom.ibm.iaccess.ActLikeExternal=true -jar /home/acs/acsbundle.jar /plugin=cldownload /system=MSD820 /clientfile=/home/MSDDCB/systemnameGroupPTFCurrency /sql=SELECT * FROM systools.group_ptf_currency and getting error MSGCLS004 - The syntax of the command is incorrect. (!callmethod) I can't see anything obvious ??? can you ? Don Brown Director MSD Information Technology t: 07 3368 7888 m: 0408 751 644 e: dbrown@msd.net.au www.msd.net.au From: "Scott Forstie" <notifications@github.com> To: "forstie" <forstie@noreply.github.com> Cc: "msddcb" <don.brown@msd.net.au>, "Comment" <comment@noreply.github.com> Date: 09/07/2020 09:44 AM Subject: Newsletter / Marketing: Re: forstie/Generating spreadsheets with SQL.sql @forstie commented on this gist. 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 — You are receiving this because you commented. Reply to this email directly, view it on GitHub, or unsubscribe.

…

______________________________________________________________________ This email has been scanned by the Symantec Email Security.cloud service.

______________________________________________________________________

______________________________________________________________________ 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.

______________________________________________________________________

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"

DOH !!!! Thank you!! Don Brown Director MSD Information Technology t: 07 3368 7888 m: 0408 751 644 e: dbrown@msd.net.au www.msd.net.au From: "Scott Forstie" <notifications@github.com> To: "forstie" <forstie@noreply.github.com> Cc: "msddcb" <don.brown@msd.net.au>, "Comment" <comment@noreply.github.com> Date: 09/07/2020 11:36 AM Subject: Newsletter / Marketing: Re: forstie/Generating spreadsheets with SQL.sql @forstie commented on this gist. You're missing the double quotes (") which should appear at the start and end of you SQL statement text. Change this: /sql=SELECT * FROM systools.group_ptf_currency to this: /sql="SELECT * FROM systools.group_ptf_currency" — You are receiving this because you commented. Reply to this email directly, view it on GitHub, or unsubscribe.

…

______________________________________________________________________ This email has been scanned by the Symantec Email Security.cloud service.

______________________________________________________________________

______________________________________________________________________ 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.

______________________________________________________________________

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!)

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

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

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

Hello Scott,

I am trying to send an email but I am getting a Truncation Error. I was using SNDSMTPEMM first. But, then it failed with Truncation Error as there are certain limits to its mail body text size. Then I switched to command SNDDST. But I was surprised as it failed too. I was using the MSG type *LONGMSG which can handle 5000 characters. But still it failed. My email body text is not that big. Here is the error and the my code.

Error: --

 SQL State: 22001 Vendor Code: -302 Message: [SQL0302] Conversion error on variable or parameter *N. Cause . . . . . :   Variable or parameter *N or entry 1 in a descriptor area contains a value that cannot be converted to the attributes required by the statement.  Error type 9 occurred.  Error types and their meanings are: 1 -- Overflow. 2 -- Floating point overflow. 3 -- Floating point underflow. 4 -- Floating point conversion error. 5 -- Not an exact result. 6 -- Numeric data that is not valid. 7 -- Double-byte character set (DBCS) or UTF-8 data that is not valid. 8 -- C NUL-terminator is missing for character variables or double NUL-terminator is missing for graphic variables and the program was compiled with the *CNULRQD option. 9 -- Truncation when mapping a variable or constant to a character or binary parameter on a CALL statement, or when using a character or graphic variable in a GET DESCRIPTOR or SET DESCRIPTOR statement. 10 -- Incompatible conversion from the input SQLDATA value to the specified SQLTYPE in a REXX application. 11 -- Overflow on translation of UTF-8 character data. If the variable name is *N and the statement is FETCH, a descriptor area was specified. If the parameter name is *N and the statement is CALL, a descriptor area, a constant, or a special register was specified. Recovery  . . . :   Change the value of the variable or parameter or entry in the descriptor area so that it can be converted and is valid.  Try the request again.

-------------------------------My Code:-------------------------

CREATE OR REPLACE PROCEDURE QGPL.SEND_EMAIL(
in v_email varchar(200) for sbcs data,
in v_subject varchar(100) for sbcs data,
in v_note varchar(5000) for sbcs data,
in v_attach varchar(10) for sbcs data
)
BEGIN

DECLARE v_dstd varchar(44) for sbcs data;
DECLARE v_stmt varchar(6000) for sbcs data;

SET v_dstd = 'Email Message';

CALL SYSTOOLS.LPRINTF('v_email: ' CONCAT v_email);
CALL SYSTOOLS.LPRINTF('v_subject: ' CONCAT v_subject);
CALL SYSTOOLS.LPRINTF('v_note: ' CONCAT v_note);
CALL SYSTOOLS.LPRINTF('v_attach: ' CONCAT v_attach);

SET v_stmt = 'SNDDST TYPE(*LMSG) TOINTNET(' CONCAT v_email CONCAT ') DSTD(''' CONCAT v_dstd CONCAT ''') LONGMSG(''' CONCAT v_note CONCAT ''') PTY(*HIGH) SUBJECT(''' CONCAT v_subject CONCAT ''')';

CALL SYSTOOLS.LPRINTF('FULL STRING: ' CONCAT v_stmt); -------This statement is not printing in Job Log
CALL qsys2.qcmdexc(v_stmt);

END;

If an lprintf doesn't appear in the joblog, its mostly likely because the string is NULL.
The string will be NULL if any element of the concatenation is NULL.
Maybe v_dstd is NULL.

Hello Scott,

Thank you for our response. I was able to pass the error. v_dstd is not null. I am setting it thru SET command. It seems the issue was in the CALL SYSTOOLS.LPRINTF('FULL STRING: ' CONCAT v_stmt); statement.

Seems like LPRINTF cant handle to print a string that big. So I removed that line and the code worked.

Ah. LPRINTF is limited to 1000.
IN PRINT_STRING VARCHAR(1000) CCSID 37
Is it possible that your string is > 1000?

Hello Scott,

Yes. Its bigger than 1000 but less than 5000. I think it is close to 1600.

When I try lprintf'ing a string longer than 1,000, I do not see the message in the joblog either, because a failure (-302) occurs on the call.
Maybe there's an SQL0302 message in your joblog.
I can look at extending the size of the lprintf string, if you think is worth doing so.

Yes. I see the -302 message too.

And my Vote for increasing the lprintf string size would be a YES. It will really help in debugging. Sometimes we do need to print and see long Strings like xml, json, email body text etc. And these kind of variable values can be long.

Ok, I'll extend it to 30,720. The message data will appear in the joblog 512 characters at a time. (max of 60 messages)

Sounds great. Thanks Scott. Appreciate it.

By when can we expect it to be part of a PTF?

You can have the improvement today.
I updated the script found here: https://gist.github.com/forstie/314dde2d3e32ef9b7b83495e6d620fff

I will get this in a PTF at some point, so that everyone has it by default.

Great. Thanks Scott.

Scott - a couple of questions.
Must you relocate acsbundle.jar or can you reference /QIBM/ProdData/Access/ACS/Base/acsbundle.jar? When ACS is updated do you need to copy the new file?

Also couldn't figure out why I was getting two emails but see there are two SNDSMTPEMM (78 & 114) - they have different subject lines but otherwise seem identical. Just wondering if I'm missing something or am running more than I need.

This looks like it will automate an audit requirement for us so very anxious to get it working.

Jeff

Hi Jeff,
When I initially created this Gist, ACS was not shipping with the IBM i operating system, so a one-time manual establishment of the acsbundle.jar was a required pre-req.
Now that acsbundle.jar ships with the HTTP PTF Group, SQL examples like this one can just point to the jar shipped by IBM.
When the jar is updated by an HTTP PTF Group level, I know of no change needed by the SQL consumer.
Note... I did not say that you need to copy the jar. Just use the path to point to the jar shipped by IBM.

generate_spreadsheets() - generates the spreadsheet to the IFS ... and ... emails it
coolstuff.email_spreadsheets () - simply shows how to send emails using SQL, including attachments found in the IFS

Glad to see that this can help.

That makes sense! That "locate" makes much more sense now.
Great - I get it so I should be able to just use generate_spreadsheets()

almost there - now I just need to capture job log and send it along too to meet audit requirements. I might just wrap a CL around this to do that with a CPYSPLF and add that to SNDSMTPEMM as attachment. I think the trick will be attaching the correct splf.

If it were me, I'd use this handy-dandy GENERATE_PDF scalar function that generates a PDF file in the Integrated File System containing the content of a spooled file.
https://www.ibm.com/docs/en/i/7.5?topic=services-generate-pdf-scalar-function

Knew you'd have a better way! Just have to make the name unique in a way that I can reference it in the ATTACH of the SNDSMTPEMM, which should be too hard. Thanks!