Product Enhancement and Maintenance Log

- Cozzi's SQL iQuery v7.6 for IBM i V7R2 and later - Licensed Program (2COZ-IQ7)
- "SQL iQuery" is a Web, CL and Interactive SQL query tool for IBM i.
- iQuery allows users to run SQL statements (including the SELECT statement) from Command Entry, the Web, CL programs, menus, job scheduler, batch jobs, etc. basically anywhere a CL command can be run and from your HTML web pages.
Pinned:- IBM Issued the following PTFs to correct a major issue on IBM i V7R4. The following PTFs should be installed (PTF release date: 27 JULY 2021)
  1. V7R3 PTF - SI76400 (APRIL 2022)
  2. V7R4 PTF - SI76775 (JULY 2021)
  3. V7R4 PTF - SI76779 (JULY 2021)
- The Nightly Build and Fix Log is available separately on this linked page.
- NOTE: As of June 2022 the SQL udtf/udf/proc library shipped with SQL iQuery are at "code freeze". The SQL udtf/udf/proc library has been migrated to our strategic product named SQL Tools (2COZ-STn) which gives us the ability to improve and enhance the UDTF/UDF/PROC library external to SQL iQuery updates. All SQL iQuery licensed holders who acquired a license on or before June 1, 2022 may request a no-charge SQL Tools license.
14-NOV-2023- Corrected an issue with EMAIL options where the new FROM ADDRESS would not properly replace the configuration record &FA code when no address is specified.
02-NOV-2023- The ability to disable certain SQL statements, such as INSERT, UPDATE, DELETE, MERGE and DROP has been released. Now to disable these SQL statements when run within iQuery, create a data area named IQ_INSERT, IQ_UPDATE, IQ_DELETE, IQ_MERGE, and/or IQ_DROP and those statement are now blocked from being exectured. A message is written to the joblog. Note this effects all users but can be controlled by creating these *DTAARA objects in a library that is added to or removed from the Job's library list.
- Two new EMAIL parameters are being introduced to help override the "sent from" user/email address. Now the COZZI SENDMAIL command recognize the EMAIL FROMADDR( statement and the SNDDST command recognizes the EMAIL FROMUSER() and FROMADDR( ) statements. Use these to change the FROM parameter of SENDMAIL and the USRID( ) parameter of SNDDST. The SNDSTMPEMM command has no such capability.
29-SEPT-202- A new *LIBL figurative constant returns the current library.
- A new *CURDIR figurative constant returns the current working director for the job.
- Now figurative constants such as *LIBL, *CURLIB, *USER, etc. may be assigned to Session Variables as their representative value, or as a literal string. That is eval &V1 = '*LIBL'; will copy the literal text '*LIBL' into &V1, while: eval &V1 = *LIBL; will copy the library list itself, into &V1.
21-SEP-2023- A new iQuery Script EMAIL option "EMAIL SEND;" causes the script to immediately process and send the email that was setup by the script. It does this without the need for a resultSet (i.e., no iQuery-generated attachment). This allows users to create scripts that generate a list of email addresses and then send out a boiler-plate or "canned" email to each person on that list... using something like an iQuery Script FOREACH SELECT INTO statement.
07-SEP-2023- Implemented the MBROPT(*REPLACE) feature on the RUNiQRY command when OUTPUT(*OUTFILE) or OUTPUT(*FILE) is specified. Now the member may becleared of data before any new data is added to the file when MBROPT(*REPLACE) is selected. In addition the OUTMBR parameter is now correctly shown only when an source output option is selected--it is no longer shown when either of these OUTPUT parmaeter options are selected: OUTPUT(*OUTFILE | *FILE).
10-JUL-2023- The CSVOPT parameter settings may be be specified using the PUTENV command in SQL iQuery Script.
21-MAY-2023- Formal release and update to SQL iQuery. This release includes everything from the 09 May 2023 build as well as updates to the OUTPUT options and specifically to the integrated IBM ACS Output support. One small improvement, the #DFTOUTPUT and #OUTPUT SQL iQuery Script Directives now support output options with or without the leading * (asterisk). That is *PDF and PDF, *CSV, CSV, *EXCEL and EXCEL are now supported. Previously the leading asterisk was required. In IBM ACS output, the SQL statement may now contain embedded appostrophies as well as embedded double quotes. Those symbols are now escaped as required by the Java ACS and QShell interface.
- The DLYJOB (delay job) is now a regular built-in command, so no more hashtag is required. That is: dlyjob 5; -- Delays your job for 5 seconds.
- The way in-line CL commands are processed has changed. We now use the QCAPCMD SQL Stored Procedure. This allows inline CL commands to run remotely when you have connected to a remote system. To force the command to run locally, use the Local version of the CL: directive, LCL: (local CL) which forces the script processor to use the C runtime system function, causing the CL command to always run on the local system. Note that LCL: may be used anytime regardless of connection status.
09-MAY-2023- Enhancments to CSV output. Now you can specify how to "escape" certain characters in the CSV data. You can also specify the escape character itself.
- Enhanced the *EXCEL (native iQuery XLS output) so that you can now specify *NONE for the Column Headning Line Break Character. Previous each column heading is seperated by a linefeed or user-specified character. Now all column headings may be on the same line (i.e., no breaks) by specifying this new *NONE option.
- The Number of Records in a result set are now more accurately retrieved. In addition the new iq_ROWCOUNT environment variable contains the last successulf iQuery resultSet rowsize count.
- When using the email option, the EMAIL, CC, and BCC email addresses are now saved to the Environment under the iq_EMAIL, iq_EMAILCC and iq_EMAILBCC environment variables.
- The Google Charts output option has been enhanced to better support date values.
- SQL iQuery Script Prompter support has a new field property. The new REQD (required entry) attribute causes the field to be manditory entry but not blank. A normal "ME" or Manditory Entry field can contain a blank entry. In some cases you want to require the user to enter a non-blank value into a field, any value, so a list of SPCVAL (special values) may be specified. However any other value so long as it isn't blank may be the rule for this field. Case in point, an email input field that has a list of valid email addresses, but also allows the user to enter any email address. In this case the REQD(*YES) keyword can be used to require a non-blank entry while not restricting it to a set of VALUES. The keyword name is REQ or REQD or RQD (all are synonyms). The valid options are *YES or *NO (also *TRUE *FALSE, or 1 and 0)
March 2023- Various fixes and enhancements. See the fix log (link above) for details.
02-JAN-2023- The WRKUSR command is now open source and available on It has also been updated in iQuery to reflect this change, including the ability to Copy an existing User Profile.
06-JAN-2023- The DO and FOR opcodes have been enhanced. Now, "DO FOR", "DOFOR", "DO" or "FOR" may be specified as synonums. The FOR opcode is recommended however.
- Rebuilt iQuery to leaverage the latest round of IBM PTF Groups and fixes.
28-NOV-2022- Internal optimization and streamlining of code. This is an optional build for existing customers.
07-NOV-2022- New HDROPTION(*NOHDR *NOCOLHDR *NOROWMSG) options are introduced. They restrict the output of the standard report headings, column headings, and resultSet size row count message, respectively. In addition HDROPTION(*NONE) causes a 3 of these new options to be applied.
- The STMF and STMFNAME parameters now appear when OUTPUT(*TEXT) is specified when prompting the RUNiQRY CL command.
26-OCT-2022- Fixes and usability enhancements.
12-SEP-2022- Enhanced the OUTPUT(*XLSX) option to respect the STMFDIROPT(*CREATE) parameter. Now, when IBM i ACS is used to output native XLSX content, iQuery will attempt to create the output folder used as the target of the command when the STMFDIROPT(*CREATE) is used (this is and was the default). Previously iQuery did allowed ACS to handle the output, but it does not create the target folder. So now, iQuery creates it if needed and then routines the statement to ACS.
15-AUG-2022- Corrected an issue with iQuery Script with nested conditional statements that use the &SQLSTATE session variable. It was being updated upon each condition which was confusing some users. Now conditional statements no longer set the &SQLSTATE session variable.
26-MAY-2022- Refresh using latest PTF/TR updates on our own system.
- IBM issued a set of PTFs for a bug we detected when restoring SQL functions to another system or partition. We had updated our installer to rebuild all shipped iQuery SQL UDF/UDTFs. However we now check at install time to see if the required PTFs have been installed, and if so, avoid the rebuild process. This can lead of much faster installation time. checks for specific PTFs using the IBM i PTF API instead of the much slower IBM SQL Service.
- The SQL iQuery Find Member macro (FINDMBR) has been enhanced to support from/to last source changed dates.
05-MAY-2022- Support for IBM V7R5 build. Compatible with new SQL Data Types and CLI changes.
04-MAR-2022- All SQL Functions are now rebuilt on the local system during install to work around an IBM i SQL RSTOBJ bug that looses information about SQL functions.
01-MAR-2022- Formal rebuild and release of SQL iQuery V7R6.
25-FEB-2022- The Installer now includes a helptext panel. Press F1 from the Install/Configuration sceen to see more information abou the install/configuration options. Press enter to return from the Help pane.
12-FEB-2022- OUTPUT(*XLS) now respects the #COLHDG SQL iQuery Script directive. Previously this applied to Display and Print output only. When #COLHDG FIELDS (or FLDNAMES) is specified, the field/column names are used instead of the column headings. Use #COLHDG COLUMNS or COLHDG or even COLHDR to use the column headings; Column Headings are still used as the default. The XLSOPT parameter of the RUNiQRY command now includes an element for Column Heading designation. The default is still *COLHDG but *FIELDS or *FLDNAME may be specified as well.
- OUTPUT(*EXCEL) is now configurable during installation. You can set it so that OUTPUT(*EXCEL) is routed to the ACS Bundle launcher for native XLSX output. You will need the correct PTF for your IBM i version, listed below, to enable this capability:
  • V7R2M0 - SI73105 5770SS1
  • V7R3M0 - SI73104 5770SS1
  • V7R4M0 - SI73103 5770SS1
When OUTPUT(*EXCEL) is specified, by default, our own in-house (native IBM i code) is used. It generates SpreadSheetML format output with the .XLS suffix. This causes Excel to issue a warning when the file is open. This warning was introduced by Microsoft circa Office 2016. Prior to that, no such message was issued. Today, our native .XLS output is still the best choice for IBM i to Excel content when it comes to formatting. However, a native/binary .XLSX file is sometimes desired for simple, raw output. We now support that output format. The new *ACSxxxx output options (including *ACSXLSX) will produce a native Excel binary worksheet, while *ACSXLS produces a binary .XLS file. The format of the output for these options does not respect the cool EXCEL formatting features in SQL iQuery Script. So the results are rather plain; similar to CSV. With this change, the new OUTPUT(*EXCEL) option can be configured at installation time on the final SQL iQuery installation Prompt window. It asks you if you want to map the OUTPUT(*EXCEL) option to ACS XLSX or using the native SQL iQuery driver. You can continue to use the native iQuery driver by simply specifying OUTPUT(*XLS) instead of OUTPUT(*EXCEL). Note that we've supported OUTPUT(*ACSXLSX) in iQuery for years, but some users perfer using OUTPUT(*EXCEL) as the target when end-users are involved. This change also removes the ACS Daemon running in QINTER or more accurately the subsystem in which SQL iQuery is run with OUTPUT(*EXCEL). Previously the java runtime stayed active which caused a number of support calls. This is a normal side-effect of using IBM ACS Java to produce content. However, we've now included the KillDaemon option to remove it once iQuery has finished generating the results.
04-FEB-2022- During install, the "post install objects" routine is no longer necessary and has been removed from the install prompter.
- The JSON output now defaults to the COLUMN (field) NAME instead of Column Headings (COLHDG). This was the original default but a large customer asked us to modify the default. This was causing issues for other customers. So we've changed it back to FIELDS. Users may override this option using SQL iQuery Script directives.
This scauses the naming option to be set to Field Names, Column Headings, or Alias names, respectively. In addition, a new #JSON_TOUPPER and #JSON_TOLOWER directives were added to force the resulting JSON token name to all upper or all lower case respetively. Pass ON or TRUE to enable either of these case conversion options.
07-JAN-2022- Enhanced how integration with EMAIL clients generates the email recipient list.
- Launched and updated SENDMAIL application that works on V7R2 or later, better.
04-DEC-2021- When using the SQL iQuery Script SAVEFILE command, a replace parameter may be specified for the 3rd or 4th parameter. The value is replace or yes upper/lower case is ignored. It can appear after the data variable or after the CCSID to be assigned to the file.
21-NOV-2021- Refreshed our V7R2 version with the latest updates. Also rebuilt the V7R3/V7R4 version for consistency in release dates.
10-NOV-2021- Introduced "RPG-style" concat to the EVAL opcode in iQuery Script. Users may now do assignments such as
eval &part = 'FIL' + '12345' 
05-NOV-2021- Improved output performance for certain types of queries for non-interactive output. That is everything except OUTPUT(*) should perform substancially faster by default.
- Introduced a new CURSOR parameter on RUNiQRY CL command. This allows you to control the SQL CLI cursor type. The default setting should be good enough. Users may see faster results with batch and non-Displayed output. If a result or table function seems to be taking "forever" users can use the CURSOR(*FAST) parameter setting to automatically set the new CURSOR attributes to *FORWARD only so that the optimizer returns that first set of rows nearly immediately. The down side is that you can only page forward through the results interactively, and the resultSet row count is estimated and usually incorrect.
21-OCT-2021- Bob Cozzi announced today that 2COZ-IQ7 (SQL iQuery Version 7) is being released today with relaxed licensing. Now when the license expires, it will continue to operate normally. However the License Expired message(s) shall continue to be written to the joblog. Paid Licensed Users will receive a valid License Key to install which will supress this message.
23-JUN-2021- SQL iQuery now checks if IBM i 5770-SS1 option 39 - International Components for Unicode is installed. If it is not, a *DIAG message is issued and some features that require SQL Regular Expression functions (REGEXP_xxxx) are disabled. Install option 39 to resolve this issue. Installing Option 39 does not require an IPL, and is a no-charge feature.
12-JUN-2021- SQL iQuery now DROPs the SQL statement between calls. Previously it would CLOSE the statement and reallocate that same statement. However IBM's 3-level names can cause issues when running a 3-level name statement and then a non-3-level name statement on the same statement handle. Dropping the statement does cause the same issue but does use slightmore more resources.
28-JUN-2021- SQL iQuery can now display the output from resultSets returned, conditionally from Stored Procedures.
- The RTVLASTSPLF UDTF now supports more options. A new SCOPE parameter has been added that allows users to request the last SPOOLED File identity for the Job, the Current User, or the System.
- Corrected an issue with SQL iQuery Scripts LEAVE and ITER stmts when embedded in a nested WHILE/IF/WHILE, or similar nested code block.
07-JUN-2021- The SQL iQuery Installer now prompts the install user to create the "IQUERY" user profile. If this option is selected (it is the default) then user profile IQUERY with USRCLS(*PGMR) SPCAUT(*USRCLS) INLMNU(*SIGNOFF) is created and the owner of all objects in the IQUERY library is changed to NEWOWN(IQUERY). Customers can use this profile as a GRPPRF or SUPGRPPRF to control specific User Access to the iQuery product. Note that in shops with QSECURITY=20/30 this modification has little impact at all. Existing iQuery installations with QSECURITY 40 or 50 could be impacted. So review things once this update is installed. If there could be an issue, set this option to N to keep the Object Owner set to the QPGMR User Profile.
01-MAY-2021- New JOB Schedule Program name view created that allows users to see the name of the program called on a Job Scheduler Entry. If a CL Command is used for the Job Scheduler Entry that entry is not included. The View can retrieve both a straight CALL or a CALL embedded in another command such as the SBMJOB CMD parameter.
10-MAR-2021- In response to a customer, we now support the PRINT BEFORE/AFTER commands in SQL iQuery Script for OUTPUT(*CSV) content. In addition we've added a new parameter to the RUNiQRY command that provide users with a way to included the extra before/after content using the RUNiQRY command itself. When using the new EXTRA parameter, be sure to include the Start Column for the content. Many customers omit that numeric value and receive undesireable results. The syntax for extra content is:
- in iQuery Script:
 PRINT BEFORE(1, *DATE, Special Data);
- or:
- The first value is the starting column number and each comma detected shifts the data following it into the next cell (in excel) or next column when printing and now when writing to CSV files.
- New IQ_OBJTYPES table is shipped in the iQuery product library. It contains a list of all IBM i object types, such as *FILE, *PGM, *CMD, *OUTQ, etc. In addition there is an implicitly hidden column, named MITYPE that contains the 2-character internal MI object type.
- The *FINDOBJ macro now has the list of object types available via the F4=Prompt key in the prompter window. It also now uses *ALLSIMPLE parameter of OBJECT_STATISTIC whenever possible.
08-MAR-2021- OUTPUT(*EXCEL) now supports custom COLHDG (Column Heading) attributes. Within an iQuery script, specify EXCEL COLHDG(3,color,blue); to set the text color for column 3 ('C' in Excel) to Blue. Most other attributes are supported.
- The CONVERT_DATE UDF is now an inline C language function and is DETERMINISTIC. Our original CVTDATE() UDF is now a wrapper for CONVERT_DATE. We are working on publishing a TODATE() UDF that is SQL-only and include the source in a future update. It will be in the QDEMOSRC that is shipped with iQuery.
- The hugely popular SQL iQuery CSV() UDTF has been enhanced. A new DELIMITER parameter has been added. This allows users to read user-specified delimited values in addition to Comma Separated Values. For example to read a "bar" delimited file: select ... from table(iQuery.csv('/home/cozzi/pipes.txt', delim=>'|'))) bar'
- SQL iQuery for Web has retired the &VN and &VV HTML Form fields. They continue to function as before, but are being phased out. Virtually none of our customers use these components as they first appeared in SQL iQuery v2 during early iQuery for Web testing.
- New RUNiQRY options on STMFZIP and STMFRMV. STMFZIP now supports a threshold file size that triggers ZIPPING the result. When below that size, the actual file is sent. When it exceeds that size, it is ZIPPED and the ZIP file is sent. The new STMFRMV (Remove Stream file after processing) parameter option allows users to delete the IFS File and/or the ZIP file created for output. Typically this option is used when EMAILing the resultSet file.
- The MBRLIST() UDTF now supports the SRCONLY (Source Files Only) parameter to limit the search to only source files. See the online documentation for MBRLIST() UDTF for more details. Performance of MBRLIST() has also be substancially improved in various situations.
23-FEB-2021- Overall improvement to performance and SQL iQuery Scripting.
- A new IFSPATH() UDTF that replaces the original IFSDIR() UDTF.
- Printed output now supports longer (wider) print lines.
- A new RUNiQRY parameter EMAILJOBQ has been created. Specify the name of a jobq to use when running the email command when sending the resultSet via email. Sometimes JAVA screws up the Job's environment for example when running certain IBM i Db2 "Services" that use Java instead of Native program objects. This new JOBQ parameter allows users to submit the EMAIL command to a batch job to avoid any issues with java. There is also a new EMAIL JOBQ(xxxx); command in iQuery script that can be used to control the same setting.
- A special IBM i V7R2 build of iQuery V6R1 is being made available to our existing iQuery licensees. No further enhancements to iQuery for IBM i V7R2 shall be made beginning March 1st 2021.
- A v6.2 enhancement that writes a DSPF command to the joblog with the IFS outfile name. This allows users to use F9 to retrieve that command for easy viewing of the resulting IFS outfile. TO PREVENT this command from being logged, users can create a data area named IQDSPF in any library on their library list and if that data area exists, the DSPF command will NOT be logged. We recommend creating it in a library other than IQUERY so that when updates are installed that data area doesn't get removed. Therefore create it in a user library that is on your library list.
03-FEB-2021- SQL iQuery V6R1M0 is being announced today. Major enhancements in this new Version include:
  • Growing set of RTVxxxxx UDTFs that provide easy access to data normally available only from CL Commands or APIs.
  • SQL iQuery Call Level Interface (CLI API)
    • iqINIT - Initialize the SQL iQuery Environment
    • iqSCRIPT - Run an SQL iQuery Script
    • iqRUNSQL - Run a dynamic SQL statment using iQuery
    • iqSETVAR - Set the value of a Session Variable
    • iqGETVAR - Get the value of a Session Variable
    • iqDMPVARS - Dump the current Session Variables' values
    • iqCLOSE - Close the SQL iQuery Session/Environment
25-JAN-2021- Enhanced several SQL iQuery UDTFs and added a new one.
- The new RTVOBJLCK UDTF (Retrieve Object Lock List) returns the locks for an object. Unlike other SQL methods provided natively, this one returns immediately.
- A new *OBJLOCKS macro is provided that prompts you for an object name and type and lists the jobs that have the lock on the file. Both lock and job info is returned.
07-JAN-2021- Optimized the IF EXISTS ... conditional statement.
- Added the SHADING parameter to the AFP Driver for those (few) users who have WSCST and Printers that AFP supports for Shading (virtually no one does).
05-JAN-2021- Beta of our Version 6.0 due to be released in March 2021.
- The AFP Print Driver now supports the WriteNextPage() function to eject the page.
- The iQuery Script Conditional Statements engine (IF/WHILE/FOR/LEAVE/ITER) has been re-written. It now supports LEAVE/BREAK and CONTINUE/ITER(ate) operations.
- A new RETURN operation has been added to iQuery Script. Developers can use it to conditional the returned SQL statement. Previously only the final SQL stmt was returned for viewing, printing, output to EXCEL etc. Now specfy a RETURN followed by the SQL statement to be returned is permitted.
if (&FULL = '1'); 
 return select * from qiws.qcustcdt;
 return select cusno,baldue from qiws.qcustcdt;
17-DEC-2020- Our IFSDIR() UDTF now support the omit_list parameter (parm 3) which, if specified, contains the name of a folder to omit from the resultSet.
- Changes were made to IFSDIR to be more inline with the naming used by the IFS_OBJECT_STATISTICS UDTF introduced nearly 5 years after IFSDIR. Since our goal is to provide functionality not competive advantage, we've made two subtle changes to two resultSet column names to provide some level of consistency, as follows:
  • ENTSIZE is now named DATA_SIZE
  • ENTFULLNAME is now named PATH_NAME
12-DEC-2020- Minor fixes and optimizations. Also releasing the V7R2 build along with this refresh.
08-DEC-2020- Beta test release of iQuery Version 6 components are included in this V5R8 refresh.
- New OUTPUT(*ACSxxxx) is featured, allowing customers that have uploaded the IBM-supplied acsbundle.jar to a folder on the IFS to use ACS to generate output from iQuery, including XLSX format files and others. Except for XLSX itself iQuery already natively supported all ACS output formats, including XLS and ODS among others. Note that features such as column totals, fonts and numeric editing for Excel, Text, PDF, etc are not providd by acsbundle.jar so the results may be less robust than using a corresponding iQuery OUTPUT option.
- AFP Drawing is now supported as a Feature. Include the new iQueryAFP include member into your RPG IV code and you can Open, Draw and Write text to an AFP Print file that can then be Printed, sent to PDF, or email to your end users. More on this awesome new iQuery V6R1 feature coming soon.
    /include iQuery/qcpysrc,iQueryAFP  
openAFP('USRDTA(invoice)'); // Override the USRDTA and Open the AFP file.
drawFrame(); // Draw a frame (1-pt line) around the page border
drawImg('/home/images/logo.jpg' : 1 : 0.75 : 3.5 : 1.5); // Print Company Logo
drawRect(0.75 : 0.50 : 4 : 2); // Draw a rectange around the logo w/0.25 spacing
drawText(1.25 : 2.50 : 'The Company Name' : 12); // Print CompName 12pt font
closeAFP(); // Close the AFP Print file.
02-DEC-2020- Our OUTPUT(*EXCEL) Option was exhibitting odd behaviour on the very latest version of Office 365 when User-specified "Freeze Rows" was requested. We have adjusted the output to conform to the latest Office 365 specification requirements.
- Our RTVOBJD (Retrieve Object Description) UDTF now returns the CCSID for the object. Ironically, the QUSROBJD does not return the CCSID so we updated our UDTF to include it anyway. Only the individual Object Retrieve APIs such as QCLRPGMI, QBNRSPGM, or QDBRTVFD return the CCSID. The Db2 OBJECT_STATISTICS UDTF does not (as of this writing) return the CCSID either. So today, you need to use our RTVOBJD or OBJ_LIST UDTFs to retrieve object information that returns the CCSID for the object.
- The RTVOBJD UDTF now returns the CCSID for the Object.
- The OBJ_LIST UDTF now returns the CCSID for each Object.
30-NOV-2020- Fixed our *FINDMBR macro when running on V7R2. Added new STMFNAME and WORKSHEET NAME substitutions values to the RUNiQRY command. Now when %s or %u are inserted into the STMFNAME parameter value or the Worksheet name compone of the XLSOPT parameter, the system name and user profile replace the values, respectively. This allows users not leveraging iQuery Script to specify the names based on the System and User where the request is run.
- New *FILE option on the APPTITLE, USRTITLE and RPTTITLE parameters of RUNiQRY. When specified and a SELECT statement is being processed, the base file (if any) text description is retrieved and used as the Title text. If no base file can be determined, not thing is returned and it continues as always.
- The SQL iQuery API now includes a new interface. IQRUNCMD runs and logs the CL command (similar to QCMDEXC). The unique feature is that this program can run or log the command or both. For Example call iQruncmd('DSPLIBL' '0') causes the command DSPLIBL to be written to the joblog as a Request (*RQS) Message and be retrieved using F9/F8 keys. By default it logs and then runs the command. The valid choices for the 2nd parameter are: 0=Log only (command is not run); 1=Run only (the command is not logged); and 2=The Command is logged and then run. This is the default. The program is intended to be called by RPG IV but could also be called from CL provided the developer creates a VARCHAR parameter in Cl that matches what RPG and SQL use for VARCHAR and VARYING fields. (A 2-byte integer followed by the data/command string to be run).
24-NOV-2020- New WRKUSR feature When F11 or F10 are pressed, the data being presented is changed. Effectively rotating the data betwen SignOn Menu, Initial Program, Group Profile(s), and Creation information. F11 moves forward through the list of options, while F10 moves backwards through the list of options.
- Option 13 now prompts the user to change the *USRPRF's text description only.
- F18 (shift+F6) now brings up a FILTERS window that allows users to specify the selection criteria for the list of User Profiles.
18-NOV-2020- Refreshed V5R8. The RUNiQRY command now includes MSGBODY (email message body) and STMFRMV (Remove Stream file after email is sent) parameters. In addition the XLSOPT parameter now includes an option to set the Alignment of the Title rows. Previously these options where only available in SQL iQuery Script.
- The STMFZIP parameter has been enhanced. It now accepts a threshold size before the ZIP process is triggered. This means that users may now set the maximum non-zip size the IFS file can reach before the ZIP process is invoked. Files smaller than the specified threshold size will not be zipped regadless of the setting for STMFZIP's first element.
- A new message IQI3801 is sent when the number of rows on a single fetch times the number of returned columns exceeds 32k. iQuery now automatically extends the number of supported columns in the resulting fetch when it detects the Cols*Rows exceeds 32k. For example, a resultSet with 300+ columns being output to *EXCEL would use a rows-to-fetch size of 128. 128*300=38400 which exceeds the prior 32k column limit.
- The EMAILBODY parameter is added to RUNiQRY to allow the email message body to be specified on the RUNiQRY command itself.
12-NOV-2020- Refreshed V5R8 with an awesome new WRKUSR (work with user profiles) command.
- The WRKUSR command allows you to edit/maintain User Profiles on your system similar to the IBM-supplied WRKUSRPRF but with more practical information being displayed. Things like Initial Menu and Program, User Class, Group Profile, Last Sign On, Invalid SignOn Attempts, as well as Enabled/Disabled Status. From the list, end-users can launch a variety of CL commands to further work with the existing user profiles, such as CHGUSRPRF, Enable/Disable a User Profile, Delete or Create a User Profile, and more. In addition, pressing F11 toggles though alternate views of the data while F10 toggles the view in the opposite direction. Last but not least, press F13 (or shift+F1) popsup a filter dialog box (window) that allows the end-user to filter the list of users with much more control than the simle USRPRF parameter of the WRKUSR or WRKUSRPRF commands.
- The Work with User Profiles (WRKUSR) CL Command is included free to all licensed users of SQL iQuery. Standard IBM i Security applies.
11-OCT-2020- Refreshed V5R8 with performance improvements and misc fixes.
28-OCT-2020- SQL iQuery V5R8 Formally Released for General Availability.
- Update and Trial Requests may now be routed through the online web page at
- SQL iQuery Trial requests are now limited to 15 days and require the requestor to include their contact info (email and company name).
22-OCT-2020- SQL iQuery V5R8 refreshed with minor improvements and fixes.
- WRKiQRY has been enhanced. It now uses embedded SQL as well as has the ability to Position To a field via F17 (shift+F5).
- See the nightly build log for additional updates/fixes/changes.
01-OCT-2020- SQL iQuery V5R8 refreshed with minor improvements and fixes.
25-SEP-2020- SQL iQuery V5R8 now uses COMMIT(*AUTO) instead of the prior COMMIT(*NONE) as the default for the IQ, RUNiQRYF, and RUNiQRY commands. Normal users should see no difference in their results. Large Object-Binary (LOB such as CLOB, BLOB) require the SQL environment to have at least COMMIT(*AUTO) specified or the query fails. As we continue to improve LOB support in SQL iQuery this change was important to provide the least impact to our customers. Note that COMMIT(*AUTO) is an SQL CLI feature and is not enabled in classic RPG with embedded SQL.
21-SEP-2020- SQL iQuery V5R8 is formally released today. This new version is compiled to IBM i v7r3 and later. It will not run on IBM i v7r2. We WILL post a v7r2 version before October 1, 2020 and it will be the final build for our v7R2 support.
01-SEP-2020- New fixes and updates. See nightly build log for details.
20-AUG-2020- A new iQuery Macro is introduced. *FINDJOBSCD (Find Job Scheduler Entries). This macro prompts the user (using iQuery Script Prompting) to enter a search string that is used to search the exiting Job Scheduler Entries. The Job Scheduler Entry name may be specified, along with a search string. The Entry name and the Command to run are searched for the search text. Apparently a lot of customers have very lengthy Job Scheduler entry lists and have trouble locating what programs or commands are used.
12-AUG-2020- The iQuery IQ command has been updated. A change was implemented to circumvent an issue when an SQL ALIAS is created it QTEMP and then recreated. Now, iQuery uses an SQL Sequence that is created in the IQUERY library at install time, to generate an ad hoc ALIAS name and create that ALIAS in QTEMP. Each time IQ is run a unique ALIAS name in QTEMP is created and then DROPPED (deleted) when IQ returns. The issue that this work-around circumvents is fixed in the very latest V7R3 PTF HIPER package, but is not fixed on V7R2 nor on V7R4 as of this writing.
30-JUL-2020- SQL iQuery can now be used for OUTPUT(*XML) with customizable output options.
- The new XMLOPT (XML output options) parameter allows users to customize the results. XML support is limited to simple field selection such as * or named columns without casting or expressions.
15-JUL-2020- The RTVRCDFMT UDTF is introduced today. It returns a list of record formats names and information for a given file. Typically database tables contain one format, while display files can contain mutliple formats, and rarely some legacy Logical Files are multiformat as well. This UDTF and the supporting iQuery *MACRO named *RCDFMT may be used to retrieve those format names along with field count, format length, text description, and file creation date.
- The DSPVAR/DSPFLD commands now support TYPE(*GENERIC) that works similar to the CL Command Defintion PARM parameter TYPE(*GENERIC) in that it allows an entry to be a "name" or a "generic name". In additional to being a valid name, generic name fields accept trailing * or % (% is SQL generic symbol). If * or % appears anywhere except the last position, the validity checker issues an error. Also, since SQL supports the LIKE operation, we have included support on the TYPE keyword for *FULLGEN or *FULLGENERIC options. This option is similar to TYPE(*GENERIC) except TYPE(*FULLGEN) allows the generic symbols of % and * to appear anywhere in the field.
01-JUL-2020- Our first release of SQL iQuery v5r8 is available today. BETA TEST ONLY
- The new SQL iQuery IQ command for "quick" access to query a database file or view. Simply type in: iq qiws/qcustcdt and iQuery will quickly generate the stanard SELECT statement. Some differents include automatic insersion of the relative record number (or optional line counter). You can customize what is returned, with the FLD (Field list), WHERE (select/omit) and ORDERBY (key field) parameters.
- A collection of new SQL iQuery Macros is introduced including:
  • FindMbr - Find source members
  • FindObj - Find objects
  • FindFld - Find Field where used
  • FindLib - Find Libraries
  • InvalidPWD - List Invalid Signon Attempt from QAUDIT
  • CHGTODAY - List Object that were changed today
  • MYJOBS - List all jobs on system for the User
  • ACTGRP - List activation Groups for the job
  • SPLF - List all of the SPOOL files create by the user
  • SIGNON - List a user's SIGNON/SIGNOFF (job start end) History messages
24-JUN-2020- Final Release of SQL iQuery v5r7 is being released. We are moving to beta test on the next release if iQuery following today build.
- Fast Cursor Support is now optional. When using iQuery with SQL SELECT statements to display results Interactively, we've migrated to a faster SQL cursor type. The advantage of using the SQL FAST CURSOR is that results are displayed very quickly. The downside is that the row count (record count of result set) become unavailable to the application. When the FAST CURSOR is used, no row count is displayed. The benefit is that 100 million + record files can be queried with little effort unless an index/access path is built. Previously a large file (usually more than 10 million rows) would take a long time to display that first page. Now it is display in a second or two. If users perfer to see the row count/resultSet size at the cost of performance (perhaps if they don't have very large row counts) they can configure iQuery to use the legacy Static Cursor. To do that, simply create a data area named IQSTATIC or IQROWCOUNT in a library that will be on the library list at runtime. When either of these data areas exist, only STATIC SQL CURSORs are used and the resultSet row size will be returned normally.
- New #CURSORTYPE directive. This directive temporarily sets the SQL CURSOR TYPE to Dynamic or Static. It can be used in your own iQuery Scripts to cause OUTPUT(*) queries to use the old STATIC CURSOR which returns the resultSet row count. By default interactive queries use a DYNAMIC SQL CURSOR which is substancially faster for very large files. If users prefer to see the number of rows returned, then #CURSORTYPE DYNAMIC should be included in the iQuery script.
- The message filter directive #MSGFILTER ON | OFF now effects iQuery Script source member name logging. When a script member is opened and the #MSGFILTER ON is specified, the message is no longer logged. Other messages perviously impacted by this directive continue as normal.
15-JUN-2020- Added a POPUPFKEY(*SHIFT) option to the DSPWIN command in iQuery Script. Using this setting allows users to change the way the Top Bottom Fn keys are labelled. For example: Normally F17=Top F18=Bottom is displayed. However many non-Green Screen Users can't comprehend F13 to F24 on their keyboards. So iQuery Script Prompter now supports s-F5=Top s-F6=Bottom as an alternative/optional format. To specify it use the above keyword and parameter. The iQuery message file, COZLANGEN contains the messages that provided the text for these function keys. They are message ID QIQC017 and QIQC018 and QIQC027 and QIQC028. Users can customize these messages but please note they are overlaid when updating SQL iQuery to a new build or refresh.
10-JUN-2020- Added F4=Prompt support to iQuery Script Prompter. Now when a field has a set of values (using the VALUES, or SPCVAL keywords) the user may press F4 with the cursor in that field, and a list of those values appears. The user my select one of the listed values by positioning the cursor on the desired value, and pressing Enter.
- A new CLRIQRY (Clear iQuery from Job) command is being introduced. This command ends the IQUERY Activation Group within your job. In rare cases IBM SQL interfaces cause issues where the iQuery application can't clean up its storage. This new CLRIQRY command will do that for the job in which it is run.
03-JUN-2020- SQL iQuery Script now supports connections to remote database systems that are also IBM i, using no user profile and password. When this occurs, the user profile of the current user (the one running iQuery Script) is used to establish the connection to the remote location, no password, typically, required. If uses have the same user/pwd on a 2nd partition, and an iQuery Script contains:
connect to MYPT2;
without any USER and USING parameters, then a connect can still be established using the current user profile and password automatically. This is exposing a design/feature in SQL CLI that we previously did not support.
- Corrected a bug in SCANI/FINDI that we created in a build from March 2020 when we redesigned the flow of our built-in functions.
15-MAY-2020- The SQL iQuery Script IF statement "IF EXISTS" variant now supports remote systems or 3-part names. For example:
IF exists (select * from chicago.orderlib.sales where nesales > 10000);
12-MAY-2020- Re-releasing our 10-May-2020 build with fixes and one improvement.
- The new arrString() or arrstg() built-in function will convert an SQL iQuery Array into a single value either fixed-length entries or delimited entries. For example, if an iQuery Array contains 3 elements:
eval &arr[1] = 'A'; 
eval &arr[2] = 'B';
eval &arr[3] = 'C';
Then, to combined them together, you could use arrstg(&arr) as follows:
 eval &codes = arrstg(&arr);
The result would be &CODES = 'ABC'. Optionally, a separator can be used:
eval &code2 = arrStg(&arr,',');
The result would be: &code2 = 'A,B,C'. Lastly, you can specify a length for each element instead of a delimitor.
eval &code3 = arrStg(&arr,3);
This would result in: &code3 = 'A  B  C  ' The elements do not need to be the same size, so for example an array of 10-character object names, with names of different lengths could be used and assigned to a variable with each entry occupying 10 bytes in that result. The array_stg() built-in function complements the IBM-supplied LISTAGG() SQL UDF by providing a differnt but similar result with more options. Users should continue to use LISTAGG() for those instances when only an aggregated list is necessary and use array_str() when building things for use within iQuery Script.
10-MAY-2020- New built-in functions in SQL iQuery Script: EDIT( value, edit-code/edit-word) This function allows Session Variables containing numeric data to be editted using either an edit code or edit word. You can use EDIT() EDITC() or EDITW() built-ins as they all do the same thing. It doesn't matter if the edit code or edit word is specified--either work on all variations of the built-in function.
#define &amount = 1200.50 
eval &amtX = edit(&amount, 'J$');
eval &amtY = edit(&amount, '$ , 0, . -');
&amtX = $1,200.50
&amtY = $ 1,200.50
07-MAY-2020- HTTP Cookies now insert the SameSite=xxxx attribute. Without this attribute the cookie defaults to SameSite=None which also requires the Secure attribute. With today's refresh, cookies now have SameSite=Lax; inserted into them by default. In SQL iQuery Script, to set a cookie to be returned to your browser, the new sameSite parameter has been added to the end. setcookie userid=values,expires-in-minutes,path,secure,samesite, httpOnly; where sameSite may be: Lax, None, or Strict
When None is specified, then the Secure parameter is set (Secure is required when sameSite=None). If the sameSite parameter is omitted, it defaults to Lax.
23-APR-2020- There is a new installation option "Post-Install Build" jobq name. This option is used to submit a one-off job that builds objects needed by SQL iQuery. These objects (mostly UDTFs and one or two VIEWs) need to be created locally due to some issues with SAVE/RESTORE that has been detected. They also cannot be run in the current job that is installing iQuery. Therefore the option to submit them at install-time is provided. If you choose to blank out the jobq name during install, this job will not run. To run manually, later (in a different job) use the install macro as follows:
as mentioned, this is run once per install or update of SQL iQuery.
23-APR-2020- The iQuery Script #include statement, now accepts Session Variables for the various components. For example: #include userlib/scripts(&mbr) previously no session variables were permitted.
15-APR-2020- The RUNiQRY command can now accept a fully qualified source file member name as the "SQL statement". For example:
runiqry 'iquery/qiqmacro(demo)'
runiqry srcfile(iquery/qiqmacro) srcmbr(demo)
that was previously required. This will run the SQL iQuery Script in the DEMO source member in file QIQMACRO in the IQUERY library. Users requested an easy way to cut/paste logged script source member names into the RUNiQRY command. Since SQL iQuery logs source member (script) names as: library/srcfile(mbrname) users can now cut/paste that name directly into the SQL parameter of the RUNiQRY command rather than re-keying it into the SRCFILE and SRCMBR parameters. It probably would have been easier to simply change the logged message to something like srcfile(library/srcf) srcmbr(mbrname) but it is what it is.
25-MAR-2020- A new RUNOEMSQL (Run OEM Database SQL Statement) is being introduced as a test or demonstration command to run SQL statements using iQuery to target a remote database server such as mySQL, SQL Server or MariaDB. A proper ARD driver must be installed (not included). To date, the only ARD driver I've found that "mostly works" is ardGate by a German programmer. It is open source and available on GitHub. It uses JDBC as the core driver and contains a Java wrapper for the ARD program interface. Further testing is needed but for simple transactions, this seems to provide some limited function.
17-MAR-2020- A new DSN (ODBC DataSource Name) parameter has been added. This is intended to be used to connect to remote database systems via ODBC. However, connecting to existing IBM i database systems via DDM/DRDA also works with this parameter. Use either the RDB/USER/PWD parameters or the DSN parameter to connect remotely. There is a DSN(*AUTO) option that will generate a DSN Connection String from the RDB/USER/PWD parameters automatically. For now, ODBC on IBM i is not something that works (connect from IBM i to non-IBM i databases) Today it only seems to work with IBM i database systems.
16-MAR-2020- Connecting to remote databases now uses mixed case input on the RUNiQRY command. Previously all input passwords were set to UPPERCASE. Now, they remain in upper/lower case as the user has specified. For IBM i systems, this will mean that if you changed your Password requirements to use the long password with mixed case option, you can now use iQuery to connect remotely. Existing IBM i connections should not be impacted by this change as the system ignores upper/lower case for User ID and Passwords.
09-MAR-2020- Google Charts Output Support
- Several users use our HTML output (both JSON and regular HTML Table) to construct web pages. The website itself uses these functions extensively. With today's build, we are adding the OUTPUT(*CHART) option to the RUNIQRY command. #OUTPUT *CHART is also supported. This output device generates a Google Chart result set, including the required Google Chart JavaScript statements, to enable rendering a Bar, Line, Pie, Map, Geo, and other chart types. There are two versions that do basically the same thing: *CHART generates the HTML, JavaScript and DataTable data into an IFS stream file. Whereas *CHARTJS generates the same thing without the HTML wrapper. The results are stored on the IFS and can be viewed or emailed automatically. It is a future objective to also include CHART output from the Web interface--which should be available by the start of Summer.
- For example:
This would create a barchart with the STATE as the labels and BALDUE as the plot points for the barchart.
- OUTPUT(*CHART) options may be controlled with the new CHART command in iQuery Script.
- CHART TYPE(); -- Specify the actual Google Chart chart type as the patemeter. For Example, chart type(BarChart); creates a bar chart, while chart type(LineChart); creates a line chart. Now remember, that the google chart names are case-sensitive so be sure to use the correct upper/lower case for the chart type. A list of chart types is available on this link: Our current support is beta test only and should evolve in the future. Right now, most users can use the barchart default output, or specify the type of chart they need in iQuery script.
- The new CHART OPTIONS(...); command allows users of Google Charts to add any low-level chart options. The parameter value must be exactly as it would appear in the JavaScript generated by iQuery for Google Charts. This parameter is added to the Chart Options setting. For example if you were creating a map of all 50 U.S. States to show the sales by state, you might add the following: chart options("region: 'US', displaymode: 'regions', resolution: 'provinces'");
- The iQuery Prompter now supports a new keyword on the DSPVAR command. The SPCVAL (special values) parameter allows you to provide a non-restrictive list of values that the end-user may view by placing the cursor into the field and pressign F4. Unlike the VALUES keyword, SPCVAL do not cause the input field to be restricted to the list of choices specified on SPCVAL. However, we are also introducing a new RSTD(*YES | *NO) keyword that will cause SPCVAL to be treated just like VALUES choices; that is the user will have to specify one of the values on the SPCVAL list. If both VALUES and SPCVAL keywords are specified for the same input field, the the list of choices is combined and effectively is a long list of restricted-to values.
- New RUNiQRY STMFZIP parameter that will compress the resulting IFS file (when IFS output is selected). Specify STMFZIP(*YES) to ZIP up (compress) the resultings IFS stream file into a .ZIP archive. The original file is still on the IFS, but when STMFZIP(*YES) is specified, a 2nd file, with the .ZIP suffix is created. That file (the zipped archive file) is what will be EMAILed if an email option is also specified.
- Two new data areas are created when SQL iQuery is run, depending on the options selected. When steam file output is selected (such as *EXCEL, *CHART, *PDF, *CSV) the data area IQLASTSTMF (Last Stream file) is created in QTEMP. It contains the name of the IFS file that is created by iQuery. If STMFZIP(*YES) is specified, this will be the ZIP file, noth the stream file output file name. The other data area is for a new option: OUTPUT(*DTAARA). This output option directs the output to the specified data area. If no data area is specified the output is written to data area IQOUT in QTEMP. Specify a user-defined data area name on the OUTFILE parameter. Note that OUTPUT(*DTAARA) is intended for single row, single column output, however multi columns are supported.
20-FEB-2020- SQL iQuery V5R7 is release today. It is available for download from the link on our home page or download page.
20-FEB-2020- Added CHECK(AB) to list of supported keywords on the DSPVAR command in iQuery Script.
- The iQuery Prompter is now shipping with this build. Use it to create simple prompt windows for your iQuery scripts.
  • DSPWIN - Create a Prompt Window
  • DSPVAR - Define a field in the Prompt Window
    • DSPFLD - synonym for DSPVAR
    • DSPFIELD - synonym for DSPVAR
  • DSPTEXT - Add text to the Prompt Window
  • DSPERR - Write text to the error message line in the window
  • EXFMT - Send/Receive Prompt Window
- Documentation for these new features was posted on 20 Feb 2020.
- The SQL iQuery Prompt Screen support is being released for test.
- The SQL iQuery Script commands to create popup windows and fields on those windows follow:
  • DSPWIN - Create a Prompt Window
  • DSPFIELD - Add a field to a Prompt Window
  • DSPFLD - synonym for DSPFIELD
  • DSPTEXT - Add text to the Prompt Window
  • EXFMT - Send/Receive Prompt Window
- DSPWIN - Sets the Window Title and optional dimensions.
- By default windows are autosized but users may set the initial size.
DSPWIN "Customer Prompt";
- This is an early release of SQL iQuery "next". The next release of iQuery.
- Do not use this release in a Production Environment unless you have backed up your existing IQUERY.SAVF prior to loading this release onto your system.
- New features and capabilities are being incorporated into the next version and this build contains several of those enhancements. Among the new features:
  • Dynamically Created Prompts
  • CLOB Support in iQuery Script
  • Custom-Output add-on
    • New ACS Output Options via OUTPUT(*ACSxxxx)
    • All ACS output options are supported, including:
      • XLS, XLSX, CSV, ODS, and TXT
  • Misc enhancements and bug fixes
- SQL iQuery already supports CSV, ODS, and TXT navitely along with the SpreadSheetML format which is used by ODS and we use for our native XLS output. These new ACS offerings are using the ACS engine to create the results and therefore results that vary from iQuery native support can be expected.
- ACS Excel Output generates technically accurate and clean results. However its lack of custom features such as column headings, totals, fonts and different sheet names means that your results are more CSV-like in nature. Also starting up the Java VM to run ACS on the IBM i server is lengthy. For example. on a Power7+ it took over 45 seconds to create our 12-row example Excel file using OUTPUT(*acsXLS) but when we ran the same SQL statement and directed it to our native iQuery OUTPUT(*XLS) it took under 1 seconds.
- ACS Output is available on the OUTPUT parameter of the RUNiQRY command. All ACS output options begin with OUTPUT(*ACSxxxx). Note that this add-in is the only way to create true Microsoft Excel XLSX file. Natively, SQL iQuery creates SpreadSheetML output when the *EXCEL or *XLS OUTPUT option is used. This format is 100% compatible with Excel, but the .XML suffix doesn't normally open in Excel when double-clicked/selected. By forcing the .XLS suffix, it does. As of Microsoft Office 2006, Excel may issue a warning when opening SpreadSheetML files with the .XLS suffix. This message simply indicates that the file isn't a real XLS binary, but another format. That format happens to be SpreadSheetML which Excel supports natively and opens just fine. Other 3rd party spreadsheet apps have no problem opening SpreadSheetML files with a .XLS suffix, including Open Office, Libra Office and more. - RUNiQRY OUTPUT(*XLS) actually produces SpreadSheetML and contains all the fonts and features our customers have enjoyed. The new OUTPUT(*ACSXLS) creates a native XLS "binary" file that has no formatting, but does open in Excel without the warning message being issued.
- SQL iQuery now supports the ability to dynamically create 5250/green screens. The screen is used to prompt the end-user at runtime for input or just issue a message to the end-user. The new dynamic screen interface was created to enable prompting runtime values, it may be used to do most things that can be done with a normal display file. More information is available on the link on our home page.
12-DEC-2019- SQL iQuery v5r2 Is now shipping and available for download.
22-NOV-2019- SQL iQuery v5r2 Is announced today. Shipdate is planned for Jan 2020.
02-SEP-2019- SQL iQuery v5r1 Released. It is available for download from our downloads page.
15-AUG-2019- SQL iQuery v5r1 licensing is now VERSION based instead of Version/Release based. This means that when you upgrade or license iQuery V5R1 the license spans all releases within SQL iQuery Version 5. Meaning V5R1, V5R2, V5R3, etc. So you'll only need a new license key when a new Version is announced or you change Systems and Serial numbers. Previously, each new SQL iQuery Release required a new software license. (NOTE: iQuery is on its Version 5, but runs on IBM i V7R2 and later.)
12-AUG-2019- SQL iQuery v5m1 Release Candidate 1 is now available.
- New in this refresh:
- Web users may now use the HTTP Config command SetEnv to set either the CURLIB or PRODLIB for iQuery for Web CGI scripts. Simply add either of the following SetEnv controls to your HTTP config file:
- While SetEnv iq_CURLIB is different from the IBM-supplied QIBM_CGI_CHANGE_CURLIB Y and QIBM_CGI_LIBRARY_LIST environment variables in that it changes the current library until it is changed again, while QIBM_CGI_CHANGE_CURLIB accepts a Y (yes) or N (no) parameter that tells the web server to change the current library to that of the CGI program being run. The SetEnv iq_PRODLIB is a new function that allows you to set the Product Library of the library list and have it reset when the program that set it ends. In this context, the iQuery WEBIQRY program sets the CURLIB and/or Product Library for you.
- It is now recommended that once iQuery V5R1M0 is installed, that you then run the DLTLICPGM command on iQuery, then re-install it. This will ensure that all BETA and legacy objects are removed. Just doing an install could leave old objects in the library causing issues with future updates. You could also do the DLTLICPGM before install v5r1 however the install program in V5r1 has also been updated to accomodate future changes. This step only need be performed once, although it is always a good idea to delete the existing licensed program when updating or replacing it.
- Most macros have been updated to support any new interfaces. We plan on fine tuning them before the final Release Canddate (RC) is shipped.
- Visit our "nightly build" page for other details on updates to iQuery.
05-AUG-2019- SQL iQuery v5m1 beta is Refreshed today.
10-JUL-2019- SQL iQuery v5m1 beta was Refreshed today.
12-JUN-2019- SQL iQuery v5m1 beta was released on 12 June 2019. Please see the nightly build/fix log for updates provided in this early release.
- New features in this 2nd Beta build are limited to testing and bug fixes.
05-JUN-2019- SQL iQuery v5m1 is on schedule for 12 June beta release.
03-JUN-2019- SQL iQuery v5m1 Feature Enhancements
- SQL iQuery UDF/UDTF and Procedures are now shipped with the product. Prevously they were built (compiled) on the customer system. They are now created and packaged with 2COZ-IQ5 licensed program and restored as executable objects.
- New IFSSTAT() UDTF returns attributes about an IFS file.
- The most important update to iQuery v5r1 is expanded support for web delivery. iQuery now supports multipart/form data in addition to normal/default form. Multipart/form data accepts uploaded files via your web pages. iQuery can now process those uploaded files and allows you to save them to the IFS.
- New JOBLOG() stored procedure may be used to write text to the joblog. For example, RPG users can insert: EXEC SQL call iQuery.joblog('Hello World'); to write a text message to the joblog.
- New JOB_ATTR() UDTF returns the job attributes for the identified job or the current job if no qualified job name is specified.
- New JOBDATE() UDF returns the actual job date (not the system date).
- SQL iQuery is now teraspace enabled. This is required to support large file uploads via SQL iQuery for the web.
14-MAR-2019- SQL iQuery v4r8 has been updated/refreshed and released today. This release contains bug fixes and enhancements. See the nightly build log for details regarding the updates and enhancements.
24-JAN-2019- SQL iQuery v4r8 Formally Released.
01-JAN-2019- SQL iQuery v4r8 beta test is available.
- This release consolidates all the enhancements and fixes introduced in the Fall of 2018 (see notes below for details). In addition a new directive has been created, #ReplaceSessionVariables *IMMED This directive changes when Session Variables are replaced with their values. Normally this is done when the statement is run. However, setting on this setting causes it to be performed while the statement is being constructed. This allows dynamic use of session variable contents while creating lengthy statements. For example, if a counter is used, such as &Count += 1; and that Session Variable is used on the next line of the SQL iQuery Script, and then incremented again after the next line, etc. That variable's value is normally inserted upon completion of the statment build and subsequent running of that statement. Therefore the final value of &COUNT is inserted everywhere in the statement. When #ReplaceSessionVariables ALWAYS is set, then when the line is read, the current value of &Count is inserted. Therefore a user can generate a statement with the value of &Count being 1, 2, 3, 4 etc. rather than only the final value. Synonyms for #ReplaceSessionVariables include #ReplVar and #ReplaceVar and the option may be *ALWAYS, *ON, *TRUE, *IMMED, or *OFF, *FALSE, *NORMAL and *DEFER. The leading asterisk is optional.
12-DEC-2018- A new *JOBSCDPGM macro (Job Scheduler Program-to-Call) lists the job scheduler entries with the Command to run (Program to call) having more space on the resulting list. This joins *JOBSCD which lists the entries, and *JOBSCDE which lists all the entries and the last time they were submitted. Use *JOBSCDPGM when your focus is determining the program being called/command to be run by the job scheduler entry.
26-NOV-2018- The OPTIMIZE FOR xxx ROWS clause that is automatically generated for interactive SELECT statements may now be customized. Previously only "OPTIMIZE FOR 100 ROWS" was added and users had no choice in this option. Now, users may specify the number of rows for the OPTIMIZE FOR xxx ROWS clause, or prevent this clause from being added by specifying *NONE. This option has been added to the existing OPTIMIZE parameter as its 2nd element. OPTIMIZE(*FIRSTIO 50) causes "OPTIMIZE FOR 50 ROWS" to be automaticaly added to the SELECT statement, while OPTIMIZE(*FIRSTIO *NONE) will prevent the clause from being inserted. Note the use of the the first element *FIRSTIO or *ALLIO is still valid even when *NONE is specified for the second element.
16-NOV-2018- A new iQuery macro *SQLINFO is being shipped with this build. Use it to display the SQL statements used in user-created programs in a given library. Currently the macro works with just one library name. By default it queries the *CURLIB. Users may specify the library to query by passing in the SETVAR((LIB mylib)) parameter on the RUNiQRY *SQLINFO command.
14-NOV-2018- The ifsExists() UDF (Check if IFS file exists) has been enhanced. It now returns SQLSTATE=02000 if the file is not found. In addition, if the file being checked is not fully qualified to an IFS directory path, the message that is generated when the unqualified file is not found now identifies the current working directory.
- The ifsExists() UDF (Check if IFS file exists) now supports a 2nd parameter. The supress message parameter may be 0 to NOT supress "Not found" messages or 1 or more to indicate that "Not found" messages are supressed.
- A new directive #SPURIOUS_MSG may be specified to supress "Not found" messages from the IFSEXISTS() UDF and other superfluous messages. #SPURIOUS_MSG OFF will turn off certain resulting messages, such as the "Not found" message from IFSEXIST. Users may wish to reduce joblog messages by adding this directive to their SQL iQuery Scripts.
- A new built-in function GETCWD (Get Current Work Directory) or the synonym GETCURDIR may be used to return the current directory. This value may be assigned to a Session Variable or used on a conditional statement. eval &curdir = getcwd();
- A new SQL iQuery directive #md, #makedir, or #mkdir is introduced. This directive allows you to create an IFS folder. The one and only parmater is the fully qualified directory name. For example:
#md /home/cozzi/myNewFolder
This will create the new folder named MYNEWFOLDER in my home directory. When a new folder is created, SQL iQuery applies the following "modes" to the new folder: S_IRWXU | S_IRWXG | S_IRWXO
- A new SQL iQuery directive #cd, #chdir, #chgcurdir or #cwd is introduced. This directive changes the current directory for the job to the specified folder/directory name. #cd /home/cozzi
08-NOV-2018- A new UDTF and Macro are being shipped today. The RTVLIBD UDTF (Retrieve Library Description) returns the library size and object count information.
select * from table(iQuery.RTVLIBD('QGPL')) ld;'
- The *LIBSIZE macro provides a simple method to list the sizes and object counts for the libraries on your system. It supports one parameter, LIB. The LIB parameter maybe generic or a full library name. The default is *ALLUSR. In addition to a generic or full library name users may also specify: *ALL, *ALLUSR, *ALLSIMPLE, or *LIBL. With the *LIBSIZE macro, the results are sorted by library size in descending sequence. RUNIQRY *LIBSIZE creates of list of all libraries and their sizes and object counts. To include only the library names that begin with ORD, specify the SETVAR((LIB ORD*)) parameter. Likewise to include only libraries that are on the job's library list, specify SETVAR((LIB *LIBL))
05-NOV-2018- A new Macro is being shipped today. RUNiQRY *LASTIPL It shows users the date and time the operating system started. This is not exactly the IPL date/time but it is as close as we can get.
- A new SQL Stored Procedure named PRINTPDF is being introduced. This is a direct port of our hugely popular COZTOOLS CPYFRMPDF CL command. Now, SQL iQuery users can call iQuery.PRINTPDF() and copy of PDF file from the IFS to an OUTQ--one that is connected to a PDF-compatible printer, just as most HP printer--and that supports the PDF Direct protocol. The new procedure has 5 parameters, but only the first one is required. Syntax:
call iQuery.PrintPDF('/home/cozzi/hello.pdf');
This will send the HELLO.PDF file to the OUTQ associated with the job.
The parameters are:
  1. PDF File Name on the IFS
  2. Output Queue name or *JOB Default=*JOB (outq may be qualified)
  3. HOLD - *YES | *NO Default=*NO
  4. SAVE - *YES | *NO Default=*NO
  5. COPIES - 1 to system-limit Default = 1
- A new SQL UDF (Function) named PRINTPDF is being introduced. This is a direct port of our hugely popular COZTOOLS CPYFRMPDF CL command. Now, SQL iQuery users can evoke iQuery.PRINTPDF() to copy PDF files from the IFS to an OUTQ--one that is connected to a PDF-compatible printer, just as most HP printer--and that supports the PDF Direct protocol. The new function may be embedded within a select statement to allow users to process a set of PDF names from the IFS, for example using our IFSDIR() UDTF, and then send those PDF images to a specified Output Queue with just one operation. For the parameter list, see the notes for the PRINTPDF Stored Proceure.
 select iquery.printpdf( entFullName, 'LASERJET' ) from table(iquery.ifsdir('/home/cozzi/*.pdf')
The above statement would send all PDF files found in folder /home/cozzi to the LASERJET output queue.
30-OCT-2018- SQL iQuery command SwapUser <user>,<pwd>; is being introduced. This swaps the user profile for the job to the specified user. It allows you to specify just the user profile or the user profile with *NOPWD or *NOPWDCHK. If any other value is specified, then it assumes you are specifying the user's password. The user running the SWAPUSER command must have *USE authority to the user profile being swapped to.
- A bug in the #HTTP_OUT directive has been corrected.
30-Oct-2018- The #ColStyle and #ColAttr directives are being introduced. They are synonyms, and they allow you to add HTML to any resultSet column being sent to the browser when OUTPUT(*HTML) is used or iQuery for the web is used. Normally these directives are used to add HTML style to the resultSet column, but may also be used for other purposes. For example, To make the sales column bold, you might add the following to your SQL iQuery Script: #colStyle sales, <b>%sales%</b> -- Remember the percent sign is used to identify columns from the resultSet whose data is to be inserted.
25-OCT-2018- SQL iQuery macro *SPLF is announced. This uses the SQL SPOOL and Librarian Services to generate a list of SPOOL files owned by (created by) the user running the macro. The &USER parameter may be used to override the user to another user. For example: RUNiQRY *SPLF SETVAR((USER PETER)) this will list the SPOOL files for User Profile "PETER". It also supports a JOB parameter to list all the SPOOL files for a specific fully-qualified job name.
- A new SQL Procedure named CPYTOPDF has been added. This procedure copies existing SPOOL file to PDF format on the IFS. The SPOOL file must be *SCS or *AFDS format.
18-OCT-2018- SQL iQuery script now issues a message when it detects an unmatched conditional statement. For example, if there are 3 IF statements in the script, and the developer specified just 2 ENDIF statements, a warning message is issued upon existing from the iQuery script processor. However if the EXIT or RETURN opcodes are used to leave the script, no such message is generated.
- The #URL and #TAG directives have been deprecated. Do not use these two directives.
- The new #COLLINK (Column link) directive allows users to specify a resultSet column and the HTML link (anchor) to be inserted "behind" it when generating HTML. Unlike the #URL and #TAG directives, #COLINK allows for multiple columns to have URL links behind them. Each #COLLINK applies to a different result column. A synonym directive to #COLLINK is #URLLINK. The #COLLINK uses the new auto-compress column identification routine. This routine compress each column name, filtering out blanks and special symbols, to produce a more accurate column name to reference. This means that when a column name in the SELECT statement is altered to "Customer Nbr" so that the headers are clear, the user may reference that column as Customer Nbr or as CustomerNbr as they are treated as the same name internally.
Therefore: #COLLINK Customer   Nbr, HTTP://
would insert the link to SQL iQuery web page behind the Customer Nbr column,
as would: #COLLINK CustomerNbr, HTTP://
10-OCT-2018- SQL iQuery for web has been updated to support integrated reflowTable() support. Now when an iQuery Scrolling Table is generated via HTML, the iQuery.js routine named iQuery.reflowTable() is automatically called to do slight adjustments to the HTML table column widths. Previously this javaScript function had to be called in the end-user's HTML. This is no longer required.
- A new #HTML_TABLEID (HTML Table ID) directive allows users to specify the value assigned to the <table id="myTableID"> in the <table> tag.
- A new #HTML_TABLESORT (Enable HTML Table Column Sorting) directive has been added. Now by default all iQuery-generated HTML Tables may be sorted by the end-user by simply clicking on the column heading. To disable this capability, insert the new #HTML_TABLESORT directive with OFF or DISABLE as its parameter. for example: #HTML_TABLESORT disable
- Our iquery.js javascript file has been updated to support our new iQuery HTML features.
- A new RTVJOBA (Retrieve Job Attributes) UDTF is introduced. This UDTF returns information about the job name or internal job ID specified. The info returned is from format 0300 and 0400 of the QUSRJOBI API. The syntax is select * from Table(iQuery.rtvjoba( qualified job name or null or blank [, Internal 16-byte Job ID ])) joba;
20-SEP-2018- The SQL iQuery install program that is evoked automatically by the RSTLICPGM command now prompts the end-user for the optional email client to use when optionally sending an iQuery resultSet to an end-user via EMAIL. The old default was our free SENDMAIL client, but iQuery also supports the IBM-supplied SNDSMTPEMM (Send SMTP Email Message) CL command. Now when installing iQuery the user is prompted to select which of these two email options they prefer. If you select SENDMAIL, then you should download and install our SENDMAIL Java-based email command available free at If you select IBM's SNDSMTPEMM it should already be on your system. Please NOTE: Which ever is used, you must also have setup your email server for the corresponding command. SENDMAIL uses a Properties text file stored on the IFS in /home/sendmail/ while SNDSMTPEMM uses IBM-documented configuration settings. Here are examples of the SENDMAIL properties IFS text file that configures your email client so you can use our SENDMAIL email CL command with a 3rd-party email systems:
mail.smtp.local=[your local email domain (optional)]
mail.smtp.from=My Name<>
(e.g.) - EXAMPLE Go Daddy Properties
mail.smtp.from=Bob Cozzi<>
(e.g.) - EXAMPLE Google GMAIL
mail.smtp.user=[your gmail email address here]      
mail.smtp.pwd=[your gmail email password here]
mail.smtp.local=[your local ISP email relay domain here]
Here is more information if you choose to use the IBM-supplied SNDSMTPEMM CL command: "Your current user profile must be enrolled in the e-mail directory that is set by the CHGSMTPA command and the DIRTYPE keyword. For a setting of *SDD for the DIRTYPE keyword your user profile must be enrolled in the System Distribution Directory(SDD) and your user profile must also have an smtp name defined via the WRKNAMSMTP command. "
12-SEP-2018- SQL iQuery Script now supports an RPG-compatability mode for Host Variables. When using the SELECT INTO clause, SQL iQuery Session Variables may be specified using the standard SQL iQuery notation of &Var, &var2, &var3 or they may be specified using the RPG-style notation of :var, :var2, :var3. This enhancement is to allow RPG developers to cut/paste code between RPG and SQL iQuery without the need to modify the host variables. Note that the INTO clause is the only place this dual syntax is supported. Often RPG developers use SQL iQuery to build and test out their complex SQL statements. Then move the SQL statement to their RPG program. Now, this process can be virtually seemless.
- The CACHEBATTERY UDTF and VIEW have been updated to move the Important Information earlier in the resultset. Now the Days Used, Days Until Warning and Days Until Error are listed after the Cache Battery Type and Model number. Previously they were included at the end of the resultset.
- The CACHEBATTERY VIEW now supports 3-level names. To view the cache battery data on a system or partition other than the local one, specify a SETVAR value of RMT and the remote partition name. For example SETVAR((RMT CHICAGO)). This uses the cachebattery view installed in iQuery library and thus requires iQuery on any remote partition being accessed for this UDTF/VIEW to properly work remotely.
28-AUG-2018- The RUNiQRYF command's FILE parameter now includes a Remote Database name. Previously to access a remote table with that command, users would need to specify the RDB, USER, and PWD parameters. Now the FILE parameter includes a second element "Remote DB name for 3-level naming" that may be specified instead. This greatly simplifies remote database table access. Note that the internals of RUNiQRYF have been updated to directly access the remote DB system for things like file existance testing and library list searches. Also please make note that this is the RUNiQRYF command typically used by end-users and not the standard RUNiQRY command used for running SQL statements and iQuery scripts.
21-AUG-2018- A new "open source" UDF is now included. CONVERT_DATE() that may be used to convert legacy non-date fields that contain date info, such as a 6 or 8-digit numeric fields, or 6 or 8 position character fields to a true date value. Usage:
 values convert_date('180901','ymd') -> Date('2018-09-01') 
This will return a true date data-type to the caller that may be used like any other date value. Previously, we supplied our proprietary CVTDATE() routine. However, so many customers have become dependant on that routine for everyday use we felt it was justified to migrate it to an open source UDF at this time. We shall continue to ship CVTDATE for the foreseeable future, but customers should start using convert_date().
- Another open source UDF we're shipping today is the GETENV() UDF. This UDF returns the value of an Environment Value. While 99.999% of all Environment Variables are under 1k in length, there is actually no practical limit to their size; one IBMer suggested the max length is 16MB, so GETENV() has a return value of VARCHAR(32739) which is the SQL varchar limit. If that length drives you nutts, you can always change it to a CLOB yourself.
14-AUG-2018- SQL iQuery v4r7 is available.
- Externally Described SQL APIs are included in this build.
- The prototypes can be imported into your RPG code: /include iquery/qcpysrc,iQueryAPI
- The available APIs as of this initial release include:
  1. iqLoadSQL - Load SQL from an SQL iQuery Script
  2. iqSetVar - Set an SQL iQuery Script Session Variable Value
  3. iqGetVar - Get an SQL iQuery Script Session Variable Value
  4. iqClearVars - Clear all existing SQL iQuery Script Session Variables
  5. iqDumpVars - Write the names and values of each Session Varaible to the joblog
  6. iqSetVarInt - Set an SQL iQuery Script Session Variable using an Integer
  7. iqSetVarDec - Set an SQL iQuery Script Session Variable using Decimal
  8. iqServerMode - Set up SQL iQuery to run using SQL Server Mode
- We are also announcing the upcoming release of iqRUNSQL (Run SQL statements using iQuery). This API will run a user-supplied SQL statement, such as one returned from the iqLoadSQL API or another source, producing the desired output such as (CSV, XLS, PDF, Print, etc.) directly. This would be similar to embedded the RUNiQRY command in the RPG code and using QCMDEXC to run it. The planned availability for the iqRUNSQL API is October 16, 2018.
- Our original CVTDATE() UDF that converts legacy dates in numeric or character format into a true SQL date value, now supports input values with or without embedded date separators.
31-JUL-2018- Internal fixes. In rare cases, the statement handle was not be allocated before it was checked. This issue didn't exist until we implemented the Externalized SQL feature recently.
- The Excel COLUMN() parameter now accepts *LAST as the column number (first parm).
31-JUL-2018- Changes to the Excel Column Headings Scroll Lock internals. No user-facing changes. Users can still use #TITLES and #COLHDR or simply use the EXCEL lockHDR(disable); command to avoid the built-in Scroll Lock of the first two rows of your Excel output. Likewise the recently introduced XLSOPT( *N *UNLOCK) continues to be another choice for blocking the built-in scroll lock. (NOTE: Customers with very old versions of Excel installed are experiencing all rows being locked when the scroll lock is enabled. However, Office 365 Users on any platform report no such issue.)
- Beta test release for the new "Externally Described SQL" feature. Users are now be able to load SQL from an external source file member (or IFS file) and run all the normal script logic over that source member. The resulting SQL statement produced is then returned via the External SQL API to the RPG IV program that called it for processing. Note the final SQL statement in the SQL iQuery script is never run, it is always returned to the caller to be processed.
27-Jul-2018- New XLSOPT() option *UNLOCK allows end-users to turn off the built-in Excel spreadsheet output option that locks the first 2 rows for scrolling. Users reported that on very old versions of Excel the entire sheet was being locked. This is not the case with supported versions of Excel, such as Office 365. Uses may now specify RUNiQRY ... OUTPUT(*EXCEL) XLSOPT(*WIN *UNLOCK) The XLSOPT parameter is where we enable Excel options. See the prompted RUNIQRY command the XLSOPT for more information.
26-Jul-2018- New CSV_COUNT() UDF that returns the number of columns it detects in the IFS text (CSV) file specified on the CSV() UDTF function. Users asked for a way to count the number of columns of a CSV file to ensure the data was (at least) properly presented. The internal CSV() UDTF was already storing that information, so adding CSV_COUNT(data) made it relatively easy to implement. This will be included in the next refresh of the distribution. But for now it is available in the nightly build.
20-Jul-2018- New #HTML_TBODY_HEIGHT directive allows users to set the height of a generated HTML table. Normally the default is 360px but a frequent request was to be able to set it to auto. What ever value is specified here, it is inserted into the "Height:xxxx;" attribute of the HTML TBODY tag. The use of height:auto; is useful when the resultSet size is just a few rows and 360px ends up leaving too much white space below the last row of the resultSet. End-users can still resize the table by dragging the lower right corner.
- New #HTML_TABLEID or #HTML_TABLE_ID directive allows users to set the name of the HTML table being generate. For example: #HTML_TABLEID PARTSLIST will set the ID attribute to ID="PARTSLIST" for the HTML TABLE tag. Frequently users then use our iquery.js reflowTable function to fine tune the column widths after it is loaded. For example: <#include virtual="SQLiQuery Script to generate table..."> which is then followed up with <script> iQuery.reflowTable("PARTSLIST");</script> Users also often insert the reflowTable() function on their onload handler or if using JQuery in the onready callback.
10-Jul-2018- New macro *STACK is introduced to list the call stack entries. *STACK is more of an example than it is useful.
- A new RPGIV-style XLATE() built-in function is being introduced. While SQL gives users alternative solutions to translation, users are accustomed to %xlate() in RPG. Now, SQL iQuery Script's XLATE built-in function provides the same type of capability and syntax. Just remember that SQL iQuery built-in functions use commas as parameter separators.
 #default &Phone = '(630) 907-HELP';
#default &alpha = toupper('abc def ghi jkl mno pqrs tuv wxyz');
#default &digits = '222 333 444 555 666 7777 888 9999'
eval &phone = xlate(&alpha, &digits, &phone);
The result is &PHONE = '(630) 907-4357'. If the 2nd parameter of XLATE is empty (2 consecutive quotes) or *NONE or *NULL, then the characters from parameter 1 are deleted from the 3rd parameter and the data to the right of them is shifted to the left.
- A new JOB() function is introduced. It returns the part of the current job's job name requested by the user. The default is the job number. The valid choices are: NBR, USER, NAME, JOBNBR, JOBUSER, and JOBNAME. In addition, the value, INTID returns the 16-byte internal job ID, and QUALJOB (or simply QUAL) returns the fully qualified job name (similar to the QSYS2.JOB_NAME global variable). An asterisk may be used as in *NBR, *USER, etc. but is not required.
04-Jul-2018- New 4th of July Release contains bug fixes and new features for our customers to enjoy.
28-Jun-2018- A new release that includes a few new UDFs/UDTFs and fixes.
- The *MBRLIST macro now allows the LIB session variable (assigned using the SETVAR parmaeter) to be assigned *LIBL, *USRLIBL, or *ALLUSR as the library name(s).
- The chkObjExists() UDF now has a synonym of CHKOBJ() to match the CL command name.
- The OBJ_STRUCT() UDTF and *OBJSTRUCT macro have been enhanced.
18-Jun-2018- Resulting Excel Date and TimeStamp columns have been enhanced. See fix log for detals.
10-Jun-2018- A few new features and fixes are being introduced today.
- A new iQuery.js function reFlowTable() has been introduces for SQL iQuery for web. Basically it will take the output of our iQuery Scrollable table and refine the width of each column. In some cases in some browsers users would see columns appear slightly smaller or wider than desired. This is due to the nature of HTML Tables. The new reflowTable() function will properly format the table after it has been loaded, providing a clean and desireable look and feel.
- The Work with File Definition using iQuery (WRKIQRY) command now lists the columns in the sequence in which they appear in the database table.
- The SQL iQuery Script checkObjExists() function is now provided as an external SQL UDF (function) that can be used in with SQL in RPG and other languages. The function is named CHKOBJEXISTS() and returns '1' if the object exists, otherwise it returns '0'. To use it in RPG:
exec sql VALUES chkObjExists('QIWS','QCUSTCDT') INTO :exits;
This would return '1' into EXISTS if the file QCUSTCDT exists in library QIWS. If a non-file object needs to be checked, specify the object type as the 3rd parameter.
- A new RTVOBJD UDTF is introduced. It returns most of the information from the QUSROBJD API with format OBJD0300. This was primarily created for 2 reasons: (1) To complete the list of RTVxxx UDTF (RTVMBRD, RTVCMDD, RTVNETA) we're providing and (2) The IBM-supplied OBJECT_STATISTICS() UDTF is missing a couple of columns of information until you get to IBM i v7r3. So for those running v7r1 or v7r2 RTVOBJD can be used with a lateral join to complete the resultset.
25-May-2018- A new IF EXISTS library/file(mbr) *MBR; syntax has been introduced. This allows developers to check if a member exists in the designated file. The syntax may be:
IF EXISTS <library>/dbTable(member) *MBR;
IF EXISTS <library>/dbTable,member *MBR;
- Three new directives to help turn off certain capabilities are being introduced. #SPCVAL or #SYMBOLS are used to turn on or off the processing and substitution of *xxxx built-in special symbols. Also #BUILTINS or #BIF allow users to turn on/off processing SQl iQuery's built-in functions, such as DTAARA(), USRSPC(), SST(), etc. The final new directive #LOGCL or #LOGCLCMD controls whether or not CL commands that are run in your SQL iQuery Script are written to the joblog.
- All of these new directives support the standard ON/OFF, TRUE/FALSE, YES/NO, ENABLE/DISABLE parameter options. For example to disable the running of SQL iQuery Built-in functions: #BUILTINS OFF or #BUILTINS *OFF.
- The new directives' defaults are as follows:
- Two new macros are being shipped with this build. RUNiQRY *JOBLOGEX produces a list of joblog messages that are Severity 30 or greater along with reduced information, such as the from/to programs and statements, and message text. This is great for reducing the clutter of an entire joblog. The second one is RUNiQRY *FUNC which is similar to our *COZFUNC but includes the text description of the function along with the function type and name.
12-May-2018- Various bug fixes and a new #PRTEMPTY directive that forces printed output even when the resultset size is zero.
- The #PRTEMPTY directive controls whether or not to print (when OUTPUT(*PRINT) is used) the result set headers/footers when the resultSet size is 0. By default this setting is off.
- The SCANRPL CL command is now included with SQL iQuery. There is no need to use the separate download for this stand-alone tool.
- A new RTVCMDD (Retrieve Command Definition) UDTF is introduced today. Use it to return a single row of information about the specified CL Command. The syntax is:
 select * from table( iQuery.rtvcmdd('COZTOOLS','RTVDATE') ) cd';
Where COZTOOLS is the library name, and RTVDATE is where the command name goes. You can use *LIBL or *CURLIB as the library name instead of a specific library name. Tip: Use this with OBJECT_STATISTIC() or the iQuery OBJ_LIST() UDTF to query all information about *CMD objects.
22-Apr-2018- The maximum length of a CL command in SQL iQuery script has been increased. The new
CL: CPYF ...;
The new limit is the same as the system limit... appromitately 32k bytes.
12-Apr-2018- A new DECRESULT parameter has been added to RUNiQRY. This allows users to set the decimal precision that SQL uses for intermediate results. The options are 31 or 63 digits, with the SQL iQuery default being 63. This parameter functions similar to the RUNSQLSTM DECRESULT parameter. To set this option within SQL iQuery Scripts, use the #DECSCALE or #DECRESULT command with up to 3 values, Len,Decimals, Divide operation decimals. e.g., #DECRESULT 31,31,0;
- A new UDF is being introduced: RTVNETA('networkattribute') that returns the network attribute identified by the input paramter. The default is 'SYSNAME'. The list of network attribute identifiers that may be used for this UDF is located in the IBM Knowledge Center under the listing for the QWCRNETA API.
- The Job_Attr() UDTF and corresponding IQ_JOBATTR view now include the Job Date in column JOB_DATE.
22-Mar-2018- A new CACHEBATTERY() UDTF (function) is being introduced along with an SQL VIEW of the same name. Use this capability to query the cache battery on all your systems. The syntax is simple: SELECT * FROM iquery.CACHEBATTERY to view your system's cache batteries' status. Alternatively use the UDTF if you're into more complex syntax. SELECT * FROM TABLE(iquery.CacheBattery()) cb
- The advantage of this View and the UDTF is that with iQuery installed on other partitions or physical systems, you can use remote 3-level names, or the RDB() parameter of RUNIQRY to query the cache battery status of those remote systems. For example:
runiqry 'select * from chicago.iquery.cachebattery' 
runiqry 'select * from iquery.cachebattery' RDB(chicago) pwd(rosebud)
15-Mar-2018- A new release with bug fixes and enhancements has been posted for download.
07-MAR-2018- Column totals in Excel may now be used when multiple worksheets are specified.
- The worksheet name is now auto-generated when PageBreak is used. The data in the changed column is used as the sheet name.
- The width of column headings in Excel output was ignoring the text used as column headings (using only the data within the columns). This has been corrected.
01-Mar-2018- A new #PAGEBREAK directive has been added to SQL iQuery Script and to the RUNiQRY command. Specify the relative column number or field name whose value, when changed, causes printed reports to Page Eject (skip to the next page). Normally one column is specified, but up to 100 column names or relative column numbers may be specified. Logically, the Page Break directive is similar to our LEVELBREAK directive. LevelBreaks only print when the conditioned field's content has changed. PageBreak skips to a new page when the conditioned field's content has changed or Overflow occurs.
- When OUTPUT(*EXCEL) is specified, the #PAGEBREAK directive is used to generate a new Excel WorkSheet (tab) within the workbook. By default the data in the column used to control the #PAGEBACK is used as the name of the new tab. For example, #PAGEBREAK 3; If column 3 is STATE, then when the STATE content changes, the new content is used as the WorkSheet name. Everything else (headings, totals) repeats.
- EXCEL COLUMN() formatting now supports two new formats:
  1. Accounting
  2. Hidden
The Accounting format embeds commas into the numeric value, and Hidden hides the value from the Excel user but still writes it to the SpreadSheet. Here's an example:
excel column(4,numformat,accounting);
will set the 4th column so that it uses the Accounting numeric format in Excel.
- A new #PRTSPACING directive is being introduced for SPOOL and PDF output options. This directive sets the line spacing for each row in the resulting report. Users have asked for a "Double Space" option. The #PRTSPACEING option allows users to single, double, triple or more space. To double space the results, specify #PRTSPACING 2
We hae also introduced another simliar directive for double-spacing only.
When TRUE, the report will be printed double-spaced. When not true, the report is printed as it would prevously (i.e., single-spaced).
- EXCEL column attributes are enhanced to be more user-controlled. Now, the EXCEL command's COLUMN keyword supports WORDWRAP, VALIGN, FontSize, BOLD, Color, underline, FontFamily, ital, bgColor, bgPattern, and NUMFORMAT (numeric output formatting).
- EXCEL LINEFEED( mac | windows) can be used to set the line feed ASCII code used for new lines within Column Headings and Titles in the generated Excel file. Normally Windows systems use x'13', however the Mac (Apple) OS X uses X'10'. Users may target the end-user's operating system to better format columns headings using this new EXCEL attribute. The options are MAC or Windows with Windows as the default.
20-Feb-2018- A new style() directive has been added to the print after command to control the look of the so called extra output generated by a print after statement. Use "style(color:red)" (for example) to set the output to red. More documentation on this keyword is forthcoming.
print after(3,style(bold,right, underline, fontsize: 18),'Hello World');
This would print "Hello World" in the 3rd column of the Excel file, the text would be BOLD, RIGHT aligned, Underlined, and a Font Size of 18 point would be used.
01-Feb-2018- A new OUTPUT(*ODS) option has been introduced. This generates a spreadsheet on the IFS that is in the OpenDocument SpreadSheetML format. The resulting file contains the .ods suffix. This is normally used in LibreOffice and Open Office software. Since our Excel format is idential to SphreadSheetML's .ods format, every new feature/enhancement that refers to EXCEL output now also applies to .ODS output.
- The EXCEL option to write data to the generated sheet after the result set by using the PRINT AFTER() statement has been enhanced. Now users may specify the number of columns to span (or "merge" in Excel-terms) for the next entry. For example:
print after(3,'Company:',span(3),'Cozzi Productions, Inc.');
This causes the "Cozzi Productions, Inc." entry to span columns 4, 5, and 6. The first parameter of the after() keyword indicates the starting column number where the word "Company:" is written. The next parameter is "span(3)" which tells the print command to set the Excel Merge Cells attribute to 3 cells. The keywords "span" and "merge" are supported and perform the same task. Either keyword must be followed by the number of columns to span, enclosed in parens. And yes, if you follow a "span(x)" with another "span(y)" value, then the value "span(y)" shall be inserted into the cell.
18-Jan-2018- New *OUTPUT, *OUTPUTQ, *DFTOUTPUT figurative constants are now available for SQL iQuery Script conditional statements and assignements. Use them to check the current value for the corresponding setting. For example if the keyword OUTPUT(*PRINT) is specified on the RUNiQRY command, then
can be used to check if the output value is *PRINT. Note that OUTPUT values are stored in all upper case, and are therefore case-senstive. Note that *OUTPUTQ is used instead of *OUTQ. This is because the rule in place that states no SQL iQuery figurative constant may be the same as an IBM i Object Type. Therefore since *OUTQ objects exists, *OUTQ cannot be used as a figurative constant. So we use *OUTPUTQ instead.
18-Jan-2018- New #OUTPUT and #DFTOUTPUT keyword directives have been added to SQL iQuery Script. Use these keywords to set the OUTPUT device instead of using the OUTPUT() parameter of the RUNiQRY command. This allows Scripts to have a designated output other than the default on the command. For example, if the results of a script should always be sent to Excel, use the #OUTPUT *EXCEL keyword to force the output to Excel. If you need the default output to be something like *PRINT unless the end user specifies a value for the OUTPUT() parameter of the RUNiQRY command, in which case you want the OUTPUT() parameter to take priority, then the new #DFTOUTPUT keyword directive may be used to establish a default output device. For example #DFTOUTPUT *PDF forces the output to PDF unless the RUNiQRY command's OUTPUT() parameter is specified.
15-Jan-2018- When OUTPUT(*EXCEL) is specified, the columns for text (character) fields are forced to "Text" format instead of the default "General" formatting. This will help users who were experiencing numeric data that is stored in Character fields being zero suppressed by Excel. This change eliminates that issue.
09-Jan-2018- The "Find Member" parameter of QUSRMBRD is now being used. This means that when the SRCMBR and SRCFILE parameters are specified, and the SRCFILE's library is *LIBL, then if the SRCMBR is not found in the QSQLSRC file (or what ever file is specified) then the library list is seached for the next library that contains the file, and it is searched for the member name specified. The process repeats until it finds the member name or there are no more file's with the same name on the library list.
- Implemented a left-side SST() built-in function. Now users can use something like this:
eval SST(&TEXT, 11,5) = 'Hello';
to copy the value 'Hello' to the &TEXT session variable, starting in position 11, replacing positions 11 to 15 with the content of the rValue ('Hello' in this example). If the rValue is longer than the SST length parameter, the data in the lValue session value beyond that location is shifted right. In other words, this is saying "Replace position 11 to 15 with the text on the right-side of the equals sign. If &TEXT = 'Bob Cozzi, Jr. Programmer, SQL iQuery' and you issue a
eval sst(&text,16,10) = 'Author';
Then &TEXT = 'Bob Cozzi, Jr. Author, SQL iQuery'
04-Jan-2018- First release of SQL iQuery for IBM i v7r2-only build. The default download now works with IBM i v7r2 and later only. Support for IBM i v7r1 is also shipped as a separate download. However, this is the final build for IBM i v7r1.
21-Dec-2017- This is our final release of the 2017 calendar year. It is also the final release that is compatible with V7R1M0. If any serious issues are detected, we will issue a fix but as of now, the 21 Dec 2017 build is the final V7R1M0 compatible version we will be releasing.
06-Dec-2017- Excel Column Headings may now be rotated using the Rotate directive with degrees.
excel colhdr(rotate,45);
This rotates the column heading text by 45 degrees. Some Excel users had requested this capability to further reduce the post-processing of the Excel sheet that is generated.
20-Nov-2017- The new *HIST macro has been added. Running this macro shows the history log messages with message severity greater than 0. The range of messages is 24 hours old to current however, the session varaibles FROM and TO may be overriden using the SETVAR parameter of RUNiQRY by passing in the timestamp for the start and end periods. The available settings via SETVAR include:
  • SEV - The Message Severity to include (>=)
    • The Special Value *ALL may be used to indicate all severity levels
    • 0 to 99 are the available severity levels
    • The default SEV level used by this macro is 10
      • This filters out sev(0) messages.
  • FROM - The From Date and Time (as a TimeStamp string)
    • The default for FROM is 1 day ago (24 hours earlier than now)
  • TO - The TO Date and Time (as a TimeStamp string)
    • The default for TO is the current timestamp
For example: RUNiQRY *HIST SETVAR( (SEV 30) )
This produces a list of messages with Severity 30 or greater that were generated over the last 24 hours. It is a future objective to add additional selection options.
18-Nov-2017- The *USRPRF and *ALLUSR macros have been enhanced to include the last signed-on time. Previously the last signed-on date was listed. In addition, the 2nd column has been expanded to 3-characters and now more accurately represents its value. For a user whose inital menu is *SIGNOFF the value "S/O" is shown, while Disabled User Profiles show a value of "DIS". Other, normal/active user profiles continue to list blanks for this column. Remember, the difference between *USRPRF <-> *ALLUSR is that *USRPRF only lists user profiles that are not created by IBM (that is, all "user created" user profiles), plus, it includes the following IBM-supplied user profiles: QSECOFR, QPGMR, QSYSOPR, and QUSER. The *ALLUSR macro includes all user profiles.
11-Nov-2017- SQL iQuery version 4 release 5 is shipped.
- SQL iQuery for Web now uses the thread-safe C interface for stdout (standard out) instead of the non-thread-safe C++ output construct it was using. This means that very high traffic websites can continue to use SQL iQuery to generate output (both RAW and JSON) without it being corrupted by HTTP Web Server Threads being mixed up.
- A new PRINT directive is being introduced. This new iQuery command allows users to write additional data to the output (PRINT/*PDF or EXCEL) after the resulting dataset. For example:
print  after(2,'Hello World');
This would write the value Hello World to the report or excel file, after the regular output. The value is written left-justified and aligned with the 2nd column of the output. The first paramter is the column (relative field) used to align the output. NOTE that "column" refers to SQL terminology meaning the relative field and not the RPG term for "column" which means column 1,2,3 or position on the line. COLUMN in this context means, start printing by aligning with the Nth output field's column starting position. The valid parameter keyword is AFTER. The BEFORE keyword is planned for a future releease but it not shipped today. The AFTER keyword means to write the data after all the content and totals are written.
- Two new "datatype agnostic" built-in functions have been added. COMP() and iCOMP. The comp() and icomp() built-in functions compare two values for equality and return TRUE if they are equal. The two values can be different types. For example,
If (&a = &b);
could fail if &a contains text while &b contains only digits (numeric data). For example if &a='Hello', and &b=12345, then
if ('Hello' = 12345);
would fail with missmatched data types. The new comp() and icomp() built-in functions compare both values as text so the above example rewritten using comp() would be:
if comp(&a,&b);
This could convert to
if ('Hello' = '12345');
and comparison would succeed, with (obviously) a false result. You can also use the comp() and icomp() built-in functions to compare two text values while ignoring upper/lower case differents. For example:
if icomp(&name,'Rumpelstiltskin');
would convert the value stored in the &NAME Session Variable to lower case, and the 2nd parameter to all lower case and them compare them. In other words, icomp() is a case-insensitive compare statement.
01-Nov-2017- The OBJ_STRUCT() UDTF now returns the object's last used date, creation date, and current object owner as the 4th, 5th, and 6th columns of the query result set. These new columns have been addd to the *OBJSTRUCT macro and incorporated into the new IQRYOBJ command.
- The iQryOBJ (Query Object using iQuery) command has been formally released. This CL command lists the objects that make up other objects on the system. For *PGM objects, it lists the bound-in *MODULE and *SRVPGM objects, and similar results for *SRVPGM objects. Note the implementation of Last Used Date on OBJ_STRUCT passes thru to this helper CL command. To run it: iQRYOBJ OBJ(lib/obj*)
23-OCT-2017- A change to how numeric values are assigned to Session Variables has been implemented. Now, when a value contains leading zeros, the leading zeros are not removed when the value is not used in an expression. Specifically, numeric assignements are now copied verbatim to the Session Variable, unmodified. Previously they were passed through the expression analyzer, which "cleaned" them up. This change was implemented for users of SQL iQuery for Web who were seeing end-users typing in numeric values into a web form, such as a Zip Code value of 01010, or the last 4-digits of a phone number as a separate input field, e.g., 0123 and the iQuery expression analyzer would strip off the leading zeros. This no longer happens as of this build.
- The existing IFSDIR UDTF now returns the file name extension as a separate column. It extracts the suffix/extension by scanning for the last period in the IFS file name. Then copies the data to the right of that period to the new ENTEXT (ENTry EXTension) column.
16-OCT-2017- The Excel Output routine was not generating a valid Excel TIME result. With this release of SQL iQuery, TIME output is now generated according to the EXCEL specification. When SQL statements produce a TIME result or the column is a TIME data-type, then SQL iQuery inserts the year as 1899-12-31Thh:mm:ss in front of the time value. When Excel displays this value, that date value triggers it to display just the Time portion of the the value--hiding the "date" component from the end-user.
02-OCT-2017- A new directive, #NUMEDIT has been added. This allows users of iQuery Script to cause the Interactive or printed output for numeric columns to contain edit. Edits are limited to thousands notation (commas). The syntax for #NUMEDIT is:
#numedit costs,sales,profit,3,6;
Where COSTS, SALES and PROFIT are column (field) names, and 3 and 6 are relative column numbers. Use relative column numbers when column names are obscure such as SUM(PRICE) or dec( sellPrice - Cost, 11,2) as "Profit".
- A new directive #ROWCOUNTMSG has been added. Use this to turn off the "xxx records listed" message that appears at the bottom of printed queries OUTPUT(*PRINT). #rowcountmsg off -- turns off the printing of the "xxx records listed" message.
27-SEP-2017- An SQL iQuery LOG feature has been added. If the table IQUERY_LOG (iQuery Log) exists on the library list or in the IQUERY library, a row is added to it each time an SQL statement is run using the RUNiQRY or RUNiQRYF commands. The row (records) are tagged with the user, job, and date/time stamp of when the statement was run. The first 2048 characters are saved, beyond that is truncated. Users can query the log file by directly querying it or using the special built-in macro *LOG which displays the data in descending sequence by date. There is an option for the USER parameter to be passed in via the standard SETVAR parameter. e.g., SETVAR((USER BOB))
25-SEP-2017- New iQuery Script built-in function "TRIML" (Trim Length) that trims off the trailing characters from the input value. There are 2 versions:
where n is the number of characters to delete from the end (right side) of the variable. Effectively shortening it.
trim(&variable [, character(s)-to-delete ] );
where the character(s)-to-delete parameter identifies the characters to be removed from the end (right side) of the variable. If no characters are specified (i.e., only 1 parameter is specified) then trailing blanks are removed from the variable.
18-SEP-2017- SQL iQuery v4r3 had a small issue with the SQL CLI environment. This has been fixed.
15-SEP-2017- SQL iQuery v4r3 officially released and is available for download.
- It is a recommended upgrade for all SQL iQuery customers.
01-SEP-2017- SQL iQuery v4r3 officially Announced. Planned availability is 15 Sept 2017.
- Added the ability to specify "escaped characters" in built-in function parameters. Now users may specify (for example) foo('Bob's big house') as foo('Bob\'s big house') and the embedded quote is recognized appropriately. This works for all symbols, however, we continue to support the RPG-style of "escaping" which is doubling up embedded quotes, as in foo('Bob''s big house').
- A new directive is introduced: RESULTMSG ON|OFF|NONSELECT;
This directive turns off resulting messages from SQL statements except the final one. For example, if an inline UPDATE or MERGE statement results in the message "xx Rows Updated" users may turn it off by issuing "RESULTMSG NONE". or "RESULTMSG OFF". The default status is "RESULTMSG NOSELECT" which means only messages from non-SELECT statements are logged. Synonyms: ON|TRUE|ALL and OFF|FALSE|NONE|NO and NONSELECT|NOSELECT.
29-AUG-2017- The "WITH" statement joins the SELECT, VALUES and FOREACH statements in supporting the INTO clause. Common Table Expressions (CTEs) may now result in an INTO clause to return the data into Session Variables. Previously CTEs were only supported with non-READ statements (e.g., INSERT) or as the final SELECT statement in iQuery. With this update, the WITH CTE statement has the full support of iQuery.
24-AUG-2017- New examples in our sample database. Visit our samples page for more info.
- New (additional) syntax for conditional statements. Conditional statements and their corresponding ENDxx statements may now be specified as two words instead of one. For example: "endif" and "end if" are both supported. This proivdes a level of compatability with standard SQL/PL, while keeping the RPG-like names as well. The new statements include:
  • for each - FOREACH
  • do while - WHILE
  • end do - ENDDO
  • end for - ENDFOR
  • end if - ENDIF
  • end while - ENDWHILE
  • else if - ELSEIF
- These new conditions are in addition to the already existing conditional statements provided in SQL iQuery Language Script (iQL)
- Here is an example script using FOR EACH, END FOR, and IF/ENDIF in the same script.
 #define &count = 0  

for each SELECT CUSNUM,LSTNAM,BALDUE INTO &cust,&name,&due
FROM QIWS/QCUSTCDT where baldue > 0;
eval &count = &count + 1;
#msg &count. &cust,&name,&due
if isDDE(&cust);
#msg The customer number has a decimal data error (DDE)
if &COUNT >=5; -- Breaks after &count = 5
elseif &count <=5;
#msg Good &count < 5

end for; // end for and endfor now work!! :)
#statusmsg All done. &count iterations.
16-AUG-2017- A new set of examples is included in our QDEMOSRC source file in the IQUERY library. The source for 3 user-defined functions is now available in the UDFDEMO source member. By running that source member using the IBM RUNSQLSTM command, you will created 3 UDFs: OSVER(), OSVRM() and SYSINFO(). See the source file member for more information.
14-AUG-2017- A new iQuery Script directive has been implemented: #DFTOUTPUT xxxx that directs the output to the designated device (PRINT, PDF, EXCEL, etc.) if no OUTPUT() parameter has been specified on the RUNiQRY command.
- A new default value for the OUTPUT parameter of the RUNiQRY command is implemented. The new default is OUTPUT(*DFT) which work identical to the prevous OUTPUT(*) except when using the new #DFTOUTPUT directive in iQuery Script. If the new #DFTOUTPUT directive is used, its value is used as the output device unless the OUTPUT parameter is specified on the RUNiQRY command.
12-AUG-2017- Excel and Stream File enhancements and bug corrections.
- New EXCEL sheetName('July Sales') directive added to iQuery Script (iQL).
- New #STMFNAME 'JulySales.xls' directive added to iQuery Script.
- New #STMFPATH '/home/sales' directive added to iQuery Script.
- Excel Sheet names are now limited to the Microsoft Excel standard.
- FOREACH statements are now processed like an SQL statement. This means that embedded Session Variables (not those on the INTO clause) are not auto-quoted. It is up to the user/developer to enclose the session variable itself in quotes, if needed, as you would with an inline SQL statement. All other conditional statements continue to use the auto-quote feature.
23-JUL-2017- SQL iQuery V4R2M0 is announced.
- Planned availability is 2 AUG 2017.
- Complete redesign of the way remote system connectivity is handled.
- Excel output (SpreadSheetML) now automatically generates a "Freeze Pane" over the first two rows the the spreadsheet. This means the data below the column headings will scroll (similar to Subfiles) while the column headings themselves will remain in place.
8-JUN-2017- Enhancements to the CL: directive. Now when Session Variables are used and they contain embedded quotes, such as "Where's the beef" the iQuery engine will double up those quotes so commands such as the following work properly.
#define &myParmData = 'Where's the Beef?'
CL: CALL pgm(pgmA) parm(&myParmData);
If the quotes are already doubled-up by the developer, iQuery does not double them.
15-JUN-2017- The QRYOBj (Query Object Structure using iQuery) command has been updated. The new WHERE parameters allows users to customize the resultset by specifying a standard SQL WHERE clause for selecting the results. For example, to include all rows with a referenced object name of "COZTOOLS", users would specify:
- A new UDTF is being introduced: DATEDUR(). This function returns a single-row resultSet (table) that contains the difference between two date values. The date values may be of type DATE, TIME, or TIMESTAMP or any combination. To use the UDTF,
Select * from table( datedur(current_date, '1988-06-21') ) dur
This returns the Years, months, days between the 2 dates. Two timestamp values may also be specified to do things like Payroll time clock calculations (to get the number of hours and minutes between two timestamps). The DATEDUR UDTF supports two parameters, DATEDUR(d1,d2) where d1 is the more recent date/time/timestamp value, and d2 is the older value. If the older value is specified as the first parameter, the resulting column values will be negative. The columns returns are named YEARS, MONTHS, DAYS, HOURS, MINUTES, and SECONDS. There is also a MSECONDS column which is currently returned as NULL.
09-JUN-2017- A new *MBRNAME special value has been introduced. This value is replaced with the name of the Source File Member that contains the SQL iQuery (iQL) script. If an IFS file contains the script, the name of the IFS file is used, but no the file's path.
- Special Values are no longer scanned/replaced on the final SQL statement. This allows users to specify things like *YMD or *CURLIB in SQL statements without them being translated by the iQuery processor. If you want these values to be inserted into the final SQL statement, simply move them into a Session Variable and then use that session variable in the final SQL statement. Special Values are still replaced everywhere else.
25-MAY-2017- Add SPLFNAME(*JOB) and USRDTA(*JOB) options to the RUNiQRY command. The *JOB option causes the JOB NAME to be used as the SPOOL FILE NAME or USER DATA attribute. Note: This is/was a specific request for a specific customer, but is being released to the iQuery user community via this refresh.
22-MAY-2017- iQL v1.0 is released. Corrects some issues with a nested "elseif" statement.
- Various fixes and enhancements.
- Concat via += operator now works for text values. For numerics += is an incrementor.
02-MAY-2017- iQL v0.9a is released. Corrects some issues with "Is Defined" vs "IsEmpty". Now, the isEmpty() function returns true if the variable is not defined or when it is empty (contains no value).
25-APR-2017- Release candidate 2 for SQL iQuery v4.1 is now available for download.
- Changed how the SQL Environment is allocated in order to better support multithreaded environments such as the Apache HTTP Server, or batch jobs that use SQL iQuery where the SQL Server Job is automatically started.
- See the fix log for details (link is above) on fixes included in this build.
- The GETJOBDATE() UDF now returns the job date in job date format. The returned value is a numeric value for use with comparisons to legacy 6-digit dates. Use the optional first parameter to specify an alternate format for the date using traditional YMD, DMY, ISO, etc. format codes. Note: GetJobDate() returns the job's date in numeric format for use with legacy databases.
10-APR-2017- Added support for DDE() and NULL() built-in functions. DDE(&var) returns true if the variable had a decimal data error during an SELECT INTO statement. If the variable was not created or updated via a SELECT INTO, DDE will return false. NULL(&var) returns true if the variable's returned value from a SELECT INTO was NULL. If the variable was not created or updated via a SELECT INTO (or VALUES INTO) statement, then it returns false.
04-APR-2017- Release Candidate for SQL iQuery v4.1 now available.
- Corrected an issue with IFNOTEXIST statement
- Compiled for IBM i v7.1 or later.
- v4.1 includes iQL Scripting Language 0.8a
- Added "FOR &x = 1 to 10 by 1;" loop statement support to iQL.
02-APR-2017- IF/ELSEIF/ELSE/ENDIF statements are now implemented fully.
- Nested IF/WHILE/FOREACH statements are now implemented.
- The FOR (similar to DO and FOR in RPG) is included but is dissabled for now. It should be implemented in the next few weeks. (planned for May 1 deployment).
- iQuery Script is being rebranded as the iQL Programming Language.
- iQL - SQL for i Language for Query
- Pronounced "Silk" like the fabric.
- iQL objective is to provide the capabilityes of most IBM i HLLs. without support for DDS Display Files. All Database I/O is via SQL only.
- Web and IFS support is already implemented.
01-MAR-2017- SQL iQuery V4R1 GA
- Nested conditional statements are now supported.
- Compound conditional statements are now supported.
- Arrays are now formally supported.
- Many new built-in function are being introduced.
- Faster JSON transformation from DB2.
- New JSON output customization options are being introduced.
26-DEC-2016- SQL iQuery V4R1 release candidate 2
  • New WHILE loop control for "Do While" capability. Also ENDWHILE.
    • #declare &arr = 'Bob Cozzi';
    • eval(a) &arr = 'Lena Cozzi';
    • eval(a) &arr = 'Harold Konie';
    • eval(a) &arr = 'Danny O'Gara';
    • eval &count = elems(&arr);
    • eval &x = 0;
    • while &x < &count;
    • eval &option = &arr[&x+1];
    • eval &x += 1;
    • #msg Arr[&x] = &option
    • endwhile;
  • Session Variable Arrays are being introduced with the following characteristics:
    • Array notation uses left and right square brackets [ and ]
    • Arrays use 1's based notation.
    • To reference an array element, arr[x] may be used, where x symbolizes the array index.
    • If an array exists but no index is spefied, the so called "current" index is used.
    • The new setindex command may be used to set the "current" index similar to how Multiple Occurrance Data Structures OCCURS opcode is used.
    • No practical upper limit to array indexes.
    • Array elements are auto-allocated and released.
    • To use a Session Variable as an array, simply add the array brackets
    • All session variables are technically arrays.
    • If a session variable contains 1 value (as is normal) then:
      • eval &var = 'Bob';
      • #msg VAR=&var and VAR[1]=&var[1]
      • ...produce the same result.VAR=Bob and VAR[1]=Bob
    • To assign a value: eval &arr[3] = 'HelloWorld';
    • To retrieve an array's value: eval &myvar = &arr[3];
    • The EVAL opcode has been enhanced to support array "auto-add" feature.
      • EVAL(a) &arr = 'Hello World'
    • The "A" operation extender has been added to copy the value to the next available array element.
    • This automatically adds "Hello World" to the end of the &arr array or to the first array element if the array is undefined.
  • iQuery for Web now supports multiple form field values
    • When an HTML Form contains the same field multiple times, those values are now copied to Session Variable Arrays
    • Access to CGI variables is, as always, through their session variable.
      • For example:
      • <input name="OPTION"...> is normally mapped to session variable: &OPTION
      • When the OPTION input value is replicated multiple times, it is referenced via iQuery array notation, as &OPTION[&x] for example:
        • eval &x = 0;
        • eval &count = elems(&OPTION);
        • WHILE &X < &COUNT;
        • eval &opt = &OPTION[&x+1];
        • ... do something with &OPT or the array element
        • eval &x += 1;
        • endwhile;
  • New Built-in functions have been added
    • toupper() - Returns the value of its parm in all upper case
    • tolower() - Returns the value of its parm in all lower case
    • tohex() - Returns the value of its parm in hexadecimal notation
12-DEC-2016- SQL iQuery V4R1 release candidate 1
  • iQuery for web is now thread-safe
  • New #HTTP_CONTENTTYPE command to set CONTENT-TYPE value for the web
  • New BLING command to assign Session Variable Symbol(s).
  • Dataset name in JSON output can be set to *NONE to promote results one level
  • JSON column/field names controls have been added
  • JSON column/field attributes generation is now controllable
  • iQuery for web now supports << directive to write directly to browser
  • #WATCH command allows users to watch a Session variable each time it changes. The #UNWATCH or #ENDWATCH commands terminate the watch
  • EVAL/CHGVAR commands are now smarter when mixing text and numbers.
  • Built-in functions may now be nested as parms of other built-in functions
  • Introduced several new built-in functions
    • getfile() - Read IFS text file into a Session Variable
    • firstOf() - Find first of a list of characters
    • firstNotOf() - Find first char not of list of characters
    • getcookie() - Read HTTP Cookie value
    • msgid() - Get message text using MSG ID
    • tempname()/tmpname() - Create a temporary IFS file and return its name
    • strlen()/len() - Return length of contents of Session Variable
    • isWeb() - Returns TRUE if running from the HTTP server
  • Introduced several new commands
    • writestmf - Write content of Session Variable to IFS text file adding the data to the end of the file
    • savestmf - Write content of Session Variable to IFS text file, replacing any existing data in the file
    • setcookie - Create an HTTP cookie that is sent to the browser
    • coltotals - Identify the columns to be auto-totalled when creating excel, print, csv, SyLK and some other output formats
    • lvlbrk/levelbreak - Identify columns to output only when their value changes
    • cout/stdout - Send data to the standard output device
    • CL: is now a hybrid command. It allows multiple lines for the CL command, which must now be terminated with a semicolon. This is for compatibility with the way the legacy RUNSQLSTM command handles the "CL:" statement.
    • FTP - Users may now generate FTP scripts using iQuery Script as a scripting tool. See documentation for details of options and features
02-NOV-2016- Added new #HTTP_OUTFILE directives to send a text file from the IFS or a source member to the web browser (no addition processing on the file or member is done). The new directives are:
  • #HTTP_OUTFILE - sends the file immediately
  • #HTTP_OUTFILEB - sends the file just before the dataset is sent
  • #HTTP_OUTFILEA - sends the file after the dataset is sent
For example:
#HTTP_OUTFILE  mylib/qhtmlsrc(errormsg)
This sends the contents of the ERRORMSG source member in QHTMLSRC to the browser. When source members are specified, be sure to include the file name and then specify the member name in parens or the member may not be located.
To send a text file from the IFS, use IFS syntax, for example:
#HTTP_OUTFILE  /www/mywebsite/docs/errormsg.html
When IFS files are specified, only fully qualified file names should be used or the location of the file may not be correct.
20-OCT-2016- Our popular CVTDATE UDF has been completely rewritten in C for performance reasons. Originially it was created to provide a simple way to convert legacy fields that contain date information into a true Db2 SQL DATE data-type value. However what we've found is that not only are users using it for the intended purpose, but also to create JOIN conditions between legacy database files and newer Db2 SQL Tables that contain date columns, or to ORDER BY (i.e., sort) the result set. This meant that performance of the function was increasingly important. However it was written in pure IBM Db2 SQL/PL. This new version is written in C and is an order of magnitude faster. In addition, the UDF's have been customized for V7R1 and V7R2+. This means all customers should see an immediate performance improvement by installing this release of SQL iQuery. One enhanced feature in CVTDATE is support for Julian dates, e.g., cvtdate(16250,'JUL') and cvtdate(2016260,'longjul') In addition CVTDATE now uses the input date's type and length to determine whether or not there are embedded separators in the original non-date input value. whether or not it might contain embedded edit symbols. For example, the following is now handled automatically. For example:
 VALUES iquery.cvtdate('06/21/88','mdy');
VALUES iquery.cvtdate('062188','mdy');
When the input date format is something like MDY, the length of the non-date value may be 6 or 8 characters. If the value is character and contains 8 characters, then CVTDATE assumes there are embedded separators, if it is 6 or fewer characters it assums it does not have separators. This logic is adjusted for each data format and length. Therefore ISO/USA/EUR/JIS formats, when the date value is 8 no separates are expected, when the length is 10 then a separator is assumed. NOTE: if the date separator is something other than the default separator for the date format, then a full day pattern must be used. That is if the date separator for MDY is a dash instead of a forward slash, then cvtdate( mydate, 'MM-DD-YY') must be specified instead of cvtdate( mydate, 'mdy').
14-OCT-2016- Several enhancements are included in this release, including:
- Added iq_SRCFILE, iq_SRCLIB environment variables to set values in the HTTP config file using the HTTP Configuration SetEnv directives. This forces all queries that use an iQuery Script source member to be directed to the specific source file and library--preventing access to any other source file from the SQL iQuery web engine.
- New iq_ALLOWSQL environment variable (default = false) controls whether to allow full SQL statements to be passed to the iQuery engine or to restrict processing to user-supplied SQL iQuery Source File Members. When this option is true, any SQL statement may be passed to SQL iQuery for the Web via the CGI/WEB interface and is run (based on user authority). When iq_ALLOWSQL is false (the default) only SQL iQuery scripts may be processed. To enable this setting so that SQL statements may be run, add the following to your HTTPD.conf file for your HTTP server:
SetEnv iq_ALLOWSQL true
The SetEnv Apache HTTP directive sets an environment variable that toggles the iq_ALLOWSQL setting. The options are true and false. When not specified, it defaults to false and dynamic SQL statements are not processed by the SQL iQuery Web engine.
- iQuery Script now supports "raw" output for CGI/SSI Http instances. When iQuery is used as a CGI script (by calling WEBIQRY as a CGI or SSI app) the script normally defaults to Content-Type: text/html, and wraps the resulset in HTML. When the new
#httpoutfmt raw
tag is specified, the result set is sent in the format it is retrieved. This allows users to wrap the resulset columns in HTML tags or other wrappers, and have it sent unchanged to the server. There is also a new
command that can be used to send anything directly to the browser. For example, to send an image reference, the following could be used:
httpout <img src="images/logos.png" width="60px" />;
httpout statements are cached, and sent all at once just before the final SQL statement in the iQuery script is performed. If users wish to send content after the final SQL statement is run, the httpoutAfter command may be used.
- There is a new stdout command that works similar to httpout but sends the content to the so called standard output immediately. If the iQuery script is run interactively, you'll see the session window appear. If run from the web, it immediately sends the content to the browser.
stdout << 'this works like a "live" joblog.';
The << symbols are a C++ convention and are optional. With or without them it works the same. Users who use stdout for debug purposes should consider using the #sleep directive immediately after the stdout statement. By default the stdout session window closes immediately. Using #sleep 5 pauses the iQuery Script for 5 seconds to give you time to review the output.
04-OCT-2016- iQuery Script now supports email clients SENDMAIL (from Cozzi Productions, Inc.) and the IBM-supplied SNDSMTPEMM CL command, supplied with IBM i in a TR during v7.1 To specify which one you would like to use, go into the CONFIG.XML file and change the tag's value to the index of the command you want to use. Specify 1 for SENDMAIL and 2 for SNDSMTPEMM. The config.xml file is located on the IFS in the /home/iquery/config folder. As mentioned, SNDSMTPEMM comes with IBM i. The SENDMAIL command is a Java sendmail-based email client. It is provided at no additional cost to our customers who own one of our products, such as SQL iQuery or Cozzi Tools. Trial-use customers should use the IBM command. To enroll IBM i User profiles so they may use SNDSMTPEMM, use the IBM-supplied WRKNAMSMTP (work with SMTP names) CL command to add users.
04-OCT-2016- iQuery Script now supports embedding email attributes. Rather than specifying the email Subject and recipients on the RUNiQRY command, you may now specify that information in your script source code. For example:
email to(;
This adds the email address to the list of recipients. There are also CC, BCC, SUBJECT, and TEXT/BODY as parameters to set the corresponding values. For example:
email to(;
email subject('Quarterly Sales Report');
email body('Attached please find the quarterly sales report excel file.');
select * from qiws.qcustcdt;
- The EMAIL TONAME parameter allows recipient names. Use it in place of the TO() parameter when you want to insert the receipant's name along with thier email address.
email toName('Bob Cozzi, Jr.',;
- This assigns the email name/identifier 'Bob Cozzi, Jr.' to the email address specified on the 2nd parameter. There are also CCNAME() and BCCNAME parameters however each must be on a separate email command line, for example:
email toName('Bob Cozzi, Jr.',;
email ccname('Mr. President',');
01-OCT-2016- iQuery Script now supports column totals. In addition to the COLTOTAL parameter on the RUNiQRY command, iQuery Script users may now specify column total identifiers within the iQuery Script source member. The new COLTOTAL (or COLTOTALS) keyword accepts one or more column names or numbers that are added to any existing column totals from the command. Multiple column identifiers may be specified per keyword, separated by commas. For example:
coltotals 7,8,9;
This indicates that the 7th, 8th, and 9th columns should be totalled.
coltotals  baldue,sales,*last;
coltotals amtsold,qtysold;
This indicates that the columns named BALDUE and SALES are totalled, in addition, the right-most column in the resultSet (*LAST) is also totalled. Currently this feature works with OUTPUT(*EXCEL) only, but is being rolled out for the other formats *PRINT and *PDF in the next few weeks.
26-SEP-2016- iQuery Excel now supports user-specified colors and other attributes such as fonts. To set the attributes use the new iQuery Script EXCEL command with one of the following 3 parameters and attributes:
  • excel column( column-number, attr, value );
  • excel header( attr, value );
  • excel totals( attr, value );
For example:
excel column(1,color, #dd0000); -- Set Column A (1st col) Text color to red
excel totals(bgcolor, yellow); -- set the Total row background color to yellow
excel column(4,bgcolor, red); -- Set Column D (column 4) background color to red
excel header(font, Arial);
excel header(fontsize,20);
12-SEP-2016- Excel File output now support "Report Titles" that is, the APPTITLE, USRTITLE, and RPTTITLE parameters are now included when generating OUTPUT(*EXCEL) content. Likewise, when using iQuery Script, #H1 to #H4 are now included in OUTPUT(*EXCEL).
09-SEP-2016- Notes about quotes. In iQuery, when a value is assigned to a session variable, the assignment statement may include quotes. If quotes are detected, the value is implicitly text-based and no expression parsing/processing is performed. If the value is not quoted, the content of the value is inspected and a decision on whether it is text, a numeric value, or numeric expression is made. If only numbers and arithmetic symbols are detected, then the value is processed by the iQuery expression analyser and the result is assigned to the target Session Variable.
The following two statements produce identical results.
#define &company = 'Cozzi Productions, Inc.'
#define &company = Cozzi Productions, Inc.
The following two statements are considered numeric:
#eval &amount = 100.00 
#eval &amount = 100.00 * 5
But this statement is text:
#eval &amount = '100.00 * 1.5'
In order to insure a value is text and not an expression, users may include quotes in the assigned value (on the rightside of the equals sign.) When this occurs, the value is always processed as text.
- Quotes are not, however preserved with the session variable itself. This allows users to assign, for example, a different math expression to a session variable and use that expresion in the SQL statement. for example:
FOREACH select custnbr,slsamt,state 
INTO &cstnbr,&sales,&state
#if (&state = 'IL')
#eval &tax = '&sales * 0.75'
#eval &tax = '&sales * 0.5'
- The generated UPDATE statement would look something like this:
- If later on, you wanted to produce the actual result of the &TAX session variable's content, you could assign it to another variable, or even to itself:
#eval &tax = &tax
This would cause the iQuery scripting tool to insert the value of &TAX into the statement, then during the assignment of &TAX's content to the &TAX variable, the right-side would be determined to be number (numeric expression actually) and it will be processed and the product of the expression replaces the session variaable on the right-side of the statement. Then that value is copied/assigned to the &TAX session varible, replacing its original texual value.
01-SEP-2016- Misc Bug fixes and enhancements.
26-AUG-2016- New operators += and -= are now supported in iQuery Script for assignments. For numerics the rValue is added or subtracted from the existing lValue and stored in the lValue. For Text, the values are concatenated together and stored in the lValue.
eval &total += &sales;
- A new *BCAT operator >= is supported for character string concatenation.
eval &valA = 'Bob';
eval &valA >= 'Cozzi';
#msg VALA = &vala
Output: VALA = Bob Cozzi
Remember, "*BCAT" is used descriptively here, to describe the >= operator. The string "*BCAT" is not recognized by iQuery.
18-AUG-2016- New LIB_LIST() UDTF that is similar to OBJ_LIST and OBJECT_STATISTICS except it lists library names and has only one parameter which is the generic library name (generic, full, *ALL, *ALLUSR, or *IBM) for the libraries to be listed.
- PUTENV is now an inline command. In addition to the #putenv command, users may now use putenv and putsysenv with a terminating semicolon to set environment variable values. We've also added dltsysenv, as dltenv was already supported. Fundementally this means that the following two lines are equivalent:
#putenv iq_OUTPUT=*PRINT
putenv iq_OUTPUT=*PRINT;
12-AUG-2016- Version 3.2a is formally released today.
12-AUG-2016- IBM issued two new PTFs for iQuery to support UTF-8 data conversion correctly.
  1. V7R1 PTF
    • SI61475 & SI61887
  2. V7R2 PTF
    • SI61129 & SI61155
  3. V7R3 PTF
    • SI62005 & SI62028
12-AUG-2016- New CSV UDF and UDTF functions are introduced. Users can now "parse" CSV files stored on the IFS, directly with SQL. The new CSV functions include a UDTF and several UDFs.
  • select * from table( CSV( <ifs-file-name> [ , 1 | 0 ] ) ) c
  • CSV_VAL( variable-with-CSV-data , relative-column-number )
  • CSV_VAL( variable-with-CSV-data , column-name )
  • CSV_INT( variable-with-CSV-data , n )
  • CSV_INT( variable-with-CSV-data , column-name )
  • CSV_DEC( variable-with-CSV-data , n )
  • CSV_DEC( variable-with-CSV-data , column-name )
  • CSV_BIGDEC( variable-with-CSV-data , n )
  • CSV_BIGDEC( variable-with-CSV-data , column-name )
select csv_int( data, 1) as customer,csv_val(data,'lastname') as "Last Name", 
csv_val(data,'state') as "State",
csv_dec( data, 'balancedue') as "Balance Due"
FROM Table( csv('/home/cozzi/qcustcdt.csv') ) c
- CSV( <ifs-file-name> [ , 1 | 0 ] ) CSV is a UDTF that will read a CSV file and return its contents as a text string. The data is returned in the one-and-only DATA column varchar(8192). The data itself is just plain text but the first row is expected to contain the column names. If no column names/titles exist in the CSV file, then the 2nd parameter of CSV should be specified as zero 0 and CSV_xxx may only refer to the relative column numbers and not the column names/titles.
- CSV_VAL( csv-data, column_ID ) Get CSV column data. The CSV_VAL UDF returns the data associated with the identified column ID. The COLUMN_ID parameter my be the relative column number (1 through n where n = the number of columns) or it may contain the column name as identified by the first row of the CSV file. If no column name/title row is included in the CSV file, the relative column number is the only valid value for the 2nd parameter. Data is returned as a varchar(2048) field.
- CSV_INT( csv-data, column_ID ) Get CSV column data as an integer. The CSV_INT returns the CSV column's data as an integer value.
- CSV_DECcsv-data, column_ID ) Get CSV column data as a decfloat(34) value.
- CSV_BIGDEC( csv-data, column_ID ) Get CSV column data as a decimal(63,15) value. Use this value when more than 34 digits exist in the value.
- Column Name parameter: The column name must match the text in the first row of the CSV file. If no colum name/text row exists, users must use the ordinal method for the CSV_xxx() UDFs. When a column name is specified, all embedded blanks are removed and the entire name is converted to lower case. This provides for more accurate and case-insensitive column name lookups.
12-AUG-2016- Native Excel .XLS files now generate right-adjusted headers for numeric columns. In addition, column headings are now word-wrapped to provide multiple line column headings.
27-JUL-2016- Native Excel .XLS files are now produced when OUTPUT(*EXCEL) is specified. Previously iQuery created "Symbolic Link" or "Sylk" files which are the original Excel and Microsoft MultiPlan file format. With today's release, larger spreadsheets can be created since .XLS is XML-based. Column Totals via the COLTOTAL() parameter are supported. It is a future objective to support the .XLSX Excel format. User who wish to continue to produce Sylk (.SLK) files, may do so by specifying either OUTPUT(*SLK) or OUTPUT(*SYLK) but OUTPUT(*EXCEL) now produces .XLS files.
22-JUL-2016- The MCHINFO() UDTF now includes the Processor Group in the resultset. This means it now returns the Machine Type, Model Number, Processor Group, Feature Code and Serial.
- A new UDF is being introduced. FROMHEX(...) converts each 2-character value to 1-byte characters. DB2 SQL already provides the HEX(...) UDF, this is the complement of that.
- A new Stored procedure: Rcopy (or ifsRcopy) is being introduced. It allow users to send IFS stream files to a remote partition. For example:
call iquery.rcopy('/home/cozzi/*.txt','CHICAGO','REPLACE');
12-JUL-2016- New BREAK/LEAVE directives are being introduced for the FOREACH loop. When conditioned with an IF statement, they can be used to exit a FOREACH loop before EOF occurs.
02-JUL-2016- The FOREACH ... ENDFOR feature now works in both IFS text files and source members. We still don't have a good method of existing the foreach loop prematurely. But we are working on it and it should be available later this month. For now, enjoy the FOREACH loop capability. It really does add a whole new dimension to iQuery.
28-JUN-2016- The LVLBRK (Level Break) parameter is know accepted when OUTPUT(*) is specified. Previously it was recognized only when OUTPUT(*PRINT | *PDF) was specified.
- The new FOREACH command is being introduced. This command allows SQL iQuery Scripts to loop, processing a set of SQL statements. Users specify a SELECT statement along with the INTO clause to read through each row of a resultset. Processing continues to the ENDFOR statement until EOF is detected. It is a future objective to introduce a method to exit the FOREACH loop prematurely.
- New MCHINFO() UDTF returns information about your IBM i Power system. The return columns include Feature Code, Model, Serial Number and Machine Type.
20-JUN-2016- To provide a similar experience to RPG, we've created a "defined(xxx)" built-in that may be used in place of the #ifdef conditional statement. Now users may write either
#if defined(&var)
#ifdef &var
and achieve similar results. To further support this, #if defined(*v7r2m0) and other releases are supported as figurative constants.
17-JUN-2016- Built-in function nesting is now supported. Prevously, when a built-in function was used, such as #if scan('REGION',&var) > 0; the built-in did not support nested built-ins. Today a built-in may be specified as the parameter of another built-in function. For example: #if scan('REGION',dtaara(*LDA)) > 0; The inner-function are, obviously processed first, and the outer functions later.
14-JUN-2016- The VALUES INTO statement now supports multiple columns.
 VALUES current_date,current_time,current_user into &date, &time, &user;
- The #ELSE and #ENDIF statements may now be specified as else; and endif; respectively. The original syntax continues to be supported.
- The #IF statement now supports blank values and empty values as equal entities. Previously, " " and "" were different to the #IF statement. Now they are considered equal values which was the intent from the begining. This is more of a bug fix but is also documented here as a feature change.
- Two new built-in functions are introduced today.
  • scan( pattern, data [, start])
  • scani( pattern, data [, start])
  • The scan functions search the data for the pattern starting in the start position or from the beginning if no start position is specified. The location of the pattern is returned; if the pattern is not found then they return 0. scan is a case-sensitive scan, wherease scani is case-insensitive.
06-JUN-2016- Added support for CONNECT TO and CONNECT RESET to the iQuery Scripting tool. iQuery Script users may now specify, for example:
connect to chicago user COZZI using 'ROSEBUD';
connects to the RDB named CHICAGO. To connect to another system, run another CONNECT To statement. To reconnect to your local system, run the RESET option:
as your final statement or you will continue to be connected to the remote. However a subsequent RUNIQRY command will force a new connection anyway, but it's good practice to tell the system what to do. Note: This feature should not replace the 3-level naming normally used in database access with SQL, e.g.,
select * from chicago.mydata.customer
01-JUN-2016- The ifsDir() UDTF now has an optional 2nd parameter for Recursive control. When 1 is specified, the directory specified on the first parameter is recursively scanned for subdirectories; those subdirectories are included in the result set. A value of 0, NULL, or no 2nd parameter means no recursive scanning is performed (this is the default behaviour).
25-MAY-2016- New obj_list() and obj_dmg() UDTFs are being introduced. These functions produce a list of objects. obj_list() produces a list of all objects that match the selection criteria. obj_dmg() produces a list of damaged objects in the library specified (or *ALL libraries).
- OBJ_LIST() is similar to IBM's v7.2 OBJECT_STATISTICS UDTF but our OBJ_LIST() runs on IBM i v7.1 or later and includes a few additional object attributes such as damaged object indicator, Activation Group, and target release. Primarily OBJ_LIST() was created to provide a way to create a damaged objects listing, and work with v7.1 systems. To facility the damaged object listing, OBJ_DMG() UDTF is also being announced.
19-MAY-2016- #genenv and #getsysenv and #dltenv directives have been added. #putenv was implemented previously.
- #eval is now supported as a command or a directive. You no longer have to prefix it with #, but rather simply code it like a regular statement:
eval &MaxValue = 100 * &price;
The semicolon is required and the expression may span multiple lines. Basically it has syntax similar to our SQL statement interface.
- New commands crtstmf (create stream file on the IFS) and dltstmf (delete stream file on the IFS) are being introduced.
crtstmf /home/cozzi/log.txt,819;
This creates the file LOG.TXT in the /home/cozzi folder on the IFS and assigns it CCSID(819). If no CCSID is specified, it default to 1208.
dltstmf /home/cozzi/log.txt;
This deletes the file LOG.TXT in the /home/cozzi folder on the IFS.
- New inline functions are being added. In addition to SST(), we now support: dtaara (data area extraction), sysval (system value retrieval), usrspc (user space extraction), getenv (retrieve environment variable value) The main identifier (i.e., first parameter) for these built-ins supports optional quotes. So use whatever you're comfortable with. For example, the following syntax is interpreted the same: sysval(QDATFMT), sysval('QDATFMT'), and sysval("QDATFMT")
- dtaara( data-area-name [, start [,length]] ); This extract data from an existing data area using the optionally start/length parameters.
eval &CSTNBR = dtaara(*LDA,11,5);
- usrspc( user-space-name [, start [,length]] ); This extract data from an existing user space using the optionally start/length parameters.
- sysval( system-value [, start [,length]] ); This returns a System Value. It too includes optional start/length parameters. While these may be used for any system value, they are targetted to be used with the QUSRLIBL and other lengthy system values were users may need to extract a portion of the result.
eval &DATFMT = sysval(QDATFMT);
eval &SERIAL = sysval(QSRLNBR);
eval &FEAT   = sysval(QPRCFEAT);
eval &MODEL  = sysval(QMODEL);
- getenv( envrironment-variable [, start [,length]] ); This returns the value for the specified environment variable (if it exists) otherwise it returns an empty string. The optional start/length parameters allow the result to be substcripted before being assigned to the result.
eval &OUTPUT = getenv('iq_SPLFNAME'); // Case senstive!
17-MAY-2016- #rtvsysval - (Retrieve System Value) is now supported.
#rtvsysval &usrDateFmt = QDATFMT
- This copies the QDATFMT system value into the &USRDATEFMT Session Variable. It is the functional equivalent to:
eval &usrDateFmt = sysval(qdatfmt);
12-MAY-2016- Added *GRPPRF *CURUSR and *USRPRF to the list of figurative constants that may be used in iQuery Script. *GRPPRF and *USRPRF should be self-explained. *CURUSR is the user profile underwhich the job is running. Which may be different from the *USRPRF. If the User running the job has no group profile, then *GRPPRF is set to *NONE.
- *WEBUSR figurative constant returns the user ID assigned to the REMOTE_USER environment variable. Normally this environment variable is set by the HTTP Server when web Authentication is enabled and everything is setup correctly. *CURUSR and *WEBUSR often contain the same value.
11-MAY-2016- SQL iQuery Script now connects to the remote database (the RDB parameter of RUNIQRY) before launching SQL Scripting tool. Therefore all inline SQL statements, such as SELECT, VALUES, INSERT, UPDATE, DELETE are performed on the remote system. Prevously the user was required to issue the CONNECT TO statement in addition to specifying the RDB parameter. Now the RDB parameter if anything other than *LOCAL, is processed prior to launching the iQuery Scripting tool.
- Two new iQuery Scripting tool command have been added. #return (or return;) stops
- #return (alternate syntax is "return;" without the quotes)
- The #RETURN directive stops processing the SQL script and returns to iQuery. If there is any SQL statement built upto that point, it is returned and processed normally. Alternatively you may use #leave or leave; in place of #return.
- #exit (alternate syntax is "exit;" without the quotes)
- The #EXIT directive stops processing the SQL script and clears any existing statement buffer, and then returns to iQuery. Since the statement buffer is cleared, no further processing is done. Alternatively you may use #cancel or cancel; in place of #exit.
10-MAY-2016- A few fixes are included in today's build. In addition the following enhancements have been made at the request of our Customers.
- The iQuery Script #PRTDATFMT directive now controls the format of the date printed in the header of OUTPUT(*PRINT | *PDF) results. Previously *USA format was used. The new default, as of this build is *JOB and the options are: *ISO, *EUR, *JIS, *USA, *MDY, *YMD, *DMY and *JOB.
- The iQuery Script #PRTDATE directive now controls whether the Job date or System date are printed in the headers when OUTPUT(*PRINT | *PDF) are specified. The valid choices are: *JOB and *SYS
05-MAY-2016- Version 3.1 is formally released today.
- You may now do SELECT x,y,x INTO &var1,&var2,&var3 FROM myfile
from within the iQuery Scripting tool. This allows you to read one or more column values from a SQL statement into iQuery Session varaiables.
SELECT max(baldue), max(CDTDUE) INTO &bigDue,&bigCredit FROM QIWS/QCUSTCDT;
- The VALUES INTO statement is now suppported for iQuery Scripting where the INTO clause contains an iQuery Session Variable. (NOTE: VALUES only allows you to return a single value, which is why we also support the SELECT INTO stmt.)
values current_schema into &schema;
- The new RTVSQLVAR (Retieve SQL Global Variable) CL command is introduced. This command allows CL developers to retrieve the value of a previously created SQL variable into a CL variable.
27-Apr-2016- New iQuery Script directives are introduced.
  • #datfmt - Sets the date format for date results.
  • #dftrdbcol - Set the default library for unqualified names to the specified library name.
- A new iQuery Script Built-in function (the first) is being introduced.
- sst(value, start [, length ]) - Substring
- This built-infunction extracts the value of its argument (parameter) starting at the start location and continuing for the length characters (if specified) or to the end of the variable (if unspecified). Use this to retrieve or compare portions of Session Variables, figurative constants, dates, etc. Valid on the #define and #if directives only.
- For example, to test the first 3 positions of the system name for the letters 'COZ', you would use:
#IF sst(*SYSNAME,1,3) = 'COZ'
Currently, sst() is case-sensitive and must be specified in all lowercase letters, however this requirement may change in the future.
- New UDF: DTAARA(data-area [, start [, length ]])
- This function retrieves the contents of a data area into the user-written SQL statement. The optional start and length parameters are users to extract specific positions within the data area. The results are returned as a VARCHAR character string, even if the data Area is *DEC or *LGL. The special data areas as well as regular user-defined data area names (qualified or unqualified) may be specified. For example:
SELECT * from QCUSTCDT where CUSTNAME = dtaara('*LDA',51,30)
- New UDF: GETENV('environment-variable')
- This function retrieves the value of the designated environment variable. The results are returned as a VARCHAR character string.
SELECT * from QCUSTCDT where custnum = dec(envvar('LAST_CUST'),5,0)
- Changed the Location/Position column in WRKIQRY to include the "TO" position as well as the "FROM" position.
- New UDF: jobdate(['return-format'])
- This function retrieves the job date using the QUSRJOBI API and returns it as a numeric value in the format specified. The default format is YMD. To convert the job date into a true SQL date data-type, wrap the jobdate() function in our CVTDATE function as follows:
22-Apr-2016- Added new *DFTPWD macro. This macro runs a query to list user profiles that potentially have their original/default passwords. User profiles such as QSECOFR, QPGMR, QSYSOPR and even QUSER are often forgotten about and still have their original, widely-known passwords assigned to them. If they appear on this list their passwords may have never been changed since the date the OS was installed. e.g.,
runiqry *dftpwd
15-Apr-2016- Introducing Query Object Structure (QRYOBJ) command for iQuery. This new command is a wrapper for the OBJSTRUCT UDTF included in iQuery. QRYOBJ lists the contents of *PGM and/or *SRVPGM objects; listing the *MODULE and *SRVPGM names that make up the *PGM or *SRVPGM. I use this command everyday and wish I had it 20 years ago! Try it and let us know what you think. QRYOBJ is available with today's iQuery build.
12-Apr-2016- iQuery scripts no longer require #SQL for inline SQL statements. Previously, to embed an inline SQL statement, such as DROP or CREATE TABLE, The #SQL directive was required. This had several limitations. Today that requirement has been removed and inline SQL statements are properly detected and run. In addition, these statements may span multiple lines and may avoid the # directive requirement for \ (backslash) continuations.
- EMAIL, SUBJECT and EMAILOPT parameters have been added to RUNiQRY. Your email client may be configured in our new QXMLSRC file IQUERY member. The node allows users to insert their own email client command string. If you don't have one, we have a fee email client named SENDMAIL we can provide.
- Data area support has been added to iQuery scripts. The new #rtvdtaara directive reads the specified data area and stores it in the user-specified Session Variable. For example:
 #rtvdtaara &myvar = *LDA

Reads the contents of the *LDA and places it in the &MYVAR session variable. Any data area may be specified.
02-Apr-2016- Rebranding is complete. SQL Query File officially becomes SQL iQuery™ for IBM i.
- Licensed Program: 2COZ-IQ3
- Product Lirary: IQUERY
- Official General Availability Date: 05 May 2016
- Beta period 01 March 2016 through 30 April 2016
- New command names:
- RUNiQRY (replaces RUNSQLF)
- RUNiQRYF (replaces RUNQRYF)
- WRKiQRY (replaces WRKQRYF)
02-APR-2016- The #if directive is introduced along with #elseif This directives allow users to compare two values and include or perform a section of code based on the condition. For example:
 SELECT custno,custname,salestotal      
FROM quarterly.consolidatedSales
#if &SORTSEQ = D
#define &ORDERBY = DESC
#elseif &SORTSEQ = A
#define &ORDERBY = ASC
- In the above example, the Ascending/Descending keyword is set based on a user-specified value. This value could be passed in from a CL program or a web page.
29-Mar-2016- The #ifexists directive now supports long SQL file and schema names.
25-Mar-2016- New UDFs for ifsDelete and ifsCopy have been introduced.
- Both UDFs and Stored Procedures of the same names are included.
- ifsCopy - Copies an IFS file to another location or to another IFS file.
- ifsDelete - Deletes an IFS file.
- Use these along with the existing ifsDir UDTF to copy/delete IFS files using the power of SQL to select the IFS files to be deleted/copied.
- A new STMFHDR (Write Content-Type header to output stream file) has been added. Now when OUTPUT(*JSON or *HTML) is specified, the STMFHDR can be used to control whether or not to insert the standard HTTP mime-type header "Content-Type:" into the output file. The default is STMFHDR(*NO)
- SQL Query File now supports UTF-8 ccsid(1208) for output stream files sent to the IFS. Use the STMFCCSID parameter and specify either STMFCCSID(*UTF8) or STMFCCSID(1208).
01-Mar-2016- SQL Query File v2.4 has shipped. It is now available for download from our downloads page. Existing customer should download and install this version. New customers may download and restore the product with its built-in 60-day license grace period--no additional license key required until that 60-day grace period ends. After the 60 days a paid license is required. Existing customers should delete their current installed 2COZQF2 license, first, then install this new version. This normal steps to performt his upgrade are as follows:
dltlicpgm 2cozqf2 
rstlicpgm 2cozqf2 *savf savf(qgpl/cozqryf)
- See the website for more information on installation of the product.
22-Feb-2016- Bug fix log has been created. Now the daily change long (the one you are reading) shall contain any updates and enhancements and major bug fixes and release capabilities. However we've moved the list of bugs correctionsions and fixes to a new log to help reduce the size of this change log.
16-Feb-2016- SQL Query File v2.4 shipped on March 1, 2016
- New iQuery SQL Scripting tool is included "free" with SQL Query File.
- Two new macros are introduced to list Cozzi-related components on the system.
- *COZFUNC returns a list of all COZZI UDF and UDTF routines installed on the system.
- *COZXREF returns a list of all program and service programs that are bound to the COZTOOLS runtime library.
- Two new UDTF (user defined table functions) or as IBM calls them "SQL Services" have been introduced.
- OBJSTRUCT( object, library, objtype ) creates a list of the components that make up the *SRVPGM or *PGM. The list includes *SRVPGM and *MODULE objects.
- IFSDIR( folder ) returns a list of the files and folders contained in the folder.
- New preprocessor directives are introduced. These replace the previous control codes we introduced earlier this year during the 2.4 beta period.
- The new directives include but are not limited to:
  • #define - define a runtime substitution variable.
  • #default - define a runtime variable if it is not already defined.
  • #undef - undefine (delete) a runtime substitution variable.
  • #ifdef - conditional source based on a variable being defined and non-blank
  • #ifndef - conditional source based on a variable NOT being defined or being blank/empty
  • #if - conditional source based on a boolean comparison being true. For example: #if &qtyoh > 0
  • #ifexists - conditional source based on the IBM i object existing. For example: #ifexists qtemp/myfile
  • #else - else condition for #ifdef and #ifndef.
  • #endif - close a #ifdef or #ifndef block.
  • #chgvar or #setvar - changes a variable's value using expression syntax.
  • #include - include external SQL source member.
  • #joblog - write the text to the joblog.
  • #error - write the text to the joblog as an escape message.
  • #warning - write the text to the joblog as a warning.
  • #status - send a status message to *EXT.
  • #cmd - run a CL command.
  • #call - call a program.
  • #sql - run a non-SELECT SQL statement.
  • #sqlset - run an SQL SET statement.
04-Feb-2016- Improved query source member support. Now provides users with a method to set and change runtime substitution variables, including being able to specify default values when the SETVAR parameter of RUNSQLF is not specfified. In addition users no longer need to specify the & prefix within source members for the SETVAR, SETVARDFT, ISETVAR and ISETVARDFT commands. Variables on these commands may be specified with or without the & prefix. Now, setvar:&FROMDATE=160701 is the same as setvar:fromdate=160701
However within other statements, specifically within the SQL statement the prefix is required.
- New Control Codes:
  • setvar:myvar=value
  • SETVAR - Changes the value of the variable to the value specified and sets the Match Wholeword Only flag ON.
  • isetvar:myvar=value
  • ISETVAR - Changes the value of the variable to the value specified. and sets the Match Wholeword Only flag OFF.
  • setvardft:myvar=value
  • SETVARDFT - If the variable has not already be defined either by a prior SETVAR or through the RUNSQLF SETVAR parameter, the variable value is set and the Match Wholeword Only flag ON. If the variable is already defined the statement is ignored.
  • isetvardft:myvar=value
  • ISETVARDFT - If the variable has not already be defined either by a prior SETVAR or through the RUNSQLF SETVAR parameter, the variable value is set and the Match Wholeword Only flag OFF. If the variable is already defined the statement is ignored.
  • dltVar:myvariable
  • delVar:myvariable
  • DLTVAR and DELVAR - Remove the variable from memory--deleting it so it can't be used for substitutions.
  • setPrefix:new-prefix
  • SETPREFIX - Changes the prefix character used for substitution variables. By default the & is used however any symbol may be used. For example, some users prefer to use the colon to mimic SQL host variables, while other use the #pound symbol.
- Developers may want to take advantage of the SETVAR and SETVARDFT statements during SQL development. For example, assume your end-user specifies several substitution values at runtime. Normally to test the SQL statement the developer has to specify the RUNSQLF with its SETVAR parameter. This can get tedious. Now with the SETVAR and SETVARDFT statements, developers can specify the substitution values for testing and use RUNSQLF without the need to specify the SETVAR parameter. Then once the SQL source member is placed into product, simply purge or comment out the SETVAR statements. I like to use the ignore line symbol (a pound sign in column 1) when commenting out SETVAR statements.
- The OUTPUT parameter of RUNSQLF has been changed. It now accepts OUTPUT(*DISPLAY) in addition to OUTPUT(*) for interactive output. This change was provide for compatibility with legacy Query/400 queries.
- All of the *DATExxx special values now support an alternative with a trailing zero. For example, *DATEYMD normally embeds the date as YY/MM/DD. The new support allows users to remove the edit symbols by including a trailing zero. For example *DATEYMD0 will embed the day as YYMMDD with no embedded editing symbols.
- We no longer use QSYUSRI API to retrieve the user's home directory. There were too There were too many IBM i security-related issues with the QSYRUSRI API; not to mention the fact that it returned the home directory in Unicode CCSID(1200) which had to be converted. The new technique directly retrieves the home folder in the user's job CCSID and all is wonderful.
26-Jan-2016- Version 2 Release 4 is now available for download. This BETA includes the automatic 60-day trial/grace period license key for new users. If you previously downloaded prior to 26 Jan 2016, or are running IBM i v7.1, you should install this build ASAP.
20-Jan-2016- Version 2 Release 4 is announced. This release consolidates several fixes and enhancements (see notes below for details) and now includes an initial 60-day trial for new users--this trial is automatically installed with the program--no license key required. After the initial 60-day grace period, a paid license is required. V2.4 is in beta test until it is shipped later this year.
- # # #
17-Jan-2016- The was an issue were a "Pointer" error was being written to the joblog in rare cases. The application continued but was causing concern to certain users. There was an issue in the joblog message being generated when an SQL statement exceeded 1024 characters and "just the right" amount of character remaining in the statement were blank. When this happend, the QMHSNDPM API could fail. This issue has been corrected.
12-Jan-2016- Our CVTDATE function was having issues on V7R2 due to some internal SQL changes. We have re-written it to work with the new optional parameters feature in UDFs first introduced in v7r1.
- Control codes may be be continued onto multiple lines. To enable continuation the backslash symbol \ must appear as the last character on the line. When this is specified, the next non-blank character of the following line is concatenated with the current line at the position of the backslash. Embedded comment lines are currently not supported within continued Control Code lines. Here's an example:
 CL:cpyf fromfile(qiws/qcustcdt) TOFILE(MYLIB/custmast) \  
H2:This is the header text
- Two new control codes have been created. SQL and HTTP_OUTPUT
- SQL: may be used to run a non-select SQL statement. This allows users to prep the environment before the primary SQL statement is run. Examples of this might include setting the path SQL:SET PATH = *LIBL or other tasks such as adding data to a file with the INSERT statement, or calling a user-written procedure or function.
- HTTP_OUTPUT: may be used to specify the output format of the data being sent to the HTTP server. For example HTTP_OUTPUT:JSON causes the resultset to be returned as a JSON object. This allows our web users to avoid coding an extra FORM field with the output in the HTML. Users may continue to use the qryf_output form field to control the output through the HTML Form.
10-Jan-2016- SQL Query File for Web has been enhanced to provide better searching and results. Specifically our WEBSQL CGI program has been updated as follows:
  • Appostrophes from web forms are now doubled up before being passed to SQL.
  • The weblog may now be controlled from the webform using a hidden <input> tag. That tag is name="qryf_weblog" value="setting" where setting is true or false
  • Substitution variables may be identified to WEBSQL using hidden fields. Specify one or more <input name="vn" type="hidden" value="CUSTNAME"> and then include the CUSTNAME as another (visible) form input tag. WEBSQL automatically retrieves the value from CUSTNAME and passes it to the query engine.
02-Jan-2016- Legacy Query/400 QM Query "V 100n" where n=1,2,3 support has been added. These legacy codes were used to identify the line of headings for the query. SQL Query File uses H1, H2, and H3 for the same capability, and initially supported "V 1001", "V 1002" and "V 1003" before adding support for H1,H2, and H3. Today, the legacy support is moved to a different classification but no external changes should be noticed. However, we now recommend using the native H1,H2, and H3 in place of the legacy "V 100n" codes from Query/400.
- The HTTP_CSS code is now supported in the non-web version of SQL Query File when OUTPUT(*HTML) is specified.
31-Dec-2015- New Control Codes for have been added for use within source members and IFS files. Control Codes are used when an SQL statement is saved into and run from a source file member or an IFS text file. They allow the user to save some basic customizations for the report. For example, Control Codes H1, H2, and H3 correspond to lines 1,2, and 3 of the Heading lines of the report.
- New Control Codes:
  • Hn: Heading Title Line n
  • HTTP_BUFFER - Sets the size of the buffer used to write to the HTTP webserver
  • cmd - Specifies a CL command to run before the SQL statement is run.
  • HTTP_CSS - Specifies the HTML Style Sheet to be included in the generated HTML
  • URL - Specifies a URL to use when creating HTML.
  • TAG - Identifies a field that is wrapped in the URL specified on the URL code
18-Dec-2015- Enhanced the SETVAR parameter. It now includes an optional "Match Whole Word Only" option. The default is *YES. This provides a better/more fexible matching criteria when performing the scan/replace of a pre-runtime variable. Previously, only one type of searching was possible. Now users may control the search on a variable-by-variable basis. WholeWordOnly=*YES (the default) causes the search algorithm to distinguish between similarly named variables, such as &MYVAR1, &MYVAR11, and &MYVAR. When WholeWordOnly=*NO is specified, the variable is replaced as where ever it appears, regardless of context. This means that if &MYVAR1 is specified then if '&MYVAR1' and '&MYVAR11' are included in the statement, both occurrances are replaced. If SETVAR((MYVAR1 'XXX')) is specified, for SELECT &MYVAR1, &MYVAR11 FROM... then then the result is "SELECT XXX, XXX1 FROM ..."
- The distinction is based on a variable being followed by either a letter or digit. Anything else is considered non-relavent. If a variable is followed by a letter or digit, and Whole Word Only=*YES, then the replace is not performed. For example:
- In the above example, the 3 variables are successfully replaced with thier values. Because the default Whole Word Only = *YES is specified.
- In the above example, Whole Word Only=*NO causes the first variable 'CODE1' to be used for both CODE1 and CODE11 (the trailing 1 in CODE11 remains in the result).
- In most cases, Whole Word Only=*YES is what you want; the *NO option has been added to provide flexibility when concatentating the search/replace results of SETVAR with the target SQL statement, for example SELECT * FROM &divLIB.MYFILE where SETVAR((DIV 'C14' *NO)) would be concatenated with LIB.MYFILE to create C14LIB.MYFILE.
15-Dec-2015- There seems to be a buffer limit in C++ when communicating with the Apache HTTP server. To accomodate this we've reduced the internal size being sent to the HTTP server to 64k at a time. This is applicable to SQL Query File for Web only.
01-Dec-2015- New pre-runtime substitution symbols for the current date are introduced.
- These may be used within SQL source member runs with the RUNSQLF command. Their values are inserted in place of the symbol when the source member is loaded.
  • *DATE - Date in the local format.
  • *DATEMDY - Date in MM/DD/YY format.
  • *DATEYMD - Date in YY/MM/DD format.
  • *DATEDMY - Date in DD/MM/YY format.
  • *DATEISO - Date in YYYY-MM-DD format.
  • *DATEUSA - Date in MM/DD/YYYY format.
  • *DATEEUR - Date in DD/MM/YYYY format.
  • *DATEACT - Date in ddMmmYYYY format.
  • *DAY - The name of the day of the week in local format. e.g., Thursday
  • *SDAY - The short name of the day of the week in local format. e.g., Thu
  • *MONTH - The name of the month in local format. e.g., November
  • *SMONTH - The short name of the month in local format. e.g., Nov
  • *YEAR - The 4-digit year. e.g., 2015
  • *LDATE - Date in words (long format). e.g., Thursday 27 November 2015
- For example, to include the Current Date in its local format, the following code could be used:
 H2:My SQL Query - System: *SYSNAME  
H3:Customer List as of *DATE
24-Nov-2015- New pre-runtime substitution symbols are being introduced when running SQL stored in stream files or source file members.
- These may be used within SQL source member runs with the RUNSQLF command. Their values are inserted in place of the symbol when the source member is loaded.
- When an SQL statement is saved in a source member, users may embedded symbolic identifiers that are replace by the SQL Query File tool at load time--before the SQL statement is run--in order to customize the statement. This differs from runtime UDF's in that the replacement is performed before the SQL statement is prepared. Therefore the values inserted are from the originating system regardless of the system on which the query is being processed.
- The initial set of symbols includes the following:
  • *SYSNAME - System Name
  • *SRLNBR - Serial Number
  • *USRPRF - User Profile
  • *CURLIB - Current library
  • *JOB - Qualified job name
  • *VRM - IBM i version as VxRyMz
- Pre-runtime substitution symbols may be embedded anywhere in the source member including within the Headers (H1, H2, H3) or the SQL statement itself.
- For example, to include the User and System Name in a heading users may code the follow:
 H2:My SQL Query - System: *SYSNAME  
H3:Run by *USRPRF
20-Nov-2015- Fixed an issue with Report Headings that are stored in Source File members. When the legacy Query/400 Report Headings identifiers were used, an extra 8 characters were being written to the report headings in some cases. This has been corrected. NOTE: If using SQL Query File H1, H2, or H3 headings, this issue did not occur.
19-Nov-2015- Fixed a couple minor issues with Web and Excel output of Date fields.
17-Nov-2015- Circumvented a bug in IBM's SQL API interface that was causing issues in WRKQRYF when prompting for a list of files (tables/views) in a library. (See Nov 12 notes.) IBM has elected to not correct the issue, so we have altered our code to correctly handle this quirk in the SQL CLI interface.
12-Nov-2015- IBM changed the results of an SQL interface and then decided not to document it. This caused an issue on v7.2 with WRKQRYF. This issue has been resolved with this build.
- In addition, the resultset from VARCHAR fields can produce extra values at the end of each row being returned. This is an IBM-internal bug. We have written to this "quirk" so users should see no issue.
01-Nov-2015- DBCS passthrough
- New SQL Services
- New SQL Services Macros (for IBM-provided SQL Services)
- New Excel Options
- New Logging options
- New Reserved Words for substitution variables (used with SETVAR parameter)
- Final v5r4 and v6r1 release. No future updates shall be made the special edition.
- BLOB and CLOB fields can be returned but appear as *BLOBPTR unless explicitly cast.
- The *USR macro now includes the JOBD associated with the USRPRF. The USRCLS has been removed since it's really just a creation-time macro and not strictly used.
- A new IFSFILE() UDTF function has been created to allow users to use SQL (either within SQL Query File or native Embedded SQL in RPG) to retrieve IFS text files. Each line of text is returned as a single row. Specify the IFS file name as the sole parameter of IFSFILE.
27-Oct-2015- Internal bug fixes when using the API version of this product (not available to the public).
- Added a new option when writing out *EXCEL files (as SyLK images) so that cell referencing is compatible with non-Excel products. This feature is being delivered for certain customers, but is not officially supported as this time.
- Fixed an issue with S/36-style names when running on V5R4/V6R1 versions.
26-Oct-2015- Corrected an issue with the Headings 2 and 3 when output(*EXCEL) is specified.
- Updated the SLKOPT parameter. Now *WINDOWS or *OTHER may be specified for the Target Platform elements. Previously it was *PC and *MAC, which still work for backwards compatability.
- Added HASH_MD5() UDF to the product. Users may now produce an ASCII standard MD5 hash (in hex) from any database field with up to 4k in length input. The data is converted to ASCII CCSID(819) before hashing occurs so the resulting MD5 hash is consistent with non-IBM i platforms. The 16-byte MD5 hash is converted to hex and therefore the result is a 32-byte text value.
21-Oct-2015- Corrected an issue with the column headings in WRKQRYF that occurred when no column headings existed. The WRKQRYF command now uses the column text if no column headings exist, and if no column headings and no text exists then it uses the column name.
15-Oct-2015- Enhanced the LOG parameter on RUNQRYF/RUNSQLF commands. We now use *LVLx where x can be 1 to 5. See the online helptext for uses of each level.
- Added support for legacy S/36E file names (i.e., names with a period in them) to WRKQRYF and RUNQRYF commands. RUNSQLF does not require direct support. For example RUNQRYF FILE(QGPL/"CM.CUST") is now accepted (with or without the quotes). WRKQRYF has also been enhanced to automatically enclose S/36 style file names with double quotes.
- Note that if your release of IBM i5/OS V5R4 has not had PTFs installed for a long time, this support may fail. v6.1 and later users would not have such an issue.
- Shifted the licensing for trial users to require users to obtain a trial license key. The first time the product is restored you must enter the trial license key. Trial keys are available from the SQL Query File website. This will keep the product active for at least 60 days. After that a paid license is required.
30-Sep-2015- Suppressed the message that appears when the system attempts to create a user space with *REPLACE option. It now checks for the user space first, then creates it only if it does not exist. You will still see the "owner changed" message the first time the user space is created within the job, but that's all.
23-Sep-2015- Fixed an issue in WRKQRYF when a field name exceeded 10 characters the column headings were not being retrieved correctly.
- Enhanced the processor for SQL statements run from within Source File Members. Now when a source member is processed, its format can be the raw stream of an SQL statement that is produced by the RTVSQLSRC command (the QSQGNDDL API).
18-Sep-2015- Fixed an issue during WRKQRYF that logged excessive low-level msgs to the joblog.
- Increased the maximum length of the following items:
  • RDB (Remote Database) System Names - 64 characters
  • Field Names - 128 characters
  • Table (File) Names - 128 characters
15-Sep-2015- Substitution variables are now recognized and converted on the Headers as well as the SQL Statement itself. Previously only the SQL statement was processed by the SETVAR engine. Now, the SQL statement and headers H1, H2, and H3 are processed. NOTE: H1,H2,H3 are synonyms for APPTTILE, USRTITLE, RPTTITLE parameters.
- New SETVAR special values are introduced in this build. Users may now insert the following symbols for the VALUE portion of a variable on the SETVAR parameter of the RUNSQLF command:
  • *JOB
  • *OSVER

For example:

- The existing SETVAR substitution values continue to be supported. Today's enhancement is in addition the the existing SETVAR capability.
11-Sep-2015- Fixed a small issue with the WRKQRYF command when columns in the result set contain no column headings.
09-Sep-2015- Added new Diagnostics page when OUTPUT(*PRINT | *PDF) is specified. Now when the user specifies LOG(*LVL2) an additional page is created containing diagnostic information including Number of Rows, Columns, base table and the full SQL Statement itself.
- When OUTPUT(*PRINT) is specified additional options included HOLD and SAVE are now provided to control the resulting spool file.
- When OUTPUT(*PRINT) is specified and no specific SPLFNAME parameter is specified, SQL QUery file will attempt to use the base (file) table name (if one is available) as the SPOOL file name.
30-Aug-2015- Corrected a compile issue with the V5R4M0 version of SQL Query File that was causing the interactive viewer not to be able to retrieve the last SQL statement.
25-Aug-2015- A new Macro standard has been implemented that allows user-defined macros as well as SQL Query File-provided macros. Technically, end-users may create source members in a source file named QMACSRC. Then using RUNSQLF's macro support, specify the member name on the RUNSQLF command.
- For example:
This would search for the source file member "SALES" in the QMACSRC source file on the library list. If it is found, the SQL contained in that member is processed. If it is not found, SQL Query File then looks in the QQRYFSRC source file in library COZQRYF. If not found in COZQRYF/QQRYFSRC it then searches *LIBL/QQRYFSRC for the member.
- Previously macros were stored in QQRYFSRC and named XXXXXXXMAC where XXXXXXX was the name of the macro. This naming convention has been changed; dropping the "MAC" suffix entirely. This allows for "macro names" of up to 10 characters instead of the previous 7 characters.
- The source for all UDF and Procedures are now shipped in QUDFSRC instead of QSQLSRC. This prevents an issue when using RUNSQL SRCMBR(xxx) because the SRCFILE parameter defaults to SRCFILE(*LIBL/QSQLSRC). With SQL Query File's product library on the library list, the QSQLSRC file in COZQRYF is normally the first such file on the library list. Now, the QSQLSRC file is no longer a part of SQL Query File and should provide users with a better experience.
- Corrected an issue with the internal case-insensitive scan API named fScani().
24-Aug-2015- Corrected an issue with the ADDLIBLE and RMVLIBLE UDFs that was incorrectly issuing an warning when the library was already on the library list, or already removed from the library list respectively. Other fixes and enhancements to the xxxLIBLE UDFs.
- Redistributed the UDF and SP (stored procedures) for OVRDBF_MBR and SYSTOOLS. Now OVRDBF_MBR is located in OVRDBFMBR and removed from SYSTOOLS. This change will not impact end-user code as this is only a source member change.
12-Aug-2015- The installation script has been enhanced to more accurately install SQL Query File. Previously, our UDF and (stored) Procedures were not being copied to QUSRSYS as is stated in the documentation. This would prevent things like CVTDATE(), and MBRLIST() from working unless explicitly qualified to COZQRYF schema (library). Now all SQL Query File UDFs are created in COZQRYF and QUSRSYS independently.
10-Aug-2015- Corrected an issue when trying to load files from QTEMP. Apparently IBM does not include QTEMP files in the system catalog, so RUNQRYF QTEMP/xxxx would fail. The RUNSQLF command is not effected by this issue. Now when the library name is QTEMP the RUNQRYF command does not verify if the file's exists in the system catalog, instead the SQL processor will issue a message if the file does not exist.
- Corrected a bug when no title text is specified on RUNQRYF and it extracts either the member text or object text to use as the title, and that text contains embedded quotes. RUNQRYF now doubles up those quotes when passing the xxxTITLE parameters to RUNSQLF.
08-Aug-2015- Implemented a "native" Header tag for SQL SELECT statements stored in source file members or in IFS text files. The new Hn: tag should be used to identify the headers for output. H1: H2: and H3: are currently supported. To use these tag, specify them as the first item on the source line followed by the header text. Note these tags only apply to the current line, meaning headings may not be continued. In the case of headers, only the first 50 characters are used.
H2:Customer Master Regional Listing
H3:Active Customers Only
- We now support embedded CL commands within source file members and IFS text files that contain SQL statements. Use either CL: or CMD: to identify a CL command to run. Commands lengths are limited to one line and are run immediately upon detection. There is no limit to the number of commands that may be specified.
- Technical Note: CL Commands are run when the source file member of IFS Text file is loaded. They run in the order they appear in the source, however they are always run before the SQL statement itself. Here is an example CL command:
- Assume you need to add 2 libraries to the library list before running an SQL SELECT statement. In addition you need the *CURLIB changed to library PRODDATA.
- There is no difference to the SQL Query File parser between CL: and CMD:
21-Jul-2015- Refresh - Rebuild to make the v7.1 and v5r4 versions the same.
27-Jul-2015- Added DATE output capabilities to the OUTPUT(*EXCEL) option. Now Db2 for date fields are converted into Excel compatible integers and formatted as an Excel Date.
- Removed the undesireable heading lines when output to EXCEL or CSV is specified. When users used RUNQRYF to create EXCEL or CSV files, the standard Application headings would be included. This did not occur when using RUNSQLF however. The RUNQRYF is now functioning similar to RUNSQLF in this regard.
- Added a new MBRLIST function that allows users to query a member list as if it were a table.
17-Jul-2015- Fixed a small issue when a user specifies the short SYSTEM name for a file that has a long file name associated with it. It wasn't finding the file in some cases. This has been resolved.
14-Jul-2015- The COZLANGen *MSGF is now used throughout SQL Query File. Prevously the message file named COZQRYF was being used. We have standardized on COZLANGen where the last two characters are the National Language. Currently "EN" (English) is the only supported language, but we will add others as use of the product continues to spread outside of North America.
12-Jul-2015- SQL Query File no longer requires users to explicitly specify SQL(*SRCFILE) when when a source member is used--the SQL parameter now defaults to *SRCFILE and the SRCMBR parameter now defaults to SRCMBR(*NONE). This gives the command a dependancy between the two, thus RUNSQLF SRCMBR(XYZ) will run the SQL statement in source member XYZ in source file QSQLSRC on the library list. Previously, users had to to specify RUNSQLF SQL(*SRCFILE) SRCMBR(XYZ) with this modification, the SQL parameter is no longer needed. Message QFA0503 will be issued if no parameters are specified on RUNSQLF.
08-Jul-2015- SQL Query File is now officially at V2R2M0. Users may need to run DLTLICPGM 2COZQF2 first before installing this release, using RSTLICPGM 2COZQF2 *SAVF SAVF(QGPL/COZQRYF)
07-Jul-2015- Maximum record length when OUTPUT(*) specified has been increased to the system limit. Previously record lengths upto 20k were supported. The limit was imposed by the size (width) of the Column Headings. Those limits have been doubled, to beyond the current system limitations for record width/length.
- The connected database (remote system) name now appears on the screen when OUTPUT(*) is specified. This is in addition to the local system name. This name will be that which is enrolled in your Remote Database Directory (WRKRDBDIRE). Note this ony shows the remote system when RDB/USER/PWD parameters are used to CONNECT to the remote system. If 3-tier names are used the local system's database name appears.
- OUTPUT(*HTML) supports a Link Field and a Linked Field property. When using SQL Query File for Web/Modile users may now specify a result column that contains a URL and another column that is tagged with that URL upon conversion to HTML.
- To specify the URL or a field name that contains the URL, include the URL: tag within the SQL Source File Member that contains the SQL statement.
- To specify the field that should be tagged with the URL, include the TAG: tag within the SQL Source File Member that contains the SQL statement.
SELECT compname,addr1,city,state,zipcode FROM CUSTMAST ORDER BY CSTNBR
- Fixed an issue when OUTPUT(*CSV|*JSON|*PDF|*XML) is specified and the full IFS file name is also specified on the STMF parameter and STMFNAME(*STMF) is specified. SQL Query File was trying to create the file as a directory and then no data was written since the file didn't exist. This has been corrected.
04-Jul-2015- JSON numeric values that are less than 1.0 now rendor correctly. Previously if a numeric value of .43 or -.42 were selected, JSON parsers could not parse this syntax. Now, SQL Query File includes a leading zero to the left of the decimal point whenever the value is less than 1. Thus, 0.43 and -0.42 are now delivered.
- The AUTOQUOTE parameter of RUNSQLF now defaults to AUTOQUOTE(*NO). Previously SQL Query File, by default, always quoted the non-numeric SETVAR parameters. This caused some issues with most users and the way they store their data. As of this release, AUTOQUOTE is no longer the default. To automatically quote non-numeric SETVAR values, specify AUTOQUOTE(*YES) for the RUNSQLF command.
- When creating CSV or JSON files and using the default STMFNAME parameter, in some rare cases SQL Query File would not append the .CSV or .JSON suffix. This has been corrected.
01-Jul-2015- Final V5R4-compatible build released today. In addition a refresh for V7R1/V7R2 is included in today's build.
- The way we check if file exists in RUNQRYF is changed.
- The underlying code behind RUNSQLF's SETVAR parameter has been completely rewritten in C++.
- We have removed the COZRPGLIB *SRVPGM from the product. Previously several tools from COZTOOLS were included in SQL Query File. WIth this release we have completed the migration of those tools to low-level C++ code so that service program is no longer necessary.
- If you have a very old version of SQL Query File, you may need to use DLTLICPGM before installing this release. During that DLTLICPGM, you may see COZRPGLIB and RPGFREE *SRVPGM's noted as being deleted. This is expected.
18-Jun-2015- Bug fixes. Corrected an issue with the interactive viewer's paging and EOF.
- Corrected an issue with long file names being used when accessing database tables on a remote system. All versions refreshed.
15-Jun-2015- Final IBM i5/OS V5R4M0 and v6r1m0 compatible version of SQL Query File released.
- Subtle change in the SQL cursor handling. Whenever a non-interactive routine is requested for output (meaning the OUTPUT(*) option is not specified) a "scrolling cursor" is no longer used. This can improve performance in rare situations. A Scrolling Cursor is still used for OUTPUT(*) requests.
- Improving on our support for running retrieved Query/400 and QM Query source, SQL Query File (SQF) supports up to 3 Report Title lines embedded in the source member along with the SQL SELECT statement. Use "V 1001", "V 1002" and "V 1003" to identify the title text with the 100x indicating title line (1, 2 or 3) to which it applies.
V 1001 050 This is title line 1 of the report
V 1002 050 This is title line 2 of the report
SELECT custnum,lstnam from QIWS/QCUSTCDT
- Level Break-style output is supported for Print and PDF output. This allows user to indicate that certain columns in the resultset, are printed only when their value changes from the previous record's value.
08-Jun-2015- Our popular COLTOTAL (column totals) parameter on RUNSQLF has been enhanced. It supports limited derived columns. Meaning you can specify normal column totals as before, however additional column "total" entries may be specified using standard math formulas. For example, assuming you have a four-column report. Column 1 is a text value, columns 2 and 3 are SALES and GROSS PROFIT respectively, and column 4 is specified in the SQL SELECT statement as:
 Dec( 100* ( PROFIT / SALES ), 7,2)
Obviously you would not want to accumulate this column's values, since it would produce a useless result. However, with our new expression-support for column totals, users can specify a formula for the column total, as follows:
 RUNSQLF SQL(SELECT A,B,C, dec( 100 * (C/B),7,2) FROM SALES') OUTPUT(*PRINT) COLTOTAL(2 3 '4=100*(&3/&2)')
When the report is created, all 3 total columns will have the figures you need.
- Syntax: Column Total Expressions support standard expressions and parens for their embedded formulas. The first two tokens must be the derived column number (4 in our example) followed by the equals sign, followed by the expression. Support exists for embedded token identifiers that reference the relative column total to be used in the expression. In order to reference Another column total, it must, obviously, be specified as one of the column totals. In our example, above, Total columns 2 and 3 are referenced by column 4's expression. Their resulting sum is inserted into the formula in place of the tokens &2 and &3.
27-May-2015- OUTPUT(*HTML) format has been added.
- A new web (CGI) program has been added to allow SQL statements to be run from your own web pages. The results are sent to "standard output" (i.e., stdout or cout). To incorporate SQL Query File's WebSQL cgi program into your own web pages, add the necessary HTTP CONF file
StriptAlias /websql  /qsys.lib/cozqryf.lib/websql.pgm 
And then the following to your HTML:
- IBM i API QDBRTVSN does not work with DDM files. Therefore we no longer use it in the RUNQRYF command to retrieve the library for a table name. We use the IBM-supplied QSYS2/SYSTABLES table. This is also used to translate the long SQL file name to its short 10-position system name.
12-May-2015- OUTPUT(*SLK) format has been added to support output of native Excel SyLK (.SLK file extension) file format. This format, unlike CSV, includes limited formatting of the columns, headers and also supports our COLTOTAL parameter, allowing column totals to be included in the resulting Excel file. Files with the .SLK suffix may be opened directly into Excel and saved as native Excel .XLS or .XLSM files. Similar to .CSV files, .SLK files may be open by double-clicking on them. Unlike .CSV files, .SLK files are designed to limit each cell's size to a maximum of 255 characters.
4-May-2015- The new SRVMODE (SQL Server Mode option) parameter allows the SQL statements being run to run in the SQL Server job. The new option defaults to SRVMODE(*BATCH) which means that when RUNSQLF is run within a batch job, it will utilize server mode. When run in an Interactive Job, it will not use server mode. Specify SRVMODE(*ALL) if you need to utilize server mode within an interactive job, but be warned, SQL SERVER MODE ends your ability to use IBM's STRSQL command, and also causes ALL SQL statements to be run using Server Mode, even those embedded in RPG applications. Therefore, I would avoid server mode except for stand-alone batch jobs.
30-Apr-2015- Added a new F8=Build DDL to the WRKQRYF command. The new dialog box prompts the end user for the SQL source file and member name and will generate a source member containing the SQL Data Definition Language (DDL) including the CREATE TABLE and COMMENT ON statements for the current file being worked with.
10-Apr-2015- SQL Query File v2.1 supports basic XML output via OUTPUT(*XML). Both commands RUNQRYF and RUNSQLF have been enhanced to include the OUTPUT(*XML) parameter. XML support can be specified via RUNSQLF using any XML Extender syntax just like any other interface. With this update however, SQL Query File automatically generates the XML Extender functions around your SELECT statement. This function is only available on IBM i 7.1 and later at the appropriate TR level.
06-Apr-2015- SQL Query File v2.1 is available for IBM i v7.1 and later.
- New "macro" options have been added to RUNSQLF command. These "macros" evoke pre-written SQL statements that return information to the user. Most macros are mapped to IBM DB2 for i "Services" that have been enabled via SQL table access. All macros are stored in the source file QQRYFSRC in the product library. To use one of the macros, specify RUNSQLF *macroname where macroname is the same as any of the source member names in QQRYFSRC.
- Users of IBM i5/OS V5R4 or IBM i V6R1 can also install SQL Query File at no charge. However, once users move to IBM i v7.1 or later version or release, then license contained in the earlier version terminates and a paid license is required.
- The Default Library name option (DFTRDBCOL) has been added to the RUNSQLF command. This parameter can be used when specifying unqualified file names and the "current" library is either not set, or is not the library you wish to use. The library name specified for this parmaeter is used by the SQL Query File engine as the default library name for unqualified tables in the SQL statement. The parameter name DFTRDBCOL (Default Relational Datbase Collection) is taken from other IBM i commands and used for consistency. When the default DFTRDBCOL(*NONE) is specified, the command works the way it always has.
- Committment control *AUTO option is available. The COMMIT parameter accepts the COMMIT(*AUTO) and is used when issuing an Insert/Update/Delete statement. It auto-commits the transaction on those systems using committment control. The default COMMIT parameter remains *NONE. For those who need committment control, COMMIT(*AUTO) is a great feature to leaverage.
25-Mar-2015- The UDF decEdit( numValue, return-length ) has been added. This function embeds commas into the thousands positions for the numValue. However due to SQL's UDF standard, there edited return value is a fixed lenght. SQL Query File uses a fixed return lenght of 96 characters. This can cause issues with formatting. So it is recommend that users wrap decEdit() in a CHAR function. For example:
select custno, char( decEdit( sum(sales), 12), 12) FROM custtable 
This example would summarize the SALES field and return the result edited and right-justified in a 12-position result. There is redundancy in that the return length and the char() length are usually the same value. But it works.
25-Mar-2015- For V5R4 customers, a small change was made to avoid the Authority message for PTF SI30132. This issue could generate the following message to the joblog:
This error was due to a change in the sign-on authority and parameter list for the internal SQL API. The work-around is to create a data area on your system as follows:
With this update to SQL Query File, the enhanced sign-on method is used and the issue is avoided. The QSQCLICON data area is no longer needed.
24-Mar-2015- STMFOPT (Stream File Add/Replace Option) wasn't always working. This has been fixed.
- The COLTOTAL (Column Totals) parameter supports ordinals. Meaning you may specify the relative column number instead of the column name. This can be especially useful for derived columns; those without explicit names.
20-Mar-2015- Performance improvement when producing CSV files. Case studies with 500,000 record result sets indicate that this update to SQL Query File produces CSV images in approximately 1/3 the time previously required. In some less complex conversions the overall time was reduced by 90% (to 1/10 the time) of the previous method.
- CSVOPT( ... *TRIM ) - Users may specify that embedded quotes within text fields being converted to CSV may be removed. Previously, embedded quotes could be ignored or escaped. Users may specify that embedded quotes are removed from the data written to the CSV file.
17-Mar-2015- The CSVOPT(*COLHDG) option when set to *NONE was not being properly detected. This has been correct. In addition CSVOPT(*NOHDG) is now a synonym for CSVOPT(*NONE).
- Command helptext now includes the CSVOPT parameter help.
- The WRKQRYF panel now contains the field/column count as well as the record length.
- The OMITFLD parameter of RUNQRYF has been implemented. Now when FLD(*ALL) is specified (the default) the OMITFLD parameter may be used to list the fields that are to be omitted/excluded from the generated SELECT statement. This is of particular use when writing CSV files where most of the fields are to be included "except for" one or two fields.
- IBM i5/OS V5R4M0 support is being terminated on April 1, 2015. The final product build (save file) shall be made available on the product website, however no further enhancements shall be incorporated into the V5R4M0 version beginning April 1st, 2015. A previous announcement regarding sunsetting our V5R4M0 support was made on January 30, 2015. Today's announcement identifies the date that sunset begins.
08-Mar-2015- Resolved an issue with OPTIMIZE being generated even if OUTPUT(*FILE) was specified. Now, QFS checks the value of the OUTPUT parameter and only auto-generates an OPTIMIZE(xx) arguement for OUTPUT(*) and (*PRINT).
- The internal scan/replace routine for substitution arguments of the SETVAR parameter is now a stand-alone module so it can be enhanced and its use extended.
- SQL source retrieved using RTVQMQRY (Retrieve Query Source) command may now be processed by RUNSQLF SQL(*SRCF). In addition, the Title for the query may be extracted from the SQL source itself, using the Query syntax.
12-Feb-2015- A new Fn key (F6=Print) has been added to the interactive Viewer. Now when the OUTPUT(*) option is selected, users may press F6 to re-route the output to OUTPUT(*PRINT) without the need to rerun the query process. The same resultset is used to produce the SPOOL file. Today, users may only redirect the output to SPOOL files, however future enhancements may include other output media choices.
10-Feb-2015- An issue with CSV generation and escaping double-quotes has been resolved.
04-Feb-2015- A new OPTIMIZE parameter has been added to the RUNQRYF command. The default is OPTIMIZE(50) with valid range of 1 to 9999999 or *ALL. When this option is any value other than *ALL, the OPTIMIZE FOR xx ROWS is added to the generated SQL SELECT statement. This often improves performance for SELECT statements. This parameter is available on all versions of Query File SQL, however it works best on V6R1 and later.
- An internal change to the driver program that runs the RUNSQLF command from within the RUNQRYF and WRKQRYF commands has been modified. It is now a C++ *PGM object and has been made much more solid.
30-Jan-2015- QF SQL is now stand-alone. Meaning it no longer relies on the runtime version of COZTOOLS being installed. Previously if you installed QF SQL, you may have received a runtime issue if you did not have COZTOOLS installed. Now, QF SQL includes its own runtime support.
- V5R4 Support is entering "code freeze" after 1st Quarter 2015. No more updates to our no-charge V5R4 version shall be made beyond the code free date once it is announced later this year. Query File SQL for V7R1 and later shall continue to be supported and enhanced. Since we have zero customers running IBM i v6r1m0 we will review the viability of continuing V6R1M0 support. An announcement on it will come during 3rd Quarter 2015.
12-Jan-2015- Enhanced the Column Totals function (parameter COLTOTAL) such that totals are better aligned, and if the field being totalled does not contain decimal positions then the total shall also not contain the decimal. (NOTE: COLTOTAL results continue to be restricted to OUTPUT(*PRINT | *PDF) results.)
- A new shortcut for COUNT(*) results is now incorporated into the RUNQRYF command. Use RUNQRYF myFile FLD(*COUNT) to use this capability. In this example the resulting SQL statement produced and run by RUNSQLF is 'SELECT COUNT(*) FROM MYFILE'.
- The app title is now taken from message in the COZQRYF msgf.
16-Dec-2014- Fixed a bug when OUTPUT(*CSV) was specified that caused a pointer not found error.
11-Dec-2014- Removed F11 from the Viewer screen. It wasn't being used, but was "active" and pressing it resulted in some interesting characteristics.
- When in DS3 mode when using OUTPUT(*) since the third row of titles cannot fit on the screen, we now consolidate the 2nd and 3rd row. In addition, again in DS3 mode, we've moved the data from the 3rd line of the screen, to the right-most (up to) 10 positions of Title line 1. If the tile is not blank in that area, no date is displayed.
- The RUNQRYF command now uses the input file's member text as the default for the third line of Titles on the display. With standard DB2 files, the member text of single-member files is typically the same as the File's object text, so using the member text was the best choice for those rarely used multi-member files. When there is no member text, the file's object text is used instead.
- The hidden "sequence numbers" on the Viewer have been removed; F11 is deactivated. Originally a line number or sequence number was visible when using OUTPUT(*) and after pressing F11. This feature provided to be problematic and has been removed. When using RUNQRYF, use the *RRN or *ROWID options on the FLD parameter to include the row/record number in the result set.
- Several other bug fixes and performance improvements.
31-Oct-2014- Added a prompt when running an SQL UPDATE or DELETE and no WHERE clause is detected.
24-Oct-2014- Added support for third row of user-specified headings to OUTPUT(*PRINT). This support was previously added to OUTPUT(*).
18-Sep-2014- Corrected spacing issues with headers when OUTPUT(*PRINT) is specified.
- Added "column totals" features to the OUTPUT(*PRINT | *PDF) options. Users can now total up a numeric field(s) and have those totals printed at the bottom of the output. Simply specify the field name on the COLTOTAL (Column Total) parameter and Query File automatically accumulates the field's value and prints a total at the bottom of the output.
11-Sep-2014- Fields that contain Decimal Data Errors (DDE) are identified by filling those fields with asterisks****.
- Null fields (fields that have their null indicator set on) are filled with plus signs+++. These characters may be customsized using the CHGQRYF command or by specifying the new DDECHAR or NULLCHAR parameters on the RUNSQLF command.
- Decimal notation (i.e., the decimal point) may be overriden to a comma. The default is DECPOINT(*JOB) and may be specified on the RUNSQLF or CHGQRYF commands.
- A new OUTPUT option has been added. The OUTPUT(*RPGxxx) option allows you to generate RPG IV source code from an SQL query. The RPG Input, Output, or Definition specifications may be generated using *RPGI, *RPGO, *RPGIO or *DS respectively. The SRCFILE and SRCMBR parameters are used when these output options are selected. Currently, to prevent accidental overwriting of existing source code, MBROPT(*ADD) is the only option supported.
- NOTE: When OUTPUT(*RPGxxx) is specified the SQL query is only prepared but not run. That is no data is read and no data is output only the generated source code is produced.
03-Sep-2014- Our User Interface standard is to use the "carrot" symbol ^ to indicate that the SHIFT key is held down while pressing the next key. For example ^F1 means Shift + F1 or F13 (Command Key 13 to the legacy users). This provide a more clear approach to identifying command function keys to end-user.
03-Sep-2014- Fixed a bug in the OUTPUT(*CSV) support when a numeric value is negative. If the length of the digits plus the decimal notation symbol equaled the original length of the field, then when the value was negative the right-most digit may have been truncated in some conditions. This has been corrected.
- Added F13 (shift+F1) Customizable Options feature. This feature displays a list of Options that may be changed by the User. Most options are saved between sessions on a user-basis (different users may have different default option settings) and are restored upon re-entering WRKQRYF. The OUTPUT option is not saved between sessions as users do not want that option saved.
26-Aug-2014- Added a completion message "n rows xxxxxx" after the SQL statement is run. For example, if you modify a rowset and 23 rows are changed, you'll recieve the following message: "23 rows updated." We issue this message for the following SQL statements: INSERT, UPDATE, DELETE, SELECT
- Updated WRKQRYF. Now a list of files may be prompted by positioning the cursor into the File name and then press F4. The list is always generic, currently, so only specify the fractional part of the file name in order to list the ones you want. Blank out the File name to list all files in the specified library.
- Comma-separated Values Output has been added. Specify OUTPUT(*CSV) to produce an ascii text CSV file on the IFS. Query File writes to your home directory by default and creates a file with the same name as the one being queried, plus the .csv suffix. There is a new CSVOPT parameter to allow users to customzie the results.
- Fixed the level of message forwarding being used. Now messages tend to show up in the call stack where you as a user, would expect them to appear. If you find one that doesn't appear where you expect, please let us know and we'll see what we can do.
18-Aug-2014- The RDB parameter is no longer an *SNAME. It is now a simple *CHAR value.
- The WRKQRYF command now prompts for User ID/PWD whenever the RDB is changed within the Work with panel (it previously only issued the SignOn panel when selecting from a list of Remote DB names using F4).
- Minor updates on the WRKQRYF command and the F4/F10 functionality.
13-Aug-2014- The maximum width of a result set (record length) is now 32k. The previously limit was 4k or 7k depending on whether you were printing or displaying the resultSet. Apparently some users have some "very flat" files that they need to query.
12-Aug-2014- Added a remote system name and prompt to the WRKQRYF command. Users can now position the cursor into the Remote system name field, and type in the remote location they want to connect to. Optionally, pressing F4 provides a list of existing remote locations (as known to the WRKDBDIRE command) of which they can select the location to be connected to. Upon selecting a remote location name, the user is prompted with a SIGN ON dialog box to enter their User name and Password for that remote location.
- The Change Column Headings feature in WRKQRYF has been fixed. PTF SI52561 resolves this problem on our V7R1 system. However, IBM has not corrected V6R1 or earlier with this PTF. Query File has been changed to work around this issue regardless of the release on which the issue occurs. Using WRKQRYF now properly changes Column Headings when requested.
------------ ---------------------------
08-Aug-2014- Final GA release published.
29-Jul-2014- Resolved an issue with OUTPUT(*FILE) that occurs under certain condition.
- Adjusted the routing of messages produced by Query File so that they appear in the menu's SFLMSGQ and Command entry more accurately.
- Added a global error trap to avoid issues after a bad SQL statement is entered.
- Added a pre-run syntax check of the SQL statements. This gives better feedback and avoids runtime environment issues upon a syntax failure.
24-Jul-2014- RC1: Created the QRYF menu and STRQRYF command (to jump to the menu). This new menu includes options to launch RUNQRYF, RUNSQLF and the new WRKQRYF command.
- Created the new WRKQRYF command. This command accepts a file name and produces a subfile containing all the fields in that file. You can select fields to query using Query File (by typing in the sequence into the Option field) and then pressing Enter (to run it) F4 to prompt the RUNSQLF command, or F10 to run it directly. F10 with no field selected, does a "SELECT *" automatically.
21-Jul-2014- Release Candidate 1: 14 Days until formal product launch. Resovled several issues with conflicts in the Printer parameters (settings) on the RUNSQLF command. Add the 3rd header line (aka "Report Title") to the Viewer and Print routines. The initial draft of the RUNSQLF command's help text panels are not available. Various other fixes and enhancements.
17-Jul-2014- In rare instances, the Pad and Expand flags were not being set properly. This could cause the database to return fields that appeared "smashed together". when writing output to the display. For example: RUNSQLF 'SELECT * FROM foo' OUTPUT(*). This usually occurred only on V5R4 and V6R1 systems. It has been corrected.
16-Jul-2014- Created a "Replace UnDisplayable Characters" routine that is called when OUTPUT(* | *PRINT) are specified. Some legacy files that are program described and included packed data, were causing the display I/O to fail. This routine filters everything less than X'20' out and replaces it with blanks (x'40'). We made the decision to leave in X'20' and above since sometime there is a need to view those characters or force display attributes/colors. Characters x'20' and above do not cause I/O errors. Parameters on RUNQRYF have been redesigned a bit. Most users will not see any difference. Basically we reduced the initial number of parameters that appear when the RUNQRYF command is prompted. The only non-essential parameter that is initially displayed is the RDB (remote database system name). And we are reviewing that one as well--it may vanish before we ship the product on August 5. As with any Command, press F10 to view additional parameters when Prompting the RUNQRYF command.
12-Jul-2014- Software licensing is active. Installing Query File requires the use of RSTLICPGM 2COZQF2 DEV(*SAVF) SAVF(qgpl/cozqryf) to install.
- The Product Library is COZQRYF and if you have a beta version installed, please delete or clear the COZQRYF library before running RSTLICPGM.
- A trial or permanent license key must be installed using the ADDLICKEY command.
- To obtain a license key, visit
- We fixed an issue with CHGQRYF's Prompt Override Program.
- Changed the way OUTPUT(*) behaves during a batch job. Now it automatically redirects the output to OUTPUT(*PRINT) when OUTPUT(*) is specifed and the job is a batch job. This is consistent with standard IBM i CL command attributes.
- The Query File Attributes user space is now *LIBL sensitive. If user space QRYF appears on the library list, it is used. Otherwise, the product library (COZQRYF) is searched for that user space.
01-Jul-2014- Fixed an issue with the RUNQRYF's MBR parameter (it wasn't always working).
- Corrected an issue when OUTPUT(*PRINT) is specified and numeric data is included in the output. It is now correctly right-justified.
- The rowsFetched variable (in the Query File SDK) is now properly set to 1 when a non-scrolling cursor is specified and the Fetch operation is successful.
27-Jun-2014- Changed the max length for the File name to 128 and increased the max field name length to 18 characters. Also began work on OUTPUT(*JSON) so you may see references to JSON objects but support for JSON is not available at this time.
20-Jun-2014- Fixed a major bug in the read ("fetch") routine that was occurring on some versions of IBM i but not others. This build is now stable on V5R4 through V7R2 of the operating system.
17-Jun-2014- An IBM replacement *SRVPGM for SQL that we were using apparently had issues with some user's configurations. We've rolled back the SQL routine so that it uses the the production version of IBM's SQL service program and in our test situations, seems to avoid any issues Users said they had experienced.
- Query File is now qualified library COZQRYF. The final/production version of COZQRYF may have a different Product Libary name, and all objects will point to that new library.
16-Jun-2014- Several issues with V5R4-compatibility have been corrected. The final version will be V5R4 compatible however, some features will simply not work on i5/OS v5r4.
15-Jun-2014- LOG(*NO) is now the default for the RUNSQLF command. Previously when using the RUNQRYF command and specifying LOG(*SQL) the RUNQRYF and the RUNSQLF would log the SQL statement. This change will resolve this issue.
- The end of the Early Program test period is approaching on August 1st. We have updated the build to issue a message as that date approaches. In add for our beta test users, we now include an automatic 1 month extension to give you more time to make a decision on whether to license Query File or remove it from your system.
- When specifying STMFOPT(*ADD) and OUTPUT(*TEXT) the resulting text file was always replaced or overwritten. This had been corrected. Now when a text file already exists, the data-only is added to the existing file--no headers are output.
- After an unsuccessful connection to a remote database, a 2nd "valid" attempt was treated as if it too failed. This was cause by failure to clean up the connection handle. This has been corrected.
14-Jun-2014- Beta Tester Fixes have been implemented. Thank you to our Beta Test Users who report important issues and request new features. We do appreciate it and listen.
- Fixed: When using FLD(*RNN or *RELNO) and the record count exceeded 1 million, an untrapped error is generated. This has been corrected.
- Fixed: The right-justify routine would fail to properly right-adjust a numeric field in certain conditions. This has been corrected.
- Fixed: When using the Viewer and attempting to Position To a specific record/row the program would ignore and truncate row numbers above 32767. This has been corrected.
- Fixed: The "Null field" indicator (the plus sign) was being turned on when a field contains bad numeric data, and not properly reset for the next field. This has been corrected.
- Fixed: When OUTPUT(*OUTFILE) is specified and MBROPT(*ADD) is also specified and the file existed, the SQL INSERT statement would fail due to always including the OVERRIDING SYSTEM VALUE clause. This can only work with files that contain an IDENTITY column. This clause has been removed on the MBROPT(*ADD) option when the file already exists. It is a future objective to auto-detect identity columns and include the OVERRIDING SYSTEM VALUE clause automatically.
- Fixed: When an invalid SQL statement is specified for the RUNSQLF command and that statement is shorter than 7 characters, a Substring error would occur when the system checked for a "SELECT" statement. This has been corrected.
12-Jun-2014- The Viewer now includes F4=Field List to list the resulting fields of the query. Several bug fixes and work arounds have been implemented in this build. Also we have detected some features not functioning on V5R4 but those features have no usability impact on the product. For example, the number of records in the resultSet was not implemented until v6r1 so "Records 0" shows up on the Viewer on V5R4. The COZQRYLIB *SRVPGM was missing from the posted build for a short period. If you received a message indicating that it could not find COZQRYLIB, that issue is fixed in this release.
10-Jun-2014- We have rewritten the core SQL engine in C++ to provide better recovery than is available wtih standard C or RPG.
03-Jun-2014- If users do not specify the xxx.PDF suffix when OUTPUT(*PDF) is specified, a file is created without any suffix. Now, Query File searches for a suffix and when there is none, it adds .PDF to the name of the stream file.
02-June-201- Added NAMING(*SYS | *SQL) to RUNQRYF at the request of some of our advanced users.
- Fixed an issue when NAMING(*SQL) with all fields and *ROWID or *RRN special values.
- Enhanced the Interactive Viewer RUNQRYF/QRYF OUTPUT(*) option to be more accurate.
- Added OUTPUT(*TEXT) as an output option to RUNSQLF and RUNQRYF commands. When this option is selected, a text file is created on the IFS with the query file result set. Headings similar to the printed output are created, however no "page width" limitation is incorporated. The entire TEXT output file is treated as one big page with headings appearing only at the top of the text. This is suitable for PC-based software packages or FTP transfer to another server. NOTE: This is plain text/plain ASCII output.
30-May-2014- Added *RRN symbolic name to RUNQRYF. Now you can use *RRN on the WHERE and ORDERBY parameters to select and/or sort by Relative Record number.
- Several bug fixes when OUTPUT(*) is specified have been resolved.
- The maximum record length of the result set when OUTPUT(*) is specified has been increased substancially.
18-May-2014- Mochasoft TN5250 improperly returns the current display mode and the valid device capabilities (i.e., whether DS3 and DS4 modes are supported or only DS3). This causes RUNQRYF to fail when used on a tn5250 device that supports only 80x24 mode. An immediate work-around is to change the TN5250 device to 27x123 capability. This can be found under the Terminal settings. We have a Query File software work-around for this situation, in plan. But it will not be implemented util June 1, 2014. Until that time, we recommend: (A) modifying the Mocha Terminal configuration, (B) use only IBM Client Access or (C) use another 3rd party emulator. We have reported this issue to Mochasoft but are not optimistic of it becoming a priority.
- NOTE: This issue might cause other applications that leverage IBM DSM APIs (Qsn*) to also receive bad information. For example, when a display device supports only 80x24 mode, the TN5250 device reports that it also supports 132x27 mode. Upon switching to that mode, the program receives a device error.
17-May-2014- Corrected an issue with even-length packed decimal field's buffer offset being improperly calculated.
16-May-2014- Added FLD(*RRN) option to RUNQRYF. The relative record number from the database file is listed. This differs from the *ROWID which is the row number within the results set. This option is available on the RUNQRYF command.
- Corrected an issue with OUTPUT(*PRINT | *PDF) via RUNQRYF that could occur when the title was not passed to the print routine. Since its a variable-length field, it was getting a subscript error when issuing %subst(title:1:1)='*' and it was empty. However, comparing it as IF (title = '*') works fine.
- Corrected a problem when STMF(*HOME) was specified with OUTPUT(*PDF). The command was creating a directory named "%u" or "%h" in the HOME folder. This problem has been corrected.
15-May-2014- Corrected issue with "Library COZTOOLS not found." message on systems where COZTOOLS or APPTOOLS are not installed. The issue was merely one of the Query File objects being bound to their service programs using the COZTOOLS library as a qualifier. Now, all Query File objects are bound to service programs using *LIBL.
- Corrected an issue when using RUNQRYF against a remote system file. Previously the existence of the file was always checked, now it is only checked when the remote system is not specified, that is when RDB(*LOCAL) is specified the CHKOBJ command is run against the database file.
- Corrected an issue when an invalid remote system is specified and the remote SQL connection fails, Query File would attempt to close the SQL cursor and issue an exception. Now the attempt to close the cursor is still performed, but the exception is traped and should no longer cause and issue.
- Added a LOGSQL parameter to RUNQRYF to write the generated SQL statement to the joblog. This is in addition to any SQL statements written when OUTPUT(*FILE) is specified. _ Corrected a but when using RUNQRYF with a series of embedded quotes in the WHERE or ORDERBY parameters. The command would fail during the hand off to the RUNSQLF command with an "Invalid characters following SELECT" error. This has been fixed in the current build.
14-May-2014- Corrected issues with the interactive viewer OUTPUT(*) parameter.
- Corrected the default title/subtitles.
13-May-2014- Corrected an issue with positioning to records and paging in the Viewer.
- General navigation bug fixes in the interactive Viewer.
12-May-2014- Posted new build ( file to the website for download.
- Fixed an issue with the OUTFILE name (needed to %TRIMR the lib name).
- Added the COMMIT parameter to RUNQRYF when OUTPUT(*FILE) is specified.
- Removed a redundant SQL message from the joblog. The original SQL message is still logged, and 2nd-level text can be seen with F1.
- Completely rewrote the scan/replace engine in C and C++ for performance and prev-V7R1 compatibility.
- Added OUTPUT(*PDF) STMF and STMFNAME parameters to RUNQRYF (they were already on the RUNSQLF command, but now we've exposed them to RUNQRYF.
- Several internal performance and reliability enhancements.
09-May-2014- Changed RUNQRYF so that it is now a "wrapper" of the Query File command. Previously RUNQRYF and RUNSQLF where two distinct commands, each duplicating the call interfaces to our underlying Query File engine. But this was never our design intent. Now when RUQNRYF is performed, it builds an SQL statement and passes it along with the other parameters to the advanced RUNSQLF command itself. This allows us to improve one component while providing those improvements to both interfaces.
- Provided direct PDF output via OUTPUT(*PDF) parameter. Previously the command would create a SPOOL file, then convert that SPOOL file to a PDF file on the IFS. Now, when OUTPUT(*PDF) is specified, the generated Report is written directly to the IFS as a PDF file, ready to go; no temporary SPOOL file necessary.
02-May-2014- The SQL statement behind the RUNQRYF command's OUTPUT(*FILE) option is now written to the joblog. Some users wanted to know how we do what we do with RUNQRYF. Today we added support to write the RUNQRYF-generated SQL statement to the joblog. Users do not have control over that option at this point in time, however it may be added in the future.
***********- Original Beta Test Release Reported Bugs/Issues
- Problems with pageup/down when OUTPUT(*) is specified. (fixed)
- Supporting *SRVPGM's were qualified/hardcoded to library COZTOOLS. (fixed)
- Packed Decimal(2,0) fields buffer size was incorrectly calculated. (fixed)
- REQ: Provide OUTPUT(*PDF) option for RUNQRYF similar to RUNSQLF command. (announced)
- REQ: Provide OUTPUT(*FILE) option for RUNQRYF similar to RUNSQLF command. (announced)
- REQ: Provide OUTPUT(*TEXT) option for RUNQRYF and RUNSQLF command. (announced)
- REQ: Provide SPLFNAME for RUNQRYF when OUTPUT(*PRINT) is specified. (accepted)
- When OUTPUT(*PDF) is specified, with STMF(*HOME) the home directory was not being returned correctly. (fixed)
- Mocha TN5250 emulator with 80x24 display-only enabled fails with OUTPUT(*). (circumvented)