iQuery

www.cozTools.com

www.MidrangeNews.com

Contact Us

COZTOOLS

SQL iQuery

Latest Enhancements

Note: updates included in our weekly build are listed in our Change Log

Version 3.2 Features

  1. Native Excel .XLS output
  2. CSV_VAL() UDF - Parse/Read CSV files from the IFS
  3. OBJ_LIST(), OBJ_DMG(), LIB_LIST() UDTFs
  4. FOREACH to read/loop through a dataset in iQuery Script is now supported
  5. Level-Break Output to control when a column is printed ("only on changed data")
  6. #ifdef and "if defined()" directives in iQuery Script to condition code based on definitions
  7. Version, Release level symbols for #ifdef and "if defined()" similar to RPG
  8. SQL VALUES INTO is now supported in iQuery Script
  9. Several new built-in functions:
    •  SCAN, SCANI, DTAARA, SYSVAL, USRSPC, and SST
  10. SQL CONNECT TO, CONNECTION RESET are now supported in SQL iQuery Script

Version 3.0 and 3.1 Features

  1. Write once and output to any available format
  2. SQL iQuery Scripting language
  3. Web-based SQL Capability with direct-to-web output
  4. JSON (JavaScript Object Notation) Output
    • To the IFS or directly to the web.
  5. HTML Output
    • To the IFS or directly to the web
  6. EXCEL Output
    • To the IFS
  7. Column Totals for OUTPUT(*PRINT | *PDF | *Excel)
    • Summary Columns as well as derived totals (using our expression component)
  8. New UDFs and (stored) Procedures are being shipped.

More details on the enhancements in our latest version available here.

Recent Features

This is a list of the top features of SQL iQuery

  1. Awesome Excel Output with Fonts and Column Totals
  2. 10x faster conversion to CSV.
  3. Basic XML output.
  4. Redirect Interactive Results to Printer without rerunning the query.
  5. Support for a default database library ("RDB Collection") is added.
  6. Column Totals for OUTPUT(*PRINT) are available.
  7. User Defined Functions for Date Conversion and Decimal Editing.
  8. Omit Field parameter added to the RUNIQRY command.
  9. Added a new Commitment Control *AUTO ("Auto Commit") option.
  10. Added new user-specified OPTIMIZE FOR xx ROWS parameter.
  11. Output Options have been added to generate RPG IV I/O source (D Specs) from SQL SELECT statements.
  12. Special "macro" access to IBM DB2 SQL Services.

SQL iQuery for IBM i is an SQL command line tool that allows you to run any SQL statement as a CL command. Run SQL from the Command line, within CL programs, behind menus or even on the Job Scheduler, SQL iQuery can be used to get the job done now, the way your users need it.

SQL iQuery has a one-time-charge (OTC) for IBM i V7.1 and later. There is also a special no-charge edition for IBM i5/OS v5r4m0 and IBM i v6r1m0. See our downloads page for details.

10x Faster Conversion to CSV Format

Now when OUTPUT(*CSV) is requested, SQL iQuery converts a block of records at a time. This is all done in memory (via a User Space) and yields substantial performance improvements. One customer who runs a nightly conversion of several million records to CSV for a BI data warehouse app, reported the job being reduced to 1/10th the original running time.

Basic XML Output Support

A new OUTPUT(*XML) option has been added. This option automatically creates XML Extender statements around the SELECT statement and produces an XML stream file on the IFS. Using XML itself with SQL can be an ugly and complex string of functions, SQL iQuery simplifies this by generating that ugly syntax for you. The RUNIQRY command converts simple SELECT statements to the XML syntax, but is limited to non-derived fields. Using OUTPUT(*XML) with the RUNIQRY command is even easier since the SQL statements generated by RUNIQRY avoid derived fields.

Redirect Output

A number of our users run SQL statements interactively, using the WRKiQRY or RUNIQRY command. They view the results on the green screen, and then use that same SQL statement to send the final product to *PRINT or *PDF output. Now, the interactive SQL iQuery Viewer has a new Function key (F6) and that prompts the user for a new output device, and sends the current SQL result set to that output device without rerunning the query. This allows users to redirect their output without the need to consume valuable resources just to rerun the SQL query. 

Support for DFTRDBCOL (Default Library)

The RUNIQRY (Run SQL using iQuery) command now supports the Default Library parameter. The default library (also referred to as the Default Relational Database Collection) parameter allows you to specify a library to be used by the SQL engine when it encounters an unqualified table (i.e., database file) name within your SQL statement.

Column Totals

One of SQL iQuery's best features in its mini report writer. It can generate a listing and allows customization of the headers to provide that "custom RPG Report" look without writing a single line of RPG. The new COLTOTAL (Column Total) parameter allows you to specify the name of a field ("column") within the SQL query that will be totaled. Alternatively the relative column number within the SQL query may be specified, which is especially usefully for derived columns. Column totals appear at the bottom of the report (printed or PDF output) and are aligned below the relative column of the report. Three asterisks are also included on the total line.

User Defined Functions for Date Conversion and Decimal Editing

Three new UDFs have been included with SQL iQuery 2.5 including CVTDATE (Convert numeric field to true SQL date value), DECDATE (convert true SQL Date value to numeric/decimal value) and DECEDIT (Edit Numeric Result).

  • CVTDATE( numValue [, 'YMD' ] ) - Use this UDF to convert any 6-, 7-, or 8-digit numeric value to a true date data-type. The optional 2nd parameter identifies the input format of the data stored in the numeric variable. It defaults to YMD.
  • DECDATE( dateValue [, 'YMD'] ) - Use this UDF to convert a true SQL Date value to a numeric value. The 2nd parameter identifies the output format for the converted value. It defaults to 'YMD'.
  • DECEDIT( numExpression [, size ] ) - Use this UDF to embed thousands notation in numeric values. Normally only the decimal notation and negative sign are embedded in results from SQL iQuery. This UDF allow users in North America, to include thousands notation along with those other edit values. This UDF is available on IBM i V6R1 and later for users who want to accomplish similar results to the DB2 VARCHAR_FORMAT and CHAR enhancements until those feature are available for IBM i users. Use caution when using this function as SQL does not provide a method to control the length of the resulting value, therefore all DECEDIT functions return a 96-position VARCHAR value in the resultset. This can make for some undesirable reports or inquiries.  One work around is to wrap the DECEDIT itself in the SQL CHAR function with the desired length specified. For example: CHAR( decedit( sales, 15),15)  In this example, the field SALES is returned right-justified within a 15-byte result buffer, and the CHAR function truncates that return buffer from its normal 96-bytes to the 15 positions desired.

Omit Field Parameter on RUNIQRY

The RUNIQRY command is a quick and easy way to run an SQL "SELECT * FROM XYZ" statement without the need to enter the SQL statement. Just RUNIQRY XYZ and press Enter. If you need more granular control, RUNIQRY supports the FLD (included fields) parameter to allow users to specify the list of fields to be included in the output. With SQL iQuery v2.5 the RUNIQRY command supports the OMITFLD (Omit Field) parameter. This is the complement of the existing FLD parameter. Any field name specified on the OMITFLD parameter is not included on the generated SELECT statement. When OMITFLD is NOT specified, a standard "SELECT * FROM XYZ" is generated. When OMITFLD is specified, the list of fields in the corresponding file is generated, and any fields appearing on the OMITFLD parameter are removed from that list. The resulting list is then used to build a lengthy SELECT statement. In our early evaluation testing, most users use OMITFLD when converting database files to CSV format where they want to omit a handful of fields from the result set while retaining the vast majority of fields.

*AUTO Commitment Control

The new COMMIT(*AUTO) option allows users to have any INSERT/UPDATE/DELETE statements automatically committed when commitment control is active on the tables/files and ignored when no commitment control is detected for those files.

OPTIMIZE FOR xx ROWS Parameter

The RUNIQRY command now includes an OPTIMIZE FOR (OPTIMIZE) parameter that accepts a specific number of records and is used by the SQL query engine for result set optimization. The default is OPTIMIZE(50) but any number or *ALL or *NONE may be specified. When OPTIMIZE(*ALL) is specified, no "OPTIMIZE FOR" clause is added and the RUNIQRY command receives an OPTIMIZE(*ALLIO) parameter that causes the SQL query engine to optimize the query for all I/O (useful for reports, csv options, etc.) When OPTMIZE(*NONE) is specified, the RUNIQRY command receives the default value for the OPTIMIZE parameter.

RPG IV Source Code Generation

When using embedded SQL in RPGIV, one of the challenges is getting the host variables setup so that your FETCH statement have host variable names into which the data can be returned. SQL iQuery can help with that. The new OUTPUT(*RPGIO | *RPGDS) options generate RPG Input and Output specifications or RPG Definition ("D") Specs in the form of a data structure, for the resulting SELECT statement. The Statement itself is not processed, but it is PREPAREd and the resulting format is extracted to produce RPG source. When OUTPUT(*RPGxx) is specified, the OUTFILE and OUTMBR parameters of the RUNIQRY command must specify the source file and member into which the generated source code is stored.

Special Access to IBM DB2 SQL Services

SQL iQuery now includes a source file named QIQMACROS that contains source members with embedded SQL statements. These SQL statements call IBM DB2 for i Services and provides a short-cut or macro-like capability for running these services. For example,  to view the currently installed DB2 PTF Group Level, IBM provides this information via the GROUP_PTF_INFO table in QSYS2. The source member PTFGROUP contains an SQL statement to run a query over that table. To access the statement, use the member name prefixed with an asterisk. For example: RUNIQRY *PTF several other source members are available and users may add or modify what is there. While new SQL iQuery macros will be added as necessary, the list of those bundled with the v2.5 build include:

  • RUNIQRY SQL(*USR) - A concise list of User Profiles and pertinent user profile information.
  • RUNIQRY SQL(*PTF) - The current DB2 for i Group PTF Level.
  • RUNIQRY SQL(*SYS) - IBM i version/release level, CPU cores, and RAM information.
  • RUNIQRY SQL(*TCPIP) - TCP/IP Related information about your system.
  • RUNIQRY SQL(*DSK) - Disk Drive Information.

No-Charge Licensing for V5R4M0 and V6R1M0

SQL iQuery has is chargeable license program PROD ID 2COZIQ3. The one-time-charge (OTC) is listed on our pricing page. However, Cozzi Productions has always waived the license fee for IBM i5/OS V5R4M0 users. With the announcement of SQL iQuery v2.2 this OTC is also waived for IBM i V6R1M0. The reasoning is that users of older releases are, by definition not spending money on new software. So we want to provide our software to them at no charge. However, this no-charge license expires when their system is upgraded to or replaced with a system running IBM i V7R1M0 (v7.1) or later. If they never upgrade their system, the no-charge license never expires.

Check out SQL iQuery for yourself. Download our free trial today.

Copyright © 2014 Cozzi Productions, Inc. All Rights Reserved.