COMMANDS

Support email

www.MidrangeNews.com

Contact Us

COZTOOLS

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 QMACSRC 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 QIQMACROS 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 QMACSRC in a library on the library list.

For example, if the source member named SAMPLE is created in source file QMACSRC 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 QMACSRC 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 and your system's setup but most are for daily use.

Click on the Macro name to view the SQL Statement being run for that 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
ACTJOB2017-06-15 10.11.14571List Active Jobs
ALLUSR2017-06-15 10.11.14504List User Profiles
DB2PTF2017-06-15 10.11.14503DB2 Group PTF Level
DFTPWD2017-06-15 10.11.14389Check for Users Potentially with Default Password
DMGOBJ2017-06-15 10.11.14356Damaged Objects Report
DSK2017-06-15 10.11.14485Disk Unit Report
DSPJRN2017-06-15 10.11.14207Display Journal
GRPPRF2017-06-15 10.11.14494Group Profile Membership list
IBMBLOG2017-06-15 10.11.14495IBM Db2 for i Blog Article Links
INTERACT2017-06-15 10.11.14318All Interactive Jobs
JOBATTR2017-06-15 10.11.1494Job Attributes
JOBLOG2017-06-15 10.11.142176Joblog table Function QSYS2.JOBLOG_INFO(...)
JOBSCD2017-06-15 10.11.14502Check Job Scheduler Entry Last Submitted Status
JOBSCDE2017-06-15 10.11.14471List Job Scheduler Entries
LASTUSED2017-06-15 10.11.14337Program Last used Date/Time
LASTUSED22017-06-15 10.11.1442Program Last Used Date/Time -- iQuery OBJ_LIST
LIBL2017-06-15 10.11.14497Current Job''s Library List
LICPGM2017-06-15 10.11.14357Installed License Programs (a better view)
MBRLIST2017-06-15 10.11.1418Member List Macro: SETVAR &LIB &FILE &MBR
MCHINFO2017-06-15 10.11.1448Machine Type,Model,Proc Group,Feature Code,Serial
MSGW2017-06-15 10.11.14316List Jobs in MSGW (Message Wait) Status
OBJLIST2017-06-15 10.11.145300Object list using IBM's Object Statistics UDTF
OBJSTRUCT2017-06-15 10.11.14402Exploded Object List (*MODULE and *SRVPGM list)
PTFBKPLVL2017-06-15 10.11.14462Back-level Group PTFs
PTFGRP2017-06-15 10.11.14487PTF Group Levels
SPCVAL2017-06-15 10.11.14507Special Values Query
SYS2017-06-15 10.11.14394System Information
SYSVAL2017-06-15 10.11.14482System Values Listing
TCPIP2017-06-15 10.11.14492TCP/IP Info
TOPHOGS2017-06-15 10.11.14475Top Storage Hogs
TOPLIMITS2017-06-15 10.11.14378Top 3 Consumption Points
USRGRP2017-06-15 10.11.14493List User Profile Group Profiles
USRPRF2017-06-15 10.11.14510List Non-IBM User Profiles

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