-
-
Save forstie/2deda50658106461f650cd71917feff9 to your computer and use it in GitHub Desktop.
-- ================================================================= | |
-- 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'; | |
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.
Thank you Scott for your patience and your assistance! Your willingness to assist is what is remembered. I will submit an IBM Support ticket.