About six months ago, I talked about the relatively new DB2 for i Service tool and why it was pretty nifty. Now, let’s take a closer look at it and see how you actually use it.
As I said in the previous tip on this topic, the DB2 for i Services developed by Scott Forstie’s team and being enhanced with every new technology refresh is another way to investigate what’s happening on your IBM i. Although we’re accustomed to doing this using a variety of very powerful system commands, the DB2 for i Service package provides a fresh, more flexible, and more sophisticated way of digging out this information.
Although I have referred to it as a “package,” it’s not something you need to buy and it comes bundled into your normal i environment. Consequently, everyone can use it and probably should.
In short, it’s a set of DB2 tables that contain all sorts of interesting system data. And all you have to do is query these tables using standard SQL statements.
First, find yourself an SQL environment.
For most people, that will probably be the SQL environment that’s part of Navigator. For the demos below, I’ll be using STRSQL in green-screen, because for the moment I don’t have access to a Navigator environment. Sorry. It looks a little old school but doesn’t change the outcome.
For our first lab experiment, let’s get into the SQL environment of our choice and issue the following SQL statement:
SELECT * FROM QSYS2.PTF_INFO
We’ll get the following screen result.
Figure 1: Run the command SELECT * FROM QSYS2.PTF_INFO.
And it’s as simple as that.
We know that we can use the PF keys to scan right and left to find the particular fields we want to look at, but this is where DB2 for i Services has a big advantage over standard operating system commands. Instead of specifying the asterisk (*), we can change our SQL statement to zero in on just the fields we want to look at.
SELECT PTF_PRODUCT_ID, PTF_PRODUCT_RELEASE_LEVEL, PTF_RELEASE_LEVEL, PTF_LOADED_STATUS FROM QSYS2.PTF_INFO
This way, we get a much more streamlined view.
Figure 2: Get a specific field display.
What’s even better is the ability to take several of these DB2 tables and use them together to generate the kinds of views that you just can’t get with the standard system commands. A good example is given on the IBM website. Unfortunately, I screwed up and forgot to copy the URL where I got this code. But you can trust me. Right?
WITH ACTIVE_USER_JOBS (Q_JOB_NAME, CPU_TIME, RUN_PRIORITY) AS (
SELECT JOB_NAME, CPU_TIME, RUN_PRIORITY FROM TABLE (QSYS2.ACTIVE_JOB_INFO('NO','','','')) x WHERE JOB_TYPE <> 'SYS'
) SELECT Q_JOB_NAME, CPU_TIME, RUN_PRIORITY, V_SQL_STATEMENT_TEXT, CURRENT TIMESTAMP - V_SQL_STMT_START_TIMESTAMP AS SQL_STMT_DURATION, B.* FROM ACTIVE_USER_JOBS, TABLE(QSYS2.GET_JOB_INFO(Q_JOB_NAME)) B
WHERE V_SQL_STMT_STATUS = 'ACTIVE'
ORDER BY SQL_STMT_DURATION DESC
This statement takes two of the tables, Active_Job_Info and Get_Job_Info, to find the longest-running SQL statement. That’s pretty cool, don’t you think?
Where Are You Getting This Stuff, Dave?
Well, some of it I’m making up, but the list of databases that are available is found on the IBM website here.
Only certain types of info are available using the code above , but if you click on a specific table, you’ll be shown the data elements that are found there. This table/element list is your primary resource for building queries. Other than that, there’s not much out there in the way of documentation.
You can also get to pretty much the same info by going here. It’s up to you.
Once you know the table and data element names, you’re ready to go.
Doing This in CL
There may be lots of times that you want to do your DB2 for i Services SQL work off the cuff by keying the SQL statement into an SQL environment. Sure, why not. But there may also be lots of times when you wish you could set up a statement and run it from a CL command. And the good news is, you can.
If you’re on 7.1 or above, you just use the RUNSQL command in your CL. For example:
RUNSQL SQL(‘CREATE TABLE QTEMP/OUTPUT_TABLE AS +
(SELECT PTF_PRODUCT_ID, PTF_RELEASE_LEVEL, +
Actually, I’m told by people that I trust (I saw it on the Internet and they can’t say anything on the Internet that is not true) that you can do this in 6.1 if you have PTF SF99601 level 25 (thanks, Dan Foldager and Simon Hutchinson). But seriously, if you know your PTF level, shouldn’t you just be at 7.2? If you’re not there, then you have to use RUNSQLSTM and put the SQL statement in a source file. Sounds like a lot of bother to me. Easier to upgrade. Again, up to you.
And Once I Create an Output File?
What you do at that point in the CL is up to you. I guess I would move it to the IFS and then pick it up from there. You could email it, print it, look at it longingly. I don’t really care.
The bottom line is that yes, you can find out a great deal about your system using the good ol’ system commands that we have grown up using.
But DB2 for i Services provides another way to do things, a way that allows you to choose the fields that you see, mash different tables together to get specialty information, and move that output to the IFS, where you can do God knows what to it.
Seriously, what else could you want?