SQL iQuery™ Quick Start
A few examples of using SQL iQuery's command line tool and mobile/web component are illustrated here.
More examples are available here.
To learn about the SQL iQuery Scripting Tool, click one of the links below.
To generate some cool-looking HTML output, use RUNiQRY and specify OUTPUT(*HTML).
==> RUNIQRY SQL('SELECT CUSNUM,LSTNAM,CITY,STATE,ZIPCOD,BALDUE,CDTDUE FROM QIWS/QCUSTCDT') OUTPUT(*HTML)
OUTPUT(*HTML) When Viewed with a Browser:
You can save the HTML to the IFS and view it there to review the results. This is what it looks like:
Now let's generate a more compatible Excel file. We have an Excel page with specifics on CSV and our Excel format.
==> RUNIQRY SQL('SELECT CUSNUM,LSTNAM,CITY,STATE,ZIPCOD,BALDUE,CDTDUE FROM QIWS/QCUSTCDT') OUTPUT(*EXCEL)
After dragging the resulting Excel file from the IFS to your desktop:
Next, I simply double click on it to open it in Excel or another Spreadsheet that I have installed:
The above image was take immediately after opening the file, no reformatting is done at all. Note the column headings are included and in bold, and numeric fields appear correctly. Also, DB2 DATE fields are converted into Excel date integers and formatted as a date so you rarely have to screw around with the data after sending it off to Excel.
SQL iQuery's standard output options (* or *PRINT) produces some pretty cool results too:
==> RUNIQRY SQL('SELECT CUSNUM,LSTNAM,CITY,STATE,ZIPCOD,BALDUE,CDTDUE FROM QIWS/QCUSTCDT')
OUTPUT(*) - Displayed Result:
Changing it up a bit is easy. Need it in CSV format? Just alter then OUTPUT parameter, no other work required!
==> RUNIQRY SQL('SELECT CUSNUM,LSTNAM,CITY,STATE,ZIPCOD,BALDUE FROM QIWS/QCUSTCDT') OUTPUT(*CSV)
12 rows selected.
12 records + 1 heading lines written to /home/cozzi/QCUSTCDT.CSV
Viewing the results on the IFS, looks like this:
==> RUNIQRY SQL('SELECT CUSNUM,LSTNAM,CITY,STATE,ZIPCOD,BALDUE,CDTDUE FROM QIWS/QCUSTCDT') OUTPUT(*JSON)
The above shows a couple of "records" in JSON format. In addition to the rows of data, we also include a column attributes node named attr that contains information about the fields, such as type, length, name, and column headings. Here's a piece of that section of the file.
Note the main node is named "rowset" within it are the following data items:
- tblname - Base File Name
- libname - Base Schema (library) name
- attr - Field attributes (an array, one for each field)
- name - Field name
- type - Datatype as a text
- datatype - 1-character "RPG-Style" data type code
- length - Field length
- decimals and decpos - Number of decimal positions
- width - maximum number of columns field's content occupies.
- varchar - '1' = Field is VARCHAR (or VARLEN in DDS)
- allownull - '1' = Field is null capable (NULL or ALWNUL in DDS)
- alias - Long field name
- colhdg - Column Headings (an array of up to 3 elements)
- row - Field content (an array, one for each row in the
- FIELD - Field name/content pairs. One for each field in the result set.
- rowcount - Number of rows returned in the result set.
rowset.row.CUSNUM rowset.row.LSTNAM rowset.row.CITY rowset.row.STATE rowset.row.ZIPCOD rowset.row.BALDUE rowset.row.CDTDUE
To determine the field's attributes (type, length and/or column headings, etc.) for a field in the returned resultset:
rowset.attr.name rowset.attr.type rowset.attr.length rowset.attr.decimals rowset.attr.width rowset.attr.colhdg rowset.attr.colhdg rowset.attr.colhdg
Here, the attributes of the first field "CUSNUM" are accessed.