iQuery

www.cozTools.com

www.MidrangeNews.com

Contact Us

SQL iQuery Logo

SQL iQuery™ for IBM i

Your best choice for IBM i Db2 to EXCEL or the Web's JSON formats

Introducing iQuery™ - The IBM i SQL tool that allows you to create and run modern database queries using SQL and you can even repurpose your existing Query/400 queries for SQL.

With SQL iQuery you can run ad hoc SQL statements (including SELECT) from Command Entry, CL or the Web and generate output that includes, print, display, PDF, Excel XML, JSON, CSV, etc. In addition SQL iQuery includes a powerful scripting language so you can write SQL "programs" to handle tasks that would normally require hundreds or even thousands of lines of RPG IV code.

New! iQuery v4.6 Shipped April 18, 2018

The latest release of our SQL iQuery is available for download. You can get a free 60-day trial automatically when you install SQL iQuery on your system (no registration required). Once you realize you need it you can order it here and we'll email a license key.

To view the latest updates included in the latest release visit our Change Log.

Update: 18 April 2018 - Several new features and enhancements are being introduced, including:

  • New SQL Functions including:
    • CACHEBATTERY() - Display your Cache Battery Status
    • RTVNETA() - Retrieve Network Attributes
    • JOBDATE() - Retrieve the Job's (vs. "system") Date
    • IFSFILE() - Retrieve IFS Text File Contents
    • IFSDIR() - Retrieve files and subdirectors withing the IFS Directory
    • CSV() - Advanced Comma Separated Values Table functions
    • RTVMBRD() - Retrieve Basic Member Description Information
    • RTVCMDD() - Retrieve CL Command Definition Description
    • MBRLIST() - A Queriable Member list
    • OBJ_STRUCT() - Enumerates the objects that make up a specified *PGM or *SRVPGM
    • OBJ_EXPORT() - Lists the Exported Procedures for a specified object
    • OBJ_LIST() - Lists Objects in a Library
      • Priviously Introduced UDFs
      • SYSNAME() - Returns System name
      • SRLNBR() - Returns the System Serial Number
      • FROMHEX() - Folds 2-character hex patterns into one resulting character
      • GETENV() - Returns the value for the specified Environment variable
      • DTAARA() - Returns data from the user-specified *DTAARA object
      • VRM()/VER() - Returns the IBM i Version in VxRyMz format
      • IQVRM() - Returns the iQuery Version in VxRyMz format
  • New SQL iQuery Macros
    • *DB2PTF - Lists the current DB2 for i PTF level installed on "this" system.
    • *ACTJOB - Lists all non-IBM active jobs running in all subsystems.
    • *DFTPWD - List all User Profiles that have a Default Password.
    • *DMGOBJ - List all Damaged Objects.
    • *DSK - List Disk space usages (Similar to WRKDSKSTS).
    • *GRPPRF - Lists the members of each Group Profile on the system.
    • *HIST - Lists all QHST log messages for the last 24-hour period.
    • *CACHE - List the information about the CACHE Batteries being used on the system.
    • *IBMBLOG - Checks for and lists the DB2 for i BLOG article by date published (descending).
    • *INTERACT - Similar to *ACTJOB except includes only those jobs tagged as Interactive.
    • *JOBATTR - Queries the IQUERY.IQ_JOBATTR view to list the current job's attributes.
    • *JOBLOG - Queries the QSYS2.JOBLOG_INFO UDTF to list the messages in "this" job's joblog.
    • *LASTUSED - Lists objects in the specified library by Last Used Date (descending order). By default library IQUERY is used.
    • *LIBL - Lists the library list for the job.
    • *LOG - If you've create the iQuery History Log, then this macro will list the contents of that log.
    • *LICPGM - List the installed Licensed Program and when their license keys expire.
    • *MCHINFO - Lists the Machine type, model number, processor group, system name, system serial number, feature code and IBM i version/release level.
    • *MSGW - List any messages in MSGW (Message Wait) status.
    • *OBJSTRUCT - List the structure (make up) of a program or Service Program. See the IQRYOBJ CL command.
    • *PTFGRP - A great macro that goes out to the IBM site and pulls in and then compares the current PTF level available with the PTF level installed on this system.
    • *SYSVAL - Lists all System Values along with their actual value.
    • *USERS or *USRPRF - Lists information about all User Profiles.
    • *USRGRP - The complement of *GRPPRF, this one shows each user profile and all the group profiles to which it belongs.
    • *VER - Displays the current IBM i version, release and modification level, along with the TR level.
    • *IQVER - Display the current SQL iQuery vesion and release level.
  • New SQL iQuery Features and Capabilities:
    • Default Decimal Precision now default to 63-digits and is controllable.
    • #PAGEBREAK command to control when to skip to a new page in your printed output or to start a new WorkSheet in Excel output.
    • OUTPUT(*EXCEL) Now supports multiple WorkSheet "tabs"
    • Column Totals in Excel are now replicated within each new Worksheet.
    • More formatting capabilities in Excel Columns.
    • #PRTSPACING command controls the number of lines to space between printed lines.
    • Excel output now supports *MAC or *PC or *WINDOWS options for Target Platform designation.
    • print after command allows non-SQL resultSet data to be added to the output of excel or print content.
    • A new #TITLES *NONE option in SQL iQuery Script to supress non-column headings.
    • Major improvements to the SQL iQuery Scripting language.
    • New Job Attributes view named iQuery/IQ_JOBATTR that is built over the JOB_ATTR() UDTF.
    • New Cache Battery view named iQuery/CACHEBATTERY that is built over the CACHEBATTERY() UDTF.
    • A new SQL iQuery SDK allows RPG programs to use the dynamic capabilities of iQuery Script within their own programs.

Special V7R1 No-Charge License Available

If you are on IBM i v7r1, you can install and immediately start using SQL iQuery V4R5M0. It was frozen in late January 2018 and is being made available to all IBM i v7r1m0 customers at no charge for the life of the IBM i V7R1M0 release on their system. This version runs on IBM i V7R1M0 only and does not run on later releases of IBM i. To summarize, this version runs on IBM i v7r1 only, has a $0.00 license fee, and does not work on IBM i v7r2 and later.
Visit our Downloads page to locate and download this special edition as well as our retail version.

Helpful Downloads

The latest version of iQuery focuses on the web. Specifically we are the only solution that allows you to move SQL results directly to JSON files on the IFS or directly to the Web browser for processing by standard web tools, such as JQuery, DataTables, W3Schools, and many others. We can do this because our SQL iQuery product can write the results of your SQL SELECT statements directly to the web browser as JSON content.

Our customers have build entire websites using just SQL iQuery v4 along with HTML and thier favorite JavaScript because iQuery can produce JSON.

When you need something that you can start using immediately, SQL iQuery v4 is your best choice.

But we didn't stop there. SQL iQuery now supports native .XLS Excel output directly allowing customers to transform their native SQL SELECT statement results to EXCEL. You can even email the results directly to your end-users--it's all built-in.

This is the best version we've ever released; it is time to start taking advantage of the great features found in SQL iQuery

SQL iQuery is a modern SQL tool that allows you to immediately view your SQL SELECT statement results on the web, the green screen, PDF, Excel, JSON, XML, CSV and others.

Latest enhancements

New! Free PowerPoint: Using SQL iQuery Scripting (PDF)

Modernize Your Queries using SQL iQuery

SQL iQuery allows you to use standard SQL to build reports, PDFs, Excel files, database outfiles, and web content. You can also replace most legacy Query/400 scripts with iQuery and get the job done faster. In addition, using one or two SQL statements can often help you avoid writing hundreds or even thousands of lines of RPG code--and SQL iQuery gives you the flexibility to modify those statements externally or at runtime.

Our RUNiQRY (Run SQL using iQuery) command is going to be your RPG Developer's new best friend!

The iQuery SQL Scripting tool provides the most powerful SQL Scripting system on the platform.

Key Features

  • Run SQL statements (including SELECT) from the web or Command Entry or CL
  • Run ad hoc statements
  • Run SQL scripts stored in source members or IFS text files
  • Interactive results from Command entry
  • Write once, output to any of our supported file formats (excel, PDF, JSON, XML, csv, outfile, etc.)
  • Read CSV files from the IFS
  • Selectively build FTP Scripts within iQuery Script to transfer data to/from your system

 See what SQL iQuery can do for you

Data Conversion

What good is a tool if every time you need an alternate output format, you have to buy an add-on, install a new feature, or go looking for yet another tool? With SQL iQuery, you simply specify the output format you want and the existing industry standard SQL statement is used to produce the data in that format; nothing's easier. Here's a list of output formats supported by SQL iQuery's Write One and Done interface:

With SQL iQuery, it does not matter what the end-format is going to be. You can prototype the SELECT statement until you get it the way you want it, then decide what the output format will be. Use our interactive viewer to view your results, customize and refine the statement, tweak the titles and then save it to a source file member. When you're ready to run that statement, you decide what output format you need. Need a PDF today but that one special user wants in it Excel? No worries, just run it and set the OUTPUT parameter to *EXCEL and it just works. You're the hero!

Contact Us...

Product Information

  • Name: SQL iQuery™
  • Install Library: IQUERY
  • Product ID: 2COZ-IQ4
  • Built for IBM i: v7.1 and later
  • SQL iQuery CL Command: RUNIQRY and RUNIQRYF

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

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