SQL iQuery™
Macro Support
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 in a source member and recall it later, using the following syntax:
RUNIQRY SRCMBR(SAMPLE) SRCFILE(MYLIB/QSQLSRC)
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:
RUNIQRY *USRPRF
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:
RUNIQRY *SAMPLE
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.
| Member | Last Modified | Views | Text |
| ACTJOB | 2018-06-20 16.51.52 | 804 | List Active Jobs |
| ALLUSR | 2018-06-20 16.51.52 | 735 | List User Profiles |
| CACHE | 2018-06-20 16.51.52 | 32 | Display the Cache Batteries status |
| CACHEBAT | 2018-06-20 16.51.52 | 30 | Display the Cache Batteries status |
| CMPDUPOBJ | 2018-06-20 16.51.52 | 20 | Check for Duplicated Objects in 2 libraries |
| COMMENTS | 2018-06-20 16.51.52 | 17 | Creates COMMENTS ON SQL iQuery UDF/UDTF/Procedures |
| CRTIQLOG | 2018-06-20 16.51.52 | 16 | Run once: Create SQL iQuery log database table |
| DB2PTF | 2018-06-20 16.51.52 | 752 | DB2 Group PTF Level |
| DEMO | 2018-06-20 16.51.52 | 193 | SQL iQuery simple demo using QIWS.QCUSTCDT |
| DFTPWD | 2018-06-20 16.51.52 | 670 | Check for Users Potentially with Default Password |
| DMGOBJ | 2018-06-20 16.51.52 | 594 | Damaged Objects Report |
| DSK | 2018-06-20 16.51.52 | 714 | Disk Unit Report |
| DSPJRN | 2018-06-20 16.51.52 | 469 | Display Journal |
| FUNC | 2018-06-20 16.51.52 | 15 | SQL iQuery UDTF/UDF/Procs installed system wide |
| GRPPRF | 2018-06-20 16.51.52 | 710 | Group Profile Membership list |
| HIST | 2018-06-20 16.51.52 | 190 | IBM History Log |
| IBMBLOG | 2018-06-20 16.51.52 | 877 | IBM Db2 for i Blog Article Links |
| INTERACT | 2018-06-20 16.51.52 | 532 | All Interactive Jobs |
| IQVER | 2018-06-20 16.51.52 | 30 | SQL Macro to display the current SQL iQuery Versio |
| IQVRM | 2018-06-20 16.51.52 | 30 | SQL Macro to display the current SQL iQuery Versio |
| JOBATTR | 2018-06-20 16.51.52 | 343 | Job Attributes |
| JOBCMD | 2018-06-20 16.51.52 | 15 | List Commands Run in Job SETVAR((JOB ...)) |
| JOBLOG | 2018-06-20 16.51.52 | 2950 | Joblog table Function QSYS2.JOBLOG_INFO(...) |
| JOBLOGEX | 2018-06-20 16.51.52 | 17 | Joblog table Function QSYS2.JOBLOG_INFO(...) |
| JOBSCD | 2018-06-20 16.51.52 | 721 | Check Job Scheduler Entry Last Submitted Status |
| JOBSCDE | 2018-06-20 16.51.52 | 719 | List Job Scheduler Entries |
| JRNDBF | 2018-06-20 16.51.52 | 63 | List User *FILE's being Journalled (long running) |
| KEYFLD | 2018-06-20 16.51.52 | 244 | Key Fields List SETVAR((FILE MYFILE) (LIB MYLIB)) |
| LASTUSED | 2018-06-20 16.51.52 | 579 | Program Last Used Date/Time -- iQuery OBJ_LIST |
| LIBL | 2018-06-20 16.51.52 | 719 | Current Job''s Library List |
| LICPGM | 2018-06-20 16.51.52 | 570 | Installed License Programs (a better view) |
| LOG | 2018-06-20 16.51.52 | 181 | SQL iQuery Log File query SETVAR((USER xxxxx)) |
| MBRLIST | 2018-06-20 16.51.52 | 439 | MBRL SETVAR((LIB ll) (FILE ff) (MBR mm) (TEXT tt)) |
| MCHINFO | 2018-06-20 16.51.52 | 250 | Machine Type,Model,Proc Group,Feature Code,Serial |
| MSGW | 2018-06-20 16.51.52 | 769 | List Jobs in MSGW (Message Wait) Status |
| OBJLIST | 2018-06-20 16.51.52 | 7315 | Object list using IBM's Object Statistics UDTF |
| OBJSTRUCT | 2018-06-20 16.51.52 | 605 | Exploded Object List (*MODULE and *SRVPGM list) |
| PTFBKPLVL | 2018-06-20 16.51.52 | 660 | Back-level Group PTFs |
| PTFGRP | 2018-06-20 16.51.52 | 698 | PTF Group Levels |
| RPGFREE | 2018-06-20 16.51.52 | 31 | Search for COZTOOLS/RPGFREE *SRVPGM Where Used |
| SIGNON | 2018-06-20 16.51.52 | 25 | SQL iQuery macro to list recent User Sign On/Off |
| SPCVAL | 2018-06-20 16.51.52 | 748 | Special Values Query |
| SYS | 2018-06-20 16.51.52 | 630 | System Information |
| SYSVAL | 2018-06-20 16.51.52 | 672 | List all System Values and their Value |
| TCPIP | 2018-06-20 16.51.52 | 720 | TCP/IP Info |
| TOPHOGS | 2018-06-20 16.51.52 | 702 | Top Storage Hogs |
| TOPLIMITS | 2018-06-20 16.51.52 | 602 | Top 3 Consumption Points |
| USERS | 2018-06-20 16.51.52 | 115 | List Non-IBM User Profiles |
| USRGRP | 2018-06-20 16.51.52 | 701 | List User Profile Group Profiles |
| USRPRF | 2018-06-20 16.51.52 | 693 | List Non-IBM User Profiles |
| VER | 2018-06-20 16.51.52 | 24 | Display IBM i VxRyMz & TR level along w/iQuery ver |
| VERSION | 2018-06-20 16.51.52 | 25 | Verbose macro name for *VER macro |