Blog

SQL Tools for IBM i

Compatible with IBM i Version 7 rel 2, 3 and 4

The New Standard in “Every IBM i Shop has It”

SQL Tools is a perfect collection of powerful and easy-to-use SQL Functions for #IBMi that can be used with any SQL interface. You can modernize those RPG IV applications so that you have more powerful access to things like APIs and utilities
or use them stand-alone in your favorite SQL processor.

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.

SQL Embedded in RPG IV users can simply insert these functions into new or existing EXEC SQL statements.
SQL iQuery users can use them in SQL statements run via RUNiQRY or embedded in SQL iQuery Scripts.
RUNSQL and RUNSQLSTM users can use them just like any other SQL function.
IBM ACS RUNSQL SCRIPTS users can use SQL Tools to extend and enhance access to their IBM i server’s data.
HelpSystem SEQUEL SQL Data Access

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;

Pricing and Availability

Available: June 2, 2021

Price: SQL Tools is
offered to IBM i customers as an initial one-time fee with an
annual software maintenance and upgrades option. You buy a
license to the current version at the time of licensing and
keep it forever. If you want to stay current and receive annual updates and
fixes, we offer annual software
maintenance. And as always, no per-partition charge. Here’s the break down:

$695 (OTC) one-time charge.
Includes all updates for “SQL Tools 2021” for one year.
After first year, annual maintenance is available for just $395/year.
Includes updates to latest release and fixes.

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

Customers who want to prepare to order, can submit an approval for “SQL Tools 2021” to whomever they need to, for a one-time charge of $695 (USD) to be delivered on or before June 10, 2021. Those orders may be submitted to us beginning June 2,
2021.

Vendor Info:
Cozzi Productions, Inc.
www.SQLiQuery.com
Lombard IL 60148
USA

SQL Tools Function List

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 this “reactive” web page documentation linked below. It currently supports mobile and desktop experiences.
Click or tap the SQL Tool name (below) and it’s current parameters and docs should appear.

Function
Description
ACTGRP (Func)
Returns a list of Activation Groups and their properties as resultSet rows.
Each activation group names activated in the job running this SQL Tool are returned, along with the name of the initiating program, the domain and storage model. Note that on V7R2, users may receive a domain violation message in the joblog. This message is trapped and ignored. It does not appear on V7R3 and later.
BASE64_DECODE (Func)
UDF to decode a base64-encoded string to character.
This UDF differs from others in that it is written in C using the base64 algorithm and tends to perform much faster.
The result value is returned as a CLOB(1.2M) value which can be cast to the form needed in your application.
BASE64_DECODE (Func)
UDF to decode a base64-encoded string to character.
This UDF differs from others in that it is written in C using the base64 algorithm and tends to perform much faster.
In addition, The resulting character string is returned as ASCII text which is then converted to your job’s CCSID. BASE64 is an ASCII-based routine.
BOMDATE (Func)
Retrieve 1st of Month date for the input date.
BOMEOM (Func)
Retrieve 1st and Last Dates of the Month for the given date.
CHGAUT (Proc)
Procedure that allows you to change the authority of an IFS object.
CHGAUT (Func)
Function to change the authority of an IFS object.
CHGCURLIB (Func)
UDF to change the job’s current library to what is passed to it as the one and only parmaeter.
CHGCURLIB (Proc)
Stored Procedure to change the job’s current library to what is passed to it.
CPUCOUNT (Func)
UDF to return the number of CPU “cores” active for the entire system (all partitions). The returned value is an integer.
CPYFRMPDF (Func)
Copy a PDF from the IFS to an OUTQ so it can be printed.
This interface sends a binary copy of the PDF to the OUTQ so it can be printed as it appears in a PDF reader, on the printer associated with the specified OUTQ.
CPYFRMPDF (Proc)
Copy a PDF from the IFS to an OUTQ so it can be printed.
This interface sends a binary copy of the PDF to the OUTQ so it can be printed as it appears in a PDF reader, on the printer associated with the specified OUTQ.
CPYOUTQ (Proc)
Procedure to copy all SPOOLED files in an OUTQ to the IFS as TEXT or PDF images. (Based on the OUTPUT parameter value.) Note: Any parameter with a DEFAULT of NULL is not used to filter the SPOOLED file names, therefore NULL or unspecified means the same as passing ‘*ALL’ for that parameter.
CPYTOPDF (Proc)
Copy a SPOOLED file to the IFS as a PDF or TEXT file.
CPYTOPDF (Func)
Copy a SPOOLED file to the IFS as a PDF or TEXT file.
CRTSTMF (Proc)
Create a file on the IFS with the specified CCSID.
CRTUSRSPC (Proc)
Procedure to Create a User Space.
CURLIB (Func)
UDF to return the current library (if any) from the library list.
CVTDATE (Func)
Convert date content from a 6,7, or 8-position textual value to a true date value.
CVTDATE (Func)
Convert date content from a 6,7, or 8-digits value to a true DATE value.
CVTOBJDTS (Func)
Convert 13-digit “IBM i System Object Date/Timestamp to TimeStamp
CVTTODTS (Func)
Convert 13-digit “IBM i System Object Date/Timestamp to TimeStamp
DATE_TO_CHAR (Func)
Convert the input date to character in the Date Format specified. This is similar to using the VARCHAR(date_value,XXX) function however DATE_TO_CHAR supports more formats.
DATE_TO_DEC (Func)
Convert the input date to Decimal notation in the format specified.
DATEDUR (Func)
Calculate the years,months,days, hours,mins,secs between 2 timestamps.
DATETOCHAR (Func)
Convert the input date to character in the Date Format specified. This is similar to using the VARCHAR(date_value,XXX) function however DATETOCHAR supports more formats.
DATETODEC (Func)
Convert the input date to Decimal notation in the format specified.
DLTSPLF (Proc)
Delete SPOOLED Files using SPOOL Attributes. Only SPOOLED files that match all the input options specified are deleted. The ACTION or PREVIEW parameter may be used to return the list of candidate SPOOLED files. This is called PREVIEW mode using ACTION=>’*PREVIEW’ which is the default. Use ACTION=>’*DELETE’ to run the DLTSPLF command for each SPOOLED file selected by the other parameters.
DTAARA (Func)
UDF to retrieve the contents of an existing data area.
DTAARA (Func)
UDF to retrieve the contents of an existing data area.
EDITDEC (Func)
UDF to convert numeric data into an edited numeric character string.
ENCODE_HTML (Func)
URL encodes a text string. The encoding format is specified on the ENCODE parameter. That %xy encoded values are rendered in the CCSID specified on the ENCODE parameter.
ENCODE_XML (Func)
Escapes text that could be used within XML tags by encoding the 5 XML reserved characters.
EOMDATE (Func)
Retrieve End of Month date for the input date.
FLDLIST (Func)
List the Fields definitions (RPG-compatible format) for the File
FROMHEX (Func)
UDF to convert 2-bytes into 1-character, “Convert from hex to character”
FTPDIR (Proc)
SQL Stored Procedure to FTP and entire IFS folder and optionally its subfolders.
GETCPUCOUNT (Func)
UDF to return the number of CPU “cores” active for the entire system (all partitions). The returned value is an integer.
GETCURLIB (Func)
UDF to return the current library (if any) from the library list.
GETENV (Func)
UDF to return the value of an Environment variable. The value is returned as a VARCHAR(32739) that should be CAST to the length and type desired.
GETSRLNBR (Func)
UDF to return the System Serial Number. This is simlar to using RTVSYSVAL SYSVAL(QSRLNBR). Note the returned value is VARCHAR(10) and not 8 as would be the case in some other interfaces.
GETSYSNAME (Func)
UDF to return the partition’s system name. This is similar to using RTVNETA SYSNAME(&SYSNAME). Note the returned value is VARCHAR(10) and not 8 as would be the case in some other interfaces.
HASH (Func)
Returns the MD5 or SHA-x hash for the input data.
IFSACCESS (Func)
UDF to check if the IFS file exists for the specified mode (read/write/execute).
IFSACCESS (Func)
UDF to check if the IFS file exists for the specified mode (read/write/execute).
IFSEXISTS (Func)
UDF to check if the IFS file exists. If the file specified on the PATH_NAME parameter exists, the return value is 1, otherwise it is 0.
IFSSTAT (Func)
UDTF to return the attributes of the specified IFS file. The data returned is similar to the IFS stat64() API.
JOB (Func)
Retrieve part of the job identity or the fully Qualified job name.
JOB_ATTR (Func)
Retrieve the job attributes by calling SQL Tools RTVJOBA function
JOB_DATE (Func)
Retrieve the job date use the QUSRJOBI format JOBI0300.
JOBDATE (Func)
Retrieve the job date use the QUSRJOBI format JOBI0300.
JOBLOG (Proc)
Write text as a message to the joblog using CPF9897.
KEYLIST (Func)
List the Key Fields for the given File/Table
MBRLIST (Func)
Retrieve a list of Member names and their attributes
MCHINFO (Func)
Retrieve the PowerX hardward info as it relates to IBM i. Machine type, modle, processor group, serial number feature code, etc.
OBJECT_EXISTS (Func)
Returns 1 if the object exists on the system, otherwise returns 0.
OBJECT_LIST (Func)
Returns a list of Objects and their attributes. The amount of information returned depends on the DETAIL_INFO parameter setting. By default all information is returned. The OBJECT_LIST with DETAIL_INFO 100 or 200 return fast. Larger DETAIL_INFO values can take a little longer to return.
OBJEXISTS (Func)
Returns 1 if the object exists on the system, otherwise returns 0.
OBJTYPES (Func)
Creates an SQL ALIAS in QTEMP that contains the IBM i Object Types. Use this Function to build the Object Types dataset for your own user-created tools that may need a list of object types. This SQL Tools requires the IBM-supplied QSYSINC library’s MIOBJTYP source member in the MIH source file. Otherwise it will fail.
This SQL Tool has no parameters.
OSVER (Func)
UDF to return the current IBM i version/release level as a dec(7,2) value.
OSVRM (Func)
UDF to return the current IBM i version/release level in VxRyMz format.
PARSE_CALL (Func)
Parses an IBM i CL-style CALL statement and returns the called program and (if specified) the qualified library name.
Pass in ‘CALL QSYS/QCMDEXC(…)’ and it will return one row with QCMDEXC and QSYS. One great use is to retieve the called program name for the Job Scheduler. Something like: select p.objname as “Called Program”, p.objlib as Library, JS.* from qsys2.scheduled_job_info JS, lateral (select * from table(sqltools.PARSE_CALL(js.COMMAND_STRING)) pc) P
PARSE_SQLCALL (Func)
Parses an IBM Db2 for i SQL-style stored procedure CALL statement. The Stored Procedure name and Schema (if specified) are returned.
PATHTOQSYS (Func)
UDTF to parse an IFS Path for a QSYS.LIB object name and return a table that contains the object, library, object type, and if specified, the member.
PDFMAP (Func)
UDTF to retrieve PDF Map info (AlIAS for RTVPDFMAP)
PGMREF (Func)
Retrieve Program References (PGMREFS) UDTF
QCMDLOG (Proc)
Writes the input command string as a *RQS message to the joblog. Note: The CL command is not run.
QCMDRUN (Proc)
Run a CL command
RTVCMDD (Func)
Retrieve Command Creation Definition (RTVCMDD) UDTF
RTVDBR (Func)
UDTF to retrieve file dependencies.
RTVDEVSTS (Func)
Retrieve Configuration Device Status (RTVDEVSTS) UDTF
RTVDTAARA (Func)
UDTF to retrieve the contents of an existing data area.
RTVGRPPRF (Func)
UDTF that Retrieves the Group Profile (including supplemental Groups for the User Profile.
RTVJOBA (Func)
Retrieve the job attributes by calling QUSRJOBI API
RTVJOBD (Func)
Retrieve Job Description (RTVJOBD) UDTF
RTVLASTSPLF (Func)
Retrieve Last SPOOLED File Info (RTVLASTSPLF) UDTF. This UDTF returns the name, number and other properties of the most recently created SPOOLED file for the job running the UDTF.
RTVLIBD (Func)
Retrieve Library Description (RTVLIBD) UDTF
RTVMBRD (Func)
Retrieve Member Description (RTVMBRD) UDTF
RTVMSGD (Func)
Retrieve Message ID Description (RTVMSGD) UDTF
RTVMSGID (Func)
Retrieve Message ID Descr (RTVMSGID) UDTF
RTVNETA (Func)
Retrieve Network Attributes of the system
RTVOBJD (Func)
Retrieve Object Description (RTVOBJD) UDTF
RTVOBJLCK (Func)
UDTF to return the locks currently held on an object.
RTVPDFMAP (Func)
UDTF to Retrieve PDF Map Entry information.
RTVRCDFMT (Func)
UDTF to return the record format name and description for the specified file.
RTVSPLFA (Func)
Retrieve SPOOLED File Attributes (RTVSPLFA) UDTF
RTVUSRPRF (Func)
Retrieve User Profile (RTVUSRPRF) UDTF
RTVUSRSPC (Func)
UDTF to read the contents of a User Space. The data is returned as a CLOB(16M) value that should be cast to the format desired.
SPLF_DATA (Func)
Read SPOOL File Data as text. (V7R2M0 and later)
SPLF_DATA (Func)
Read SPOOL File Data as text. (V7R2M0 and later)
SRLNBR (Func)
UDF to return the System Serial Number. This is simlar to using RTVSYSVAL SYSVAL(QSRLNBR). Note the returned value is VARCHAR(10) and not 8 as would be the case in some other interfaces.
SYSNAME (Func)
UDF to return the partition’s system name. This is similar to using RTVNETA SYSNAME(&SYSNAME). Note the returned value is VARCHAR(10) and not 8 as would be the case in some other interfaces.
TODATE (Func)
Convert textual data to a true DATE data-type value.
TODATE (Func)
Convert textual data to a true DATE data-type value.
TODTS (Func)
Convert text input into TimeStamp value.
TOHEX (Func)
UDF to convert 1-character to 2-hex character pair.
TS_FMT (Func)
Returns the input timestamp value in RFC 3339 standard format.
UNEDIT (Func)
UDF to convert textual numeric values with edit symbols to decimal. For example, a text value of ‘$12,465.72CR’ cannot be converted with standard SQL interfaces. the UDF will convert this value to -12465.72 correctly. Syntax: UNEDIT(‘numeric string’ [ , ‘optional 3-char edits’]) The 2nd parameter is optional and may conain the Currency Symbol, Thousands Separator, and Decimal notation symbol (in that order).
USRSPC (Func)
UDTF to read the contents of a User Space. The data is returned a maximum of VARCHAR(32702). When more is needed, use multiple USRSPC() functions or use the RTVUSRSPC() UDTF.
USRSPC (Func)
UDTF to read the contents of a User Space. The data is returned a maximum of VARCHAR(32702). When more is needed, use multiple USRSPC() functions or use the RTVUSRSPC() UDTF.
WATCHLIST (Func)
Retrieve a list of WATCHES along with their job info and start time.
#END List