IBM has been leveraging SQL services to gain access to various system functions for several versions now. Often, these services give you access to system administration functions that do not exist in CL commands. For instance, there are many CL commands to view information, but those often only offer a print option to save data for comparison or review later. But there is often an IBM I service offering that will let you run an SQL statement and get that same data into a file or exportable format.
These SQL services are being added to, or enhanced, all the time with the DB2 PTF groups and the Technology Refresh (TR) updates.
https://www.ibm.com/support/pages/ibm-i-services-sql
This series will give a tech note on utilizing some of these IBM I services as a systems administrator.
This week’s entry is aimed at helping you to quickly find large files and objects on your system library structure.
As system administrators, our storage usage is always at the forefront of concern. This is especially true when you don’t necessarily have access to more disk space to just add more drives. We must use the storage space we have wisely and conserve it when we can. These examples can help you to track down where the space is consumed and give you some tips on potential items to clean up.
Our first example is going to be based on looking within the system libraries for save file objects. We often make a backup of some objects (or a library) before a change, and a save file can be a handy way to quickly preserve the “before” state. Plus, they give you easy access to recover the content quickly right on your system. But they can be culprits for using up space which are often forgotten over time. (Yes, I am guilty of that for sure!)
To look for save files on our system, we will use the QSYS2.OBJECT_STATISTICS() table function. This service works a bit differently, so you might want to dig into the details on it bit more here.
This table function has three parameters:
Using this statement below, we can look for save file objects in libraries that do not start with the letter Q:
SELECT *
FROM TABLE (
QSYS2.OBJECT_STATISTICS('*ALLUSR', '*FILE')
)
WHERE OBJATTRIBUTE = 'SAVF'
AND OBJLIB NOT LIKE 'Q%'
ORDER BY OBJSIZE DESC;
As you can see, we have a few save files that show up in the results. You might find some larger ones that were forgotten over time on your system.
Assuming you don’t find any save file objects that are worth clearing out, you might want to look at other large objects across the system. To do that, we can use the same table function and change some of our selection criteria. In this example, we are going to select specific fields and rename the columns to a bit simpler naming (assuming that we want to export this to an Excel spreadsheet).
SELECT OBJNAME AS OBJECT,
OBJLIB AS LIBRARY,
OBJATTRIBUTE AS ATTRIBUTE,
OBJSIZE AS SIZE,
OBJDEFINER AS WHO_CREATED,
LAST_USED_TIMESTAMP AS LAST_DATE,
OBJTEXT AS DESCRIPTION,
LAST_USED_OBJECT AS LAST_USED_TRACKED
FROM TABLE (
QSYS2.OBJECT_STATISTICS('*ALL', 'ALL')
)
WHERE OBJLIB NOT LIKE 'Q%'
ORDER BY OBJSIZE DESC
FETCH FIRST 20 ROWS ONLY;
This statement will select the top 20 objects on the system by size (in descending order), and present them in your output. Note that we used the ‘LAST_USED_TIMESTAMP’ and the ‘LAST_USED_OBJECT’ columns to show us the last used date/time, and a note as to whether that is tracked on the object:
Now let’s take a look at gathering library size information. This might be something we can use to narrow down to larger libraries for potential cleanup as well. Another table function that has been included with the operating system is QSYS2.LIBRARY_INFO. As a standalone function, it can tell you the size of a specific library.
SELECT OBJECT_COUNT,
LIBRARY_SIZE,
LIBRARY_SIZE_COMPLETE,
LIBRARY_TYPE,
TEXT_DESCRIPTION
FROM TABLE (
QSYS2.LIBRARY_INFO('LIBRARY_NAME')
);
That’s handy functionality, but by itself it is limited to the library you specify in the table function.
With a little modification, we can look for all libraries when we combine it the QSYS2.OBJECT_STATISTICS function. We can nest the LIBRARY_INFO function as a column that will pull the library size based on the OBJNAME column from OBJECT_STATISTICS. Note that we use *ALL for the library name and LIB for object type on the OBJECT_STATISTICS function. Also, we did not exclude any libraries (such as IBM system libraries), we want to see them all.
SELECT Z.OBJNAME AS LIBRARY,
Z.OBJTEXT AS TEXT,
(SELECT LIBRARY_SIZE
FROM TABLE (
QSYS2.LIBRARY_INFO(Z.OBJNAME)
)),
Z.IASP_NUMBER,
Z.IASP_NAME
FROM TABLE (
QSYS2.OBJECT_STATISTICS('*ALL', 'LIB')
) Z
ORDER BY LIBRARY_SIZE DESC;
Here is why we did not exclude the IBM (Q*) libraries… we find that QGPL is a larger library on this system. (That is not uncommon, as it is the ‘general purpose’ library.)
What if we suspect that we have some large files that might have deleted records in them as well? We can utilize another SQL view in QSYS2 to dig into that one: SYSTABLESTAT.
This view lists a row for every table that has at least 1 partition or member on the system. It contains lots of statistical information similar to a Display File Description (DSPFD) command. In our exercise, we are mostly concerned about the size of the file and the amount of deleted records it might have. In this statement, we chose to sort on the size, but you can sort on deleted records as well.
SELECT TABLE_SCHEMA,
TABLE_NAME,
NUMBER_DELETED_ROWS,
DATA_SIZE
FROM QSYS2.SYSTABLESTAT
ORDER BY DATA_SIZE DESC;
If you find a large file with a lot of deleted rows, that might be a candidate for reusing deleted records (or scheduling a reorganization of the file to compress out the deleted records). But, do we have a method to find files with deleted rows, and the file is not set to reuse deleted rows? With SQL, we can put that together!
Much like the earlier example of getting a list of all libraries and their sizes, we will combine a couple of QSYS2 views together to get what we want to see: SYSTABLESTAT and SYSFILES. SYSFILES is another take on the DSPFD command output but has different columns than SYSTABLESTAT. So, we will combine them together to find which files have deleted records and are not set to reuse those deleted records.
SELECT A.SYSTEM_TABLE_SCHEMA,
A.SYSTEM_TABLE_NAME,
A.REUSE_DELETED_RECORDS,
(SELECT NUMBER_DELETED_ROWS
FROM QSYS2.SYSTABLESTAT
WHERE TABLE_SCHEMA = (A.SYSTEM_TABLE_SCHEMA)
AND TABLE_NAME = (A.SYSTEM_TABLE_NAME)) AS DELETED_ROWS
FROM QSYS2.SYSFILES A
WHERE SYSTEM_TABLE_SCHEMA NOT LIKE 'Q%'
AND REUSE_DELETED_RECORDS = 'NO'
ORDER BY DELETED_ROWS DESC;
We use SYSFILES as our base selection view and pull the deleted row information as a new column into the statement. Here is a sample of what we get from this query:
A note to keep in mind when using any of these examples. Depending on your system, and how many libraries or file objects you have, these statements could take a few minutes to run. If you have a large system, you might want to make your selection criteria a bit more specific to minimize the output or run time of these queries.
Now, you have a handful of quick tools in your pocket to track what libraries and objects are using up your storage resources. You can dig into those consumers and might be the hero because you can clean up some old and forgotten things on the system!