DB2 for i Services use SQL instead of system APIs
The past few days I spent in the country of Luxembourg in the beautiful village of Schengen. I was in Luxembourg as part of the Common Europe modernization tour sponsored in part by ARCAD software where Charlie Guarino and I are talking about of all things ‘Modernization’. It was very fun to listen to Charlie teach a class on RationalDeveloper for i in French! I was very impressed. In addition to modernization I have been also covering all the new 7.2 release content. This new release session is in part to blame for this blog.
Originally we were supposed to have stayed at one of the many hotels with in the city of Luxembourg, seems there are huge elections going on and every hotel room in the city was already booked! That is how we ended up in the village of Schengen.
For those that live Europe, you may recognize that name, same for you history buffs. For the rest of us, a quick note about the importance of this little village tucked in to a corner of Luxembourg right next to both the German and French boarders. This is the place that the Schengen agreement was signed. This is the agreement that allows for freedom of movement within in Europe. All ‘Schengen’ countries no longer have any boarder guards or passport control. You can freely move about these countries.
On to the technical discussion for this entry. I am going to cover the ability to use SQL as your means to access ‘stuff’. What the heck am I talking about. Back, a couple of technology refreshes ago, the database team started to create a number of DB2 for I Services. These services provide a way for you to access essential detail from various IBM i components strictly using SQL. What does this mean? Time to talk examples. I think that is the best way for this sort of topic.
Say you want to find out some information about a user profile, what are your options? I am specially referring to doing this programmatically. Today, you might have to dig out your IBM I API manual to look up the specification for the user ID API, or if you are in java, you could leverage the java toolbox to get details about the user ID from the user ID class. Seems in either case, it’s going to take you some time and research to find what you are looking for and create a program to get the information, and then figure out how to filter and sort the returned results for the specific information you are after. Seems you are in for a fair amount of work for this activity.
What if there was a better way, using nothing but SQL. Allowing you to access specific data in addition to sorting or filtering with a single simple command. Leveraging these new DB2 for I Services you can do exactly that. Easy access to ‘stuff’ on your system.
To help illustrate what i am talking about, I am going to give you a number of very useful examples that show you how to use these services. Want to take a moment here to thank Scott Forstie at this point as he is the SQL wizard that actually defined these SQL examples.
Determine if a specific PTF Group level is installed on your system.
SELECT MAX(PTF_GROUP_LEVEL) FROM QSYS2.GROUP_PTF_INFO WHERE PTF_GROUP_NAME LIKE 'SF99__0%' AND PTF_GROUP_STATUS = 'INSTALLED'
In this example we are using the group PTF service and filtering on a specific PTF Group number to see if it has a status of installed.
Find the Technology Refresh installed on your system
SELECT CURRENT SERVER CONCAT ' is running ' CONCAT PTF_GROUP_TARGET_RELEASE CONCAT ' with TR level: ' CONCAT PTF_GROUP_LEVEL AS TR_LEVEL FROM QSYS2.GROUP_PTF_INFO WHERE PTF_GROUP_DESCRIPTION = 'TECHNOLOGY REFRESH' AND PTF_GROUP_STATUS = 'INSTALLED' ORDER BY PTF_GROUP_TARGET_RELEASE DESC FETCH FIRST 1 ROWS ONLY
In this example we will return a pretty statement showing you the system release and the TR currently installed. First we are getting the system name and concatenating it with the system release form the PTF Group target release field. We then have a touch of logic tossed in to find the first row of data that matches the words ‘Technology Refresh’ and ‘Installed’. This first matched row gives you the installed the TR level. Pretty simple!
What about getting a list of the users on your system whose passwords are about to expire.
SELECT * from qsys2.user_info where status = '*ENABLED' AND DATE_PASSWORD_EXPIRES BETWEEN CURRENT TIMESTAMP AND CURRENT TIMESTAMP + 7 DAYS;
This was a real life request from one of our customers. They have IBM traveler running in their shop and several of their executives have gotten to the point that they only use their mobile device for email. Considering I am actually writing this on my iPad, not that far fetched in this modern world we live in. Why is knowing when the password is ready to expire important? Well, with traveler there is no notification that your profile is ready to expire, you just stop working. No explanation. Sort of a pain to be honest. With this simple SQL you can get a list of the user that will expire in the next 7 days,
Top ten users of storage
SELECT USER_NAME, SUM(STORAGE_USED) AS TOTAL_STORAGE_USED from qsys2.user_storage GROUP BY USER_NAME ORDER BY TOTAL_STORAGE_USED DESC FETCH FIRST 10 ROWS ONLY ;
Here we are grouping the output of the total storage used by user name in descending order and then just grabbing the top ten users.
System variables that have been changed in the past week
SELECT ENTRY_TIMESTAMP, "CURRENT_USER", cast( cast(substring(entry_data,3, 10) as varchar(10) for bit data) as varchar(32000) ccsid 37) as entry_data_char_form, X.* FROM TABLE ( QSYS2.Display_Journal( 'QSYS', 'QAUDJRN', -- Journal library and name '', '', -- Receiver library and name CURRENT TIMESTAMP - 7 DAYS, -- Starting timestamp CAST(null as DECIMAL(21,0)), -- Starting sequence number 'T', -- Journal codes 'SV', -- Journal entries '','','','', -- Object library, Object name, Object type, Object member '', -- User '', -- Job '' -- Program ) ) AS x ORDER BY entry_timestamp DESC
This example is a bit more complicated. We are accessing the audit journal filtering on the past 7 days for just the system value entries.
For a complete list of the services that we have delivered, check out the following link from IBM I developerWorks: http://bit.ly/DB2foriServices
For those that have been using SQL on a regular basis, I am sure these example have created a number of additional possibilities. What about the rest of us that have not started using SQL on a consistent basis ( or at all!), can this stuff be useful? Sure can! There are a couple of ways that you can just copy and paste these examples into a ‘Run SQL script ‘ interface where you will be able to run the script and view the report. Most people have installed iAccess Navigator client on their workstation. In this interface there is a nice interface to run any SQL script,and see the results. In addition in our new Mobile iAccess support you can also run these scrips right from the ‘Run SQL Sripts’ from your mobile device! For details about the iAccess Mobile support check out the following URL: http://www-03.ibm.com/systems/power/software/i/access/mobile.html
In this entry, I was only able to give you a handful of examples, for a complete list of the DB2 for I Services, check out the TR update section for DB2 for I on the IBM I developerWorks page. Hope you will be able to find your favorite use for these simple and powerful services for IBM I.
You are now free to move about your code!