-
-
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'; | |
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.
It displays a 5.
Verified also with WRKPTFGRP SF99950. Level 5 and Level 4 have status 'INSTALLED' .