IBM seems to be on a data-centric path with IBM i. They are continously adding more new features to DB2 for i SQL than they are to the rest of the system. For example, we now have the ability to display information about System Values, Disk Drive Configurations, User Profiles, Network Attributes and the list goes on.
Unlike the APIs on which many of these services are based, the interface to these services is through database; or more accurately through SQL. But if you are sitting on Command Entry and would like to produce a list of User Profiles and invalid sign on attempts, you have no native option to run the corresponding SQL service other than jumping into STRSQL to run it, and then exit. Plus, what about saving a complex SQL statement to run later? That's challenging to say the least. That's why I created the SQL Query File licensed program. (Free if you're running IBM i5/OS V5R4M0 or V6R1M0, but requires a paid license for IBM i v7.1 and later)
With this release of SQL Query File, the SQL Command Line tool for IBM i, we've added quick and direct access to many of the SQL services from Command Entry or anywhere else (CL programs, menus, etc.) For example, to view a list of disk drives configured on your system, you could run the *DSKINFO macro, as follows:
Once run, a display similar to the following appears:
We also have similar macros for User Profiles, TCP/IP information, DB2 PTF level, and system information. And more will be added as we hear from our users as to which services they'd like simplified.
Of course you can run any IBM i SQL statement from our RUNSQLF command. But considering the above listing was produced from the following SQL statement, I think I'd take the macros over entering that length statement any day.
-- Display Disk Unit Status Information - SQL Query File v2r1m1 V 1001 050 Disk Drive Information Report SELECT ASP_NUMBER as "ASP", UNITNBR as "Drive Number", DISK_TYPE as "Disk Type", DISK_MODEL as "Disk Model", CASE WHEN UNIT_TYPE = 1 THEN 'SSD' WHEN UNIT_TYPE = 0 THEN 'HDD' ELSE '???' END as "Drive Type", char(decEdit(UNITMCAP,20),20) as "Drive Capacity", char(decEdit(UNITSPACE,20),20) as "Drive Free Space", PERCENTUSE as "Percentage Used", CASE WHEN MIRRORPS is NULL THEN 'NOT Mirrored' WHEN MIRRORPS = '0' THEN 'Inactive' WHEN MIRRORPS = '1' THEN 'Mirrored' END as "Mirrored", CASE WHEN MIRRORUS is NULL THEN ' ' WHEN MIRRORUS = '1' THEN 'Paired' WHEN MIRRORUS = '2' THEN 'Syncing' WHEN MIRRORUS = '3' THEN 'Suspended' END AS "Mirrored Status" FROM QSYS2.SYSDISKSTAT