Support email

Contact Us

SQL iQuery Logo

SQL iQuery

Macro Support

SQL iQuery macros provide a shortcut way to run lengthy SQL statements that may or may not be wrapped up in SQL iQuery Script statements. The benefit is that it is shorter syntax to run these lengthy statements, it provides a way to "name" these scripts, provides a level of indirection, and you have the added advantage of using all the OUTPUT and other options of the SQL iQuery engine. Need a list of Active Jobs in Excel format? No problem!


Normally, to run an SQL statement, you specify it on the SQL parameter of the RUNIQRY command as follows:


You can also save the SQL statement into a source member and recall it later by referencing the source member name, as follows:


Another great feature in SQL iQuery allows users to store SQL statements in source members and easily recall them using the Macro syntax. This is a kind of "short cut" to the SQL statements and iQuery Script stored in the source member. We call this feature Macro support.

Using the RUNIQRY command, users may run pre-written SQL statements using the member name and source file. But a short cut approach is provided in the format of Macro syntax. To use it, the member name is specified on the SQL parameter of RUNiQRY and is prefixed with an asterisk. The source member must exist in a source file named QSQLMACRO and that file must be in a library on the library list.

There are a number of macros shipped with SQL iQuery (see below) they are stored in the QIQMACRO source file in library IQUERY.

If the macro name does not exist in QSQLMACRO on the library list, then QIQMACRO in library IQUERY is searched for the macro name. This means user-written macro names will override those supplied with the SQL iQuery product.

For example, the source member named DEMO in source file QIQMACRO contains: SELECT * FROM QIWS.QCUSTCDT; along with a few SQL iQuery Script statements. To run this macro, the following RUNIQRY command could be used:


When this macro is run, SQL iQuery first checks for the macro name as a member in file QSQLMACRO on the library list. If it does not locate a member with that name, it then searches source file QIQMACRO in the SQL iQuery product library (IQUERY) for the member.

Basically this allows users to override the shipped Macro names with their own custom macro, without changing those shipped with SQL iQuery.

SQL iQuery Macros

The SQL iQuery product ships with the following Macros. Some are for testing/debugging purposes, but most are for daily use.

To use a macro, specify the Macro name with a leading asterisk. For example:


This runs the HIST macro and sends the output to the display (by default).

Another macro that I use frequently is the *MCHINFO (Machine Info) macro. It displays the system serial number, machine type, model number, processor group, feature code, and IBM i vrm all in one place.

Note: You can scroll through this table using your mouse roller/scroll wheel. Oh, and FYI, this HTML Scrolling Table is included in SQL iQuery at no additional cost.

SQL iQuery (2COZ-IQ5) Macro List
iQuery Macros are evoked by running:
RUNiQRY *macroName e.g., RUNIQRY *ACTJOB
87 records retrieved.
ACTJOB List Active Jobs - Alias *ACTJOBS
ACTJOBS List Active Jobs (similar to WRKACTJOB)
ALLOBJ List Users with *ALLOBJ authority
ALLOBJAUT List Users with *ALLOBJ authority - ALIAS *ALLOBJ
ALLUSR List All User Profiles
CACHE Display Cache Batteries status
CACHEBAT Display Cache Batteries status (see CACHE macro)
CCSID List the CCSID values for "this" job
CHGTODAY List Files changed "today" SETVAR((LIB xxx))
CHKDUPOBJ List Duplicate objects for 2 different libraries
CMDLIST List Command Definition Information
COMPLIB Compare Objects in two libraries (smallest first)
COZRPGFREEList Programs that bind to Legacy RPGFREE *SRVPGM
COZTOOLS List Programs that bind to COZTOOLS (long running)
COZXFUNC List SQL iQuery UDF/UDTF not installed in STD Libs
COZXREF List PGMs that bind to any COZZI Libs (long run)
CRTIQLOG Create SQL iQuery log database table (run once)
DB2PTF DB2 Group PTF Level
DFTPWD Check for Users Potentially with Default Password
DMGOBJ Damaged Objects Report
DROPIQUDF Drop iQuery Func not in iQuery or related libs
DSK Similar to WRKDSKSTS but with Mirroring Info
DSPJRN Display Journal
FLDLIST Display File Field Descriptions
FUNC Display SQL iQuery Functions List - Alias *FUNCS
FUNCS Display SQL iQuery Functions List
GRPPRF Group Profile Membership list
HIST Local IBM i History Log (last 24 hours)
HOME List Files/Folders in my home dir
IBMBLOG IBM Db2 for i Blog Article Links
INTER Display All Interactive Jobs
INTERACT Display All Interactive Jobs
IQUERY List *PGM/*SRVPGM using SQL iQuery product library
IQVER Display the current SQL iQuery Ver
JOBATTR Job Attributes for this job via JOB_ATTR() UDF
JOBCMD List Commands Run in Job. Use SETVAR((JOB <job>))
JOBD List job descriptions
JOBLOG Joblog table Function QSYS2.JOBLOG_INFO(...)
JOBLOGEX Joblog table Function QSYS2.JOBLOG_INFO(...)
JOBSCD Check Job Scheduler Entry Last Submitted Status
JOBSCDE List Job Scheduler Entries
JOBSCDPGM List Job Scheduler Entry Program to Call
JRNDBF List User *FILE's being Journalled (long running)
LASTIPL Display last system start date/time (last IPL)
LASTSIGNONList Non-IBM User Profiles
LASTUSED Program Last Used Date/Time -- iQuery OBJ_LIST
LIBL Current Job's Library List
LIBSIZE Library Sizes
LICPGM Installed License Programs (a better view)
LOG SQL iQuery Log File query SETVAR((USER xxxxx))
MACRO SQL iQuery Macro List (this member list) ALIAS
MACROS SQL iQuery Macro List (this member list)
MBRLIST List of Members in the specified file
MCHINFO Machine Type,Model,Proc Group,Feature Code,Serial
MSGW List Jobs in MSGW (Message Wait) Status
OBJLIST Object list using IBM's Object Statistics UDTF
OBJOWNER Object list of Objects by Owner, Creator and Lib
OBJSTRUCT Exploded Object List (*MODULE and *SRVPGM list)
OUTQ List OUTQ (output queues) with SPOOL Files
PTFBKPLVL Back-level Group PTFs
PTFGRP PTF Group Levels
RPGFREE List Programs that bind to Legacy RPGFREE *SRVPGM
SIGNON User SignOn/SignOff History
SPCVAL Special Values Query
SPLF List my spool files
SQLINFO List SQL Statements used in programs
STACK Display Call Stack
SYS System Information
SYSINFO Alt version of *MCHINFO macro
SYSVAL List all System Values - Alias for *SYSVALS
SYSVALS List all System Values
TOPHOGS Top Storage Hogs
TOPLIMITS Top 3 Consumption Points
USER List User Profile Attributes - Alias for *USERS
USERS List User Profile Attributes
USRGRP List each User Profile's Group Profiles
USRPRF List User Profile Attributes - Alias for *USERS
VER Display IBM i Version/Release/TR Alias *VERSION
VERSION Display IBM i Version/Release/TR
VRM Display IBM i Version/Release/TR Alias *VERSION
WATCH List active Watches from STRWCH (ALIAS:WATCHLIST)
WATCHLIST List active Watches from STRWCH

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