SQL Tools - High performance SQL functions for IBM i and RPG IV
Bob Cozzi IBM i SQL functions library, SQLTOOLS is now free to use by all IBM i customers. For updates visit this github page. For support, use the github forums here or go over to LinkedIn.com/in/BobCozzi and tag Bob Cozzi in your message.
Existing customers can update to V3.4 at no charge anytime. Just download it from the Release link on the right side of this page.
SQL Tools provides SQL Table Function (UDTF) wrappers for most IBM i APIs as well as providing additional capabilities, such as financial functions, CSV support and IFS read/write capabilities.
SQL Tools may be installed and run on IBM i Version 7.2 and later without changes. That is if you have V7R2 and V7R5 partitions, you can use the exact same SQL Tools functions on both without syntax issues or feature restrictions.
To download SQL Tools for IBM i, click on the Release link on the right-side of this page. Then select the appropriate save file. Most users on IBM i V7R3 and later should download and install the SQLTOOLS.SAVF, while those on V7R2 should use the SQLTOOLS72.SAVF for their system.
Once you have downloaded the save file to your PC, upload it to the IBM i server using binary mode FTP or similar. Normally you should create a save file object on the IBM i server in QGPL and upload the PC .SAVF to that host file. IBM i allows you to create a save file using the CRTSAVF CL command.
CRTSAVF QGPL/SQLTOOLSIf the save file already exists, you should clear it before performing the FTP upload.
CLRSAVF QGPL/SQLTOOLS
If the SAVF does not exist, FTP BINARY upload will automatically create it correctly if and only if the PC file name SQLTOOLS.SAVF or SQLTOOLS72.SAVF contains the .SAVF suffix.
To install SQL Tools, the RSTLICPGM (Restore Licensed Program) CL command is used. If a prior version of SQL Tools is already installed on your system, you need to remove it first before installing the latest release.
Determine which version is installed. Use the GO LICPGM CL command to list all installed licensed programs
GO LICPGM
Selection option 10=Display and scroll down until you see licensed program 2COZ-STn where n is 1, 2, or 3
If you locate a prior installation, remove it using the DLTLICPGM CL command as follows:
DLTLICPGM 2COZST3
Be sure to replace the trailing '3' with the version you currently have installed.
To install SQL Tools, use the RSTLICPGM CL command
RSTLICPGM 2COZST3 *SAVF SAVF(QGPL/SQLTOOLS)
If you are installing into an IBM i V7R2 partition, then use the save file named QGPL/SQLTOOLS72 on the RSTLICPGM command.
I also have a free CL command named WRKOBJSQL (Work with SQL Objects) that displays a list of the installed SQL objects in the specified library. NOTE: This command was previously named WRKFUNC and was included in the SQL iQuery package.
You can use:
WRKOBJSQL SQLTOOLS
to view the available functions, stored procedures and views that were installed by SQL Tools.