IBM i 7.1 is riding towards the sunset - but there is still some light of the day left. This popular version of the IBM i operating system was released by IBM in April 2010 and has been available for more than six years now, completely stable from the beginning and through all its lifetime. Although it has been replaced by IBM i 7.2 in May 2014 and 7.3 in April 2016, it is still very popular on many Power servers - due to the many enhancements and new functions added to the version since its birth.
The enhancements and functions added to IBM i 7.1 was provided through a new concept introduced by IBM shortly after the arrival of 7.1 - the Technology Refresh. Technology Refresh (just referred to as "TR" by many IBM i professionals) is a way for IBM to add new functions to the operating system without having to create a full new version of the operating system. Installing a new version of IBM i can be a huge task, because replacing the full bottom of the software stack always require extensive tests of all software on top of the operating system, which again will have many customers refrain from upgrading. Technology Refresh is a much smaller package than a full new version and is easy to install, since no other piece of software is affected. The small size also means that the new functions can be made available to the customers much quicker - we don't have to wait several years for a new version to arrive, only the time it takes for IBM to produce the next TR.
A new TR for IBM i 7.1 was released every six months since April 2010, even after IBM i 7.2 was made available in 2014. Many functions added to the newer version 7.2 were backported to 7.1 through a Technology Refresh, thus breathing more life into this aging release. And when 7.2 got a new TR, there was a TR for 7.1 as well. But no more - shortly before the announcement of IBM i 7.3 in 2016, IBM told us that no more TR's would be delivered for 7.1. TR11 was the last Technology Refresh that IBM i 7.1 would get.
As I described in my previous post called "DB2 for i Services - a great tool for system administration", IBM is working hard to extend SQL with great new services to facilitate system management. One of the new functions added to IBM i 7.3 in TR1 (and IBM i 7.2 in TR5) was a table function called HISTORY_LOG_INFO to return information from the history log. The history log is a central piece of the operation system and important to system administrators because it contains information about every job on the system. Important system messages are also sent to the history log. Previously it has been difficult to search the history log for some information because there were no easy way to read the messages in the log programmatically (i.e. no output file option on the DSPLOG command), but this new function makes it easy to pull the information from the history log and analyze it.
Unfortunately for those of us still on IBM i 7.1 this new and very useful function will never be delivered by IBM to our release, since no new TR is coming. I'm not disappointed by IBM - they must focus on the newer versions and not waste resources on keeping old software alive. IBM i 7.3 is very attractive and offers so much in terms of new functionality and stability, and it would be nice to jump to 7.3 right away and get access to the HISTORY_LOG_INFO function. But this is not an option for most of us on IBM i 7.1 at this time, so what to do? Well, if we can't make IBM give us this function we'll have to do it ourselves...
So I decided to build a similar table function for the old, but trusted IBM i 7.1! The building blocks were already available: SQL user defined table functions and an API to get data from the history log. I took the liberty to use the same name and parameters and result columns for my function as IBM has used for theirs, so when we finally make it to IBM i 7.3 we don't have to recode our use of this function.
You will find the new table function HISTORY_LOG_INFO for IBM 7.1 in my git repository on Bitbucket - just click here. The welcome page contains information about how to create the function on your own system, and the source code is open source and can be modified to your own needs, if so desired.
The table function is defined in SQL but the serviceprogram utilized by the SQL function is written in RPG. It takes two optional parameters, the starting and ending timestamp for the messages to be retrieved. It then calls the system API QMHOLHST to get a list of history log messages between the starting and ending timestamps. Each message is then read and the data is formatted before being returned to the SQL function.
Formatting the data can be very CPU intensive so be careful when you use this function! As you probably already have experienced in the past, working with the history log can be a time consuming process because of the often huge amount of messages in the history log. And this function is not a silver bullet, all that data still has to be formatted by the serviceprogram before you get the result from SQL.
Feel free to use this function as is or modify it to your needs. And be patient - some important enhancement will be made to the function shortly. This will be posted in an upcoming blog post.
Thursday, December 08, 2016
Friday, December 02, 2016
DB2 for i Services - a great tool for system administration
System management on the IBM i has always been easy - there are commands to list and work with every object type on the system. And when you needed to work with a list of objects, most of the commands supported an file parameter, so you could dump the list into a file and extract and manipulate the list from the file as you wished.
However, some of the commands used for system management does not have the option to write the list to a file. Instead you had to use an API to retrieve the list, which could mean that you had to read a complex API documentation and understand all the parameters for the API and how to work with user spaces or wherever the output from the API was placed. This was tedious and error prone and few people had the knowledge, time and energy to accomplish that.
IBM wanted to make it easier to manage the IBM i for people without extensive knowledge of the system, and they combined the existing API's with an excellent tool for handling and manipulation lists - SQL! IBM added SQL views and table functions to list object and system information, and now the information was easy to work with - just fire up a SQL prompt and select data from the table function, that gives you the wanted information. Since you're using SQL to retrieve the list of information, all the SQL facilities like selecting, grouping, ordering, joining etc. are at your disposal. IBM even found a great name for these new system management functions: "DB2 for i Services". You can also see it named as "IBM i Services" or "SQL Services".
The services are documented at the DeveloperWorks website. In the beginning the number of services was small, but since the start some years ago the number has increased substantially and the list of services has grown tremendously. Take a look of the services available on DeveloperWorks and you will surely find one or more, that you can use to great benefit. Even if you are not on the latest and greatest version of IBM i, there will be services available to you even at IBM i 6.1!
IBM has even provided samples of the DB2 for i Services in the Access Client Solutions product - the replacement for IBM i Access for Windows. The Run SQL Script plugin has a whole bunch of samples ready to be insert into the SQL editor. Just go to the menu Edit and select "Insert from Examples", and you will get a long list like this:
Click on the one you want, and a sample of a SQL statement returning the selected information will be insert into the typing area of the Run SQL Script window.
Go ahead and explore the services and see what IBM has made available for you. In an upcoming post I will show how you can make your own services like the ones that IBM has created.
Until then - have fun!
However, some of the commands used for system management does not have the option to write the list to a file. Instead you had to use an API to retrieve the list, which could mean that you had to read a complex API documentation and understand all the parameters for the API and how to work with user spaces or wherever the output from the API was placed. This was tedious and error prone and few people had the knowledge, time and energy to accomplish that.
IBM wanted to make it easier to manage the IBM i for people without extensive knowledge of the system, and they combined the existing API's with an excellent tool for handling and manipulation lists - SQL! IBM added SQL views and table functions to list object and system information, and now the information was easy to work with - just fire up a SQL prompt and select data from the table function, that gives you the wanted information. Since you're using SQL to retrieve the list of information, all the SQL facilities like selecting, grouping, ordering, joining etc. are at your disposal. IBM even found a great name for these new system management functions: "DB2 for i Services". You can also see it named as "IBM i Services" or "SQL Services".
The services are documented at the DeveloperWorks website. In the beginning the number of services was small, but since the start some years ago the number has increased substantially and the list of services has grown tremendously. Take a look of the services available on DeveloperWorks and you will surely find one or more, that you can use to great benefit. Even if you are not on the latest and greatest version of IBM i, there will be services available to you even at IBM i 6.1!
IBM has even provided samples of the DB2 for i Services in the Access Client Solutions product - the replacement for IBM i Access for Windows. The Run SQL Script plugin has a whole bunch of samples ready to be insert into the SQL editor. Just go to the menu Edit and select "Insert from Examples", and you will get a long list like this:
Click on the one you want, and a sample of a SQL statement returning the selected information will be insert into the typing area of the Run SQL Script window.
Go ahead and explore the services and see what IBM has made available for you. In an upcoming post I will show how you can make your own services like the ones that IBM has created.
Until then - have fun!
Subscribe to:
Posts (Atom)