Managing Your System with SQL

 

As IS professionals, we devote our careers to making information readily available for managers to make timely, informed decisions.  Shouldn't we provide ourselves with important management information about our own systems?  In this article, I discuss a method of obtaining vital management information about the AS/400 itself.  The technique uses some of the AS/400's display commands to output information to an outfile.  As a relational, object-based system, these outfiles provide that vital information in externally described relational database files.  We can query those database files with Structured Query Language, SQL. 

 

I discuss ways that SQL can query those files to help manage the system.  The techniques determine:  

 

·        The largest but least used objects we may consider for deletion; and users and libraries that own and contain the most wasted storage space. 

·        User profiles that have too much authority or those we need to delete.

·        Objects not used by applications. 

·        Whether the save operation saves the objects it's supposed to. 

·        Unused controllers, devices and lines.

 

The following command displays objects' descriptions sending the output to an outfile:

 

DSPOBJD OBJ(*ALL/*ALL) OBJTYPE(*ALL) OUTPUT(*OUTFILE) OUTFILE(MIS/ALLOBJECTS)

 

This creates an object called ALLOBJECTS in a library called MIS.  This file contains information about every object on the system.  On our system, this file contains over 40,000 records.  Some important fields include: 

 

·        The object's library;

·        its name;

·        its type;

·        its attribute;

·        its storage size;

·        the text description of the object;

·        creation date;

·        the object's owner;

·        it's last save date and time;

·        the date and time of the last change to the object;

·        the date and time the object was last accessed; and

·        the number of days the object was used. 

 

Quite a bit of information in one file, right? -- This only includes about one third of the information contained in the file! 

 

The second command displays all user profiles to an outfile: 

 

DSPUSRPRF USRPRF(*ALL) OUTPUT(*OUTFILE) OUTFILE(MIS/ALLUSRPRFS)

 

This creates an object called ALLUSRPRFS in library MIS, containing information on every user profile on the system.  Some important information in this file includes: 

 

• User profile name;
• user class;
• password change date;
• password expired indicator;
• previous sign on date and time;
• special authorities;
• maximum storage space allowed and used;
• group profile indicator;
• limited capability indicator; and
• status (whether enabled or disabled)

 

Again, I've listed only about one third of the fields in this file.   

 

The display program reference command (DSPPGMREF) displays all objects referenced by programs specified by the command's PGM parameter: 

 

dsppgmref pgm(*ALL/*all) output(*outfile) objtype(*all) outfile(mis/allpgmrefs)

 

Now that we have created these three files, we have the information we need to analyze some key aspects of the system.  Analyzing this much information about 40,000 objects would take quite a while if we looked at each individual description.  With the set-based information available in SQL, however, we generate exception reports and summarize the information, as desired. 

 

 

 

 

Technique One:  Determining the Resource Hogs

 

Object resource hogs consume the most disk space, but have been used the least.  The following Advanced Systems Concept's Sequel query (herein referred to as "Sequel") provides us with the tool to determine the degree to which each object wastes resources, and lists those objects in that order:

 

SELECT      odobow COLHDG("OBJECT" "OWNER"),

odlbnm COLHDG("LIBRARY" "NAME"),

odobnm COLHDG("OBJECT" "NAME"),

odobat COLHDG("OBJECT" "ATTRIBUTE"),

odudat COLHDG("LAST" "USED" "DATE"),

odobsz COLHDG("OBJECT" "SIZE") NAME(OBJECTSIZE),

oducnt COLHDG("DAYS" "USED") NAME(DAYSUSED),

odobtx COLHDG("TEXT" "DESCRIPTION")

OBJECTSIZE/(DAYSUSED+1) LEN(10,0) NAME(PIGFACTOR)

FROM        mis/allobject

ORDER BY    pigfactor DESC

 

            The output of this command is shown in figure 1.  To run the same command in IBM's SQL (I will refer to it as "SQL") enter the following:

 

SELECT      odobow, odlbnm, odobnm, odobat, odudat,

odobsz, oducnt, odobtx, odobsz/(oducnt+1)

FROM        mis/allobject

ORDER BY    9 DESC

 

The rest of the examples include samples of ASC's Sequel, for readibility.  To convert these queries to SQL, remove the COLHDG, NAME and ALIAS (NAME) functions. 

 

Figure 1:  Sample Sequel Output of Resource Hogs

 

 

"PIG FACTOR", also known as resource waster,  divides object size by the days used count.  (The DAYSUSED+1 is used so that SQL won't divide by zero.)  After all the least used, biggest objects are prime candidates for disk space down-sizing.  With this technique, we can trim 90 percent of the extra disk space with only 10 percent of the effort.  The query shows all the archived or unused physical and source files programmers have created over the years, never used, but never bothered to delete.  By analyzing the first two pages of this report and pruning the unnecessary objects, we should recover a large chunk of disk space. 

 

We can determine which users own the most disk space and determine the number of objects that each user owns:

 

SELECT      odobow NAME("OWNER"),

SUM(odobsz) LEN(10,0) NAME("SIZE"),

COUNT(*) COLHDG("NUMBER" "OF" "OBJECTS")

FROM        mis/allobject

GROUP BY    owner

ORDER BY    size DESC

 

Similarly, the following command will list the amount of disk space and the number of objects each library contains:

 

SELECT      odlbnm NAME("LIBRARY"),

SUM(odobsz) LEN(10,0) NAME(SIZE),

COUNT(*) COLHDG("NUMBER" "OF" "OBJECTS")

FROM        mis/allobject

GROUP BY    library

ORDER BY    size DESC

 

 

 

Technique Two:  Determining User Profile Security Risks:

 

This technique lists all user profiles that have not signed on in the last year.  It uses the file ALLUSERS in library MIS created earlier:

 

SELECT      upuprf COLHDG("USER" "NAME"),

upgrpi COLHDG("GROUP" "PROFILE") NAME(GROUPPROF),

uppsod COLHDG("LAST" "SIGN-ON" "DATE") NAME(LASTSIGNON),

upuscl COLHDG("USER" "CLASS"),

uppwcd COLHDG("PASS" "WORD" "CHANGED"),

uppwex COLHDG("PWD" "EXP"),

upmxsu COLHDG("STORAGE" "USED"),

upownr COLHDG("OBJECT" "OWNER"),

upgrpf COLHDG("GROUP" "PROFILE"),

uptext COLHDG("USER" "PROFILE" "TEXT"),

upltcp COLHDG("LIMETED" "CAPABILITY"),

uppwon COLHDG("NO" "PASS" "WORD"),

uppwei COLHDG("PASSWORD" "EXPIRE" "INTERVAL"),

upspau  COLHDG("SPECIAL" "AUTHORITIES")

FROM        mis/allusers

WHERE       groupprof='*NO' AND lastsignon<'940101'

ORDER BY    lastsignon

 

            The output of this command is shown in figure 2. 

 

 

 

Users who have not signed on recently make prime candidates for user profile right-sizing.  I exclude group profiles, because these profiles never sign on.  We can also build an SQL query that will determine which user profiles have too much authority, or use too much disk space. 

 

 

Technique Three:  Determining Which Files Are Not Used by Applications:

 

There are so many objects on our systems that it's really hard to determine if each has value.  The following query determines which objects our applications do not reference -- in other words, the objects that do not belong to any application.  To do this, we "outer" join the DSPPGMREFS file with the ALLOBJECTS file, producing a list of objects in the second file not referenced by objects in the first.  Additionally, we can double check the objects' days used count and last used date to make sure that we really don't use the objects. Note that when we reorganize physical files, the system increments the days used count and changes the last used date of both the physical file and its associated logicals--so if you regularly reorganize your files, some "unused" files may indicate a moderate days used count. 

 

SELECT      odlbnm NAME(LIBRARY),

            odobnm NAME(OBJECT),

            odobat NAME(ATTRIBUTE),

            odldat COLHDG("LAST" "USED" "DATE"),

            oducnt COLHDG("DAYS" "USED" "COUNT"),

            odobsz COLHDG("OBJECT" "SIZE"),

            odobtp COLHDG("TYPE"),

            odobtx COLHDG("TEXT" "DESCRIPTION"),

            whfnam NAME(OBJECT2),

            whlib NAME(LIBRARY2)

FROM        mis/allobject, mis/allpgmrefs

JOIN        odobnm=whfnam AND odlbnm=whlib

 

on the "Sequel Definition Exit Display", specify join type "*ONLYDFT".  Performing an outer join in IBM's SQL is very difficult, so please consult the IBM Redbook on SQL:  Structured Query Language/400:  A Guide for Implementation, document number GG24‑3321‑03.  The output of the above command is shown in figure 3. 

 

Figure 3:  Example Output to of Sequel Query to Determine Unused Objects

 

 

Some might ask, "Where do unused files and programs come from?"  I applied this technique to find that a major commercial AS/400 financial application's two largest logical files were not used.  Apparently, previous versions of the software used the logical files, but when the software no longer used those logicals, they never stopped shipping them to their customers.  Those two files occupied 300 megabytes of disk space, and the vendor had shipped over 30 other logical files not used by their applications!  Another reason is that the vendor may use those objects for modules not installed on your system.  For ease-of-use or for performance reasons, one may choose to limit the scope of the DSPPGMREF and DSPOBJD commands to a single application or application group.  One may choose to run these commands over a single library, the library list (*LIBL), the system users' library list (*USRLIBL), or all user libraries (*ALLUSER). 

 

 

Technique Four:  Does the Save Operation Save Objects It's Supposed to?

 

Do we know that our save program saves all the objects we think it should?  Did our programmers miss something when they last modified their save program?  Here's how we know:

 

SELECT      odlbnm COLHDG("LIBRARY" "NAME") NAME(LIBRARY),

odobnm COLHDG("OBJECT " "NAME"),

SST(odsdat,5,2) COLHDG("SAVED" "YEAR") NAME(SAVEYEAR),

SST(odsdat,1,2) COLHDG("SAVED" "MONTH") NAME(SAVEMONTH),

SST(odsdat,3,2) COLHDG("SAVED" "DAY") NAME(SAVEDAY),

odobtx COLHDG("TEXT" "DESCRIPTION"),

odssze COLHDG("SAVED" "SIZE"),

odobat COLHDG("OBJECT" "ATTRIBUTE"),

odudat COLHDG("LAST" "USED" "DATE"),

odsdat COLHDG("SAVE" "DATE"),

odscmd COLHDG("SAVE" "COMMAND"),

odsdev COLHDG("SAVE" "DEVICE")

FROM        mis/allobject

WHERE       library<>'QSYS' AND library<>'QSPL'

ORDER BY    saveyear, savemonth, saveday

 

            The output for this command is shown in figure 4. 

 

Figure 4:  Example of Sequel Query to Determine Unsaved Objects

 

 

I exclude objects in library QSYS, because IBM, for performance reasons, has chosen not to update the save information in most objects in the QSYS library.  This seems silly for those of us who have a large enough save window, but IBM insists that their customers do not want this feature.  I usually exclude library QSPL, because IBM does not save spool files.  I sequence the query in save date order, so any objects that have not been saved recently would appear first. 

 

 

Technique Five:  Finding Unused Controllers, Devices and Lines:

 

Do our systems have hundreds, possibly thousands of controllers and devices?  Do we know which ones we use and which ones we don't.  Here's one more SQL query to answer that question:

 

SELECT      odlbnm COLHDG("LIBRARY" "NAME"),

odobnm COLHDG("OBJECT" "NAME"),

odobtp COLHDG("OBJECT" "TYPE") NAME(OBJECTTYPE),

odobat COLHDG("OBJECT" "ATTRIBUTE"),

oducnt COLHDG("DAYS" "USED" "COUNT"),

SUBSTR(odudat,5,2) CAT " " CAT SUBSTR(odudat,1,2)

   COLHDG ("LAST" "USED" "YR_MO" NAME("USEDDATE")

odcdat COLHDG("CREATION" "DATE")

FROM        mis/allobject

WHERE       objecttype IN ('*CTLD', '*DEVD', '*LIND') AND (useddate<'94 07')

ORDER BY    useddate

 

            The output for this command is shown in figure 5. 

Figure 5:  Example output to find objects not used by applications. 

 

 

This query selects all devices and controllers that have not been used since before July 1994, and sequences the output in ascending date order.  When I first executed this query, I discovered that I had 175 communications objects that had not been used in more than six months.  Obviously a time to start housekeeping. 

 

These techniques just touch the surface of the power of SQL in managing our systems.  If an AS/400 command can output to an outfile, try your own techniques.