ACS Getting New Database Schema Functions
March 29, 2017 Alex Woodie
IBM is adding more functionality to the upcoming release of Access Client Solutions (ACS) that should make IBM i database engineers and database developers very happy, including a new capability that allows users to work directly with schemas.
The database schema function was not on the list of new ACS features that IBM released back in February, when it first unveiled IBM i 7.3 TR2 and IBM i 7.2 TR6. IBM uses the major operating system and technology refresh (TR) announcements as a vehicle to also announce other products that aren’t part of the OS proper, but which are important to the IBM i installed base nevertheless.
However, judging from a recent COMMON webinar by IBM’s DB2 for i Business Architect Scott Forstie, the situation regarding ongoing enhancements to the ACS product is quite fluid leading up to group database PTF delivery day.
“I don’t even have all the detail on here,” Forstie says in a 40-minute video covering the database enhancements in IBM i 7.3 TR2 and IBM i 7.2 TR6, which you can see here on the YouTube. “The paint’s not dry on this slide even. There’s still a few finishing touches, but it will be done soon here.”
Back in February, the upcoming release of ACS was slated to bring some interesting, if not earthshattering, new functionality. That included enhancements to the SQL formatting function and new CL prompting in the Run SQL Scripts interface, as well as improved filtering in the spool file output component.
But IBM was holding out on a major new database function it was secretly working on for the new release of ACS. IBM is calling it simply Schemas.
Forstie did his best to flesh out the schematic with the essential details during the webinar. As you can see from Figure 1, the Schema function gives the user a list of DB2 for i schemas on the left. The user can browse through those schemas and take a variety of supported actions upon them, including viewing a schema, copying it, or making changes to the schema definition.
Database engineers who have a need to work with schemas will like this new feature in ACS. “Schemas is going to let you navigate through your SQL schemas, also known as libraries, and interact with database objects,” Forstie says.
In particular, the “generate SQL” function in Schemas figures to empower DB2 for i database engineers to work with essential database building blocks in a powerful new way. “It will give me back the perfect SQL that would form one of those tables–not with the data in it, just the construct of it,” Forstie says.
Forstie discussed several other database-related enhancements that may not have been included in the original ACS announcement from IBM last month, including an IFS filtering function that provides a way to limit the result of a SQL query that’s being shown on the screen; and a new “upload results” function that lets the user save the results of a SQL query executed through the tool as a DB2 for i file.
If there’s a macro theme for ACS version 22.214.171.124, it’s that IBM i users should ban 5250 from their data diet. “Interactive SQL that doesn’t have a green screen is much better for you,” Forstie says. “So please lay off the green unless it’s healthy vegetables and be using ACS for your interactive SQL.”
ACS wasn’t designed to be a database engineer’s best friend. The Java-based client was intended to be a run-anywhere program that would allow IT pros and end users alike to tackle essential IBM i tasks, and as such it included a full 5250 emulator, printer emulation, data transfer facilities, file viewers, and a virtual console for LAN and HMC management.
However, the product’s position as a core database tool has emerged over the past several years, which means Forstie and his team are increasingly called on to provide feedback.
“Tim Rowe, Jessie Gorzinski, and team do an outstanding job on ACS,” Forstie says in the presentation. “I’m a very fortunate person. I get to work directly with these two brilliant guys and they allow me to help guide and influence and even test in some cases, what ACS should be for the database user.”