In my previous post IBM i 7.1 is not dead - yet... I introduced the SQL function HISTORY_LOG_INFO. This function has been created by IBM for IBM i 7.3 and 7.2, and because this function is such a powerful tool for a system administrator doing system log analysis, I decided to create a similar function for 7.1.
I promised those of you following this blog that enhancements to the function would follow shortly. Now the updated function is ready and I can finally write about it. The reason for the update was that the system history log (QHST) can contain a great amount of data, and searching in the system history log can be quite CPU intensive and take longer than anticipated.
The API behind my function is called Open List of History Log Messages (QMHOLHST) and you can specify some selection parameters when calling the API. You can select specific message identifiers, message types and/or jobs, and I added these selection parameters to my version of the function. You may argue that you already have this selection just by adding a WHERE clause to the SQL statement containing the HISTORY_LOG_INFO table function, and this is true indeed. However, the WHERE clause is first applied to the data when the table function has formatted and returned all its data to the DB2 engine. By allowing the user to specify the selection information to the table function as parameters, the selection can be forwarded to the API and much less data will be returned and formatted, thus saving time and CPU.
The first version of my function had two parameters - start and ending timestamps. The new version adds three parameters - a list of message identifiers, a list of message types and finally a list of jobs to find. Does it sound familiar? Well, it's exactly the same parameters as those on the DSPLOG command!
The list of message identifiers can be up to 200 items, each separated by comma or blank. The identifiers can be entered in upper or lower case and can be generic. For example, CPF0000 specifies that all CPF messages that meet the specifications of the other parameters are selected.
The list of message types can be up to 9 types, again separated by comma or blank and upper or lower case.
And finally the list of jobs can contain up to 5 jobs, more or less qualified. A qualified job name consists of three elements: job number, job user and job name separated by slash(es). You may specify just the job name, but you can add the user (and eventually the job number) if you like. No generic job values are allowed.
The new version of the function is found in my Bitbucket git repository, just like the previous one. The README documentation has been updated to include description of the new parameters and some additional examples of how to use them. Go and have a look.
I hope that these enhancements will help making your system log searches much faster, thus saving time and CPU. If you have any questions or suggestions, you are welcome to comment this post, or you can enter your question or suggestion on the BitBucket git repository in the Issues section.
