Query Supervisor Gives Database Engineers New Power
April 14, 2021 Alex Woodie
One of the most exciting new features in the upcoming releases of IBM i 7.4 Technology Release (TR4) and 7.3 TR10 is the Query Supervisor. We recently caught up with Scott Forstie, IBM’s Db2 for i business architect, who gave us the low down on the new tech for controlling the database.
Forstie told IT Jungle said that the Query Supervisor was the biggest new feature added to Db2 for i with IBM i 7.4 TR4 and 7.3 TR10, which IBM announced yesterday (see “IBM Unveils Spring 2021 Batch of TRs” for more news about the TRs).
So, what is the Query Supervisor and what does it do? We’ll let the Db2 for i expert explain it:
“We invented a new tool within the SQL Query Engine (SQE) called the Query Supervisor,” Forstie said. “The Query Supervisor is unique technology within the SQE that will watch a query as it’s being implemented.”
The Query Supervisor, which is technically a Db2 for i Service, monitors each query to see if it exceeds resource consumption thresholds that are set by the user, Forstie said.
“Elapsed time is probably the easiest to understand,” he said. “How long is the clock time for this query to be implemented? CPU time? Temporary storage? Total I/O counts? Those are four different categories of user supplied thresholds.”
When any of those thresholds have been eclipsed by user or an application, the Query Supervisor will tell the database engineer. It can be configured to send an alert to the database engineer, post a message to QSYSOPR, or the query can be captured to be studied later.
But the Query Supervisor can go beyond simply monitoring database queries, and be configured to take action. When query thresholds are exceeded, the Query Supervisor can call an exit program that can take action, such as automatically stopping a query.
The most obvious use for Query Supervisor is to end runaway queries, which are queries that contain poorly written SQL statements that can bring a machine to its digital knees.
“The problem with tracking runaway queries before is you . . . had to guess that it was runaway,” Forstie said. “We have this thing called query governor that would look at estimates [of query resource consumption] before it executed. In practice, it’s imperfect, because it’s an estimate.”
There are other methods that administrators can use to view the consumptions of active queries, such as the Work with Active Jobs command. But this is an imperfect solution to the problem of runaway queries, too.
“How do you [control runaway jobs] without Query Supervisor? You have to continuously poll the active jobs and see who’s progressing and taking more resource than they should. Polling solution are not ideal, because your polling can actually disrupt the perfect of the production workloads.”
There are other uses for Query Supervisor beyond putting the kibosh on runaway queries. For example, it can be used to help bolster database performance. Forstie provides this example:
“Let’s say you have a query and it’s got a predicate . . . It’s looking at where the US state equals question mark,” he said. “Well, if you’re dialing up and looking in a database table, the performance of that query is going to change based upon whether the state is California or Minnesota. It’s very data-dependent, and it’s also going to be influenced by things like index strategies and so forth.”
Optimizing database queries like this is a difficult task, because there are so many variables at play. This is one of the reasons why Forstie has been so adamant about the need for IBM i shops to hire more database engineers, or to upskill existing administrators in the science and the art of database engineering.
“Those who are tasked with the performance of the production workloads have a difficult job because . . . somebody tells them ‘Hey, we’re executing this query thousands of times per day. Usually it’s sub-second but today it took 10 seconds. Can you explain how that happened,'” Forstie said. “Query Supervisor will give you the precise details.”
Consumption of temporary storage is one of Forstie’s bugaboos, as he sees many IBM i applications consuming more temporary storage than is advised. This is a job for Query Supervisor!
“Query Supervisor will give you precise query-by-query readouts of temporary storage use,” Forstie said. “You don’t have to do something like terminate [the job]. But you might establish your own data marts based on temporary storage consummate by application, by user, by time of day, by whatever metric you need, and then you have a very clear way to approach the topic.”
Forstie is looking forward to see all the ways that IBM i customers will put the Query Supervisor to use.
“It’s going to be the dawn of a new era, I imagine for how database engineers manage the database. And in this sense, it’s not managing the files, but it’s managing the access to the data,” he said. “There’s going to be all this new insight and it’s going to be exciting for me to see what do database engineers do with it.”
All too often, IBM i shops ask technologists to solve problems, but don’t give them enough data to succeed. “But if you give precise data to a technologist, guess what? They’re probably going to innovate,” Forstie said. “They’re going to just solve the problems, because the clear data is going to open up doors. That’s my hope, anyway.”
By the way, queries written in SQL are the most obvious target of the Query Supervisor. But it will also keep an eye on non-SQL database access methods, since most of those are routed through the SQE with IBM i 7.3 and 7.4, Forstie said.
The Query Supervisor will become available on May 14, when the database PTF ships.
RELATED STORY
