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.
Getting Started
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:
PGM
RUNSQL SQL(‘CREATE TABLE QTEMP/OUTPUT_TABLE AS +
(SELECT PTF_PRODUCT_ID, PTF_RELEASE_LEVEL, +
PTF_LOADED_STATUS +
FROM QSYS2.PTF_INFO)
WITH DATA’)
ENDPGM
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.
Bottom Line
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?
David Shirey is president of Shirey Consulting Services, providing technical and business consulting services for the IBM i world. Among the services provided are IBM i technical support, including application design and programming services, ERP installation and support, and EDI setup and maintenance. With experience in a wide range of industries (food and beverage to electronics to hard manufacturing to drugs--the legal kind--to medical devices to fulfillment houses) and a wide range of business sizes served (from very large, like Fresh Express, to much smaller, like Labconco), SCS has the knowledge and experience to assist with your technical or business issues. You may contact Dave by email at
MC Press books written by David Shirey available now on the MC Press Bookstore.
 |
||
 |
|
21st Century RPG: /Free, ILE, and MVC Boost your productivity, modernize your applications, and upgrade your skills with these powerful coding methods. List Price $69.95 Now On Sale
|
More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.
TRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now! Request Now.
Forms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.
Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:
Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.
IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn:
LATEST COMMENTS
MC Press Online