- Maintenance Log
- Cozzi SQL iQuery for IBM i - Licensed Program (2COZ-IQ3)
- "SQL iQuery" is a Web and 5250 Command Line SQL Tool for IBM i.
- COZZI SQL Query File - Licensed Program (2COZ-QF2) - was deprecated on 01-Mar-2016 the new LPP 2COZ-IQ3 replaces 2COZ-QF2.
Pinned:- PTFs required for iQuery support of UTF-8 input/output data are:
  1. V7R1 PTF - SI61475 & SI61887
  2. V7R2 PTF - SI61129 & SI61155
  3. V7R3 PTF - SI62005 & SI62028
- 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. The database files being queried may be IBM i partition, local or remote; or may exist on non-IBM i databases when additional software drivers are installed.
- The bug fix Log is available separately on this linked page.
- Nightly Build Log
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 trace 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 control this setting, add the following to your HTTD.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 unspecified, 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 TO alias to allow recipient names is supported, as follows:
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)