SQL Tools v3.4.0

This latest version includes the move to no-charge licensing. SQL Tools is now "free" to IBM i customers. Just download, install and start using it immediately.
This latest build includes:
Latest Refresh:
Updated the QINTER, QBATCH, QHTTPSVR, and QUSRNOMAX SQL VIEWS to show the subsystem and User more effectively.
Prior Releases:
Bug fixes and some corrections in our build routine.
Enhanced our FD_LIST (File description list) table function so that it now returns one row for each record format in Logical Files. Now, multi-format logical file formats are returned. Previously only the first one was returned.
Corrected an issue in the IFS_READ functions when a non-IFS file is being used. Only IFS_READ_BIN (Binary) supports non-IFS files (that is /QSYS.LIB files) and returns raw data properly. The other return an internal failure to read and no rows.
Added a new USRSPC_DATA table function that returns one or more user space attributes along with the data contained in the user space. The previous USRSPC_LIST table function did not return the User Space data, so this new USRSPC_DATA function returns the same columns as USRSPC_LIST plus 2 additional columns, DATA and DATA_BIN. The DATA parameter is a CLOB, while the DATA_BIN is a BLOB. Use the CLOB column to view the content as text data, while the DATA_BIN (BLOB) version returns the content in form that can be used with data structures or even the IBM SQL INTERPRET scalar function to extract the data you need. Previously we included the RTVUSRSPC table function that returned one user space's data. Now you can cycle through or simply view a list of user spaces and their content.
Corrected an issue in JOB_LIST where non-existing jobs are returned. Now *NOTFOUND is returned for Jobs that were not found. This is the way the QGYOLJOB API returns qualified jobs (when specified).
Corrected an issue in our READSPLF table function when a large spooled file was being read, with multiple buffers and the final row (last line of the SPOOLED file) would often be truncated. Also the SPLNBR parameter was not correctly processing the *LAST option when only one SPOOLED file existed for the SPOOLED file name. We have also added START_PAGE and START_LINE parameters to the READSPLF function. Users can you control where they want the output to begin (helpful for interactive or program driven environments).
Corrected the spelling of the AUTHORITY column entries on our OBJECT_AUT_LIST table function to match that of the GRTOBJAUT AUT parameter.
Corrected an issue with OUTQ_ENTRIES and SPOOLED_FILE_LIST start/end date/time input selection.
Redesigned/rewrote the OBJECT_LOCK_LIST table function. No more information is returned, it runs faster, and has a cleaner resultSet. Corrected a small issue in SPOOLED_FILES and SPOOLED_FILE_LIST UDTF when run multiple times in the same job.
Performance improvements to IFS_READ, IFS_LIST, and READSRC table functions.
New Class_List table function returns a list of *CLS object attributes.
Corrected a bug in XMLPARSE when used as a return value for a function. Re-write it entirely to work around this internal IBM i bug.
Improved the performance of IFS_READxxxx table functions by avoiding a redundant copy function that was previously overlooked.
Corrected an issue with the IFS_ACCESS an IFS_EXISTS UDFs which are used by some of our other functions to check if IFS files exist.
Corrected several issues in our IFS_LIST (IFS Directory) function when the object name pattern (OBJECT_FILTER) or recurse subdirectories (SUBTREE) parameters were used together.
Added our popular WRKOBJSQL (Work with SQL objects) CL command in this product.
Corrected an issue with the DLTOBJ function and procedure where their specific name could be overlaid during installation in some cases.
A cool new WTR_LIST table function is introduced. This function returns a list of Printer Writers along with important attributes of the writers and their output queue. If you need to see what printers are active, this table function is for you. In addition, the new WRITERS View in SQLTOOLS can be used to quickly query a list of all active writers. Just a simple:
select * from sqltools.Writers
The new LIBD_LIST (Library Description list) table function returns a list of library names, object counts and size of all objects and the library itself. This is an extension of our original RTVLIBD table function but allow users to request a list for generic library names and multiple libraries at one.
Bug fixes and several corrections to the installer for V7R2 shops. The installer now detects that you are running V7R2 and re-builds certain components that need to be rebuilt on V7R2 for compatibility purposes.
Also a new SAVF_LIST table function is introduces in this build. It lists the descriptions for save files specified don its input parameters. Our previous SAVF_OBJECTS table function now returns the entries. Whereas SAVF_LIST show the save file description, objects saved, size, saved date, and so forth.
Bug fixes and authority checking in some functions to prevent runtime messages.
New WSID_LIST and RTGE_LIST table functions return the list of Workstation Entries and Routing Entries for one or more subsystem descriptions.
New RCDLOCK_LIST table function to return record locks for one or more files.
The JOBD_LIST (list Job Description Descriptions ) now supports the new Time Zone property. Releases where the TIMEZONE is not applicable, the TIME_ZONE column is returned as NULL.
IFS_LIST now reports the target of *SYMLNK (Symbolic Link) files it detects in the IFS. In addition a new field CRTDATE has been added that contains only the date the file was created. The previous CREATE_DATE timestamp column continues to exist in the resultSet. The new column allows faster composition of WHERE clause when selection based on the create date and not the create time is important.

This is a minor refresh. We have added the Activation Group attribute for Trigger Programs to our TRIGGER_LIST table function.

Two new functions for J.D. Edwards users are being introduced with this refresh.

1. JDE2Date - Converts JD Edwards 6-digit julian-like values to ISO Date value.
2. date2JDE - Converts a true date value to JD Edwards 6-digit julian-like format.

Both functions are written in C using the CEEDATE and CEEDAYS APIs, are DETERMINISTIC and preform very fast.
READSRC had an issue with closing the file in certain situations.
The IFS_LIST function left the IFS directory handle open in some situations.
Several functions had their European vs USE decimal notation corrected.
Enhanced our QHST_LIST table function. now when you are on V7R4 or later, the name of the program that sent the message is returned. In addition, the message ID filter and message type filters now work as expected and ignore upper/lower case values.
The QHST View can be used to view the QHST entries from the last 24 hours.
The JOB_LIST Function has some bug fixes for returned CCSID values and submitted_by job information.
The OBJLOCK_LIST function now supports multiple library and object names in one instance.
The MBR_LIST function has some bug fixes and minor enhancements. The FILE_TYPE parameter now responds more naturally.
New READ_DTAARA table function that returns the contents of a specific data area.
New DTAARA_LIST table function that returns a list of data areas and their content.
The existing FD_LIST (File Description list) that returns a list of database file attributes now accepts multiple file names and multiple library names so you can get a consolidated list of attributes for the files you need, even for multiple libraries.
New PGM_LIST table function returns a list of programs and/or service programs along with most of their compiler attributes (including PUBAUT or "AUT" as it is known on IBM i). It also supports are new multi-object name design like FD_LIST and OBJECT_LIST do.
A new TRIGGER_LIST table function returns a more user-friendly list of database triggers for the given file/library.
Added a new Job Description function JOBD_LIST to list multiple job description information.
This function joins our already existing RTVJOBD table function to provide full *JOBD coverage.
Corrected an issue with SAVF_OBJECTS UDTF when the save file is empty it was causing a runtime issue.
Our OBJECT_LIST UDTF was completely re-written using C++ and our custom namespace. It now offers multiple object names, object types and even library name support. Handling all those lists internally.
If you're on V7R3 or later, then the IBM OBJECT_STATISTICS UDTF is likely good enough for your purposes, but this added capability gives our OBJECT_LIST the advantage. [12-OCT-2024] A bug in OBJECT_LIST would cause it to exit when object-type *LIB was specified. This has been corrected in this build.
Added the APAR attribute to OBJECT_LIST. This is primarily used for *CMD objects to store whether the CHGCMDDFT was used on the command.
Our existing MSGQ_LIST table function has been rewritten using our new model. It should provide similar results to the prior version but gives us more control of it moving forward.