Re: SQL - Generate Spreadsheet

I wish I could remember. That ship has sailed.
--
Bryan Dietz

On Jul 2, 2025, at 5:09 PM, Don Brown via MIDRANGE-L <midrange-l@xxxxxxxxxxxxxxxxxx> wrote:

 (tip for extra lines in QSH, type an "18" then press F-14 )

This is handy, where did you find this gem and are there more gems to be
found ?

Thanks
Don



Don Brown

Senior Consultant

[1]OneTeam IT Pty Ltd
P: 1300 088 400

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
Bryan Dietz
Sent: Thursday, 3 July 2025 6:49 AM
To: midrange-l@xxxxxxxxxxxxxxxxxx
Subject: Re: SQL - Generate Spreadsheet

thinking about other ways to create the xls....
If you have the IBM i on fairly recent PTFs the acsbundle.jar exists in
the IFS.

so you could run the following from qsh

java -jar /QIBM/ProdData/Access/ACS/Base/acsbundle.jar
/system=localhost
/PLUGIN=cldownload
/sql="SELECT SYS_DNAME AS LIBRARY,SYS_ONAME AS OBJECT,
OBJTYPE,USER_NAME,OBJ_AUTH,
OBJOPER AS OPR,OBJMGT AS MGT,OBJEXIST AS EXIST,
OBJALTER AS ALTER,OBJREF AS REF,
DATA_READ AS READ,DATA_ADD AS ADD,DATA_UPD AS UPDATE,
DATA_DEL AS DELETE,DATA_EXEC AS EXECUTE
FROM QSYS2.OBJECT_PRIVILEGES
WHERE SYS_DNAME = 'QSYS'
AND OBJTYPE = '*CMD'
ORDER BY SYS_ONAME,USER_NAME"
/clientfile=/tmp/put_your_file_name_here.xls
/colheadings=1

see the GettingStarted.html and look for "9.1.17 CLDownload" section

(tip for extra lines in QSH, type an "18" then press F-14 )

if interested in a CL, I had this bookmarked:
[2]https://www.web400.com/downloads/ACS/xfrdta.clp.txt

Bryan

Jim Oberholtzer wrote on 7/2/2025 2:37 PM:

What I need to do is generate the spreadsheets, I don’t need to consume

the file in CL. The CL would only be a wrapper to run all the SQL
statements needed. The preferred output would be a spreadsheet in the IFS.

So I’m still not clear on how to build the SQL so it will at least run

in ACS.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: [3]https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
[4]https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

--
Message protected by MailGuard: e-mail anti-virus, anti-spam and content
filtering.
[5]https://www.mailguard.com.au

References

Visible links
1. https://www.oneteamit.com.au/
2. https://www.web400.com/downloads/ACS/xfrdta.clp.txt
3. https://lists.midrange.com/mailman/listinfo/midrange-l
4. https://archive.midrange.com/midrange-l.
5. https://www.mailguard.com.au/
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.