Back to SQL iQuery

SQL Tools 2021 for IBM i

The New Standard in "Every Shop has It"

Compatible with Version 7 Rel 2, 3 and 4

You need to use IBM i interfaces such as APIs in RPG. SQL Tools allows you to call most APIs using SQL instead of legacy CALL/PARM and myriad parameters, prototypes and setup. Just SELECT INTO and go! Best of all, they work on IBM i V7R2, R3, R4 and the upcoming R5 without a problem.

SQL Tools is the perfect collection of powerful and easy-to-use SQL Functions for your #IBMi shop. Use them in your favorite SQL interface, such as ACS RUNSQL SCRIPTS, STRSQL, SQL iQuery, DBeaver, RPG IV, and CL via RUNSQLSTM

SQL Tools Documentation Index

Each SQL Tool includes a link to it's documentation. We use SQL Tools inside of SQL iQuery to produce these pages. Click or tap the SQL Tool name (below) to view it's documentation.

Function Description
#END List

#IBMi V7R2 Users take note: Installing #SQLTOOLS gives you the advantage of having many SQL Services on your V7R2 installation with capabilities formerly available only with IBM Services running on #IBMi V7R3 and later.

One call to an SQL Tools function can replace hundreds of error-prone lines of RPG and API calls. One example that we see all the time: Using the QUSROBJD API to retrieve an object's description requires upwards of 100+ lines of code. However our #SQLTOOLS RTVOBJD() UDTF only requires that the fields where the object description info is being stored are declared; No prototypes, no data structures no /copy statements. This example shows 100% of the code needed to retrieve an object description using #SQLTOOLS:

                dcl-s owner varchar(10);
                dcl-s lastUsed date;
                dcl-s neverUsed int(5);
                dcl-s daysSince int(10);
                dcl-s today date inz(*JOB);

                 *INLR = *ON;               
                exec sql select objowner, lastusedDate
                           INTO :Owner, :lastUsed:neverUsed
                      from table(SQLTOOLS.rtvobjd('MYDATA','OVERSTOCK','*FILE'));
                if (SQLState < '02000');  // Got it?
                   if (OWNER = 'QSECOFR' and neverUsed >- 0);
                     daysSince = %diff( lastUsed : today : *DAYS);
                     if (daysSince < 180);  // Hasn't been used in 6 months? Then ignore it
                        call sqlTools.joblog('Object OVERSTOCK is owned by QSECOFR');
                  exec sql call sqlTools.joblog('Object OVERSTOCK in QGPL not found.');   

As a convention, we've elected to use the LIBRARY, OBJECT, TYPE sequence order for input parameters. This means that when specifying an object and library as a parameter to any SQLTOOLS UDTF, the library name is normally the first parameter, followed by the object name. If an object type is necessary it follows the object name. This matches the SQL SCHEMA.TABLE sequence so your brain can be "Thinking in SQL" while using SQL Tools. For example:

select * from table( sqlTools.object_list('COZTOOLS','RTV*','*CMD')) objlist;
select srctype,text,chgdts,cur_records
from table( sqlTools.rtvmbrd('CODESRC','QRPGLESRC','WRKUDTF')) mbrd;

Note that in rare cases where the library name is often not traditionally specified, such as with *PDFMAP or *DEVD objects, the UDTFs for these objects (RTVPDFMAP, RTVDEVSTS either do not have a library parameter or the library parameter follows the object&apo;s name parameter.

One popular routine in #IBMi world is to process the names of members in source files. This can be use for scanning for specific things characteristics, recompile, change mangement, or even change something in the data itself. To do that over the years, people have used DSPFD OUTPUT(*OUTFILE) OPTION(*MBRLIST) and then read the data; in recent years the QUSLMBR API has ben utilized insead of the OUTFILE approach. But today with #SQLTOOLS MBR_LIST UDTF you can scan through a member list using SQL embedded in RPG.

In the example that follows, the program creates a member list using our MBR_LIST UTDF and then does something with that list. In the example we check if there are any source members that do NOT have an SEU TYPE ("source type") of RPGLE or SQLRPGLE. If there are, their info is written to the joblog. Note that in the code, I write the results to the joblog using the Qp0zLprintf API, although SQL Tools does include a Stored Procedure named JOBLOG that does something similar.

ctl-opt dftactgrp(*NO) ACTGRP(*NEW) ;                                                               
dcl-s srcFile varchar(10);                                                                          
dcl-s srcLib  varchar(10);                                                                          
dcl-s mbr     varchar(10);                                                                          
dcl-s seuType varchar(10);                                                                          
dcl-s lastChg Date;                                                                                 
dcl-s isNull  int(5);                                                                               
dcl-s text    varchar(50);                                                                          
// Prototype of system functiont that writes to the joblog                                          
dcl-PR joblog int(10) extproc('Qp0zLprintf');                                                       
pattern pointer  VALUE options(*STRING:*TRIM);                                                      
*N      pointer  VALUE options(*STRING:*TRIM:*NOPASS);                                              
*N      pointer  VALUE options(*STRING:*TRIM:*NOPASS);                                              
*N      pointer  VALUE options(*STRING:*TRIM:*NOPASS);                                              
*N      pointer  VALUE options(*STRING:*TRIM:*NOPASS);                                              
*N      pointer  VALUE options(*STRING:*TRIM:*NOPASS);                                              
*N      pointer  VALUE options(*STRING:*TRIM:*NOPASS);                                              
*N      pointer  VALUE options(*STRING:*TRIM:*NOPASS);                                              
*N      pointer  VALUE options(*STRING:*TRIM:*NOPASS);                                              
*N      pointer  VALUE options(*STRING:*TRIM:*NOPASS);                                              
dcl-pi  entryPlist  EXTPGM('FINDMBR');                                                              
sFile char(10) const;                                                                               
sLib  char(10) const;                                                                               
exec sql set option commit = *NONE, naming = *SYS;                                                  
*INLR = *ON;                                                                                        
if (%Parms() >= %ParmNum(sFile));                                                                   
srcfile = %trimR(sFile);                                                                            
srcfile = 'QRPGLESRC';                                                                              
if (%Parms() >= %ParmNum(sLib));                                                                    
srcLib = %trimR(sLib);                                                                              
srclib = '*LIBL';                                                                                   
// Search for source members without SEU Type of RPGLE or SQLRPGLE                                  
exec SQL DECLARE mbrList CURSOR FOR                                                                 
SELECT fileName, filelib,mbrname, srctype,lastsrcchgdate,mbrtext                                    
FROM TABLE( sqlTools.mbr_list(:srcLib, :srcFile)) ml;                                               
EXEC SQL OPEN MBRLIST;                                                                              
EXEC SQL FETCH MBRLIST                                                                              
INTO :srcfile, :srcLib, :mbr, :seuType, :lastChg:isNull,                                            
if (SQLState < '02000');                                                                            
joblog('Src File   Src Lib    Member     SEU Type   +                                               
Last Chg   Text'+X'25');                                                                            
DOW (SQLSTATE < '02000');                                                                           
if (SEUTYPE <> 'RPGLE' and SEUTYPE <> 'SQLRPGLE');                                                  
joblog('%-10s %-10s %-10s %-10s %-10s %-50s' + X'25' :                                              
srcfile : srcLib : mbr : seuType :                                                                  
%char(lastChg:*USA) : text);                                                                        
EXEC SQL FETCH MBRLIST                                                                              
INTO :srcfile, :srcLib, :mbr, :seuType, :lastChg:isNull,                                            
EXEC SQL CLOSE mbrList;                                                                             

Just call it from Command entry if you want to scan QRPGLESRC, otherwise pass in the source file name as the first parameter:

    >call coztest/scanmbr                                                 
     Src File   Src Lib    Member     SEU Type   Last Chg   Text          
     QRPGLESRC  COZTOOLS   AAAREADME  TXT        03/25/2013 Read me first 

Pricing and Availability

Available: June 2, 2021

Price: SQL Tools is offered to IBM i customers as a one-time fee. You license the current version ("SQL Tools 2021" as of this writting) and receive any updates/fixes to that version for as long as they are offerred. Customers who want to stay current by receiving annual updates, can opt for our annual software maintenance ("SWMA") currently $195/year U.S. When we ship "SQL Tools 2022" those users with a current SWMA contract will become eligible to upgrade to that release. Here's the break down:

  1. $695 (OTC) one-time charge, includes:
  2. Optional software maintenance (SWMA) is available at $395/annually per license. It includes:

More Information: Customers who have questions, may reach out to Bob Cozzi at this link.

Join our email list to get notifications for events and updates.

Quick Installation

  1. Download the .ZIP to your PC
  2. Unzip the SQLTOOLS.ZIP file to reveal the SQLTOOLS.SAVF file.
  3. FTP the SQLTOOLS.SAVF to your IBM i server, placing it into QGPL or similar.
  4. On the green screen, run the following installation command:
  5. Start enjoying SQL Tools!

Extended Installation Instructions

Vendor Info:
Cozzi Productions, Inc.
Lombard IL 60148