Exploring Security Applications for IBM i OLAP
July 13, 2022 Alex Woodie
The IBM i platform is typically thought of as a transactional server, and it does that job very well. But it also has solid analytical capabilities, thanks in large part to new OLAP features added many years ago with IBM i 7.3. Now the platform’s database guru, Scott Forstie, is looking for ways to promote greater use of these OLAP capabilities, and security is emerging as a top application.
Sometimes it takes a while to figure out what a good thing you have. This appears to be the case with IBM i 7.3, which launched way back in 2016 and remains the most popular release of the operating system today, having grown its installed base every year since 2016, according to HelpSystems IBM i Marketplace studies.
Two of the big new features introduced by IBM with 7.3 are analytically related. They include the addition of new database tables to support temporal functions in Db2 for i, as well as new SQL-based commands to provide OLAP (online analytical processing) functionality.
Temporal support in Db2 for i helps users analyze data by keeping track of every update to tables made over time. By adding three new columns to the table – for tracking the transaction ID for the entry, when it was made, and when it was removed – IBM essentially allows users to go back and query the table as it stood at some point in the past.
Some users call this “time travel queries,” and it’s a feature that can be quite useful for certain kinds of reporting. Pushing this functionality into the database makes a lot of sense, since it lets users retire any other programs they made to track these types of data changes over time, saving on maintenance and probably boosting performance to boot.
Similarly, IBM i 7.3 brought a handful of new OLAP functions to users. IBM i already had some OLAP-type functions, such as rank, dense rank, and row number, but 7.3 introduced a host of new SQL functions for advanced analytics, such as cumulative distribution support and lag and lead capabilities. These functions brought the OLAP functions of Db2 for i up to par with Db2 for LUW, making them as good as any platform in the industry, Forstie said back in 2016.
So what has commenced on the OLAP front since then? It was a topic that IT Jungle brought up at the recent POWERUp conference in New Orleans.
“As you know, from IBM i 7.3 we’ve had all the OLAP support you’d want to have in the query language, so it’s more resident. And I’m seeing more people using it,” Forstie said. “But I still think we need to tell the story better.”
Forstie has been trying to show IBM i users some non-traditional uses for the new OLAP functions and temporal tables, with security chief among them.
For example, an IBM i shop could use the OLAP functionality to query the QAUDJRN to help find potential security violations. Another one is to create a small data mart on the IBM i database that employs temporal tables to track the granting and revoking of special authorities on IBM i.
“It’s different than the audit journal,” Forstie said. “The audit journal says it’s a secure log, and as long as I have receivers online, I can go back and see things. But a data mart has already been organized. It’s report-ready. ‘Yesterday we had 40 people who had ALLOBJ, but today we had 50. Who are the new 10 and who did it?'”
IBM has done a lot to improve security reporting on the platform lately. Security was one of IBM’s big focuses with the recent launch of IBM i 7.5, which brought more out-of-the-box configurations set to bolster security. As the tamper-proof record of system- and data-level changes, the QUADJRN plays a central role.
“We’re trying to make the audit journal very accessible,” Forstie said, “and the way I thought we should do that was, let’s establish permanent user defined table functions that have all of the substringing, casting, interpreting of entry-specific data into relational columns with the proper data types.”
“If the database team does this one time for every audit journal entry type, then nobody has to ever do it again,” he continued. “They can just consume it.”
Clearly, there’s a lot more work IBM can do to improve users’ ability to understand the state of security on their box and to detect threats in shorter timeframes. Forstie is planning to bring an array of database- and SQL-powered tools to bear on that challenge.
But OLAP is also uniquely positioned to help customers understand the huge amounts of security data generated on the platform. Wither it’s through temporal tables or pre-aggregation of data, the world of data analytics has broad applicability within the security domain. Forstie knows this, and we can’t wait to see what he’ll come up with next.
RELATED STORIES
IBM Delivers More Out-of-the-Box Security with IBM i 7.5
Inside the New Analytic Functions of IBM i 7.3
OLAP Aggregation Specification In DB2 For i 7.3
Would be nice to have in the system also the possibility to select the storage backend engine layout of a table i.e. introducing beside OLAP query something similar to “column-store technology” compressed and column oriented.
I.e. to support fast datamarts or storing continuos sensor data / timeseries data coming from the field (high volume and a lot or redundancy) where typically all the traditional relational guarantees are less stringent.
Having it directly in the “i” would be really the “best” of both worlds.