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!
RUNiQRY *ACTJOB OUTPUT(*EXCEL) EMAIL(BOB@coztools.com)
Normally, to run an SQL statement, you specify it on the SQL parameter of the RUNIQRY command as follows:
RUNIQRY SQL('SELECT * FROM QIWS/QCUSTCDT')
You can also save the SQL statement into a source member and recall it later by referencing the source member name, as follows:
RUNIQRY SRCMBR(SAMPLE) SRCFILE(MYLIB/QSQLSRC)
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:
RUNIQRY *DEMO
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:
RUNiQRY *HIST
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.
| Macro Name |
Macro Description |
|
|---|---|---|
| ACTGRP | List Activation Groups for "this" Job | |
| ACTGRPS | List Activation Groups for "this" Job | |
| ACTIVE | List Active Jobs or SPOOL Files for a given User | |
| ACTJOB | List Active Jobs - Alias *ACTJOBS | |
| ACTJOBS | List Active Jobs (similar to WRKACTJOB) | |
| ALIAS | List All ALIAS objects and their based-ed Table | |
| 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 | |
| CHGOBJ | Change Object Descriptions | |
| CHGTODAY | List Files changed "today" SETVAR((LIB xxx)) | |
| CHKDUPOBJ | List Duplicate objects for 2 different libraries | |
| CHKUDF | Check if all UDF and UDTF were created properly | |
| CLRAG | Reclaim (delete) activation groups for this job | |
| CMDDFT | List Commands with changed parameter defaults | |
| CMDLIST | List Command Definition Information | |
| COMPLIB | Compare Objects in two libraries (smallest first) | |
| COZRPGFREE | List 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) | |
| CPU | List Active Jobs by CPU Usage | |
| CRTIQLOG | Create SQL iQuery log database table (run once) | |
| DB2PTF | DB2 Group PTF Level | |
| DEMO | SQL iQuery Demo using QIWS.QCUSTCDT | |
| DEMOCSV | Read output from *DEMO as a CSV file | |
| DEMOCSV2 | Read output from *DEMO as a CSV file | |
| DEMOCSV3 | Read output from *DEMO as a CSV file | |
| DEPFILE | SQL iQuery Macro -- Alias for DSPDBR | |
| DEPLF | Display Logical Vies built over Physical file | |
| DEPPF | Find Logicals Built of a Physical File | |
| DFTPWD | Check for Users Potentially with Default Password | |
| DISKF | List Disk Failure notices from QSYSOPR | |
| DMGOBJ | Damaged Objects Report | |
| DROPIQUDF | Drop iQuery Func not in iQuery or related libs | |
| DSK | Similar to WRKDSKSTS but with Mirroring Info | |
| DSKSTS | Similar to WRKDSKSTS but with Mirroring Info | |
| DSN | Remote Database Directory Entry Data Source Names | |
| DSPDBR | List Database Relations for a file or member | |
| DSPFFD | SQL iQuery Display File Field Description | |
| DSPJRN | Display Journal | |
| DUPFUNC | Find SQL Functions and Procedures on the System | |
| FINDDUPFN | Find SQL Functions and Procedures on the System | |
| FINDFLD | Find Fields used in Files | |
| FINDFUNC | Find SQL Functions and Procedures on the System | |
| FINDJSE | Find Job Scheduler Entries | |
| FINDLIB | Find Libraries | |
| FINDMBR | Find Members | |
| FINDOBJ | Find Objects | |
| FINDOBJ72 | Find Objects | |
| FINDSPL | Find SPOOLED Files - Alias *FINDSPLF | |
| FINDSPLF | Find SPOOLED Files | |
| 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 using IFSPATH() | |
| HOMEDIR | List Files/Folders in my home dir using IFSDIR | |
| IBMBLOG | IBM Db2 for i Blog Article Links | |
| INSTALL | Post-Install Create support objects locally | |
| INTER | Display All Interactive Jobs | |
| INTERACT | Display All Interactive Jobs | |
| INVALIDPWD | List Invalid Signon attempts | |
| IPLSTS | RUNiQRY *IPLSTS (Last IPL Status) | |
| IQFUNCS | Display SQL iQuery Functions List | |
| 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(...) | |
| JOBSCDCMD | List Job Scheduler Entry Command to run | |
| JOBSCDE | List Job Scheduler Entries | |
| JOBSCDLAST | List Job Scheduler Entry Last Submitted Status | |
| JOBSCDPGM | List Job Scheduler Entry called programs | |
| JRNDBF | List User *FILE's being Journalled (long running) | |
| KEYFIELDS | Key Fields List SETVAR((FILE MYFILE) (LIB MYLIB)) | |
| KEYFLD | Key Fields List SETVAR((FILE MYFILE) (LIB MYLIB)) | |
| KEYLIST | Key Fields List SETVAR((FILE MYFILE) (LIB MYLIB)) | |
| LASTIPL | Display last system start date/time (last IPL) | |
| LASTSIGNON | List 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 | |
| MYJOBS | List Active Jobs for the User running the macro | |
| NETA | List all network attributes | |
| OBJLIST | Object list using IBM's Object Statistics UDTF | |
| OBJLOCKS | List Object Locks | |
| OBJOWNER | Object list of Objects by Owner, Creator and Lib | |
| OBJSTRUCT | Exploded Object List (*MODULE and *SRVPGM list) | |
| OUTQ | List OUTQ (output queues) containing SPOOL Files | |
| OUTQE | List SPOOL Files for an Output Queue (OUTQ) | |
| PTFBKPLVL | Back-level Group PTFs | |
| PTFGRP | PTF Group Levels | |
| RCDFMT | List files and their record format name(s) | |
| RCLSTG | Display last Reclaim Storage Statistics | |
| RDB | Alias for DSN (Data Source Names) Listing | |
| RMVLIBLE | Add or remove library list entry | |
| RPGFREE | List Programs that bind to Legacy RPGFREE *SRVPGM | |
| SIGNON | User SignOn/SignOff History | |
| SPCVAL | Special Values Query | |
| SPLF | List my spool files | |
| SQLUSAGE | List SQL Statements used in programs | |
| STACK | Display Call Stack | |
| STGUSERS | Top Storage Users (Output to Google Chart) | |
| SYS | System Information | |
| SYSINFO | Alt version of *MCHINFO macro | |
| SYSOBJ | Return System Name for a Long SQL Name | |
| SYSVAL | List all System Values - Alias for *SYSVALS | |
| SYSVALS | List all System Values | |
| TCPIP | TCP/IP Info | |
| TOPHOGS | Top Storage Hogs | |
| TOPLIMITS | Top 3 Consumption Points | |
| UDF | List User Defined Functions and Procedures Names | |
| UDTF_LIB | Determine and use SQL TOOLS if installed | |
| USER | List User Profile Attributes - Alias for *USRPRF | |
| USERJOBS | List Active Jobs for the User running the macro | |
| USERS | List User Profile Attributes - Alias for *USRPRF | |
| USRGRP | List each User Profile's Group Profiles | |
| USRJOBS | List Active Jobs for the User running the macro | |
| USRPRF | List User Profile Attributes | |
| 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) | |
| WATCHES | List active Watches from STRWCH (ALIAS:WATCHLIST) | |
| WATCHLIST | List active Watches from STRWCH | |
| WRKFUNC | Find SQL Functions and Procedures on the System | |
| XXCHKCMT | Check if all UDF and UDTF were created properly | |
| XXFINDDMG | Find Damaged Objects | |
| 141 records retrieved. | ||