Report Writing Punishes IBM Power i Shops

October 6, 2014 Dan Burger

Report writing is a peculiar skill for an IT department. The technical skills to get it done are unquestionably the responsibility of IT, but the actual report generation should be in the hands of the person needing the information–the business analyst, the marketing or financial personnel, and even the C-level decision makers. It takes reporting tools to make this happen, and not the tools that IBM Power Systems shops used 10 or 20 years ago.

Allowing end users to run reports is one of the targets of organizations with the aim of better aligning business requirements and IT optimization. It takes new skills, new tools, and new knowledge to make this happen, says Doug Mack, one of IBM’s DB2 for i experts. Mack is a member of the team at the DB2 for i Center of Excellence, which consults with shops running Power on i. He specializes in helping organizations migrate from the original AS/400 reporting tool called Query/400 to the modern reporting tool called Web Query.

With new skills and new tools come benefits for end users and IT staff. End users get access to the data they want, in a format they want, and they get it when they want it. The IT staff is invested in getting the new delivery system set up, but afterward its workload is reduced because they are not on call for every report that every department requests. Those requests become self-service.

“The majority of engagements by the DB2 for i Center for Excellence staff involve working with people who are just getting started,” Mack says. “A smaller percentage of our time is working with people who have been using Web Query version 1 and want to enhance features and migrate to Web Query version 2.”

The impact of mobile devices in the workplace has accelerated the interest in modern reporting methods. In many shops the requirement that dashboard reports be supported by mobile devices is understood. Business analysts are access-oriented workers. Having a tool that will take an existing report and deploy it to a mobile device without a lot of extra work is a priority addition to the IT toolbox.

“Small shops have business analysts that understand what’s driving business. Those shops have to accommodate this desire for information,” Mack says. “Operational reporting requires better ways to get information from a system.”

Mack’s experience is with DB2 Web Query, so he recommends that product be matched to production databases. He says small shops can accomplish this with few infrastructure changes. In this case, the small category is defined by a goal of creating tactical operational reporting, which entails building self-service dashboards for a variety of department informational access needs. The added demand for memory and processing power is well within the capability of the machines smaller shops are running, Mack says.

If you’ve been wondering what you will do with the additional performance of the Power7 and Power8 servers, upgraded reporting capabilities is one thing that could be added to your to-do list.

Infrastructure upgrades are more common in the larger SMB shops and at the enterprise level. As the query workloads get ramped up, it becomes important to isolate them from production workloads. The queries will likely have a variety of data sources and more complex data cleansing issues. These organizations are looking at creating a data warehouse architecture. That entails adding another partition or another server to support the data warehouse.

“What you see in the larger SMB enterprise shops is a much bigger emphasis on analytics,” Mack says. “They may have a designated analytics team as part of their change in organizational structure. Some have chief marketing officer or chief data architect positions that focus on analytics.”

These organizations typically set up an isolated reporting database where information from Oracle databases, spreadsheet databases, FTP sources, and DB2 is consolidated to provide a clear picture for analysis.

“We are seeing more of that (in our customer engagements),” Mack says. “There comes a time when tactical operation reporting does not fill meet the requirements without a data warehouse. The infrastructure to handle that might be more server resources and a tool to create that database and automate the process.”

Mack provided an example of a project he was involved with for an enterprise client. The organization, which runs hundreds of IBM i servers, had a specific business problem around security compliance reporting. For the security officer to report on compliance, it was a six-month project just to gather all the data across all the servers and do the analysis to determine which servers were out of compliance.

Mack and the DB2 for i team he works with built a data warehouse that grabs security information from all the servers and provides a consolidated dashboard view that identifies areas that are out of compliance. The report is available daily.

That project, a cooperative venture with the IBM’s Lab Service Security team, demonstrated so much potential that IBM made a decision to productize it and make it available to other clients. It’s part of a package of PowerSC (the SC stands for security and compliance) tools for IBM i and is called the PowerSC Compliance and Assessment Reporting Tool.

Any conversations about Web Query will likely intersect with SQL. And that alone might account for some of the misconceptions and indifference toward it. Although SQL has made substantial inroads into the IBM i community, it remains an outlier, a bit of a mysterious stranger. Even though IBM decided years ago to make SQL a priority in its DB2 for i database enhancements–and database enhancements are usually the headliners in operating system enhancements for IBM i–SQL database techniques are not widely used in the i community.

“There is no requirement to change the constructs that define the database,” Mack notes. “Web Query is an SQL-based tool, but it can be used with non-SQL data structures. But having this conversation opens the door to what SQL can do.”

Web Query engagements are nowhere near as rare as database modernization engagements, but they increase the awareness of database modernization aspects and the possibilities that modernization brings. Modernization is really a strategic choice being made by enterprise IBM i shops, where a lot more code is being written and data centric development makes more sense from a cost-effectiveness perspective.

Web Query is available in Express and Standard editions. The Express Edition is a no-charge upgrade from Query/400, but its use is limited to a small handful of users. IBM has rejiggered the pricing on the Standard Edition, making it simpler to buy, but it remains pricey compared to reporting tools available from the third-party vendor community that offer the dashboarding capabilities that most shops in the SMB are looking for. You’ll also find IBM business partners with expertise in Query/400 to Web Query migrations that provide an optional (and likely less expensive) source for obtaining modern reporting skills using Web Query.