Midrange News for the IBM i Community

Customized Disk Drive Information SYSDISKSTAT Published by: Bob Cozzi on 17 Jul 2015 view comments

IBM started including some very useful tables in QSYS2 and other locations during the Technology Refresh era of IBM i OS. In them they begin shipping VIEWS and some TABLE FUNCTIONS that produce results previously reserved for APIs and OUTFILEs from CL commands.

One that I use on my own system and on client's systems is the SYSDSKSTAT VIEW located in QSYS2. It gives you some useful information about the disk drives installed in the partition.

A quick RUNQRYF command displays its content:



This is from our Web partition on our Power7+ system.

The advantage of RUNQRYF vs RUNSQLF is that when you want to just look at the contents of the file quickly, RUNQRYF followed by the file name is quicker to type than a lengthy "SELECT * FROM XXXX/YYYYY" statement. But both produce similar results. Also, unlike all native IBM i commands, RUNQRYF supports long file names; as you can see, the name SYSDISKSTAT is 11 characters long, something a native interface wouldn't handle. But SQL Query File commands handle it just fine.

From Raw to Lovely

It is too bad IBM didn't include cool column headings for these new views and tables they're providing. But they decided it was good to use the column (field) names as column headings so users become familiary with them. I agree. It was a good choice and you don't run queries over these tables everyday, so it's something we can live with.

If I need to provide something for a customer, they normally don't care about the raw information, they want something they can easily read. So I've written a short SQL SELECT statement over the SYSDISKSTAT file that displays the pertinent information in a clean presentation. Here is that SQL SELECT statement:

-- Display Unit Status Information - SQL Query File for IBM i    
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",              
                   as   "Drive               Capacity",          
                   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"   

The first line is an SQL comment that is ignored by SQL Query File. The 2nd line is the title of the query that appears at the top of the output (as documented in the previously installment of this blog). Next the rather lengthy SELECT statement appears. Using the "AS" clause, we assign new column headings to the various columns being included. In addition, I'm using the CASE/WHEN/ELSE statement to convert a few 1's and 0's into meaningful words that humans can read. (Mirrored and Not Mirroed, for example). I also translate the internal drive type into whether the drive is a Solid-State Drive (SSD) or a regular Hard Disk Drive (HDD).

We actually ship this query with the SQL Query File package. In addition, rather than remembering which source member it's stored in, just use our shortcut on the RUNSQLF command. To run this query, type in the following command:


The results will appear as follows:

Formatted Disk Information Panel

Again, this is from our Web partition, so we only have two virtual drives assigned to it. But you can see the more attractive column headings and everything fits on one screen width (you don't have to F8 to the right).

What about the Web?

Similar to what we finished with last time, I've included a link to this query via the web. SQL Query File supports all major output formats, including Excel, CSV, HTML, JSON and others. The link below is requesting that the output be sent as a Scrolling HTML Table.

Try it yourself courtesy of SQL Query File for Web/Mobile.



Return to midrangenews.com home page.
Sort Ascend | Descend