-
-
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'; | |
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!
Hello Scott
I have two problems:
- Empty created xlsx file with size of zero even when there is data (fields in my file are only normal character and date fields). No errors are recorded. If I change to a csv or ods file I will get all the data in the output file.
- If I run using a user profile that has *NONE for the password I get an error indicating that *NONE is not allowed for a password. I am not explicitly using any user or password parameters.
I have the above problems regardless of whether I use the the SQL statement (via STRSQL or Run SQL Scripts) or running QSHELL against the Java acsbundle.jar plugin=cldownload. I am on IBM i V7R5 TR3 and the acsbundle.jar is dated 2023-07-28 (yyyy-mm-dd)
values SYSTOOLS.GENERATE_SPREADSHEET(
PATH_NAME => '/home/myuser/testfile',
SPREADSHEET_QUERY => 'SELECT * FROM mylib.myfile',
SPREADSHEET_TYPE => 'xlsx',
COLUMN_HEADINGS => 'COLUMN')
QSYS/STRQSH CMD('java -Dcom.ibm.iaccess.ActLikeExternal=true -jar
/QIBM/proddata/Access/ACS/Base/acsbundle.jar /plugin=cldownload
/system=MYSYSTEM /clientfile=/home/myuser/testfile.xlsx /sql="SELECT * FROM mylib.myfile" /colheadings=1')
If I use the ACS Data Transfer the xlsx is created but not via the above sql or command.
What could be the problem?
Sure enough, this fails for me as well when attempted by a user that has Password(*NONE).
Using authority collection, I was able to determine that the generate failed due to a lack of authority to use ACS.
JOEUSER3 | /QIBM/ProdData/Access/ACS *DIR
current - *EXCLUDE
required - *OBJOPR *EXECUTE
You could repeat these steps on your IBM i to see the failure detail.
For the least privileges issue, I expected someone to ask about this. It’s a bit complex, at least it seems to be so.
I used Authority Collection to find (and authorize) the security dependencies within the IFS.
After these changes, the user with NO authority was permitted to use CLDOWNLOAD and GENERATE_SPREADSHEET.
-- /QIBM/ProdData/Access/ACS *DIR *OBJOPR *EXECUTE *EXCLUDE
cl:CHGAUT OBJ('/QIBM/ProdData/Access/ACS') USER(JOEUSER) DTAAUT(*RX);
-- /QOpenSys/QIBM/ProdData/JavaVM/jdk80/64bit/jre/.systemPrefs *DIR *OBJOPR *ADD *DLT *UPD *OBJOPR *READ *EXECUTE
-- /QOpenSys/QIBM/ProdData/JavaVM/jdk80/64bit/jre/.systemPrefs/com/ibm/iaccess/base/prefs.xml *STMF *OBJEXIST *OBJOPR *READ *ADD *DLT *UPD
cl:CHGAUT OBJ('/QOpenSys/QIBM/ProdData/JavaVM/jdk80/64bit/jre/.systemPrefs') USER(JOEUSER) DTAAUT(*RWX);
cl:CHGAUT OBJ('/QOpenSys/QIBM/ProdData/JavaVM/jdk80/64bit/jre/.systemPrefs/com/ibm/iaccess/base/prefs.xml') USER(JOEUSER) OBJAUT(*OBJEXIST);
-- /QOpenSys/QIBM/ProdData/JavaVM/jdk80/64bit/jre/.systemPrefs/com/ibm/iaccess/base/prefs.xml *STMF *OBJOPR *READ *OBJEXIST
-- /QOpenSys/QIBM/ProdData/JavaVM/jdk80/64bit/jre/.systemPrefs/.system.lock *STMF *OBJOPR *ADD *DLT *UPD *OBJOPR *READ
cl:CHGAUT OBJ('/QOpenSys/QIBM/ProdData/JavaVM/jdk80/64bit/jre/.systemPrefs/com/ibm/iaccess/base/prefs.xml') USER(JOEUSER) DTAAUT(*RX) OBJAUT(*OBJEXIST);
cl:CHGAUT OBJ('/QOpenSys/QIBM/ProdData/JavaVM/jdk80/64bit/jre/.systemPrefs/.system.lock') USER(JOEUSER) DTAAUT(*RWX);
-- /QOpenSys/QIBM/ProdData/JavaVM/jdk80/64bit/jre/.systemPrefs/.systemRootModFile *STMF *OWNER *OBJOPR *READ
cl:CHGOWN OBJ('/QOpenSys/QIBM/ProdData/JavaVM/jdk80/64bit/jre/.systemPrefs/.systemRootModFile') NEWOWN(JOEUSER) RVKOLDAUT(*NO);
Hi Scott
Thank you for your reply. I have run the above commands for the user profile but the problem persists. An empty xlsx file is created even when data exists. That same user profile can however create a csv, ods or a txt file with no problems. Why would it fail for a xlsx file and it does not log any errors.
Are there any messages in the joblog?
Did you try starting authority collection for that user?
Authority collection showed me the specific errors, when the spreadsheet failed to create due to an authority topic, which may not apply to this situation.
Does xlsx work if you change COLUMN to NONE? Just curious.
This even happens with a user profile that has user class *SECOFR.
Using 'NONE' or 'COLUMN' or 'LABEL' makes no difference.
STRQSH on acsbundle:
There are no messages in the joblog. Command ended normally with exit status 0. There are no spool files and no dumps. I copied out the acsbundle.jar from my PC and placed it into \home\myuser and that did not help either.
GENERATE_SPREADSHEET (in ACS Run Sql Scripts):
The first file I tried had no error but an empty xlsx. I tried another file and it gave the following error.
Command ended normally with exit status 20.
Message GENERATE_SPREADSHEET: ACS - cldownload FAILED - CONSULT JOBLOG FOR
DETAILS returned from SIGNAL, RESIGNAL, or RAISE_ERROR.
Message GENERATE_SPREADSHEET: FAILED WITH SQLCODE=-438 SQLSTATE=VSP05
MESSAGE=GENERATE_SPREADSHEET: ACS - cldownload FAILED - CONSULT JOBLOG
FOR DETAILS returned from SIGNAL, RESIGNAL, or RAISE_ERROR.
User-defined function error on member QSQPTABL.
User-defined function error on member QSQPTABL.
Message GENERATE_SPREADSHEET: FAILED WITH SQLCODE=-438 SQLSTATE=VSP05
MESSAGE=GENERATE_SPREADSHEET: ACS - cldownload FAILED - CONSULT JOBLOG
FOR DETAILS returned from SIGNAL, RESIGNAL, or RAISE_ERROR.
Message . . . . : User-defined function error on member QSQPTABL.
Cause . . . . . : An error occurred while invoking user-defined function
GENERATE_SPREADSHEET in library SYSTOOLS. The error occurred while invoking
the associated external program or service program GENSPREAD in library
SYSTOOLS, program entry point or external name GENERATE_SPREADSHEET_1,
specific name GENSPREAD. The error occurred on member QSQPTABL file QSQPTABL
in library QSYS2. The error code is 1. The error codes and their meanings
Authority Control:
I ran STRAUTCOL USRPRF(MYUSER) OBJ(*ALL) OBJTYPE(*ALL) INCDLO(*ALL) INCFSOBJ(*ALL) DLTCOL( YES)
then ran GENERATE_SPREADSHEET
then ran
select s.path_name, s.REQUIRED_AUTHORITY, s.CURRENT_AUTHORITY, s. from qsys2.authority_collection s
where s.AUTHORIZATION_NAME = 'MYUSER' and s.REQUIRED_AUTHORITY <> s.CURRENT_AUTHORITY
Results indicated that I have all the required authorities (in a lot of cases I have more authority than what is required eg: *USE vs *CHANGE). We have a third party software vendor that also creates xlsx files and uses Java on our IBM i. I am starting to think that the problem is not authority related but settings related problem with perhaps the third party software vendors setting overriding things leading to an empty xlsx for the IBM i commands??
Still surprised that it can create ods and csv files only on our machines (test and production).
If you're using IBM i 7.4 or 7.5, there is an "official" version of generate_spreadsheet.
Are you on IBM i 7.4 or 7.5?
I am on IBM i 7.5 TR3 and am using the official GENERATE_SPREADSHEET in SYSTOOLS and the acsbundle is the one that the PTF has placed in the /QIBM/ProdData/Access/ACS/Base.
Even running the sample IBM i database file QIWS.QCUSTCDT to /tmp produces an empty xlsx file.
If you run this query, do you see 26?
select ptf_group_level
from qsys2.grouP_ptf_info
where ptf_group_name = 'SF99704' and
ptf_grouP_status = 'INSTALLED'
order by ptf_group_level desc
limit 1;
I ran the above sql query and it had no records returned.
WRKPTFGRP SF99704 also displays nothing.
Running the following sql shows I am on V7R5M0 TR3:
SELECT CURRENT SERVER CONCAT ' is running ' CONCAT PTF_GROUP_TARGET_RELEASE
CONCAT ' with TR level: ' CONCAT PTF_GROUP_LEVEL AS TR_LEVEL
FROM QSYS2.GROUP_PTF_INFO WHERE PTF_GROUP_DESCRIPTION = 'TECHNOLOGY REFRESH'
AND PTF_GROUP_STATUS = 'INSTALLED' ORDER BY PTF_GROUP_TARGET_RELEASE,
PTF_GROUP_LEVEL DESC
LIMIT 1;
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;
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:
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.