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 Macro List
iQuery Macros are evoked by running:
RUNiQRY *mmmmmm where mmmmmm is the macro name
ACTGRPList Activation Groups for "this" Job
ACTGRPSList Activation Groups for "this" Job
ACTIVEList Active Jobs or SPOOL Files for a given User
ACTJOBList Active Jobs - Alias *ACTJOBS
ACTJOBSList Active Jobs (similar to WRKACTJOB)
ALIASList All ALIAS objects and their based-ed Table
ALLOBJList Users with *ALLOBJ authority
ALLOBJAUTList Users with *ALLOBJ authority - ALIAS *ALLOBJ
ALLUSRList All User Profiles
CACHEDisplay Cache Batteries status
CACHEBATDisplay Cache Batteries status (see CACHE macro)
CCSIDList the CCSID values for "this" job
CHGOBJChange Object Descriptions
CHGTODAYList Files changed "today" SETVAR((LIB xxx))
CHKDUPOBJList Duplicate objects for 2 different libraries
CHKUDFCheck if all UDF and UDTF were created properly
CLRAGReclaim (delete) activation groups for this job
CMDDFTList Commands with changed parameter defaults
CMDLISTList Command Definition Information
COMPLIBCompare Objects in two libraries (smallest first)
COZRPGFREEList Programs that bind to Legacy RPGFREE *SRVPGM
COZTOOLSList Programs that bind to COZTOOLS (long running)
COZXFUNCList SQL iQuery UDF/UDTF not installed in STD Libs
COZXREFList PGMs that bind to any COZZI Libs (long run)
CPUList Active Jobs by CPU Usage
CRTIQLOGCreate SQL iQuery log database table (run once)
DB2PTFDB2 Group PTF Level
DEMOCSVRead output from *DEMO as a CSV file
DEMOCSV2Read output from *DEMO as a CSV file
DEMOCSV3Read output from *DEMO as a CSV file
DEPFILESQL iQuery Macro -- Alias for DSPDBR
DEPLFDisplay Logical Vies built over Physical file
DEPPFFind Logicals Built of a Physical File
DFTPWDCheck for Users Potentially with Default Password
DISKFList Disk Failure notices from QSYSOPR
DMGOBJDamaged Objects Report
DROPIQUDFDrop iQuery Func not in iQuery or related libs
DSKSimilar to WRKDSKSTS but with Mirroring Info
DSKSTSSimilar to WRKDSKSTS but with Mirroring Info
DSNRemote Database Directory Entry Data Source Names
DSPDBRList Database Relations for a file or member
DSPFFDSQL iQuery Display File Field Description
DSPJRNDisplay Journal
DUPFUNCFind SQL Functions and Procedures on the System
FINDDUPFNFind SQL Functions and Procedures on the System
FINDFLDFind Fields used in Files
FINDFUNCFind SQL Functions and Procedures on the System
FINDJSEFind Job Scheduler Entries
FINDLIBFind Libraries
FINDMBRFind Members
FINDOBJFind Objects
FINDOBJ72Find Objects
FUNCDisplay SQL iQuery Functions List - Alias *FUNCS
FUNCSDisplay SQL iQuery Functions List
GRPPRFGroup Profile Membership list
HISTLocal IBM i History Log (last 24 hours)
HOMEList Files/Folders in my home dir using IFSPATH()
HOMEDIRList Files/Folders in my home dir using IFSDIR
IBMBLOGIBM Db2 for i Blog Article Links
INSTALLPost-Install Create support objects locally
INTERDisplay All Interactive Jobs
INTERACTDisplay All Interactive Jobs
INVALIDPWDList Invalid Signon attempts
IQFUNCSDisplay SQL iQuery Functions List
IQUERYList *PGM/*SRVPGM using SQL iQuery product library
IQVERDisplay the current SQL iQuery Ver
JOBATTRJob Attributes for this job via JOB_ATTR() UDF
JOBCMDList Commands Run in Job. Use SETVAR((JOB <job>))
JOBDList job descriptions
JOBLOGJoblog table Function QSYS2.JOBLOG_INFO(...)
JOBLOGEXJoblog table Function QSYS2.JOBLOG_INFO(...)
JOBSCDCMDList Job Scheduler Entry Command to run
JOBSCDEList Job Scheduler Entries
JOBSCDLASTList Job Scheduler Entry Last Submitted Status
JOBSCDPGMList Job Scheduler Entry called programs
JRNDBFList User *FILE's being Journalled (long running)
LASTIPLDisplay last system start date/time (last IPL)
LASTSIGNONList Non-IBM User Profiles
LASTUSEDProgram Last Used Date/Time -- iQuery OBJ_LIST
LIBLCurrent Job's Library List
LIBSIZELibrary Sizes
LICPGMInstalled License Programs (a better view)
LOGSQL iQuery Log File query SETVAR((USER xxxxx))
MACROSQL iQuery Macro List (this member list) ALIAS
MACROSSQL iQuery Macro List (this member list)
MBRLISTList of Members in the specified file
MCHINFOMachine Type,Model,Proc Group,Feature Code,Serial
MSGWList Jobs in MSGW (Message Wait) Status
MYJOBSList Active Jobs for the User running the macro
NETAList all network attributes
OBJLISTObject list using IBM's Object Statistics UDTF
OBJLOCKSList Object Locks
OBJOWNERObject list of Objects by Owner, Creator and Lib
OBJSTRUCTExploded Object List (*MODULE and *SRVPGM list)
OUTQList OUTQ (output queues) containing SPOOL Files
OUTQEList SPOOL Files for an Output Queue (OUTQ)
PTFBKPLVLBack-level Group PTFs
PTFGRPPTF Group Levels
RCDFMTList files and their record format name(s)
RCLSTGDisplay last Reclaim Storage Statistics
RDBAlias for DSN (Data Source Names) Listing
RMVLIBLEAdd or remove library list entry
RPGFREEList Programs that bind to Legacy RPGFREE *SRVPGM
SIGNONUser SignOn/SignOff History
SPCVALSpecial Values Query
SPLFList my spool files
SQLUSAGEList SQL Statements used in programs
STACKDisplay Call Stack
STGUSERSTop Storage Users (Output to Google Chart)
SYSSystem Information
SYSINFOAlt version of *MCHINFO macro
SYSOBJReturn System Name for a Long SQL Name
SYSVALList all System Values - Alias for *SYSVALS
SYSVALSList all System Values
TOPHOGSTop Storage Hogs
TOPLIMITSTop 3 Consumption Points
UDFList User Defined Functions and Procedures Names
UDTF_LIBDetermine and use SQL TOOLS if installed
USERList User Profile Attributes - Alias for *USRPRF
USERJOBSList Active Jobs for the User running the macro
USERSList User Profile Attributes - Alias for *USRPRF
USRGRPList each User Profile's Group Profiles
USRJOBSList Active Jobs for the User running the macro
USRPRFList User Profile Attributes
VERDisplay IBM i Version/Release/TR Alias *VERSION
VERSIONDisplay IBM i Version/Release/TR
VRMDisplay IBM i Version/Release/TR Alias *VERSION
WATCHList active Watches from STRWCH (ALIAS:WATCHLIST)
WATCHLISTList active Watches from STRWCH
WRKFUNCFind SQL Functions and Procedures on the System
XXCHKCMTCheck if all UDF and UDTF were created properly
XXFINDDMGFind Damaged Objects
141 records retrieved.

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