IBM i SQL Services

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.

Every new TR IBM seems to offer more SQL SERVICES and SQL features. This is why we created our SQL Query File product.

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:

RUNSQLF  *DSKINFO

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.

RUNSQLF  *DSKINFO Macro Defintion

-- 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