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

The TR level is different from the Database PTF Group level.
Database enhancements ship in the Database PTF Group.

Sorry, this is the query you'll need to run.

select ptf_group_level from qsys2.grouP_ptf_info where ptf_group_name = 'SF99950' and ptf_grouP_status = 'INSTALLED' order by ptf_group_level desc limit 1;

It displays a 5.

Verified also with WRKPTFGRP SF99950. Level 5 and Level 4 have status 'INSTALLED' .

You are exactly where you need to be.
I had hoped to avoid this, but could you please open an IBM Support ticket?

Thank you Scott for your patience and your assistance! Your willingness to assist is what is remembered. I will submit an IBM Support ticket.

Thank you Scott for your patience and your assistance! Your willingness to assist is what is remembered. I will submit an IBM Support ticket.

A ticket was submitted to IBM Support. It is as I had expected; there was a third party Excel solution in the /qibm/userdata/java400/EXT folder. After removing the third party software (in this case it was the Apache POI jar files - 3 of them) from the folder it all started working again.

Ok then!
Thank you for closing the loop on the difficulty.

@VincentKKwan - What exactly were the problematic files? I am getting the "FAILED WITH SQLCODE=-438 SQLSTATE=VSP05" error.

We are on 7.4 and just upgraded to level 26, so GENERATE_SPREADSHEET is brand new for us.

We do have a bunch of files in that directory, but I have no idea what they are, or whether they are needed for something else.

@jkyeung the poi files that caused us problems were specifically (where 20081019 is just the date in yyyymmdd):

/qibm/userdata/java400/EXT/poi-contrib-3.2-FINAL-20081019.jar
/qibm/userdata/java400/EXT/poi-scratchpad-3.2-FINAL-20081019.jar
/qibm/userdata/java400/EXT/poi-3.2-FINAL-20081019.jar

hope this helps?

Thanks. We definitely don't have any of those. But maybe other JARs also interfere with GENERATE_SPREADSHEET.

Just curious - was POI still being used? If so, were you able to keep using it despite removing those three files?

@jkyeung We moved the 3 Apache POI jar files out of the /qibm/userdata/java400/EXT folder and placed them in a new folder. We then changed the CLASSPATH environment variable on the IBM i to explicitly include the 3 jar files by their fully qualified name so that programs could continue to work.

@VincentKKwan - Thanks, that's a helpful strategy to know. It sounds like something everyone learns if they work with Java on IBM i enough.

@jkyeung Hello. Did you manage to solve the error problem? I have a similar problem and can't find a solution yet....

@jkyeung Hello. Did you manage to solve the error problem? I have a similar problem and can't find a solution yet....

I had the same issue by trying to run a query on a temporary table (GLOBAL TEMPORARY, or QTEMP), i switched to creating the table in another library then dropping it. now generate_spreadsheet works fine

@jkyeung Hello. Did you manage to solve the error problem? I have a similar problem and can't find a solution yet....

I had the same issue by trying to run a query on a temporary table (GLOBAL TEMPORARY, or QTEMP), i switched to creating the table in another library then dropping it. now generate_spreadsheet works fine

Good plan.

@jkyeung Hello. Did you manage to solve the error problem? I have a similar problem and can't find a solution yet....

I had the same issue by trying to run a query on a temporary table (GLOBAL TEMPORARY, or QTEMP), i switched to creating the table in another library then dropping it. now generate_spreadsheet works fine

I don't use temporary table.
I found the initial error from JAVA:
MSGSOCK007 - Could not connect. (A remote host did not respond within the timeout period. (Connection timed out)).

Probably i don't have some PTF's, i'm not sure yet.

PTF Group levels for GENERATE_SPREADSHEET
IBM i 7.5 SF99950 Level 7 & IBM i 7.4 SF99704 Level 28:

https://www.ibm.com/support/pages/node/7031280

PTF Group levels for GENERATE_SPREADSHEET IBM i 7.5 SF99950 Level 7 & IBM i 7.4 SF99704 Level 28:

https://www.ibm.com/support/pages/node/7031280

IBM specifies these PTFs for all scalar functions, but most scalar functions work well.

@Venholm - Yes! A colleague and I eventually found our problem: we do not have DNS set up the way GENERATE_SPREADSHEET is expecting. So the system parameter for the underlying ACS function was not usable. The system could not be found.

We verified this by using the raw IP address for system in the ACS call, which worked correctly.

So there were two takeaways from this exercise:

1. We didn't have to wait for any PTF from IBM to provide GENERATE_SPREADSHEET; we could have simply used the underlying ACS function ourselves, had we known about it. The SQL wrapper was merely a convenience, and even then only a minor one if you were trying to generate spreadsheets from a CLP or a non-SQL RPG program.
2. QSYS2.SYSTEM_STATUS_INFO (which is mentioned in the code comments above, and probably what the officially released GENERATE_SPREADSHEET relies on) is not a reliable way to get the local system address. Better is to use QSYS2.NETSTAT_ROUTE_INFO.

We wound up ditching GENERATE_SPREADSHEET in favor of our own ACS wrapper (mostly CL, with a small SQLRPGLE helper to get the system IP). And actually, this was just because my colleague wanted to try out the new features in our latest upgrade (SF99704 Level 26, on a 7.4 system; and don't get me started on the disaster that is SEND_EMAIL!). There are more sophisticated spreadsheet generators out there, including one I wrote in Python.

Have the same error: GENERATE_SPREADSHEET: FAILED WITH
SQLCODE=-438 SQLSTATE=VSP05 MESSAGE=GENERATE_SPREADSHEET: ACS - cldownload
FAILED - CONSULT JOBLOG FOR DETAILS
This only occurs when a regular system user executes the program which attempts to generate the spreadsheet. There are no errors when running under my user profile.
Checked various auth settings, function usage, object auth, IFS folders and objects and cannot find any explanation.
GENERATE_SPREADSHEET function is set to *allow usage. I also set the user group profile with use authority.
Confirmed auth to /QIBM/proddata/Access/ACS/Base/acsbundle.jar
And to the /tmp/Spreadsheet folder that is used in the generate spreadsheet command.
Any other suggestions?

Have the same error: GENERATE_SPREADSHEET: FAILED WITH SQLCODE=-438 SQLSTATE=VSP05 MESSAGE=GENERATE_SPREADSHEET: ACS - cldownload FAILED - CONSULT JOBLOG FOR DETAILS This only occurs when a regular system user executes the program which attempts to generate the spreadsheet. There are no errors when running under my user profile. Checked various auth settings, function usage, object auth, IFS folders and objects and cannot find any explanation. GENERATE_SPREADSHEET function is set to *allow usage. I also set the user group profile with use authority. Confirmed auth to /QIBM/proddata/Access/ACS/Base/acsbundle.jar And to the /tmp/Spreadsheet folder that is used in the generate spreadsheet command. Any other suggestions?

Does the user used to run the command have the rights to acces the table and/or schema used in the query ?

The program creates a file in the DOWNLOAD library and that part is working correctly. That library has Public *ALL auth since it is used for temporary files that will be emailed/downloaded by the user. The user has auth rights to the file within that library as well..
SYSTOOLS has Public *USE auth.

SPREADSHEET COMMAND: QSYS/STRQSH CMD('java -jar
/QIBM/proddata/Access/ACS/Base/acsbundle.jar /plugin=cldownload
/system=SYSNAME /clientfile=/tmp/spreadsheet/spreadsheetdata.xlsx
/hostfile=DOWNLOAD/WORKFILE /colheadings=1 /usecollabels')

The program creates a file in the DOWNLOAD library and that part is working correctly. That library has Public *ALL auth since it is used for temporary files that will be emailed/downloaded by the user. The user has auth rights to the file within that library as well.. SYSTOOLS has Public *USE auth.

SPREADSHEET COMMAND: QSYS/STRQSH CMD('java -jar /QIBM/proddata/Access/ACS/Base/acsbundle.jar /plugin=cldownload /system=SYSNAME /clientfile=/tmp/spreadsheet/spreadsheetdata.xlsx /hostfile=DOWNLOAD/WORKFILE /colheadings=1 /usecollabels')

If it works for you but not for the user it usually means that your environment settings are different to the users environment settings. I would recommend you look at your environment variables WRKENVVAR *SYS and *JOB and look for those JAVA environment variables like CLASSPATH etc.. that may contain values that are different to your own.

Hello Scott,
SYSTOOLS.GENERATE_SPREADSHEET is not working when db column names contains characters like £ or §.

I tried to change my JOB CCSID from 1144 to 37 without successo.

Here is a sample
sqlCommand = 'SELECT A§NDOM from mytable WHERE A§NDOM= ''12345'' ';

DSPJOBLOG shows that § is replaced by @

Thanks,

Variant characters in library, file, or column names can be frustrating to work with.
Can you use the long SQL name for the A§NDOM column, as a alternative approach?
If there isn't a long SQL column name for A§NDOM, add one using create or replace table () with data on replace preserve rows.

Variant characters in library, file, or column names can be frustrating to work with. Can you use the long SQL name for the A§NDOM column, as a alternative approach? If there isn't a long SQL column name for A§NDOM, add one using create or replace table () with data on replace preserve rows.

Thank you!

Hello Scott,

I have a couple of extra features/requests:

• 1 Parameter to define column separator for CSV files (i.e. comma , semicolon, tab)
• 2 Parameter to define if quotes are to be used to split values.
• 3 For interactive 5250 sessions an option to remove ENTER and log at the end of SYSTOOLS.GENERATE_SPREADSHEET execution

Thank you
Giovanni

Hello Scott,
...

• 3 For interactive 5250 sessions an option to remove ENTER and log at the end of SYSTOOLS.GENERATE_SPREADSHEET execution

Thank you Giovanni

For Point 3 you can use the Environnement Variable ' QIBM_QSH_CMD_OUTPUT' set to 'NONE' to avoid that

Hello Scott,
...

• 3 For interactive 5250 sessions an option to remove ENTER and log at the end of SYSTOOLS.GENERATE_SPREADSHEET execution

Thank you Giovanni

For Point 3 you can use the Environnement Variable ' QIBM_QSH_CMD_OUTPUT' set to 'NONE' to avoid that

Great! Thanks!

Oh boy, this function works in mysterious ways. For posterity—and to hopefully assist a few others along the way with the Generate_Spreadsheet function and the ever-so-helpful error message:
SQLSTATE=VSP05 MESSAGE=GENERATE_SPREADSHEET: ACS - cldownload FAILED - CONSULT JOBLOG FOR DETAILS.
here's a few finding of mine.

• VSP05 Error: If you encounter a VSP05 error, note that it is not an authority error on the ACS file (authority errors are denoted as VSP06).
• Environment Variable: When troubleshooting, ensure that the environment variable QIBM_QSH_CMD_OUTPUT is set to 'STDOUT'. This configuration will ensure that a Java dump is created in /home/_USER_/IBM/iAccessClient/Dumps.
• JT400 Compatibility: Verify that you don't have an outdated or misplaced JT400 or similar files in QIBM/USERDATA/JAVA400 (this one took quite some time to figure out). JT400 should in theory only be on ProdData.
• User Enablement: The user running the command must be enabled and have an active password, even for scheduled job users.
• Temporary Tables: Avoid using queries on QTEMP or temporary tables, as Generate_Spreadsheet runs in a new job, which won't share the QTEMP library.

Hopefully, this will help clarify some common pitfalls and save others some troubleshooting time!

@JulienBellio / @forstie This was very helpful thank you. I had an issue where this wasn't working for some users, and it relates to their user profiles being expired. This is an issue for us as we use single signon primarily which allows you to sign on even if you password is expired, but also have a system policy to set passwords to expired after 3 months, as not all users use SSO. Something to note.