03
Sun, Dec
0 New Articles

TechTip: DB2 for IBM i Services - The Practical

Typography
  • Smaller Small Medium Big Bigger
  • Default Helvetica Segoe Georgia Times

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.

102816ShireyFig1DMU

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.

102816ShireyFig2DMU

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

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 This email address is being protected from spambots. You need JavaScript enabled to view it. or by phone at (616) 304-2466.


MC Press books written by David Shirey available now on the MC Press Bookstore.

21st Century RPG: /Free, ILE, and MVC 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

BLOG COMMENTS POWERED BY DISQUS

LATEST COMMENTS

Support MC Press Online

$0.00 Raised:
$

Book Reviews

Resource Center

  • SB Profound WC 5536 Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application. You can find Part 1 here. In Part 2 of our free Node.js Webinar Series, Brian May teaches you the different tooling options available for writing code, debugging, and using Git for version control. Brian will briefly discuss the different tools available, and demonstrate his preferred setup for Node development on IBM i or any platform. Attend this webinar to learn:

  • SB Profound WP 5539More 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.

  • SB HelpSystems ROBOT Generic IBM announced the E1080 servers using the latest Power10 processor in September 2021. The most powerful processor from IBM to date, Power10 is designed to handle the demands of doing business in today’s high-tech atmosphere, including running cloud applications, supporting big data, and managing AI workloads. But what does Power10 mean for your data center? In this recorded webinar, IBMers Dan Sundt and Dylan Boday join IBM Power Champion Tom Huntington for a discussion on why Power10 technology is the right strategic investment if you run IBM i, AIX, or Linux. In this action-packed hour, Tom will share trends from the IBM i and AIX user communities while Dan and Dylan dive into the tech specs for key hardware, including:

  • Magic MarkTRY 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.

  • SB HelpSystems ROBOT GenericForms 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.

  • SB HelpSystems ROBOT GenericIT security is a top priority for businesses around the world, but most IBM i pros don’t know where to begin—and most cybersecurity experts don’t know IBM i. In this session, Robin Tatam explores the business impact of lax IBM i security, the top vulnerabilities putting IBM i at risk, and the steps you can take to protect your organization. If you’re looking to avoid unexpected downtime or corrupted data, you don’t want to miss this session.

  • SB HelpSystems ROBOT GenericCan you trust all of your users all of the time? A typical end user receives 16 malicious emails each month, but only 17 percent of these phishing campaigns are reported to IT. Once an attack is underway, most organizations won’t discover the breach until six months later. A staggering amount of damage can occur in that time. Despite these risks, 93 percent of organizations are leaving their IBM i systems vulnerable to cybercrime. In this on-demand webinar, IBM i security experts Robin Tatam and Sandi Moore will reveal:

  • FORTRA 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:

  • FORTRAManaging messages on your IBM i can be more than a full-time job if you have to do it manually. Messages need a response and resources must be monitored—often over multiple systems and across platforms. How can you be sure you won’t miss important system events? Automate your message center with the Robot Message Management Solution. Key features include:

  • FORTRAThe thought of printing, distributing, and storing iSeries reports manually may reduce you to tears. Paper and labor costs associated with report generation can spiral out of control. Mountains of paper threaten to swamp your files. Robot automates report bursting, distribution, bundling, and archiving, and offers secure, selective online report viewing. Manage your reports with the Robot Report Management Solution. Key features include:

  • FORTRAFor over 30 years, Robot has been a leader in systems management for IBM i. With batch job creation and scheduling at its core, the Robot Job Scheduling Solution reduces the opportunity for human error and helps you maintain service levels, automating even the biggest, most complex runbooks. Manage your job schedule with the Robot Job Scheduling Solution. Key features include:

  • LANSA 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.

  • LANSAWhen it comes to creating your business applications, there are hundreds of coding platforms and programming languages to choose from. These options range from very complex traditional programming languages to Low-Code platforms where sometimes no traditional coding experience is needed. Download our whitepaper, The Power of Writing Code in a Low-Code Solution, and:

  • LANSASupply Chain is becoming increasingly complex and unpredictable. From raw materials for manufacturing to food supply chains, the journey from source to production to delivery to consumers is marred with inefficiencies, manual processes, shortages, recalls, counterfeits, and scandals. In this webinar, we discuss how:

  • The MC Resource Centers bring you the widest selection of white papers, trial software, and on-demand webcasts for you to choose from. >> Review the list of White Papers, Trial Software or On-Demand Webcast at the MC Press Resource Center. >> Add the items to yru Cart and complet he checkout process and submit

  • Profound Logic Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application.

  • SB Profound WC 5536Join us for this hour-long webcast that will explore:

  • Fortra 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: