IBM leads this Technology Refresh with database improvements to improve customer business value.
Last week, I was fortunate to have a chat with Steve Will, Chief Architect of the IBM i operating system, and Scott Forstie, DB2 for i Business Architect, about what’s new on the software side of this announcement. I'll also take a look at the hardware side.
Steve Pitcher: Steve, why was this Technology Refresh announcement made so early in the year? To coincide with the new hardware being released?
Steve Will: It is exactly to coincide with that. Power Systems wanted to get the new machine out. In order to do that, we needed to provide support with a new Technology Refresh. If we put a TR out, we want to make sure we’re announcing all of the Spring functions at the same time.
SP: A big part of this announcement is DB2. It has been like that for quite some time. I’ve said it before, and I’ll reiterate that, in my humble opinion, DB2 is by far the most important feature of the IBM i operating environment.
Scott Forstie: For the last few years, I keep hearing things like “It seems like you guys have really accelerated how fast you guys update the database.” I like to hear that, but I think we’ve done a better job in promoting it. People are noticing we’re continually updating the database.
Our focus, or strategy, is updating SQL. I try to link this with business value. It’s about productivity. It’s about saving time. We’re going through great lengths to make SQL consumable. While some clients may not be using DB2 as SQL or may not want to become SQL experts, they should still be able to find things to help them. The other end of the spectrum is people who are SQL users, and they just consume these updates like crazy. They love these things. SQL programmers get to do more in fewer steps. Things you used to have to write programs for are encapsulated in a single statement.
Also, we have better IBM i systems management. We’ve taken a big step forward with security administration and compliance.
SP: Give me an example please.
SF: Look at authorization lists. It’s tried and true technology and a good practice to use them. It simplifies deployment and management of security. We’ve now given you the ability to ask tough security questions about authorization lists by way of a query and get actionable answers back.
I can show the *PUBLIC security settings for all my authorization lists with a single statement.
WHERE AUTHORIZATION_NAME = '*PUBLIC';
The result looks like this:
Figure 1: Authorization list security settings in one statement
It returns a row for every authorization list. Now the value here is that you can see two authorization lists that anyone on the system can change. As a security auditor, we may want to put this type of query in a daily or weekly report to help with our security strategy. Are we in compliance with our strategy?
This very query is built into IBM i Access Client Solutions. You don’t need to view my presentations and download the slides to find this. It’s in the tool.
SP: What do you mean? It’s in the tool?
SF: It’s built into Run SQL Scripts inside Access Client Solutions. We have an option in there. It’s Edit > Insert from Examples. We have categories for all of these things including these services. You get good payback from people using these examples, but we hope you go a step further and build upon these to do a self assessment.”
We’ve extended Access Client Solutions to really help the database engineer. The database engineer will be interested in what they can now do with this Java-based tool. Anybody on a Mac will be quite happy about this. They don’t have to worry about running the old Windows Access application in some tolerance mode.
SP: And Linux guys like me...
SF: We’ve added Schemas. It’s for navigating from schema to schema, or library to library. Working with indexes. Authorize a view. All those actions are becoming approachable using ACS. This isn’t the endpoint in the journey, but it is a significant step forward.
SP: This is very good to see. We pulled a huge MySQL schema into DB2 on 7.3. We were in the 7.3 IBM Navigator for i web interface doing row and column access work. It performed okay, but I wouldn’t call it speedy. I’d imagine this is much faster.
SF: Yes. Also the number of clicks you need to use is far less compared to the browser. Any time you’re seeing a large amount of data, this is the hands-down winner.
SP: So my question to you would be, is the intent to move Schemas from Navigator for i to ACS or maintain them both in each interface?
SF: We need to get as much content into ACS so that the System i Navigator is no longer necessary. Much of the features are already in ACS and in the Navigator for i interface. For database engineers, Navigator for i lacks the usability that they’ve grown accustomed to and frankly they need. Some of the things work great in Navigator for i. Some work okay, but when you start to scale the amount of data and complexity of database objects, it makes it hard to get your job done. There are some things that have never made it into the browser interface. Some things are in both. Some things are in one or the other. The user type and what you want to accomplish really dictates what tool you spend most of your time in. Let’s take performance. It’s in Navigator for i under Performance Data Investigator. Would you expect to see that in ACS? Today’s answer would be no. It works great in the browser, so there’s no rationale to move it over. For database users, we had a strong reason to bring Schemas into ACS rather than Navigator.
Figure 2: Schemas in ACS offer developers fast and easy access to DB2
Author's note: The new release of Access Client Solutions (188.8.131.52) is targeted for release in March 2017.
Other Database Software Components of Today’s Announcement
- New JSON_TABLE function that allows queries to process JSON as relational data and leverages HTTP functions to tap external JSON data sources
- IS or IS NOT JSON query predicate
- JSON EXISTS query predicate
- New SQL built-in function: LISTAGG aggregates a set of string values for the group into one string by appending the string-expression
- Enhanced SQL built-in functions: LTRIM and RTRIM add optional second parameter (trim-expression)
- RPG Compiler PTFs are included in DB2 PTF Groups (!)
- Nested data structures
SQL Programming Enhancements
- Ability to use remote three-part names within triggers
- Improved HTTP Verbose functions
- SQL RPG precompiler generates free-format code
Database Engineer (DBE) Enhancements
- Index Advisor efficiency improvements
- Improved ACS features
Database Architectural Limit Enhancements
- Maximum number of literals in an SQL statement extended from 4K to 32K
Announcing the New IBM Power Systems S812
The new hardware in question is a Power Systems model S812. This machine appears to be IBM’s answer to the small customers whose step into POWER8 would’ve been, until now, much more horsepower than they needed. Its configuration is either a single-core IBM i partition or a maximum four-core AIX partition.
Below are the details of today’s IBM announcement:
The Power System S812 server brings robust and solid business transaction processing with proven infrastructure for AIX and IBM i operating system environments. The S812 is a 1-socket, 2U system designed to meet compute demands of larger deployments, for example, in retail space and as an entry point for noncompute-intense workloads that require high reliability and stability.
This 1-socket system can be ordered with one core for IBM i (up to 25 users) and up to four cores for AIX. The S812 provides the benefits of great performance per core and per socket, utilizing the POWER8 processor. It also provides resilient I/O capabilities, internal storage, and hot-plug PCI capabilities, along with greater reliability, availability, and serviceability.
Other benefits include:
• A foundation to core workloads in small footprints at a very competitive price point
• Right fit for small businesses and scale-out deployments
• Energy efficient and easy to manage by utilizing advanced energy control
The Power System S812 supports one processor socket, offering 1-core with IBM i or 4-core with AIX 3.026 GHz POWER8 configurations in a 19-inch rack-mount, 2U (EIA units) drawer configuration.
The Power S812 server supports a maximum of 64 GB DDR4 memory for IBM i workloads and a maximum of 128 GB DDR4 memory for AIX workloads. Memory features supported are 16 GB and 32 GB and run at speeds of 1600 Mbps.
I/O and system attributes in the system unit include:
• Six PCIe Gen3 Low Profile (LP) Slots (all hot pluggable)
• CAPI capable on two PCIe x16 slots (AIX)
• Twelve or eight 2.5-in. SAS SFF-3 bays for HDD or SSD (all hot pluggable)
– Eight bays available for IBM i
– Eight or twelve bays for AIX, depending on backplane
• Multiple RAID options supported
• One DVD
• Two front USB 3.0 and two rear USB 2.0 ports
• One system port (rear)
• Service processor
• 1+1 Redundant hot-swap AC power supplies
• 19-inch rack-mount 2U configuration
• AIX 6.1, 7.1, 7.2, or later, operating system support
• IBM i 7.3 TR2, 7.2 TR6, or later, operating system support with a maximum of 25 IBM i user entitlements