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