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 QIQMACRO and that file must be in a user 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.

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 QIQMACRO 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 customs macros, 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.

Cozzi SQL iQuery (2COZ-IQ4) Macro List
iQuery Macros are evoked by running:
65 records retrieved.
ACTJOBList Active Jobs
ALLOBJUsers with *ALLOBJ authority and not *DISABLED
ALLUSRList User Profiles
CACHEDisplay the Cache Batteries status
CACHEBATDisplay the Cache Batteries status (alt)
CCSIDShows the CCSID related values
CHKDUPOBJCheck for Duplicated Objects in 2 libraries
CMDLISTList of Command Info in *CURLIB or other library
COMMENTSAdd Comments to SQL iQuery UDF/UDTFs (run once)
COZTOOLSList Programs that bind to COZZI libs (long run)
COZXFUNCList Cozzi SQL UDF/UDFT not installed in STD Libs
COZXREFList Programs that bind to COZZI libs (long run)
CRTIQLOGCreate SQL iQuery log database table (run once)
DB2PTFDB2 Group PTF Level
DEMOSQL iQuery simple demo using QIWS.QCUSTCDT
DFTPWDCheck for Users Potentially with Default Password
DMGOBJDamaged Objects Report
DROPIQUDFList Cozzi SQL UDF/UDFT not installed in STD Libs
DSKSimilar to WRKDSKSTS but with Mirroring Info
DSPJRNDisplay Journal
FUNCDisplay SQL iQuery Functions List
GRPPRFGroup Profile Membership list
HISTLocal IBM i History Log (last 24 hours)
IBMBLOGIBM Db2 for i Blog Article Links
INTERACTDisplay All Interactive Jobs
IQVERDisplay the current SQL iQuery Ver
IQVRMDisplay the current SQL iQuery Ver
JOBATTRJob Attributes
JOBCMDList Commands Run in Job SETVAR((JOB ...))
JOBDList job descriptions
JOBLOGJoblog table Function QSYS2.JOBLOG_INFO(...)
JOBLOGEXJoblog table Function QSYS2.JOBLOG_INFO(...)
JOBSCDCheck Job Scheduler Entry Last Submitted Status
JOBSCDEList Job Scheduler Entries
JRNDBFList User *FILE's being Journalled (long running)
LASTUSEDProgram Last Used Date/Time -- iQuery OBJ_LIST
LIBLCurrent Job''s Library List
LICPGMInstalled License Programs (a better view)
LOGSQL iQuery Log File query SETVAR((USER xxxxx))
MACROSSQL iQuery UDTF/UDF/Procs installed system wide
MCHINFOMachine Type,Model,Proc Group,Feature Code,Serial
MSGWList Jobs in MSGW (Message Wait) Status
OBJLISTObject list using IBM's Object Statistics UDTF
OBJSTRUCTExploded Object List (*MODULE and *SRVPGM list)
PTFBKPLVLBack-level Group PTFs
PTFGRPPTF Group Levels
SIGNONSQL iQuery macro to list recent User Sign On/Off
SPCVALSpecial Values Query
STACKDisplay Call Stack
SYSSystem Information
SYSVALList all System Values and their Value
TOPHOGSTop Storage Hogs
TOPLIMITSTop 3 Consumption Points
USERAlt version of *USRPRF macro
USERSAlt version of *USRPRF macro
USRGRPList each User Profile's Group Profiles
USRPRFList Non-IBM User Profiles
VERAbbr version of *VERSION Macro
VERSIONDisplay IBM i VxRyMz & TR level along w/iQuery ver
VRMAbbr version of *VERSION Macro

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