Skip to content

Instantly share code, notes, and snippets.

@forstie
Last active March 20, 2024 11:41
  • Star 6 You must be signed in to star a gist
  • Fork 3 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save forstie/2deda50658106461f650cd71917feff9 to your computer and use it in GitHub Desktop.
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. …
-- =================================================================
-- 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';
@forstie
Copy link
Author

forstie commented Oct 19, 2022

Ah. LPRINTF is limited to 1000.
IN PRINT_STRING VARCHAR(1000) CCSID 37
Is it possible that your string is > 1000?

@yadavsant
Copy link

Hello Scott,

Yes. Its bigger than 1000 but less than 5000. I think it is close to 1600.

@forstie
Copy link
Author

forstie commented Oct 19, 2022

When I try lprintf'ing a string longer than 1,000, I do not see the message in the joblog either, because a failure (-302) occurs on the call.
Maybe there's an SQL0302 message in your joblog.
I can look at extending the size of the lprintf string, if you think is worth doing so.

@yadavsant
Copy link

Yes. I see the -302 message too.

And my Vote for increasing the lprintf string size would be a YES. It will really help in debugging. Sometimes we do need to print and see long Strings like xml, json, email body text etc. And these kind of variable values can be long.

@forstie
Copy link
Author

forstie commented Oct 19, 2022

Ok, I'll extend it to 30,720. The message data will appear in the joblog 512 characters at a time. (max of 60 messages)

@yadavsant
Copy link

Sounds great. Thanks Scott. Appreciate it.

By when can we expect it to be part of a PTF?

@forstie
Copy link
Author

forstie commented Oct 19, 2022

You can have the improvement today.
I updated the script found here: https://gist.github.com/forstie/314dde2d3e32ef9b7b83495e6d620fff

I will get this in a PTF at some point, so that everyone has it by default.

@yadavsant
Copy link

Great. Thanks Scott.

@jeffrey-carey
Copy link

Scott - a couple of questions.
Must you relocate acsbundle.jar or can you reference /QIBM/ProdData/Access/ACS/Base/acsbundle.jar? When ACS is updated do you need to copy the new file?

Also couldn't figure out why I was getting two emails but see there are two SNDSMTPEMM (78 & 114) - they have different subject lines but otherwise seem identical. Just wondering if I'm missing something or am running more than I need.

This looks like it will automate an audit requirement for us so very anxious to get it working.

Jeff

@forstie
Copy link
Author

forstie commented Mar 9, 2023

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.

@jeffrey-carey
Copy link

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.

@forstie
Copy link
Author

forstie commented Mar 9, 2023

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

@jeffrey-carey
Copy link

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!

@VincentKKwan
Copy link

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?

@forstie
Copy link
Author

forstie commented Feb 13, 2024

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

@VincentKKwan
Copy link

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.

@forstie
Copy link
Author

forstie commented Feb 13, 2024

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.

@VincentKKwan
Copy link

VincentKKwan commented Feb 14, 2024

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).

@forstie
Copy link
Author

forstie commented Feb 14, 2024

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?

@VincentKKwan
Copy link

VincentKKwan commented Feb 14, 2024

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.

@forstie
Copy link
Author

forstie commented Feb 14, 2024

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;

@VincentKKwan
Copy link

VincentKKwan commented Feb 14, 2024

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;

@forstie
Copy link
Author

forstie commented Feb 14, 2024

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;

@forstie
Copy link
Author

forstie commented Feb 14, 2024

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

@VincentKKwan
Copy link

VincentKKwan commented Feb 15, 2024

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

@forstie
Copy link
Author

forstie commented Feb 15, 2024

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

@VincentKKwan
Copy link

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

@VincentKKwan
Copy link

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.

@forstie
Copy link
Author

forstie commented Mar 20, 2024

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment