Deleting spool files using SQL

Many of the IBM i partitions I have worked upon have had an issue of old spool files occupying valuable storage space. Since IBM i 7.2 there has been a way for me to analyze the spool files, using SQL, and make decisions on which would be deleted. The process of doing the deleting had to be entrusted to a program containing CL commands.

The latest round of Technology Refreshes, 7.4 TR2 and 7.3 TR8, gave me a way I can delete old spool files using a SQL procedure, DELETE_OLD_SPOOLED_FILES

Warning:  Use this SQL procedure carefully. If you do not you run the risk of deleting all spool files from your partition.

DELETE_OLD_SPOOLED_FILES has five parameters:

Happy 7th birthday RPGPGM.COM

Another year has passed and this blog has reached its seventh anniversary. We are living in interesting times with IBM i, and over the years I have been fortunate to write about many things, that in my opinion, have made significant improvements to programming with this operating system. As well as a whole lot of cool additions to Db2 for i (SQL). I have learn a lot from what I have written about, and I am sure that I write better code than I use to.

The past year has been an exciting time, not only for this blog but for me too. I would include the following as my personal high points, in no particular order:

Making a hash of records for comparisons

Another addition to Db2 for i as part of the latest Technology Refreshes for IBM i 7.4 TR2 only, is the ability to create a hash value for a record from a file using the HASH_ROW SQL built in function. I am not going to describe what hash values are, if you are interested there is a Wikipedia article describing it. I am just going to say that hash-ing a string produces hexadecimal value of the data.

Previously I had been able to hash individual fields, or columns, using the various standards of hash. As HASH_ROW uses SHA512 I can do the same for a field using HASH_SHA512:

01 SELECT HASH_SHA512('A') AS "Hash", 02 LENGTH(TRIM(HASH_SHA512('A'))) AS "Length" 03 FROM SYSIBM.SYSDUMMY1 04 UNION 05 SELECT HASH_SHA512('ABCDEFGHIJKLMNOPQRSTUVWXYZ'), 06 LENGTH(TRIM(HASH_SHA512('ABCDEFGHIJKLMNOPQRSTUVWXYZ'))) 07 FROM SYSIBM.SYSDUMMY1

Using SQL to compare files for differences

Having looked at, and played, with a number of the new additions to Db2 for i in the latest new Technology Refresh this is my favorite and in my opinion the most useful: table function COMPARE_FILE.

COMPARE_FILE gives me the ability to identify differences between physical files, source files, and DDL (SQL) tables. I can compare two files layout and the data within them with this table function, and have a results row returned for each difference. Wow, both of those with the same table function!

Alas, it is only available in the Technology Refresh for 7.4 only, IBM i 7.4 TR2. It is not available in IBM i 7.3 TR8.

COMPARE_FILES has a number of parameters:

New SQL table function to display a job's locks

Before the latest round of new Technology Refreshes, 7.4 TR2 and 7.3 TR8, the only ways I knew to get Job Lock information was either by using an API or the Display Job command, DSPJOB. These new TRs introduced a new table function, JOB_LOCK_INFO, that allows me to see the same information with just a simple SQL statement.

JOB_LOCK_INFO has three parameters:

1. Job name: This is the only required parameter. This can either be the full job name, or for information about the current job an asterisk ( * ) can be used
2. Internal objects only: Optional. NO, which is the default, shows only external objects. YES shows internal objects, internal space objects, and external objects
3. Ignore error: Optional. NO when an error is encountered it is returned. YES, which is the default, only a warning is returned

Define a new Data Structure from one that is nested

In the past I have written about nesting data structures, a way to contain data structure(s) within another data structure. And there are times I have wanted to define a new data structure to be identical to another using the LIKEDS keyword.

01 dcl-ds One qualified ; 02 OneSF1 char(1) ; 03 dcl-ds Two ; 04 TwoSF char(5) ; 05 end-ds ; 06 OneSF2 char(1) ; 07 end-ds ; 08 dcl-ds Test1DS likeds(One) ;

Improvements to RPG's timestamps

Ever since timestamps were introduction to RPG I have always felt aggrieved that they never contained the entire timestamp. Timestamps have six decimal places following the seconds and only the first three positions, milliseconds, were filled. The last three positions were always zero. Occasionally I have found in timestamp fields in two records with same value. I would unable to determine which record is older, without using other fields or the file's relative record number.

The latest round of new Technology Refreshes, IBM i 7.4 TR2 and 7.3 TR8, brought improvements to the way RPG does timestamps.

Some of what I am going to describe here may have been available in earlier releases or TRs.

Key data structure big improvement

I do not use the Keyed data structure, but I can appreciate this change that occurred as part of the latest new Technology Refreshes, IBM i 7.4 TR2 and 7.3 TR8, was significant.

Before the latest TRs the second parameter of the %KDS built function had to be a number to denote how many of the key fields were to be used. The change in the new TRs now allows for that second parameter to also allow a variable.

The old way would look something like this:

01 if (NbrKeyFlds = 1) ; 02 chain %kds(FileKeys:1) TESTFILER ; 03 elseif (NbrKeyFlds = 2) ; 04 chain %kds(FileKeys:2) TESTFILER ; 06 endif ;

Retrieving information about a library using SQL

As part of the new Technology Refreshes, 7.4 TR2 and 7.3 TR8, came a table function to return information about a single library. I have to admit that I don't really care about some of the information returned in the results, but there are three columns I am going to find very useful.

The new table function LIBRARY_INFO is found in the library QSYS2. It combines data elements from the RTVLIBD and DSPOBJD commands. When calling this table function there are two parameters, one mandatory and the other is optional:

1. Library name:  No surprise that this is the mandatory parameter
2. Ignore errors?  What to do when an error is encountered. This is the optional parameter. Valid values are: YES and NO

In these examples I am not going to use the second parameter.

Right, let's jump right in and see what information we can retrieve about one of my libraries:

Today is 7.4 TR2 and 7.3 TR8 day

Today is the day the PTFs for the latest Technology Refreshes are available for download, IBM i 7.4 TR2 and 7.3 TR8.

The relevant PTFs are:

• Db2 for i
• RPG

You can find links to what are in these Technology Refreshes here.

I am now going to generate a support ticket asking RZKH to load these TRs on their partitions. When they have I can start sharing how to use these enhancements.

Discovering the database's limits

Every so often I stumble across something in Db2 for i that I may not have a practical use for, but I find interesting. The SQL table SQL_SIZING is a good example of this. It contains one row for each limit of the IBM i Db2 database manager.

The table contains just four columns:

1. SIZING_ID:  The ANSI, and ISO, number that defines the database sizing id
2. SIZING_NAME:  The ANSI, and ISO, database sizing id name
3. SUPPORTED_VALUE:  Sizing limit (value). If null the sizing limit is not applicable for IBM i
4. COMMENTS:  Even though the sizing name is big enough to contain an adequate description of the sizing id this column gives a more detailed description

Adding RPG compiler parameters into the SQL Option statement

This feature was brought to my attention by someone who was converting a fixed format SQLRPGLE program to modern all free RPG code, and wondered the purpose of this code snippet.

01 C/EXEC SQL 02 C+ SET OPTION COMMIT = *NONE, 03 C+ COMPILEOPT = 'DBGVIEW(*ALL)' 04 C/END-EXEC

She understood the purpose of line 2. It was line 3 she had not seen before. Neither had I, and was intrigued as to what its purpose was and how else could it be used.