Re: Given a list of objects, run SQL Queries on it

The equivalent is querying QSYS2.OBJECT_PRIVILEGES .I personally use for this things interactive SQLWorkbench/J (it's opensource). It is still java, it's jdbc based, and has some smarts for the ibmi (it uses native functions to i.e. retrive table source, you can see the job number servicing your conn...) and you can use it  then for many other sql databases. BLOB handling is also much better than ACS (you can see right there the image preview, the hex, text, save all the blobs to files...).
Compared to ACS it allows for easy export of the result set on an excel (or same excel but different sheet), with just a command before the SQL select (@wbexport), so basically you run the macro saved in the tool, and it generates the excel (besides the result set on video), so basically with a click you can have an "audit package".
my 2c

On Tuesday, November 25, 2025 at 04:50:21 PM GMT+1, Jim Oberholtzer <midrangel@xxxxxxxxxxxxxxxxx> wrote:

It’s audit season, and auditors being auditors want quite a bit of almost useless information from a long list of objects, IE: DSPOBJAUT on command STRDFU.  A) it’s already locked out from all users (where it even exists) except a very small list of *ALLOBJ users, B) I removed it from the production systems.  But the auditors still want to see a null data set. 

So,  what I would like to build  is an SQL based procedure that will run all the various reports they want.  The reports would be separate Excel spreadsheets for each object. 

Read list of objects
Run a series of SQL commands to get data (IE DSPOBJAUT, sql version not the command)
Create the spreadsheet
Loop back to the list until EOF.

My limited SQL abilities stop me from doing this in SQL.  I could do it in CLLE but the auditors would reject it since they cannot spell IBM i without being spotted the “I”.  The can read SQL.

Suggestions?


--
Jim Oberholtzer
Agile Technology Architects