IBM i TRs Bring Database Enhancements, Too
October 30, 2019 Alex Woodie
The heart of the IBM i server is its database, so it’s natural that we would see some enhancements to Db2 for i with delivery of IBM i 7.4 Technology Refresh 1 and 7.3 TR 7 next month. The new features may not be earth-shaking, but they deliver some useful functionality nonetheless.
Let’s start with some foundational changes to the database itself, including the way that database engineers architect the tables that store information in the database. With 7.4 TR1, IBM i shops can now add a new child table to the database without having an exclusive lock on the parent table. This will make it easier for database engineers to expand the database without violating referential integrity.
This new feature is only available on IBM i 7.4 TR1, however. In its developerWorks article on the topic, IBM explains that the new function applies to any operation that adds or removes a foreign key constraint.
In a similar vein, administrators can now grant and revoke access to database files using the GRTOBJAUT and RVKOBJAUT CL commands without having an exclusive lock on the file. That means the administrator can update DDL access paths without forcing users off the system, as they previously had to do. This feature is available with 7.4 TR1 and 7.3 TR7.
Another new feature for 7.3 and 7.4 is the capability to deploy changes to database tables without needing to respond to the inquiry message warning about possible data loss. This will prevent the operation from hanging, according to the developerWorks entry on this topic.
Adding a child table to a Db2 for i database no longer requires a lock on the parent table with 7.4 TR1. (Image courtesy IBM)
Just as it has with previous TRs, IBM is giving users SQL-based alternatives to traditional control language (CL) commands and APIs on IBM i. A user won’t always find that completing an administrative is easier with a SQL command, but in some situations, that’s just what the doctor ordered.
First up, we have new SQL alternatives to the Display Program (DSPPGM) and Display Service Program (DSPSRVPGM). These SQL commands replace traditional CL commands and allow an IBM i professional to use SQL for tasks like conducting impact analysis, managing application implementations, and other use cases.
IBM has also delivered a SQL alternative to the Retrieve Directory Information (RTVDIRINF) command and the Qp0lGetAttr()–Get Attributes API. The new IFS_OBJECT_STATISTICS() UDTF table function lets IBM i pros find and manage objects within the Integrated File System (IFS), IBM says.
Lastly, IBM giveth the community SQL replacements for two APIs: Retrieve Object References (QP0LROR) and Retrieve Referenced Objects (QP0LRRO). The new SQL table functions — IFS_JOB_INFO, IFS_OBJECT_USAGE_INFO and IFS_OBJECT_USAGE_JOBS — make it easy to capture details about IFS usage for systems-management and application-management use cases, the company says.
These are important enhancements, according to Tim Rowe, the IBM i business architect in charge of application development and middleware. “The ability to understand and look at your IFS through an SQL service” is quite useful, Rowe said. “You can understand who has the largest file in the IFS and get a top 10 list of that in a matter of seconds.”
There’s one more new feature for developers who use the IFS for source code control. Now C and C++ developers can use embedded SQL with their Unicode UTF-8 (CCSSID 1208) source code on the SQL precompiler, IBM said.
For more info on the database enhancements, check out the developerWorks wiki for Db2 for i updates. You can also read more about IBM i 7.4 TR1 in announcement letter 219-492 and read about IBM i 7.3 TR7 in announcement letter 219-489.
RELATED STORIES
How The Latest TRs Bolster HA/DR And Security
I wonder if this is part of the knowledge base that will be trashed shortly with developerWorks?