Support email

Contact Us

SQL iQuery Logo

SQL iQuery

Macro Support

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 in a source member and recall it later, using the following syntax:


A great feature in SQL iQuery allows users to store SQL statements in source member and more easily recall them. We call this our "SQL Macro" feature or simply, Macros.

Using the RUNIQRY command, users may run pre-defined SQL statements using the member name as a "macro" identifier. To do so, the member name is specified on the SQL parameter, prefixed with an asterisk. User-defined macros are stored in QSQLMACRO on the library list, whereas SQL iQuery's built-in macros are stored elsewhere. For example to run the SQL iQuery-provided USRPRF (User Profile Listing) macro, type the following:


This statement runs the SQL statement stored in the source member named USRPRF in the source file named QIQMACRO in the SQL iQuery product library.

There are a number of macros shipped with SQL iQuery (see below) and end-users may create their own. User-created Macros should be stored in source file QSQLMACRO in a library on the library list.

For example, if the source member named SAMPLE is created in source file QSQLMACRO and contains: SELECT * FROM QIWS/QCUSTCDT; then following RUNIQRY command could be used to run that SQL statement as follows:


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

The SQL iQuery product ships with the following Macros. Some are for testing/debugging the product as well as providing information on your system, but most are for daily use.

Click the Macro name to view the SQL iQuery statement behind the macro.

Powered by COZTOOLS find out more

Click on a name in the list to view its contents.
Source Viewer © 2004 Robert Cozzi, Jr. All rights reserved.

MemberLast ModifiedViewsText
ACTJOB2018-06-20 16.51.52804List Active Jobs
ALLUSR2018-06-20 16.51.52735List User Profiles
CACHE2018-06-20 16.51.5232Display the Cache Batteries status
CACHEBAT2018-06-20 16.51.5230Display the Cache Batteries status
CMPDUPOBJ2018-06-20 16.51.5220Check for Duplicated Objects in 2 libraries
COMMENTS2018-06-20 16.51.5217Creates COMMENTS ON SQL iQuery UDF/UDTF/Procedures
CRTIQLOG2018-06-20 16.51.5216Run once: Create SQL iQuery log database table
DB2PTF2018-06-20 16.51.52752DB2 Group PTF Level
DEMO2018-06-20 16.51.52193SQL iQuery simple demo using QIWS.QCUSTCDT
DFTPWD2018-06-20 16.51.52670Check for Users Potentially with Default Password
DMGOBJ2018-06-20 16.51.52594Damaged Objects Report
DSK2018-06-20 16.51.52714Disk Unit Report
DSPJRN2018-06-20 16.51.52469Display Journal
FUNC2018-06-20 16.51.5215SQL iQuery UDTF/UDF/Procs installed system wide
GRPPRF2018-06-20 16.51.52710Group Profile Membership list
HIST2018-06-20 16.51.52190IBM History Log
IBMBLOG2018-06-20 16.51.52877IBM Db2 for i Blog Article Links
INTERACT2018-06-20 16.51.52532All Interactive Jobs
IQVER2018-06-20 16.51.5230SQL Macro to display the current SQL iQuery Versio
IQVRM2018-06-20 16.51.5230SQL Macro to display the current SQL iQuery Versio
JOBATTR2018-06-20 16.51.52343Job Attributes
JOBCMD2018-06-20 16.51.5215List Commands Run in Job SETVAR((JOB ...))
JOBLOG2018-06-20 16.51.522950Joblog table Function QSYS2.JOBLOG_INFO(...)
JOBLOGEX2018-06-20 16.51.5217Joblog table Function QSYS2.JOBLOG_INFO(...)
JOBSCD2018-06-20 16.51.52721Check Job Scheduler Entry Last Submitted Status
JOBSCDE2018-06-20 16.51.52719List Job Scheduler Entries
JRNDBF2018-06-20 16.51.5263List User *FILE's being Journalled (long running)
KEYFLD2018-06-20 16.51.52244Key Fields List SETVAR((FILE MYFILE) (LIB MYLIB))
LASTUSED2018-06-20 16.51.52579Program Last Used Date/Time -- iQuery OBJ_LIST
LIBL2018-06-20 16.51.52719Current Job''s Library List
LICPGM2018-06-20 16.51.52570Installed License Programs (a better view)
LOG2018-06-20 16.51.52181SQL iQuery Log File query SETVAR((USER xxxxx))
MBRLIST2018-06-20 16.51.52439MBRL SETVAR((LIB ll) (FILE ff) (MBR mm) (TEXT tt))
MCHINFO2018-06-20 16.51.52250Machine Type,Model,Proc Group,Feature Code,Serial
MSGW2018-06-20 16.51.52769List Jobs in MSGW (Message Wait) Status
OBJLIST2018-06-20 16.51.527315Object list using IBM's Object Statistics UDTF
OBJSTRUCT2018-06-20 16.51.52605Exploded Object List (*MODULE and *SRVPGM list)
PTFBKPLVL2018-06-20 16.51.52660Back-level Group PTFs
PTFGRP2018-06-20 16.51.52698PTF Group Levels
RPGFREE2018-06-20 16.51.5231Search for COZTOOLS/RPGFREE *SRVPGM Where Used
SIGNON2018-06-20 16.51.5225SQL iQuery macro to list recent User Sign On/Off
SPCVAL2018-06-20 16.51.52748Special Values Query
SYS2018-06-20 16.51.52630System Information
SYSVAL2018-06-20 16.51.52672List all System Values and their Value
TCPIP2018-06-20 16.51.52720TCP/IP Info
TOPHOGS2018-06-20 16.51.52702Top Storage Hogs
TOPLIMITS2018-06-20 16.51.52602Top 3 Consumption Points
USERS2018-06-20 16.51.52115List Non-IBM User Profiles
USRGRP2018-06-20 16.51.52701List User Profile Group Profiles
USRPRF2018-06-20 16.51.52693List Non-IBM User Profiles
VER2018-06-20 16.51.5224Display IBM i VxRyMz & TR level along w/iQuery ver
VERSION2018-06-20 16.51.5225Verbose macro name for *VER macro

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