IBM i Tech Refresh Reiterates Database Emphasis
October 12, 2015 Dan Burger
|
Whether or not the majority of IBM i shops have an interest in SQL, IBM continues to make database enhancements its top priority as it rolls out new Technology Refreshes for its eponymous Power Systems operating system. The fact that Big Blue makes the DB2 for i database a priority is an indicator of its importance at the heart of the system. The i 7.1 TR11 and i 7.2 TR3 enhancements keep the database heart beating strong. Ever since the TR program launched with the release of IBM i 7.1 TR1 in September 2010, we’ve seen database enhancements take the leading role in almost every TR to date. TR11/TR3 followed the established pattern. Scott Forstie is the DB2 for i business architect at IBM. I asked him, during our phone conversation last week, what he would pick as the most significant database enhancements in the Technology Refresh that becomes generally available on November 20. He began with LIMIT and OFFSET, programming capabilities that application and database developers should find useful. Using the OFFSET clause, an SQL programmer can create a subset of the data by skipping rows in the query result before having rows returned. It would be used when an SQL query returns more results than the developer wants to consume. In other words, he or she would “offset” into that SQL query result. It becomes the programmer’s decision on the number of rows offset. After offsetting to the appropriate row, the LIMIT clause is used to control the number of rows returned. These capabilities are particularly useful with mobile development, where the mobile app does not require all the data of the traditional desktop app. “My criteria for choosing the LIMIT and OFFSET capabilities as one of the top enhancements is based on how many people have asked for the feature,” Forstie says. “LIMIT AND OFFSET has come up often through the years. It made sense for us to invest in that because of the mobile and stateless environments our clients are embracing.” Also at the top of Forstie’s list of database enhancements is the SQL-based bundle of IBM i services are designed to provide important operating system details. Many people will respond to that news with the retort that they already have commands that do this work. Why reinvent the wheel and call it one of the top SQL enhancements of the TR? On the way to answering that question, let’s take a look at the services that will be available when TR11/TR3 becomes available next month.
These services were created as a collaborative effort with IBM subject matter experts outside the database team. “At first, the members of these other development teams were puzzled about what these database people wanted,” Forstie recalls. “They were thinking, ‘We have commands and APIs. Why do we need SQL?’ They didn’t know or understand what could be accomplished with a query engine–how it could improve systems management or solve an application development problem.” Forstie explains further. “The services show the brilliance of user defined table functions. It looks like a physical table because you just query it. But behind the scenes you have a programming engine that’s deriving the rows and columns in real time and presenting the information in a way that you like so you can order and group things. User defined table functions combine transaction history tables in a single function. That’s a benefit to developers because they don’t need to repeat logic multiple times as they typically do when building an app. When people have their eyes open to this, then they get it.” Most of these teams don’t have database experts. They have subject matter experts making decisions on what data and data types to use. They determine what the data looks like. The database people help with the decisions about how the view should be created and what the UDTF engine needs to do to produce what’s needed. That’s the collaboration. Forstie says the same discovery will happen in IBM i shops as they become more familiar with SQL. These aren’t the first services Forstie’s database team has delivered in a TR. According to the database business architect, more people are becoming aware that these services exist. That leads to looking for more meaningful questions to answer using SQL.
“It is not my goal to provide SQL alternatives for every IBM i command. I’m trying to lead our clients down a path so they can help me prioritize what is useful. Some things are fine as is. You don’t need to renovate everything; just look for the most valuable opportunities,” he says. Future TR cycles are very likely to introduce more of these services. To convince end users that SQL is better than the commands they are now using takes examples like the SQL services in the TRs. “It takes time,” Forstie says. “Most people I talk with have not heard of this sort of thing. If you haven’t heard about it, it’s hard to get some value out of it. It becomes easier as we go. I can show how companies benefit, so that it makes sense to people who are not database conversant. I have information from clients that love it.” Not everyone wants to add SQL programming to their list of skills, however. And sometimes there are comprises that give the edge to existing CL commands and APIs rather than using SQL. The options co-exist, Forstie says. It’s not an either or situation in all cases. And it’s not about ripping out what works and replacing it with something just because it’s considered to be new and modern. “But in instances where the view of the data is inadequate, that’s where there is value,” Forstie says. “And in instances where we’ve created a tool that can be compared with an existing tool that is based on APIs and CL commands, the comparison points out the automation and content value the SQL provides.” Whether it’s IBM or an end user, decisions get made after considering factors such as value, risk, development costs and payback. “Now that we have built as many of these services as we have, it’s not terribly expensive to build another one,” Forstie says. One more TR enhancement that gets singled out today is the Run SQL Scripts capability that’s being added to Access Client Solutions. Forstie bets this will have the widest impact. IBM i Access Client Solutions is the interface for accessing and managing IBM i from multiple end-user device options. It’s an option to i Access for Windows, which is still supported but has not received any enhancements for some time. Run SQL Scripts is a feature of i Access for Windows. “If you were to compare every function and option that is in the Windows version of Run SQL Scripts to what we are delivering in ACS, you would find all the function that would be considered ‘normal’ usage,” says IBM i business architect Tim Rowe. “Some of the function links over to the Web Navigator and there are a couple of more advanced functions that are not included with this iteration. These advanced items will be delivered in a future ACS iteration. We have delivered 100 percent of what 90 percent of the users require.” There are more DB2 for i enhancements in the upcoming Technology Refreshes for i 7.1 and 7.2. SQL Query performance gains are certainly part of the TR. Security is another on-going process with new improvements applicable to database and operating system governance. Scans of database infrastructures to detect vulnerabilities are an added capability. For more information on the Technology Refresh, see the IBM announcement letter 215-351. RELATED STORIES It’s TR Time: What’s Next For The IBM i Database? DB2 Enhancements, Free Form RPG, Modernization Top Rowe’s ‘Big Hits’ List App Dev, Database Top IBM i TR9 and TR1 Enhancements IBM i 7.2 Tightens Data Access And Security As The World Turns: Investments In IBM i IBM i Modernization Redbook A Must Read Get Database Skills for Career ROI
|
