Back to SQL iQuery

SQL Tools for IBM i

The New Standard in "Every Shop has It"

SQL Tools is the perfect collection of powerful and easy-to-use SQL Functions for your #IBMi shop. They can be used with any SQL interface. Use them in your RPG IV applications so you have access to things previously only available through APIs and CL commands. Also use them stand-alone in your favorite SQL processor.

Compatible with Version 7 Rel 2, 3 and 4

SQL Tools replaces thousands of lines of error-prone RPG coding of API calls, providing direct SQL access to many APIs as well as a ton of new functionality, such as date conversions, base64 decoding and more.

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

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

In rare/specialty cases where the library name is often not used, such as with *PDFMAP or *DEVD objects, the library name parameter follows the object name parameter, or there may be no library name parameter. For example:

select * from table(sqlTools.rtvpdfmap('QPRINTMAP')) pdfmap;
select * from table(sqlTools.rtvdevsts('TAP01')) sts;

Another example is using the SQL Tools MBR_LIST UDTF to scan through the descriptions of the members in a source file. In this example, I scan through the source file name I pass to the program looking for Source Types other than RPGLE and SQLRPGLE. Note that in the code, i write the results to the joblog using Qp0zLprintf because it is my preferred way to do that vs the less capable DSPLY opcode.

           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 at an initial one-time fee. If you want to stay current and receive annual updates and fixes, we offer annual software maintenance. And as always: no per-partition charge, 2-serial number license. Here's the break down:

  1. $695 (OTC) one-time charge, includes:
  2. After first year, optional annual maintenance is available (SWMA) for just $195/year. 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

SQL Tools Documentation Index

Each SQL Tool includes a link to it's documentation. This is an evoling document and is updated frequently. We are using SQL Tools to help produce these "reactive " web pages linked below. It currently supports mobile and desktop experiences. Click or tap the SQL Tool name (below) and it's current parameters and documentation will appear.

Function Description
#END List