| Update Log | Docs | IBM i V7R3/R4 Download | IBM i v7r2 Download |
| Build: Thu 02 Dec 2021 - 08:43 AM | Build: Thu 02 Dec 2021 - 01:55 PM | ||
Compatible with IBM i 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
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:
More Information: Customers who have questions, may reach out to Bob Cozzi at this link.
Extended Installation Instructions
Vendor Info: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 | |