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

#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:

        *FREE    
                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');
                     endif;
                    endif;
                else;
                  exec sql call sqlTools.joblog('Object OVERSTOCK in QGPL not found.');   
                endif;
            

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.

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);                                
            end-PR;                                                                                 
                                                                                                    
           dcl-pi  entryPlist  EXTPGM('FINDMBR');                                                   
              sFile char(10) const;                                                                 
              sLib  char(10) const;                                                                 
           end-pi;                                                                                  
                                                                                                    
           exec sql set option commit = *NONE, naming = *SYS;                                       
          *INLR = *ON;                                                                              
                                                                                                    
          if (%Parms() >= %ParmNum(sFile));                                                         
             srcfile = %trimR(sFile);                                                               
          else;                                                                                     
             srcfile = 'QRPGLESRC';                                                                 
          endif;                                                                                    
                                                                                                    
          if (%Parms() >= %ParmNum(sLib));                                                          
             srcLib = %trimR(sLib);                                                                 
          else;                                                                                     
             srclib = '*LIBL';                                                                      
          endif;                                                                                    
                                                                                                    
              // 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,                       
                          :text;                                                                    
            if (SQLState < '02000');                                                                
            joblog('Src File   Src Lib    Member     SEU Type   +                                   
                      Last Chg   Text'+X'25');                                                      
            endif;                                                                                  
            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);                                                
              endif;                                                                                
              EXEC SQL FETCH MBRLIST                                                                
                     INTO :srcfile, :srcLib, :mbr, :seuType, :lastChg:isNull,                       
                          :text;                                                                    
           enddo;                                                                                   
           EXEC SQL CLOSE mbrList;                                                                  
           return;                                                                                  

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.
www.SQLiQuery.com
Lombard IL 60148
USA

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 the 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 should appear.

Function Description
{{FUNCTION}} ({{FUNCTYPE}}) {{FUNCDESC}}
#END List